MySQL（MySQL 8.0版本）

创建数据库

create database 数据库名 default charset utf8mb4;

create database school default charset utf8mb4;

删除数据库

drop database if exists 数据库名;

切换数据库

use 数据库名;

常用数据类型

• 整数：int / integer (-2^31 - 2^31-1) , tinyint / smallint / bigint int unsigned: 无符号整数（0-2^32-1） tinyint unsigned (0-255)
• 小数：float / double decimal(decimal(位数，保留位数)，最大位数65，保留位数30)
• 字符串：char / varchar------->65535------>1024 longtext ---->longlob(二进制大对象)---->放路径，url
• 时间日期：date / time / datetime timestamp(时间戳，不推荐)
• 布尔型：boolean ----->tinyint(1)----->0/1
• 枚举型：enum------> ‘M’ / ‘F’

1、mysql中可以使用反引号``将表名，列名等括起来，也可以不用，但是前提是不能使用mysql

2、使用 comment ‘说明’ 语法对自己的语句进行说明

3、使用 – 进行注释

4、在语句中 一般来说 = 代表等于，在set 或者select 为定义语句时候是赋值的意义

创建表(创建一张学生表)

use school;  -- 在使用数据库时记得先用 use 切换数据库

-- 建表
create table 表名
(
	列名1 类型 [后可跟：是否为空(not null),默认值(default 值)],
    列名2 ……,
    ……，
    primary key (列名)  -- 设置主键
)engine=InnoDB;

-- 新建一张学生表
create table tb_student
(
	stu_id integer not null comment '学号',
    stu_name varchar(10) not null comment '姓名',
    stu_sex char(1) default 'M' not null,
    stu_birth date,
    primary key (stu_id)
)engine=InnoDB comment '学生表';

添加列(add)，删除列(drop)

alter table 表名 add column 添加的列名 类型以及各种属性等;
alter table 表名 drop column 列名;
-- 给学生表添加一个地址列
alter table tb_student add column stu_addr varchar(100);
-- 删除性别列
alter table tb_student drop column stu_sex;

修改(modify , change)

-- 将之前删除的性别添加
alter table tb_student add column boolean default 1;

-- 修改性别的类型，默认值
alter table tb_student modify column stu_sex char(1) default 'M';

-- 修改性别的列名（前者是原列名，后者为新列名），类型，默认值
alter table tb_student change column stu_sex stu_gender char(1) default 'F'

modify: 适用更改一些小操作，例如：属性，默认值

change：适用更改一些大操作，例如：重命名

添加约束条件（add constraint）

1、 添加检查约束

alter table 表名 add constraint 给约束起的名字 约束的关键字 对应条件;

-- 给性别添加检查约束，强制输入的必须符合条件
alter table tb_student add constraint ck_student_gender check(stu_gender = 'M' or stu_gender = 'F');

-- 删除约束
alter table tb_student drop constraint ck_student_gender;

2、添加唯一约束

alter table tb_student add constraint uk_id unique(stu_id);

3、添加外键约束（1:1,1:n,m:n）（foreign key）

-- 给某一列添加外键约束，参照学院表
alter table tb_student add constraint fk_student_co_id foreign key (co_id) references tb_college (co_id);

-- 删除外键约束
alter table tb_teacher drop foreign key fk_teacher_co_id;
alter table tb_teacher drop constraint fk_teacher_co_id;

​ 当存在多对对的时候，需要建立一张中间表，该表会与两张表建关系，通过中间表构建两张表的关系。

即是：中间表分别与两张表建立外键约束

示例：学生表与课程表有多对多关系，建立一张记录表（中间表）

-- 多对多需要添加一张中间表记录两者之间的关系,选课记录表
create table tb_record
(
	rec_id bigint unsigned not null auto_increment comment '记录号',
    stu_id integer not null comment '学号',
    cou_id varchar(10) not null comment '课程编号',
    score decimal(4,1) comment '考试成绩',
    rec_date datetime default now() comment '选课时间',
    primary key (rec_id),
    foreign key (stu_id) references tb_student (stu_id),
    foreign key (cou_id) references tb_course (cou_id),
    unique (stu_id, cou_id)
)engine=InnoDB comment '中间表';

