MySQL基础篇

配置

net start mysqlxx：启动mysql服务，xx代表mysql安装时的命名

配置环境变量：找到安装mysql server的bin目录下，将其地址复制后添加进环境变量path里即可

mysql --version：查看版本号

mysql -u xxx -P xxx -h xxx -p xxx：进入mysql服务器，-u xxx表示用户名，-P xxx表示端口号，-h xxx表示IP，-p xxx表示密码

库操作

增

---

创建一个新的数据库

create database 库名

创建一个新的数据库并指明其字符集

create database 库名 character set '字符集'

• utf8、gbk

判断数据库是否存在，若存在则不创建

create database if not exists 库名

删

---

删除数据库

drop database 库名

若存在数据库则删除

drop database if exists 库名

改

---

更改数据库字符集

alter database 库名 character set 字符集

查

---

查看已有的数据库

show databases

查看库内的表

show tables

查看库/表的编码格式

show create database/table 库名/表名

查看指定数据库下保存的数据表

show tables from 表名

其他

---

使用数据库

use 库名

导入数据

source 文件全路径名

操作表

增

---

创建新表

create table 表名(字段1 数据类型,字段2 数据类型,...)

• 可以在末尾使用character set 字符集来指明表的字符集，也可以在数据类型后面添加，来指明字段的字符集

查询现有的表来创建表（同时会导入表中数据）

create table 表名 as 查询条件

• 若要查询并创建其他库内的表，需要库名.表名
• 在create table后使用if not exists可以判断其表是否存在
• 此方法创建的表不会拥有原表上所具有的约束

删

---

删除表

drop table 表名

清空表

truncate table 表名

改

---

数据

---

添加字段

alter table 表名 add 字段名 数据类型

• 在数据类型后使用first/after 字段名可以指定添加位置

修改字段类型、长度、默认值

alter table 表名 modify 字段名 数据类型

• 在数据类型后使用default可以修改其默认值

重命名字段

alter table 表名 change 旧字段名 新字段名 数据类型

• 也可修改数据类型长度

删除字段

alter table 表名 drop column 字段名

表

---

重命名①

rename table 旧表名 to 新表名

重命名②

alter table 旧表名 rename to 新表名

其他

---

查看表结构

describe/desc 表名

数据操作

增

---

直接添加

insert into 表名(字段1,字段2...) values(值1,值2...)

• 添加的值要按照字段的顺序添加
• 没有指明添加的字段，其值默认为null
• 在values后可添加多条数值，用,隔开

使用查询结果添加

insert into 表名(字段1,字段2...) 查询条件

• 查询条件所查询的字段要与添加的字段一一对应

注意：数据类型要匹配，数据类型长度应确保足够

删

---

delete from 表名 where 条件表达式

• 不加where条件限制，会删除全表
• 支持回滚

改

---

update 表名 set 字段1=值1,字段2=值2... where 条件表达式

• 若无where条件限制，则会修改全表

查

---

去重

---

select distinct 字段a,字段b from 表名

• 当有多个字段时，保留两者合起来没有重复的信息
• distinct位于字段b时，报错

过滤（where）

---

1. 将满足条件的字段名筛选出来
2. where紧挨在from后面
3. where是条件相等（返回1）时显示，若返回null或0则不会显示
4. 不能用于聚合函数中

select 字段名 from 表名 where 条件字段名 between 条件上界 and 条件下界

• 包含条件上界和条件下界
• 条件上界和条件下界不能反
• 条件字段名后加not则会取出和条件相反的结果

select 字段名 from 表名 where 字段名 in(条件1,条件2...)

• 筛选出字段为条件1、条件2…的
• not in则相反

select 字段名 from 表名 where 字段名 like %条件%

• like表示模糊查询
• %在前面或在后面，代表不确定条件在第几位数位置，可为0或多
• 每一条_代表一个不确定的字符
• 可用\转义，也可在条件后用escape x，此时可用x充当转义字符

过滤（having）

---

select 字段 from 表名 group by 字段 having 条件表达式

• 声明在group by后面
• 常与group by一同使用
• 效率较低

排序

---

select 字段名 from 表名 order by 条件字段名1 desc

• desc表示按照降序排列
• asc表示按升序排列，默认为asc
• 可以使用别名进行排序
• 声明在where之后
• desc后再加条件字段名2，会先按1排序后，如果数据相同则按2排序

分页

---

select 字段名 from 表名 limit 位置偏移量,显示条数

