SQL大全_MySQL基础（持续更新中...）

SQL基础_增删改查简单操作

1. 创建数据库

   • 默认字符集
     create database DATABASE_NAME;
create database 数据库名;
   • 设置字符集编码
     create database DATABASE_NAME character set utf8;
create database 数据库名 character set utf8;
2. 查看数据库信息
   show create database DATABASE_NAME;
show create database 数据库名;
3. 删除数据库
   drop database DATABASE_NAME;
drop database 数据库名;
4. 查询所有数据库
   show databases;
5. 使用数据库/进入数据库
   use DATABASE_NAME;
use 数据库名;
6. 查询库中存在什么表
   show tables;

7. 创建新表

   create table TABLE_NAME(
    column1 type1 [not null] [primary key],
    column2 type2 [not null],
    ...
   );

8. 删除表
   drop table TABLE_NAME;
drop table 表名;
9. 查询所有表
   show tables;
10. 修改表名
    alter table 旧表名 rename to 新表名;
    alter table OLD_TABLENAME rename to NEW_TABLENAME;
11. 查询创表语句
    show create table TABLE_NAME;
show create table 表名;
12. 查询表结构
    desc TABLE_NAME;
desc 表名;

13. 增加一个列/增加一个字段

    • 在所有列的最后增加一个字段
      alter table TABLE_NAME add [column] column1 type [not null];
alter table 表名 add [column] 列名 类型 [not null];
    • 在某个列之后增加一个字段
      alter table TABLE_NAME add [column] column1 type1 [not null] after column2;
alter table 表名 add [column] 列名 类型 [not null] after 已存在的列名;
    • 添加一个字段在第一列
      alter table TABLE_NAME add [column] column1 type1 [not null] first;
alter table 表名 add [column] 列名 类型 [not null] first;
14. 删除某个列/字段
    alter table TABLE_NAME drop [column] column1;
    alter table TABLE_NAME drop [column] 字段名;

15. 修改字段

    • 修改字段名
      alter table TABLE_NAME change [column] OLD_COLUMNNAME NEW_COLUMNNAME NEW_TYPE;
alter table 表名 change [column] 旧字段名 新字段名 新数据类型;
    • 修改字段类型
      alter table TABLE_NAME modify [column] COLUMNNAME NEW_TYPE;
alter table 表名 modify [column] 字段名 新数据类型 新类型长度 新默认值 新注释;

16. 简单的增删改查数据的语句

    • 增（insert into）
      insert into 表名(field1,field2,...) values(value1,value2);
    • 删（delete）
      delete from 表名 where 条件；
    • 改（update）
      update 表名 set field1=xxx,field2=xxx,... where 条件；
    • 查（select）
      select 字段名 from 表名 where 条件;

知识点：

1.drop、delete和truncate之间的区别

• drop删除库或者表，数据和结构定义；delete和truncate只是删除表的数据
• delete可以指定where条件，删除满足条件的记录，truncate删除所有记录
• 对于自增字段的表，delete不会将 自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数

---

SQL提升_常用关键字

表 emp

表 dept

1. where条件子句

运算符	含义	示例
=	等于	where id = 5
<> 或 !=	不等于	where id != 5
>	大于	where id > 5
<	小于	where id < 5
>=	大于等于	where id >= 5
<=	小于等于	where id =< 5
and	并且	where job = '销售' and sal = 1250.00
or	或者	where id = 5 or id = 10
between...and	区间范围	where id between 5 and 10
not	非/甚少单独使用，一班与其他关键字连接使用	???
in	当查询某个字段的值为多个值得时候使用	where id in(5,10,15,20)
not in	当查询某个字段的值不为多个值得时候使用	where id not in(5,10,15,20)
is null	为空	where manager is null
is not null	不为空	where manager is not null

2. 模糊查询like

_ ：代表一个未知字符
% ：代表0或多个未知字符

题	答案
以x开头	x%
以x结尾	%x
包含x	%x%
以x开头以y结尾	x%y
第二个字符是x	_x%
第三个字符是x 倒数第二个是y	_x%y

3. order by 排序

语法：order by 列名

1. order by 语句用于根据指定的列对结果集进行排序。
2. order by 语句默认按照asc升序对记录进行排序，如果需要降序排列需要添加desc。
   例：select * from emp order by sal desc;

4. limit

语法：select * from 表名 limit 跳过的条数,每页的条数
跳过的条数= (请求的页数-1)*每页的条数
好处：用户体验,网络传输,查询压力
推导:
第一页 : limit 0,5
第二页 : limit 5,5
第三页 : limit 10,5
......
第N页 : limit (pageNo-1)*pageSzie,pageSzie
pageNo:页码,pageSize:单页面显示条数]
例：
select * from emp limit 0,3;
select * from emp limit 3;

5. 聚合函数

