mysql学习笔记

一.基础

1.基本查询

   1.查询的列表可以是：表中的字段、常量值、表达式、函数
   2.查询的结果是一个虚拟的表格
   3.查询前选择库保险一些 use ctms
   4.有些字段名可能和mysql的关键字一样，可以使用卓分号也就是键盘1左边的那个键

select `name` from biao
select 100  select '字符'
select 100+200  select 9%5 
select VERSION()

2.别名(as或者空格实现)

select 9%5 结果  
select 9%5 as 结果
如果别名包含关键字，特殊符号就用引号引起来
select 9%5 as out put
select 9%5 as 'out put'

3.去重distinct（ dis tin ct 抵死 庭 柯特）

select distinct last_modified_by from bill_common

4.+ 之作用于运算

select 100+20     数字加数字，正常计算
select '100'+20   可以转换为数字的字符，正常计算
select '字符'+20  不可以转换为数字的字符，字符=0，0+20计算
select null+20    null加任何都为null

5.字段拼接为一个新字段 concat()

ifnull()函数解决字段有null的情况
select concat('a','c',ifnull(null,'是null要替换的值')) as 拼接 #如果拼接的字符或者字段有null的话，拼接结果都是null
select concat('a','c') as 拼接
select concat(created_by_name,'--',created_date) 创建人时间拼接（加双杠，注意带引号） from bill_common

6.查看表结构 desc

desc groups

7.条件查询

select 查询列表 from 表名 where 筛选条件
		简单条件运算符 ：> < = != <>（不等于）  >= <=
		逻辑运算符 ：&& || !
		关键字 ：
			like 模糊查询 和通配符一起使用 
				% 表示任意多个字符
				_ 表示单个字符的
				例：包含2，开头2，结尾2
					'%2%','2%','%2'
				例：第三个字符为2，第6个字符也为2
				 '__2__2%'
				例子：第二个字符为_
				斜杠转移 '_\_%'
				自定义转移字符 '_&_%' escape '&'
			between and 在某某之间 包含临界值
			in  在（这些之间 等价于=，只要有一个等于就能查询出数据）
			is null is not null 
				这2个用于判断空或者非空，=不能作用于null

8.排序查询order by

【order by 一般放在查询语句的最后面，limit 除外】
【asc:顺序 desc：倒叙]不写默认顺序】
select distinct 列 from 表 order 列 by desc

【order by 支持表达式 ifnull(bill_invoicing_amount,0)+ifnull(0,bill_sub_add_amount)和别名 总金额 排序】
select ifnull(bill_invoicing_amount,0)+ifnull(0,bill_sub_add_amount) as 总金额	
from bill_invoice  order by 总金额 desc

【函数排序 length() 获取字段长度的函数】
select length('字节长度')
select length(address) 字节长度,address from bill_invoice order by length(address) desc
【多个字段排序，从左到右依次排序，在左边的列排完后，有相等的数据中，右边的列进行排序】
select length(address) 字节长度,address,bill_amount from bill_invoice order by length(address) desc,bill_amount asc

二.常见函数

1.字符处理函数

1.concat()：拼接字段
例： select concat('李','-','hehe')
2.lenght()：获取参数的字节（utf8的中文默认3字节，英文1字节）长度
例： select length('雅')
3.upper(),lower():字母转大写和字母转小写
例：select upper('hjKL')
		select lower('hKJ')
4.substr():字符串截取
【sql语法的索引从1开始】
【substr()方法被重载过多次，参数不同用法也不一样】
例：
	【字符，数字   意思是截取索引为7的后面所有字符（包含索引7的字符）】
	 select substr('狂风爵溪站-亚索',7) 英雄名
	【字符，数字,数字   意思是截取索引为1，5位长度的字符（索引1-5的字符）】
	 select substr('狂风爵溪站-亚索',1,5) as 大招
	【显示姓氏的首写字母并且大写，以下划线拼接名，名小写】
	 select concat(upper(substr(surname,1,1)),'_',lower(`name`)) from test
	 