• 公式：(页数-1)*显示条数,显示条数
• 位置偏移量为0时，可以省略
• 声明在order by之后
• select 字段名 from 表名 limit 显示条数 offset 位置偏移量
  • 8.0新特性

多表查询

---

1. 多表查询要指明查询条件，否则会出现笛卡尔积现象
2. 如果多张表出现了相同字段名，则需要指明查询字段所在的表名，如果每个字段都指明其所在的表，能提高查询速度
3. 可以给表起别名，能在select和where中使用，但起别名后，如果在select和where中使用了表，就必须使用别名
4. 有n个表，至少有n-1个连接条件

select 字段名 from 左表 join 右表 on 关联条件

• 内连接，取出两个表共有的部分

select 字段名 from 左表 left join 右表 on 关联条件

• 左外连接，取出两个表共有的部分，同时取出左表的剩余数据

select 字段名 from 左表 right join 右表 on 关联条件

• 右外连接，取出两个表共有的部分，同时取出右表的剩余数据

select 字段名 from 左表 left join 右表 on 关联条件 where 右表字段名 is null

• 去右表：除去共有的，显示左表剩下的
• 左表比右表多，右表少的为null，比较值为null，筛选为null的值即为多出的部分
• 同理可得去左表

select 字段名 from 左表 rigth join 右表 on 关联条件 union select 字段名 from 左表 left join 右表 on 关联条件

• 满外连接，除共有部分外，还包括左表和右表的剩余数据
• union是将两者联合起来，并去掉重复的记录
• union all是将两者联合起来，包含重复的记录（效率高）
• 将去左表和去右表联合起来，即为除去共有部分的数据

分组

---

select 字段1,字段2... from 表名 group by 字段1,字段2...

• 常与聚合函数使用
• select中出现的字段必须出现在group by中，group by出现的字段不一定需要出现在select中
• 声明在order by前面
• group by 字段... with rollup会在最后统计所有记录的数量
  • 不能与order by一同使用

子查询

---

单行子查询操作符：=、>、>=、<、<=、<>

• 可在having 、case中使用
• 内查询的返回结果只能有一条数据
• 内查询的结果可以为null

多行子查询操作符：in、all、any/some

• any和all需要和单行操作符一起使用

select 字段名 from 表1 where exists

(select * from 表2 where 表1.条件=表2.条件)

• 表1传入字段中一条数据，子查询是否匹配，若不匹配则表1继续传入数据
• 存在则保留，not exists与其相反

group by和limit不能声明子查询

总结

---

select 字段1,字段2... ——————>查询内容

from 表1 (left/right)join 表2 on 连接条件 ——————>查询的表

where 过滤条件 ——————>过滤非聚合函数条件

group by 字段1,字段2... ——————>分组

having 过滤条件 ——————>过滤含聚合函数条件

order by 字段1,字段2... (asc/desc) ——————>排序

limit x,y ——————>显示条数

执行顺序

from --> on --> (left/right)join --> where --> group by --> having --> select --> distinct --> order by --> limit

函数

单行函数

---

其他

---

isnull(字段名)：筛选出字段为null的

least(字段a,字段b...)：筛选出最小的字段

greatest(字段a,字段b...)：筛选出最大的字段

数值类

---

abs(x)返回x的绝对值

sign(x)返回字段x的符号，正数返回1，负数返回-1，0返回0

pi()：返回π

ceil(x)、ceiling(x)：天花板值

floor(x)：地板值

least(x,y...)：返回其中的最小值

greatest(x，y...)：返回其中的最大值

mod(字段1x,y)：返回字段1除以字段2后的余数

rand()：返回一个0~1的随机数

rand(x)：返回一个0~1的随机数，x为种子，相同的x产生的随机值相等

round(x)：返回一个四舍五入后的值

round(x,y)：返回一个四舍五入后的值，y表示保留小数点后y位

truncate(x,y)：返回截断后的值，y为保留的小数位

sqrt(x)：返回x的平方根，x为负数时，返回null

bin(x)：返回x的二进制

hex(x)：返回x的十六进制

oct(x)：返回x的八进制

conv(x,f1,f2)：将x按照f1进制转换为f2进制并返回

字符类

---

ascii(s)：返回字符串s的第一个字母的ascii值

char_length(s)：返回字符串s的长度，中文为1个长度

length(s)：返回字符串s的长度，中文长度由编码格式决定

concat(s1,s2,s3...)：将字符串s1，s2，s3…连接起来（不能起别名）

concat_ws(x,s1,s2,s3...)：将字符串s1，s2，s3…连接起来，用字符串x隔开

