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;