mysql数据库基础知识集-必知必会

一、SQL语言

1、SQL语言分类

（1）数据定义语言DDL：用于创建，修改，删除数据库中的各种对象（数据库、表、视图、索引等），常用命令有CREATE，ALTER，DROP
（2）数据操作语言DML：用于操作数据库表中的记录，常用命令有INSERT，UPDATE，DELETE
（3）数据查询语言DQL：用于查询数据库表中的记录，基本结构：SELECT <字段名> FROM <表或视图名> WHERE <查询条件>
（4）数据控制语言DCL：用于定义数据库访问权限和安全级别，常用命令：GRANT，REVOKE

2、SQL书写要求

（1）SQL语句可以单行或多行书写，用分号结尾
（2）SQL关键字用空格分隔，也可以用缩进来增强语句的可读性
（3）SQL对大小写不敏感
（4）可用#或-- 单行注释，用/* */多行注释，注释语句不可执行

3、SQL书写流程

3.1 数据库操作

1、Create database great_list;# 创建数据库
2、drop database 数据库名; #删除数据库
3、show database ;# 查看有什么库
4、select database(); #查看当前使用的数据库
5、use database 数据库名; #使用数据句库

3.2 数据表结构增删改

3.2.1 增

1、show tables;查看数据库中所有表
2、Desc 表; #查看表结构
3、create table table_name(字段名 数据类型 可选的约束条件）；#创建表结构的语法
（1）demo：创建班级和学生表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(10)
);
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default ‘’,
age tinyint unsigned default 0,
height decimal(5,2),
gender enum(‘男’,‘女’,‘人妖’,‘保密’),
cls_id int unsigned default 0
)
4、修改表名：alter table <原表名> rename <新表名>;
5、show create table 表名; #查看表的创建语句–详细过程
demo : show create tabele students;

3.2.2 删

1、drop table 表名; #删除表
demo：drop table students;

3.2.3 改

1、修改表–添加字段：alter table 表名 add <字段名> 字段类型;##
demo：alter table students add birthday datetime;
2、修改表字段名：alert table 表名 change 原名 新名 类型及约束;##
demo：alter table syudents change birthday birth datetime not null;
3、alter table 表名 drop <字段名> #删除表–删除字段
demo :later table students drop birthday;
4、修改字段类型：alter table 表名 modify <字段名> <新字段类型>及约束;
demo : alter table students modify birth date nout noll;
5、修改字段的排列位置：alter table <表名> modify <字段名> <数据类型> first|after参照字段名;

3.3 数据表内容增删改

3.3.1 增加

1、全列插入：值的顺序与表结构字段的顺序完全一一对应，此时 字段名列表不用填写
insert into 表名 values (…)；
例：
insert into students values(0,’郭靖‘,1,‘蒙古’,‘2016-1-2’);
2、部分列插入：值的顺序与给出的列顺序对应，此时需要根据实际的数据的特点 填写对应字段列表
insert into 表名 (列1,…) values(值1,…)
例：
insert into students(name,hometown,birthday) values(‘黄蓉’,‘桃花岛’,‘2016-3-2’);
3、全列多行插入
insert into 表名 values(…),(…)…;
例：
insert into classes values(0,‘python1’),(0,‘python2’);
insert into student values(1001,‘123456’,‘赵六’,‘男’,1507,120,‘北京’,‘23-5月-1995’,’@10422’);
4、部分列多行插入
insert into 表名(列1,…) values(值1,…),(值1,…)…;
例：
insert into students(name) values(‘杨康’),(‘杨过’),(‘小龙女’);

3.3.2 删

1、delete from 表名 where 条件;where子句表示为删除操作限定删除条件，若省略则代表删除表中的所有行
例：
delete from students where id=5;
2、逻辑删除，本质就是修改操作
update students set isdelete=1 where id=1;
3、delete与truncate的区别：
（1）delete可以添加删除条件删除表中部分数据，truncate只能删除表中全部数据。
（2）delete删除表中数据保留表结构，truncate直接把表删除(drop)然后再创建(create)一张新表，执行速度比delete快。

