sql常用命令/学习笔记(命令行模式)
目录
- sql常用命令/学习笔记(命令行模式)
- 0.数据库
- 1.mysql的介绍
- 2.基础环境变量配置
- 3.常见命令
- 4.语法规范
- 5.mysql图形化界面的安装和使用
- 6.DQL的学习(data query language)
- 7.DML的学习(data manipulation language )
- 8.DDL的学习(data define language)
- 9.TCL语言的学习(transaction control language)
- 10.视图
- 11.变量
- 12.存储过程和函数
- 13.流程控制结构
0.数据库
0.1数据库的好处
- 可以持久化数据到本地
- 结构化查询
0.2数据库常见概念
- DB:数据库,储存数据的容器;
- DBMS:数据库管理系统,又称为数据库软件或数据库产品,用于创建和管理DB;
- SQL:结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有主流数据库通用的语言
0.3数据库存储特点
- 数据存放到表中,表存放到库中
- 一个库可以有多张表,每张表哦于唯一的表名用来标示
- 表中有多个列(字段),相当于java中的属性
- 表中的每一行数据相当于java中的对象
0.4常见的数据库管理系统
- mysql
- oracle
- db2
- sqlserver
1.mysql的介绍
1.1mysql的优点
- 开源、免费、成本低
- 性能高、移植性好
- 体积小、便于安装
1.2安装和登陆
1.2.1安装
参考我的安装mysql的博客
1.2.2登陆
远程连接
mysql -h localhost -P 3306 -u root -p
h-host-主机
P-Port-端口号
u-user-用户
p-password-密码
如果登陆本机可以省略掉-h和-P内容即:
mysql -u root -p
1.3退出
\p
或者exit
2.基础环境变量配置
参考我的安装mysql的博客
3.常见命令
- 查看当前所有数据库
show databases;
- 打开指定的库
use 库名;
- 查看当前库的所有表
show tables;
- 创建表
create table 表名(
列名 列类型,
列名 列类型,
...
);
- 查看表结构
dasc 表名;
- 查看服务器版本
方式一:登录到mysql服务端
select version();
方式二:不登录到mysql服务端
mysql --verson
#或
mysql --v
4.语法规范
- 不区分大小写,但建议关键字大写,表名,列名小写;
- 每条命令用分号结尾(\g也可以但不常用);
- 命令可以根据需要进行缩进和换行,习惯上关键字单独一行;
- 注释:
单行注释:#注释文字
单行注释:–注释文字
多行注释:/注释文字/
5.mysql图形化界面的安装和使用
参考我的博客:sequel Pro的安装和使用
6.DQL的学习(data query language)
6.1基础查询
语法:select 查询列表 from 表名;
特点:
11. 查询列表可以是:表中的字段、常量值、表达式、函数
12. 查询的结果是一个虚拟的表格
6.1.1查询表中的单个字段
语法:
SELECT 列表名 FROM 表名;
6.1.2查询表中的多个字段
语法:
SELECT 列表名1 , 列表名2 , 列表名3 FROM 表名;
6.1.4查询表中所有字段
语法:
SELECT * FROM 表名;
6.1.5查询的注意事项
- 查询前需要打开所查询的库。在查询命令前加入打开对应库的代码后执行命令。打开某个库的代码:
USE 库名
- 着重号”`“用于区分关键字与字段(列表名)
- 执行一行命令时应选中当前输入的命令再执行
6.1.6查询常量值
语法:
SELECT 100;
SELECT 'ironman';#(字符串放入单引号)
6.1.7查询表达式
语法:
SELECT 100%98 ;#相当于查询2
6.1.8查询函数
语法:
SELECT VERSION();
6.1.9为字段取别名
好处:
- 提高可读性,便于理解。
- 如果要查询的字段有重名的情况,用别名可以区分开来。
6.1.9.1AS
关键字
SELECT 字段 AS 别名;
例:
SELECT 200%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
6.1.9.2直接起别名
SELECT 字段 别名;
例:
SELECT 200%98 结果;
6.1.9.3注意事项
在起别名的时候如果别名中有特殊符号应用双引号将别名引起来。
例:
SELECT last_name AS "ming zi" FROM employees;
6.1.10去重DINSTINCT
语法:
SELECT DISTINCT 字段名 FROM 数据库;
例:
SELECT DISTINCT department_id FROM employees;
6.1.11“+”号的作用
作用:作为运算符使用
其中一方为字符型,试图将字符型树脂转换成数值型,如果转换成功则做加法运算,如果转换失败,则将字符型转换成0。如果其中一方为null则结果为null
例:
SELECT 100+90; #查询190
SELECT '123'+10 #查询133;
SELECT 'hello'+10 #查询10;
SELECT null+10; #查询null
6.1.12查询表的结构
语法:
DESC 表名;
6.1.13常用函数
6.1.13.1拼接函数CONCAT()
语法:
CONNCAT(列名1,列名2,列名3,.....);
例子:
SELECT CONCAT(first_name,last_name) AS 姓名 FROM employees;
注意事项:如果拼接的内容中有null
则全部为null
。
6.1.13.2判断是否为空IFNULL()
语法:
SELECT IFNULL(列名,当列的内容为空时替换的内容);
例子:
SELECT IFNULL(num,0); #如果num储存的内容为空则输出时替换为0
6.1.13.3判断是否为空ISNULL()
语法:
SELECT ISNULL(列名);
例子:
SELECT ISNULL(num); #如果num储存的内容为null则替换为1,不为null则替换为0
6.2条件查询
6.2.1基本语法
SELECT 查询列表 FROM 表名 WHERE 筛选条件;
6.2.2按照条件表达式筛选
条件运算符:
>,<,=,<>,>=,<=(大于,小于等于,不等于,大于等于,小于等于)
例子:
SELECT * FROM employees WHERE salary>12000;#查询工资大于12000的员工信息
SELECT last_name ,department_id FROM employees WHERE department_id<>90;#部门编号不等于90号的员工名和部门编号
6.2.3按照逻辑表达式筛选
逻辑运算符:
and,or,not(也支持&&,||,!)
例子:
SELECT last_name,salary FROM employees WHERE salary>=10000 and salary<=20000#查询工资在10000到20000之间的员工名和工资
6.2.4模糊查询
语句:
like,between and,in,is null
6.2.4.1like
一般与通配符搭配使用。
通配符:
%
包涵任意0个或多个字符
_
包涵任意单个字符
例子:
SELECT * FROM employees WHERE last_name LIKE '%a%';#查询员工名中包涵字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '__a_b%';#查询员工中第三个字符为a,第五个字符为b的员工名
SELECT * FROM employees WHERE last_name LIKE '_\_%';#查询员工中第二个字符为下划线的员工名
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';#查询员工中第二个字符为下划线的员工名
ESCAPE后接定义为转意符的符号
6.2.4.2between and
例子:
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120; #查询员工编号在100-120之间的员工信息
等价于
SELECT * FROM employees WHERE employee_id>=100 AND employee_id<=120; #查询员工编号在100-120之间的员工信息
特点:
- 可以提高代码简洁度;
- 包涵边界值;
- 边界值左小右大;
6.2.4.3in
例子:
SELECT last_name,job_id FROM employees WHERE job_id IN ('IT_RPOT','AD_VP','AD_RPES');#查询员工的工种编号是IT_RPOT,AD_VP,AD_RPES其中之一的员工名和工种编号
等价于
SELECT last_name,job_id FROM employees WHERE job_id='IT_RPOT' OR job_id='AD_VP' OR job_id='AD_RPES';#查询员工的工种编号是IT_RPOT,AD_VP,AD_RPES其中之一的员工名和工种编号
注意事项:
- 提高代码简洁度;
- in列表的值类型必须一致或兼容;
- 不支持通配符。
6.2.4.4is not null
例子:
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;#查询没有奖金的员工名和员工率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;#查询有奖金的员工名和员工率
6.2.4.5<=>
SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;#查询没有奖金的员工名和员工率
缺点:可读性差
6.3排序查询
语法:
SELECT 查询列表 FROM 表 [WHERE 筛选条件] ORDER BY 排序列表[ASC|DESC]; #ASC升序,DESC降序
例子:
SELECT * FROM employees ORDER BY salary DESC;#查询员工信息,工资由高到低排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;#查询部门编号大于等于90,按入职先后排序
注意事项:ASC可以省略
6.3.1按表达式排序
例子:
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;#按年薪高低现实员工信息和年薪
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC;#按年薪高低现实员工信息和年薪
6.3.2按函数排序
例子:
SELECT LENGTH(last_name) AS 字节长度,last_name,salary FROM employees ORDER BY LENGTH(last_name) DESC; #按照员工姓名长度排序
6.3.3按多个字段排序
例子:
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;#查询员工信息,要求先按照工资排序,再按员工编号排序
6.3.4注意事项
- ASC代表升序,DESC代表降序,如果不写默认升序
- ORDER BY子句支持单个字段、多个字段、表达式、函数、别名;
- ORDER BY子句一般放在查询语句最后,
limit
子句除外
6.4常见函数
概念:类似于java中的方法。
优点:
- 隐藏了实现细节
- 提高代码重用性
语法:
SELECT 函数名(实参列表) [FROM 表];
分类:单行函数:主要用作处理;分组函数(统计函数):用做统计。
6.4.1单行函数
6.4.1.1字符函数
6.4.1.1.1 LENGTH
SELECT LENGTH('字符') ;#查询参数值字节个数
6.4.1.1.2 UPPER,LOWER
SELECT UPPER('abc'); #将字符改为大写
SELECT LOWER('abc');#将字符改为小写
例子:
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) AS 姓名 FROM employees;#将姓小写,名字大写输出
6.4.1.1.3 SUBSTR,SUBSTRING
注意:sql中索引值从1开始
SELECT SUBSTR('字符',索引值,[索引长度]); #返回索引值开始的字符
例子:
SELECT SUBSTR('TomandJarry',7,5); #返回jarry
6.4.1.1.4 INSTR
SELECT INSTR('被索引字符','索引字符') ;#返回索引字符第一次出现在被索引字符的位置,如未找到,则返回0
例子:
SELECT INSTR('TomAndJarry','And') ;# 返回4
6.4.1.1.5 TRIM
SELECT TRIM(['a' FROM]'字符串'); #去除字符串首位特定字符,如果不写默认去空格
例子:
SELECT TRIM('a' FROM 'aaaaTomaaaaaa'); #输出Tom
6.4.1.1.6 LPAD
SELECT LPAD('字符',长度,'填充字符');#用指定字符实现左填充字符至指定长度,如果字符本身长度大于指定的长度,则截断。
例子:
SELECT LPAD('字符',10,'填充'); #输出:填充填充填充填充字符
SELECT LPAD('字符',1,'填充'); #输出:字
6.4.1.1.7 RPAD
SELECT RPAD('字符',长度,'填充字符');#用指定字符实现右填充字符至指定长度,如果字符本身长度大于指定的长度,则截断。
例子:
SELECT RPAD('字符',10,'填充'); #输出:字符填充填充填充填充`
SELECT RPAD('字符',1,'填充'); #输出:字
6.4.1.1.8 REPLACE
SELECT REPLACE('字符','被替换字符','替换字符');#将字符中被替换字符替换成替换字符
例子:
SELECT REPLACE('TomAndJarry','Jarry','Dog');#输出:TomAndDog
6.4.1.2数学函数
6.4.1.2.1 ROUND
SELECT ROUND(数值,位数);#将数值四舍五入至指小数点后几位数
例子:
SELECT ROUND(3.14); #输出3`
SELECT ROUND(-3.14); #输出3`
SELECT ROUND(3); #输出3`
SELECT ROUND(3.1415,3); #输出3.142
6.4.1.2.2 CEIL
SELECT CEIL(数值);#向上取整
例子:
SELECT CEIL(3.14);#输出4`
SELECT CEIL(-3.14);#输出-3`
SELECT CEIL(1.00);#输出1
6.4.1.2.3 FLOOR
SELECT FLOOR(数值);#向下取整
例子:
SELECT FLOOR(3.14);#输出3`
SELECT FLOOR(-3.14);#输出-4`
SELECT FLOOR(1.00);#输出1
6.4.1.2.4 TRUNCATE
SELECT TRUNCATE(数值,截断位数);#讲述值在小数点后n位截断
例子:
WLECT TRUNCATE(3.1415,2);#输出3.14
6.4.1.2.5 MOD
SELECT MOD(数值1,数值2); #取余
例子:
SELECT MOD(10,3); #输出1`
SELECT MOD(10,0); #输出null
6.4.1.3日期函数
6.4.1.3.1 NOW
SELECT NOW(); #返回系统当前日期+时间
6.4.1.3.2 CURDATE
SELECT CURDATE(); #返回系统当前日期,不包含时间
6.4.1.3.3 CURTIME
SELECT CURTIME(); #返回系统当前时间,不包含日期
6.4.1.3.4 YEAR、MONTH、MONTHNAME、DAY、HOUR、MINUTE
SELECT YEAR(NOW());#打印出当前的年份`
SELECT MONTH(NOW());#打印出当前的月份`
SELECT MONTHNAME(NOW());#打印出当前的月份的英文
6.4.1.3.5 STR_TO_DATE
日期格式符:
%Y | 四位的年份 |
%y | 两位的年份 |
%m | 月份(01,02,03) |
%c | 月份(1,2,3) |
%d | 日(01,02,03) |
%H | 小时(24小时制) |
%h | 小时(12小时制) |
%i | 分钟(00,01,02) |
%s | 秒(00,01,02) |
SELECT STR_To_DATE('字符型日期','指定的格式');#将将指定的格式字符型日期转换成日期类型
例子:
SELECT STR_TO_DATE('02-08-2020','%m-%d-%Y');#输出2020-02-08
6.4.1.3.6 DATE_FORMAT
SELECT DATE_FORMAT(日期,'指定的格式');#将日期转换成将指定的格式字符型日期
例子:
SELECT DATE_FORMAT('2020/02/08','%Y年%m月%d日');#输出2020年02月08日
6.4.1.4其他函数
6.4.1.4.1 VERSON
SELECT VERSON(); #显示当前数据库版本号
6.4.1.4.2 DATABASE
SELECT DATABASE(); #查看当前库
6.4.1.4.3 USER
SELECT USER(); #查看当前用户
6.4.1.5流程控制函数
6.4.1.5.1 IF
SELECT IF(条件表达式,式子1,式子2); #成立返回式子1,不成功返回式子2
例子:
SELECT(10>5,'是','否‘); #返回是
6.4.1.5.2 CASE
使用方式:
6.4.1.5.2.1类 java中switch case
CASE 要判断的字段或者表达式
WHEN 常量1 THEN 要显示的值1或者语句1
WHEN 常量2 THEN 要显示的值2或者语句2
...
ELSE 常量n THEN 要显示的值n或者语句n
END;
例子:
SELECT salary AS 原始工资 ,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
#查询员工工资,要求:
#部门号等于30,显示的工资为原来的1.1倍
#部门号等于40,显示的工资为原来的1.2倍
#部门号等于50,显示的工资为原来的1.3倍
#其他部门工资为原工资。
6.4.1.5.2.1类 java中多重if
CASE
WHEN 条件1 THEN 如果条件1满足执行的语句1
WHEN 条件2 THEN 如果条件2满足执行的语句2
...
ELSE 如果所有条件都不满足执行的语句
END;
例子:
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>20000 THEN 'B'
WHEN salary>20000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
#查询员工工资情况
#如果工资大于20000,显示A级别
#如果工资大于15000,显示B级别
#如果工资大于10000,显示C级别
都则显示D级别
6.4.2分组函数
特点:
- 可以嵌套或搭配其他函数使用;
- 参数类型:
函数 | 支持的参数类型 | 功能 | null是否参与运算 |
---|---|---|---|
SUM | 数值型 | 求和 | 否 |
AVG | 数值型 | 平均值 | 否 |
MAX | 数值和字符型 | 最大值 | 否 |
MIN | 数值和字符型 | 最小值 | 否 |
COUNT | 任何类型 | 非空个数 | 否 |
- 与
DISTINCT
搭配运算
例子:
SELECT COUNT(DISTINCT salary),COUNT(DISTINCT salary) FROM employees;#输出30 60,30为去重后
6.4.2.1SUM
语法
语法:
SELECT SUM(参数) FROM employees;
例子:
SELECT SUM(参数) FROM employees;#统计salary的和
6.4.2.2AVG
语法:
SELECT AVG(参数) FROM employees;
例子:
SELECT AVG(salary) FROM employees;#统计salary的平均值
6.4.2.3MAX
语法:
SELECT MAX(参数) FROM employees;
例子:
SELECT MAX(salary) FROM employees;#统计salary的最大值
6.4.2.4MIN
语法:
SELECT MIN(参数) FROM employees;
例子:
SELECT MIN(salary) FROM employees;#统计salary的最小值
6.4.2.5COUNT
语法:
SELECT COUNT(参数) FROM employees;
例子:
SELECT COUNT(salary) FROM employees;#统计salary的非空个数
SELECT COUNT(*) FROM employees;#统计表的总行数
SELECT COUNT(常量值) FROM employees;#额外增加常量值的列后统计行数
注意事项:COUNT(*)
效率最高.
6.5分组查询
语法:
SELECT 分组函数,列(要求出现在GROUP BY后面)
FROM 表
[WHERE 筛选条件]
GROUP BY分组的列表
[HAVING 条件]
[ORDER BY 子句];
#查询列表必须特殊,要求是分组函数和group by后出现的字段
#分组列表可以是函数或者表达式
#HAVING用于在查询结果后筛选
例子:
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;#查询每个工种的最高工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;#查询邮箱中包含a字符的,每个部门的平均工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;#查询有奖金的领导手下员工的最高工资
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;#查询哪个部门的员工个数大于2
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000
ORDER BY MAX(salary) ASC;#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资,最高工资由小到大排序
6.6连接查询/多表查询
用处:当数据存于不同表的时候进行查询
SELECT 列1,列2 FROM 表1,表2;#不可取会出现问题。
#出现笛卡尔积现象:表1有m行,表2有n行,结果等于m*n行
#发生原因:没有有效的连接条件
语法:
6.6.1连接方法
- 按照年代分类:
sq192标准:在mysql中仅支持内连接
sq199标准:在mysql中不支持全外连接 - 按照功能分类:
- 内连接:
等值连接;
非等值连接;
自连接。 - 外连接:
左外连接;
右外连接;
全外连接。 - 交叉连接:
6.6.1.1sql192标准
6.6.1.1.1等值连接
语法:
SELECT 列1,列2 FROM 表1,表2 WHERE 条件;
例子:
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#查询员工名对应的部门名
注意事项:
- 可以起别名
- 两个表的顺序可以调换
- 可以加筛选
- 可以加分组
- 可以加排序
- 可以多表连接
6.6.1.1.2非等值连接
例子:
SELECT salary,grade_level
FROM employees,job_grades
WHERE salary BETWEEN job_grades.`lowest_sal`AND job_grades.`highest_sal`;
#查询工资和对应的工资等级
6.6.1.1.3自连接
例子:
SELECT e.employee_id,e.first_name,m.employee_id,m.first_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
#查询员工名和上级的名称
6.6.2sql199
语法:
SELECT 查询列表
FROM 表1 [别名] [连接类型]
JOIN 表2 [别名]
ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表]
6.6.2.1内连接INNER
6.6.2.1.1等值连接
例子:
SELECT last_name,department_name
FROM departments d
INNER JOIN employees e
ON e.`department_id`=d.`department_id`;
#查询员工名、部门名
#查询员工名、部门名、工种名,并按部门名降序(添加三表连接)
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN dobs j ON e.`job_id`=j.`job_id`
ORDER BY department_name DESC;
6.6.2.1.2非等值连接
例子:
#查询工资级别>20的个数,并按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
INNER JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`hightest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
6.6.2.1.3自连接
例子:
#查询员工和其上级名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`;
6.6.2.2外连接
特点:
- 查询主表中的所有记录
如果从表中有和他匹配的,则显示匹配的值
如果从表中没有和他匹配的,则显示null
外连接查询结果=内连接结果+主表有从表没有的信息 - 左外连接:
LEFT JOIN
左边的是主表 - 右外连接:
RIGHT JOIN
右边的是主表 - 左外和右外交换两个表顺序,可以实现同样效果
6.6.2.2.1左外连接LEFT[OUTER]
例子:
#查询女生对应的男朋友
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`;
#查询没有男朋友的女生
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
6.6.2.2.2右外连接RIGHT[OUTER]
例子:
#查询女生对应的男朋友
SELECT b.name,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`;
#查询没有男朋友的女生
SELECT b.name
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
6.6.2.3交叉连接CROSS
例子:
#使用99语法标准实现笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
6.6.3全外连接(mysql不支持)FULL[OUTER]
语法:
#效果相当于左外+右外
SELECT b.*,bo.*
FORM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id;
6.7子查询(内查询)
定义:出现在其他语句中的SELECT
语句称之为子查询(内查询)
外部的查询语句称之为主查询(外查询)
例子:
SELECT first_name FROM employees WHERE department_id IN(
SELECT department_id FROM departments WHERE location_id=1700
);
分类:
- 按字查询出现的位置:
SELECT
后面
仅支持标量子查询FROM
后面
支持表子查询WHERE
或HAVING
后面🌟
标量子查询(单行)🌟
列子查询(多行)🌟
行子查询EXISTS
后面(相关子查询)
表子查询- 按结果集的行列数不同:
- 标量子查询(结果集只有一行一列)
- 列子查询(结果集只有一列多行)
- 行子查询(结果集只有一行多列)
- 表子查询(结果集一般多行多列)
6.7.1SELECT
后面
例子:
#查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.`department_id` = d.`department_id`
)FROM departments d;
例子:
#查询员工号=102的部门名
SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id =102
) 部门名;
6.7.2FROM
后面
#查询每个部门的平均工资的工资等级
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
6.7.3WHERE
或HAVING
后面🌟
特点:
- 子查询放在小括号内
- 子查询一般放在条件右侧
- 标量字查询,一般搭配单行操作符使用(>,<,=…)
- 列字查询一般搭配多行操作符使用(IN,ANY/SOME,ALL)
操作符 | 含义 |
---|---|
IN/NOT IN | 等于列表中的任意一个 |
ANY/SOME | 和子查询返回的某一个值比较 |
ALL | 和子查询返回的所有值比较 |
6.7.3.1标量子查询
例子:
#谁的工资比 Abel 高?
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
例子:
#返回公司工资最少的员工的last_name,job_id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);
例子:
#查询最低工资大于50号部门最低工资的部门id及其最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id='50'
);
注意事项:
字查询结果必须一行一列
6.7.3.2列子查询
例子:
#返回location_id是1400或1700中所有员工的姓名
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
例子:
#返回其他工种中比job_id为`IT_RPOG`部门任意工资低的员工的:工号、姓名、job_id、salary。
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id <>'IT_PROG';
6.7.3.3行子查询
例子:
#查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
6.7.4EXISTS
后面(相关子查询)
语法:
EXISTS (完整的查询语句);
#结果1或0
例子:
#查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id` = e.`department_id`
);
6.8分页查询
语法:
SELECT 查询列表
FROM 表
[JOIN TYPE] [JOIN 表2]
[ON 连接条件]
[WHERE 筛选条件]
[GROUP BY 分组字段]
[HAVING 分组后的筛选]
[ORDER BY 排序]
LIMIT offset,size;#offest要显示的条目的其实索引(0开始),size显示要显示的条目个数
例子:
#查询第11-25条信息
SELECT * FROM employees LIMIT 10,15;
例子:
#查询有奖金的人,并且工资较高的前10名显示出来
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
6.8UNION
联合查询
语法:
#将多条查询语句的结果合并成一个结果
#多用于查询结果是来自于不同没有关系的表中
查询语句1
UNION [ALL]
查询语句2
UNION [ALL]
...
查询语句n
注意事项:🌟
- 要求多条查询语句的查询列数一样
- 要求多条查询语句的词啊勋的每一列的类型和顺序一致
UNION
关键字默认去重,使用UNION ALL
不去重
例子:
#查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id > 90;
例子:
#热点搜索(多数据查询同一个关键词)
#查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id,cname,csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';
7.DML的学习(data manipulation language )
7.1插入语句INSERT
7.1.1经典插入
语法:
INSERT INTO 表名(列名,...) VALUES(值1,...)
注意事项:
- 插入的值的类型要与列的类型一致或兼容
- 不可以为
null
的列必须插入值 - 列的顺序可以调换
- 列数和值的个数必须一致
- 可以省略列名,默认所有列,且列的顺序和表的顺序一致
7.1.1.1插入的值的类型要与列的类型一致或兼容
#插入一个人的信息
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'q','女','1990-4-23','18989898989',NULL,2);
7.1.1.2不可以为null
的列必须插入值,可以为NULL
的列如何插入值例子:
7.1.1.2.1方式一
例子:
#直接插入`null`
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'q','女','1990-4-23','18989898989',NULL,2);
7.1.1.2.2方式二
例子:
#列名也不写
INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id)
VALUES(13,'q','女','1990-4-23','18989898989',2);
7.1.1.3省略列名
INSERT INTO beauty
VALUES(13,'q','女','1990-4-23','18989898989',NULL,2);
7.1.2简洁插入
语法:
INSERT INTO 表名
SET 列名=值,列名=值...;
例子:
INSERT INTO beauty
SET id=19,NAME='刘涛',phone=999;
7.1.2两种插入方式的比较
经典插入 | 简洁插入 | |
---|---|---|
多行插入 | ✅ | ❌ |
子查询 | ✅ | ❌ |
7.2修改语句UPDATE
7.2.1单表修改
语法:
UPDATE 表名
SET 列=新值,列=新值...
WHERE 筛选条件;
例子:
#修改beauty中姓唐的电话号改为119
UPDATE beauty
SET phone='119'
WHERE NAME LIKE '唐';
例子:
例子:
7.2.2多表修改(级联更新)
7.2.2.1sql192语法
语法:
UPDATE 表1 别名,表2 别名
SET 列=值,....
WHERE 连接条件
AND 筛选条件;
7.2.2.1sql199语法
语法:
UPDATE 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件
SET 列=值,...
WHERE 筛选条件;
例子:
#修改张无忌女朋友的手机号为119
UPDATE boys bo
INNER beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119'
WHERE bo.`boyName`='张无忌';
例子:
#修改没有男朋友的女朋友的男朋友为2
UPDATE boys bo
RIGHT beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;
7.3删除语句
7.4方法一DELETE
语法:
DELETE FROM 表名 WHERE 筛选条件
7.4.1单表删除🌟
例子:
#删除手机号以9结尾的女生信息
DELETE FROM beauty WHERE phone LIKE '%9';
7.4.1多表删除
7.4.1.1sql192
语法:
DELETE 表1的别名,表2的别名
FROM 表1 别名,表2 别名
WHERE 连接条件
AND 筛选条件
7.4.1.1sql199
语法:
DELETE 表1的别名,表2的别名
FROM 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件
WHERE 筛选条件
例子:
#删除刘德华的女朋友的信息
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfrinend_id` = bo.`id`
WHERE bo.`boyName`='刘德华';
7.4方法二TRUNCATE
语法:
TRUNCATE TABLE 表名;
例子:
#清空boys表
TRUNCATE TABLE boys;
7.4两种删除比较
DELETE | TRUNCATE |
---|---|
可以加WHERE 筛选 | 不可以加WHERE 筛选 |
效率低 | 效率高 |
删除自增长列后插入数据,自增长的列的值从断点开始 | 删除自增长列后插入数据,自增长的列的值从1开始 |
有返回值 | 无返回值 |
可以回滚 | 不可以回滚 |
8.DDL的学习(data define language)
8.1库的管理
8.1.1创建CREAT
语法:
CREATE DATABASE [IF NOT EXISTS] 库名;
例子:
#创建图书库
CREATE DATABASE books;
例子:
#books库如果不存在创建,如果存在不创建
CREATE DATABASE IF NOT EXISTS books
8.1.2修改ALTER
语法:
#更改字符集
ALTER DATABASE 库名 CHARACTER SET 字符集名称;
8.1.3删除DROP
语法:
DROP DATABASE [IF EXISTS] 库名;
例子:
#删除books库
DROP DATABASE IF EXISTS books;
8.2表的管理
8.2.1创建CREAT
语法:
CREATE TABLE [IF NOT EXISTS] 表名(
列名 列的类型[长度(约束)],
列名 列的类型[长度(约束)],
列名 列的类型[长度(约束)],
...
)
例子:
#创建表book
CREATE TABLE book(
id INT ,#编号
bName VARCHAR(20),#图书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME #出版日期
);
例子:
#创建作者表
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(20)
);
8.2.2修改ALTER
8.2.2.1修改列名
语法:
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 列的类型;
例子:
#修改publishDate 为 pubDate
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
8.2.2.2修改类型或约束
语法:
ALTER TABLE 表名 MODIFY COLUMN 列名 列类型;
例子:
#修改book表中pubdate的类型为TIMESTAMP
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
8.2.2.3添加列
语法:
ALTER TABLE 表名 ADD COLUMN 列名 列类型;
例子:
#在author表中添加列 annual 类型为DOUBLE;
ALTER TABLE author ADD COLUMN annual DOUBLE;
8.2.2.4删除列
语法:
ALTER TABLE 表名 DROP COLUMN 列名;
例子:
#删除author表中annual列
ALTER TABLE author DROP COLUMN annual;
8.2.2.5修改表名
语法:
ALTER TABLE 旧表名 RENAME TO 新表名;
例子:
#修改表author名为 book_author
ALTER TABLE author RENAME TO book_author;
8.2.3删除DROP
语法:
DROP TABLE 表名;
例子:
#删除表book
DROP TABLE book;
8.2.3复制
8.2.3.1仅复制表的结构
语法:
CREATE TABLE 复制后的表名 LIKE 被复制的表;
例子:
#复制author表并命名为copy
CREATE TABLE copy LIKE author;
8.2.3.1复制表的结构和数据
语法:
CREATE TABLE 复制后的表名 SELECT 列名 FROM 被复制的表;
例子:
#复制author的所有数据到copy表
CREATE TABLE copy SELECT * FROM author;
8.2.3.1仅复制表的部分结构
语法:
CREATE TABLE 复制后的表名 SELECT 想复制的结构的列名1,想复制的结构的列名2... FROM 被复制的表 WHERE 0;
例子:
#复制author表的id,au_name 两个列的结构到copy表
CREATE TABLE copy SELECT id,au_name FROM author WHERE 0;
8.3数据类型
8.3.1数值型
8.3.1.1整型
整数类型 | 字节 | 范围 |
---|---|---|
TinyInt | 1 | 有符号:-128~127 无符号:0~255 |
SamallInt | 2 | 有符号:-32768~32767 无符号:0~65535 |
MediumInt | 3 | 有符号:-8388608~32767无符号:0~1677251 |
Int、Integer | 4 | 有符号:-2147483648~2147483647无符号:0~4294967295 |
BigInt | 8 | 有符号:-9223372036854775808~9223372036854775807无符号:0~9223372036854775807*2-1 |
注意事项:
- 如果插入的数值超过范围,会报错并插入临界值。
- 长度代表最大宽度,如果为数不够用0在左边补齐,但要搭配关键字
ZEROFILL
8.3.1.1.1设置有符号或无符号
语法:
CREATE TABLE 表名(
列1 INT,#有符号
列2 INT UNSIGNED #无符号
);
8.3.1.2浮点型
浮点数 | 字节 |
---|---|
float | 4 |
double | 8 |
定点数 | 字节 |
---|---|
DEC(M,D)|DECIMAL(M,D) | M+2 |
特点:
- M代表整个数据的长度、D代表小数点后保留几位(四舍五入方式),超过范围插入临界值;
- M和D均可省略,DEC中M默认为10,D默认为0;浮点型由插入数值决定
- 定点型精度高。
8.3.2字符型
字符串类型 | 最多字符数 | 描述及存储需求 |
---|---|---|
char | M(可以省略默认为1) | M为0~255之间的整数 |
varchar | M(不可以省略) | M为0~65535之间的整数 |
text | ||
blob(二进制) |
特点:
char
代表固定长度字符varchar
代表可变长度字符char
比varchar
效率高
8.3.3其他类型
关键字 | 类型 |
---|---|
binary | 包含二进制字符串 |
varbinary | 不包含二进制字符串 |
Enum | 枚举类型 |
Set | 类似枚举,不过可以一次插入多个 |
8.3.2日期型
日期和时间类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
date | 4 | 1000-01-01 | 9999-12-31 |
datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
timestamp | 4 | 19700101 080001 | 2038年某一刻 |
time | 3 | -838:59:59 | 838:59:59 |
year | 1 | 1901 | 2155 |
特点:
- Timestamp和实际时区有关,更能反应试剂日期,而datetime则只能反应出插入时的当地时区
- timestamp的属性受Mysql和SQLmode的影响
8.4常见约束
含义:用于限制表中的数据,为了保证添加到表中的数据准确可靠
约束类型 | 关键字 | 作用 | 是否支持列级约束 | 是否支持表级约束 |
---|---|---|---|---|
非空约束 | NOT NULL | 用于保证字段的值不能为空。例如学号、姓名 | ✅ | ❌ |
默认约束 | DEFAULT | 用于保证该字段由默认值。例如性别 | ✅ | ❌ |
主键约束 | PRIMARY | 用于保证该字段具有唯一性,并且非空。例如学号 | ✅ | ✅ |
唯一约束 | UNIQUE | 用于保证该字段具有唯一性,可以为空。 | ✅ | ✅ |
外键约束 | FOREIGN KEY | 用于保证该字段的值必须来自主表的关联列的值 | ❌ | ✅ |
检查约束(Mysql不支持) | CHECK | 检查插入数据是否符合特定约束 | ✅ | ✅ |
语法:
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
);
主键约束和唯一约束的对比:
保证唯一性 | 是否允许为空 | 一个表中存在多少 | 是否允许组合 | |
---|---|---|---|---|
主键约束 | ✅ | ❌ | 至多1个 | ✅ |
唯一约束 | ✅ | ✅ | 多个 | ✅ |
外键的特点:
- 要求在从表设置外键
- 要求外键列的类型和主表的关联列类型一致要求一致或兼容,名称无要求
- 主表的关联列必须是一个key(主键、唯一键)
- 插入数据时先插入主表后插入从表,删除数据时先删除从表后删除主表。
注意事项:
5. 创建表或者修改表时添加约束
8.4.1创建表时添加列级约束
例子:
CREATE TABLE stu(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,#非空
gender CHAR(1) CHECK(gender='男',gender='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18 #默认约束
);
8.4.2创建表时添加表级约束
语法
CREATE TABLE 表名(
列名 类型,
列名 类型,
列名 类型,
...
列名 类型,
[CONSTRAINT 约束名] 约束类型(字段名);
);
例子:
CREATE TABLE stu(
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
mijorid INT,
CONSTRAINT uq UNIQUE(seat),
CONSTRAINT ck CHECK(gender='男',gender='女),#检查
CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES mojor(id)#外键
);
8.4.3修改表时添加约束
语法
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;#列级约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(字段名) [外键的引用];
例子
ALTER TABLE stu MODIFY COLUMN stuname VARCHAR(20) NOT NULL;#添加非空约束
ALTER TABLE stu MODIFY COLUMN age INT DEFAULT 18;#添加默认约束
ALTER TABLE stu MODIFY COLUMN id INT DEFAULT KEY;#添加主键约束
ALTER TABLE stu ADD MODIFY KEY(id);#添加主键约束
ALTER TABLE stu MODIFY COLUMN seat INT UNIQUE;#添加唯一约束
ALTER TABLE stu ADD UNIQUE(seat);#添加唯一约束
ALTER TABLE stu ADD FOREIGN KEY(majorid) REFERENCES major(id);#添加外键约束
8.4.3修改表时删除约束
ALTER TABLE stu MODIFY COLUMN stuname VARCHAR(20) NULL;#删除非空约束
ALTER TABLE stu MODIFY COLUMN age INT;#删除默认约束
ALTER TABLE stu MODIFY COLUMN id INT;#删除主键约束
ALTER TABLE stu DROP PRIMARY KEY;#删除主键约束
ALTER TABLE stu DROP INDEX seat;#删除唯一约束
ALTER TABLE stu DROP FOREIGN KEY majorid;#删除外键约束
8.5标识列
特点:
AUTO_INCREMENT
一定是一个KEY- 一个表至多一个标识列
- 标识列类型只能是数值型
- 标识列可以通过
SET auto_increment_increment=n
设置步长 - 可以通过手动插入值设置标识列起始值
8.5.1创建表时设置标识列
语法
CREATE TABLE 表名(
列名 类型 KEY AUTO_INCREMENT
);
例子:
CREATE TABLE tab(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
8.5.1复制表时设置标识列
语法
ALTER TABLE 表名 MODIFY COLUMN 列名 类型 KEY AUTO_INCREMENT;
例子:
ALTER TABLE tab MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
9.TCL语言的学习(transaction control language)
事务:一个或一组SQL语句组成的一个执行单元,这个执行单元中每个SQL语句相互依赖,这个执行单元要么全部执行,要么全部不执行,如果执行错误,整个单元将会回滚。
存储引擎:用于mysql中用不同存储技术在文件中,通过SHOW ENGINES
来查看存储引擎。其中innodb引擎支持事务。
9.1事务的ACID属性
- 原子性:指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么不发生。
- 一致性:事务必须使数据库从一个一致性状态切换到另一个一致性的状态。
- 隔离性:指一个事务的执行不能被其他事务干扰,即一个事务哪不的操作及使用的数据对兵法的其他事务时隔离的,ing发执行的各个事务之间不能互相干扰。
- 持久性:指一个事务一旦被提交,他对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其具有任何影响。
9.2事务的分类
- 隐式事务:事务没有明显的开始和结束的标记,比如
SELECT
、INSERT
、UPDATE
、DELETE
等 - 显式事务:事务具有明显的开始和结束的标记,前提:必须先设置自动提交功能为禁用
SET AUTOCOMMIT =0
。
9.3事务的创建
语法:
SET AUTOCOMMIT=0;#设置自动提交关闭
[START TRANSACTION;]#开始事务
编写事务的sql语句
语句1;
语句2;
...
COMMIT;#提交事务
ROLLBACK;#回滚事务
例子:
#张飞转账给李白
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE account SET balance =500 WHERE username = '张飞';
UPDATE account SET balance =1000 WHERE username = '李白';
COMMIT;#成功COMMIT失败ROLLBACK
9.4数据库隔离级别:
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题
可能会导致的问题:
- 脏读:对于两个事务T1,T2,T1读取了已经被T2更新但还没有提交的字段之后,若T2回滚,T1度去的内容就是临时且无效的。
- 不可重复读:对于两个事务T1,T2,T1度去了一个字段,T2更新了该字段,T1再次读同一个字段值就不同了。
- 幻读:对于两个事务T1,T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行,之后,如果T1再次读取同一个表,就会多出几行。
隔离级别:
隔离级别 | 描述 | Oracle是否支持 | Mysql是否支持 |
---|---|---|---|
READ UNCOMMITTED (读未提交数据) | 允许事务读取未被其他事务提交的变更。脏读、不可重复读、幻读的问题都会出现 | ❌ | ✅(默认) |
READ COMMITED (读已提交数据) | 只允许事务读取已经被其他事务提交的变更。可以避免脏读,但不可重复读和幻读的问题可能出现 | ✅ (默认) | ✅ |
REPEATABLE READ (可重复读) | 确保事务可以多次从一个字段读取相同的值。在这个事务持续期间,禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读,但幻读的问题仍然存在 | ❌ | ✅ |
SERIALIZABLE (串行化) | 确保事务可以从一个表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入更新和删除操作。所有并发问题都可以避免,但性能低下 | ✅ | ✅ |
9.5设置数据库隔离级别
语法
SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;
例子:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;#读未提交数据
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;#读已提交数据
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;#可重复读
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;#串行化
9.6回滚点
语法:
SAVEPOINT 节点名;#设置节点
ROLLBACK TO 节点名;#回滚至保存点
10.视图
含义:表的投影。
优点:
- 重用sql语句
- 检查sql操作
- 保护数据提高安全性
10.1创建视图
语法:
CREATE VIEW 视图名
AS 查询语句;
例子:
#查询姓名中包含a字符的员工名、部门名和工种信息
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
10.2使用视图
语法:
SELECT 列名 FROM 视图名 WHERE 查询条件;
例子:
#查询姓名中包含a字符的员工名、部门名和工种信息
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
10.3修改视图
10.3.1方式一
语法:
#如果存在则修改,不存在就创建
CREATE OR REPLACE VIEW 视图名
AS 查询语句;
10.3.1方式二
语法:
ALTER VIEW 视图名
AS 查询语句;
10.4删除视图
语法:
DROP VIEW 视图名,视图名...;
10.4查看视图
语法:
DESC 视图名;#方式一
SHOW CREATE VIEW 视图名;#方式二
10.5更新视图
语法:
CREATE OR REPLACE VIEW 视图名
AS
新的查询语句;
不允许更新的视图:
- 包含分组函数、
DISTINCT
、GROUP BY
、HAVING
、UNION
、UNION ALL
- 常量视图
SELECT
中包含子查询JOIN
- FROM一个不能更新的视图
WHERE
子句的子查询饮用了FROM子句中的表
10.5视图和表的对比
创建语法的关键字 | 是否占用物理空间 | 使用 | |
---|---|---|---|
视图 | CREATE VIEW | 基本没有 | 主要用于查询 |
表 | CREATE TABLE | 占用 | 增删改查 |
10.6DELETE
和TRUNCATE
在事务使用时的区别
TRUNCATE
不支持回滚,DELETE
支持回滚
11.变量
11.1系统变量
含义:变量由系统提供,不是用户定义,属于服务器层面。
11.1.1全局变量
作用域:服务器每次启动将为所有全局变量赋初值,针对所有会话(链接)有效,但不能夸重启。
语法:
#查看所有系统变量
SHOW [SESSION] VARIABLES;#查看会话变量
#查看满足某种条件的部分系统变量
SHOW [SESSION] VARIABLES LIKE '%char%';#查看会话变量
#查看指定的某个系统变量的值
SELECT @@[SESSION].系统变量名;#查看会话变量
#为某个系统变量赋值
SET [SESSION] 系统变量名 = 值;#会话变量
SET @@[SESSION].系统变量名 = 值;#会话变量
11.1.2会话变量
作用域:仅仅针对于当前会话(连接)有效,每次服务器启动会为每个客户端赋初值。
语法:
#查看所有系统变量
SHOW GlOBAL VARIABLES;#查看全局变量
#查看满足某种条件的部分系统变量
SHOW GlOBAL VARIABLES LIKE '%char%';
#查看指定的某个系统变量的值
SELECT @@GLOBAL.系统变量名 ;#查看全局变量
#为某个系统变量赋值
SET GLOBAL 系统变量名 = 值;#全局变量
SET @@GLOBAL.系统变量名 = 值;#全局变量
11.2自定义变量
定义:由用户定义的变量
使用步骤:声明-使用-赋值
11.2.1用户变量
作用域:针对于当前会话(连接)有效,同会话变量的作用域。应用于任何位置。
语法:
#声明并初始化
SET @用户变量名=值;#方式一
SET @用户变量名:=值;#方式二
SELECT @用户变量名:=值;#方式三
#更新用户变量的值
SET @用户变量名=值;#方式一
SET @用户变量名:=值;#方式二
SELECT @用户变量名:=值;#方式三
SELECT 字段 INTO 变量名 FROM 表;#方式四
#使用
SELECT @用户变量名;
注意事项:
- 变量为弱类型;
- 用
SELECT INTO
更新时字段需为一行一列
11.2.2局部变量
作用域:仅仅在定义他的BEGIN END
中,应用在BEGIN END
第一句
语法:
#声明
DECLARE 变量名 类型;
#声明并赋值
DECLARE 变量名 类型 DEFAULT 值;
#赋值
SET 局部变量名=值;#方式一
SET 局部变量名:=值;#方式二
SELECT 局部变量名:=值;#方式三
SELECT 字段 INTO 局部变量名 FROM 表;#方式四
#使用
SELECT 局部变量名;
12.存储过程和函数
定义:类似于java中方法
12.1存储过程
含义:一组预先编译好的SQL语句的集合,理解成批处理语句。
12.1.1创建
语法:
#创建
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体;
END;
注意事项:
- 参数列表包含:参数模式、参数名、参数类型
IN
该参数可以作为输入OUT
该参数可以作为输出(返回值)INOUT
该参数及可以作为输入有可以作为输出
- 如果存储过程体只有一句话,
BEGIN END
可以省略 - 存储过程的结尾可以使用
DELIMITER
重新设置。
12.1.2DELIMITER
语法:
DELIMITER 结束标记
例子:
DELIMITER $;
12.1.3调用
语法:
#调用
CALL 存储过程名(实参列表);
12.1.4示例
例子:
#根据女生名,查询对应男生信息
#创建
CREATE PROCEDURE myp(IN beaytyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyName;
END$
#调用
CALL myp('ross')$
例子:
#根据女生名返回男生名
#定义
CREATE PROCEDURE myp(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name = beautyName;
END$
#调用
SET @bName$
CALL myp('rose',@bName)$
例子:
#传入a,b返回时a,b翻倍
#创建
CREATE PROCEDURE myp(INOUT a INT ,INTOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END$
#调用
SET @a=10;
SET @b=20;
CALL myp(@a,@b)$
12.1.4删除
语法:
DROP PROCEDURE 存储过程名;
例子:
DROP PROCEDURE myp;
12.1.4查看
语法:
SHOW CREATE PROCEDURE 存储过程名;
12.2函数
优点:同存储过程
存储过程与函数区别:函数有且仅有一个返回值
适合用于处理数据后返回结果
12.2.1创建
语法:
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
RETURN 值;
END$
注意事项:函数体中只有一句话可以省略BEGIN END
12.2.2调用
语法:
SELECT 函数名(参数列表);
例子:
#返回公司员工个数
#创建
CREATE FUNCTION myf() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义变量
SELECT COUNT(*) INTO c#赋值
FROM employees
RETURN c;
END$
#调用
SELECT myf()$
12.2.3查看
语法:
SHOW CREATE FUNCTION 函数名;
12.2.4删除
语法:
DROP FUNCTION 函数名;
13.流程控制结构
类型 | 执行方式 | 函数 |
---|---|---|
顺序结构 | 从上到下 | |
分支结构 | 两条或多条路径中选择一个 | IF 函数、CASE 结构、IF 结构 |
循环结构 | 满足一定条件基础上重复执行一段代码 | WHILE 、LOOP 、REPEAT |
IF
结构:
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
END IF;
WHILE
[标签]:WHILE 循环条件 DO
循环体;
END WHILE[标签];
LOOP
[标签]:LOOP
循环体;
END LOOP[标签];
REPEAT
[标签]:REPEAT
循环体;
UNTIL 结束循环调节
END REPEAT[标签];
ITERATE
:相当于continue
LEAVE
:相当于break
名称 | 特点 |
---|---|
WHILE | 先判断后执行 |
LOOP | 先执行后判断 |
REPEAT | 没有条件的死循环 |
如果你看到了这里,那么你的未来一定会很优秀。愿保初心,追寻梦想!