约束可以在建表时添加的

删除更新（delete, update）

-- 删除
delete from 表明 where 条件
-- 不指定条件，则全部删除
delete from tb_student where stu_id=001;

-- 更新
update 表名 set 赋值语句1，赋值语句2，…… where 条件;

update tb_student set stu_id=234, stu_addr='北京' where stu_id = 2345;

查询

查询是数据库做重要的操作

use school;

-- 查询所有信息
select * from tb_student; -- 尽量不使用，推荐将需要的列都列出来
-- 投影
select stu_id, stu_name, stu_addr from tb_student;  

-- 查询所有课程以及学分（投影和别名）--> alias
select cou_name as 课程名称, cou_credit as 学分 from tb_course;
select cou_name 课程名称, cou_credit 学分 from tb_course;

-- 查询所有女生的姓名和出生日期（筛选）where
select stu_name, stu_birth from tb_student where stu_sex=0;

-- 查询所有80后姓名，性别，出生日期  （分支）
select stu_name, stu_sex, stu_birth
	from tb_student
    where stu_birth >= '1980-1-1' and stu_birth <= '1989-12-31';
    
select stu_name, stu_sex, stu_birth
	from tb_student
    where stu_birth between '1980-1-1' and  '1989-12-31';
  
-- 在查询时将性别显示为男女
select 
	stu_name, 
	case stu_sex when 1 then '男' when 0 then '女' else '未知' end as stu_sex, 
    stu_birth
	from tb_student
    where stu_birth >= '1980-1-1' and stu_birth <= '1989-12-31';
   
-- 方言(在其他数据库不一定适用)   if()函数
select  
	stu_name, 
	if(stu_sex, '男' , '女')as stu_sex, 
    stu_birth
	from tb_student
    where stu_birth >= '1980-1-1' and stu_birth <= '1989-12-31';
    
-- 查询姓杨的学生姓名和性别（模糊查询）
--    % ：0个或任意多个
--    _ : 一个字符 ,    __: 两个字符
select stu_name, stu_sex from tb_student where stu_name like '杨%';
select stu_name, stu_sex from tb_student where stu_name like '杨_';

-- 查询姓杨的名字两个字的学生姓名和性别
select stu_name, stu_sex from tb_student where stu_name like '杨__';

-- 查询名字中有‘不’ 或‘嫣’的学生姓名（模糊） 先后顺序 最右边的先执行
select stu_name from tb_student where stu_name like '%不%' or stu_name like '%嫣%';


-- union: 去重 满足上一个条件又满足下一个也会输出、union all : 重复元素会保留，满足两个条件会输出两次
select stu_name from tb_student where stu_name like '%不%'
union 
select stu_name from tb_student where stu_name like '%嫣%';

-- 基于正则表达式的查询 .匹配任意一个字符 ，^$ : 起始终止符，{}多少次
select stu_name from tb_student where stu_name regexp '杨.';
select stu_name from tb_student where stu_name regexp '杨..';
select stu_name from tb_student where stu_name regexp '^杨.$';
select stu_name from tb_student where stu_name regexp '杨.{2}';
select stu_name from tb_student where stu_name regexp '[杨林].{2}';

-- 查询没有录入家庭住址的学生姓名（空值）null 或者 ''
select stu_name from tb_student where stu_addr is null;
select stu_name from tb_student where stu_addr <=> null;  
-- <=>：专门用于和空值比较，建议使用is null,is not null

-- 查询录入家庭地址的 <>：不等于
select stu_name from tb_student where stu_addr is not null and stu_addr <> '';

-- 查询学生选课的所有选课日期(去重)distinct
select distinct sel_date from tb_record;

-- 查询学生的家庭地址（去重）
select distinct stu_addr from tb_student where stu_addr is not null;

