数据库MySQL

MySQL

DDL语句（定义语句）

用于对数据库、表进行定义，包括创库、创表、定义字段、删除表等

-- 查询数据库
show databases;
-- 创建数据库
create database 111;
-- 删除数据路
drop database 111;
-- 选择数据库
use 111;

-- 查询表
show tables;
-- 创建表
create table student(
s_name varchar(32),
s_sex varchar(32),
age int(3),
salary float(8,2),
s_course varchar(64));
-- 修改表名
alter table student rename s_student;
-- 删除表
drop table s_studnet;
-- 查询创建表信息
show create table s_student;

-- 添加字段
alter table s_student add address varchar(32);
-- 删除字段
alter table s_student drop address;
-- 修改字段类型
alter table s_student modify s_sex varchar(10);
-- 修改字段名
alter table s_student change s_sex sex VARCHAR(10);
-- 修改字段名 + 类型
alter table s_student change sex s_sex varchar(32);
-- 查询字段信息
desc s_student;

DML语句（操作语句）

主要是针对表中字段属性及其数据的操作，包括增删改查

-- 增添数据
		-- 方式一
insert into person values (1,'刘旭','男',21,12000,'JAVA');
		-- 方式二
insert into person(id, name, sex, age, salary, course) values (2,'黄月桂','女',21,12000,'JAVA');
		-- 方式三
insert into person(name, sex, age, salary, course) values ('周珍珍','女',21,12000,'Python');

-- 删除数据person
		-- 方式一：清空数据，不保留id顺序
truncate person;
		-- 方式二：清空数据，保留id顺序
delete from person;
		-- 单条件删除
		
delete from student where id=1;
		-- 多条件删除
delete from person where sex='女' and age=21;
delete from person where sex='男' or course='Python';
delete from person where course in('JAVA','Python');

-- 修改数据
update person set age=18 where name='黄月桂';
update person set salary=20000 where  sex='女' and age=21;
update person set salary=30000 where sex='男' or course='Python';
update person set salary=40000 where course in('JAVA','Python');

-- 查询数据
select *from person;
-- 单条件查询
select name, age,salary from person where id=1;
-- 多条件查询
select name, age,salary from person where sex='女' and course='Python';
-- 去重查询
select distinct name from pperson;
-- 模糊查询
select * from person where name like '%周%';
select * from person where name like '黄__';
-- 排序查询
select * from person order by salay asc;
select * from person order by age desc;
select * from person order by age desc, salary asc;
-- 分页查询
select * from person limit(0,1);
-- 分页查询公式： select * from 表名 limit((页数-1)*数据条数,数据条数);

-- 聚合查询
select max(salary) from student;
select min(salary) from student;
select sum(salary) from student;
select avg(salary) from student;
select count(id) from student;

-- 子查询：查找当前学生中工资最高的学生信息
select* from student where salary=(select max(salary) from student);

-- 分组查询:查询各个学科中的平均工资
select distinct course, avg(salary) from student group by course;

-- 分组过滤查询:查询平均工资大于8000的学科
select distinct course,avg(salary) from student group by course having avg(salary)>8000;

-- 内连接
select stu.name as 学生姓名,tea.name 教师姓名 from student stu inner join teacher tea inner join score sc on stu.id = sc.s_id and tea.id = sc.t_id;

-- 外连接
select stu.name as 学生姓名,sc.score as 成绩 from student stu left join score sc on stu.id = sc.s_id;
select stu.name as 学生姓名,sc.score as 成绩 from student stu right join score sc on stu.id = sc.s_id;


-- 全连接
select stu.name as 学生姓名,sc.score as 成绩 from student stu left join score sc on stu.id = sc.s_id
union
select stu.name as 学生姓名,sc.score as 成绩 from student stu right join score sc on stu.id = sc.s_id;

DCL语句（控制语句）

常用于创建账号和授予权限等操作

-- 创建子账号
create user 'lx'@'localhost' identified by '123456';
-- 授予子账号权限
grant select, insert on java2402.* to 'lx'@'localhost';
-- 撤回子账号权限
revoke insert on java2402.* from 'lx'@'localhost';
-- 删除子账号
drop user 'lx'@'localhost';

基本数据类型

整数类型