5.instr():返回字符串（整体查找）第一次出现的索引，没找到则返回0
select instr('真人是个人才啊','人才')
6.trim() :去除前后的字符
select trim('a' from 'aaaaa打aa的aaaaa')
7.lpad():用指定的字符实现左填充指定长度 rpad():右填充和左边相反
select lpad('测试',5,'*')
【如果指定长度比已存在的数据的字符长度还要短，则从右边截断字符串】
select lpad('测试',1,'*')
8.replace():替换
select replace('阿强爱上了阿珍，爱上了阿珍','阿珍','阿美') as tt

2.数学函数

1.round() :四舍五入，默认取整,第二个参数为保留2位小数
select round(1.33)
select round(-2.339,2)

2.ceil()：向上取整,返回大于等于参数的最小整数，1.00=1
select ceil(1.00)
select ceil(1.002)
select ceil(-1.002)

3.floor():向下取整，返回小于等于参数的最大整数
select floor(-1.002)
select floor(1.002)

4.truncate(): 指定保留几位小数，后面的小数位直接截断
select truncate(1.669,2)

5.mod():取余数 被除数的正负决定余数的正负
select mod(10,-3)
select mod(10,3)
select mod(-10,-3)

3.日期函数

select now() ：返回当前系统年月日时分秒
select curdate():返回当前系统年月日
select curtime():返回当前系统时分秒

select year('2021-1-1')
select month('2021-1-1')
select day('2021-1-1')

str_to_date():将字符通过指定的日期格式转化为日期
select str_to_date('2021-1-2','%Y-%m-%d')
select str_to_date('4-1-2021 19:41:38','%m-%d-%Y %H:%i:%s')
select created_date from bill_invoice where  created_date= str_to_date('4-1-2021 19:41:38','%m-%d-%Y %H:%i:%s')

date_format():将日期转为字符
select date_format(now(),'%Y年/%m月/%d日')
select date_format(now(),'%Y%m%d')

4.流程控制函数

【if 函数：表达式，满足表达式返回的结果，不满足表达式返回的结果】
select if(10>5,'大','小')

【case 函数】
【 写法一 ：
case 要判断的字段或者表达式
when 常量1 then 要显示的值或者语句（语句的话要加分号）
when 常量2 then 要显示的值或者语句
else 要显示的值或者语句（默认情况）
end  （结尾）】
例：
select id,cas 原始cas,
case id
when 1 then cas*1
when 2 then cas*2
else cas+1
end as 新cas
from test

【 写法二 ：
case 
when 条件1 then 要显示的值或者语句（语句的话要加分号）
when 条件2 then 要显示的值或者语句
else 要显示的值或者语句（默认情况）
end  （结尾）】
例：
select id,
case 
when cas>15 then 'A'
when cas>=10 then 'B'
when cas>8 then 'C'
else 'D' 
end 等级
from test

5.聚合函数

sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
1.基本使用
select sum(cas) 和,max(cas) 最大值,min(cas) 最小值,round(avg(cas),2) 平均数保留2位,count(cas) 个数
from test
2.特点
sum、avg 一般处理数值型
max、min、count 可以处理任何类型
3.空值处理
sum、avg、max、min、count都忽略了空值
4.和distinct搭配
select sum(distinct cas), sum(cas) from test
5.count函数的具体介绍
【count(cas)：统计指定列的行数，count(*)：统计所有的行数，count(1)：在查询的表的最前面加一列，每行的数据都为1，然后统计1的个数】
select count(cas),count(*),count(1)
from test
效率：count(*)  >= count(1) >count(字段)

6.和聚合函数一同查询的字段要么是聚合函数，要么是分组 group by 后的字段

三.分组查询group by

1.语法

	select 聚合函数，列（要求出现在group by 的后面）
	from 表
	where 筛选条件
	group by 分组的列表
	
【筛选条件分2类】
						数据源           位置              关键字