-- 查询男学生的姓名和生日按年龄从大到小排列（排序）
-- asc: 升序  desc：降序
-- curdate: 获取当前时间
-- datediff: 计算时间差，以天为单位
-- floor: 向下取整，取小
-- ceil：向上取整，取大
select stu_name, stu_birth from tb_student where stu_sex=1 order by stu_birth;

select 
	stu_name, 
    stu_birth,
    floor(datediff(curdate(),stu_birth)/365) as stu_age
    from tb_student where stu_sex=1 order by stu_age desc;
    
-- 查询年龄最大的学生的出生日期（聚合函数）
select min(stu_birth) from tb_student;

-- 查询年龄最小的学生的出生日期
select max(stu_birth) from tb_student;

-- 编号为1111课程考试成绩的最高分
select max(score) from tb_record where cou_id=1111;

-- 查询学号1001考试成绩的最低分,自动处理空值
select min(score) from tb_record where stu_id=1001;

-- 查询学号1001的考试成绩平均分，空值为0分
select avg(score) from tb_record where stu_id=1001;
select sum(score) / count(*) as 平均分 from tb_record where stu_id=1001;
-- 标准差
select std(score) as 标准差 from tb_record where stu_id=1001;

-- 将score空值替换为50
select avg(ifnull(score,50)) from tb_record where stu_id=1001;

过程

use hrs;

-- （存储）过程：将一系列的SQL组装到一起编译存储起来以备调用

-- 删除存储过程
drop procedure if exists sp_upgrade_emp_sal;

-- 创建存储过程  没有返回值
delimiter $$
create procedure sp_upgrade_emp_sal() 
-- 不能直接运行，需要关闭sql_safe_updates  show like '%safe%'
begin
	set sql_safe_updates=off;
	update tb_emp set sal=sal+200 where dno=10;
	update tb_emp set sal=sal+500 where dno=20;
	update tb_emp set sal=sal+800 where dno=30;
end $$
delimiter ;

-- 调用存储过程
call sp_upgrade_emp_sal();




delimiter $$ 
-- 输入，输出参数
create procedure sp_get_avg_sal(dept_no int, out avg_sal decimal(6,1))
begin
	select round(avg(sal),1) into avg_sal from tb_emp where dno=dept_no;
end $$

call sp_get_avg_sal(10,@a);
select @a;

窗口函数

use hrs;

-- 窗口函数
	-- 1、查询排名问题
    -- 2、Top——N问题
-- ? window functions
-- dense_rank, rank, row_number ----> 产生排名
-- value	row_number  rank  dense_rank
--  1		  1			1		1
--  1		  2			1		1
--  2		  3			3		2
--  3		  4			4		3
--  3		  5			4		3
--  3					4		3
--  4					7		4
--  4
--  5
select rank_num, ename, sal from 
(select 
	dense_rank() over (order by sal desc )as rank_num,
    ename, sal
    from tb_emp)temp where rank_num between 4 and 6;
    
-- 查询每个部门月薪前两名的员工
select ename, sal, dno from tb_emp t1 where(
select count(*) from tb_emp t2 where t1.dno=t2.dno and t1.sal<t2.sal)<2
order by dno;
    
-- 使用窗口函数
select rank_num, ename, sal, dno from
	(select rank() over (partition by dno order by sal desc, dno )as rank_num,
    ename, sal, dno from tb_emp)temp where rank_num <3;

存取json格式数据

-- JSON类型：处理没有固定结构的数据 --> MySQL 5.7

use hrs;

drop table if exists `tb_test`;

create table `tb_test`
(
    `id` bigint unsigned auto_increment,
    `data` json,
    primary key (`id`)
);

insert into tb_test 
values 
    (default, '{"name": "骆昊", "tel": "13122335566", "QQ": "957658"}'),
    (default, '{"name": "王大锤", "birth": "1990-1-1", "sex": "男"}');

-- 查询
select 
	id,
    data ->> '$.name' as uname,
    data ->> '$.tel' as tel,
    data ->> '$.birth' as birth
from tb_test;