insert(s,idx,len,r)：将字符串s从下标第idx位置开始往后的len个替换为r

replace(s,a,b)：将字符串s中的a替换为b

upper(s)、ucase(s)：将字符串s转换为大写

lower(s)、lcase(s)：将字符串s转换为小写

left(s,n)：取出字符串左边n个字符

right(s,n)：取出字符串右边n个字符

lpad(s,len,p)：将字符串s按len位数进行左对齐，位数不足时用字符串p代替

rpad(s,len,p)：将字符串s按len位数进行右对齐，位数不足时用字符串p代替

trim(s)：去掉字符串s首尾的空格

ltrim(s)：去掉字符串s左侧的空格

rtrim(s)：去掉字符串s右侧的空格

trim(r from s)：去掉字符串s首尾的r

trim(leading r from s)：去掉字符串s左侧的r

trim(trailing r from s)：去掉字符串s右侧的r

repeat(s,n)：返回字符串s重复n次后的结果

space(n)：返回n个空格

strcmp(s1,s2)：比较字符串s1和s2的字符，都相等返回0，否则返回1或-1

substr(s,idx,len)：字符串s从下标第idx位置开始，取len个字符后返回

locate(f,s)：返回字符串f在字符串s中首次出现的位置，未找到时返回0

elt(m,s1,s2,s3...)：返回为字符串s(m)的字符串

field(s,s1,s2,s3...)：返回字符串s在所有s(n)中首次出现的位置

find_in_set(s1,s2)：返回字符串s1在s2中首次出现的位置，其中s2为用,分隔开的一个个的字符串

reverse(s)：返回将s反转后的字符串

nullif(v1,v2)：比较两个字符串，返回结果v1v2，如果v1v2相等，则返回null，否则返回v1

日期类

---

常用

---

curdate()：返回当前年月日

curtime()：发挥当前时分秒

now()、sysdate()：返回当前年月日时分秒

utc_date()：返回世界标准年月日

utc_time()：返回世界标准时分秒

unix_timestamp()：返回当前时间的时间戳

unix_timestamp(d)：返回指定的日期的时间戳

from_unixtime(t)：将指定的时间戳转换为普通格式的日期

year(d)、month(d)、day(d)、hour(t)、minute(t)、second(t)：返回具体的年月日时分秒

monthname(d)：返回月份（january…）

dayname(d)：返回星期几（monday…）

weekday(d)：返回周几（周一是0，…）

quarter(d)：返回日期对应的季度（1~4）

week(d)、weekofyear(d)：返回一年中的第几周

dayofyear(d)：返回一年中的第几天

dayofmonth(d)：返回日期所对应月份中的第几天

dayofweek(d)：返回日期对应一周的周几（周日是1…）

extract(t from d)：返回指定日期的指定的t所返回的值

• microsecond：返回毫秒数
• second：返回秒数
• minute：返回分钟
• hour：返回小时
• day：返回天数
• week：返回日期是一年中的第几个星期
• month：返回日期是一年中的第几个月
• quarter：返回日期是一年中的第几个季度
• year：返回日期的年份
• second_microsecond：返回秒和毫秒
• minute_microsecond：返回分钟和毫秒
• minute_second：返回分钟和秒
• hour_microsecond：返回小时和秒
• hour_second：返回小时和秒
• hour_minute：返回小时和分钟
• day_microsecond：返回天和毫秒
• day_second：返回天和秒
• day_minute：返回天和分钟
• day_hour：返回天和小时
• year_month：返回年和月

time_to_sec(t)：将时间t转化为秒（小时 * 3600 + 分钟 *60 + 秒）

sec_to_time(t)：将时间t转化为对应的时分秒

date_add(d,interval n t)：将时间d按照指定的t进行数值上的+n(_n)

date_sub(d,interval n t)：将时间d按照指定的t进行数值上的-n(_n)

• hour：小时
• minute：分钟
• second：秒
• year：年
• month：月
• day：日
• year_month：年月
• day_hour：日小时
• day_minute：日分钟
• day_second：日秒
• hour_minute：小时分钟
• hour_second：小时秒
• minute_second：分钟秒

addtime(t1,t2)：返回t1加上t2的时间

subtime(t1,t2)：返回t1减去t2的时间

datediff(d1,d2)：返回d1-d2的日期间隔（天）

timediff(t1,t2)：返回t1-t2的时间间隔

from_days(n)：返回自0000年1月1日开始，n天以后的日期