分组前筛选  原始表           group by子句前面  where
分组后筛选  分组后的虚拟表   group by子句后面  having

聚合函数做条件一定放在having后面
能在分组前筛选的就在分组前筛选
group by 可以支持多个字段或者表达式排序，也能支持排序（排序order by 放在最后面）

2.按照字段分组

例1：查询每个部门的平均工资（分组前的筛选）
select round(avg(xin_zi),2),bu_men
from groups
group by bu_men

例2：查询邮箱中包含a字符的，每个部门的最高工资
select max(xin_zi),bu_men
from groups
where you_xiang like '%a%'
group by bu_men

例3：查询哪个部门的员工个数>2（分组后的筛选）
分析：
先查每个部门员工个数
然后筛选
注意：group by 前面的筛选是where，是筛选分组前的原始表，分组后会形成一个虚拟的表，这个表只能用having 进行分组后筛选
select bu_men , count(*)
from groups
group by bu_men
having count(*)>2

例4：查询每个部门有奖金的员工的最高工资>800的部门编号和最高工资
select max(xin_zi),bu_men_id,bu_men
from groups
where jiang_jin is not null
group by bu_men
having max(xin_zi)>800

3.按照函数分组

例1：按照员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于2的有哪些
select count(*) 员工个数,length(`name`) 姓名长度
from groups
group by length(`name`) 
having 员工个数>2

4.按照多个字段分组

例1：查询每个部门，每个等级的平均薪资，和相同部门相同等级的人数，并用薪资高低排序
select bu_men,deng_ji,avg(xin_zi) 平均薪资,count(*) 相同部门相同等级的人数
from groups
group by bu_men,deng_ji
order by 平均薪资 desc

四.多表连接查询

1.语法分类

按照年代分类
sql 1992 标准：mysql只支持内连接
sql 1999 标准：mysql支持内连接+外连接（左外连和右外连）+交叉连接
按照功能分类
内连接：
	等值连接
	非等值连接
	自连接
外连接
	左外连接
	右外连接
	全外连接
交叉连接

2.92年sql语法几种连接写法（除了这个目录下其他都是99年写法）

多表连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要给表取别名
可以搭配排序、分组、筛选等子句使用
92年sql语法
1.等值连接
select bs.bill_common_no from bill_common b ,bill_common_shift bs where bs.bill_common_no=b.bill_common_no 
2.非等值连接
select sw.project_name from kk_shift_waybill sw,kk_ctms_shift s where s.date between sw.date and sw.date
3.自连接
例：查询出发地和到达地相同的调度单
select * from kk_ctms_shift chu,kk_ctms_shift dao where chu.send_province_code=dao.arrive_province_code

3.99年sql语法

	select 查询列表 
	from 表1
	【连接类型】 join 表2 
	on 连接条件
	where 【筛选条件】
	【group by】
	连接类型关键字
	内连接   inner
	左连接   left
	右连接   right
	全外连接 full
	交叉连接 cross （就是笛卡尔积 2表相乘）
	

4.内连接

等值连接(分组+筛选+2表连接)
select count(*) 公司下调度单数量,s.company_no 公司编号 from kk_shift_waybill sw inner join kk_ctms_shift s on s.shift_no=sw.shift_no
group by s.company_no having count(*)>100

等值连接(分组+筛选+3表连接)
select sw.project_name,count(sw.waybill_no) from kk_shift_waybill sw 
inner join kk_ctms_shift s   on s.shift_no=sw.shift_no
inner join kk_ctms_waybill w on w.waybill_no=sw.waybill_no
where sw.project_name is not null
group by sw.project_name
having count(sw.waybill_no)>100

4.外连接（左右）left right

左连接以左边的表为主表，右连接以右边的表为主表，如果副表和主表没有匹配的数据，则以null填充

select count(*) from kk_ctms_waybill
select count(*) from kk_shift_waybill

