mysql入门基础 史上最全的mysql基础教程

MySQL基础

• 一、MySQL

• 1. 常见命令
• 2. 语法规范

• 二、查询：DQL语言

• 1. 基础查询
• 2. 条件查询
• 3. 排序查询
• 4. 常见函数

• 01 单行函数
• 02 分组函数

• 5. 分组查询
• 6. 连接查询

• 01 sql92标准
• 02 sql99标准

• 7. 子查询
• 8. 分页查询
• 9. 联合查询

• 三、操纵：DML语言

• 1. 插入
• 2. 修改

• 01 修改单表记录
• 02 修改多表记录

• 3. 删除

• 四、定义：DDL语言

• 1. 库的管理
• 2. 表的管理
• 3. 数据类型

• 01 整型
• 02 浮点型
• 03 字符型
• 04 日期型

• 4. 常见约束

• 01 创建表时添加约束
• 02 修改表时添加约束
• 03 修改表时删除约束

• 5. 标识列

一、MySQL

1. 常见命令

1.登陆与退出： mysql [-h 主机名 -P 端口号] -u root -p密码
2.查看当前所有的数据库：show databases;
3.打开指定的库：use 库名;
4.查看当前库的左右表：show tables;
5.查看其他库的所有表：show tables from 库名;
6.创建表：create table 表名(
		列名 列类型,
		列名 列类型,
		)
7.查看表结构：desc 表名;
8.查看服务器版本：select version();

2. 语法规范

1. 不区分大小写，建议关键字大写，表名、列名小写
2. 每条命令最好用分号结尾
3. 每条命令根据需要可以进行缩进和换行
4. 注释：

#单行注释
-- 单行注释
/*注释文字*/

二、查询：DQL语言

1. 基础查询

/* 语法：select 查询列表 from 表名;
 * 特点：
 * 1.查询列表可以是：表中的字段、常量值、表达式、函数
 * 2.查询的结果是一个虚拟的表格		
 */

# 查询表中的单个字段
select last_name from employees ;

# 查询表中的多个字段
select last_name,salary,email from employees ;

# 查询表中的所有字段
select * from employees ;

# 查询常量值
select 100 ;
select 'john' ;

# 查询表达式
select 100*98 ;

# 查询函数
select version();

# 起别名
select 100*98 as 结果;
select last_name as 姓, first_name as 名 from employees;
select last_name 姓, first_name 名 from employees;

select salary as `out put` from employees ;

# 去重
select distinct department_id from employees ;

# +号的作用
select 100 + 90; # 两个操作数都是数值型，则作加法运算
select '123' + 90; # 其中一方为字符型时，视图将字符型数值转换为数值型
				   # 若转换成功，则继续做加法运算；否则将字符型转为0
select null + 123; # 只要一方为null，则结果就为null

# 拼接
select concat('a','b','c') as 结果;

2. 条件查询

/* 语法:select 
 * 				查询列表 
 * 		from
 * 				表名
 * 		where 
 * 				筛选条件;
 * 分类：
 * 		1.按条件表达式筛选：> < = != <> >= <=
 * 		2.按逻辑表达式筛选：&& || ! and or not
 * 		3.模糊查询：like、between and、in、is null
 */

# 按条件表达式筛选
select * from employees e  where salary > 12000;
select last_name,department_id from employees where department_id <> 90;

# 按逻辑表达式筛选
select last_name,salary ,commission_pct 
from employees e 
where salary >= 10000 and salary <= 20000;

# 模糊查询
/*like: 
 * 1.一般和通配符搭配使用：
 * 			% 任意多个字符，包含0个字符
 * 			_ 任意单个字符
 */
# 查询员工名中包含字符a的员工信息
select * from employees where last_name like '%a%'; 
# 查询员工名中第二个字符为_的员工名
select last_name from employees e where last_name like  '_\_%';# like '_$_%' escape '$';
/*between and:
 * 使用between and 可以提高语句的简洁度
 * 区间简洁包含
 * 临界值顺序不可颠倒
 */
select * from employees e where employee_id  between 100 and 120;
/*in:
 * 含义：用于判断某字段中的值是否属于in列表中的某一项
 * 特点：括号内容不支持使用通配符
 */
select last_name, job_id from employees e where job_id in('IT_PROT','AD_VP','AD_PRES');
/*is null:
 * =或<>不能判断null值
 */
select last_name, commission_pct from employees where commission_pct is null;