3.3.3 改

1、update <表名> set <字段1>=<值1>,<字段2>=<值2>…<字段n>=<值n>] [where <更新条件>];
例：
update students set gender=0,hometown=‘北京’ where id=5;
（1）set子句用于指定表中要修改的字段名及其字段值。每个指定的值可以是表达式，也可以是该字段对应的默认值。如果指定的是默认值，可用关键字default。
（2）where子句用于限定表中要修改的行。若不指定，则修改表中所有的行。
（3）修改多个字段值时，set子句的每个值用逗号分开即可。

3.4 条件语句查询

3.4.1 SQL语句执行及书写顺序

from -> where -> group by -> having -> select -> distinct -> order by -> limit

*select …* 组函数(*MIN()/MAX()/SUM()/AVG()/COUNT()*)
*from …join … on …* 左外连接：left join … on … 右外连接: right join … on …
*where …
group by …* (*oracle,SQL server中出现在select 子句后的非分组函数，必须出现在 group by子句后)*
*having …* 用于过滤 组函数
*order by …* asc 升序， desc 降序

*limit (0,4)* 限制N条数据 如: topN数据*

*–union 并集
–union all(有重复)
–intersect 交集
–minus 相减*

在MySQL5.7.5及之后的版本，select设置别名后（as**），可以在group by，having和order by子句中来引用select中的别名

3.4.2 where条件判断

1、where后面支持多种运算符，进行条件的处理：
（1）比较运算符： 等于: =、大于: >、大于等于: >=、小于: <、小于等于: <=、不等于: != 或 <>
（2）逻辑运算符： and、or、not
（3）模糊查询： %表示任意多个任意字符；_表示一个任意字符

（1）语法
select <字段名列表> from 表名 where 字段名 like <通配符>;
（2）举例
select * from emp where ename like '%a%';#查询姓名中包含a的员工信息
select * from emp where ename like 'a%';#查询姓名以a开头的员工信息
select * from emp where ename like '_a%';#查询姓名中第二个字符为a的员工信息,"_"只匹配单个字符

（1）不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
（2）在确实需要使用通配符时，也尽量**不要把它们用在搜索模式的开始处**，否则搜索起来是非常慢的。
（3）注意通配符的位置。如果放错地方，可能不会返回想要的数据

（4）范围查询：范围查询分为连续范围查询和非连续范围查询；in表示在一个非连续的范围内；between … and …表示在一个连续的范围内
（5）空判断： 1. null与’ '空是不同的， 2. 空 is null，3.判非空is not null
（6）优先级
优先级由高到低的顺序为：小括号 > not > 比较运算符 > 逻辑运算符（and比or先运算，如果同时出现并希望先算or，需要结合()使用）

3.5 排序

（1）asc从小到大排列，即升序；默认按照列值从小到大排列（即asc关键字）
（2）desc从大到小排序，即降序

3.6 分页查询

1、limit

（1）从start开始，获取count条数据，start默认值为0，也就是当用户需要获取数据的前n条的时候可以直接写上 xxx limit n; 在sql语句中limit后不可以直接加公式

select * from 表名 limit start=0,count
select * from students where gender=1 limit 0,3;# 查询前3行男生信息

有些时候我们仅需要返回第一行或者前几行，这时候就需要用到LIMT 子句

（2）select <字段名列表> from <表名> limit [偏移量,] 行数;

① lmit接受一个或两个数字参数，参数必须是一个整数常量。
② 第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
③ 如果只给定一个参数，表示返回最大的记录行数目。
④ 初始记录行的偏移量是0(而不是1)。*

给定两个参数：查询基本工资第6到10名的员工
select ename,sal
from emp
order by sal desc
limit 5,5;

2、倒序方式

顺序选出前50行数据；倒序后选出前10行，但是此时是倒序排列，即选出50-41行的数据；将数据再一次倒序排列，即选出了41-50行数据

select *
from (select top 10 *
      from (select top 50 *
            from Table_name
            order by id) as t1
      order by id desc) as t2
order by id