select distinct w.waybill_no,w.created_date 
from kk_shift_waybill sw 
left join kk_ctms_waybill w on
w.waybill_no=sw.waybill_no 
where w.waybill_no like '%99' and w.created_date  between '2020-12-18 00:50:52' and '2021-12-18 00:50:52'

五.子查询

概念：

	出现在其他语句内部的select语句，称为子查询或者内查询
	内部嵌套其他select语句的查询，称为外查询或者主查询
	主查询使用子查询的结果，子查询运行优先
分类：
按子查询出现的位置
	select 后面
		仅仅支持标量子查询
	from 后面
		支持表子查询
	where或者having后面 <重点>
		支持标量子查询（单行）<重点>
		列子查询（多行）<重点>
		行子查询
	exists后面（相关子查询）
		支持表子查询
按结果集的行列数but
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集只有一行多列）
	表子查询（结果集一般为多行多列）

1.where或者having后面的子查询

（1）.标量子查询（单行子查询）

 注意：只能有一行一列的值，且使用单行操作符><=等进行比较
 例1：谁的工资比黄一高
 select `name`,xin_zi from groups where xin_zi>(select xin_zi from groups where `name`='黄一')
 
 例2: 查询工资比黄一高且等级也比黄一高的姓名、薪资和等级
 select `name`,xin_zi,deng_ji from groups where xin_zi>(select xin_zi from groups where `name`='黄一')and deng_ji>(select deng_ji from groups where `name`='黄一')
  
 例3：查询工资最低员工的姓名和部门
 select `name`,bu_men,xin_zi from groups where xin_zi=(select min(xin_zi) from groups)
 
 例4：查询最低工资大于人事部的最低工资的部门id和部门最低工资
 select min(xin_zi),bu_men,bu_men_id from groups group by bu_men having min(xin_zi)>( select min(xin_zi) from groups where bu_men='人事部')

（2）列子查询（多行子查询）

多行操作符
 in :在某某之间存在就满足条件
	例 1 in (1,2,3) 满足
 any some ：和子查询的结果的某一个值做比较 
  例15 > any(10,15,20) 满足
 all ：和子查询的结果的所有值做比较 
  例15 > all(10,15,20) 不满足
例1：查询部门id为20或者70的部门中所有的员工姓名
select `name`,bu_men from groups where bu_men in (select bu_men from groups where bu_men_id in (20,70) group by bu_men)

例2：查询比人事部员工工资高的员工的所属部门和工资
select `name`,bu_men,xin_zi from groups where xin_zi>any(select xin_zi from groups where bu_men='人事部')

例3：查询比人事部所有员工工资都要高的员工的所属部门和工资
select `name`,bu_men,xin_zi from groups where xin_zi>all(select xin_zi from groups where bu_men='人事部')

3.行子查询(使用场景较少)
例1：查询员工编号最小，并且工资最高的员工信息
select * from groups where (bian_hao,xin_zi)=(select min(bian_hao),max(xin_zi) from groups)

2.select后面的子查询

例:查询每个部门的员工个数（强行想的场景）
select * ,(select count(*) from groups gs where gs.bu_men=g.bu_men ) 部门员工个数 from groups g

3.from后面的子查询

注意：from 后面必须要给子查询结果取别名
例：查询每个部门的平均工资的工资等级
select bu_men,round(avg(xin_zi),2) from groups  group by bu_men
select * from dengjiselect ag.*,d.deng_ji from (select bu_men,round(avg(xin_zi),2) as 平均薪资 from groups  group by bu_men) ag inner join dengji d on ag.平均薪资 between d.down and d.up

4.exists后面（相关子查询）

select exists(select xin_zi from groups where xin_zi=800)  子查询有查询数据则为true 返回1
select exists(select xin_zi from groups where xin_zi=8100) 子查询有查询数据则为false 返回0

五.分页查询 limit

【第一个参数为起始索引，从0开始，第二个参数为要显示的条数】
例1：查询前3条的员工
select * from groups limit 0,3
select * from groups limit 3
例2：查询第2条到第6条的信息
select * from groups limit 1,5