# 安全等于<=>
select last_name, commission_pct from employees where commission_pct <=> null;
select last_name, commission_pct from employees where salary <=> 12000;

3. 排序查询

/* 语法：
 * select 查询列表
 * from 表
 * [where 筛选条件]
 * order by 排序列表 [asc|desc]
 * 注意：
 * 1.asc代表升序，desc代表降序，默认为升序
 * 2.order by 后支持单个、多个字段、表达式、函数、别名
 * 3.order by 子句一般是放在查询语句的最后边，limit 子句除外*/
select * from employees e order by salary desc;
select * from employees e order by salary asc;
# 添加筛选条件
select *
from employees e 
where department_id >= 90
order by hiredate asc;
# 按表达式进行排序
select *, salary * 12 *(1+ifnull(commission_pct,0)) 年薪
from employees e 
order by salary * 12 *(1+ifnull(commission_pct,0)) desc;
# 按别名排序
select *, salary * 12 *(1+ifnull(commission_pct,0)) 年薪
from employees e 
order by 年薪 desc;
# 按姓名的长度显示员工的姓名和工资[按函数排序]
select length(last_name) 字节长度, last_name, salary
from employees e 
order by length(last_name) desc ;
# 查询员工信息，要求先按工资排序，再按员工编号排序[按多个字段排序]
select * from employees e 
order by salary asc, employee_id desc;

4. 常见函数

01 单行函数

/*语法：
 * select 函数名(实参列表) [from 表];
 * 分类：
 * 1.单行函数：concat, length, ifnull
 * 2.分组函数*/

# 字符函数
# length:获取参数值的字节数
select length('join');
# concat: 拼接字符串
select concat(last_name,'_',first_name) 姓名 from employees e ;
# upper/lower
select upper('join');
# substr/substring
select substr('abcdefg',4) out_put;	  # defg
select substr('abcdefg',1,3) out_put;  # abc
# instr: 返回子串第一次出现的索引，若找不到返回0
select instr('abcdefg','cde') as out_put;
# trim
select length(trim('   abc   '))as out_put;
select length(trim('a'from'aaaaaaaaabcaaadaaaaaaaaa')) as out_put;
# lpad:用指定的字符实现左填充指定长度
select lpad('张智森',10,'*') as out_put ; # *******张智森
select rpad('abg',5,'b') as out_put ; # abgbb
# replace: 替换
select replace('abbcdded','b','z');

# 数学函数
# round 四舍五入
select round(1.65); #2
select round(-1.65); #-2
select round(1.567,2); #1.57

# ceil:向上取整，返回大于等于该参数的最小整数
select ceil(1.00) ;
select ceil(-1.02); #-1

# floor:向下取整

# truncate：截断
select truncate(1.65435,1); #1.6

# mod：取余
select mod(-10,-3); #-1
select mod(10,-3); #1
select mod(-10,3); #-1

# rand：获取随机数，返回0-1之间的小数

# 日期函数
# now:返回当前系统日期+时间
select now();

# curdate:返回当前系统日期，不包含时间
select curdate();

# curtime:返回当前系统时间，不包含日期
select curtime();

# 可以获取指定的部分，年/月/日/小时/分钟/秒
select year(now());
select month(now());
select monthname(now());

# str_to_date:将日期格式的字符转换成指定格式的日期
select str_to_date('3-16-2003','%c-%d-%Y');
select * from employees e where hiredate ='1992-4-3';

# date_format:将日期转换为字符
select date_format(now(),'%y年%m月%d日');

# datadiff:计算两个日期之间相差的天数
select datadiff('2003-3-16','2002-8-27');

# 其他函数
select version();
select database();
select user();

# 流程控制函数
# if函数：if else的效果
select if(10 > 5, '大', '小');
select last_name,commission_pct,
if(commission_pct is null,'没奖金，垃圾','有奖金，牛逼') 备注 from employees e ;

# case函数：switch case 的效果
select salary 原始工资,department_id,
case department_id 
when 30 then salary * 1.1
when 40 then salary * 1.2
when 50 then salary * 1.3
else salary 
end as 新工资
from employees e ;

# case函数：类似于多重if
select salary,
case 
when salary > 20000 then 'A'
when salary > 15000 then 'B'
when salary > 10000 then 'C'
else 'D'
end as 工资级别
from employees e ;

02 分组函数