to_days(d)：返回自日期d开始，距离0000年1月1日的天数

last_day(d)：返回d所在月份的最后一天日期

makedate(y,n)：根据给定年份y和天数n，返回一个对应日期

maketime(h,m,s)：将给定的时分秒合成时间后返回

period_add(t,n)：返回time加上n后的时间

日期和字符串

---

格式化：字符串 ——> 日期

解析： 日期 ——> 字符串

date_format(d,f)：按照字符串f形式格式化日期d

time_format(t,f)：按照字符串f形式格式化时间t

str_to_date(s,f)：按照字符串f形式对字符串s进行解析为日期

• %Y：4位数表示年
  • %y：2位数表示年
  • %M：英文月名表示月份（January…）
• %m：2位数表示月份（01,02,03…）
  • %b：英文缩写月名表示月份（Jan…）
  • %c：数字表示月份（1,2,3…）
  • %D：英文后缀表示月中的天数（1st，2nd，3rd…）
• %d：2位数表示月中的天数（01,02,03…）
  • %e：数字表示月中的天数（1,2,3…）
• %H：2位数表示24小时制（01,02,03…）
  • %h、%I：2位数表示12小时制（01,02,03…）
  • %k：数字形式表示24小时制（1,2,3…）
  • %l：数字形式表示12小时制（1,2,3…）
• %i：2位数表示分钟（00,01,02…）
• %S、%s：2位数表示秒（00,01,02…）
• %W：星期英文名称（Sunday…）
• %a：星期英文缩写（Sun,Mon…）
• %w：数字表示星期（0=Sunday，1=Monday…）
• %j：3位数表示年中的天数（001,002…）
• %U：以数字表示年中的第几周，Sunday表示第一天（1,2,3…）
• %u：以数字表示年中的第几周，Monday表示第一天（1,2,3…）
• %T：24小时制
• %r：12小时制
• %p：AM或PM
• %%：表示%

流程控制

---

if(v,v1,v2)：如果v是true，则执行v1，否则执行v2

ifnull(v1,v2)：如果v1是null，输出v2，否则输出v1

case when ... then ... when ... then ... else ... end：当when后的条件成立，执行then，否则会最后执行else（else可省略）（类似if-else）

case ... when ... then ... when ... then ... else ... end：（类似于switch-case）

加密与解密

---

md5(s)：将字符串s进行加密（不可逆）

sha(s)：将字符串s进行加密（更安全，不可逆）

MySQL信息函数

---

version()：查看当前MySQL的版本号

connection_id()：返回当前MySQL服务器的连接数

database()、schema()：返回当前命令行所在的数据库

user()：返回当前MySQL的用户名，格式为“主机名@用户名”

charset(s)：返回字符串s的字符集

collation(s)：返回字符串s的比较规则

inet_aton(ip)：将ip地址转化为数字形式

inet_ntoa(v)：将数字形式的ip地址v还原回去

benchmark(n,ex)：让表达式ex执行n次，多用于测试表达式执行的效率

convert(v USING c)：将v所用的字符编码修改为c

聚合函数

---

公式：avg=sum/count

• 只适用于数值类型

• 计算的字段不包含null

avg()：求平均值

sum()：求和

• 适用于数值类型、字符串类型、日期时间类型
• 计算的字段不包含null

max()：求最大值

min()：求最小值

• 计算表内有多少条记录：count(*)、count(1)
• 计算的字段不包含null

count()：计算指定字段在查询结果中出现的次数

约束

总结

---

查看表中约束

select * from information_schema.table_constraints where table_name='表名'

show create table 表名

• 非空不显示

1. 声明在字段后面，称为列级约束。在所有字段声明好后所声明的约束，称为表级约束。
2. 在使用表级约束时，若()内包含多个字段，则构成复合约束（受约束的多个字段，只要有一个字段不同，即视为不同）
3. 若已赋值，不能对不符合约束特性的字段使用alter去修改表中的对应字段，也不能使用update去不合理的修改已被约束的字段
4. 在表级约束中，constraint 表名可加可不加，用于给约束其别名，若不加，约束名默认为字段名，若是有()的复合约束，则约束名为()内排第一个的字段名

not null：非空约束

---

• 只有列级约束
• 可以有多个非空
• 删除约束时null可以省略

1）添加约束

create table时添加约束：create table 表名(字段1 数据类型 not null,~)

alter table时添加约束：alter table 表名 modify 字段名 数据类型 not null

2）删除约束

alter table时删除约束：alter table 表名 modify 字段名 数据类型 null

