一、SQL
1.什么是SQL
SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中。
2.SQL的通用语法
①SQL语句可以单行或多行书写，以分号结尾。
②SQL语句可以使用空格或缩进来增强语句的可读性。
③MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
④注释:
单行注释: -- 注释内容或 # 注释内容 (MySQL特有)
多行注释:/*注释内容*/
3.SQL的分类
4.DDL
DDL——数据库操作
①查询
查询所有数据库 show databases；
查询当前数据库 select database()；
②创建
create database [ if not exists ] 数据库名 [ default charset ] [ collate 排序规则 ]；
③删除
drop database [ if exists ] 数据库名；
④使用
use 数据库名；
DDL——表操作
①查询
查询当前数据库的所有表 show tables；
查询表结构 desc 表名；
查询指定表的建表语句 show create table 表名；
②创建
create table 表名 (
字段1 字段1类型 [comment 字段1注释]，
字段2 字段2类型 [comment 字段2注释]，
字段3 字段3类型 [comment 字段3注释]，
......
字段n 字段n类型 [comment 字段n注释]
) [comment 表注释]；
③修改
添加字段 alter table 表名 add 字段名 类型(长度) ；
修改字段类型 alter table 表名 modify 字段名 新数据类型(长度)；
修改字段名和字段类型 alter table 表名 change 旧字段名 新字段名 类型(长度)
删除字段 alter table 表名 drop 字段名；
修改表名 alter table 表名 rename to 新表名；
删除表 drop table 表名；
5.DML
DML——添加数据
①给指定字段添加数据
insert into 表名(字段名1，字段名2，......) velues (值1，值2，......)；
②给全部字段添加数据
insert into 表名 velues (值1，值2，......)；
③批量添加数据
为当前表中指定字段赋值
insert into 表名(字段名1，字段名2，......) velues (值1，值2，......)，(值1，值2，......)；
为当前表中所有字段赋值
insert into 表名 velues (值1，值2，......)，(值1，值2，......)；
注意
插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内
DML——修改数据
update 表名 set 字段名1 = 值1，字段名2 = 值2，....[ where 条件]；不加where：修改全部
DML——删除数据
delete from 表名 [where 条件]；
6.DQL
DQL——基本查询
①查询多个字段
select 字段1，字段2，字段三，...... from 表名；
select * from 表名；
②设置别名
select 字段1 [as 别名1]，字段2 [as 别名2]，字段3 [as 别名3]，...... from 表名；
③去除重复记录
select distinct 字段列表 from 表名；
DQL——条件查询
select 字段列表 from 表名 where 条件列表；
DQL——聚合函数
SELECT 聚合函数 (字段列表) FROM 表名；
注意：NULL值不参与所有聚合函数运算
DQL——分组查询
select 字段列表
from 表名
[where 条件]
group by 分组字段名
[HAVING 分组后过滤条件]；
where与having区别
执行时机不同: where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
判断条件不同: where不能对聚合函数进行判断，而having可以。
DQL——排序查询
select 字段列表
from 表名
order by 字段1 排序方式1，字段2 排序方式2
排序方式：升序 asc (默认值) 降序 desc
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;
DQL——分页查询
SELECT 字段列表
FROM 表名
LIMIT 起始索引，查询记录数；
注意事项:
起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
DQL——执行顺序
7.DCL
DCL——用户管理
查询用户
use mysql ；
select *
from user ；
创建用户
create user '用户名'@'主机名' identified by '密码'；
修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'；
删除用户
drop user '用户名'@'主机名' ；
DCL——权限控制
查询权限
show grants for '用户名'@'主机名' ;
授予权限
frant 权限列表 on 数据库名.表名 to '用户名'@'主机名' ;撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名' ;二、函数
1.常用的字符串函数
concat 字符串拼接
select concat('Hello' ，' MySQL');
lower 字符串全部转为小写
select lower('Hello");
upper 字符串全部转为大写
select upper('Hello");
lpad 左填充
select ipad('01', 5,'-');
rpad 右填充
select rpad('01', 5,'-');
trim 去掉字符串头部和尾部的空格
select trim(' Hello MysqL ");
substring 返回从字符串str从start位置起的len个长度的字符串
select substring('Hello MysqL",1,5);
2.常用的数值函数
3.常用的日期函数
4.常用的流程函数
三、约束
概念:约束是作用于表中字段上的规则，用于限制存储在表中的数据
目的:保证数据库中数据的正确、有效性和完整性。
create table user(
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment强姓名',
age int check ( age > && age <= 12 ) comment'年龄'
status char(1) default'1' comment"状态'
gender char(1) comment强性别
comment'用户表';外键约束
概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
添加外键
CREATE TABLE 表名(
字段名 数据类型
..
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)例：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表(主表列);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称外键约束删除更新行为
ALTERTABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;四、多表查询
1.连接查询
连接查询——内连接
内连接查询的是两张表交集的部分
隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件 ... ;显式内连接
SELECT 字段列表 FROM 表1 [INNER]JOIN 表2 0N 连接条件 ... ;连接查询——外连接
左外连接：相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据
SELECT 字段列表 FROM 表1 LEFT [OUTER]JOIN 表2 0N 条件 ... ;
/*外连接演示
1. 查询emp表的所有数据， 和对应的部门信恩(左外连接)
表结构: emp，dept
连楼条件:emp.dept_id = dept.id */
select e.*, dname from emp e left outer join dept d on e.dept_id = d.id;右外连接：相当于查询表2(右表)的所有数据 包含 表1和表2交集部分的数据
SELECT 字段列表 FROM 表1 RIGHT [OUTER]JOIN 表2 0N 条件 ... ;连接查询——自连接
自连接查询，可以是内连接查询，也可以是外连接查询
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件例：
/*自连接
1. 查询员工 及其 所属领导的名字
表结构: emp8*/
select a.name , b.name from emp a , emp b where a.managerid = b.id;
/*2.查询所有员工 emp 及其领导的名字 emp ， 如果员工没有领导， 也需要查询出来
表结构: emp a，emp b */
select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid = b.id;2.联合查询 ——union，union all
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION[ALL]
SELECT 字段列表 FROM 表B ...例：
/*1.将薪资低于5000的员工,和年龄大于50岁的员工全部查询出*/
select * from emp where salary < 5000
union all
select * from emp where age > 50;对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重
3.子查询
标量子查询
子查询返回的结果是单个值 (数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询。常用的操作符: = <> > >= < <=
/*1. 查询“销售部”的所有员工信息
a.查询'销售部'部广ID*/
select id from dept where name ='销售部';
/*b. 根据销售部部广ID,查询员工信息*/
select * from emp where dept_id = (select id from dept where name ='销售部');列子查询
子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询
常用的操作符:IN 、NOTIN、ANY 、SOME 、ALL
/*1.查询“销售部”和“市场部”的所有员工信息
a.查询“销售部”和“市场部”的部ID*/
select id from dept where name ='销售部' or name ='市场部';
/*b. 根据部门ID， 查询员工信息*/
select * from emp
where dept_id in (select id from dept where name = '销售部' or name = '市场部');
/*2. 查询比财务部所有人工资都高的员工信息
a.查询所有财务部人员工资*/
select id from dept where name ='强财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');
/*b.比财务部所有人工资都高的员工信息*/
select * from emp
where salary > all (select salary from emp where dept_id =
(select id from dept where name = '财务部') );行子查询
子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询
常用的操作符:= 、<>、IN 、NOT IN
/*行子查询
1. 查询与“张无忌” 的薪资及直属领导相同的员工信息 ;
a. 查询“张无忌” 的薪资及直属领导*/
select salary，managerid from emp where name ='张无忌';
/*b.查询与“张无忌” 的薪资及直属领导相同的员工信息 ;*/
select * from emp where (salary,managerid)=
(select salary,managerid from emp where name = '张无忌');表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询
常用的操作符:IN
/*1.查询与“鹿杖客”,"宋远桥”的职位和薪资相同的员工信息
a.查询 “鹿杖客”,“宋远桥” 的职位和薪资*/
select job, salary from emp where name='鹿杖客' or name ='宋远桥';
/*b，查询与"鹿杖客”,"宋远桥”的职位和薪资相同的员工信息*/
select * from emp
where (job,salary) in ( select job, salary from emp where name='客' or name='宋远桥');
/*2.查询入职日期是“2006-01-01”之后的员工信息,及其部门信息
a.入职日期是 “2006-01-01” 之后的员工信息*/
select * from emp where entrydate > '2006-01-01':
/*b.查询这部分员工,对应的部门信息;*/
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e
left join dept d on e.dept-id = d.id ;五、视图
1.创建视图
CREATE [OR REPLACE] VIEW 视图名称(列名列表) AS SELECT语句
[WTH[CASCADED / LOCAL] CHECK OPTION]
create view 视图名称(列名列表) as select语句2.查询视图
//查看创建视图语句
show create view 视图名称;
//查看视图数据
select * from 视图名称 ... ;3.修改视图
方式一
create [or replace] view 视图名称[(列名列表)] as select语句
[WTH[CASCADED / LOCAL] CHECK OPTION]
方式二
alter view 视图名称[(列名列表)]as select语句
[WTH[CASCADED / LOCAL] CHECK OPTION]4.删除视图
drop view [if exists] 视图名称 ...总结：1.视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2.数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
3.视图可帮助用户屏蔽真实表结构变化带来的影响
六、存储过程
就是数据库SQL 语言层面的代码封装与重用
1.创建与调用
//创建
create procedure 存储过程名称([参数列表])
begin
sql语句
end;
//调用
call名称([参数]);
//查看
查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema = 'xxx';
查询某个存储过程的定义
show greate procedure 存储过程名称;
//删除
drop procedure [if exists] 存储过程名称;例
//创建
create procedure p1()
begin
select count(*) from student;
end;
//调用
call p1();
//查看
select * from test.emp where routine_schema = 'xxx';
或者
show create procedure p1;
//删除
drop procedure if exists p1;注意: 在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符
2.存储过程的参数
create procedure 存储过程名称([in/out/inout 参数名 参数类型])
begin
sql语句
end;例
/*根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)
score >= 85分，等级为优秀
score >= 6日分 月 score < 85分，等级为及格
score < 6日分，等级为不及格*/
create procedure p4(in score int, out result varchar(10))
begin
if score >= 85 then
set result :='优秀';
elseif score >= 60 then
set result :='及格';
else
set result :='不及格';
end if;
end;
call p4(68,@result);
3.变量
系统变量
是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)
//查看系统变量
show [session | global] variables; ---查看所有系统变量
show [session | global] variables like '...' ; ---可以通过LIKE模糊匹配方式查找变量
show @@[session | global] 系统变量名; ---查看指定变量的值
//设置系统变量
set [session | global] 系统变量名=值;
set @@[session | global] 系统变量名=值;
注意:如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置
用户定义变量
是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。
//赋值
sat @var_name=expr [,@var_name=expr]...;
或者
sat @var_name:=expr [,@var_name:=expr]...;
select @var_name:=expr [,@var_name:=expr]...;
或者
select 字段名 into @var_name from 表名;
//使用
select @var_name;例
//赋值
set @myname = 'itcast!;
set @myage := 10;
set @mygender :=男' @myhobby := java' ;
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
//使用
select @myname , @myage , @mygender , @myhobby;
select @mycolor ，@mycount;局部变量
是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN... END块
//声明
declare 变量名 变量名类型[default...];
变量类型就是数据库字段类型：int,bigint,char,varchar,date,time等
//赋值
set 变量名:=值;
或者
select 字段名 into 变量名 from 表名...;例
create procedure p2()
begin
declare stu_count int default 0;
select count(*) into stu_count from student;
select stu_count;
end;
call p2();4.流程控制结构
if条件判断
if 条件1 then
...
elseif 条件2 then
...
else
...
end if;case循环
//语法一
case case_value
when when_value1 then statement_list1
[when when_value2 then statement_list2]...
[else statement_list]
end case;
//语法二
case
when search_condition1 then statement_list1
[when search_condition2 then statement_list2]
[else statement_list]
end case;例
/*case
根据传入的月份，判定月份所属的季节(要求采用case结构)
1-3月份，为第一季度
4-6月份，为第二季度
7-9月份，为第三季度
10-12月份，为第四奔度*/
create procedure p(in month int)
begin
declare result varchar(10);
case
when month >= 1 and month <= 3 then
set result :='第一季度';
when month >= 4 and month <= 6 then
set result :='第二季度';
when month >= 7 and month <= 9 then
set result :='第三季度;
when month >= 10 and month <= 12 then
set result :='第四季度';
else
set result := '非法参数';
end case
select concat('您输入的月份为:',month，'所属的季度为:',result);
end;
call p(8);while循环
//先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
sql逻辑...
end while;
//计算从1累加到的价，n为传入的参数值
定义局部变量， 记录累加之后的值;
每循环一次,就会0n,进行减1,如n减到0,则退出循环
create procedure p7(in n int)
begin
declare total int default 0;
while n>0 do
set total := total + n;
set n := n - 1;
end while;
select total;
end;
call p7(100);repeat循环
#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
sql逻辑
until 条件
end repeat;
例：计算从1累加到n的值，n为传入的参数值
create procedure p8(in n int)
begin
declare total int default 0;
repeat
set total := total + n;
set n := n - 1;
until n <= 0
end repeat;
end;
call(p8);loop循环
LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用：
LEAVE:配合循环使用，退出循环。
ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
[begin_label:] loop
sql逻辑...
end loop [end_label];例
1.计算从1累加到n的值，n为传入的参数值。
create procedure p9(in n int)
begin
declare total int default 0;
sum:loop
if n<=o then
leave sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;
end;
call p9(100);
2.计算从1到n之间的偶数累加的值，n为传入的参数值。
create procedure p1(in n int)
begin
declare total int default 0;
sum:loop
if n<=0 then
leave sum;
end if;
if n%2 = 1 then
set n := n-1;
iterate sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;
end
call p10(10);游标
游标(CURSOR)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下
//声明游标
declare 游标名称 cursor for 查询语句;
//打开游标
open 游标名称;
//获取游标记录
fetch 游标名称 into 变量[,变量];例
根据传入的参数uage，来查询用户表tb_user中所有的用户年龄小于等于uage的用户姓名 (name)
和专业(profession)，并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中
create procedure p11(in uage int)
begin
//定义游标记录符合条件的结果集
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age<= uage;
declare exit handler for SQLSTATE '02000' close u_cursor;
//如果满足后面的状态码就执行退出处理 然后关闭游标
drop table if exists tb_user_pro;
create table if not exists tb_user_pro( //如果有这个tb_user_pro表先删除在创建
id int primary key auto_increment //创建表结构
name varchar(100)
profession varchar(100)
);
open u_cursor; //开启游标
while true do
fetch u_cursor into uname ,upro; //循环获取游标中的数据
insert into tb_user_pro values (null, uname, upro); //将这三个数据插入到新表中
end while;
close u_cursor; //关闭游标
end;
call p11(40);条件处理程序
条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤
七、索引
索引(index)是帮助MySQL高效获取数据的数据结构(有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
优缺点
1.索引的结构
二叉树
二叉树缺点: 顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢
红黑树缺点: 大数据量情况下，层级较深，检索速度慢
B-Tree(多路平衡查找树)
以一颗最大度数(max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key，5个指针)
B+Tree
以一颗最大度数(max-degree)为4 (4阶)的b+tree为例
MySOL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
相对于B-Tree区别:
1.所有的数据都会出现在叶子节点
2.叶子节点形成一个单向链表
hash索引
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存在hash表中。
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突(也称为hash碰撞)，可以通过链表来解决
Hash索引特点
1.Hash索引只能用于对等比较(=，in)，不支持范围查询 (between，>，<，...)
2.无法利用索引完成排序操作
3.查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引
2.索引的分类
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种
聚集索引选取规则:
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则innoDB会自动生成一个rowid作为隐藏的聚集索引。
3.索引的语法
//创建索引
create[unique|fulltext] index index_name on table_name(index_col_name,...);
//查看索引
show index from table_name;
//删除索引
drop index index_name on table_name;
36万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