函数名称	描述	例子
count()	满足查询条件的记录总和数	select count(*) from emp
sum()	总和	select sum(sal) from emp
avg()	平局值	select avg(sal) from emp
max()	最大值	select max(sal) from emp
min()	最小值	select min(sal) from emp

注意事项：

1. count(列名/字段)
   统计该字段再表中出现的次数，忽略字段为null的情况，即不统计字段为null的记录
2. count(*) 不建议使用
   会统计所有的列，相当于行数的总和，包含字段为null的记录
3. count(1)
   1表示代码行，在统计结果的时候，包含字段为null的记录

对比：

1. 在表中没有主键时，count(1)比count(*)效率高
2. 有主键时，count(主键)效率最高
3. 若表中只有一个字段，则count(*)效率最高

6. group by 分组

语法：group by 字段/列名
将某个字段相同值的数据划分为一组 进行统计查询的查询方式
例： select job from emp group by job

7. having

语法：having 分组后的条件子句
作用：having 的作用时筛选满足条件的组，即在分组之后过滤数据
重点1：where，having的区别

1. where是在分组（group by）前筛选，having是分组（group by）后的筛选条件，是对已经分组之后的数据组内再筛选
2. where子句中不能使用聚合函数（count，sum，avg，max，min）为条件

重点2：SQL关键字顺序

select 字段名 from 表名 
[left|right|inner join 表名]  --联合查询(外连接|内连接)
[where ...]  --分组前过滤的条件
[group by ...]  --分组
[having ...]  --分组后过滤的条件
[order by ...]  --排序
[limit 跳过的条数,每页的条数];  --分页

8. as

作用：起别名

1. 给数据列名取一个新的别名
2. 给数据库表名取一个新的别名
3. 可给一个经过计算或总结查询出的结果用一个新的别名来替代
   例：
   select name [as] n,manager [as] m,hirthday [as] h from emp;
select * from emp [as] e;
select sum(sal) [as] 总薪资 from emp e;
   注意：as 可省略

9. distinct

作用：去重（去除重复的记录行）
例：select distinct job from emp;

SQL进阶_多表联查

多表查询是指基于两个和两个以上的表的查询。因为在实际应用中，我们不会也没有办法将所有的数据都存储在一张表上，所以查询单张表不能满足我们的需求。
如上述数据库中的emp（员工表）中我们只显示了员工对应的部门编号，而如果需要查询员工信息的同时将部门名称也显示出来，我们就需要用到dept（部门表）。

select * from emp,dept where emp.dept_id = deptId;

1.笛卡尔积

1.1 笛卡尔积简介

笛卡尔积又叫笛卡尔乘积，是一个叫笛卡尔的人提出来的。 简单的说就是两个集合相乘的结果。可以理解为在数学中，有两个集合X和Y的乘积的结果，我们又叫做直积。
假设集合A={a, b}，集合B={1, 2, 3}，则两个集合的笛卡尔积为{(a, 1), (a, 2), (a, 3), (b, 1), (b, 2),(b, 3)}。

1.2 笛卡尔积在SQL中的运用

我们在对数据库表进行操作时，经常会多张表进行关联，然而多表我们很容易出现冗余数据。
比如有两张表：student表，mark表
student表

mark表

查询语句
select class,student.code,name,sub,mark from student,mark;
执行查询语句后会出现下面这种情况，我们称为笛卡尔积查询；表1有4条数据，表2也有4条数据，可以将表1理解为集合X，表而理解为集合Y，那我们查出的结果就为 4*4=16条数据。但是这样会造成冗余数据的产生。那么我们如何避免这种情况的发生呢？

我们可以在where条件子句中设置关联条件，来避免发生这种情况。
语法格式为：

select 列名1,列名2,... from 表名1,表名2 WHERE 表名1.列名1=表名2.列名2

2.多表联查的连接方式

• 内连接
  [inner] join on
  table1 [inner] join table2 on 连接条件
• 外连接
  -左外链接
  left join on
  table1 left join table2 on 连接条件
  -右外链接
  right join on
  table1 right join table2 on 连接条件

例子：
emp员工表，dept部门表

• 内连接

select e.id as 员工id,e.name as 员工名,d.name as 部门名 from emp as e inner join dept as d on e.dept_id = d.id;

• 外连接

  • 左外链接

  select e.id as 员工id,e.name as 员工名,d.name as 部门名 from emp as e left join dept as d on e.dept_id = d.id;
  

  • 右外链接

  select e.id as 员工id,e.name as 员工名,d.name as 部门名 from emp as e right join dept as d on e.dept_id = d.id;
  

仔细思考：
内连接和外连接的区别？
左外连接和右外连接的区别？
内连接 查询出来的为两个表之间的交集数据（匹配数据）
左外连接 显示 join 左边的表的所有数据（不管两个表是否匹配），对于不匹配的部分都用 NULL 表示
右外连接 显示 join 右边的表的所有数据（不管两个表是否匹配），对于不匹配的部分都用 NULL 表示