unique：唯一性约束

---

• 可以有列级约束和表级约束
• 可以有多个唯一
• 可以有多个null
• 使用表级约束时可以使用复合约束
• 添加唯一性约束的列会自动创建唯一索引，而删除唯一性约束只能通过删除唯一索引，唯一索引就是唯一索引名，即约束名

1）添加约束

create table时添加约束：

1. 列级约束：create table 表名(字段1 数据类型 unique,~)
2. 表级约束：create table 表名(字段1 数据类型,~,constraint 约束名 unique(字段名))

alter table时添加约束：

1. 方式一：alter table 表名 modify 字段名 数据类型 unique
2. 方式二：alter table 表名 add constraint 约束名 unique(字段名)

2）删除约束

alter table时删除约束：alter table 表名 drop index 约束名

primary key：主键约束

---

• 非空且唯一
• 一张表中只能有一个主键约束
• 可以有列级约束和表级约束
• 使用表级约束时给约束起别名无效，最终约束名仍然会是primary key
• 使用表级约束时可以使用复合约束，但被限定为复合主键约束的字段不能为null
• 主键约束一旦定义后，不推荐删除

1）创建约束

create table时添加约束：

1. 列级约束：create table 表名(字段1 数据类型 primary key,~)
2. 表级约束：create table 表名(字段1 数据类型,~,primary key(字段名))

alter table时添加约束：

1. 方式一：alter table 表名 modify 字段名 数据类型 primary key
2. 方式二：alter table 表名 add primary key(字段名)

2）删除约束

alter table时删除约束：alter table 表名 drop primary key

auto_increment：自增约束

---

• 使用该约束必须确保该约束的字段拥有主键约束或唯一性约束
• 一张表中只能有一个自增约束
• 使用该约束的字段数据类型必须是整型
• 只能有列级约束
• 使用自增约束的字段，在显示的给该字段赋值时，若赋值为0或null或负数，则会在当前最大值的基础上自增，若赋值为大于最大值的某个值，则会在这个值的基础上递增（不能为已有值：唯一性约束或主键约束）

1）添加约束

create table时添加：create table 表名(字段1 数据类型 unique/primary key auto_increment,~)

alter table时添加：alter table 表名 modify 字段名 数据类型 auto_increment

2）删除约束

alter table时删除：alter table 表名 modify 字段名 数据类型

foreign key：外键约束

---

• 主表（父表）：被关联的表

  从表（子表）：关联其他表的表

• 从表所关联的表的字段，必须是被主键约束或唯一性约束（确保值唯一）

• 主表和从表所关联的字段数据类型必须一致，且必须用同一种搜索引擎

• 只有表级约束

• 可以有多个外键约束

• 先有主表，才能用从表关联。

  为关联字段赋值时，必须先为主表赋值，之后才能为从表赋值。

  删除关联字段时，必须先删除从表字段，之后才能删除主表字段

• 从表的关联字段的值可以为null

• 不给外键约束起别名时，会自动产生一个别名（非字段名）

• 创建外键约束时，会自动建立一个索引，删除外键约束即删除对应索引

• 约束等级：（格式：on update xxx on delete xxx）

  • cascade：删除或修改主表字段或值时，同时删除或修改从表所关联的字段或值
  • set null：删除或修改主表字段或值时，将从表所关联的字段的值改为null
  • restrict、no action：关联的字段或值不允许修改或删除（默认）

• 查看

1）添加约束

create table时添加：create table 从表名(从表字段1 数据类型,~,constraint 约束名 foreign key(从表字段) references 主表名(主表字段))

alter table时添加：alter table 表名 add constraint 约束名 foreign key(从表字段) references 主表名(主表字段)

2）删除约束

alter table时删除：

1. alter table 从表名 drop foreign key(约束名)（删除外键约束）

2. alter table 从表名 drop index 约束名（删除外键约束对应索引）

check：检查约束

---

• 添加该约束后，会在给该约束对应的字段赋值时，检查是否满足添加要求
• 可以有多个检查约束
• 可以有列级约束和表级约束
• 无复合约束

1）添加约束

create table时添加：

1. 列级约束：create table 表名(字段1 数据类型 check(条件),~)
2. 表级约束：create table 表名(字段1 数据类型,~,check(条件))

alter table时添加：alter table 表名 add constraint 约束名 check(条件)

2）删除约束

alter table时删除：alter table 表名 drop check 约束名

default：默认值约束

---

• 可以有多个默认值约束

1）添加约束