# 分组函数
-- 功能：用作统计使用，有称为聚合函数或统计函数或组函数
-- 分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
-- 特点：
-- 1.sum、avg 一般用于处理数值型
--   max、min、count 可以处理任何类型
-- 2.以上分组函数都忽略null值
-- 3.可以和distinct 搭配实现去重运算
-- 4. count函数
-- 5.和分组函数一同查询的字段要求是group by 后的字段

# 1.简单的使用
select sum(salary) from employees e ;
select avg(salary) from employees e ;
select count(salary) from employees e ;
select max(salary) from employees e ;

select sum(salary) 和,round( avg(salary),2) 平均 
from employees e ;

# 2.和distinct搭配
select sum(distinct salary),sum(salary) from employees e ;
select count(distinct salary),count(salary) from employees e ;

# 3.count函数详细介绍
select count(salary) from employees e ;
select count(*) from employees e ; # 只要一行内存在一个不为null则统计上，即行数
select count(1) from employees e ; # 与上一个作用一样，都是统计行数

5. 分组查询

# 进阶5：分组查询
/*语法：
select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[order by 子句]
注意：查询列表必须特殊，要求是分组函数和group by 后出现的字段

* 分组查询中的筛选条件可以分为两类*/

# 简单分组
# 案例1：查询每个工种的最高工资
select max(salary),job_id
from employees e 
group by job_id;

# 案例2：查询每个位置上的部门个数
select count(*),location_id
from departments d  
group by location_id ;


# 添加分组前的筛选条件
# 案例1：查询邮箱中包含a字符的，每个部门的平均工资
select avg(salary),department_id
from employees e 
where email like '%a%'
group by department_id ;

# 案例2：查询有奖金的，每个领导手下员工的最高工资
select max(salary),manager_id
from employees e 
where commission_pct is not null 
group by manager_id ;


# 添加分组后的筛选条件
# 案例1：查询哪个部门的员工个数大于2
select count(*),department_id 
from employees e
group by department_id 
having count(*)>2;

# 案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary),job_id 
from employees e 
where commission_pct is not null
group by job_id 
having max(salary) > 12000;


# 按表达式或函数分组
# 案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
select count(*),length(last_name) len_name
from employees e 
group by len_name 
having count(*) > 5;
# group by 和having 语句都支持别名


# 按多个字段分组
# 案例：查询每个部门每个工种的员工的平均工资
select avg(salary),department_id,job_id 
from employees e 
group by job_id ,department_id ;


# 添加排序
# 案例：查询每个部门每个工种的员工的平均工资,并按平均工资的高低显示
select avg(salary),department_id,job_id 
from employees e 
group by job_id ,department_id
order by avg(salary) desc;

6. 连接查询

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

01 sql92标准

# 1.等值连接
/*
1.多表等值连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件
3.可以搭配使用
*/
# 案例1：查询女生名和对应的男生名
select name, boyname
from boys, beauty
where beauty.boyfriend_id= boys.id;

# 案例2：查询员工名和对应的部门名
select last_name, department_name
from employees,departments 
where employees.department_id = departments.department_id ;


# 2.为表起别名: 注意为表起了别名，则查询的字段就不能使用原来的别名去限定
# 案例：查询员工名、工种号、工种名
select last_name, e.job_id, job_title
from employees e,jobs j
where e.job_id = j.job_id;


# 3.两个表的顺序可以调换
select last_name, e.job_id, job_title
from jobs j,employees e
where e.job_id = j.job_id;


# 4.可以加筛选
# 案:1：查询有奖金的员工名、部门名
select last_name, department_name
from employees e , departments d 
where e.department_id = d.department_id 
and e.commission_pct is not null;

# 案例2：查询城市名中第二个字符为o的部门名和城市名
select department_name ,city
from departments d ,locations l 
where d.location_id = l.location_id 
and city like '_o%';


# 5.可以加分组
# 案例1：查询每个城市的部门个数
select count(*) 个数, city 
from departments d , locations l 
group by city;

# 案例2：查询有奖金的每个部门名和部门领导编号和该部门的最低工资
select department_name ,d.manager_id, min(salary)
from departments d ,employees e 
where d.department_id  = e.department_id 
and commission_pct is not null 
group by department_name, d.manager_id  ;


# 6.可以加排序
# 案例：查询每个工种名和与员工个数，并按照员工个数降序
select job_title ,count(*) 个数
from employees e , jobs j 
where e.job_id = j.job_id 
group by job_title 
order by count(*) desc ; 