3、not in方式

排除前40行数据，从第41行开始，顺序选10行，即选出了41-50行数据

select top 10 *
from Table_name
where id not in (select top 40 id
                 from Table_name
                 order by id)
order by id

4、ROW_NUMBER()方式

按id顺序排序后给每行数据进行编号；选出编号在41-50的数据，即41-50行数据

select * 
from (select *,ROW_NUMBER() OVER(Order by id) as RowNumber
      from Table_name) as t
where t.RowNumber between 41 and 50

3.7 group by 分组

分组后需要对每个组内的数据进行聚合运算，可以使用sum、avg、count、max、min函数对数值型数据进行聚合计算，使用max和min函数对日期时间型数据进行聚合，使用group_concat函数对字符串型数据进行分组合并

3.7.1 拼接 group_concat()

group_concat(字段名)根据分组结果，使用group_concat()来放置每一个分组中某字段的集合，以；分隔

（1）单字段拼接
select gender,group_concat(distinct name order by name desc separator ';') from students group by gender; 
group_concat()：里面也可以用distinct/order by /separator等
（2）多字段拼接，以逗号隔开
select group_concat(name，sex) as name from student where classId = 1

3.7.2 聚合函数

select gender,count(*) from students group by gender; 
也可以使用sum、avg、count、max、min等函数

3.7.3 筛选 having

在SELECT 语句中，使用GROUP BY子句进行分组后，如果需要对分组后的数据进行筛选，可以使用HANING子句指定筛选条件，用来过滤分组结果

（1）having只能接分组字段、聚合字段、或者跟分组字段一一对应的字段

（2）having作用和where类似，但having只能用于group by 而where是用来过滤表数据

① WHERE 子句主要用于过滤表，而 HAVING 子句主要用于过滤分组。
② WHERE 子句不可以使用聚合函数，HAVING 子句中可以包含聚合函数。
③ HAVING 子句是在数据分组后进行过滤，WHERE 子句会在数据分组前进行过滤，WHERE 子句排除的行不包含在分组中

3.7.4 新增汇总行 with rollup

with rollup的作用是：在最后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果。

select gender,count(*) from students group by gender with rollup; 

3.8 横向合并

一表作为主表可以保证维度的完整性，多表作为主表可以保证度量的准确性。在没有明确表示需要保证维度完整性的情况下，优先保证度量的准确性，所以将度量值所在的表作为主表。度量字段通常存在于多表中，因此通常情况下可以将多表作为主表进行外连接。

3.8.1 内连接

[ inner] join按照连接条件，返回两张表中满足条件的记录

3.8.2 左连接

left join按照连接条件，返回两张表中满足条件的记录，以及左表中的所有记录，右表匹配不到显示null。

3.8.3 笛卡尔积连接

两张表中的每一条记录进行笛卡尔积组合，然后根据where条件过滤虚拟结果集中的记录。

select <字段名1>[,<字段名2>...,<字段名n>] from <表名1>,<表名2> where <筛选条件>；
select ename,job,hiredate,sal,dname
from emp,dept
where emp.deptno=dept.deptno;

3.8.4 自连接

通过设置表别名，将一张表虚拟成多张表。

3.8.5 连接条件

（1）等值连接：连接条件是两张表中的关键字段取值相等
select ename,job,hiredate,sal,dname
from emp
inner join dept
on emp.deptno=dept.deptno;
（2）不等值连接：连接条件是两张表中的关键字段取值满足非等值比较运算
select ename,job,hiredate,sal,grade
from emp
inner join salgrade
on sal between losal and hisal;

3.9 纵向合并

纵向合并查询又称为联合查询，它是用union关键字把多条select语句的查询结果合并为一个结果集；纵向合并的前提是被合并的结果集的字段数量、顺序和数据类型必须完全一致。字段名不一样的情况下，会将第一个结果集的字段名作为合并后的虚拟结果集的字段名

（1）语法
select <字段1>[,<字段2>,…] from <表名1>
union[ all]
select <字段1>[,<字段2>,…] from <表名2>;