create table时添加：create table 表名(字段1 数据类型 default 值,~)

alter table时添加：alter table 表名 modify 字段名 数据类型 default 值

视图

创建

create view 视图名 as 查询语句

• 查询语句中给字段起别名会作为视图中的字段名

• 给字段起别名的另外一种方式：

  create view 视图名(别名1,别名2,~) as select 字段1,字段2,~ ...

  • 需要确保别名个数与字段个数相同

• 可在视图上创建视图

• 视图中可以有基表中不存在的字段（如通过聚合函数得到的字段）

查看

①查看表和视图：show tables

②查看视图结构：desc 视图名

③查看视图属性信息：show table status like '视图名'

④查看视图详细定义信息：show create view 视图名

删除

drop view 视图1,视图2...

修改

①create or rerlace view 视图名 as 查询语句

②alter view 视图名 as 查询语句

总结：

1. 修改或删除视图中的数据，会导致原表的数据发生改变，反正，修改或删除原表的数据，也会改变视图的数据
2. 对基表不存在的字段无法进行修改或删除操作，对基表中有对应约束的字段也不能进行相应的修改
3. 当基于两张视图1视图2创建的视图，

存储过程与函数

存储过程

---

无参无返回值

---

创建

delimiter $

create procedure 存储过程名()

begin select ... from 表名 ... end $

delimiter ;

调用

call 存储过程名()

无参有返回值

---

创建

delimiter $

create procedure 存储过程名(out 传出变量 数据类型)

begin select ... into 传出变量 from 表名 ... end $

delimiter ;

调用

call 存储过程名(@传出变量)

查值

select @变量名

有参无返回值

---

创建

delimiter $

create procedure 存储过程名(in 传入变量 数据类型)

begin select ... from 表名 ... end $

delimiter ;

调用

①call 存储过程名(值)

②set @传入变量:=值 call 存储过程名(@传入变量)

有参有返回值

---

两个变量

---

创建

delimiter $

create procedure 存储过程名(in 传入变量 数据类型,out 传出变量 数据类型)

begin select ... into 传出变量 from 表名 ... end $

delimiter ;

调用

set @传入变量:=值 call 存储过程名(@传入变量,@传出变量)

查值

select @传出变量

一个变量

---

创建

delimiter $

create procedure 存储过程名(inout 变量 数据类型)

begin select ... into 变量 from 表名 ... end $

delimiter ;

调用

set @变量:=值 call 存储过程名(@变量)

查值

select @变量

总结

---

1. 存储过程与C的函数，Java中的方法类似，都是对一个功能的封装调用

2. $表示以此符号作为该过程的结束标志，遇到该符号则表示该存储过程函数结束

3. ;在修改结束标志，为了不影响后续的结束标志

4. into是将某个查询结果存放进传出变量中

   in是表示该存储过程需要一个传入变量

   out是表示该存储过程会产生一个传出变量

5. 传入或传出变量的数据类型必须与其查询的字段的类型一致

存储函数

---

创建

• 创建函数前，执行set global log_bin_trust_function_creators=1

  则可以不使用deterministic contains sql reads sql data

• 函数可以没有传入参数

delimiter $

create function 函数名(传入变量 数据类型)

returns 数据类型 deterministic contains sql reads sql data

begin return (查询语句) end $

delimiter ;

调用

select 函数名(传入变量值)

查看、修改、删除

---

查看

---

1. 查看创建信息

存储过程：show create procedure 存储过程名

存储函数：show create function 函数名

2. 查看状态信息

存储过程：show procedure status like '存储过程名'

存储函数：show function status like '函数名'

• like '名'可以不写，会查所有库中的所有存储过程或函数
• like用法和模糊查询一样，也可以模糊查询名

3. 查看信息

存储过程：select * from information_schema.Routines where ROUTINE_NAME='存储过程名' and ROUTINE_TYPE='PROCEDURE'

存储函数：select * from information_schema.Routines where ROUTINE_NAME='函数名' and ROUTINE_TYPE='FUNCTION'

• 存储过程或函数非重名的情况下，and后面的语句可以省略
• PROCEDURE或FUNCTION必须是大写

修改

---

• 内容体不能修改，只能修改其特性

删除

---

存储过程：drop procedure 存储过程名

存储函数：drop function 函数名

语法

数据类型

---

整型

---

tinyint：1字节 smallint：2字节 mediumint：3字节

int/integer：4字节 bigint：8字节

• 若类型后有()，表示显示其宽度（带上符号后的位数）
• zerofill修饰时，会用0填充，使其宽度等于定义的宽度，且数值类型会更改为unsigned

