|
SQL 分为四类 1、DML（数据操作语言）操作数据库中的数据（insert、update、delete） 2、DDL（数据定义语言）创建、删除、修改数据库、数据表 3、DQL（数据查询语言）对数据库中的数据进行查询（select） 4、DCL（数据控制语言）用来提交数据库事务（commit、rollback）
|
目录
六、视图View
七、触发器Trigger
八、存储过程Procedure
一、管理数据库结构
返回顶部目录数据库 -》数据表 -》数据
1、操作数据库 SQL
返回顶部目录1、创建数据库
create database 数据库名称 default character set utf8 collate utf8_general_ci;
2、删除数据库
drop database 数据库名称;
3、查看数据库
show databases;
4、选择数据库
use 数据库名称;
2、 操作数据表 SQL
返回顶部目录1、创建数据表
create table 数据表名(
字段信息
字段名 数据类型,
);
create table student(
id int,
name varchar(10),
score int
);
2、删除数据表
drop table 数据表名;
3、查看数据表
show tables;
4、查看某张表的结构
desc 数据表名;
5、修改数据表
新增一个字段
alter table 数据表名 add 字段名 字段数据类型;
修改一个字段
alter table 数据表名 change 旧字段名 新字段名 新字段数据类型;
删除一个字段
alter table 数据表名 drop 字段名;
3、SQL 函数
返回顶部目录3.1 数学函数
abs() 求绝对值
select abs(num) from user;
floor() 返回小于参数的最大整数
select floor(num) from user;
ceil() 返回大于参数的最小整数
select ceil(num) from user;
3.2 字符串函数
insert(str1,index,len,str2)
str1中index位置开始，长度为len的字符替换为str2,index从1开始
select insert(name,2,2,'java') from user;
upper(),ucase()
将字母值变为大写
select upper(name) from user;
select ucase(name) from user;
lower(),lcase()
将字母值变为小写
select lower(name) from user;
select lcase(name) from user;
left(str,len)
返回str字符串的前len个字符
select left(name,3) from user;
right(str,len)
返回str字符串的后len个字符
select right(name,3) from user;
substring(str,index,len)
截取str，从index位置开始，长度为len
select substring(name,2,2) from user;
reverse()
反序输出
select reverse(name) from user;
3.3 日期函数
curdate() current_date() 获取当前日期
select current_date();
select curdate();
curtime() current_time() 获取当前时间
select curtime();
select current_time();
now() 获取当前日期+时间
select now();
datediff(day1,day2) 计算day1和day2之间相隔的天数
select datediff('2022-5-1','2022-3-31');
adddate(day,num) 计算day日期num天之后的日期
select adddate('2022-2-20',173);
subdate(day,num) 计算day日期num天之前的日期
select subdate('2022-2-20',173);
3.4 聚合函数
count() 根据某个字段统计记录数
select count(*) from user;
sum() 计算某个字段值的总和
select sum(num) from user;
avg() 求某个字段值的平均值
select avg(num) from user;
max() 求某个字段值的最大值
select max(num) from user;
min() 求某个字段值的最小值
select min(num) from user;
select name,count(id) from user group by name order by count(id) desc;
select name,count(id) from user group by name having count(id) > 2 order by count(id) desc;
where 和 having 的区别
where 和 having 都是追加条件的，区别在于 SQL 是否进行了分组查询，如果进行了分组查询则使用 having，如果没有进行分组查询则使用 where。
4、 SQL 运算符
返回顶部目录4.1 算术运算符
1、执行运算符：加减乘除
select num+100 from user;
2、比较运算符：大于、等于、小于、不等于
select num > 100 from user;
3、逻辑运算符：&&、||、!
select !(num < 100 || num > 50) from user;
4.2 特殊运算符
1、is null 判断字段值是否为空
select name is null from user;
2、between and 判断字段值是否在某个区间之内
select num between 50 and 100 from user;
3、in 判断字段值是否在某个确定值的集合内
select name from user where id in (1,2,3);
select name from user where id = 1 || id = 2 || id = 3;
4、like 模糊查询
包含’电脑’
select * from user where name like '%电脑%';
以’电脑’开头
select * from user where name like '电脑%';
以’电脑’结尾
select * from user where name like '%电脑';
name 长度为 2
select * from user where name like '__';
name 长度为 3，并且以’电’开头
select * from user where name like '电__';
name 长度为 3，中间为’电’
select * from user where name like '_电_';
name 长度为 3，以’电’结尾
select * from user where name like '__电';
5、 数据 CRUD
返回顶部目录CRUD
create 创建
read 读取
update 修改
delete 删除
添加数据
insert into 数据表(字段列表) values(字段值)
insert into user VALUES('数据库',300,33);
查询数据
select 字段列表 from 数据表名;
select id,name from user;
select * from user;
修改数据
update 数据表名 set 字段名=字段值,字段名=字段值,... where 条件
删除数据
delete from 数据表名 where 条件
二、表关系
返回顶部目录1、表设计
返回顶部目录1.1主键
表中的一个字段，将其设置为主键，该字段的值是每一行记录的唯一标识。
默认情况下，每张表都要有一个主键，一张表只能有一个主键。
主键的值必须唯一。
主键生成策略：代理主键，主键的值与业务无关，仅仅是用来标识一行数据的
一般定义为 int 类型，因为 int 类型存储空间小，而且可以设置自增，当然有例外情况，订单编号，要求唯一，需要使用 String UUID
create table student(
id int primary key auto_increment,
name varchar(11),
age int
);
1.2外键
表中的一个字段，将其设置为外键，用来跟其他表进行关联的
外键必须跟其他表的主键建立约束关系
学生和班级
| 编号 | 名称 |
|---|---|
| 001 | Java1班 |
| 002 | Java2班 |
| 编号 | 姓名 | 班级 |
|---|---|---|
| 1 | 张三 | |
| 2 | 李四 | |
| 3 | 王五 |
学生一定属于某个班级，所以学生表就需要有一个外键，用来约束学生必须对应一个存在的班级
数据表的体现就是学生表的外键要跟班级表的主键建立约束关系，学生表的班级信息需要被班级表的主键所约束
create table class(
id int primary key auto_increment,
name varchar(11)
);
create table student(
id int primary key auto_increment,
name varchar(11),
cid int,
foreign key(cid) references class(id)
);
2、表之间关系
返回顶部目录数据表之间的关系分三种：
1、一对一：A 表中的一条数据只能对应 B 表中的一条数据，B 表中的一条数据只能对应 A 表中的一条数据。
2、一对多：A 表中的一条数据只能对应 B 表中的一条数据，B 表中的一条数据可以对应 A 表中的多条数据。
3、多对多：A 表中的一条数据可以对应 B 表中的多条数据，B 表中的一条数据可以对应 A 表中的多条数据。
一对一关系一般不用，主要用的是一对多和多对多
多对多
学生选课
学生表 account、课程表 course、中间表 account_course
create table account(
id int primary key auto_increment,
name varchar(11)
);
create table course(
id int primary key auto_increment,
name varchar(11)
);
create table account_course(
id int primary key auto_increment,
cid int,
aid int,
foreign key (cid) references course(id),
foreign key (aid) references account(id)
);
account 和 course 都是主表，account_course 是从表，主表中的主键要约束从表中的外键。
两个列作为一个主键，叫做联合主键
三、多表关联查询
返回顶部目录1、关联查询
返回顶部目录多张表进行关联查询
嵌套查询
select name from class where id = (select cid from student where name = '张三');
将两个 SQL 语句进行嵌套处理，第二条 SQL 的值作为第一条 SQL 的条件进行查询
连接查询
- 内连接（笛卡尔积）
select * from student inner join class on class.id = cid;
select s.id sid,s.name sname,c.id cid,c.name cname from student s,class c where c.id = s.cid;
-
外连接
- 左连接：左表所有数据和右表满足条件的数据
select * from student left join class c on student.cid = c.id;- 右连接：右表所有数据和左表满足条件的数据
select * from student right join class c on student.cid = c.id;
2、一对多关联查询
返回顶部目录查询全部学生信息，包括所在班级信息
select s.id sid,s.name sname,c.id cid,c.name cname from student s,class c where s.cid = c.id;
查询全部班级，包括班级的学习
select c.id cid,c.name cname,s.id sid,s.name sname from student s,class c where s.cid = c.id;
3、多对多关联查询
返回顶部目录select a.id aid,a.name aname,c.id cid,c.name cname from account a,course c,account_course ac
where a.id = ac.aid and c.id = ac.cid;
4、去重distinct
返回顶部目录select distinct a.id aid,a.name aname,c.id cid,c.name cname from account a,course c,account_course ac
where a.id = ac.aid and c.id = ac.cid;
去重操作只需要在 SQL 语句中添加 distinct 关键字即可，去重的要求是查询出来的数据必须完全一致，每个字段值都一样。
5、分页limit
返回顶部目录分页查询是在 SQL 语句中添加 limit 进行，limit index,length
index：截取的起始位置
length：截取的长度
select * from account limit 0,10;
四、数据库索引Index
返回顶部目录1、数据库索引
返回顶部目录索引是一种特殊的数据库结构，可以用来快速查询数据表中的特定记录，它是提高数据库性能的重要方式，所有字段都可以添加索引。
主键自带索引，索引就是用来提高查询速度的。
索引包括：普通索引、唯一性索引、全文索引、单列索引、多列索引、空间索引
虽然使用索引可以提升检索数据的速度，但是创建和维护索引需要耗费时间，索引需要占用物理空间，给字段添加索引的时候需要结合具体情况来选择。
普通索引：不需要任何限制条件的索引，可以在任意数据类型的字段上创建。
唯一性索引：索引的值必须唯一，比如主键索引。
全文索引：只能创建在 char、varchar、text 类型的字段上，查询数据量较大的字符串类型字段时，使用全文索引可以提高查询速度，InnoDB 存储引擎不支持全文索引。
单列索引：只对应一个字段的索引。
多列索引：在一张表的多个字段上创建一个索引，跟联合主键类似。
空间索引：只能建立在空间数据库上（GIS），InnoDB 存储引擎不支持该索引。
索引的特点：
- 主键自带索引
- 可以给任何字段添加索引
索引的设计原则：
- 字段出现在 where 语句中的列
- 索引的值尽量唯一，效率更高
- 不要添加过多的索引，维护成本很高
1、添加索引
alter table account add index index_name(name);
create index index_name on account(name);
2、删除索引
alter table account drop index index_name;
drop index index_name on account;
2、数据库索引机制的实现
五、事务Transaction
1、事务
返回顶部目录将多条 SQL 作为一个整体，要么全部执行，要么一条都不执行。
张三 1000，李四 1000
张三借给李四 500
张三 500，李四 1500
update user set money = 500 where name = '张三';
update user set money = 1500 where name = '李四';
事务的特点：
- 原子性：多条 SQL 是一个整体，不可再分割
- 一致性：SQL 执行前后，数据库的数据值保持一致
- 隔离性：一个事务的执行不能被其他事务所干扰
- 持久性：一个事务一旦提交，数据库中数据的改变是永久性的
事务的具体操作：
1、开启事务
start transaction;
2、回滚
rollback;
3、提交
commit;
关闭事务的自动提交，MySQL 默认的执行方式是自动提交，默认开启了自动提交。
关闭自动提交
set autocommit = 0;
开启自动提交
set autocommit = 1;
查看 autocommit 的值
show variables like 'autocommit';
start transaction;
update user set money = 500 where id = 1;
update user set money = 1500 where id = 2;
rollback;
commit;
2、Mysql的事务隔离机制
返回顶部目录点击延伸阅读>
Mysql的事务隔离机制
六、视图View
返回顶部目录数据库中的一张虚拟表，允许不同用户或应用程序以不同的方式查看同一张表中的数据。
创建视图
create view view_common as select id,name from user;
create view view_all as select * from user;
使用视图
select * from view_common;
删除视图
drop view view_common;
七、触发器Trigger
返回顶部目录触发器定义了一系列的操作，可以对指定表进行插入，更新，删除操作的同时自动执行这些操作。
触发器的优点：
- 开发更快，因为触发器存储在数据库中的，所以不必编写每个触发器在应用程序中执行的操作
- 更容易维护，定义触发器之后，访问目标表，会自动调用触发器。
- 业务全局实现，如果修改业务，只需要修改触发器即可，不需要修改业务代码。
create table tab1(
tab1_id varchar(11)
);
create table tab2(
tab2_id varchar(11)
);
create trigger t_afterinsert_on_tab1
after insert on tab1
for each row
begin
insert into tab2(tab2_id) values(new.tab1_id);
end;
删除触发器
drop trigger t_afterinsert_on_tab1;
create trigger t_afterdelete_on_tab1
after delete on tab1
for each row
begin
delete from tab2 where tab2_id = old.tab1_id;
end;
八、存储过程Procedure
返回顶部目录存储过程是一组为了完成特定功能的 SQL 语句的集合，存储在数据库中的，用户可以直接进行调用，类似于方法的调用。
一次编写，多次使用，避免开发人员重复编写相同的 SQL。
创建存储过程
参数：
1、输入输出类型：入参、出参
入参是指传到存储过程中的参数，类似于 Java 中的参数
出参是指存储过程返回的数据，类似于 Java 中的返回值
2、参数名称
3、参数类型
create procedure add_name(in target int)
begin
declare name varchar(20);
if target = 1 then
set name = 'MySQL';
else
set name = 'Java';
end if;
insert into user(name) values (name);
end;
调用存储过程
call add_name(2);
删除存储过程
drop procedure add_name;
出参
create procedure count_of_course(out num int)
begin
select count(*) into num from course;
end;
call count_of_course(@num);
select @num;
create procedure test(in num int,out val int)
begin
if num = 1 then
select id into val from user;
else
select money into val from user;
end if;
end;
call test(2,@val);
select @val;
if
create procedure example_if(in x int)
begin
if x = 1 then
select id from student;
elseif x = 2 then
select name from student;
end if;
end;
call example_if(2);
case-when
create procedure example_case(in x int)
begin
case x
when 1 then select id from student;
when 2 then select name from student;
else select cid from student;
end case;
end;
call example_case(6)
while
create procedure example_while(out sum int)
begin
declare i int default 1;
declare s int default 0;
while i<=100 do
set s = s+i;
set i = i+1;
end while;
set sum = s;
end;
call example_while(@sum);
select @sum;
推荐阅读：
数据库的设计
Mysql的执行流程
4150
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