# 7.可以实现三表连接
# 查询员工名、部门名和所在城市
select last_name ,department_name ,city 
from employees e ,departments d ,locations l 
where d.location_id = l.location_id 
and e.department_id =d.department_id ;

# 非等值连接
# 案例：查询员工工资和工资级别
select salary, grade_level
from employees e ,job_grades jg 
where salary between jg.lowest_sal and jg.highest_sal ;


# 自连接
# 案例：查询员工名和上级名称
select e1.employee_id, e1.last_name, e2.employee_id , e2.last_name 
from employees e1, employees e2 
where e1.manager_id  = e2.employee_id ;

02 sql99标准

select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序条件]

# 一、内连接
#1.等值连接
# 案例1：查询员工名、部门名
select last_name, department_name
from employees e 
inner join departments d 
on e.department_id = d.department_id ;

# 案例2：查询名字中包含e的员工和工种名（添加筛选）
select last_name, job_title
from employees e 
inner join jobs j 
on e.job_id = j.job_id 
where e.last_name like "%e%";

# 案例3：查询部门个数>3的城市名和部门个数（添加分组+筛选）
select city, count(*) 部门个数
from departments d 
inner join locations l 
on d.location_id = l.location_id 
group by city
having count(*)>3;

# 案例4：查询每个部门员工个数大于3的部门名和员工个数，并按降序（添加排序）
select count(*) 员工个数, department_name
from departments d 
inner join employees e 
on d.department_id = e.department_id 
group by department_name 
having count(*)>3
order by count(*) desc;

# 案例5：查询员工名、部门名、工种名，并按部门名降序
select last_name, department_name, job_title 
from jobs j 
inner join employees e on e.job_id = j.job_id 
inner join departments d on e.department_id = d.department_id 
order by department_name desc;


# 2.非等值连接
# 查询员工的工资级别
select salary, grade_level
from employees e 
join job_grades jg 
on e.salary between jg.lowest_sal and jg.highest_sal ;


# 3.自连接
# 查询员工的名字、上级的名字
select e.last_name, m.last_name 
from employees e 
join employees m
on e.manager_id = m.employee_id ;

上述案例中inner都可以省略，inner join 的效果与sql92中的等值连接一样。

# 二、外连接
/* 用于查询查询一个表中有，另一个表中没有的情况
 * 1.外连接的查询结果为主表中的所有记录，
 * 		若从表中有和它匹配的，则显示匹配的值
 * 		若从表中没有和它匹配的，则显示null
 * 		外连接查询结果=内连接结果+主表中有而从表中没有的记录
 * 2.左外连接：left join 左边的是主表
 * 	 右外连接：right join 右边的是主表
 * 3.左外和右外交换两个表的顺序，可以实现同样的效果 */

# 左外连接
select b.name
from beauty b 
left outer join boys bo 
on b.boyfriend_id = bo.boyName 
where bo.id is null;

# 右外连接
select b.name
from boys bo 
right outer join beauty b 
on b.boyfriend_id = bo.boyName 
where bo.id is null;

# 案例：查询哪个部门没有员工
select d.*, e.employee_id
from departments d 
left outer join employees e 
on d.department_id = e.department_id 
where e.department_id is null;


# 交叉连接
select b.*, bo.*
from beauty b 
cross join boys bo;
# 即笛卡尔成乘机

7. 子查询

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
select 后面：仅支持标量子查询
from 后面：支持表子查询
where或having 后面：标量子查询、列子查询、行子查询
exists后面：表子查询（相关子查询）
按结果集的行列数不同：标量子查询（结果集只有一行一列），列子查询（结果集只有一行多列），行子查询（结果集可以有一行多列）、表子查询（结果集一般为多行多列）

# 一、where或having 后面
-- 1.标量子查询（单行子查询）
# 案例1：谁的工资比Abel高？
select *
from employees e 
where salary>(
	select salary
	from employees
	where last_name = 'Abel'
);

select job_id, salary 
from employees e 
where employee_id = 141;

# 案例2：查询最近工资大于50号部门最低工资的部门id和其最低工资
select min(salary), department_id 
from employees e 
group by department_id 
having min(salary) > (
	select min(salary)
	from employees 
	where department_id = 50
);