浮点型

---

float：4字节 double：8字节

• 在存储时，会默认拥有有符号的取值范围，即使重新定义为无符号类型，也不会扩大存储空间
• 赋值时的宽度大于显示定义时的宽度，小数部分会四舍五入，整数部分若超出，则会报错
• 存在精度损失，避免使用=判断

定点数类型

---

decimal(m,d)：m+2字节

• m为精度（总位数），d为标度（小数），0<=m<=65，0<=d<=30，d<m
• 底层以字符串形式储存
• 最大取值范围和double一致
• 不指明其精度和标度时，默认为decimal(10,0)，在定义的精度或标度与赋值不符时，同样会报错或四舍五入取值

位类型

---

bit(m)：m位

• 1<=m<=64表示二进制的位数
• 若不指明m，则默认为1位

时间类型

---

year：1字节 格式：YYYY、YY

• 最大值：1901 最小值：2155
• 表示年

time：3字节 格式：HH:MM:SS

• 最大值：-838：59：59 最小值：838：59：59
• 表示时分秒
• 若有D HH:MM:SS这种情况，此刻的D表示天数，最小值为0，最大值为34，会被转换为小时，格式为D*24+HH

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 最小值：2038-01-19 03：14：07UTC
• 表示带有时区的年月日时分秒

字符类型

---

char(m)：m字符的字节

• 固定长度，不指明m时，默认为1字符
• 0<=m<=255
• 存储时会在右侧填充空格补齐指定m的宽度，在被检索时会去除尾部空格

varchar(m)：m字符+1字节

• 可变长度，不指明m时会报错
• 0<=m<=65535（21845个字符）

枚举类

---

enum：1或2字节

• 赋值时只能使用规定的值，且只能使用一个
• 忽略大小写
• 可以使用索引调用值
• 无限制非空的情况下，可以为null

set：1、2、3、4、8字节

• 可以插入多个已声明的值
• 插入相同的值时，会自动删除重复的值

变量

---

系统变量

---

查看

---

查询所有系统变量：

1. 全局：show global variables
2. 会话：show session veriables、show variables

查询部分系统变量：

1. 全局：show global variables like '模糊变量名'
2. 会话：show variables like '模糊变量名'

查看指定系统变量：

1. 全局：select @@global.系统变量名
2. 会话：select @@session.会话变量名
3. 先会话，后全局：select @@变量名

修改

---

1. 修改配置文件（一劳永逸，需重启服务）

2. 运行期间修改：

   1. 系统：①set @@global.系统变量名=值

      ②set global 系统变量名=值

      • 重启服务失效
      • set后使用persist可以实现持久化（新特性）

   2. 会话：①set @@session.会话变量名=值

      ②set session 会话变量名=值

      • 建立新会话失效

用户定义变量

---

会话用户变量

---

创建

①set @用户变量名=值

②set @用户变量名:=值

③select @用户变量名:=查询语句

④select 查询语句 into @用户变量

调用

select @用户变量

局部变量

---

• 使用declare声明在begin ... end中，且必须声明在begin中的首行处
• 不使用default是，默认初始值是null
• 定义在存储过程和函数中，出了方法体则局部变量失效

定义

declare 局部变量名 类型 default 值

赋值

①set 局部变量名=值

②set 局部变量名:=值

③select ... into 局部变量名 from 表名 ...

调用

select 局部变量名

错误的处理

---

重定义错误名

---

declare 错误名 condition for 错误码

declare 错误名 condition for sqlstate '字符串错误码'

处理错误

---

declare 处理方式 handler for 错误类型 处理语句

处理方式

• continue：遇到错误不处理，继续执行
• exit：遇到错误则退出

错误类型

• sqlstate '字符串错误码'：长度为5的字符串错误码
• mysql_error_code：错误码
• 重定义错误名：重定义的错误名
• sqlwarning：匹配所有01开头的sqlstate错误码
• not found：匹配所有02开头的sqlstate错误码
• sqlexception：匹配没有被sqlwarning和not found匹配的错误码

自定义错误

---

signal sqlstate '字符串错误码' set message_text='错误信息'

流程控制

---

分支结构

---

1）if

if 表达式1 then 语句1; elseif 表达式2 then 语句2; ... else 语句; end if

• elseif、else等可以不写
• 使用在begin ... end中
• 只能用于存储过程和函数中

2）case

①case 表达式 when 值1 then 语句1; when 值2 then 语句2; ... else 语句; end case

