数据库增删改查

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;
上一篇:MYSQL中NULL空值与空字符串区别详解及代码


下一篇:javascript从入门到高级,每天不停更新知识点(13天)