`
mysql(5.5)基本语法
文章目录
前言
- 知识目录
- 这是平时学习总结的地方,用做知识库
- 平时看到其他文章的相关知识,也会增加到这里
- 随着学习深入,会进行知识拆分和汇总,所以文章会随时更新
- 参考的文章过多,所以参考会写不全,见谅
注意:所有的关键词尽量大写,我这里为了看着方便,都用的小写,自己调试就行
1.数据库基础
1.特点
- 将数据放到表中,表再放库中
- 一个数据库中可以有多个表,每个表都有一个名字,用来标识自己。表名有唯一性
- 表具有一些特性,这些特性定义了数据在表中如何储存
- 表由列组成,我们也称为字段。每个表都是由一个或者多个列组成,类似“属性‘
- 表中的数据是按行来储存的,每一行类似于“对象”
2.英文
DB
数据库(database) 存储数据的仓库
DBMS
数据库管理系统(database management system)数据库是通过DBMS创建和操作的容器
数据库软件或数据库产品,
SQL
结构化查询语言(structure query language):专门用来与数据库通信的语言 ,不是某个数据库软件特有的,几乎所有的主流数据库通用的语言
3.好处
- 可以持续化到本地
- 结构化查询
- 开源、免费、成本低
- 性能好、移植性好
- 体积小,便于安装
4.常见数据库
MySQL oracle db2 sql server
5.几个必知英文
DML(data manipulation language),数据操作语言,如增删该查
DDL(data definition language),数据定义语言,如建表删表,修改表字段(改变表结构)
DCL(data control language),数据控制语言,如权限授权
DQL(data query language),数据查询语言
TCL(Transaction control language) 事务控制语言
- 一般用到DQL DML , 另外的DDL、DCL是交给dba去研究的
2.语法规范
- 不分大小写,但是建议关键字大写,表名、列名小写
- 每条命令最好分号结尾
- 每个命令根据所需要,可以进行缩进、换行
- 注释
- 单行注释: # 注释文字
- 单行注释: --注释文字
- 多行注释: /* 注释文字 */
3.常用语法
1.启动与关闭
win10的dos中(需要管理员运行)
net start 服务名 //启动sql
net stop 服务名 //关闭sql
计算机--右击管理--服务
2.登陆
mysql自带的客户端
只限于 root 用户
通过 windows 自带的客户端
mysql [-h主机名 -P端口号] -u用户名 -p密码
3.查看服务器的版本
登录到mysql服务端
select version();
没有登录到mysql服务端
mysql --version 或者 mysql --v(在dos中不用分号)
4.退出
exit 或者 ctrl + c
5.查看当前所有的数据库
show databases ;
6.着重号使用
用于字段和保留关键字重复的时候,不至于识别错误
NAME 是保留关键词,但是有一个字段就是name,那么此时就用`name`,将它和关键词区别开
4.通配符
% 任意多个字符,包含0个字符
_ 任意单个字符
通配符转义:
\通配符
4.DQL语言学习
进行数据库访问的时候,必须要先打开指定库
1.基础查询
语法: select 查询列表 from 表名;
1.查询列表:表中字段、常量值、表达式、函数(这个就是挑选显示在虚拟表中的)
1.查询列表
字段
1.表中多个字段:
逗号隔开:字段,字段 select 字段名,字段名 from 表名;
2.表中所有字段:
1.在 select 后,直接双击要进行查询的字段,就可以直接出现在后面了,(是反引号引着的 ``),记得加逗号
2.用 * 表示所有的字段,类似于通配符,但是是按照默认的顺序查询的,不能自己决定顺序
select * from 表名;
常量值
select 100; #这里就不用找库了
select ‘john’
1.字符型和日期型的常量值必须要用单引号引起来,数值型不需要
表达式
select 100%98;
函数
select version(); #调用函数执行的返回值
2.起别名
- 便于理解
- 如果查询的字段有重名的情况,使用别名可以区别开来
一:
1.单个值起别名
select 查询列表 as 别名 from 表名;
执行结果显示就以别名的形式展示出来
2.多个值起别名
select 查询列表 as 别名,查询列表 别名,...,from 表名;
二:
可以直接使用空格代替as,但是数量太多了,就不要用了,容易混淆
三:
别名中含有特殊字符的,使用双引号引起来,当值出现歧义
3.去重:
select distinct 查询列表 from 表名 #删除筛选出来的查询列表的重复值
1.查询列表是多个字段的时候,不会报错,但是不同人貌似运行出来不一样,这个需要自己尝试
4.+号的作用
mysql 中只有一个作用——运算符
1.两个操作数都是数值型,则进行加法运算
select 100+90;
2.一个操作数是数值型,一个是字符型,则会试图转换字符型到数值型
如果成功,则执行加法运算
select '100'+90;
如果失败,则将字符型数值转化为0
3.一方为 null,结果肯定是 null
select null+0;
5.拼接
select contact(字段1,字段2,...)[as 别名] #常搭配起别名一起使用
字段可以是字段,可以是其他的类型符号
6.null
特别注意null这个项
ifnull(值为null的字段或表达式,替换null的值) #是null返回指定的值,不是null,返回自身值
null +/and 另一个值,结果就是null
7. 查找索引
show index from stuinfo #查询列表的所有索引
2.条件查询
是在基础查询的基础上进行的
语法:select 查询列表 from 表名
where 筛选条件 ;
选出表中符合筛选条件的查询列表
1.筛选条件分类
1.按照条件表达式
条件运算符 > < = != <> >= <=
1.查询列表可以是多个,同时筛选条件可以是一个或者多个
2.逻辑运算表达式:
逻辑运算符 && || ! and or not
用于连接条件表达式
select name,salary from employees where salary>100 and salary<900;
选出 name 和 salary 中salary>100 and salary<900的
&& 和 and :与,两个条件均为true,结果为true
|| 和 or :或,两条件一个为真,则结果为真
! 和 not :非,输出相反结果,本身为true,结果为false;本身为false,结果为true
3.模糊查询:
like
name like ’%a%‘; #选出 包含 任意个 a 的 name
1.通常和通配符联合使用
between and
id between 100 and 30 ;
1.包含两边的值,区间是[30,100],
2.两个值放置顺序顺序固定,左大右小
3.两个值类型要一致
in
id in (‘IT’,‘AD’) #选出条件 id 中是 IT 、AD
1.判断某字段的值是否属于in列表中的某一项(可以理解为 =)
2.提高语句简洁度
3.不支持通配符
is null | is not null
salar is null; #选出工资是零的
1.= 或 <> 不能用于判断 null
2.is null 和 is not null 仅可以判断 null
3.安全等于 <=> (就是 ==,判断是否等于) ,可以判断null,也可以判断其他值
salary <=> null;
salary <=> 1200;
3.排序查询
基础查询+[条件查询]+order by 排序的列表/字段 asc|desc
select 查询列表 from 表 [where 筛选条件]
order by 排序列表/字段 asc|desc
asc 升序 desc 降序 默认:升序
排序列表:单个字段、多个字段、表达式、函数、别名,一般在最后,limit字句除外
多个字段排序规则,类似多级关键词
order by salary asc , employee_id desc; #先按照salary排序,然后按照emplyee_id排序
4.常见函数
调用
select 函数名(实参列表)[from 表名]
特点:
叫什么:函数名
做什么:函数功能
分类:
单行函数:
分组函数:
- 又叫统计函数、聚合函数、组函数
- 统计使用
1.单行函数
1.字符函数
-
length
length() #获取字符串的字节个数
-
contact
contact(字符串,字符串,…) #拼接字符串
-
upper 、lower
upper() #转化为大写
lower() #转化为小写
-
substr substring
substr(字符串,截取的索引值,字符长度) ; #截取字符串,索引从 0 开始,长度默认为0
-
instr
instr(字符串,子串) t; # 返回子串在字符串中的第一次出现的起始索引,不存在返回值为0
-
trim
trim(字符串) ; #去除字符串前后的空格
trim(子串 from 字符串) ; # 去除字符串前后出现的子串
-
lpad
lpad(子符串,指定长度,子串) #在指定字符串左边实现填充指定长度的子串
-
rpad
rpad(子符串,指定长度,子串) #在指定字符串右边实现填充指定长度的子串
-
repleace
repleace(字符串,字符串中的子串1,子串2) # 子串2替换掉字符串中的所有的子串1
2.数学函数
-
round
round(数值,保留的小数点位数)
1.四舍五入,不管正负,先进性绝对值四舍五入,然后进行加正负号
2.小数位数默认0
-
ceil
ceil(参数只有一个) #向上取整,返回 >= 该参数的最小整数
-
floor
floor(参数只有一个) #向下取整,返回 <= 该参数的最大整数
-
truncate
truncate(数值,小数点后保留几位); #截断
-
mod
mod(被除数,除数) #取余,结果和被除数保持一致,因为是被除数剩下来的
3.日期函数
-
now
now(); #没有参数,返回当前系统日期+时间
-
curdate
curdate() #没有参数,返回当前系统日期
-
curtime
curtime() #没有参数,返回当前系统时间
-
year,month,day,hour,minute,second
year(时间) #获取指定时间的年
month() #获取指定时间的月
monthname() #获取指定月份名称
-
str_to_date
str_to_date(时间,‘%y-%c-%d指定的格式’) #将字符通过指定的格式转化成日期
-
date_format
date_formt(日期,‘%y年%m月%d日’) #日期转换为固定的格式
-
datediff
datediff(日期1,日期2) # 日期1 - 日期2得两者间隔天数
常用的日期格式
格式符 | 功能 |
---|---|
%Y | 四位的年份 |
%y | 两位的年份 |
%m | 月份(01,02,…11,12) |
%c | 月份(1,2,3,…,11,12) |
%d | 日(01,02,…) |
%H | 小时,24小时制 |
%h | 小时,12小时制 |
%i | 分钟,(00,01,…59) |
%s | 秒,(00,01,…59) |
4.其他函数
- version()
- database()
- user()
- password('字符‘):返回该字符的密码形式
- md5(‘字符’) :返回该字符的md5加密形式
5.流程控制函数
-
if
if(条件,条件为真输出结果,条件为假输出结果)
-
case
1.类似于 switch case效果
case 要判断的式子
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end as 别名
例子:
select salary,id,
case id
when 30 then salary*1.1
else salary
end as 新工资
2.类似于多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
when 条件n then 要显示的值n或语句n
else 要显示的值或语句
end as 别名
例子:
select salary,
case salary >1000 then ‘a’
else ‘d’
end as 新工资
注意case中是不要分号的,但是select后的字段需要逗号
2.分组函数
功能:用作统计使用,又称为聚合函数、统计函数、组函数
注意:和分组函数一同查询的字段有限制
参数:1.仅处理数值型的数据:sum avg 处理任何数据:max min count
2.全部都忽略了null值
3.可以和 distinct 搭配使用,剔除重复数据
-
sum
sum(只有一个参数) #求和
-
avg、
avg(只有一个参数) #平均值
-
max
最大值
-
min
最小值
-
count
计算非空数值个数
1.count(*) #统计行数,只要那一行有一列的数据存在,那这一行就统计进去(常用)
select count(*) from employees where id=‘90’; #统计id为90的行数
2.count(字段) : 统计该字段上非空值的个数
3.count(1) #统计所有行,相当于添加了一列的1,然后进行统计1的个数
5.分组查询
select 分组函数,分组列表1,分组列表2(要求出现在 group by 的后面,就是分组后的字段)
from 表名
[where 筛选条件]
group by 分组列表1,分组列表2 //将两个列表(分组字段)都一样的合并到一组,多个字段没有顺序要求
[having 分组后的筛选]
[order by 排序列表] //排序要放到最后
注意:
1.要求查询列表特殊,要求是分组函数和group by后出现的字段
2.having 和 group by 后面很少用别名,麻烦
3.记住最后加分号,
4.分组查询分为两类:分组前筛选、分组后筛选
数据源 位置 关键词
分组前筛选:原始表 group by子句前面 where
分组后筛选:分组后的结果表 group by子句后面 having
分组函数做条件,肯定是在having中
例子:
按照员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
select count(*) length(last_name) len_name
from employees
group by len_name
having count>5;
思路:
1.尽快读懂题意的方法就是把那个表格的表头画出来,都需要显示什么,可以尽快读懂题意
2.各个、每个;这类的题眼就是分组的依据
3.分清数据源,然后就是筛选的关键词用在哪里,分清条件用在哪里、
6.连接查询
- 又称:多表查询,查询的字段来自于多个表
1.笛卡尔
笛卡尔矩阵积:表1 有m行 表2 有n行 ,结果有 m*n行
原因:没有有效的连接条件
避免:添加有效的连接条件
分类:
按年代:
sq192标准 仅支持内连接,支持部分外连接(用于oracle\aqlserver,不适用mysql)
sq199标准 推荐
按功能:
内连接:
等值连接
非等值连接
自连接
外连接:
左 外连接
右 外连接
全 外连接(mysql不适用)
交叉连接:
2.aql192标准
select 查询列表 #查询条件需要限定,因为此处在代码from后执行
from 表1 别名,表2 别名,...
where 有效的连接条件 #连接条件,就是当做桥梁连接两个表,多的见备注
[and 有效的连接条件] #见备注
and 筛选条件
[group by 分组字段]
[having 分组后的筛选]
[order by 排序列表]
备注:
1.当连接的表超过三个的时候,连接条件超过三个,where只能连接两个,多的用and连接
2.多表最好先把各个表涉及的共同的字段列出来(好写连接条件),然后把不相同的字段列出来
3.执行思路见下图(我暂时这样子理解的)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2hv5JH34-1589825838881)(F:\go博客整理\数据库\sql基础语法\1588756677508.png)]
1.等值连接
有效连接为:
where 表1.key = 表2.别名
多表连接
- 多表连接的结果为多表的交集部分
- n 表连接,至少需要 n-1个连接条件,多个条件筛选,第一个用where,其余的用and
- 多表的顺序无要求
- 一般需要非表起别名
- 可以搭配前面介绍的所有字句(排序,筛选,分组)
2.非等值连接
有效连接:
where 非等值的连接条件
3.自连接
有效连接
from 表1 别名1;表1 别名2 #就在一个表中
where 非等值的连接条件
关系数据库三大完整性中的参照完整性
3.sql199
select 查询列表
from 表1 别名
连接类型 join 表2 别名 on 连接条件 #连接条件,就是当做桥梁连接两个表
连接类型 join 表3 别名 on 连接条件 #连接条件,就是当做桥梁连接两个表
...
[where 筛选条件]
[group by 分组条件]
[having 筛选条件]
[order by 排序类型]
连接类型:
内连接:inner(等值、非等值、自连接)
外连接:
左连:left[outer]
右连:right[outer]
全外:full[outer]
交叉连接:cross
1.内连接—等值连接
2.内连接—非等值连接
3.内连接—自连接
以上三个都是sql192标准是一样的,所谓的 内连接就是在连接两部分表的交集,表之间是要有链接的
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-smsBtqzZ-1589825838885)(F:\go博客整理\数据库\sql基础语法\1588828562914.png)]
4.外连接
听的课有问题,再说
主表到子表数据的映射,如果没有与之映射的数据就用null填充,并集
应用:一个查询一个表中有,另一个表中没有的记录
特点;
1.外连接的查询结果为主表中所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示 null
查询结果=主表所有的行,其中从表和它匹配的将显示匹配行,如果没有匹配的则显示null
外连接查询结果=内连接结果+主表中有而从表没有的内容
2.左外连接 :left join 左边的是主表
右外连接:right join 右边的是主表
全外连接:两边都是主表
主要信息在哪个表,哪个就是主表
3.一般用于查询除了交集部分的剩余的不匹配的行
3.左外和右外交换两个表的顺序,可以实现同样的效果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lMe2oWdn-1589825838891)(F:\go博客整理\数据库\sql基础语法\1588829850739.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SUuZEBVQ-1589825838906)(F:\go博客整理\数据库\sql基础语法\1588829820739.png)]
5.全外连接
相互映射,没有的数据使用null填充,相当于合/并集
查询的记录条数=主表和字表的并集部分,子表有的数据,主表没有也用null填充
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lT3vA4dk-1589825838914)(F:\go博客整理\数据库\sql基础语法\1588830527846.png)]
5.交叉连接
两个表没有顺序
select 查询列表
from 表1 别名
cross join 表2 别名;
7.子查询
-
出现在其他语句内部的select语句。子查询、内查询
外部的语句可以是insert 、update、delete、select等
-
外面是select语句,内嵌套其他select语句的查询。主查询、外查询
分类:
-
按照子查询出现的位置
select :仅支持标量子查询
from :支持表子查询
where 和 having :标量子查询,列子查询,行子查询
exists :表子查询
-
结果集的行列数不同
标量子查询(结果集有一行一列) :单行
列子查询 (结果集有一列多行):多行
行子查询 (结果集有多行多列)
表子查询 (结果集一般为多行多列)
-
只是记录语法格式,看之前已经有数据库基础了
1.where或having后面
1.特点
-
子查询放在小括号内
-
子查询一般放在条件的右侧
-
标量子查询,一般搭配单行操作符使用
< > <= >= <>
列子查询,一般搭配多行操作符使用
in any/some all
-
起始就是查询的嵌套,嵌套使用()把子查询括起来
where () and () #多条件
分组以后就可以使用 having
having () and ()
2.标量子查询
- 子查询优先于主查询
3.列子查询
- 多行子查询
- 多行操作符
操作符 | 含义 | 取值 |
---|---|---|
in / not in | 等于列表中的任意一个 | 所有值 |
and / some | 和子查询返回的某一个值比较 | |
all | 和子查询返回的所有值比较 |
a in () #a 取括号中值的一个
4.行子查询
多个字段当成一个字段
where (employees_id,salary)=(
select min(employee_id),max(salary)
from employees);
2.select
仅支持标量子查询
select d.*,(
select count(*)
from employees
)
from departments d;
3.from
就是将子查询充当一张表,要求必须起别名
from () 别名;
4.exists
相关子查询
exists(完整的查询语句) 结果:1或0
可以用in代替
select department_name
from departments d
where exists(子查询)
先执行外查询,然后根据相关字段在where里面过滤
8.分页查询
1.应用:
- 要现实的数据,一页显示不全,分页提交sql请求
2.特点:
- limit语句放在查询语句的最后
2.语法
执行顺序
7 select 查询条件
1 from 表
2 [join type] join 表2
3 on 连接条件
4 where 筛选条件
5 group by 分组字段
6 having 分组后的筛选
8 order by 排序的字段
9 limit 起始条件索引,要显示的条目个数(显示的行数),
#索引从0开始,0可以省略不写
#要显示的页数page,每页显示的条目数size limit (page-1*size size)
9.union联合查询
uinon 联合 合并:将多条查询语句的结果合并成一个结果
用于多个没有连接的表之间的查询,即:要查询的结果来自于多个表,且多个表没有直接的链接关系,单查询信息一致
select... #查询语句1
union
select... 查询语句2
union
select... 查询语句3
...
#其实就是将 or 的分句给拆开了
特点:
-
要求多条查询的条数(列数)是一致的
-
要求多条查询的每一列的类型和顺序最好是一致的
-
就是所有语句的并集,除掉重复值。
使用 union all可以包含重复项
5.DML语言学习
数据插入语言
插入:insert
修改:update
删除:delete
1.插入语句
1.经典插入方式
insert into 表名(列名1,列名2,…)values(值1,值2,…)
-
插入的值的类型要与列的类型一致或者兼容
-
不可以为 null 的列必须插入值,
可以为 null 的值,插入值的方式:
方式一:列下的值写 null
方式二:列不写,对应列的值也不写(可以为空的可以不用写)
-
列的顺序可以调换,下面写的对应的值就行,
列和值要一一对应
-
可以省略列名,默认是所有列,而且列的顺序和表中的列的顺序一致
2.方式二
insert into 表名
set 列名=表名,列名=表名,…
3.方式三:
insert into beauty(id,name)
select 26,‘周周’;
3.两种方式比较:
-
方式一支持多行插入,方式二不支持
insert into beauty values(23,'周周' 12,'大大')
2.方式一支持子查询,方式二不支持
insert into beauty(id,name)
select 26,'周周';
# 将查询的结果集,插入到beauty
2.修改语句(更新)
1.修改单表的记录
(1)update 表名
(3 )set 列=新值,列=新值,…
(2)where 筛选条件;
2.修改多表的记录
sql192语法
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件
sql199
update 表1 别名
inner |left |right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件
set 列=值,...
where 连接条件
and 筛选条件
3.删除语句
1.方式一:delete
1.单表的行删除
delete from 表名 [where 筛选条件] [limit 条目数] # 删除的是整行
2.多表的行删除
sql192语法:
delete 要删除的表的别名,要删除的表的别名
from 表1 别名 ,表2 别名
where 连接条件
and 筛选条件
sql199语法
delete 要删除的表的别名,要删除的表的别名
from 表1 别名
inner |left|join 表2 别名
on 连接条件
where 连接条件
and 筛选条件
2.方式二:truncate
truncate table 表名; #此语句不能添加where语句,就是清空表格,但是表格仍然还在
3.对比
1.假如要删除的表中有自增数列
* 如果使用delete删除,在插入数据,自增长列的值从断点开始
* 如果使用truncate删除,在插入数据,自增长列的值从1开始
2.truncate 无返回值 (受影响的行数值的反馈)
delete 有返回值
3.truncate删除不能回滚
delete删除可以回滚
4.truncate 的效率高
6.DDL学习
创建:create
修改:alter(修改的是结构,不是数据)
删除:drop
新建/删除 if exists 新建存在不报错不新建,删除不存在不删除不报错
1.库的管理
打开/指定库
use 库名;
创建
create database exists 库名; #创建库,在data文件夹中,库名不能重复,
create database if not exists 库名;#这个是容错性的,存在不创建,不存在就创建,不报错
修改(一般不用)
#数据库一般不做修改,修改库会导致数据丢失,一般不用
#真要是改的话,停止服务器,改服务器存的data里的库文件夹的名称,然后再重启服务器
#更改库的字符集
alter database 库名 character set 使用的字符集样式;
删除
drop database 库名;
drop database if exists 库名;#如果库存在,删除,不存在,也不报错
查看
show tables; #查看当前库
show tables from 库名; #查看其他库的所有表
2.表的管理
创建
create table 表名(
列名 列的类型[(长度)约束],#name varchar(20)
列名 列的类型[(长度)约束],#id int
...
列名 列的类型[(长度)约束])
表修改
alter table 表名 add|drop|modify|change column 列名 列类型 约束
-
修改列命名
alter table 表名 change column 列名 列数据类型
-
列的类型或约束
alter table 表名 modify column 列名 类型 约束
-
添加新列
alter table 表名 add column 新列名 类型 [first|after 指定字段名|默认last]
-
删除列
alter table 表名 drop column 列名称
-
修改表名
alter table 表名 rename to 新表名
删除
drop table 表名
drop table if not exists 表名
复制
1.仅仅复制表的结构
create table 新表名 like 旧表名
2.复制表的结构+数据
create table 新表明
select * from 旧表
select 字段
from 旧表
where 筛选条件
3.仅复制部分结构,部分数据
create table 新表明
select 字段
from 旧表
where 1=2;
# 条件是始终不会成立的,所以新建的表是只有 select里面的来自旧表的字段
查看
desc 表名 #查看表结构
select * from 表名 #查看表的内容
2.常见数据类型
数值型:
整型
小数
:定点数
:浮点数
字符型
较短文本:
char
varchar
较长文本:
text
blob(较长的二进制数据,照片)
日期:
1.整型
整数类型 | 字节 | 范围 |
---|---|---|
快捷记忆 | 一个字节8位 | 0算无符号的 有符号的关于零对称,但是无符号的多一个0, 最大的无符号数要减1 |
tinyint | 1 | 有符号-(2^4) ~ (2^4)-1 无符号0 ~ (2^ 8)-1 |
smallint | 2 | 有符号 无符号 |
Mediumint | 3 | 有符号 无符号 |
Int 、integer | 4 | 有符号 无符号 |
bigint | 8 | 有符号 无符号 |
常见使用
- 设置符号
t1 int[(长度)] [zerofill] #设置 带符号 的整型
t1 int[(长度)] [zerofill] [unsigned] #设置 不带符号 的整型
# [(长度)] 设置数据长度
#[zerofill] 当输入的数据位数,不够数据设置的长度的时候,在数据前面填充0(默认只支持正数)
#[unsigned] #设置 不带符号 的整型
-
报错
-
设置的数超出范围上限或者下限,会报错,默认的是插入的是 临界值,
负数是0,正数是最大的那个无符号数值
-
-
不设置默认长度,各个类型都有自己的默认长度
2.浮点型
浮点数类型 | 字节 | 范围 |
---|---|---|
float(M,D) | 4 | |
double(M,D) | 8 | |
定点数类型 | 字节 | 范围 |
---|---|---|
DEC(M,D) DECIMAL(M,D) | M+2 | 最大取值范围和double相同 给定decimal的有效取值范围由M和D决定 |
- M和D
- 可以省略
- M :整数部位位数+小数部位位数(5),超过范围插入的是临界值(如:整数要插入3位,你输入4位,显示的是999)
- D :小数点后保留的位数,四舍五入的
- 默认值:
- float double 会根据插入的数据的精度确定精度
- dec 默认是M为10,D默认是0
- 两者区别
- 定点型的精度高,如要求的精度高的数据:货币运算
- 数据类型选用原则
- 选择的数据类型越简单越好,能保存数值的类型越小越好
3.字符型
字符数:一个中文就是一个字符数,一个英文字母也是一个字符
字符串类型 | char(M) | varchar(M) |
---|---|---|
最多字符数 | M(可以省略,默认为1) | M(不可省略) |
描述及储存需求 | M为0~255 | M为0~65535 |
特点 | 固定长度的字符 | 可变长度的字符 |
空间消耗 | 比较耗费空间 | 比较节省 |
效率 | 高 | 低 |
binary 和 varbinary
保存较短的二进制
Enum类型(枚举)
枚举类型,要求插入的值必须是列表中指定的值之一,一次只能选取一个成员
列表成员数 | 字节储存 | |
---|---|---|
1~255 | 1字节 | |
255~65535(最多) | 2字节 |
set类型(集合)
与Enum类型相似,可以保存0~64个成员,一次可以选取多个成员,根据成员个数的不同,存储所占的字节也不同
成员数 | 字节数 |
---|---|
1-8 | 1 |
9-16 | 2 |
17-24 | 3 |
25-32 | 4 |
33-64 | 8 |
inset into tab_set values('a,b,c')
日期型
日期类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
date | 4 | 1000-01-01 | 9999-12-31 |
datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
timestamp | 4 | 19700101080001 | 2038年某个时刻 |
time | 3 | -838:59:59 | 838:59:59 |
year | 1 | 1901 | 2155 |
-
区分
timestamp 支持的时间范围小,取值范围19700101080001—2038年的从某个时间
datetime的取值范围是:
3.常见约束
-
约束
一种限制,用于限制表中的数据,为了保证表中数据的准确和可靠性
-
分类:
-
not null :非空,用于保证该字段的值不能为空
-
default :默认,用于保证该字段有默认值
-
primary key : 主键,用于保证该字段的值有唯一性,并且非空,起名字没效果,还是primary
-
unique : 唯一,用于保证该字段具有唯一性。可以为空
-
check : 检查约束(mysql不支持,使用不报错)
-
foreing key :外键,用于限制两个表的关系,用于保证该字段的值必须来自主表的关联列的值。在从表添加外键约束,用于引用主表某列的值
-
-
添加约束:
- 创建表时
- 修改表时
-
约束的添加分类:
列级约束 表级约束 位置 列的后面 所有列的下面 支持的约束类型 语法上都支持,但是外键约束没有效果 除了非空、默认,其他都支持 起约束名 不可以 可以,对主键无效 使用 一个字段可以追加多个,中间用空格断开,无顺序要求 -
创建表添加约束
create database students; #创建数据库 use stedents;#使用数据库 create table stuinfo( id int primary key,#设置列级约束主键, stuName varchar(20) not null #设置列级约束 非空 gender char(1) check(gender='男' or gender='女') #设计列级约束检查 age int default 18 #设置列级约束默认 majorId int foreign key references major(id) #列级约束外键,但是不支持(5.7版本后认为foreign key 错误语法) #表级约束语法 #[constraint 约束名] 约束类型(字段名),约束名不能重复 constraint pk primary key(id)#设置表级主键 constraint up unique(seat) #设置表级唯一键 constraint ck check(gender='男'or gender='女')#设置表级检查 constraint fk_stuinfo_major foreign key(majorid) references major(id)#表级外键
* 修改表时添加约束 mysql #1.添加列级约束 alter table 表名 modify column 字段名 字段类型 新约束 #添加表级约束 alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用] #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; #表级约束 1.alter table stuinfo modify column key(id); 2.alter table stuinfo add constraint 添加约束的列,起的名字(别名) primary key(需要添加约束的列) #4.添加唯一 #1.列级约束 alter table stuinfo modify column seat int unique; #2.表级约束 alter table stuinfo add unique(seat); #5.添加外键 alter table stuinfo add foreign key(majorid) references major(id) (#向表stuinfo添加列dept_id,并在其中定义foreign key 约束,与之相关联的列是stu表中的id列 alter table stuinfo add constraint fk_stuinfo_stu foreign key(dept_id) references stu(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.删除唯一 #1.列级约束 alter table stuinfo drop index 唯一键名; #5.删除外键 alter table stuinfo drop foreign key 外键约束名
-
-
主键和唯一性区别
唯一性 是否为空 一个表有几个 是否可以组合 主键 √ × 至多1个 可以,但不推荐 唯一 √ √ 可以有多个 可以,但不推荐 -
外键
-
用于限制两个表的关系,从表的字段值引用了主表的某字段值
-
要求在从表设置外键关系
-
从表的外键列表的类型和主表的关联列(被引用列)的类型要求一致或者兼容,名称无要求
- 主表的关联列(被引用列 )必须是以key(一般是主键或者唯一键)
-
插入数据时,先插入主表,在插入从表
删除数据,先删除从表,在删除主表
-
-
含外键删除,
#删除主表数据 #方式一:级联删除 外键添加子句 on delete cascade; 方式二:级联置空 外键添加子句 on delete set null
-
可以加入的数目
可以加入数目不限制,顺序也不限制。
7.TCL学习
-
TCL
- Transaction control language 事务控制语言
-
事务
一个或一组sql语句组成一个执行单元,每个mysql语句相互依赖,这个执行单元要么全部执行,要么全部不执行。即单个语句出现意外整个单元都会回滚(撤销),所有被影响的数据将返回事务开始以前的状态
-
innodb支持事务
1.标识列(自增)
- 又称自增长列,
- 可以不用手动插入值,系统提供默认的序列值
1.标识列不一定必须要跟主键搭配,但是必须是一个key
2.一个表中只能有一个标识列
3.标识列的类型只能是数值型
4.查看标识列:
show variables like '%auto_increment%'
设置步长:
set auto_increment_increment=3
也可以通过手动插入值,设置起始值
#1.创建表时设置
create table if exists 表名(
列名 类型 auto_increment)#id int unique auto_increment
#2.修改表时设置
alter table 表名 modify column 列名 列数据类型(int) [约束类型] auto_increment
#3.删除标识列(修改时)
直接把修改表时设置标识列里面的auto_increment去掉,然后刷新就行
2.事务
1.acid属性
-
原子性(atomicity)
原子性是事务不可分割的工作单位,事务中的操作要么都发生,要么都不发生
-
一致性(comsistency)
事务必须使数据库从一个一致性状态转换到另一个一致性状态
-
隔离性(isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即使一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能相互干扰。
-
持久性(durability)
是指一个事务一旦被提交,它对于数据库中数据的改变就是永久的,接下来的其他操作和数据库故障不应该对其有任何影响
原子性 一个事务不可再被分隔,要么都执行,要么都不执行 一致性 事务必须使数据从一个一致性状态转换到另一个一致性状态 隔离性 一个事务的执行不受其他事务的影响 持久性 一个事务一旦被提交,则会永久改变数据库的数据
2.分类
隐式(自动)事务:事务没有明显的开启和结束标记
比如:insert 、update 、delete语句
显示事务:事务有明显的开启和结束标记
前提:必须先禁用自动提交功能
设置:set autocommit=0(只对当前事务有作用,每次打开就要重新设置)
3.使用步骤
#1.开启事务
set autocommit =0 #禁用自动提交
start transaction; #开启事务,可以省略
#2.编写事务中的sql语句(即需要整体执行的语句)
(select、insert、update、delete)#基本只支持这四个,其实select有没有都可以,没影响查看
语句1
语句2
...
语句3
#设置回滚点
savepoint 回滚点名称
#3.结束事务
commit;#提交事务,
rollback;#回滚事务,结束事务之前存在内存中
rollback to 回滚点名称 #回滚到指定的地方
4.并发事务
1.事务并发问题发生原因
- 多个事务 同时操作 同一个数据库 数据,当这些事务访问数据库中相同的数据时,如果不采取必要的隔离机制,就会导致各种并发问题
2.并发问题有哪些
(两个事务T1、T2)
- 脏读: T1读取了已经被T2更新单还没有被提交的字段之后,若T2回滚,T1读取的内容就是临时无效的(一个事务读取了其他事务没有提交的数据,读到的是其他事务“更新”的数据,针对一条数据)
- 不可重复读:T1读取了一个字段,然后T2更新了该字段,之后,T1再读取同一个字段,值就不同了(一个事务多次读取,结果不一样)
- 幻读:T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行,之后,如果T1再次读取同一个表,就会多出几行。(一个事务读取其他事务还没有提交的数据,只是读到其他事务“插入”得数据,针对多条数据)
3.解决
-
数据库事务的隔离性:数据库必须具有隔离并发运行各个事务的能力,使得他们不会相互影响,以避免各种并发问题
-
隔离级别:一个事务和其他事务隔离的程度。不同的隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但是并发性就越弱
隔离级别 中文 描述 出现的问题 read unicommitted 读未提交数据 允许事务读取其他事务提交的变更 脏读、幻读 read commmited 读已提交 只允许事务读取已经被其他事务提交的变更 避免脏读,会出现不可重复读、幻读 repeatable read 可重复读 确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,进制其他事务对这个字段进行更新 可避免脏读、不可重复读,但是仍有幻读 serializable 串行 确保事务可以从一个表中读取相同的行,在这个事务持续期间,进制其他事务对该表进行插入如、更新和删除操作 并发问题可以被避免,但是性能是非低下 - Oracle 支持:read commited(默认)、serializable
- mysql 支持:四种,repeatable read(默认)
#每启动一个mysql程序,就会获得一个单独的数据库连接,每个数据库连接都有一个全局变量@@tx_isolation,表示当前事务隔离级别 #查看当前的隔离级别 select @@tx_isolation #设置当前mysql连接的隔离级别 set transaction isolation level 隔离级别; #设置数据库的隔离级别 set session|global transaction isolation level 隔离级别
- Oracle 支持:read commited(默认)、serializable
5.事务中删除
3.视图
1.含义
mysql5.1版本出现的新特性,和普通的表一样使用,本身是一个虚拟表,它的数据来自表,是动态生成的,只保留了sql逻辑,不保存查询结果。
2.应用场景
- 多个地方用到同样的查询结果
- 该查询结果使用的sql语句比较复杂
3.使用
#就相当于函数的封装,可以调用函数的思想,达到代码复用
#最大作用就是查询,更新太麻烦,直接再写一个就行了
#1.创建
create view 视图名 as 视图内部的代码(查询语句)
#2.调用
select * from 视图名 # *是指视图中包含的字段
select 视图名.'视图中的字段名' from 视图名
#3.修改
#方式一
create or replace view 视图名 as 视图语句 #视图存在就修改,不存在就创建
#方式二
alter view 视图名
#4.删除
drop view 视图名,视图名,视图名...
#5.查看
#方式一
desc 视图名
#方式二
show create view 视图名\G #格式化查出来的视图创建过程
#6更新
#创建账号需要权限,主要是用来查询用的
#视图是一个虚拟表,增删改查和表是一样的操作,只不过表名变成1视图名(虚拟表名称),但是直接更改会影响原始数据的。基本上都是不能更新的,对其更新也不能机械理解为对原始表的更新
#不能进行更新的
1.分组函数、distinct、group by、having、union、union all
2.join
3.常量视图
4.where后的子查询用到了from中的表
5.用到了不可更新的视图
4.特点
- 重用sql语句,简化了操作
- 把封装的视图当成一个新的表,很多子查询可以使用视图代替
- 注意注释,容易忘记查询的功能
5.对比表
视图 | 表 | |
---|---|---|
创建关键字 | create view | create table |
实际占用物理空间 | 只保存了sql逻辑 | 保存了数据 |
使用 | 一般只做查询用 | 增删改查 |
8.变量
跟普通的编程语言中的变量差不多,比如:Java中的变量
1.分类
- 系统变量
- 全局变量
- 回话变量
- 自定义变量
- 局部变量
- 用户变量
2.详解
1.系统变量
- 说明:是由系统提供的,不是用户定义的,属于服务器层面
- 作用域
- 全局变量:服务器每次启动将为所有的全局变量赋初始值,针对所有的回话(连接)有效,但是不能跨重启
- 回话变量:仅仅针对当前回话(连接)有效
使用语法:
#以下global|session如果都不写,可以省略,默认session
#1.查看所有的系统变量
show global variables;#全局变量
show [session] variables #回话变量,默认回话
#2.查看满足条件的部分系统变量
show global|session variables like '%char%'
show session variables like '%char%'
#3.查看指定的某个系统变量的值
select @@global|session.系统变量名
#4.为某个系统变量赋值
#方式一:
set global|session 系统变量名 = 值
#方式二:
set @@global|session.系统变量名 = 值
注意:
如果是全局级别,需要加上global,
如果是回话级别,需要加上session,
如果不写,默认的是session
2.自定义变量
-
说明:变量时用户定义的,不是由系统的
-
步骤:
- 声明
- 赋值
- 使用(查看、比较、运算等)
-
区别两种自定义变量
用户变量 局部变量 作用域 当前会话中 begin end 中 定义和使用的位置 会话中的任意位置 只能用在 begin end 中的第一句话 @符号 必须加@ 一般不加,只有select使用 类型 不限定 需要限定 -
作用域:
-
用户变量:很对当前回话(连接)有效,同于回话变量作用域
-
局部变量:只能在定义它的begin end 中有效
-
#用户变量
#1.声明并初始化
set @用户变量名 = 值
set @用户变量名 := 值
select @用户变量名:= 值
#2赋值
#方式一
就是声明并初始化,一样的操作
#操作二
select 字段1,字段2 into 变量名1,变量名2 from 表名;#注意是一个
#3使用
select @用户变量名 #系统变量时2个@
#局部变量
#注意,只用将用户变量的 @ 去掉就行
#1.声明并初始化
declare 变量名 类型
declare 变量名 类型 default 默认值
#2赋值
#方式一
set 用户变量名 = 值
set 用户变量名 := 值
select @用户变量名:= 值
#操作二
select 字段 into 变量名 from 表名;#注意是一个
#3使用
select @用户变量名
9.存储过程和函数.
- 存储过程和函数:类似Java中的方法
- 好处:
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的链接次数,提高了效率
1.存储过程
- 说明:实际开发中一般不使用存储过程,最重要的问题就是不容易维护,比如阿里就禁用了存储过程…
1.含义
- 一组预先编译好的sql语句的集合,理解成批处理语句
- 可以有0个 返回,也可以有多个返回
2.使用
#1.创建语法
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的sql语句)
end
注意:
1.参数列表有三部分
参数模式
in :进(读) ,该参数可以作为输入,就是该参数需要调用方(就是call ) 传入值
out :出(写) ,该参数可以作为输出,该参数可以作为返回值
inout :既进又出(可读可写):改参数可作为输入也可作为输出,该参数需传入值,也可返回值
参数名
参数类型
举个栗子 :instuname varchar(20)
2.如果存储过程体仅仅是一句话,begin end 可以省略
* 存储过程体中的每条sql语句的结尾要求必须加分号
* 存储过程的结尾可以使用 delimiter 重新设置
delimiter 结束标记 #这个标记可以用你想用的任何字符
* sqlyog 不支持结束标记语法,需要到windows的dos窗口中使用
#2.调用语法
call 存储过程名(实参列表);
#3.删除
drop procedure 存储过程名字 #只能一次删除一个,不能同时一条语句删除多个
#4.查看存储过程信息
show create procedure 存储名; #desc是查看表结构,而储存结构只是sql语句
#5.修改
里面的语句是不能修改的,直接删了重新建就行
#栗子
#1.空参列表
#插入到admin表中两条记录
delimiter $
create procedure myp1()
begin
insert into admin(name,'password')
values('lili,123'),('baba','456')
end $ #需要到windows的dos窗口中使用
#有参数方法后,也需要创建两个变量来表示这个值
call myp1()$ #存储过程添加的标记,在调用的时候也要加上
#2.创建带 in模式参数的存储过程
2.函数
- 有且仅有有1个返回
#1.创建函数
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
注意:
1.参数列表
参数名字
参数类型
2.函数体肯定有 return 语句,如果没有会报错
如果return语句没有放在函数体的最后也不会报错,但是不建议
3.函数体中仅有一句话,则可以省略 begin end
4.使用delimiter语句设置结束标记
delimiter 结束标记;
#2.调用语法
select 函数名(参数列表);
#3.查看函数
show create function 函数名;
#4.删除函数
drop function 函数名;
举个栗子
#出现1418这个错误的,输入set global log_bin_trust_function_creators=1;开启函数功能
#1.无参有返回值
#案例:返回公司员工个数
delimiter $
create function myf1() returns int
begin
declare c int default 0;#定义变量
select count(*) int c; #赋值
from employees;
return c;
end $
select myf1()$
#2.有参数有返回
#根据员工名,返回它的工资
create function myf2(empName varchar(20)) returns double
begin
set @sal=0;#定义用户变量
select salary;
from employees;
where last_name = empName;
return @sal;
end $
select myf2('k_ing') $
10.流程控制结构
- 三种结构可以参考学习编程(Java go等)基础的学习。
- 只写怎么应用,
1.顺序结构
2.分支结构
1.if 函数
if(表达式1,表达式1成立返回的值,表达式1不成立返回的值)
- 任何地方
2.if 结构
elseif 条件2 then 语句2;
[else 语句n;]
end if ;
注意:
1.应用在begin end 中
2.case
#方式一
#类型Java中的switch语句,一般用于实现等值判断
case 变量|表达式|字段
when 要判断的值 then 返回的值或语句;
when 要判断的值 then 返回的值或语句;
...
else 要返回的值或语句
end case;
#方式二
#类似Java中的多重 if 语句,一般用于实现区间判断
case
when 要判断的条件 then 返回的值或语句;
when 要判断的条件 then 返回的值或语句;
...
else 要返回的值或语句;
end case;
特点
1.可作为表达式,嵌套在其他语句中,可以放在任意的位置
2.作为独立的语句使用,只能放在 begin end中
3.若when中的值满足或者成立,则执行对应的then后的语句,并结束case
4.else可以省略,若省略了,并且所有的when条件都不满足,则返回null
3.循环结构
1.分类
#循环结构
while loop repeat
#循环控制
iterate 类似于 continue ,继续,结束本次循环,进行下一个
leave 类似于 break ,结束当前的循环
1.while
[标签:]while 循环条件 do
循环体
end while [标签];
2.loop
[标签:] loop
循环体
end loop [标签];
1.用来模拟简单的死循环
3.repeat
[语法:] repeat
循环体
until 循环结束条件
end if [标签];
案例:批量插入,根据次数插入到admin中多条记录
delimiter $
create procedure pro_while1(in insertCount int)
begin
declare i int default 1;
a:while i<=insertCount Do
insert into admin(name,word)
values(concat('lili',i),'111');
if i>=20 then leave a;
end if;
set i=i+1;
end while a;
end $
11触发器
需要补充
参考
- 尚学堂课件
- mysql 必知必会