六.联合查询 union

概念：将多条查询语句的结果合并成一个结果
语法：
	查询语句1
	union
	查询语句2
	union
	查询语句3
	--------
使用场景：
	要查询的结果来自于多个表，且多个表之间没有直接的连接关系，但查询的信息一致
	
注意:
	多条查询语句的查询列数要一样
	多条语句的查询的每一列类型和顺序最好一致（利于查看）
	union 关键字默认去重 union all 不去重

例1：查询部门编号大于90，或者邮箱包含'l'的员工信息
select * from groups where bu_men_id>90
union 
select * from groups where you_xiang like '%l%'

例2：查询中国男性和国外女性信息 
select idc,namec from China where sex='男'
union all
select id,`name`from `Foreign` where sex='男'

七.插入语句 insert into

1.经典插入法一

insert into 表名(列名1,....) values (值1,.....)
1.插入的值的类型要与字段的类型一致
insert into groups (id,bu_men_id,bu_men,`name`,ru_zhi_date,xin_zi,jiang_jin,you_xiang,deng_ji,bian_hao)
values(10,100,'武装部','亚索','2021-07-05 16:07:53',9000,null,'dadsa12@163.com',4,9)



2.不可以为null的列不能插入值，可以为null的列如何插入值？
方式一（要么列名写上，值写null）
insert into groups (id,bu_men_id,bu_men,`name`,ru_zhi_date,xin_zi,jiang_jin,you_xiang,deng_ji,bian_hao)
values(10,100,'武装部','永恩','2021-07-05 16:07:53',9000,null,'dadsa12@163.com',4,9)
方式二（要么不写列名，也不写值）
insert into groups (id,`name`,xin_zi)
values(11,'赵信',9000)

3.列的顺序是否可以颠倒？
(一一对应就行，可以颠倒)
insert into groups (`name`,xin_zi,id)
values('赵信',9000,12)

4.列数和值的个数必须一致
5.可以省略列名，默认所有列且列的顺序和表中的顺序一致
insert into groups 
values(13,100,'武装部','蛮子','2021-07-05 16:07:53',9000,null,'dadsa12@163.com',4,9)

2.方式二语法：

insert into 表名
set 列名=值，列名=值...

insert into groups 
set id=14,`name`='刀妹',xin_zi=8000

3.两种插入方法比较

1.方式一支持多行插入，方式二不支持
insert into groups values
(15,100,'武装部','蛮子','2021-07-05 16:07:53',9000,null,'dadsa12@163.com',4,9),
(16,100,'武装部','猴子','2021-07-05 16:07:53',9000,null,'dadsa12@163.com',4,9),
(17,100,'武装部','男刀','2021-07-05 16:07:53',9000,null,'dadsa12@163.com',4,9)

2.方式一支持子查询，方式二不支持
insert into groups(id,`name`,xin_zi) 
select 18,'赵信',9000

八.修改语句 update

1.修改单表的记录

语法：
update 表名
set 列=新值
where 筛选条件

update groups set `name`='李某某' where id=1

2.修改多表的记录

语法：
update 表1 别名
inner| left | right  join 表2 别名
on 连接条件
set 列=值，列=值...
where 筛选条件

update groups g left join test t on g.id=t.id set g.bu_men='测试部',t.surname='hehe' where g.id=1
select * from groups g left join test t on g.id=t.id

3.修改的条件是别的表查询出的数据(子查询)

update groups set bu_men='武装部' where `name`=(select '赵信')
【错误写法，修改的表不能和作为条件的表为同一张表】
update groups set bu_men='武装部' where `name` in  (select `name` from groups where `name`='赵信' )
【正确写法，修改的条件是别的表查询出的数据】
update groups g set bu_men='武装部' where g.id in  (select t.id from test t)

4.修改的值是别的表查询出的数据(子查询)

