sql基础语法

`	

mysql(5.5)基本语法

前言

1. 知识目录
2. 这是平时学习总结的地方，用做知识库
3. 平时看到其他文章的相关知识，也会增加到这里
4. 随着学习深入，会进行知识拆分和汇总，所以文章会随时更新
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

1. 用于连接条件表达式

   ​ select name,salary from employees where salary>100 and salary<900;

   ​ 选出 name 和 salary 中salary>100 and salary<900的

2. && 和 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

1. asc 升序 desc 降序 默认：升序

2. 排序列表：单个字段、多个字段、表达式、函数、别名，一般在最后，limit字句除外

3. 多个字段排序规则，类似多级关键词

   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 的分句给拆开了

特点：

1. 要求多条查询的条数（列数）是一致的

2. 要求多条查询的每一列的类型和顺序最好是一致的

3. 就是所有语句的并集，除掉重复值。

   使用 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.两种方式比较：

1. 方式一支持多行插入，方式二不支持

   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 列名 列类型  约束

1. 修改列命名

   alter table 表名 change column 列名 列数据类型
   

2. 列的类型或约束

   alter table 表名 modify column 列名 类型 约束
   

3. 添加新列

   alter table 表名 add column 新列名 类型 [first|after 指定字段名|默认last]
   

4. 删除列

   alter table 表名 drop column 列名称
   

5. 修改表名

   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	有符号 无符号

常见使用

1. 设置符号

t1 int[(长度)] [zerofill] 		     #设置   带符号   的整型
t1 int[(长度)] [zerofill] [unsigned]   #设置  不带符号   的整型
# [(长度)] 设置数据长度
#[zerofill] 当输入的数据位数，不够数据设置的长度的时候，在数据前面填充0（默认只支持正数）
#[unsigned]   #设置  不带符号   的整型

2. 报错

   • 设置的数超出范围上限或者下限，会报错，默认的是插入的是 临界值，

     负数是0，正数是最大的那个无符号数值

3. 不设置默认长度，各个类型都有自己的默认长度

2.浮点型

浮点数类型	字节	范围
float(M,D)	4
double(M,D)	8

定点数类型	字节	范围
DEC(M,D) DECIMAL(M,D)	M+2	最大取值范围和double相同 给定decimal的有效取值范围由M和D决定

1. M和D
   • 可以省略
   • M :整数部位位数+小数部位位数（5），超过范围插入的是临界值（如：整数要插入3位，你输入4位，显示的是999）
   • D :小数点后保留的位数，四舍五入的
2. 默认值：
   • float double 会根据插入的数据的精度确定精度
   • dec 默认是M为10，D默认是0
3. 两者区别
   • 定点型的精度高，如要求的精度高的数据：货币运算
4. 数据类型选用原则
   • 选择的数据类型越简单越好，能保存数值的类型越小越好

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

1. 区分

   timestamp 支持的时间范围小，取值范围19700101080001—2038年的从某个时间

   datetime的取值范围是：

3.常见约束

1. 约束

   一种限制，用于限制表中的数据，为了保证表中数据的准确和可靠性

2. 分类：

   • not null :非空，用于保证该字段的值不能为空

   • default :默认，用于保证该字段有默认值

   • primary key : 主键，用于保证该字段的值有唯一性，并且非空，起名字没效果，还是primary

   • unique : 唯一，用于保证该字段具有唯一性。可以为空

   • check : 检查约束（mysql不支持，使用不报错）

   • foreing key :外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值。在从表添加外键约束，用于引用主表某列的值

3. 添加约束：

   • 创建表时
   • 修改表时

4. 约束的添加分类：

   	列级约束	表级约束
   位置	列的后面	所有列的下面
   支持的约束类型	语法上都支持，但是外键约束没有效果	除了非空、默认，其他都支持
   起约束名	不可以	可以，对主键无效
   使用	一个字段可以追加多个，中间用空格断开，无顺序要求

   • 创建表添加约束

     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 外键约束名
     

5. 主键和唯一性区别

   	唯一性	是否为空	一个表有几个	是否可以组合
   主键	√	×	至多1个	可以，但不推荐
   唯一	√	√	可以有多个	可以，但不推荐

6. 外键

   • 用于限制两个表的关系，从表的字段值引用了主表的某字段值

   • 要求在从表设置外键关系

   • 从表的外键列表的类型和主表的关联列（被引用列）的类型要求一致或者兼容，名称无要求

     • 主表的关联列（被引用列 ）必须是以key(一般是主键或者唯一键)

   • 插入数据时，先插入主表，在插入从表

     删除数据，先删除从表，在删除主表

7. 含外键删除,

   #删除主表数据
   #方式一：级联删除
   外键添加子句 on delete cascade;
   方式二：级联置空
   外键添加子句 on delete set null
   

8. 可以加入的数目

   可以加入数目不限制，顺序也不限制。

7.TCL学习

1. TCL

   • Transaction control language 事务控制语言

2. 事务

   一个或一组sql语句组成一个执行单元，每个mysql语句相互依赖，这个执行单元要么全部执行，要么全部不执行。即单个语句出现意外整个单元都会回滚（撤销），所有被影响的数据将返回事务开始以前的状态

3. 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 隔离级别
  

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. 提高代码的重用性
  2. 简化操作
  3. 减少了编译次数并且减少了和数据库服务器的链接次数，提高了效率

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 必知必会

跳转

知识目录