数值类型包括tinyint（1字节）、smallint（2字节）、mediumint（3字节）、int（4字节）、bigint（8字节）

一般存储状态码使用tinyint，没有特殊要求使用考虑int

CREATE TABLE user (
  id int(3) PRIMARY KEY auto_increment,
  name varchar(3) ,
  age int(3) unsigned zerofill 
);
-- unsigned 无符号
-- zerofill 零填充

浮点类型

小数类型包括float、double、decimal

但是float和double可能会失去精度，所以小数一般使用decimal，因为decima是以字符串形式存储小数

字符串类型

char为定长字符串，意味开辟固定长度空间（浪费空间）

varchar为可变长字符串，意味传入多大的字符串就开辟多少空间

MySQL 5.0 以前，char(32)表示字符串最大长度为32字节；5.0之后，char(32)表示最大长度为32字符

char(32)
varchar(32)

blob：存储二进制数据

longblob：存储极长的二进制数据

text：存储纯文本数据

longtext：存储极长的纯文本数据

时间日期类型

date
time
datetime
timestamp
year

# UPDATE CURRENT_TIMESTAMP - 数据行修改数据后，自定更新时间戳(只有 datetime 和 timestamp 类型支持自动更新)
CREATE TABLE user (
  id int(3) PRIMARY KEY  AUTO_INCREMENT,
  name varchar(3) COLLATE utf8mb4_general_ci DEFAULT NULL,
  age int(3) unsigned zerofill DEFAULT NULL,
  xxx timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
)

约束

主键约束： primary key

-- 主键约束
-- 方式一：
CREATE TABLE `student` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(32),
  PRIMARY KEY (`id`)
);

-- 方式二：
CREATE TABLE `student` (
  `id` int(3)  PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(32),
);
-- 删除主键
ALTER TABLE student drop PRIMARY KEY;

唯一约束：unique

-- 唯一约束
-- 方式一
CREATE TABLE `student` (
  `id` int(3) UNIQUE,
  `name` varchar(32),
) ;
-- 方式二
CREATE TABLE `student` (
  `id` int(3),
  `name` varchar(32),
) ;
ALTER TABLE student ADD UNIQUE(id); 

-- 删除唯一约束
ALTER TABLE student drop INDEX id;

非空约束：not null

-- 非空约束
-- 方式一
CREATE TABLE `student` (
  `id` int(3) NOT NULL,
  `name` varchar(32),
) ;

-- 方式二
CREATE TABLE `student` (
  `id` int(3),
  `name` varchar(32),
) ;
ALTER TABLE student MODIFY id INT(3) NOT NULL; 

-- 删除非空约束
ALTER TABLE student MODIFY id INT(3) NULL; 

外键约束：foreign key

-- 外键约束
-- 方式一
CREATE TABLE `student` (
  `id` int(3) NOT NULL,
  `name` varchar(32),
	CONSTRAINT type_id FOREIGN KEY(id) REFERENCES type(id); 
) ;

-- 方式二
CREATE TABLE `student` (
  `id` int(3),
  `name` varchar(32),
) ;
ALTER TABLE student ADD CONSTRAINT type_id FOREIGN KEY(id) REFERENCES type(id);

-- 删除非空约束
ALTER TABLE student DROP FOREIGN KEY type_id; 

索引

主键索引和唯一索引的添加、删除方式和主键约束和唯一约束一样，通过主键索引和唯一索引查询速度会很快

普通索引

# 普通索引 ----------------------------------------------------------------------------------
# 理解：普通索引对于数据没有约束，作为索引，字段作为查询条件会更快
-- 添加索引
ALTER TABLE student ADD INDEX(name);
-- 删除索引
DROP INDEX name on student;

全文索引

-- 添加全文索引
-- 添加解析器：WITH PARSER ngram（中、日、韩）
ALTER TABLE student ADD FULLTEXT(info) WITH PARSER ngram;
-- 使用全文索引
SELECT * FROM student WHERE MATCH(info) against('胡歌很帅');
-- 删除全文索引
DROP INDEX info on student;

索引面试题：

1、索引的数据结构？

B+树

2、索引的优缺点？

优点：索引字段作为查询会更快 缺点：添加、删除数据时，索引的结构会发生变化