-- 2.列子查询（多行单列）
# 案例1：返回location_id是1400或1700的部门中的所有员工姓名
select distinct last_name
from employees e 
where department_id in (
	select department_id 
	from departments 
	where location_id in (1400,1700)
)

# 案例2：返回其他部门中比job_id为“IT_PROG”部门任一工资低的员工的员工号、姓名、job_id和salary
select last_name ,employee_id ,job_id ,salary 
from employees e 
where salary < any(
select distinct salary
from employees
where job_id = 'IT_PROG'
)and job_id <> 'IT_PROG';

# 案例3：返回其他部门中比job_id为“IT_PROG”部门所有工资低的员工的员工号、姓名、job_id和salary
select last_name ,employee_id ,job_id ,salary 
from employees e 
where salary < all(
select distinct salary
from employees
where job_id = 'IT_PROG'
)and job_id <> 'IT_PROG';


-- 3.行子查询(多行多列)
# 案例：查询员工编号最小并且工资最高的员工信息
select *
from employees e 
where (employee_id ,salary)=(
	select min(employee_id),max(salary)
	from employees
);

# 二、select 后面<仅仅支持标量查询>
# 案例1：查询每个部门的员工个数
select d.*,(
	select count(*) 
	from employees e
	where e.department_id = d.department_id 
)个数
from departments d ;

# 三、from 后边
/*将子表查询充当一张表，要求必须起别名*/
# 案例：查询每个部门的平均工资的工资等级

select *,jg.grade_level 
from (
	select avg(salary) ag,department_id
	from employees e 
	group by department_id 
)ag_dep
inner join job_grades jg 
on ag_dep.ag between lowest_sal and highest_sal;

# exists后面（相关子查询）
# 案例：查询有员工名的部门名
select department_name
from departments d 
where exists(
	select *
	from employees e 
	where e.department_id = d.department_id 
);

8. 分页查询

当要显示的数据一页中显示不全，需要分页提交sql请求

语法：select 查询列表
from 表
[join type] join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit offset,size; //offset：要现现显示条目的起始索引，size：要显示的条目个数

# 案例1：查询前五条员工信息
select * from employees e limit 0,5;
select * from employees e limit 5;  # 默认起始值为0

# 案例2：查询第11条至第25条
select * from employees e limit 10,15;

# 案例3：有奖金的员工信息，并且工资较高的前10名的显示
select * from employees e 
where commission_pct is not null 
order by salary desc 
limit 10;

9. 联合查询

将多条查询语句的结果合并成一个结果

语法：查询语句1
union
查询语句2
union
……

# 引入案例：查询部门编号>90，或者邮箱包含a的员工信息
select * from employees e where email like '%a%' or department_id > 90;

select * from employees e where email like '%a%'
union 
select * from employees e where department_id > 90;

要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时：

1. 要求多条查询语句的查询列数是一致的
2. 要求多条查询语句的查询每一列的类型和顺序一致
3. union关键字默认去重，如果用union all 可以包含重复项

三、操纵：DML语言

1. 插入

语法：

insert into 表名(列名,…)
values(值，…);

# 方法一：
# 1.插入的值的类型要与列的类型一致或兼容
insert into beauty (id,name,sex,borndate,phone,photo,boyfriend_id)
values(13,'孟凡','女','2003-3-16','1898888888',null,2);

# 2.不可以插入null的列必须插入值。可以为null的列如何插入值？
# 方式一：
insert into beauty (id,name,sex,borndate,phone,photo,boyfriend_id)
values(13,'xxx','女','2003-3-16','1898888888',null,2);

# 方式二：
insert into beauty (id,name,sex,borndate,phone,boyfriend_id)
values(13,'xxx','女','2003-3-16','1898888888',2);

# 3.列的顺序可以调换

# 4.列数和值的个数必须保持一致

# 5.可以省略列名，默认所有列，而且列的顺序和表中的顺序一致
insert into beauty 
values(18,'张飞','女',null,'119',null,null);

# 方式二：
insert into beauty 
set id = 19, name = '刘涛', phone = '999';

2. 修改

01 修改单表记录

语法：

update 表名
set 列 = 新值，列 = 新值，……
where 筛选条件；

# 案例1：修改beauty表中姓孟的电话改为：
update beauty set phone = '18888199199'
where name like '孟%';

02 修改多表记录

语法：

sql92语法：
update 表1 别名，表2 别名
set 列 = 值，……
where 连接条件
and 筛选条件；

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

