目录
来源：MySQL数据库
一、排序与分页
（1）排序
if无排序操作，默认查询返回数据为添加数据顺序。
ORDER BY后没有指明排序方式默认按照升序排列。
| 升序 | ASC（ascend） |
| 降序 | DESC（descend） |
eg: 依据salary从高至低顺序排列
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;注意：（1）可以用列的别名进行排序操作，但只能在ORDER BY中使用，不可在WHERE中使用。（2）WHERE需要声明在FROM之后，ORDER BY之前。
（2）分页
MySQL使用LIMIT实现分页。
需求：每页显示pageSize条记录，显示第pageNo页
显示公式：LIMIT(pageNo-1)*pageSize,pageSize
格式：LIMIT [位置偏移量,] 行数LIMIT 0，条目数=LIMIT 条目数
注意：LIMIT需要放在SELECT后
LIMIT 3 OFFSET 4：获取第5条后3条数据，等同于“LIMIT 4,3”
二、多表查询（关联查询）
多表查询需要有连接条件。
注：if查询语句出现多表存在字段，则必须指明此字段所在表（建议每个字段前指明所在表）。
可在SELECT和WHERE中使用表的别名，如果有别名必须使用，不可使用表名（表名被覆盖）。
if有n个表实现多表查询，则需要至少n-1个连接条件。
（1）多表查询分类
1.等值连接和非等值连接
（2）SQL99语法实现多表查询
1.JOIN...ON...语法
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件说明：类似FOR循环。可使用ON子句指定额外的连接条件。
2.内连接(INNER JOIN)
语法：
SELECT 字段列表
FROM TableA INNER JOIN TableB
ON 关联条件
WHERE 等其他子句;3.外连接(OUTER JOIN)
左外连接（LEFT OUTER JOIN）
语法：
--实现查询结果是A
SELECT 字段列表
FROM TableA LEFT JOIN TableB
ON 关联条件
WHERE 等其他子句;右外连接（RIGHT OUTER JOIN）
语法：
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;满外连接（FULL OUTER JOIN）
满外连接的结果=左右表匹配的数据+左表没有匹配到的数据+右表没有匹配到的数据
SQL99可用FULL JOIN or FULL OUTER JOIN实现满外连接。
MySQL不支持FULL JOIN，但可用UNION代替。
（3）UNION
UNION用于合并查询结果，合并结果集时两表对应列数和数据类型要一样。
语法：
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2注：各个SELECT语句之间使用UNION or UNION ALL分隔。
UNION：返回两个查询结果集的并集，去除重复记录。
UNION ALL：返回两个查询结果集的并集，不去除重复记录。
（4）SQL JOINS的实现
#实现A - A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;
#实现B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;
#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;
#实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B）
#使用左外的 (A - A∩B) union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句三、单行函数
（1）数值函数
1.基本函数
| 函数 | 说明 |
| ABS(x) | 返回绝对值 |
| SIGN(x) | 返回符号。正数返回1，负数返回-1,0返回0 |
| PI() | 返回圆周率值 |
| CEIL(x),CEILING(x) | 返回>=某值的最小整数 |
| FLOOR(x) | 返回<=某值的最大整数 |
| LEAST(e1,e2,e3...) | 返回列表最小值 |
| GREATEST(e1,e2,e3...) | 返回列表最大值 |
| MOD(x,y) | 返回x除以y后的余数 |
| RAND() | 返回0~1随机值 |
| RAND(x) | 返回0~1随机值，x作为种子值，相同x产生相同随机数 |
| ROUND(x) | 返回x值四舍五入后最接近x的整数 |
| ROUND(x,y) | 返回x值四舍五入后最接近x的整数，保留到小数点后y位 |
| TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
| SQRT(x) | 返回x的平方根。当x值为负数返回NULL |
| RADIANS(x) | 将角度转化为弧度，参数x为角度值 |
| DEGREES(x) | 将弧度转化为角度，参数x为弧度值 |
2.三角函数
| 函数 | 说明 |
| SIN(x) |
返回
x
正弦值，参数
x
为弧度值
|
|
ASIN(x)
|
返回
x
反正弦值，即获取正弦为
x
的值。如果
x
值不在
-1
到
1
之间，则返回
NULL
|
|
COS(x)
|
返回
x
的余弦值，参数
x
为弧度值
|
|
ACOS(x)
|
返回
x
反余弦值，即获取余弦为
x
的值。如果
x
值不在
-1
到
1
之间，则返回
NULL
|
|
TAN(x)
|
返回
x
正切值，参数
x
为弧度值
|
|
ATAN(x)
|
返回
x
反正切值，即返回正切值为
x
的值
|
|
ATAN2(m,n)
|
返回两个参数的反正切值
|
|
COT(x)
|
返回
x
余切值，x
为弧度值
|
3.指数对数
| 函数 | 说明 |
|
POW(x,y),
POWER(X,Y)
|
返回
x
的
y
次方
|
|
EXP(x)
|
返回
e
的
x
次方
|
|
LN(x)
，
LOG(x)
|
返回以
e
为底的
x
的对数，当
X <= 0
时，返回
NULL
|
|
LOG10(x)
|
返回以
10
为底的
x
的对数，当
X <= 0
时，返回
NULL
|
|
LOG2(x)
|
返回以
2
为底的
x
的对数，当
X <= 0
时，返回
NULL
|
4.进制转换
| 函数 | 说明 |
|
BIN(x)
|
返回
x
的二进制编码
|
|
HEX(x)
|
返回
x
的十六进制编码
|
|
OCT(x)
|
返回
x
的八进制编码
|
|
CONV(x,f1,f2)
|
返回
f1
进制数变成
f2
进制数
|
（2）字符串函数
| 函数 | 说明 |
|
ASCII(S)
|
返回字符串
S
中的第一个字符的
ASCII
码值
|
|
CHAR_LENGTH(s)
|
返回字符串
s
的字符数。作用与
CHARACTER_LENGTH(s)
相同
|
|
LENGTH(s)
|
返回字符串
s
的字节数，和字符集有关
|
|
CONCAT(s1,s2,......,sn)
|
连接
s1,s2,......,sn
为一个字符串
|
|
CONCAT_WS(x,
s1,s2,......,sn)
|
同
CONCAT(s1,s2,...)
函数，但是每个字符串之间要加上
x
|
|
INSERT(str, idx, len,
replacestr)
|
将字符串
str
从第
idx
位置开始，
len
个字符长的子串替换为字符串
rep
|
|
REPLACE(str, a, b)
|
用字符串
b
替换字符串
str
中所有出现的字符串
a
|
|
UPPER(s)
或
UCASE(s)
|
将字符串
s
的所有字母转成大写字母
|
|
LOWER(s)
或
LCASE(s)
|
将字符串
s
的所有字母转成小写字母
|
|
LEFT(str,n)
|
返回字符串
str
最左边的
n
个字符
|
|
RIGHT(str,n)
|
返回字符串
str
最右边的
n
个字符
|
|
LPAD(str, len, pad)
|
用字符串
pad
对
str
最左边进行填充，直到
str
的长度为
len
个字符
|
|
RPAD(str ,len, pad)
|
用字符串
pad
对
str
最右边进行填充，直到
str
的长度为
len
个字符
|
|
LTRIM(s)
|
去掉字符串
s
左侧的空格
|
|
RTRIM(s)
|
去掉字符串
s
右侧的空格
|
|
TRIM(s)
|
去掉字符串
s
开始与结尾的空格
|
|
TRIM(s1 FROM s)
|
去掉字符串
s
开始与结尾的
s1
|
|
TRIM(LEADING s1
FROM s)
|
去掉字符串
s
开始处的
s1
|
|
TRIM(TRAILING s1
FROM s)
|
去掉字符串
s
结尾处的
s1
|
|
REPEAT(str, n)
|
返回
str
重复
n
次的结果
|
|
SPACE(n)
|
返回
n
个空格
|
|
STRCMP(s1,s2)
|
比较字符串
s1,s2
的
ASCII
码值的大小
|
|
SUBSTR(s,index,len)
|
返回从字符串
s
的
index
位置其
len
个字符，作用与
SUBSTRING(s,n,len)
、
MID(s,n,len)
相同
|
|
LOCATE(substr,str)
|
返回字符串
substr
在字符串
str
中首次出现的位置，作用于
POSITION(substr
IN str)
、
INSTR(str,substr)
相同。未找到，返回
0
|
|
ELT(m,s1,s2,…,sn)
|
返回指定位置的字符串，如果
m=1
，则返回
s1
，如果
m=2
，则返回
s2
，如
果
m=n
，则返回
sn
|
|
FIELD(s,s1,s2,…,sn)
|
返回字符串
s
在字符串列表中第一次出现的位置
|
|
FIND_IN_SET(s1,s2)
|
返回字符串
s1
在字符串
s2
中出现的位置。其中，字符串
s2
是一个以逗号分
隔的字符串
|
|
REVERSE(s)
|
返回
s
反转后的字符串
|
|
NULLIF(value1,value2)
|
比较两个字符串，如果
value1
与
value2
相等，则返回
NULL
，否则返回
value1
|
注：MySQL中字符串位置从1开始。
（3）时间函数
1.获取时间
| 函数 | 说明 |
|
CURDATE()，CURRENT_DATE()
|
返回当前日期，只包含年、
月、日
|
|
CURTIME()
，
CURRENT_TIME()
|
返回当前时间，只包含时、
分、秒
|
|
NOW()
/SYSDATE()/CURRENT_TIMESTAMP()/
LOCALTIME()/LOCALTIMESTAMP()
|
返回当前系统日期和时间
|
|
UTC_DATE()
|
返回
UTC
（世界标准时间）
日期
|
|
UTC_TIME()
|
返回
UTC
（世界标准时间）
时间
|
2.时间戳与日期转换
| 函数 | 说明 |
|
UNIX_TIMESTAMP()
|
以
UNIX
时间戳的形式返回当前时间。
SELECT UNIX_TIMESTAMP()
->********
|
|
UNIX_TIMESTAMP(date)
|
时间
date
以
UNIX
时间戳的形式返回
|
|
FROM_UNIXTIME(timestamp)
|
UNIX
时间戳的时间转换为普通格式时间
|
3.获取时间的函数
| 函数 | 说明 |
|
YEAR(date) / MONTH(date) / DAY(date)
|
返回具体的日期值
|
|
HOUR(time) / MINUTE(time) /
SECOND(time)
|
返回具体的时间值
|
|
MONTHNAME(date)
|
返回月份：
January
，
...
|
|
DAYNAME(date)
|
返回星期几：
MONDAY，
TUESDAY.....SUNDAY
|
|
WEEKDAY(date)
|
返回周几，注意，周
1
是
0
，周
2
是
1，
...周日是
6
|
|
QUARTER(date)
|
返回日期对应的季度，范围为
1
～
4
|
|
WEEK(date)
，
WEEKOFYEAR(date)
|
返回一年中的第几周
|
|
DAYOFYEAR(date)
|
返回日期是一年中的第几天
|
|
DAYOFMONTH(date)
|
返回日期位于所在月份的第几天
|
|
DAYOFWEEK(date)
|
返回周几，注意：周日是
1
，周一是
2
，。。。周六是7
|
4.EXTRACT
语法：EXTRACT(type FROM date)
说明：返回指定日期特定的部分，type指定返回的值
| type取值 | 含义 |
| QUARTER | 返回日期在一年中的第几个季度 |
| SECOND | 返回秒数 |
| DAY_HOUR | 返回天和小时 |
5.秒钟和时间转换函数
| 函数 | 说明 |
|
TIME_TO_SEC(time)
|
将
time
转化为秒并返回结果值。
转化公式：
小时*3600+分钟*60+秒
|
|
SEC_TO_TIME(seconds)
|
将
seconds
描述转化为包含小时、分钟和秒的时间
|
6.计算时间的函数
| 函数 | 说明 |
|
DATE_ADD(datetime, INTERVAL expr type)
，
ADDDATE(date,INTERVAL expr type)
|
返回与给定日期时间相差
INTERVAL
时间段的日期时间
|
|
DATE_SUB(date,INTERVAL expr type)
，
SUBDATE(date,INTERVAL expr type)
|
返回与
date
相差
INTERVAL
时间间隔的日期
|
|
ADDTIME(time1,time2)
|
返回
time1
加上
time2
的时间。当
time2
为一个数字时，代表的是
秒
，可以为负数
|
|
SUBTIME(time1,time2)
|
返回
time1
减去
time2
后的时间。当
time2
为一个数字时，代表的是
秒
，可以为负数
|
|
DATEDIFF(date1,date2)
|
返回
date1-date2
的日期间隔天数
|
|
TIMEDIFF(time1, time2)
|
返回
time1-time2
的时间间隔
|
|
FROM_DAYS(N)
|
返回从
0000
年
1
月
1
日起，
N
天以后的日期
|
|
TO_DAYS(date)
|
日期
date
距离
0000
年
1
月
1
日的天数
|
|
LAST_DAY(date)
|
返回
date
所在月份的最后一天的日期
|
|
MAKEDATE(year,n)
|
针对给定年份与所在年份中的天数返回一个日期
|
|
MAKETIME(hour,minute,second)
|
将给定的小时、分钟和秒组合成时间并返回
|
|
PERIOD_ADD(time,n)
|
返回
time
加上
n
后的时间
|
7.日期格式化与解析
| 函数 | 说明 |
|
DATE_FORMAT(date,fmt)
|
按照字符串
fmt
格式化日期
date
值
|
|
TIME_FORMAT(time,fmt)
|
按照字符串
fmt
格式化时间
time
值
|
|
GET_FORMAT(date_type,format_type)
|
返回日期字符串的显示格式
|
|
STR_TO_DATE(str, fmt)
|
按照字符串
fmt
对
str
进行解析，解析为一个日期
|
非GET_FORMAT函数中fmt参数格式符：
| 格式符 | 说明 | 格式符 | 说明 |
|
%Y
|
4
位数字表示年份
|
%y
|
表示两位数字表示年份
|
|
%M
|
月名表示月份（
January,....
）
|
%m
|
两位数字表示月份 （01,02,03...
）
|
|
%b
|
缩写的月名（
Jan.
，
Feb.
，
....
）
|
%c
|
数字表示月份（
1,2,3,...
）
|
|
%D
|
英文后缀表示月中的天数
（
1st,2nd,3rd,...
）
|
%d
|
两位数字表示月中的天数
(01,02...)
|
|
%e
|
数字形式表示月中的天数
（
1,2,3,4,5.....
）
| ||
|
%H
|
两位数字表示小数，
24
小时制
（
01,02..
）
|
%h
和
%I
|
两位数字表示小时，
12
小时制（01,02...
）
|
|
%k
|
数字形式的小时，
24
小时制
(1,2,3)
|
%l
|
数字形式表示小时，
12
小时制（1,2,3,4...
）
|
|
%i
|
两位数字表示分钟（
00,01,02
）
|
%S
和
%s
|
两位数字表示秒
(00,01,02...)
|
|
%W
|
一周中的星期名称（
Sunday...
）
|
%a
|
一周中的星期缩写（
Sun.
，
Mon.,Tues.，.
..
）
|
|
%w
|
以数字表示周中的天数
(0=Sunday,1=Monday....)
| ||
|
%j
|
以
3
位数字表示年中的天数
(001,002...)
|
%U
|
以数字表示年中的第几周，
（
1,2,3...
）其中
Sunday
为周中第一天
|
|
%u
|
以数字表示年中的第几周，
（
1,2,3
。。）其中
Monday
为周中第一
天
| ||
|
%T
|
24
小时制
|
%r
|
12
小时制
|
|
%p
|
AM
或
PM
|
%%
|
表示
%
|
GET_FORMAT函数中date_type和format_type参数取值如下：
| 时间 | 标准 | 格式 |
| DATETIME | USA | %Y-%m-%d %H.%i.%s |
| TIME | USA | %h:%i:%s%p |
（4）流程处理函数
| 函数 | 说明 |
|
IF(value,value1,value2)
|
IF value
值=
TRUE
，返回
value1
，否则返回value2
|
|
IFNULL(value1, value2)
|
如果value1
不为
NULL
，返回
value1
，否则返回value2
|
|
CASE WHEN
条件
1 THEN
结果
1 WHEN
条件
2 THEN
结果
2 .... [ELSE resultn] END
|
相当于Java
的
if...else
|
|
CASE expr WHEN
常量值
1 THEN
值
1 WHEN
常量值
1 THEN 值1 .... [ELSE
值
n] END
|
相当于
Java
的
switch...case...
|
eg：
SELECT IF(1 > 0,'正确','错误')
->正确
SELECT IFNULL(null,'Hello Word')
->Hello Word
SELECT CASE 1
WHEN 1 THEN '我是1'
WHEN 2 THEN '我是2'
ELSE '你是谁'（5）加密与解密函数
| 函数 | 说明 |
|
PASSWORD(str)
|
返回字符串
str
加密版本，
41
位长字符串。加密结果
不可逆
，常用于用户密码加密
|
|
MD5(str)
|
返回字符串
str
的
md5
加密后的值。若参数为NULL，则会返回
NULL
|
|
SHA(str)
|
原明文密码
str
计算并返回加密后的密码字符串，当参数为NULL时，返回
NULL
。
SHA加密算法比MD5更加安全
|
|
ENCODE(value,password_seed)
|
返回使用
password_seed
作为加密密码加密
value
|
|
DECODE(value,password_seed)
|
返回使用
password_seed
作为加密密码解密
value
|
注：ENCODE和DECODE函数互为反函数。
（6）信息函数
| 函数 | 说明 |
| VERSION() |
返回当前
MySQL
的版本号
|
| CONNECTION_ID() |
返回当前
MySQL
服务器的连接数
|
| DATABASE().SCHEMA() |
返回
MySQL
命令行当前所在数据库
|
| USER()，CURRENT_USER()、SYSTEM_USER()，
SESSION_USER()
|
返回当前连接
MySQL
的用户名，返回结果格式为“主机名
@
用户名
”
|
|
CHARSET(value)
|
返回字符串
value
自变量的字符集
|
|
COLLATION(value)
|
返回字符串
value
的比较规则
|
（7）无分类函数
| 函数 | 说明 |
|
FORMAT(value,n)
|
返回对数字
value
进行格式化后的结果数据。
n
表示
四舍五入
后保留到小数点后n
位
|
|
CONV(value,from,to)
|
将
value
值进行不同进制之间转换
|
|
INET_ATON(ipvalue)
|
将以点分隔的
IP
地址转化为一个数字
|
|
INET_NTOA(value)
|
将数字形式的
IP
地址转化为以点分隔
IP
地址
|
|
BENCHMARK(n,expr)
|
将表达式
expr
重复执行
n
次。用于测试
MySQL
处理
expr
表达式所耗费的时间
|
|
CONVERT(value USING
char_code)
|
将
value
所使用的字符编码修改为
char_code
|
四、聚合函数
对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。
（1）简介
| 函数 | 说明 | |
| 数值型函数 | AVG() | 均值 |
| SUM() | 求和 | |
| 任意数据类型 | MIN() | 最小 |
| MAX() | 最大 | |
| 任意数据类型 |
COUNT(*)
|
返回表中记录总数。（统计NULL行）
|
|
COUNT(expr)
|
返回
expr不为空
的记录总数
|
（2）GROUP BY
1.基本使用
--使用GROUP BY子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
注：WHERE一定放在FROM后面
--SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
--包含在GROUP BY子句中的列不必包含在SELECT列表中
GROUP BY department_id;2.多列分组
--使用多列进行分组
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
使用
WITH ROLLUP
关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;注：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
（3）Having
1.基本使用
| HAVING子句 | 行已被分组 |
| 使用了聚合函数 | |
| 满足having子句条件的分组将被现实 | |
| Having需要与GROUP BY一起使用 |
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
--非法使用聚合函数：不能在WHERE子句中使用聚合函数
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;2.WHERE和HAVING的对比
| 函数 | 优点 | 缺点 |
| WHERE | 先筛选数据再关联，效率高 | 不能使用分组中计算函数进行筛选 |
| HAVING | 可以使用分组中计算函数 | 最后结果集中筛选，执行效率低 |
（4）SELECT
1.查询结构
--方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
--方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
/*其中：
（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积
（3）where：从表中筛选的条件
（4）group by：分组依据
（5）having：在统计结果中再次筛选
（6）order by：排序
（7）limit：分页
*/
SELECT查询两顺序：
--1.关键字顺序不能颠倒的：
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
--2.SELECT语句执行顺序：
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT五、子查询
（1）简介
1.基本使用
语法：
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list FROM table);
--子查询在主查询之前执行完成；子查询结果被主查询使用。注：子查询要包含在括号内；子查询置于比较条件右侧；单行操作符对应单行子查询，多行操作符对应多行子查询。
（2）单行子查询
1.单行比较操作符
| 操作符 | 说明 |
| <> | 不等于 |
（3）多行子查询
又称集合比较子查询，内查询返回多行。
1.多行比较操作符
| 操作符 | 说明 |
| IN | 等于列表中任意一个 |
| ANY | 和单行比较操作符使用，和子查询返回某值比较 |
| ALL | 和单行比较操作符使用，和子查询返回所有值比较 |
| SOME | ANY别名 |
六、创建和管理表
（1）基础知识
1.存储过程
2.数据类型
| 数据类型 | 函数 |
|
整数类型
|
TINYINT
、
SMALLINT
、
MEDIUMINT
、
INT(或INTEGER)
、
BIGINT
|
|
浮点类型
|
FLOAT
、
DOUBLE
|
|
定点数类型
|
DECIMAL
|
| 位类型 | BIT |
| 日期时间类型 |
YEAR
、
TIME
、
DATE
、
DATETIME
、
TIMESTAMP
|
|
文本字符串类型
|
CHAR
、
VARCHAR
、
TINYTEXT
、
TEXT
、
MEDIUMTEXT
、
LONGTEXT
|
|
枚举类型
| ENUM |
| 集合类型 | SET |
| 二进制字符串类型 |
BINARY
、
VARBINARY
、
TINYBLOB
、
BLOB、
MEDIUMBLOB
、
LONGBLOB
|
|
JSON
类型
|
JSON
对象、
JSON
数组
|
|
空间数据类型
|
单值：
GEOMETRY
、
POINT
、
LINESTRING
、
POLYGON
；
集合：
MULTIPOINT
、
MULTILINESTRING、
MULTIPOLYGON
、GEOMETRYCOLLECTION
|
| 关键字 | 说明 |
| NULL | 数据列包含NULL |
| NOT NULL | 列不允许包含NULL |
| DEFAULT | 默认值 |
| PRIMARY KEY | 主键 |
| AUTO_INCREMENT | 自动递增，适用整数类型 |
| UNSIGNED | 无符号 |
| CHARACTER name | 指定字符集 |
| 数据类型 | 说明 |
| INT |
从
-2^31
到
2^31-1
的整型数据。存储大小为
4
个字节
|
| CHAR(size) |
定长字符数据。若未指定，默认为
1
个字符，最大长度
255
|
| VARCHAR(size) |
可变长字符数据，根据字符串实际长度保存，
必须指定长度
|
| FLOAT(M,D) |
单精度，占用
4
个字节，
M=
整数位
+
小数位，
D=
小数位。
D<=M<=255,0<=D<=30
，默认M+D<=6
|
| DOUBLE(M,D) |
双精度，占用
8
个字节，
D<=M<=255,0<=D<=30
，默认
M+D<=15
|
| DECIMAL(M,D) |
高精度小数，占用
M+2
个字节，
D<=M<=65
，
0<=D<=30
，最大取值范围与
DOUBLE相同。
|
| DATE |
日期型数据，格式
'YYYY-MM-DD'
|
| BLOB |
二进制形式的长文本数据，最大可达
4G
|
| TEXT |
长文本数据，最大可达
4G
|
| 整数类型 | 字节 | 有符号数取值范围 | 无符号数取值范围 |
| TINYINT | 1 | -128~127 | 0-255 |
| SMALLINT | 2 |
-32768~32767
|
0~65535
|
| MEDIUMINT | 3 |
-8388608~8388607
|
0~16777215
|
| INT/INTEGER | 4 |
-2147483648~2147483647
|
0~4294967295
|
| BIGINT | 8 |
-9223372036854775808~9223372036854775807
|
0~18446744073709551615
|
| 整数类型 | 说明 |
| TINYINT |
用于枚举数据。比如系统设定取值范围很小且固定的场景
|
| SMALLINT |
用于较小范围统计数据。比如统计工厂固定资产库存数量等
|
| MEDIUMINT |
较大整数的计算。比如车站每日的客流量等
|
| INT/INTEGER |
取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号
|
| BIGINT |
只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等
|
| M |
显示宽度，
M
的取值范围是(0, 255)。eg：int(5)--当数据宽度小于5位数字前面需要字符填充
|
|
UNSIGNED
|
无符号类型（非负），最小取值为0
|
| ZEROFILL | 左边不够M位用0填充 |
| 类型 | 占用字节数 | 说明 |
|
FLOAT
| 4 | 单精度浮点数 |
| DOUBLE | 8 | 双精度浮点数 |
FLOAT(M,D) OR DOUBLE(M,D)：M--精度；D--标度。(M,D)中M=整数位+小数
位，
D=
小数位。
D<=M<=255
，
0<=D<=30。
| 语法格式 | 字节数 | 范围 |
|
DECIMAL(M,D),DEC,NUMERIC
|
M+2
字节
|
有效范围由
M
和
D
决定
|
DECIMAL(M,D):表示高精度小数。M--精度；D--标度。0<=M<=65，0<=D<=30，D<M。
|
二进制字符串类型
|
长度
|
长度范围
|
占用空间
|
| BIT(M) | M | 1<=M<=64 | 约(M+7)/8个字节 |
BIT类型没有指定（M）默认为1位。存储二进制数据。
| 类型 | 含义 | 字节数 | 格式 | 下限 | 上限 |
| YEAR | 年 | 1 |
YYYY
或
YY
| 1901 | 2155 |
| TIME | 时间 | 3 |
HH:MM:SS
|
-838:59:59
|
838:59:59
|
| DATE | 日期 | 3 |
YYYY-MM-DD
|
1000-01-01
|
9999-12-03
|
| DATETIME | 日期 时间 | 8 |
YYYY-MM-DD
HH:MM:SS
|
1000-01-01
00:00:00
|
9999-12-31
23:59:59
|
| TIMESTAMP | 日期 时间 | 4 |
YYYY-MM-DD
HH:MM:SS
|
1970-01-01
00:00:00 UTC
|
2038-01-19
03:14:07UTC
|
|
当取值为01到69
时，表示
2001
到
2069
|
|
当取值为70到99
时，表示
1970
到
1999
|
|
当取值整数的0或00
添加的话，那么是
0000
年
|
|
当取值是日期/字符串的'0'
添加的话，是
2000
年
|
| 文本字符串类型 | 长度 | 长度范围 | 存储空间 |
| CHAR(M) | M（固定长度） | 0<=M<=255 | M个字节 |
| VARCHAR(M) | M（可变长度） | 0<=M<=65535 | M+1个字节 |
| TINYTEXT | L（小文本，可变长度） | 0<=L<=255 | L+2个字节 |
| TEXT | L（文本，可变长度） | 0<=L<=65535 | L+2个字节 |
| MEDIUMTEXT | L（中等文本，可变长度） | 0<=L<=16777215 | L+3个字节 |
| LONGTEXT | L（大文本，可变长度） | 0<=L<=4294967295 | L+4个字节 |
| ENUM | L | 0<=L<=65535 | 1或2个字节 |
| SET | L | 0<=L<=64 | 1,2,3,4或8个字节 |
|
成员个数范围（
L
表示实际成员个数）
|
占用的存储空间
|
|
1<=L<=8
|
1
个字节
|
|
9<=L<=16
|
2
个字节
|
|
17<=L<=24
|
3
个字节
|
|
25<=L<=32
|
4
个字节
|
|
33<=L<= 64
|
8
个字节
|
--eg：创建表
CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);
--插入数据
INSERT INTO test_set (s) VALUES ('A'), ('A,B');
--插入重复的SET类型成员时，会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');（2）创建和管理数据库
1.创建数据库
--方法1：创建数据库
CREATE DATABASE 数据库;
--方法2：创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 数据集;
--方法3：判断数据库是否存在，不存在则创建（推荐）
CREATE DATABASE IF NOT EXITSTS 数据库名;2.使用数据库
--查看当前所有的数据库
SHOW DATABASES; #末尾S代表多个数据库
--查看当前正在使用的数据库
SELECT DATABASE(); #使用一个mysql中全局函数
--查看指定库下所有的表
SHOW TABLES FROM 数据库名;
--查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
or
SHOW CREATE DATABASE 数据库名\G
--使用/切换数据库
USE 数据库名;注：操作表格和数据之前说明是哪个数据库操作，否则就要对所有对象加上“数据库名.”。
3.修改数据库
--更改数据库字符集（eg：utf8、gbk）
ALTER DATABASE 数据库名 CHARACTER SET 字符集;
--方式1：删除指定的数据库
DROP DATABASE 数据库名;
--方式2：删除指定的数据库（推荐）
DROP DATABASE IF EXISTS 数据库名;（3）创建表
1.创建方式1
| 必须具备 | 1.CREATE TABLE权限；2.存储空间 |
| 必须指定 | 1.表名；2.列名（字段名），数据类型，长度 |
| 可选指定 | 1.约束条件；2.默认值 |
语法格式：
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);eg：
-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);2.创建方式2
| 说明 |
使用
AS subquery
选项，
将创建表和插入数据结合起来
|
|
指定列和子查询中列要一一对应
| |
|
通过列名和默认值定义列
|
--语法格式
CREATE TABLE table [(column,column...)] AS subquery;3.查看数据表结构
语法格式：
SHOW CREATE TABLE 表名\G（4）修改表
1.追加列
--语法格式：
ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];
--eg：
ALTER TABLE tablename
ADD job_id varchar(15);2.修改列
--语法格式：
ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值][FIRST|AFTER 字段名2];
--eg：
ALTER TABLE tablename
MODIFY last_name VARCHAR(30);3.重命名列
--语法格式：
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;4.删除列
--语法格式：
ALTER TABLE 表名 DROP 【COLUMN】字段名（5）重命名表
--方法一：使用RENAME
RENAME TABLE emp
TO myemp;
--方法二：
ALTER table dept
RENAME [TO] detail_dept;--[TO]可省略（6）删除表
--语法格式：
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
--eg：
DROP TABLE tablename;IF EXISTS： 如果数据库存在对应数据表，则删除数据表；如果不存在，则忽略。
（7）清空表
--eg；
TRUNCATE TABLE tablename;|
TRUNCATE
| 不能回滚 |
|
DELETE
| 可以回滚 |
七、数据处理增删改
（1）插入数据
方式一：VALUES的方式添加
1.表的所有字段按默认顺序插入数据
VALUES只能向表插入一条数据。值顺序与字段定义顺序相同。
INSERT INTO 表名
VALUES (value1,value2,....);2.表指定字段插入数据
INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);3.同时插入多条数据
--语法格式：
INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
--或者
INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);|
Records
|
表明插入的记录条数
|
|
Duplicates
|
表明插入时被忽略的记录，原因可能是记录包含了重复的主键值
|
|
Warnings
|
表明有问题的数据值，例如发生数据类型转换
|
4.组合语句插入多行
--语法格式：
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]注：INSERT加入子查询，不必写VALUES子句，子查询值列表应与INSERT子句列名对应。
（2）更新数据
--eg：
UPDATE employees
SET department_id = 100000
WHERE employee_id = 000001;可一次更新多条数据；
如果回滚，需要在DML前设置：SET AUTOCOMMIT=FALSE；
如果省略WHERE，则表所有数据被更新。
（3）删除数据
--语法结构：
DELETE FROM table_name [WHERE <condition>];|
table_name
|
指定要执行删除操作的表
|
|
[WHERE ]
|
可选参数，指定删除条件。如果没有
WHERE
，DELETE语句将删除表中的所有记录
|
如果省略WHERE，则表中全部数据被删除。
八、约束
（1）概述
1.约束分类
| 列数限制划分 | 单列约束：每个约束只约束一列 |
| 多列约束：每个约束可约束多列数据 | |
| 作用范围划分 | 列级约束：只作用一个列 |
| 表级约束：可作用于多列 |
| 位置 | 支持约束类型 | 是否可以起约束名 | |
| 列级约束 | 列的后面 | 语法支持，但外键无效果 | 不可以 |
| 表级约束 | 所有列下面 | 默认、非空不支持，其他支持 | 可以（主键无效果） |
--查看某个表已有的约束
--information_schema数据库名（系统库）
--table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';（2）非空约束
| 作用 |
限定某个字段
/
某列的值不允许为空
|
| 关键字 | NOT NULL |
| 特点 |
1.默认所有类型值都可以是
NULL
，包括
INT
、
FLOAT
等数据类型
2.非空约束只出现在表对象列，只能某列单独限定非空，不组合非空
3.一个表可以有很多列都分别限定了非空
4.空字符串
''
不等于
NULL
，
0
也不等于
NULL
|
1.添加非空约束
--（1）建表
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);
--（2）建表后
alter table 表名称 modify 字段名 数据类型 not null;2.删除非空约束
alter table 表名称 modify 字段名 数据类型 NULL;
--去掉not null，相当于修改某个非注解字段，该字段允许为空
或
alter table 表名称 modify 字段名 数据类型;
--去掉not null，相当于修改某个非注解字段，该字段允许为空（3）唯一性约束
1.添加唯一约束
| 说明 |
用来限制某个字段
/
某列的值不能重复。唯一约束允许出现多个空值：NULL
|
| 关键字 |
UNIQUE
|
| 特点 | 1.同一个表可以有多个唯一约束 2.唯一约束可以是某列值唯一也可以是多列组合唯一 3.唯一性约束允许列值为空 4.MySQL会给唯一约束列默认创建一个唯一索引 5.不命名唯一约束就默认与列名相同 |
--建表
create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);
--用户名和密码组合不能重复
--建表后指定唯一约束
--字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
--方式1：
alter table 表名称 add unique key(字段列表);
--方式2：
alter table 表名称 modify 字段名 字段类型 unique;2.关于复合唯一约束
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段列表)
--字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);3.删除唯一约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名';
--查看都有哪些约束
--eg：
ALTER TABLE USER
DROP INDEX name_pwd;
--注：可以通过show index from表名称;查看表的索引（4）主键约束
| 作用 |
用来唯一标识表中的一行记录
|
| 关键字 |
primary key
|
| 特点 | 唯一约束+非空约束的组合。主键约束不允许重复和NULL |
1.添加主键约束
--建表
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) --表级模式
);
--增加主键约束
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);
--字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键
2.复合主键
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名1,字段名2) --表示字段1和字段2组合是唯一的，也可以有更多个字段
);3.删除主键约束
alter table 表名称 drop primary key;
--删除主键约束，不需要指定主键名，因为一个表只有一个主键（5）自增列
| 说明 | 某个字段值自增 |
| 关键字 |
auto_increment
|
| 特点与要求 | 1.一个表最多只能有一个自增长列 2.当需要产生唯一标识符或顺序值时，可设置自增长 3.自增长列约束的列必须是键列 4.自增约束的列的数据类型必须是INT类型 5.如果指定了0和null，会在最大值基础自增。自增指定具体值，直接赋值为具体值 |
1.如何指定自增约束
--建表
create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);
--建表后
alter table 表名称 modify 字段名 数据类型 auto_increment;2.如何删除自增约束
--alter table 表名称 modify 字段名 数据类型 auto_increment;--给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型; --去掉auto_increment相当于删除（6）外键约束
| 作用 |
限定某个表的某个字段的引用完整性
|
| 关键字 |
FOREIGN KEY
|
| 主表和从表 |
主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
|
1.添加外键约束
--（1）建表
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
--(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
--(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列
--（2）建表后
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];2.删除外键约束
--(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';--查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
--（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; --查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;（7）CHECK约束
| 作用 |
检查某个字段的值是否符号
xx
要求，一般指的是值的范围
|
| 关键字 |
CHECK
|
（8）DEFAULT约束
| 作用 |
给某个字段
/
某列指定默认值，设置默认值后，插入数据时，如果此字段没有显式赋值，则赋值为默认值
|
| 关键字 |
DEFAULT
|
1.字段加默认值
--建表
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
primary key(字段名),
unique key(字段名)
);
--说明：默认值约束一般不在唯一键和主键列上加
--建表后
alter table 表名称 modify 字段名 数据类型 default 默认值;
--如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
--同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;2.删除默认值约束
alter table 表名称 modify 字段名 数据类型 ;--删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null;--删除默认值约束，保留非空约束九、视图
（1）创建视图
CREATE VIEW 视图名称
AS 查询语句
--扩展
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]1.创建单表视图
--eg：
CREATE VIEW empvu10
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 10;（2）查看视图
--语法1：查看数据库的表对象、视图对象
SHOW TABLES;
--语法2：查看视图的结构
DESC / DESCRIBE 视图名称;
--语法3：查看视图的属性信息
--查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G
--语法4：查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;（3）修改删除视图
1.修改视图
--方法1：使用CREATE OR REPLACE VIEW子句修改视图
CREATE OR REPLACE VIEW empvu10
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 10;
--说明：CREATE VIEW子句中各列的别名应和子查询中各列相对应
--方式2：ALTER VIEW
ALTER VIEW 视图名称
AS
查询语句2.删除视图
--删除视图只是删除视图的定义，并不会删除基表的数据
--删除视图的语法格式：
DROP VIEW IF EXISTS 视图名称;
十、存储过程与函数
（1）创建存储过程
--语法格式
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END| IN |
当前参数为输入参数，也就是表示入参
|
| OUT |
当前参数为输出参数，也就是表示出参
|
| INOUT |
当前参数既可以为输入参数，也可以为输出参数
|
| characteristics |
创建存储过程时指定的对存储过程的约束条件
|
（2）调用存储
1.调用格式
--语法格式
CALL 存储过程名(实参列表)
--1.调用in模式的参数：
CALL sp1('值');
--2.调用out模式的参数：
SET @name;
CALL sp1(@name);
SELECT @name;
--3.调用inout模式的参数：
SET @name=值;
CALL sp1(@name);
SELECT @name;（3）处理函数
1.语法分析
学过函数：LENGTH、
SUBSTR
、
CONCAT
等
--语法格式
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END2.调用存储函数
SELECT 函数名(实参列表)3.对比存储函数和存储过程
| 关键字 | 调用语法 | 返回值 | 场景 | |
| 存储过程 |
PROCEDURE
|
CALL
存储过 程()
|
理解为有
0
个或多个
|
一般用于更新
|
| 存储函数 |
FUNCTION
|
SELECT
函数()
|
只能是一个
|
一般用于查询结果为一个值并返回时
|
（4）存储过程与函数删改查
1.查看
--1.使用SHOW CREATE语句查看存储过程和函数的创建信息
--语法结构
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
--2.使用SHOW STATUS语句查看存储过程和函数的状态信息
--语法结构
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
--返回子程序特征
--3.从information_schema.Routines表中查看存储过程和函数的信息
--语法结构
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE ={'PROCEDURE|FUNCTION'}];
--说明：存储过程和函数名相同情况，最好用ROUTINE_TYPE来指明查询的是存储过程还是函数2.修改
--修改存储过程或函数
ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'|
CONTAINS SQL
|
子程序包含SQL语句，但不包含读或写数据的语句
|
|
NO SQL
|
子程序中不包含SQL语句
|
|
READS SQL DATA
|
子程序中包含读数据的语句
|
|
MODIFIES SQL DATA
|
子程序中包含写数据的语句
|
|
SQL SECURITY { DEFINER | INVOKER }
|
指明谁有权限来执行。
DEFINER：只有定义者自己才能够执行。
INVOKER：调用者可以执行
|
3.删除
--删除存储过程和函数，可以使用DROP语句
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名
--IF EXISTS：如果程序或函数不存储，可以防止发生错误，产生一个用SHOW WARNINGS查看的警告十一、变量与流程控制
（1）变量
1.查看系统变量
查看所有或部分系统变量
--查看所有全局变量
SHOW GLOBAL VARIABLES;
--查看所有会话变量
SHOW SESSION VARIABLES;
--或
SHOW VARIABLES;
--查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
--查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';查看指定系统变量
--查看指定的系统变量的值
SELECT @@global.变量名;
--查看指定的会话变量的值
SELECT @@session.变量名;
--或者
SELECT @@变量名;修改系统变量值
--方式1：修改MySQL配置文件，继而修改MySQL系统变量的值
--方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值
--修改系统变量值
--方式1：
SET @@global.变量名=变量值;
--方式2：
SET GLOBAL 变量名=变量值;
--为某个会话变量赋值
--方式1：
SET @@session.变量名=变量值;
--方式2：
SET SESSION 变量名=变量值;2.用户变量
| 会话用户变量 |
作用域和会话变量一样，只对当前连接会话有效
|
| 局部变量 |
只在BEGIN和END语句块中有效。局部变量只能在存储过程和函数中使用
|
会话用户变量
--变量定义
--方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
--方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
--查看用户变量的值 （查看、比较、运算等）
SELECT @用户变量局部变量
|
定义
|
可以使用DECLARE语句定义一个局部变量
|
| 作用域 |
仅仅在定义它的BEGIN...END中有效
|
| 位置 |
只能放在BEGIN...END中，而且只能放在第一句
|
BEGIN
--声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
--为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
END--定义变量
DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL
--变量赋值
--方式1：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
--方式2：一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;
--使用变量（查看、比较、运算等）
SELECT 局部变量名;| 作用域 | 定义位置 | 语法 | |
| 会话用户变量 | 当前会话 | 会话任何位置 | 加@，不用指定类型 |
| 局部变量 | 定义在BEGIN END |
BEGIN END
第一句话
|
一般不加
@,
需要指定类型
|
（2）定义条件与处理程序
1.定义条件
--语法格式
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）2.定义处理程序
--语法格式
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句| CONTINUE |
遇到错误不处理，继续执行
|
| EXIT |
遇到错误马上退出
|
| UNDO |
遇到错误后撤回之前的操作。
MySQL
中暂时不支持这样的操作
|
--定义处理程序的几种方式
--方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
--方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
--方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
--方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
--方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
--方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';（3）流量控制
1.分支结构
IF语法结构：
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IFCASE语法结构：
--情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
--情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）2.循环结构
LOOP语法结构：
LOOP
-- 循环执行的语句
END LOOP [loop_label];
--loop_label表示LOOP语句的标注名称WHILE语法结构：
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];REPEAT语法结构：
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]3.跳转语句
LEAVE语法结构：
LEAVE 标记名ITERATE语法结构：
ITERATE label十二、触发器
MySQL触发器（Trigger）是一种数据库对象，允许在特定事件（如插入、更新或删除）发生时，自动执行一段预定义的SQL代码。
（1）触发器创建
1.语法
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;| 表名 |
表示触发器监控的对象
|
| BEFORE/AFTER | 表示触发时间
BEFORE：
表示在事件之前触发；
AFTER：
表示在事件之后触发
|
| INSERT/UPDATE/DELETE |
表示触发的事件
INSERT：
表示插入记录时触发
UPDATE：
表示更新记录时触发
DELETE：
表示删除记录时触发
|
（2）查看、删除触发器
1.查看触发器
查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。
--方式1：查看当前数据库的所有触发器的定义
SHOW TRIGGERS\G
--方式2：查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
--方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息
SELECT * FROM information_schema.TRIGGERS;2.删除触发器
触发器也是数据库对象，删除触发器也用DROP
语句。
--语法格式
DROP TRIGGER IF EXISTS 触发器名称;
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