（2）举例
union去重：
select * from t1
union
select * from t2;

union all不去重：
select * from t1
union all
select * from t2;

4.0 嵌套查询-子查询

（1）又称为嵌套查询，它是指在一个select语句中包含另一个或多个完整的select语句。子查询的语法规则：
① 子查询需要用圆括号括起来。
② 子查询最多可以嵌套到32层（个别查询可能会不支持32层嵌套）。
③ 执行顺序由内到外，先执行内部的子查询，再执行外部的主查询。

（2）按子查询出现的位置，可分为：
① select子句中：将子查询返回的结果作为主查询的计算字段（标量子查询、列子查询）
② where或having子句中：将子查询返回的结果作为主查询的筛选条件（标量子查询、行子查询、列子查询）
③ from子句中：将子查询返回的结果作为主查询的一张表（标量子查询、行子查询、列子查询、表子查询）

（3）子查询常用运算符

子查询出现在where或having子句中时，可以使用>、>=、<、<=、=、<>/!=等比较运算符或[not ]in、any/some、all、[not ]exists等操作符进行条件筛选

[not]in子查询：查询普通员工的信息
select * from emp
where empid not in (select mgr from emp where mgr is not null);

exists子查询：如果dept表中存在30号部门则查询该部门的员工信息
select *
from emp
where deptno=30 and exists (select deptno from dept where deptno=30);

any/some子查询：查询基本工资高于30号部门任意员工的员工信息（任一）
select *
from emp
where sal>any(select sal from emp where deptno=30) and deptno<>30;

all子查询：查询基本工资高于30号部门所有员工的员工信息（所有）
select *
from emp
where sal>all(select sal from emp where deptno=30);

表子查询：查询各部门工资最高的员工
select emp.*
from emp
join (select deptno,max(sal) as max_sal from emp group by deptno) as t
on emp.deptno=t.deptno
where sal=max_sal;

4.1 查询优化

1、如果子查询的查询量和主查询的数据量是一样的，则能用join的时候尽量不用子查询，但如果子查询客户减少查询量，可以选择子查询
2、子查询虽然很灵活，但是执行效率并不高。执行子查询时，可以使用连接查询（join）代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。当where子句中需要使用聚合函数作为筛选条件时，只能使用子查询。

5、字符长度截取

5.1 从左/右开始截取字符串

left（str, length）： left（被截取字段，截取长度） ，从右取则将left改为righ

5.2 字符串截取

substring（str, pos, length） ： substring（被截取字段，从第几位开始截取，截取长度）
1、从字符串的第 5 个字符位置开始取，直到结束
select substring(‘apple.com’, 5);
2、从字符串的第 5 个字符位置开始取，取4个字符
select substring(‘apple.com’, 5, 4);
3、 从字符串的第 5 个字符位置（倒数）开始取，直到结束
select substring(‘apple.com’, -5);
4、 从字符串的第 5 个字符位置（倒数）开始取，取 4 个字符
select substring(‘apple.com’, -5, 4);

5.3 按关键字截取字符串

substring_index（str,delim,count）： substring_index（被截取字段，关键字，关键字出现的次数）
1、 截取第二个 ‘.’ 之前的所有字符
select substring_index(‘www.apple.com’, ‘.’, 2);
2、 截取第二个 ‘.’ （倒数）之后的所有字符
select substring_index(‘www.apple.com’, ‘.’, -2);

6、通用函数

1、CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
2、FORMAT(X, D)- 格式化数字X到D有效数字。
3、CURRDATE(), CURRTIME()- 返回当前日期或时间。
4、NOW（） – 将当前日期和时间作为一个值返回。
5、MONTH（），DAY（），YEAR（），WEEK（），WEEKDAY（） – 从日期值中提取给定数据。
6、HOUR（），MINUTE（），SECOND（） – 从时间值中提取给定数据。
7、DATEDIFF（A，B） – 确定两个日期之间的差异，通常用于计算年龄
8、SUBTIMES（A，B） – 确定两次之间的差异。
9、FROMDAYS（INT） – 将整数天数转换为日期值