# 案例：修改张无忌的女朋友的手机号为114
update boys bo
inner join beauty b on bo.id = b.boyfriend_id 
set b.photo = '114'
where bo.boyName  = '张无忌';

3. 删除

方式一：delete

单表的删除：
delete from 表名 where 筛选条件

多表删除：
sql92:
delete别名
from 表1 别名，表2 别名
where 连接条件
and 筛选条件

sql99:
delete 表1的别名，表2的别名
from 表1 别名
inner/left/right join 表2 别名 on 连接条件
where 筛选条件;

方式二：turncate
turncate table 表名;

-- 方式一：delete 
# 1.单表删除
# 案例：删除手机号以9结尾的女神信息
delete from beauty where phone like '%9';

# 2.多表删除
# 案例：删除张无忌的女神的信息以及自己的信息
delete b,bo
from beauty b
inner join boys bo on b.boyfriend_id = bo.id 
where bo.boyName = '张无忌';

-- 方式二：turncate 语句（清空数据）
# 案例：将魅力值>100的男神删除
turncate table boys;

四、定义：DDL语言

1. 库的管理

# 1.库的创建
# 案例：创建库Books
create database if not exists books;


# 2.库的修改
# rename database books to 新库名;已废弃

# 更改库的字符集
alter database books character set gbk;


# 3.库的删除
drop database if exists books;

2. 表的管理

# 1.表的创建
/* 语法：
 * create table 表名(
 * 		列名 列的类型 [(长度) 约束],
 * 		列名 列的类型 [(长度) 约束],
 * 		列名 列的类型 [(长度) 约束],
 * 		……
 * 		列名 列的类型 [(长度) 约束]
 * )*/
# 案例：创建Book表
create table if not exists book(
	id int, #编号
	bName varchar(20),# 图书名
	price double,
	authorId int,
	publishDate datetime
);



# 2.表的修改
# ①修改列名
alter table book change column publishDate pubDate datetime;

# ②修改类型
alter table book modify column pubDate timestamp;

# ③添加新列
alter table book add column annual double;

# ④删除列
alter table book drop column annual;

# ⑤修改表名
alter table book rename to Book;


# 3.表的删除
drop table if exists book;


# 4.表的复制
# 仅仅复制表的结构
create table copy like author;

# 复制表的结构与数据
create table copy 
select * from author;

# 只复制部分数据
create table copy3
select id, au_name
from author
where nation='中国';

# 仅仅复制某些字段（部分表的结构）
create table copy4
select id,au_name
from author 
where 1=2; # 构建一个不可能满足的条件

3. 数据类型

01 整型

整数类型	字节
tinyint	1
smallint	2
mediumint	3
int/integer	4
bigint	8

# 一、整型
/*特点：
 * 1.若不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
 * 2.如果插入的数值超出了整数的范围，会报out of range异常，并且插入临界值
 * 3.如果不设置长度，会有默认长度
 *   长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用。
 */
# 1.设置无符号与有符号
create table tab_int(
	t1 int,
	t2 int unsigned
);
desc tab_int;
insert into tab_int values(-123456);
insert into tab_int values(-123456,12344554);

02 浮点型

# 二、小数
/* 
 * 1.浮点型
 * float(M,D)
 * double(M,D)
 * 2.定点型
 * dec(M,D)
 * decimal(M,D)
 * 
 * 特点：
 * 1.M: 整数部位+小数部位
 *   D: 小数部位
 *   如果超出范围，则插入临界值
 * 2.M,D可以省略,
 *   如果是decimal，则M默认为10，D默认为0，
 *   如果是float和double，则会根据插入的数值的精度来决定精度
 * 3.定点性精度要求较高，货币运算等使用。
 */
drop table if exists tab_float ;
create table tab_float(
	f1 float(5,2),
	f2 double(5,2),
	f3 decimal(5,2)
);
desc tab_float ;
insert into tab_float values(123.45, 123.45, 123.45);
insert into tab_float values(123.456, 123.456, 123.456);
insert into tab_float values(123.4, 123.4, 123.4);
select * from tab_float ;

03 字符型

# 三、字符型
/* 
 * 较短的文本：
 * char(M)
 * varchar(M)
 * 较长的文本：
 * text
 * blob
 * 
 * 特点：
 * 1.char代表固定长度的字符，varchar代表可变长度的字符
 * 2.M代表最大字符长度
 */