【错误写法，Subquery returns more than 1 row,好像修改的值不能为多个】
update groups g set `name`=(select namec from China  ) where g.id in  (select t.id from test t )
【正确写法，修改的值子查询的值只能有一行一列】
update groups g set `name`=(select namec from China where idc=1 ) where g.id in  (select t.id from test t )

九.删除语句 update,truncate

1.方式一update

删除是以行为单位

【单表删除】
语法：
delete from 表名 where 条件
例：
delete from groups where `name` like '%妹'
【多表删除】
语法：
（可以删除一张表，只要写一个，删除多个就写多个）
delete 表1 别名,表2 别名
from 
inner| left | right  join 表2 别名
on 连接条件
where 筛选条件

例：
delete g from groups g left join test t on g.id=t.id where g.id=1

delete g,t from groups g left join test t on g.id=t.id where g.id=2 

2.方式二truncate（清空数据）

语法：
不能加连接条件，直接删除整张表的数据
truncate table 表名

3.delete和truncate的区别

1.delete可以加筛选条件，truncate不可以
2.truncate效率高一点点
3.假如被删除的表中有自增长列
使用delete删除会从断点开始
使用truncate删除会从1开始
4.delete删除后会有返回值（Affected rows: 1）,truncate没有
5.truncate删除后不能回滚，delete可以回滚

十.DML语言和DDL语言

1.DML语言

数据操作语言
插入 insert
修改 update
删除 delete

2.DDL语言

数据定义语言
库和表的管理
一.库的管理（针对于结构非数据）
创建、修改、删除
二.表的管理（针对于结构非数据）
传教、修改、删除

创建 create (cr ea te 可 r t)
修改 alter
删除 drop

十一.库的管理

语法：create database 库名

例：
create database Books
【报错数据库以存在 Cant create database 'Books'; database exists】
create database Books
【如果不存在，则创建数据库，增加容错，避免打断施法】
create database if not exists Books

2.库的修改
	1.库名修改（基本不修改）：最好去服务器找到库对应的文件夹，修改文件名
	2.字符集更改：
		默认创建是utf-8
		alter database Books character set gbk
	3.库的删除
		drop database if exists Books

十二.表的管理

1.表的创建 create table

语法
【】表示非必填字段
create table 表名(
	列名 列的类型 【长度 约束】,
	列名 列的类型 【长度 约束】,
	列名 列的类型 【长度 约束】,
	...
	列名 列的类型 【长度 约束】
)
use Books
创建图书表
create table if not exists book(
	id int, #编号
	bName varchar(20), # 图书名
	price double, # 价格
	authorId int, # 作者编号
	publishDate datetime #出版日期
)
验证
desc book

创建作者表author

create table if not exists author(
	id int,
	an_name varchar(20),
	nation varchar(20) #国籍
)

desc author

2.表的修改 alter table

语法：
alter table 表名 add|drop|change|modify column 列名 【列类型,约束】

单词column：列 change：改变 modify：修改
1.修改列名(旧列名，新列名和新类型)
alter table book change column publishDate pubDate datetime

2.修改列的类型或者约束
alter table book modify column pubDate timestamp #时间戳类型
alter table book modify column pubDate datetime

3.添加新列
alter table book add column annual double （书籍没有年薪，用于下面的删除列使用）
alter table author add column annual double 

4.删除列
alter table book drop column annual 

5.修改表名
alter table author rename to book_author

desc book_author

3.表的删除 drop tabl

drop table if exists book
show tables (显示当前库里面的表)

4.表的复制 insert into

insert into book_author values
(1,'adc','韩国'),
(2,'jom','美国'),
(3,'&&','非洲'),
(4,'##','不知道国')
1.仅仅复制表的结构
select * from book_author
【复制】
create table copy like book_author
select * from copy

2.复制表结构+数据
【复制】
create table copy2 select * from book_author
select * from copy2
【只复制部分数据】
create table copy3 select id,an_name from book_author where nation='不知道国'
select * from copy3

3.仅仅复制某些字段
【让筛选条件不存在实现查询的字段为null】
create table copy4 select id,an_name from book_author where 1=2
select * from copy4