解决方案：少量添加、删除没什么影响，批量添加、删除之前把索引删掉，操作完成之后再加上索引

3、索引的分类？

聚簇索引（主键索引）和非聚簇索引（唯一索引、普通索引、全文索引）

4、聚簇索引和非聚簇索引的区别？

聚簇索引叶子节点存储的是数据行（效率高），非聚簇索引叶子节点存储的是数据行的地址

5、索引失效的情况？

最短路径算法

最左匹配原则

OR关键字

模糊查询

函数

视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。（虚拟表）

-- 创建视图：with check option 不满足条件不能修改视图
create view student_view as select name, age from student where salary > 5000 with check option;
-- 使用视图：视图的使用与普通表一样
select * from student_view;
-- 删除视图
drop view student_view;

触发器

触发器分为前置触发器和后置触发器，前置触发器用于delete、update操作，后置触发器用于insert操作

-- 前置触发器
delimiter xx
create trigger before_delete before delete on student for each row 
begin 
	update course set sum=sum-1 where id=old.course_id;
	update class set sum=sum-1 where id=old.class_id;
end xx 
delimiter ;
-- 执行删除操作
delete from student where id=8;
-- 删除触发器
drop trigger before_delete;

-- 后置触发器
delimiter xx
create trigger after_insert after insert on student for each row 
begin 
	update course set sum = sum+1 where id = new.course_id;
	update class set sum=sum+1 where id =new.class_id;
end xx
delimiter ;
-- 执行插入操作
insert into student values(8,'李俊瑶', '女', 21,11000,'python', 1,2,3);
-- 删除触发器
drop trigger after_insert;

存储过程

-- 存储过程

delimiter //
create procedure pro01()
begin 
	select * from student;
	select * from course;
	select * from class;
end // 
delimiter ;

call pro01();

drop procedure pro01;-- 删除存储过程
-- ----------------------------------------------------------------------------------------------
-- 包含 in 的触发器
delimiter //
create procedure pro02(in s_age int(3))
begin 
select *from student where age=s_age;
end //
delimier ;

call pro02(21);
drop procedure pro02;
-- ----------------------------------------------------------------------------------------------
-- 包含 out 的触发器
delimiter //
create procedure pro03(in s_age int(3), out s_salary float(8,2))
begin 
select salary into s_salary from student where age = s_age;
end //
delimiter ;

call pro03(18, @s_salary);
select @s_salary;
drop procedure pro03;
-- ----------------------------------------------------------------------------------------------
-- 包含 inout 的触发器
delimiter //
create procedure pro04(inout param int(3))
begin 
select t_id into param from student where age = param;
end //
delimiter ;

set @param = 18;
call pro04(@param);
select @param;

drop procedure pro04;

函数

预定义函数

# 数学类函数 ----------------------------------------------------------------
SELECT ABS(-10);# 求绝对值
SELECT pow(2,3);# 求次方
SELECT SQRT(9);# 求平方根
SELECT MOD(10,3);# 求余数
SELECT RAND();# 求随机数

# 字符串类函数 ----------------------------------------------------------------
SELECT CONCAT("123","456");# 拼接
SELECT SUBSTRING("123456",2,2);# 截取(目标字符串，第几个字符，截取长度)
SELECT LENGTH("123456");# 获取长度（字节）

# 日期时间类函数 ----------------------------------------------------------------
SELECT NOW();
SELECT YEAR(now());
SELECT MONTH(now());
SELECT DAYOFMONTH(now());
SELECT HOUR(now());
SELECT MINUTE(now());
SELECT SECOND(now());
SELECT DATE_FORMAT(now(),"%Y年%m月%d日 %H时%i分%s秒");

自定义函数

# 注意：MySQL的函数必须有返回值
# 需求：创建函数，传入学生id，返回学生的姓名
# 8.0版本创建自定义函数必须声明 DETERMINISTIC
delimiter xx
CREATE FUNCTION fun(s_id INT(3)) RETURNS VARCHAR(32) DETERMINISTIC
	BEGIN
		DECLARE s_name VARCHAR(32);
		SELECT name INTO s_name FROM student WHERE id=s_id;
		RETURN s_name;
	END xx
delimiter ;

# 调用函数
SELECT fun(2);

# 删除函数
DROP FUNCTION fun;