# enum 枚举类型:要求插入的值必须属于列表中指定的值之一
create table tab_char(
	c1 enum('a','b','c')
);
insert into tab_char values('a');
insert into tab_char values('b');
insert into tab_char values('c');
insert into tab_char values('n');
insert into tab_char values('A');
select * from tab_char;

# set 集合类型：与enum类似，可以保存最多64个成员，与enum的区别是：
# set一次可以选取多个成员，enum 只能选择一个
create table tab_set(
	s1 set('a','b','c','d')
);
insert into tab_set values('a');
insert into tab_set values('a','b');

04 日期型

# 四、日期型

/* 
 * 分类：
 * date 只保存日期
 * time 只保存时间
 * year 只保存年
 * 
 * datetime 保存日期+时间
 * timestamp 保存日期+时间
 * 
 * 特点：
 * 				字节				范围				时区的影响
 * datetime		8				1000——9999		不受
 * timestamp	4				1970——2038		受
 */

create table tab_date(
	t1 datetime,
	t2 timestamp
);
insert into tab_date values(now(),now());
select * from tab_date ;
set time_zone='+9:00';
show variables like 'time_zone';

4. 常见约束

# 常见约束
/* 
 * 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性
 * 
 * 分类：六大约束
 * 1.not null：非空，用于保证该字段的值不能为空
 * 2.default: 默认，用于保证该字段的值有默认值
 * 3.primary key：主键，用于保证该字段的值具有唯一性，并且非空
 * 4.unique：唯一，用于保证该字段的值具有唯一性，可以为空
 * 5.check：检查约束(mysql中不支持)，
 * 6.foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
 * 				在从表中添加外界约束，用于引用主表中某列的值
 * 
 * 添加约束的时机：
 * 1.创建表时
 * 2.修改表时
 * 
 * 约束的添加分类：
 * 1.列级约束：六大约束语法上都支持，但外键约束无效果
 * 2.表级约束：除了非空和默认其他都支持
 */

01 创建表时添加约束

# 1.添加列级约束
/* 
 * 语法：
 * 直接在字段名和类型名后面追加约束类型即可
 * 只支持：默认、非空、主键、唯一
 */
use students;
create table stuinfo(
	id int primary key,# 主键
	stuName varchar(20) not null,# 非空
	gender char(1) check(gender='男' or gender='女'),# 检查，但无效果
	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.添加表级约束：
/* 
 * 语法：
 * [constraint 约束名] 约束类型（字段名）
 */
drop table if exists stuinfo ;
create table stuinfo(
	id int,
	stuname varchar(20),
	gender char(1),
	seat int,
	age int,
	majorid int,
	
	constraint pk primary key(id), # 主键
	constraint uq unique(seat), # 唯一
	constraint ck check(gender = '男' or gender = '女'),
	constraint fk_stuinfo_major foreign key(majorid) references major(id) # 外键

	);

外键的特点：

1. 要求在从表设置外键的关系
2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3. 主表的关联列必须是一个key(一般是主键或唯一)
4. 插入数据时，应该先插入主表，再插入从表
   删除数据时，先删除从表，再删除主表

02 修改表时添加约束

/* 
 * 1.添加列级约束
 * alter table 表名 modify column 字段名 字段类型 新约束;
 * 2.添加表级约束
 * alter table 表名 add [constriant 约束名] 约束类型(字段名) [外键的引用];
 */
# 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 ;
# 5.添加外键
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

03 修改表时删除约束

# 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;

5. 标识列

# 标识列
/* 
 * 又称为自增长列（可以不用手动的插入值，系统提供默认的序列值）
 * 
 * 特点：
 * 1.标识列不一定必须和主键搭配，但必须是一个key
 * 2.一个表只可以有一个标识列
 * 3.标识列的类型只能是数值型
 * 4.标识列可以通过set auto_increment_increment = 3;设置步长
 * 
 */

# 一、创建表时设置标识列
drop table if exists tab_identity;
create table tab_identity(
	id int primary key auto_increment ,
	name varchar(20)
);
truncate table tab_identity ; # 删除表
insert into tab_identity values(null,'john');
insert into tab_identity(id, name) values(null,'john');
insert into tab_identity(name) values('john');
select * from tab_identity ;

# 设置步长
set auto_increment_increment = 3;


# 二、修改表时设置标识列
alter table tab_identity modify column id int primary key auto_increment ;


# 三、修改表时删除标识列
alter table tab_identity modify column id int;