mysql连表查询基本增删改查

1. #MYSQL的一下基础
2. 启动mysql服务
3. net start mysql
4. 
5. 
6. ===================================================
7. CREATE TABLE user_u_s (
8.  id INT(4) ZEROFILL,#属性zerofill长度不够0来补齐
9.  `name` NVARCHAR(20)
10. );
11. =====================================================
12. CREATE TABLE user_tb(
13.   id INT(5) PRIMARY KEY AUTO_INCREMENT,#auto_increment属性为自增
14. 
15. );
16. =========================================================
17. 
18. #表 test
19. DESC test  #desc 表名 查看表结构
20. 
21. SHOW CREATE TABLE test;#展示创建表的语a句
22. 
23. 
24. ======================================================
25. #创建t01表
26. CREATE TABLE t01 (
27.   id INT(5)PRIMARY KEY AUTO_INCREMENT,
28.   `name` NVARCHAR(10)
29. )AUTO_INCREMENT =100;#创建表的末尾添加auto_increment = 100 ;自增从100开始
30. #修改表的自增方式,增长方式为20,20 的增加
31. ALTER TABLE t01 AUTO_INCREMENT = 20;
32. 
33. #增长方式为跳着增长.为5个5个的增长
34. SET @@auto_increment = 5;
35. 
36. #清空表，但是自增在原有数据上增长
37. DELETE FROM t01;
38. #清空表，自增从1开始 truncate
39. TRUNCATE
40. =======================================================
41. 
42. CREATE TABLE t02(
43.   id INT(4),
44.   `name` NVARCHAR(10) DEFAULT '男' #default 用来设置默认值的
45. 
46. );
47. =====================================================
48. CREATE TABLE t03(
49.   id INT(2) COMMENT 't03表的主键',#列注释
50.   `name` NVARCHAR(10) COMMENT '姓名'#列注释
51.   PRIMARY KEY ('id') #约束可以这样写
52. )COMMENT = 't03表' ;#表注释
53. 
54. 数据库表类型
55. =============================================
56. #表类型写在括号后面
57. creat table 表 (
58. 
59. id int(4),
60. name varcher(10)
61. 
62. )engine = MyISAM或engine = innoDB;
63. #engine = MyISAM用于表查询
64. #engine = innoDB 用于表事物，增删改
65. 
66. 
67. =============================================
68. #修改表名:ALTER TABLE 旧表名 RENAME AS 新表名;
69. ALTER TABLE test RENAME AS test01;
70. 
71. #添加字段
72. ALTER TABLE test01 ADD `age1` INT(2);
73. #修改字段属性
74. ALTER TABLE test01 MODIFY `name` CHAR(4)
75. #删除字段
76. ALTER TABLE test01 DROP age;
77. #修改字段
78. ALTER TABLE test01 CHANGE `name` `username` NVARCHAR(10)
79. #添加主键
80. ALTER TABLE test01 ADD CONSTRAINT pk_test01 PRIMARY KEY(id);
81. #删除主键
82. ALTER TABLE test01 PRIMARY KEY ;
83. #删除外键
84. ALTER TABLE test01 DROP FOREIGN KEY fk_test01;
85. #添加外键
86. ALTER TABLE test01
87.   ADD CONSTRAINT  fk_test01
88.   FOREIGN KEY (id)
89.   REFERENCES test(id);
90. 
91. 创建主外键
92. ===================================================
93. CREATE TABLE t01(
94. id INT(5) AUTO_INCREMENT PRIMARY KEY NOT NULL,
95. `name` NVARCHAR(10)
96. 
97. );
98. 
99. CREATE TABLE t02(
100. idd INT(5)  NOT NULL,
101. `name` NVARCHAR(10)
102. 
103. );
104. 
105. ALTER TABLE t02 ADD CONSTRAINT
106. fk_t02 FOREIGN KEY (idd) 
107. REFERENCES t01(id);
108. 
109. 
110. 
111. 
112. ===================================================
113. DML数据操作介绍
114. insert
115. update
116. delete
117. 
118. ================================================
119. INSERT INTO t01 (`name`,age) VALUES('掌',1);
120. COMMIT
121. 
122. 修改数据
123. ====================================================
124. update t01 set name = 'zhang' , age =0 ; #不加where条件表里面的数据会全部被修改
125. 
126. UPDATE t01 SET `name`='张' ,age = 4 WHERE id= 3;
127. 
128. mysql自带的一些函数
129. UPDATE t01 SET age = age +10;
130. 
131. concat字符追加、、、在name列字段前添加hao的字符
132. UPDATE t01 SET `name` = CONCAT('hao',`name`);
133. 
134. 
135. where 条件拼接
136. =====================================================
137. and  or  < > !=或<>  >=  <= between(一般不用 效率低)
138. 
139. UPDATE t01 SET `name` ='新值' WHERE id>1 AND id<4; 
140. 
141. UPDATE t01 SET `name` ='hehe' WHERE id BETWEEN 3 AND 5; 
142. 
143. 
144. 
145. 事物提交方法
146. ===========================================================
147. 1.start transction 开始一个事物，标记事物的起点。
148. 2.commit 提交一个事物给数据库。
149. 3.rollback 将事物回滚，数据回到本次事物的初始状态。
150. 4.set autocommit = 1； 还原mysql数据库的自动提交。
151. 5.set autocommit = 0； 关闭mysql数据库的自动提交。
152. 
153. 演示银行转账
154. ============================================================
155. CREATE TABLE `account`(
156. `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
157. `name` NVARCHAR(30) NOT NULL,
158. `cash` DECIMAL(9,2) NOT NULL #整数位9位，小数位2位。
159. 
160. )ENGINE=INNODB;
161. 
162. INSERT INTO account (`name`,`cash`) VALUES('a',2000);
163. INSERT INTO account (`name`,`acsh`) VALUES('b',3000);
164. 
165. SET autocommit=0;
166. START TRANSACTION;
167. 
168. UPDATE account SET cash=cash-600 WHERE `name`= 'a';
169. UPDATE account SET cash=cash_600 WHERE `name`='b'
170. sql语句。。。
171. SET autocommit =1;
172. #commit
173. #rollback
174. 
175. 
176. 使用DQL查询
177. ============================================================
178. `grade` 年级表  
179. 字段
180. `GradeID`（PRIMARY KEY）,`GradeName`
181. PRIMARY KEY (`GradeID`)
182. 
183. `result` 成绩表
184. 字段
185. `StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`
186. KEY `SubjectNo` (`SubjectNo`)
187. 
188. `subject` 课程表
189. 字段
190. `SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`
191. PRIMARY KEY (`SubjectNo`)
192. 
193. student 学生表
194. 字段
195. `StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`
196. PRIMARY KEY (`StudentNo`)
197. ==========================================================
198. SELECT * FROM student ;# 用*查询效率低，一般不用
199. 
200. SELECT Sex,Phone FROM student; #只查看Sex,Phone 这两个字段
201. SELECT s.`Address`,s.`Email` FROM student AS s ; 使用别名查询其中 AS 可以省略（表起别名）
202. 
203. SELECT s.`Address`,s.`Email` FROM student s ; 使用别名查询（表起别名）
204. 
205. SELECT s.`Address` AS 地址,s.`Email` AS 邮箱 FROM student s ; 字段使用别名查询
206. 
207. SELECT COUNT(studentno) AS 总数 FROM student; 记录总数的函数（count）
208. 
209. 使用distinct
210. =====================================================================
211. 使用distinct去除重复，跟在select后面 后面是需要去除重复的字段
212. 语法：select distinct 字段1，字段2，字段N from 表
213. SELECT DISTINCT r.`SubjectNo`FROM result AS r;
214. 
215. ===============================================================
216. concat 查询出的列前加：学生 并跟列起的别名：学生姓名
217. SELECT CONCAT('学生：',studentName) 学生姓名 FROM student ;
218. 
219. SELECT VERSION();查询版本号
220. 
221. SELECT s.`ClassHour` AS 旧课时,s.`ClassHour`+10 新的课时 FROM `subject` AS s;
222. 
223. SELECT s.subjectName AS 课程名称,s.`ClassHour` AS 总课时, s.`ClassHour`/10 AS 平均课时 FROM `subject` AS s;
224. 
225. where条件语句
226. ==========================================================
227. 逻辑操作符
228. and或&& 逻辑与
229. or或|| 逻辑或
230. xor或^ 逻辑异域
231. not或！逻辑非
232. SELECT r.`StudentResult` AS 考试成绩 FROM result AS r 
233.  WHERE r.`StudentResult`>=70 AND r.`StudentResult`<=90;
234. 
235. 
236. =========================================================
237. #查询StudentName 为空的字段 而不是StudentName=null
238. SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` IS NULL; 
239. #查询StudentName字段 ‘李 ’开头的
240. SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '李%';
241. #查询StudentName字段 ‘文’ 结尾的
242. SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '%文';
243. #查询StudentName字段 ‘李 ’开头的后面匹配 任意 一个 字 一个下划线代表一个字
244. SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '李_';
245. #查询StudentName字段 开头的匹配任意一个 字 末尾以梅字结尾 一个下划线代表一个字
246. SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '_梅';
247. #查询StudentResult字段 成绩等于 79,90,80的人
248. SELECT r.`StudentResult` FROM result AS r WHERE r.`StudentResult` IN (79,90,80);
249. #转义字符 关键字 escape 来定义转义字符
250. SELECT r.`StudentResult` FROM result AS r WHERE r.`StudentResult` LIKE '%:%%' ESCAPE ':';
251. 
252. 
253. 联合查询(内连接)inner join
254. ===========================================================
255. 语法：select 字段1....字段n from 表1 inner join 表2 on 表1条件=表2条件；
256. （inner）可以省略
257. #两张表联合查询
258. SELECT s.`StudentName`,r.`StudentResult` 
259.  FROM student AS s INNER JOIN result AS r ON s.`StudentNo`=r.`StudentNo`
260. 
261. 
262. #三张表联合查询
263. SELECT s.`StudentName`,r.`StudentResult` ,u.`SubjectName`
264.  FROM student AS s INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
265.    INNER JOIN `subject` AS u ON r.`SubjectNo`=u.`SubjectNo`;
266. #查询成绩小于70的学生
267. SELECT s.`StudentName`,r.`StudentResult` 
268.  FROM student AS s INNER JOIN result AS r ON r.`StudentNo`=s.`StudentNo`
269.    WHERE r.`StudentResult`<=70;
270. 
271. left join    
272. =================================================================
273. #右表中没有返回null
274. SELECT DISTINCT s.`StudentName` AS 学生姓名：,r.`StudentResult`AS 成绩
275.  FROM student AS s LEFT JOIN result AS r ON s.`StudentNo`=r.`StudentNo`;
276. 
277. 
278. right join     
279. =================================================================
280. #左表中没有返回null 
281. SELECT DISTINCT s.`StudentName` AS 学生姓名：,r.`StudentResult`AS 成绩
282.  FROM student AS s RIGHT JOIN result AS r ON s.`StudentNo`=r.`StudentNo`;  
283. 
284. 等值与非等值（相当于 inner join） 
285. =============================================================
286. 语法：select 字段1，字段2 from 表1，表2 where 字段1=字段2；
287. SELECT a.`StudentName`,r.`StudentResult` FROM student AS a ,result AS r 
288.  WHERE a.`StudentNo`=r.`StudentNo` AND r.`StudentResult`>=80 
289. 
290. 自身连接
291. =================================================================
292. 1.类似父子标题的关系
293. CREATE TABLE IF NOT EXISTS  category(
294.  categoryId INT(10)  UNSIGNED  NOT NULL  AUTO_INCREMENT,
295.  pid  INT(10) NOT NULL,
296.  categoryName   VARCHAR(32) NOT NULL,
297.  PRIMARY KEY(categoryId)
298. );
299. 
300. INSERT INTO  category VALUES
301. (2,1,"美术设计"),
302. (3,1,"软件开发"),
303. (4,3,"数据库基础"),
304. (5,2,"Photoshop基础"),
305. (6,2,"色彩搭配学"),
306. (7,3,"PHP基础"),
307. (8,3,"一起学JAVA");
308. 
309. SELECT c1.`categoryName`,c2.`categoryName` FROM category AS c1 INNER JOIN category AS c2 
310.  ON c1.`categoryId` = c2.`pid`
311. 
312. ==================================================================
313. #order by 排序 desc （降序） asc（升序 默认）
314. 
315. SELECT r.studentResult AS 成绩 FROM result AS r ORDER BY r.studentResult DESC;
316. 
317. SELECT s.`SubjectName`,r.`StudentResult` 
318.  FROM result AS r INNER JOIN `subject` AS s ON s.`SubjectNo`=s.`SubjectNo`
319.  WHERE s.`SubjectName`='数据库结构-1' ORDER BY r.`StudentResult` DESC;
320. 
321. limit分页
322. ========================================================
323. #查询总素
324. SELECT COUNT(s.`StudentNo`)AS 总数 FROM student AS s ;
325. #分页关键字 limit 
326. #语法：limit m,n
327.  m 从第几条开始取（默认0）
328.  n 取出的 最大 数目
329. 
330. SELECT s.studentName,s.studentNo FROM student AS s ORDER BY s.studentNo LIMIT 0,3;
331. 
332. 子查询
333. =======================================================
334. 在查询语句中有嵌套了一个where查询
335. 
336. 
337. 
338. 
339. #聚合函数
340. ==========================================================
341. #聚合函数
342. COUNT() 总记录数
343. SUM()求和
344. AVG()平均数
345. MAX()最大值
346. MIN()最小值
347. 
348. SELECT COUNT(r.studentNo) '总条数',
349. AVG(r.studentResult) '平均数',
350. MAX(r.studentResult) '最高分', 
351. MIN(r.studentResult) '最低分'
352. FROM result AS r ;
353. 
354. 字符函数
355. ===========================================================
356. 
357. 数学函数
358. =============================================================
359. 
360. 日期函数
361. ==============================================================
362. SELECT NOW(); 获得系统当前时间。
363. 
364. 
365. 分组group by 一般与having结合使用（注意having的使用位置）
366. =============================================================
367. SELECT COUNT(r.studentNo) '总条数',
368. AVG(r.studentResult) '平均数',
369. MAX(r.studentResult) '最高分', 
370. MIN(r.studentResult) '最低分',
371.  s.subjectno AS 姓名
372. FROM result AS r INNER JOIN `subject` AS s ON r.subjectno=s.subjectno
373. GROUP BY  r.subjectno HAVING AVG(r.studentResult)>=80;
374. 
375. 
376. 
377. 索引
378. ==========================================================================
379. 作用：
380. 提高查询速度
381. 确保数据唯一性
382. 可以加速表和表之间的连接，实现表与表之间的参照完整性
383. 使用分组和排序子句进行数据检索可以显著减少分组和排序时间
384. 全文检索字段进行收索优化
385. 
386. # 主键索引primary key 
387. （只能有一个）
388. #唯一索引 unique
389. （避免重复 ，可以有多个）
390. 
391. #常规索引index/key
392. 注意：1.用于快速定位
393.    2.加在查找条件的列
394.    3.不宜添加太多常规索引，影响数据插入，修改，删除
395. #全文索引fulltext
396. 注意：1.fulltext索引只能用于MyISASM表类型；
397. 2.只能用于char ，varchar，text 数据类型列；
398.    3.适合大型数据集
399. 
400. 
401. 
402. 添加 primary key 默认这一列添加索引
403. 例：
404.  CREATE TABLE t03(
405. id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
406. `name` NVARCHAR(10) NOT NULL unique（唯一索引） 
407.  或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）
408. 
409. );
410. 索引2（常规，唯一）创建
411. =========================================================================
412.  CREATE TABLE t03(
413. id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
414. `name` NVARCHAR(10) NOT NULL unique（唯一索引） 
415.  或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）
416. `sex` NVARCHAR(2) INDEX/KEY(常规索引)一般常作为条件的时候添加索引
417.  或 INDEX index_sex ('sex')
418.  或 KEY key_sex('sex1','sex2')一次添加两个（谁的信息量小谁在前） 
419. );
420. #创建表完成后创建索引
421. ALTER TABLE 表名 ADD INDEX inde_sex('sex1','sex2');
422. 
423. 全文索引创建
424. =========================================================================
425. CREATE TABLE t03(
426. `name` VARCHAR(10) FULLTEXT，
427. `sex` text(10)
428. )ENGINE = MYISAM;
429. 
430. #或
431. ALTER TABLE t03 ADD FULLTEXT ('sex');
432. 
433. ALTER TABLE t03 ADD `sex` TEXT(100)
434. 
435. 注意：
436.  select * from t03 where match(列名) against（‘love’）；
437.    1.添加索引后统计出现的（love）次数；
438.    2.使用关键字against
439. 
440. 管理索引
441. ==============================================================
442. 
443. #创建表索引
444. ALTER TABLE table_name ADD 索引类型 （‘列名’）；
445. #删除索引
446. 1. DROP INDEX 索引名 ON 表名
447. 2. ALTER TABLE 表名 DROP INDEX 索引名
448. 3. ALTER TABLE 表名 DROP PRIMARY KEY;
449. 
450. #查看索引
451. SHOW INDEX  FROM 表名
452. 
453. mysql数据备份（DOS窗口下）
454. ================================================================
455. 备份方法：
456.  1.使用命令
457.  2.使用管理工具sqlyog
458.  3.直接拷贝数据文件和相关配置文件
459. 
460. mysql dump客户端
461. 语法：
462. mysqldump -h主机名 -u用户名 -p数据库名 [表1 表2 表n] >path/filename.sql
463. 例：
464. mysqldump -uroot -pt04 student（数据库名） student（表名） > f:/sql.sql  回车
465. 
466. mysql恢复
467. ==============================================================
468. 1.是mysql下
469.  关键字 source
470.    source /path/sql.sql
471. 2.DOS下
472.  mysql -u用户名 -p密码 数据库名 表名 </path/sql.sql;
473. 
474. 
475. 
476. 触发器
477. =============================================================
478. 
479. 
480. 创建视图
481. ===============================================================
482. view_lottert:视图的名
483. db_lottery：数据库中的表名
484. 查询视图跟普通查询一样
485. 视图一般做查询用
486. CREATE VIEW view_lottert AS SELECT * FROM db_lottery LIMIT 0,3
487. 
488. 
489. 
490. 
491. 复制表结构
492. =============================================================
493. 
494. 
495. 存储过程(看做函数或sql的集合)
496. ================================================================
497. 
498. 
499. 
500. 索引
501. ==========================================================================
502. 作用：
503. 提高查询速度
504. 确保数据唯一性
505. 可以加速表和表之间的连接，实现表与表之间的参照完整性
506. 使用分组和排序子句进行数据检索可以显著减少分组和排序时间
507. 全文检索字段进行收索优化
508. 
509. # 主键索引primary key 
510. （只能有一个）
511. #唯一索引 unique
512. （避免重复 ，可以有多个）
513. 
514. #常规索引index/key
515. 注意：1.用于快速定位
516.    2.加在查找条件的列
517.    3.不宜添加太多常规索引，影响数据插入，修改，删除
518. #全文索引fulltext
519. 注意：1.fulltext索引只能用于MyISASM表类型；
520. 2.只能用于char ，varchar，text 数据类型列；
521.    3.适合大型数据集
522. 
523. 
524. 
525. 添加 primary key 默认这一列添加索引
526. 例：
527.  CREATE TABLE t03(
528. id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
529. `name` NVARCHAR(10) NOT NULL unique（唯一索引） 
530.  或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）
531. 
532. );
533. 索引2（常规，唯一）创建
534. =========================================================================
535.  CREATE TABLE t03(
536. id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
537. `name` NVARCHAR(10) NOT NULL unique（唯一索引） 
538.  或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）
539. `sex` NVARCHAR(2) INDEX/KEY(常规索引)一般常作为条件的时候添加索引
540.  或 INDEX index_sex ('sex')
541.  或 KEY key_sex('sex1','sex2')一次添加两个（谁的信息量小谁在前） 
542. );
543. #创建表完成后创建索引
544. ALTER TABLE 表名 ADD INDEX inde_sex('sex1','sex2');
545. 
546. 全文索引创建
547. =========================================================================
548. CREATE TABLE t03(
549. `name` VARCHAR(10) FULLTEXT，
550. `sex` text(10)
551. )ENGINE = MYISAM;
552. 
553. #或
554. ALTER TABLE t03 ADD FULLTEXT ('sex');
555. 
556. ALTER TABLE t03 ADD `sex` TEXT(100)
557. 
558. 注意：
559.  select * from t03 where match(列名) against（‘love’）；
560.    1.添加索引后统计出现的（love）次数；
561.    2.使用关键字against
562. 
563. 管理索引
564. ==============================================================
565. 
566. #创建表索引
567. ALTER TABLE table_name ADD 索引类型 （‘列名’）；
568. #删除索引
569. 1. DROP INDEX 索引名 ON 表名
570. 2. ALTER TABLE 表名 DROP INDEX 索引名
571. 3. ALTER TABLE 表名 DROP PRIMARY KEY;
572. 
573. #查看索引
574. SHOW INDEX  FROM 表名
575. 
576. mysql数据备份（DOS窗口下）
577. ================================================================
578. 备份方法：
579.  1.使用命令
580.  2.使用管理工具sqlyog
581.  3.直接拷贝数据文件和相关配置文件
582. 
583. mysql dump客户端
584. 语法：
585. mysqldump -h主机名 -u用户名 -p数据库名 [表1 表2 表n] >path/filename.sql
586. 例：
587. mysqldump -uroot -pt04 student（数据库名） student（表名） > f:/sql.sql  回车
588. 
589. mysql恢复
590. ==============================================================
591. 1.是mysql下
592.  关键字 source
593.    source /path/sql.sql
594. 2.DOS下
595.  mysql -u用户名 -p密码 数据库名 表名 </path/sql.sql;
596. 
597. 
598. 
599. 
600. 
601. 
602. 
603. 
604. 
605. 
606. 
607. 
608. 
609. 
610. 
611.

#MYSQL的一下基础
启动mysql服务
net start mysql
 
 
===================================================
CREATE TABLE user_u_s (
  id INT(4) ZEROFILL,#属性zerofill长度不够0来补齐
  `name` NVARCHAR(20)
);
=====================================================
CREATE TABLE user_tb(
  id INT(5) PRIMARY KEY AUTO_INCREMENT,#auto_increment属性为自增
 
);
=========================================================
 
#表 test
DESC test  #desc 表名 查看表结构
 
SHOW CREATE TABLE test;#展示创建表的语句
 
 
======================================================
#创建t01表
CREATE TABLE t01 (
  id INT(5)PRIMARY KEY AUTO_INCREMENT,
  `name` NVARCHAR(10)
)AUTO_INCREMENT =100;#创建表的末尾添加auto_increment = 100 ;自增从100开始
#修改表的自增方式,增长方式为20,20 的增加
ALTER TABLE t01 AUTO_INCREMENT = 20;
 
#增长方式为跳着增长.为5个5个的增长
SET @@auto_increment = 5;
 
#清空表，但是自增在原有数据上增长
DELETE FROM t01;
#清空表，自增从1开始 truncate
TRUNCATE
=======================================================
 
CREATE TABLE t02(
  id INT(4),
  `name` NVARCHAR(10) DEFAULT '男' #default 用来设置默认值的
 
);
=====================================================
CREATE TABLE t03(
  id INT(2) COMMENT 't03表的主键',#列注释
  `name` NVARCHAR(10) COMMENT '姓名'#列注释
  PRIMARY KEY ('id') #约束可以这样写
)COMMENT = 't03表' ;#表注释
 
数据库表类型
=============================================
#表类型写在括号后面
creat table 表 (
 
id int(4),
name varcher(10)
 
)engine = MyISAM或engine = innoDB;
#engine = MyISAM用于表查询
#engine = innoDB 用于表事物，增删改
 
 
=============================================
#修改表名:ALTER TABLE 旧表名 RENAME AS 新表名;
ALTER TABLE test RENAME AS test01;
 
#添加字段
ALTER TABLE test01 ADD `age1` INT(2);
#修改字段属性
ALTER TABLE test01 MODIFY `name` CHAR(4)
#删除字段
ALTER TABLE test01 DROP age;
#修改字段
ALTER TABLE test01 CHANGE `name` `username` NVARCHAR(10)
#添加主键
ALTER TABLE test01 ADD CONSTRAINT pk_test01 PRIMARY KEY(id);
#删除主键
ALTER TABLE test01 PRIMARY KEY ;
#删除外键
ALTER TABLE test01 DROP FOREIGN KEY fk_test01;
#添加外键
ALTER TABLE test01
  ADD CONSTRAINT  fk_test01
  FOREIGN KEY (id)
  REFERENCES test(id);
 
创建主外键
===================================================
CREATE TABLE t01(
id INT(5) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`name` NVARCHAR(10)
 
);
 
CREATE TABLE t02(
idd INT(5)  NOT NULL,
`name` NVARCHAR(10)
 
);
 
ALTER TABLE t02 ADD CONSTRAINT
fk_t02 FOREIGN KEY (idd) 
REFERENCES t01(id);
 
 
 
 
===================================================
DML数据操作介绍
insert 
update
delete
 
================================================
INSERT INTO t01 (`name`,age) VALUES('掌',1);
COMMIT
 
修改数据
====================================================
update t01 set name = 'zhang' , age =0 ; #不加where条件表里面的数据会全部被修改
 
UPDATE t01 SET `name`='张' ,age = 4 WHERE id= 3;
 
mysql自带的一些函数
UPDATE t01 SET age = age +10;
 
concat字符追加、、、在name列字段前添加hao的字符
UPDATE t01 SET `name` = CONCAT('hao',`name`);
 
 
where 条件拼接
=====================================================
and  or  < > !=或<>  >=  <= between(一般不用 效率低)
 
UPDATE t01 SET `name` ='新值' WHERE id>1 AND id<4; 
 
UPDATE t01 SET `name` ='hehe' WHERE id BETWEEN 3 AND 5; 
 
 
 
事物提交方法
===========================================================
1.start transction 开始一个事物，标记事物的起点。
2.commit 提交一个事物给数据库。
3.rollback 将事物回滚，数据回到本次事物的初始状态。
4.set autocommit = 1； 还原mysql数据库的自动提交。
5.set autocommit = 0； 关闭mysql数据库的自动提交。
 
演示银行转账
============================================================
CREATE TABLE `account`(
`id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` NVARCHAR(30) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL #整数位9位，小数位2位。
 
)ENGINE=INNODB;
 
INSERT INTO account (`name`,`cash`) VALUES('a',2000);
INSERT INTO account (`name`,`acsh`) VALUES('b',3000);
 
SET autocommit=0;
START TRANSACTION;
 
UPDATE account SET cash=cash-600 WHERE `name`= 'a';
UPDATE account SET cash=cash_600 WHERE `name`='b'
sql语句。。。
SET autocommit =1;
#commit 
#rollback
 
 
使用DQL查询
============================================================
`grade` 年级表  
字段
`GradeID`（PRIMARY KEY）,`GradeName`
PRIMARY KEY (`GradeID`)
 
`result` 成绩表
字段
`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`
KEY `SubjectNo` (`SubjectNo`)
 
`subject` 课程表
字段
`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`
PRIMARY KEY (`SubjectNo`)
 
student 学生表
字段
`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`
PRIMARY KEY (`StudentNo`)
==========================================================
SELECT * FROM student ;# 用*查询效率低，一般不用
 
SELECT Sex,Phone FROM student; #只查看Sex,Phone 这两个字段
SELECT s.`Address`,s.`Email` FROM student AS s ; 使用别名查询其中 AS 可以省略（表起别名）
 
SELECT s.`Address`,s.`Email` FROM student s ; 使用别名查询（表起别名）
 
SELECT s.`Address` AS 地址,s.`Email` AS 邮箱 FROM student s ; 字段使用别名查询
 
SELECT COUNT(studentno) AS 总数 FROM student; 记录总数的函数（count）
 
使用distinct
=====================================================================
使用distinct去除重复，跟在select后面 后面是需要去除重复的字段
语法：select distinct 字段1，字段2，字段N from 表
SELECT DISTINCT r.`SubjectNo`FROM result AS r;
 
===============================================================
concat 查询出的列前加：学生 并跟列起的别名：学生姓名
SELECT CONCAT('学生：',studentName) 学生姓名 FROM student ;
 
SELECT VERSION();查询版本号
 
SELECT s.`ClassHour` AS 旧课时,s.`ClassHour`+10 新的课时 FROM `subject` AS s;
 
SELECT s.subjectName AS 课程名称,s.`ClassHour` AS 总课时, s.`ClassHour`/10 AS 平均课时 FROM `subject` AS s;
 
where条件语句
==========================================================
逻辑操作符
and或&& 逻辑与
or或|| 逻辑或
xor或^ 逻辑异域
not或！逻辑非
SELECT r.`StudentResult` AS 考试成绩 FROM result AS r 
  WHERE r.`StudentResult`>=70 AND r.`StudentResult`<=90;
 
 
=========================================================
#查询StudentName 为空的字段 而不是StudentName=null
SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` IS NULL; 
#查询StudentName字段 ‘李 ’开头的
SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '李%';
#查询StudentName字段 ‘文’ 结尾的
SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '%文';
#查询StudentName字段 ‘李 ’开头的后面匹配 任意 一个 字 一个下划线代表一个字
SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '李_';
#查询StudentName字段 开头的匹配任意一个 字 末尾以梅字结尾 一个下划线代表一个字
SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '_梅';
#查询StudentResult字段 成绩等于 79,90,80的人
SELECT r.`StudentResult` FROM result AS r WHERE r.`StudentResult` IN (79,90,80);
#转义字符 关键字 escape 来定义转义字符
SELECT r.`StudentResult` FROM result AS r WHERE r.`StudentResult` LIKE '%:%%' ESCAPE ':';
 
 
联合查询(内连接)inner join
===========================================================
语法：select 字段1....字段n from 表1 inner join 表2 on 表1条件=表2条件；
（inner）可以省略
#两张表联合查询
SELECT s.`StudentName`,r.`StudentResult` 
  FROM student AS s INNER JOIN result AS r ON s.`StudentNo`=r.`StudentNo`
 
 
#三张表联合查询
SELECT s.`StudentName`,r.`StudentResult` ,u.`SubjectName`
  FROM student AS s INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
    INNER JOIN `subject` AS u ON r.`SubjectNo`=u.`SubjectNo`;
#查询成绩小于70的学生
SELECT s.`StudentName`,r.`StudentResult` 
  FROM student AS s INNER JOIN result AS r ON r.`StudentNo`=s.`StudentNo`
    WHERE r.`StudentResult`<=70;
 
left join     
=================================================================
#右表中没有返回null
SELECT DISTINCT s.`StudentName` AS 学生姓名：,r.`StudentResult`AS 成绩
  FROM student AS s LEFT JOIN result AS r ON s.`StudentNo`=r.`StudentNo`;
 
 
right join    
=================================================================
#左表中没有返回null  
SELECT DISTINCT s.`StudentName` AS 学生姓名：,r.`StudentResult`AS 成绩
  FROM student AS s RIGHT JOIN result AS r ON s.`StudentNo`=r.`StudentNo`;  
 
等值与非等值（相当于 inner join）  
=============================================================
语法：select 字段1，字段2 from 表1，表2 where 字段1=字段2；
SELECT a.`StudentName`,r.`StudentResult` FROM student AS a ,result AS r 
  WHERE a.`StudentNo`=r.`StudentNo` AND r.`StudentResult`>=80 
 
自身连接
=================================================================
1.类似父子标题的关系
CREATE TABLE IF NOT EXISTS  category(
  categoryId INT(10)  UNSIGNED  NOT NULL  AUTO_INCREMENT,
  pid  INT(10) NOT NULL,
  categoryName   VARCHAR(32) NOT NULL,
  PRIMARY KEY(categoryId)
);
 
INSERT INTO  category VALUES
(2,1,"美术设计"),
(3,1,"软件开发"),
(4,3,"数据库基础"),
(5,2,"Photoshop基础"),
(6,2,"色彩搭配学"),
(7,3,"PHP基础"),
(8,3,"一起学JAVA");
 
SELECT c1.`categoryName`,c2.`categoryName` FROM category AS c1 INNER JOIN category AS c2 
  ON c1.`categoryId` = c2.`pid`
 
==================================================================
#order by 排序 desc （降序） asc（升序 默认）
 
SELECT r.studentResult AS 成绩 FROM result AS r ORDER BY r.studentResult DESC;
 
SELECT s.`SubjectName`,r.`StudentResult` 
  FROM result AS r INNER JOIN `subject` AS s ON s.`SubjectNo`=s.`SubjectNo`
  WHERE s.`SubjectName`='数据库结构-1' ORDER BY r.`StudentResult` DESC;
 
limit分页
========================================================
#查询总素
SELECT COUNT(s.`StudentNo`)AS 总数 FROM student AS s ;
#分页关键字 limit 
#语法：limit m,n
  m 从第几条开始取（默认0）
  n 取出的 最大 数目
  
SELECT s.studentName,s.studentNo FROM student AS s ORDER BY s.studentNo LIMIT 0,3;
 
子查询
=======================================================
在查询语句中有嵌套了一个where查询
 
 
 
 
#聚合函数
==========================================================
#聚合函数
COUNT() 总记录数
SUM()求和
AVG()平均数
MAX()最大值
MIN()最小值
 
SELECT COUNT(r.studentNo) '总条数',
AVG(r.studentResult) '平均数',
 MAX(r.studentResult) '最高分', 
 MIN(r.studentResult) '最低分'
FROM result AS r ;
 
字符函数
===========================================================
 
数学函数
=============================================================
 
日期函数
==============================================================
SELECT NOW(); 获得系统当前时间。
 
 
分组group by 一般与having结合使用（注意having的使用位置）
=============================================================
SELECT COUNT(r.studentNo) '总条数',
AVG(r.studentResult) '平均数',
 MAX(r.studentResult) '最高分', 
 MIN(r.studentResult) '最低分',
 s.subjectno AS 姓名
FROM result AS r INNER JOIN `subject` AS s ON r.subjectno=s.subjectno
GROUP BY  r.subjectno HAVING AVG(r.studentResult)>=80;
 
 
 
索引
==========================================================================
作用：
提高查询速度
确保数据唯一性
可以加速表和表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据检索可以显著减少分组和排序时间
全文检索字段进行收索优化
 
# 主键索引primary key 
（只能有一个）
#唯一索引 unique  
（避免重复 ，可以有多个）
 
#常规索引index/key
注意：1.用于快速定位
    2.加在查找条件的列
    3.不宜添加太多常规索引，影响数据插入，修改，删除
#全文索引fulltext
注意：1.fulltext索引只能用于MyISASM表类型；
      2.只能用于char ，varchar，text 数据类型列；
    3.适合大型数据集
 
 
 
添加 primary key 默认这一列添加索引
例：
  CREATE TABLE t03(
id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`name` NVARCHAR(10) NOT NULL unique（唯一索引） 
  或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）
 
);
索引2（常规，唯一）创建
=========================================================================
  CREATE TABLE t03(
id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`name` NVARCHAR(10) NOT NULL unique（唯一索引） 
  或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）
`sex` NVARCHAR(2) INDEX/KEY(常规索引)一般常作为条件的时候添加索引
  或 INDEX index_sex ('sex')
  或 KEY key_sex('sex1','sex2')一次添加两个（谁的信息量小谁在前） 
);
#创建表完成后创建索引
ALTER TABLE 表名 ADD INDEX inde_sex('sex1','sex2');
 
全文索引创建
=========================================================================
CREATE TABLE t03(
`name` VARCHAR(10) FULLTEXT，
`sex` text(10)
)ENGINE = MYISAM;
 
#或
ALTER TABLE t03 ADD FULLTEXT ('sex');
 
ALTER TABLE t03 ADD `sex` TEXT(100)
 
注意：
  select * from t03 where match(列名) against（‘love’）；
    1.添加索引后统计出现的（love）次数；
    2.使用关键字against
 
管理索引
==============================================================
 
#创建表索引
ALTER TABLE table_name ADD 索引类型 （‘列名’）；
#删除索引
1. DROP INDEX 索引名 ON 表名
2. ALTER TABLE 表名 DROP INDEX 索引名
3. ALTER TABLE 表名 DROP PRIMARY KEY;
 
#查看索引
SHOW INDEX  FROM 表名
 
mysql数据备份（DOS窗口下）
================================================================
备份方法：
  1.使用命令
  2.使用管理工具sqlyog
  3.直接拷贝数据文件和相关配置文件
 
mysql dump客户端
语法：
mysqldump -h主机名 -u用户名 -p数据库名 [表1 表2 表n] >path/filename.sql
例：
mysqldump -uroot -pt04 student（数据库名） student（表名） > f:/sql.sql  回车
 
mysql恢复
==============================================================
1.是mysql下
  关键字 source
    source /path/sql.sql
2.DOS下
  mysql -u用户名 -p密码 数据库名 表名 </path/sql.sql;
 
 
 
触发器
=============================================================
 
 
创建视图
===============================================================
view_lottert:视图的名
db_lottery：数据库中的表名
查询视图跟普通查询一样
视图一般做查询用
CREATE VIEW view_lottert AS SELECT * FROM db_lottery LIMIT 0,3
 
 
 
 
复制表结构
=============================================================
 
 
存储过程(看做函数或sql的集合)
================================================================
 
 
 
索引
==========================================================================
作用：
提高查询速度
确保数据唯一性
可以加速表和表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据检索可以显著减少分组和排序时间
全文检索字段进行收索优化
 
# 主键索引primary key 
（只能有一个）
#唯一索引 unique  
（避免重复 ，可以有多个）
 
#常规索引index/key
注意：1.用于快速定位
    2.加在查找条件的列
    3.不宜添加太多常规索引，影响数据插入，修改，删除
#全文索引fulltext
注意：1.fulltext索引只能用于MyISASM表类型；
      2.只能用于char ，varchar，text 数据类型列；
    3.适合大型数据集
 
 
 
添加 primary key 默认这一列添加索引
例：
  CREATE TABLE t03(
id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`name` NVARCHAR(10) NOT NULL unique（唯一索引） 
  或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）
 
);
索引2（常规，唯一）创建
=========================================================================
  CREATE TABLE t03(
id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`name` NVARCHAR(10) NOT NULL unique（唯一索引） 
  或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）
`sex` NVARCHAR(2) INDEX/KEY(常规索引)一般常作为条件的时候添加索引
  或 INDEX index_sex ('sex')
  或 KEY key_sex('sex1','sex2')一次添加两个（谁的信息量小谁在前） 
);
#创建表完成后创建索引
ALTER TABLE 表名 ADD INDEX inde_sex('sex1','sex2');
 
全文索引创建
=========================================================================
CREATE TABLE t03(
`name` VARCHAR(10) FULLTEXT，
`sex` text(10)
)ENGINE = MYISAM;
 
#或
ALTER TABLE t03 ADD FULLTEXT ('sex');
 
ALTER TABLE t03 ADD `sex` TEXT(100)
 
注意：
  select * from t03 where match(列名) against（‘love’）；
    1.添加索引后统计出现的（love）次数；
    2.使用关键字against
 
管理索引
==============================================================
 
#创建表索引
ALTER TABLE table_name ADD 索引类型 （‘列名’）；
#删除索引
1. DROP INDEX 索引名 ON 表名
2. ALTER TABLE 表名 DROP INDEX 索引名
3. ALTER TABLE 表名 DROP PRIMARY KEY;
 
#查看索引
SHOW INDEX  FROM 表名
 
mysql数据备份（DOS窗口下）
================================================================
备份方法：
  1.使用命令
  2.使用管理工具sqlyog
  3.直接拷贝数据文件和相关配置文件
 
mysql dump客户端
语法：
mysqldump -h主机名 -u用户名 -p数据库名 [表1 表2 表n] >path/filename.sql
例：
mysqldump -uroot -pt04 student（数据库名） student（表名） > f:/sql.sql  回车
 
mysql恢复
==============================================================
1.是mysql下
  关键字 source
    source /path/sql.sql
2.DOS下
  mysql -u用户名 -p密码 数据库名 表名 </path/sql.sql;