MySQL基础
基于尚硅谷MySQL视频的学习笔记
1. 初识MySQL
1.1 数据库相关概念
数据库的常见概念 ★
1、DB:数据库,存储数据的容器
2、DBMS:数据库管理系统,又称为数据库软件或数据库产品,用于创建或管理DB
3、SQL:结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有的主流数据库软件通用的语言
数据库存储数据的特点
1、数据存放到表中,然后表再放到库中
2、一个库中可以有多张表,每张表具有唯一的表名用来标识自己
3、表中有一个或多个列,列又称为“字段”,相当于java中“属性”
4、表中的每一行数据,相当于java中“对象”
常见的数据库管理系统
mysql、oracle、db2、sqlserver
1.2 MySQL介绍
MySQL的背景
前身属于瑞典的一家公司,MySQL AB
08年被sun公司收购
09年sun被oracle收购
MySQL的优点
开源、免费、成本低
性能高、移植性也好
体积小,便于安装
MySQL服务的启动和停止
方式一:通过命令行
1. net start 服务名
2. net stop 服务名
方式二:计算机——右击——管理——服务
MySQL服务的登录和退出
1.登录:
mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码
2.退出:
exit 或 ctrl+C
MySQL的常见命令
1. 1 # 1.查看当前所有的数据库
2. show databases;
3. # 2.打开指定的库
4. use 库名
5. # 3.查看当前库的所有表
6. show tables;
7. # 4.查看其它库的所有表
8. show tables from 库名;
9. # 5.创建表
10. create table 表名(
11. 列名 列类型,
12. 列名 列类型,
13. ...
14. );
15. # 6.查看表结构
16. desc 表名;
17. # 7.查看服务器的版本
18. # 方式一:登录到mysql服务端
19. select version();
20. # 方式二:没有登录到mysql服务端
21. mysql --version
22. # 或
23. mysql --V
MySQL的语法规范
-
不区分大小写,但建议关键字大写,表名、列名小写
-
每条命令最好用分号结尾
-
每条命令根据需要,可以进行缩进 或换行
注释
单行注释:#注释文字
单行注释:– 注释文字
多行注释:/* 注释文字 */
SQL的语言分类
1. DQL(Data Query Language):数据查询语言
2. select
3. DML(Data Manipulate Language):数据操作语言
4. insert 、update、delete
5. DDL(Data Define Languge):数据定义语言
6. create、drop、alter
7. TCL(Transaction Control Language):事务控制语言
8. commit、rollback
2. DQL语言
2.1 基础查询
1. # 1、查询单个字段
2. select 字段名 from 表名;
4. # 2、查询多个字段
5. select 字段名,字段名 from 表名;
7. # 3、查询所有字段
8. select * from 表名;
10. # 4、查询常量
11. # 注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要
12. select 常量值;
14. # 5、查询函数
15. select 函数名(实参列表);
17. # 6、查询表达式
18. select 100/1234;
20. # 7、起别名
21. # ① as
22. # ② 空格
24. # 8、去重
25. select distinct 字段名 from 表名;
27. # 9、+
28. # 作用:做加法运算
29. select 数值+数值; # 直接运算
30. select 字符+数值; # 先试图将字符转换成数值,如果转换成功,则继续运算;否则转换成0,再做运算
31. select null+值; # 结果都为null
33. # 10、【补充】concat函数
34. # 功能:拼接字符
35. select concat(字符1,字符2,字符3,...);
37. # 11、【补充】ifnull函数
38. # 功能:判断某字段或表达式是否为null,如果为null 返回指定的值,否则返回原本的值
39. select ifnull(commission_pct,0) from employees;
41. # 12、【补充】isnull函数
42. # 功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0
2.2 条件查询
1. # 1.条件查询的基本结构
2. select 查询列表 from 表名 where 筛选条件
4. # 2.简单条件运算符
5. > < = <> != >= <=
6. <=>安全等于 # 可以判断null值
8. # 3. 逻辑运算符
9. && and
10. || or
11. ! not
13. # 4、模糊查询
14. between and # 左右边界不能颠倒顺序
15. between 120 and 100 # 等价于 >= 120 && <= 100
17. in ('', '') # in列表值类型必须统一,且不支持通配符
18. is null /is not null # 用于判断null值, = 不能判断null
19. like # 一般搭配通配符使用,可以判断字符型或数值型
21. # 5.通配符:
22. %:任意多个字符
23. _:任意单个字符
24. 通配符转义 \ 或者 '_$_' ESCAPE '$'
27. 普通类型的数值 null值 可读性
28. is null × √ √
29. <=> √ √ ×
经典面试题 :
1. select * from employees;
2. select * from employees where name like '%%' and id like '%%';
3. 两个查询语句的执行结果是否相同?
4. 解:不一样,若name或者id有null值则查不出来
2.3 排序查询
1. # 1. 排序查询的基本结构
2. select 查询列表
3. from 表
4. where 筛选条件
5. order by 排序列表 [asc}desc]
7. # 2. 特点
8. /*
9. 1、asc :升序,如果不写默认升序
10. desc:降序
11. 2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
12. 3、order by的位置一般放在查询语句的最后(除limit语句之外)
13. */
2.4 常见函数
- 功能:类似于java中的方法
- 好处:提高重用性和隐藏实现细节
- 调用:select 函数名(实参列表);
- 单行函数
1. # 1、字符函数
2. # concat:连接
3. # substr:截取子串,索引从1开始
4. # upper:变大写
5. # lower:变小写
6. # replace:替换
7. # length:获取字节长度
8. # trim:去前后空格
9. # lpad:左填充
10. # rpad:右填充
11. # instr:获取子串第一次出现的索引
13. SELECT LENGTH('john');
14. SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
15. SELECT UPPER('john');
16. SELECT LOWER('joHn');
17. SELECT
18. CONCAT(UPPER(last_name),LOWER(first_name)) 姓名
19. FROM employees;
20. SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
21. SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
22. SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put
23. FROM employees;
24. SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
25. SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
26. SELECT TRIM('aa' FROM 'aaaaaa张aaaa翠山aaaaa') AS out_put;
27. SELECT LPAD('殷素素',2,'*') AS out_put;
28. SELECT RPAD('殷素素',12,'ab') AS out_put;
29. SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
31. # 2、数学函数
32. # ceil:向上取整
33. # round:四舍五入
34. # mod:取模
35. # floor:向下取整
36. # truncate:截断
37. # rand:获取随机数,返回0-1之间的小数
38. SELECT ROUND(-1.55);
39. SELECT ROUND(1.567,2);
40. SELECT CEIL(-1.02);
41. SELECT FLOOR(-9.99);
42. SELECT TRUNCATE(1.69999,1);
43. #mod取余
44. /*
45. mod(a,b) : a-a/b*b
46. mod(-10,-3):-10- (-10)/(-3)*(-3)=-1
47. */
48. SELECT MOD(10,-3);
49. SELECT 10%3;
51. # 3、日期函数
52. # now:返回当前日期+时间
53. # year:返回年
54. # month:返回月
55. # day:返回日
56. # date_format:将日期转换成字符
57. # curdate:返回当前日期
58. # str_to_date:将字符转换成日期
59. # curtime:返回当前时间
60. # hour:小时
61. # minute:分钟
62. # second:秒
63. # datediff:返回两个日期相差的天数
64. # monthname:以英文形式返回月
65. SELECT NOW();
66. SELECT CURDATE();
67. SELECT CURTIME();
68. SELECT YEAR(NOW()) 年;
69. SELECT YEAR('1998-1-1') 年;
70. SELECT YEAR(hiredate) 年 FROM employees;
71. SELECT MONTH(NOW()) 月;
72. SELECT MONTHNAME(NOW()) 月;
73. SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
74. #查询入职日期为1992--4-3的员工信息
75. SELECT * FROM employees WHERE hiredate = '1992-4-3';
76. SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
77. SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
78. #查询有奖金的员工名和入职日期(xx月/xx日 xx年)
79. SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
80. FROM employees
81. WHERE commission_pct IS NOT NULL;
83. # 4、其他函数
84. # version 当前数据库服务器的版本
85. # database 当前打开的数据库
86. # user当前用户
87. # password('字符'):返回该字符的密码形式
88. # md5('字符'):返回该字符的md5加密形式
89. SELECT VERSION();
90. SELECT DATABASE();
91. SELECT USER();
93. #5、流程控制函数
94. /*
95. ①if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
96. ②case情况1
97. case 变量或表达式或字段
98. when 常量1 then 值1
99. when 常量2 then 值2
100. ...
101. else 值n
102. end
104. ③case情况2
105. case
106. when 条件1 then 值1
107. when 条件2 then 值2
108. ...
109. else 值n
110. end
111. */
112. SELECT IF(10<5,'大','小');
114. SELECT salary 原始工资,department_id,
115. CASE department_id
116. WHEN 30 THEN salary*1.1
117. WHEN 40 THEN salary*1.2
118. WHEN 50 THEN salary*1.3
119. ELSE salary
120. END AS 新工资
121. FROM employees;
123. SELECT salary,
124. CASE
125. WHEN salary>20000 THEN 'A'
126. WHEN salary>15000 THEN 'B'
127. WHEN salary>10000 THEN 'C'
128. ELSE 'D'
129. END AS 工资级别
130. FROM employees;
分组函数(统计函数\聚合函数)
1. # 1.分类
2. max 最大值
3. min 最小值
4. sum 和
5. avg 平均值
6. count 计算个数
8. # 2.简单 的使用
9. SELECT SUM(salary) FROM employees;
10. SELECT AVG(salary) FROM employees;
11. SELECT MIN(salary) FROM employees;
12. SELECT MAX(salary) FROM employees;
13. SELECT COUNT(salary) FROM employees;
16. SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
17. FROM employees;
19. SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
20. FROM employees;
22. # 3、参数支持哪些类型
23. # sum和avg一般用于处理数值型
24. # max、min、count可以处理任何数据类型
25. SELECT SUM(last_name) ,AVG(last_name) FROM employees;
26. SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;
27. SELECT MAX(last_name),MIN(last_name) FROM employees;
28. SELECT MAX(hiredate),MIN(hiredate) FROM employees;
29. SELECT COUNT(commission_pct) FROM employees;
30. SELECT COUNT(last_name) FROM employees;
32. # 4、是否忽略null(以上分组函数都忽略)
33. SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
35. SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;
37. SELECT COUNT(commission_pct) FROM employees;
38. SELECT commission_pct FROM employees;
40. # 5、和distinct搭配
41. SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
42. SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
44. # 6、count函数的详细介绍
45. count(字段):统计该字段非空值的个数
46. count(*):统计结果集的行数
47. SELECT COUNT(salary) FROM employees;
48. SELECT COUNT(*) FROM employees;
49. SELECT COUNT(1) FROM employees;
51. 效率:
52. MYISAM存储引擎下,COUNT(*)的效率高
53. INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些
55. #6、和分组函数一同查询的字段有限制,要求是group by后出现的字段
56. SELECT AVG(salary),employee_id FROM employees; # 这个查询没有意义
2.5 分组查询
1. # 1.分组查询的基本结构
2. select 分组函数,分组后的字段
3. from 表
4. [where 筛选条件]
5. group by 分组的字段
6. [having 分组后的筛选]
7. [order by 排序列表]
9. # 2.筛选对比
10. 使用关键字 筛选的表 位置
11. 分组前筛选 where 原始表 group by的前面
12. 分组后筛选 having 分组后的结果 group by 的后面
14. # 3. 常见问题
15. 1:分组函数做筛选能不能放在where后面
16. 答:不能
18. 2:where——group by——having
19. 答:一般来讲,能用分组前筛选的,尽量使用分组前筛选,提高效率
21. 3、分组可以按单个字段也可以按多个字段
22. 4、可以搭配着排序使用
24. # 4. 一些示例
25. #案例1:查询每个工种的员工平均工资
26. SELECT AVG(salary),job_id
27. FROM employees
28. GROUP BY job_id;
29. #案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
30. SELECT job_id,MAX(salary)
31. FROM employees
32. WHERE commission_pct IS NOT NULL
33. GROUP BY job_id
34. HAVING MAX(salary)>12000;
35. #案例3:每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
36. SELECT job_id,MAX(salary) m
37. FROM employees
38. WHERE commission_pct IS NOT NULL
39. GROUP BY job_id
40. HAVING m>6000
41. ORDER BY m ;
42. #案例4:查询每个工种每个部门的最低工资,并按最低工资降序
43. SELECT MIN(salary),job_id,department_id
44. FROM employees
45. GROUP BY department_id,job_id
46. ORDER BY MIN(salary) DESC;
2.6 连接查询
1. # 1.连接查询的含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
2. 笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行
3. 发生原因:没有有效的连接条件
4. 如何避免:添加有效的连接条件
6. # 2.分类:
7. # 按年代分类:
8. sql92标准:仅仅支持内连接
9. sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接
10.
11. # 按功能分类:
12. 内连接:
13. 等值连接
14. 非等值连接
15. 自连接
16. 外连接:
17. 左外连接
18. 右外连接
19. 全外连接
20.
21. 交叉连接
23. # 3. SQL92语法
24. # 等值连接
25. select 查询列表
26. from 表1 别名,表2 别名
27. where 表1.key=表2.key
28. 【and 筛选条件】
29. 【group by 分组字段】
30. 【having 分组后的筛选】
31. 【order by 排序字段】
33. ① 一般为表起别名
34. ② 多表的顺序可以调换
35. ③ n表连接至少需要n-1个连接条件
36. ④ 等值连接的结果是多表的交集部分
39. # 非等值连接
40. select 查询列表
41. from 表1 别名,表2 别名
42. where 非等值的连接条件
43. 【and 筛选条件】
44. 【group by 分组字段】
45. 【having 分组后的筛选】
46. 【order by 排序字段】
48. # 自连接
49. select 查询列表
50. from 表 别名1,表 别名2
51. where 等值的连接条件
52. 【and 筛选条件】
53. 【group by 分组字段】
54. 【having 分组后的筛选】
55. 【order by 排序字段】
58. # 4.SQL99语法
59. # 内连接
60. - 语法:
61. select 查询列表
62. from 表1 别名
63. 【inner】 join 表2 别名 on 连接条件
64. where 筛选条件
65. group by 分组列表
66. having 分组后的筛选
67. order by 排序列表
68. limit 子句;
69. - 特点:
70. ① 表的顺序可以调换
71. ② 内连接的结果=多表的交集
72. ③ n表连接至少需要n-1个连接条件
73. - 分类:
74. 等值连接
75. 非等值连接
76. 自连接
78. # 外连接
79. - 语法:
80. select 查询列表
81. from 表1 别名
82. left|right|full【outer】 join 表2 别名 on 连接条件
83. where 筛选条件
84. group by 分组列表
85. having 分组后的筛选
86. order by 排序列表
87. limit 子句;
88. - 特点:
89. ① 查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
90. ② left join 左边的就是主表
91. right join 右边的就是主表
92. full join 两边都是主表
93. ③ 一般用于查询除了交集部分的剩余的不匹配的行
95. # 交叉连接
96. - 语法:
97. select 查询列表
98. from 表1 别名
99. cross join 表2 别名;
100. - 特点:
101. 类似于笛卡尔乘积
2.7 子查询
1. # 1. 子查询的含义
2. 出现在其他语句中的select语句,称为子查询或内查询
3. 外部的查询语句,称为主查询或外查询
5. # 2. 分类:
6. # 按子查询出现的位置:
7. select后面:
8. 仅仅支持标量子查询
10. from后面:
11. 支持表子查询
12. where或having后面:★
13. 标量子查询(单行) √
14. 列子查询 (多行) √
15. 行子查询
17. exists后面(相关子查询)
18. 表子查询
20. # 按结果集的行列数不同:
21. 标量子查询(结果集只有一行一列)
22. 列子查询(结果集只有一列多行)
23. 行子查询(结果集有一行多列)
24. 表子查询(结果集一般为多行多列)
26. # 3. 示例
27. # 3.1 where或having后面
28. 特点:
29. ① 子查询放在小括号内
30. ② 子查询一般放在条件的右侧
31. ③ 标量子查询,一般搭配着单行操作符使用
32. > < >= <= = <>
33. 列子查询,一般搭配着多行操作符使用
34. in、any/some、all
36. ④ 子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
38. # 标量子查询
39. 案例:查询最低工资的员工姓名和工资
40. ① 最低工资
41. select min(salary) from employees
42. ② 查询员工的姓名和工资,要求工资=①
43. select last_name,salary
44. from employees
45. where salary=(
46. select min(salary) from employees
47. );
49. # 列子查询 ★
50. 案例:查询所有是领导的员工姓名
51. ① 查询所有员工的 manager_id
52. select manager_id
53. from employees
54. ② 查询姓名,employee_id属于①列表的一个
55. select last_name
56. from employees
57. where employee_id in(
58. select manager_id
59. from employees
60. );
61.
62. # 行子查询(了解)
63. 案例:查询员工编号最小并且工资最高的员工信息
64. SELECT *
65. FROM employees
66. WHERE (employee_id,salary)=(
67. SELECT MIN(employee_id),MAX(salary)
68. FROM employees
69. );
70. ① 查询最小的员工编号
71. SELECT MIN(employee_id)
72. FROM employees
73. ② 查询最高工资
74. SELECT MAX(salary)
75. FROM employees
76. ③ 查询员工信息
77. SELECT *
78. FROM employees
79. WHERE employee_id=(
80. SELECT MIN(employee_id)
81. FROM employees
82. )AND salary=(
83. SELECT MAX(salary)
84. FROM employees
85. );
86.
87. # 3.2 select后面
88. 仅仅支持标量子查询
89. #案例:查询每个部门的员工个数
90. SELECT d.*,(
91. SELECT COUNT(*)
92. FROM employees e
93. WHERE e.department_id = d.`department_id`
94. ) 个数
95. FROM departments d;
98. # 3.3 from后面
99. 将子查询结果充当一张表,要求必须起别名
100. #案例:查询每个部门的平均工资的工资等级
101. ① 查询每个部门的平均工资
102. SELECT AVG(salary),department_id
103. FROM employees
104. GROUP BY department_id
105. SELECT * FROM job_grades;
107. ② 连接①的结果集和job_grades表,筛选条件平均工资 between lowest_sal and highest_sal
108. SELECT ag_dep.*,g.`grade_level`
109. FROM (
110. SELECT AVG(salary) ag,department_id
111. FROM employees
112. GROUP BY department_id
113. ) ag_dep
114. INNER JOIN job_grades g
115. ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
118. # 3.4 exists后面(相关子查询)
119. 语法:
120. exists(完整的查询语句)
121. 结果:
122. 1或0
124. SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);
126. #案例:查询有员工的部门名
127. #in
128. SELECT department_name
129. FROM departments d
130. WHERE d.`department_id` IN(
131. SELECT department_id
132. FROM employees
133. )
134. #exists
135. SELECT department_name
136. FROM departments d
137. WHERE EXISTS(
138. SELECT *
139. FROM employees e
140. WHERE d.`department_id`=e.`department_id`
141. );
2.8 分页查询
1. # 1. 分页查询
2. select 查询列表
3. from 表
4. [join type join 表2
5. on 连接条件
6. where 筛选条件
7. group by 分组字段
8. having 分组后的筛选
9. order by 排序的字段]
10. limit [offset,] size;
12. # 2. 注意
13. offset代表的是起始的条目索引,默认从0开始
14. size代表的是显示的条目数
16. # 3. 公式
17. 假如要显示的页数为page,每一页条目数为size
18. select 查询列表
19. from 表
20. limit (page-1)*size,size;
2.9 联合查询
1. # 1. 联合查询: 将多条查询语句的结果合并成一个结果
2. 查询语句1
3. union
4. 查询语句2
5. union
6. ...
8. # 2. 应用场景:
9. 要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
11. # 3. 特点:★
12. 3.1 要求多条查询语句的查询列数是一致的!
13. 3.2 要求多条查询语句的查询的每一列的类型和顺序最好一致
14. 3.3 union关键字默认去重,如果使用union all 可以包含重复项
16. # 4.案例:查询部门编号>90或邮箱包含a的员工信息
17. SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;;
18. SELECT * FROM employees WHERE email LIKE '%a%'
19. UNION
20. SELECT * FROM employees WHERE department_id>90;
2.10 查询语句总结
1. # 语法以及执行顺序
2. select 查询列表 ⑦
3. from 表1 别名 ①
4. 连接类型 join 表2 ②
5. on 连接条件 ③
6. where 筛选 ④
7. group by 分组列表 ⑤
8. having 筛选 ⑥
9. order by排序列表 ⑧
10. limit 起始条目索引,条目数; ⑨
11. 3. DML语言
12. 数据操作语言:
14. 插入:insert
16. 修改:update
18. 删除:delete
3.1 插入语句
方式一:
# 语法
insert into 表名(列名) values(值1,...);
# 1.插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
# 2.不可以为null的列必须插入值。可以为null的列如何插入值?
# 2.1:
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
# 2.2:
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','1388888888');
# 3.列的顺序是否可以调换
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蒋欣','女',16,'110');
# 4.列数和值的个数必须一致
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');
# 5.可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);
方式二:
# 语法:
insert into 表名
set 列名=值,列名=值,...
# 示例
INSERT INTO beauty
SET id=19, NAME='刘涛', phone='999';
对比两种方式:
1. # 1、方式一支持插入多行,方式二不支持
2. INSERT INTO beauty
3. VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
4. ,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
5. ,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);
7. # 2、方式一支持子查询,方式二不支持
8. INSERT INTO beauty(id,NAME,phone)
9. SELECT 26,'宋茜','11809866';
11. INSERT INTO beauty(id,NAME,phone)
12. SELECT id,boyname,'1234567'
13. FROM boys WHERE id<3;
3.2 修改语句
语法:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1.修改单表的记录★
update 表名
set 列=新值,列=新值,...
where 筛选条件;
2.修改多表的记录【补充】
sql92语法:
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;
sql99语法:
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
1.修改单表的记录
案例1:修改beauty表中姓唐的女神的电话为13899888899
UPDATE beauty SET phone = '13899888899'
WHERE NAME LIKE '唐%';
案例2:修改boys表中id好为2的名称为张飞,魅力值 10
UPDATE boys SET boyname='张飞',usercp=10
WHERE id=2;
2.修改多表的记录
案例 1:修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b ON bo.id
=b.boyfriend_id
SET b.phone
='119',bo.userCP
=1000
WHERE bo.boyName
='张无忌';
案例2:修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b ON bo.id
=b.boyfriend_id
SET b.boyfriend_id
=2
WHERE bo.id
IS NULL;
SELECT * FROM boys;
3.3 删除语句
方式一:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
1、单表的删除【★】
delete from 表名 where 筛选条件
2、多表的删除【补充】
sql92语法:
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;
sql99语法:
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;
示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1.单表的删除
案例:删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
2.多表的删除
案例:删除张无忌的女朋友的信息
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id
= bo.id
WHERE bo.boyName
='张无忌';
案例:删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id
=bo.id
WHERE bo.boyName
='黄晓明';
方式二:
1
2
语法:
truncate table 表名;
示例:
1
2
案例:将魅力值>100的男神信息删除
TRUNCATE TABLE boys ;
delete 对比 truncate [面试题]
delete 可以加where 条件,truncate不能加
truncate删除,效率高一丢丢
假如要删除的表中有自增长列,如果用delete删除后,再插入数据,自增长列的值从断点开始,而truncate删除后,再插入数据,自增长列的值从1开始。
truncate删除没有返回值,delete删除有返回值
truncate删除不能回滚,delete删除可以回滚.
- DDL语言
数据定义语言 –> 库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除
创建: create
修改: alter
删除: drop
4.1 库的管理
1
2
3
4
5
6
7
8
9
10
11
12
1、库的创建
create database [if not exists]库名;
案例:创建库Books
CREATE DATABASE IF NOT EXISTS books ;
2、库的修改
RENAME DATABASE books TO 新库名;
更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
3、库的删除
DROP DATABASE IF EXISTS books;
4.2 表的管理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
1.表的创建 ★
语法:
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
案例:创建表Book
CREATE TABLE book(
id INT,#编号
bName VARCHAR(20),#图书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期
);
DESC book;
案例:创建表author
CREATE TABLE IF NOT EXISTS author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
)
DESC author;
2.表的修改
语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
① 修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
② 修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
③ 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
④ 删除列
ALTER TABLE book_author DROP COLUMN annual;
⑤ 修改表名
ALTER TABLE author RENAME TO book_author;
3.表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
通用的写法:
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
4.表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
SELECT * FROM Author;
SELECT * FROM copy2;
1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;
4.3 数据类型
常见的数据类型
数值型:
整型
小数:
定点数
浮点数
字符型:
较短的文本:char、varchar
较长的文本:text、blob(较长的二进制数据)
日期型
整型
分类:
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
特点:
① 如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常,并且插入临界值
③ 如果不设置长度,会有默认的长度
长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
t1 INT(7) ZEROFILL,
t2 INT(7) ZEROFILL,
t3 INT UNSIGNED
);
DESC tab_int;
INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(2147483648,4294967296);
INSERT INTO tab_int VALUES(123,123);
SELECT * FROM tab_int;
小数
分类:
浮点型
float(M,D)
double(M,D)
定点型
dec(M,D)
decimal(M,D)
特点:
①
M:整数部位+小数部位
D:小数部位
如果超过范围,则插入临界值
②
M和D都可以省略
如果是decimal,则M默认为10,D默认为0
如果是float和double,则会根据插入的数值的精度来决定精度
③
定点型的精确度较高,如果要求插入数值的精度较高如货币运算等则考虑使用
原则:
所选择的类型越简单越好,能保存数值的类型越小越好
1
2
3
4
5
6
7
8
9
10
11
12
13
测试M和D
CREATE TABLE tab_float(
f1 FLOAT,
f2 DOUBLE,
f3 DECIMAL
);
SELECT * FROM tab_float;
DESC tab_float;
INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);
INSERT INTO tab_float VALUES(123.456,123.456,123.456);
INSERT INTO tab_float VALUES(123.4,123.4,123.4);
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);
字符型
较短的文本:
char varchar
其他:
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本:
text
blob(较大的二进制)
char/varchar 对比
写法 M的意思 特点 空间的耗费 效率
char char(M) 最大的字符数,可以省略,默认为1 固定长度的字符 比较耗费 高
varchar varchar(M) 最大的字符数,不可以省略 可变长度的字符 比较节省 低
enum和set不区分大小写
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE tab_char(
c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');
INSERT INTO tab_char VALUES('A');
SELECT * FROM tab_char;
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('a,c,d');
日期型
分类:
date只保存日期
time 只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间
写法 范围 时区等的影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038 受
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
查看当前时区
SHOW VARIABLES LIKE 'time_zone';
更改时区
SET time_zone='+9:00';
4.4 常见约束
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类:六大约束
NOT NULL:非空,用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认,用于保证该字段有默认值
比如性别
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空
比如学号、员工编号等
UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束,用于引用主表中某列的值
比如学生表的专业编号,员工表的部门编号,员工表的工种编号
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
列级约束:
六大约束语法上都支持,但外键约束没有效果
表级约束:
除了非空、默认,其他的都支持
1
2
3
4
5
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)
主键和唯一的对比【面试题】
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √,但不推荐
唯一 √ √ 可以有多个 √,但不推荐
外键
要求在从表设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
主表的关联列必须是一个key(一般是主键或唯一)
插入数据时,先插入主表,再插入从表;删除数据时,先删除从表,再删除主表
1
2
3
4
5
可以通过以下两种方式来删除主表的记录
方式一:级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
方式二:级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
约束的增删改
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
一、创建表时添加约束
1.添加列级约束
/*
语法:
直接在字段名和类型后面追加 约束类型即可。
只支持:默认、非空、主键、唯一
*/
USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL UNIQUE,#非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)#外键(这里外键无效)
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
查看stuinfo中的所有索引,包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
2.添加表级约束
/*
语法:在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
SHOW INDEX FROM stuinfo;
通用的写法:★
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
二、修改表时添加约束
/*
- 添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束; - 添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
)
DESC stuinfo;
1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
3.添加主键
①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
4.添加唯一
①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
三、修改表时删除约束
1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
SHOW INDEX FROM stuinfo;
自增长列(标识列)
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
1、标识列必须和主键搭配吗?不一定,但要求是一个key
2、一个表可以有几个标识列?至多一个!
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值,设置起始值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
1.创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
id INT,
NAME FLOAT UNIQUE AUTO_INCREMENT,
seat INT
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
INSERT INTO tab_identity(NAME) VALUES('lucy');
SELECT * FROM tab_identity;
查看自增长相关变量
SHOW VARIABLES LIKE '%auto_increment%';
设置自增长步长(一般要进行修改)
SET auto_increment_increment=3;
2.修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
3.修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
4.5 表与表之间的关系
一对一
一对多
多对多
5. TCL语言
Transaction Control Language 事务控制语言
5.1 事务的含义
事务:
一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
5.2 事务的特点
事务的特性:ACID [面试题]
原子性:一个事务不可再分割,要么都执行要么都不执行.
一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态.
隔离性:一个事务的执行不受其他事务的干扰.
持久性:一个事务一旦提交,则会永久的改变数据库的数据.
存储引擎:
概念: 在mysql中的数据用各种不同的技术存储在文件(或者内存)中.
通过show engines;来查看mysql支持的存储引擎.
在mysql中用的最多的存储引擎有: innodb, myisam, memory等. 其中innodb支持事务,而myisam和memory不支持事务.
5.3 事务的使用
事务的创建
隐式事务:事务没有明显的开启和结束的标记
比如insert、update、delete语句
delete from 表 where id =1;
显式事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用set autocommit=0;
显式事务的使用
① 开启事务
set autocommit=0;
start transaction;#可以省略
② 编写一组逻辑sql语句
注意:sql语句支持的是insert、update、delete
设置回滚点:
savepoint 回滚点名;
③ 结束事务
提交:commit;
回滚:rollback;
回滚到指定的地方:rollback to 回滚点名;
delete和truncate在事务使用时的区别:
delete支持回滚
truncate不支持
5.4 并发事务
事务的并发问题是如何发生的?
多个事务 同时 操作 同一个数据库的相同数据时
并发问题都有哪些?
脏读:一个事务读取了其他事务还没有提交的数据,读到的是其他事务“更新”的数据
不可重复读:一个事务多次读取,结果不一样
幻读:一个事务读取了其他事务还没有提交的数据,只是读到的是其他事务“插入”的数据
更新丢失:当两个或者多个事务选择同一行,然后基于最初的选定的值更新该行时,由于每一个事务都不知道其他事务的存在,就会发生更新问题。最后的更新覆盖了其他事务所做的更新。
如何解决并发问题
通过设置隔离级别来解决并发问题
事务的隔离级别
读数据一致性 脏读 不可重复读 幻读
read uncommitted: 读未提交 最低级别,只能保证不读取物理上损坏的数据 × × ×
read committed: 读已提交 语句级 √ × ×
repeatable read: 可重复读 事务级 √ √ ×
serializable: 串行化 (每次读都需获得表级共享锁,读写相互都会阻塞,性能低下) 最高级别,事务级 √ √ √
1
2
3
4
5
6
7
8
9
mysql中默认 第三个隔离级别
repeatable read
oracle中默认第二个隔离级别
read committed
查看隔离级别
select @@tx_isolation;
show variables like 'tx_isolation';
设置隔离级别
set session|global transaction isolation level 隔离级别;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
1.演示事务的使用步骤
开启事务
SET autocommit=0;
START TRANSACTION;
编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
结束事务
ROLLBACK; # commit;
SELECT * FROM account;
2.演示事务对于delete和truncate的处理的区别
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
3.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;
6. 其他
6.1 视图
含义:虚拟表,和普通表一样使用
mysql5.1版本出现的新特性,是通过表动态生成的数据, 只保存了sql逻辑, 不保存查询结果
应用场景:
多个地方用到相同的查询结果
该查询结果使用的sql语句比较复杂
好处:
重用sql语句
简化复杂的sql操作,不必知道它的查询细节
保护数据,提高安全性(只提供对方需要的信息)
创建视图
1
2
3
4
5
6
7
8
9
10
11
12
13
14
示例:查询姓张的学生名和专业名
1.原始sql语句
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid
= m.id
WHERE s.stuname
LIKE '张%';
2.①创建视图
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid
= m.id
;
2.②使用视图简化原始sql语句
SELECT * FROM v1 WHERE stuname LIKE '张%';
修改视图
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
方式一:
/*
create or replace view 视图名
as
查询语句;
*/
SELECT * FROM myv3
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
方式二:
/*
语法:
alter view 视图名
as
查询语句;
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;
删除视图
1
2
语法:drop view 视图名,视图名,...;
DROP VIEW emp_v1,emp_v2,myv3;
查看视图
1
2
DESC myv3;
SHOW CREATE VIEW myv3;
更新视图
视图可以增删改,示例如下
1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
SELECT * FROM myv1;
SELECT * FROM employees;
1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';
具备以下特点的视图不允许更新
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
① 包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;
更新
UPDATE myv1 SET m=9000 WHERE department_id=10;
② 常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;
更新
UPDATE myv2 SET NAME='lucy';
③ Select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;
更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;
④ join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
更新
SELECT * FROM myv4;
UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');
⑤ from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
更新
SELECT * FROM myv5;
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
⑥ where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
表和视图的对比
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查,只是一般不能增删改
表 create table 保存了数据 增删改查
6.2 变量
系统变量:
全局变量:
服务器层面上的,必须拥有super权限才能为系统变量赋值,作用域为整个服务器,也就是针对于所有连接(会话)有效
会话变量:
服务器为每一个连接的客户端都提供了系统变量,作用域为当前的连接(会话)
自定义变量:
用户变量
局部变量
系统变量
说明:变量由系统定义,不是用户定义,属于服务器层面
注意:全局变量需要添加global关键字,会话变量需要添加session关键字,如果不写,默认会话级别
使用步骤:
1
2
3
4
5
6
7
8
9
10
11
1、查看所有系统变量
show global|【session】variables;
2、查看满足条件的部分系统变量
show global|【session】 variables like '%char%';
3、查看指定的系统变量的值
select @@global|【session】系统变量名;
4、为某个系统变量赋值
方式一:
set global|【session】系统变量名=值;
方式二:
set @@global|【session】系统变量名=值;
示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
1》全局变量
/*
作用域:针对于所有会话(连接)有效,但不能跨重启
*/
① 查看所有全局变量
SHOW GLOBAL VARIABLES;
② 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
③ 查看指定的系统变量的值
SELECT @@global.autocommit;
④ 为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
2》会话变量
/*
作用域:针对于当前会话(连接)有效
*/
① 查看所有会话变量
SHOW SESSION VARIABLES;
② 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
③ 查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
④ 为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
自定义变量
说明:变量由用户自定义,而不是系统提供的
使用步骤:
1、声明
2、赋值
3、使用(查看、比较、运算等)
示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
1》用户变量
/*
作用域:针对于当前会话(连接)有效,作用域同于会话变量
*/
赋值操作符:=或:=
① 声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
② 赋值(更新变量的值)
方式一:
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
方式二:
SELECT 字段 INTO @变量名
FROM 表;
③ 使用(查看变量的值)
SELECT @变量名;
2》局部变量
/*
作用域:仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
*/
① 声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】
② 赋值(更新变量的值)
方式一:
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;
方式二:
SELECT 字段 INTO 具备变量名
FROM 表;
③ 使用(查看变量的值)
SELECT 局部变量名;
案例:声明两个变量,求和并打印
用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
用户变量和局部变量的对比:
作用域 定义位置 语法
用户变量 当前会话 会话的任何地方 加@符号,不用指定类型
局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型
6.3 存储过程和函数
存储过程和函数:类似于java中的方法
好处:
1、提高代码的重用性
2、简化操作
存储过程
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
一、创建语法★
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
/*
1、参数列表包含三部分
参数模式 参数名 参数类型
举例:
in stuname varchar(20)
参数模式:
in:该参数可以作为输入,也就是该参数需要调用方传入值,in可以省略
out:该参数可以作为输出,也就是该参数可以作为返回值
inout:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值
2、如果存储过程体仅仅只有一句话,begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法:
delimiter 结束标记
案例:
delimiter $
*/
二、调用语法
CALL 存储过程名(实参列表);
----------------------案例演示-------------------------
1.空参列表
案例:插入到admin表中五条记录
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,password
)
VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
调用
CALL myp1()$
2.创建带in模式参数的存储过程
案例1:创建存储过程实现 根据女神名,查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END $
调用
CALL myp2('柳岩')$
案例2 :创建存储过程实现,用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化
SELECT COUNT(*) INTO result#赋值
FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;
SELECT IF(result>0,'成功','失败');#使用
END $
调用
CALL myp3('张飞','8888')$
3.创建out 模式参数的存储过程
案例1:根据输入的女神名,返回对应的男神名
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyname INTO boyname
FROM boys bo
RIGHT JOIN
beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName ;
END $
调用
CALL myp7('小昭', @name)$
SELECT @name$
案例2:根据输入的女神名,返回对应的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;
END $
调用
CALL myp7('小昭', @name, @cp)$
SELECT @name,@cp$
4.创建带inout模式参数的存储过程
案例1:传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a2;
SET b=b2;
END $
调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$
三、删除存储过程
语法:drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×
四、查看存储过程的信息
DESC myp2; # 不可以
SHOW CREATE PROCEDURE myp2;
函数
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1 个返回,适合做处理数据后返回一个结果
示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
/*
注意:
1.参数列表 包含两部分:
参数名 参数类型
2.函数体:
肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不报错,但不建议
return 值;
3.函数体中仅有一句话,则可以省略begin end
4.使用 delimiter语句设置结束标记
*/
二、调用语法
SELECT 函数名(参数列表)
-----------------案例演示-------------------
1.无参有返回
案例:返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义局部变量
SELECT COUNT(*) INTO c#赋值
FROM employees;
RETURN c;
END $
SELECT myf1()$
2.有参有返回
案例1:根据员工名,返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0;#定义用户变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name = empName;
RETURN @sal;
END $
SELECT myf2('k_ing') $
案例2:根据部门名,返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE ;
SELECT AVG(salary) INTO sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $
SELECT myf3('IT')$
三、查看函数
SHOW CREATE FUNCTION myf3;
四、删除函数
DROP FUNCTION myf3;
案例: 创建函数,实现传入两个float,返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $
SELECT test_fun1(1,2)$
6.4 流程控制结构
顺序、分支、循环
分支结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
1.if函数
/*
语法:if(条件,值1,值2)
功能:实现双分支
应用在begin end中或外面
*/
2.case结构
/*
语法:
情况1:类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end
情况2:
case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end
- 应用在begin end 中或外面
如果放在begin end 外面,作为表达式结合着其他语句使用
如果放在begin end 里面,一般作为独立的语句使用 - else语句可以省略,如果else省略了,全部when都不满足,则返回null
3.if结构
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能:类似于多重if
只能应用在begin end 中
循环结构
分类:
while、loop、repeat
循环控制:
iterate 【标签】 类似于 continue,继续,结束本次循环,继续下一次
leave 【标签】 类似于 break,跳出,结束当前所在的循环
语法:
示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
1.没有添加循环控制语句
案例:批量插入,根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username,password
) VALUES(CONCAT('Rose',i),'666');
SET i=i+1;
END WHILE;
END $
CALL pro_while1(100)$
2.添加leave语句
案例:批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,password
)
VALUES(CONCAT('xiaohua',i),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL test_while1(100)$
3.添加iterate语句
案例:批量插入,根据次数插入到admin表中多条记录,只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,`password`)
VALUES(CONCAT('xiaohua',i),'0000');
END WHILE a;
END $
CALL test_while1(100)$