②case when 值1 then 语句1; when 值2 then 语句2; ... else 语句; end case

• 只能用于存储过程和函数中

循环结构

---

1）loop

标签:loop 循环体; if 循环条件 then leave 标签; end loop 标签

• 常与标签使用
• if只是用来判断是否结束循环
• 只能用于存储过程和函数中

2）when

when 循环条件 do 循环体; end while

• 会爱先判断是否满足循环条件，然后执行
• 只能用于存储过程和函数中

3）repeat

repart 循环体; until 循环条件 end repeat

• 先执行，再判断循环条件
• 只能用于存储过程和函数中

跳转语句

---

1）leave 标签

• 一般用于存储过程和函数、循环中
• 类似于break

2）iterate 标签

• 只能用于循环语句中
• 类似于continue

游标

---

声明游标

declare 游标名 cursor for 查询语句

打开游标

open 游标名

使用游标

fetch 游标名 into 变量名

• 只能在循环中使用
• 将游标查询到的字段所对应的值存放在变量中

关闭游标

close 游标名

触发器

---

创建

delimiter //

create trigger 触发器名 alter/before insert on 表名 for each now

begin ... end //

delimiter ;

• after、before表示是 先在插入数据前给另外一张表插入 还是 先插入数据后再给另外一张表插入
• begin end内为 每次为表添加数据触发的内容
• 一张表可以绑定多个触发器
• 当要在begin end内为另一张表添加当前表插入的数据时，可调用new.字段来获得相应值

查看

①查看当前数据库内所有的触发器：show triggers

②查询当前数据库下的某个触发器：show create trigger 触发器名

③从information_schema库中查询：

​ select * from information_schema.triggers

删除

drop trigger 触发器名

窗口函数

---

格式

select 函数名() over(partition by 字段 order by 字段 ... ) from 表名

• partition by起分组作用，但不会将数据合并（与group by的差异）

①row_number()：根据分组字段分组并按顺序排序

②rank()：根据分组字段分组，字段相同的并排，并跳过相应的个数

③dense_rank()：根据分组字段分组，字段相同的并排，不跳过相应个数

④percent_ranl()：根据分组字段分组并按顺序排序，并显示当前位置是总量的百分之几的位置，相同的并排

⑤cume_dist()：根据分组字段分组并按顺序排序，并显示当前位置占总量的几分之几，相同的并排

⑥lag(字段,n)：显示指定字段的前n个值

⑦lead(字段,n)：显示指定字段的后n个值

⑧first_value(字段)：显示排第一的值

⑨last_value(字段)：显示排最后的值

⑩ntile(n)：分为n组，确保有n个组后再从前往后分组

CTE表达式

---

with 名 as(查询语句)

• 用于替换子查询，可重复使用
• 在连接表时连接该表即可调用

注意点

---

commit & rollback

---

commit：提交数据，将数据永久保存在数据库中，不可回滚

rollback：回滚数据，将数据回滚到最近的一次commit之后

truncate table VS delete from

---

相同：都可以实现对表数据的删除，同时保留表结构

truncate table：表数据全部删除，不可回滚

delete from：可以选择性删除表数据，支持回滚

知识点

---

注释

#、-- ：单行注释，–后需要加空格

/* */：多行注释

运算符

div、mod：/、%的另外写法，但div只会保留整数部分

整型+字符串：字符串会被转换为整型（隐式转换）。若字符串为字母，则会转为0

=：相等返回1，不相等返回0，有null则为null，两边都是字符串会按照ANSI比较

<=>：安全等于，可以用于和null比较，两边为null则为1

<>、!=：不等于的两种写法

:=：赋值运算符

逻辑运算符

not、!：非

and、&&：且，优先级比or高，也可以用(字段1,字段2,~)=(条件1,条件2,~)表示

or、||：或

xor：一真一假即满足条件

其他

dual：伪表

直接空格、AS、""：起别名的3种方式

*：显示所有字段

``：着重号，将普通变量名与关键字区分开

null：null参与运算，结果为null（0&&null、1||null除外）

is null：筛选出字段为null的

is not null：筛选出字段不为null的

b'xxxx'：xxxx表示为一个符合二进制形式的数字，b即标志该数字为二进制

细节

---

1. 两整数相除，结果为小数。
2. 两数相除，若除数为0，结果为null。
3. 取模运算的结果根据被模数的正负而决定。
4. =可以作为比较运算符，有时也可以作为赋值运算符，比如updatae中使用set。
5. 字符串索引从1开始。