十三.数据类型

1.分类

数值型
	整形
	小数
		定点数
		浮点型
字符型
	较短的文本：char 、varchar
	较长的文本：text、blob（保存较长的二进制数据）
日期型

2.整形

int 从 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型数据(所有数字)。存储大小为 4 个字节
bigint 从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据(所有数字)。存储大小为 8 个字节

3.小数型

1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M，D)
decimal(M,D) 【开发常用】

特点：

M：整数部分的位数 + 小数部分的位数（可以理解为数值的总长度为M）
D：小数部分的位数（可以理解为保留D位小数）
MD可以不写
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度

定点型的精确度较高，如果要求插入数值的精度较高，如货币运算等则考虑使用
create table test_xiaoshu(
		f1 float(5,2),
		f2 double(5,2),
		f3 decimal(5,2)
)
select* from test_xiaoshu
【2位小数插入】
insert into test_xiaoshu values(123.45,123.45,123.45)
【3位小数插入，第三位4舍五入了】
insert into test_xiaoshu values(123.456,123.456,123.456)
【1位小数插入，第二位小数补位0】
insert into test_xiaoshu values(123.4,123.4,123.4)
【整数部位加小数部位超过设置的5位，无法写入数据】
insert into test_xiaoshu values(1213.41,1123.40,1233.14)

4.字符型

较短的文本：char,varchar
特点 
					写法            M的意思                         特点         空间耗费率     效率    
char      char(M)       最大字符数,可以不写默认为1     固定长度的字符   比较耗费      高
varchar		varchar(M)		最大字符数，必须要写           可变长度的字符		比较节省      低
使用场景：
性别 sex 一般只有男女2种，不会变化，建议使用char
姓名 `name` 国家不同长度不等，建议使用varchar

枚举 enum
字母可以大小写通用，一次只能插入一个字符数据
create table test_zifu(
	z1 enum('a','c','b')
)
insert into test_zifu values('a');
insert into test_zifu values('b');
insert into test_zifu values('c');
insert into test_zifu values('A');
【不在定义的范围内的枚举值无法插入】
insert into test_zifu values('n');

select * from test_zifu

集合set
create table test_set(
	z1 set('a','c','b','d')
)

insert into test_set values('a');
insert into test_set values('b,c');
【如果有重复的只会写入一个】
insert into test_set values('c,A,C');
insert into test_set values('c,A,D');

select * from test_set

5.日期型

date只保存日期
time 只保存时间
year只保存年

datetime保存日期+时间   占用字节较多
timestamp保存日期+时间  占用字节较少

create table test_time(
  t1 datetime,
	t2 timestamp
)
show tables
insert into  test_time values(now(),now())
select * from test_time

十四.约束

1.概念种类

概念：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束 
not null :非空约束，字段不能为空，比如：学号，姓名
default ：默认约束，用于保证该字段有默认值 比如：性别
primary key :主键约束 用于保证字段的唯一性，并且非空 比如学号、员工编号
unique ：唯一约束，用于保证字段的唯一性，可以为空
check ：检查约束【mysql不支持】比如 int 型的年龄，可以设置 只要18岁到65岁的年龄输入
foreign key :外键约束 待定
注：一个列可以加多种约束
添加约束的时机：
	1.创建表时
	2.修改表时
约束的添加分类
	1.列级约束：
			外键约束没有效果，但是可以写
	2.表级约束：
			除了非空、默认约束都支持
create table 表名(
  字段名 字段类型  列级约束,
	字段名 字段类型  列级约束,
	表级约束
)

2.创建表时添加约束

1.创建列级约束

语法：
字段 字段类型 字段约束
mysql只支持 ：默认、非空、主键、唯一
create table stuinfo(
	id int primary key ,#主键
	stuName varchar(20) not null,#非空
	sex char(1) check(sex in ('男','女')),#检查
	seat int unique, #唯一
	age  int default 18,#默认
	majorId int references major(id)#外键(列级约束设置外键也没用)
)

create table major(
 id int primary key ,
 majorName varchar(20)
)

#查看表中的索引，包括主键、键名、外键、唯一
show index from stuinfo

2.创建表级约束

语法：
字段 字段类型 字段约束
mysql只支持 ：默认、非空、主键、唯一


create table stuinfo(
	id int primary key ,#主键
	stuName varchar(20) not null,#非空
	sex char(1) check(sex in ('男','女')),#检查
	seat int unique, #唯一
	age  int default 18,#默认
	majorId int references major(id)#外键(列级约束设置外键也没用)
)

create table major(
 id int primary key ,
 majorName varchar(20)
)

#查看表中的索引，包括主键、键名、外键、唯一
show index from stuinfo

【创建表级约束】
语法：
多个字段的最下面
【constraint 约束名】约束类型(字段名)

drop table if exists stuinfo;
#这样写简洁很多
create table stuinfo(
	id int,
	stuName varchar(20),
	sex char(1),
	seat int,
	age int,
	majorId int,
	#          键名   主键的键名无法修改 为PRIMARY
	primary key(id),#主键
	unique(seat),#唯一
	check(sex in ('男','女')),#检查
	foreign key(majorid) references major(id)#外键
	
)
#没必要取啥名
create table stuinfo(
	id int,
	stuName varchar(20),
	sex char(1),
	seat int,
	age int,
	majorId int,
	#          键名   主键的键名无法修改 为PRIMARY
	constraint pk primary key(id),#主键
	constraint uq unique(seat),#唯一
	constraint ck check(sex in ('男','女')),#检查
	constraint fk_stuinfo_major foreign key(majorid) references major(id)#外键
	
)
#查看表中的索引，包括主键、键名、外键、唯一
show index from stuinfo

3.通用写法

create table if not exists stuinfo(
	id int primary key ,#主键
	stuName varchar(20) not null,#非空
	sex char(1) ,
	seat int unique, #唯一
	age  int default 18,#默认
	majorId int ,
	constraint fk_stuinfo_major foreign key(majorid) references major(id)#外键建议取约束名
	
)

3.主键和唯一的区别

		保证唯一性  是否允许为空                                             一个表中可以有多少个   是否允许（多个列组合一个键）组合
主键  满足        否                                                          至多一个           可以但不推荐
唯一  满足				是（2列插入null就不行，因为2个null值是相同的，不唯一）      可以有多个				 可以但不推荐

键组合写法
primary key(id,stuName) 
unique(seat,id)
主键和唯一键的判断重复和非空都要根据组合的所有键的值来判断：
例子：
满足主键要求
	insert into stuinfo values (1,'姓名')
	insert into stuinfo values (1,'姓1名')
不满足主键要求
	insert into stuinfo values (1,'姓名')
	insert into stuinfo values (1,'姓名')
	

4.外键的特点

	1.主表：字段被别的表设置为外键的表
	2.从表：设置外键关系的表
	3.从表的外键列的类型要和主表的关联列的类型要求一致或兼容，名称无所谓
	4.主表的关联列必须是一个key （一般是主键，唯一键）
	5.插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表
	

5.修改表时添加约束

1.语法

添加列级约束：
alter table 表名 modify column 字段 字段类型  字段约束
添加表级约束：
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】

2.写法

drop table if exists stuinfo;
create table stuinfo(
	id int,
	stuName varchar(20),
	sex char(1),
	seat int,
	age int,
	majorId int
)
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
【表级约束写法】
alter table stuinfo add primary key(id)
4.添加唯一键
【列级约束写法】
alter table stuinfo modify column seat int unique
【表级约束写法】
alter table stuinfo add unique(seat)
5.添加外键
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id)

6.修改表时删除约束

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.删除唯一键(约束名)
alter table stuinfo drop index seat
5.删除外键(约束名)
alter table stuinfo drop foreign key fk_stuinfo_major