SQL学习记录（MySQL指令大全）

SQL

SQL定义： Structured Query Language 结构化查询语言
SQL通用语法

1. SQL可以单行书写可以多行书写，分号结尾
2. 空格 或缩进增加可读性
3. MySQL数据库 SQL语句不区分大小写
4. 单行注释： -- 注释内容 或 # 注释内容；多行注释：/* 注释内容 */

SQL分类

1. DDL（Data Definition Language）数据定义语言：用来定义数据库对象：数据库、表、列表，关键字：creat、drop、alter等
2. DML（Data Manipulation Language）数据操作语言：对数据库表中数据增删改，关键字insert、delete、update等
3. DQL（Data Query Language）数据查询语言：用来查询数据库中表的记录，关键字select、where等
4. DCL（Data Control Language）数据控制语言：用来定义数据库访问权限安全级别，及创建用户，关键字：GRANT、REVOKE等

DDL（Data Definition Language）数据定义语言

操作数据库：CRUD

1. C(CREATE):创建

`create database 数据库名`
 判断是否存在防止报错`create database 数据库名 if not exists 数据库名;`
 创建db检查是否存在，并且设置编码方式 `create database if not exists db character set gbk;`

2. R(Retrieve):查询

查询所有数据库名称：`show databases;`
查询某个数据库的字符集：
查询某个数据库创建语句`show create database 数据库名称;`

3. U(Update):修改

修改编码方式`alter database db character set utf8;`

4. D(Delete):删除

>删除数据库`drop database db;`
>删除前判断是否存在`drop database if exists db`

5. 使用数据库

>查询当前正在使用的数据库 `select database();`
>使用数据库 `use db`

操作表：CRUD

与操作数据库相同CRUD

1. 创建表

creat table 表名(列名1 数据类型1，
				列名2 数据类型2
				...
				列名n 数据类型n);

数据库数据类型
常用类型

整数类型： age int
小数类型： score double(5,2)
日期date： 只包含年月日 date yyyy-MM-dd
日期时间datetime： yyyy-MM-dd HH:mm:ss
时间戳timestamp：和datetime类似，不赋值或者为null默认使用当前系统时间
字符串类型：name varchar(20) 设置姓名最大20个字符

create table student(
	id int,
	name varchar(32),
	age int,
	score double(4,1),
	birthday date,
	insert_time timestamp
	);

复制表

creat table table名 like 被复制table名;

2. 查询

>查询某个数据库的所有表名`show tables;`
>查询表结构`desc 表名`

3. 修改

修改表名 	  alter table table名 rename to 新名;
修改表字符集    alter table table名 set utf8;
添加一列 	  alter table 表名 add 列名 数据类型;
修改列名/类型   alter table 表名 change 列名 新列名 数据类型;
(只修改数据类型)alter table 表名 motify 列名 新数据类型;
删除列         alter table drop 列名;

1. 删除drop table if exists table名
2. 使用

---

DML（Data Manipulation Language）数据操作语言

1. 添加数据

insert into 表名(列名1，...列名n) values(值1...值n)；

2. 删除数据

delete from 表名 where 条件
#没有条件删除所有记录（效率较低）
delete from 表名 

#删除整个表,在创建一个一摸一样的表，效率更高
truncate table 表名

3. 修改数据

#如果不加条件，所有数据都会被更改
update 表名 set 列名=数据 where 条件

DQL（Data Query Language）数据查询语言

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组之后的条件
order by
	排序
limit
	分页	

1. 查询语句

select * from stu;
select distinct address from stu;#去重查询
select name,math,english,math + english form stu; #两个列组合成为一个新列
# 两数 又一个是null结果也为null,通过`IFNULL()`
select name,math,english,math + IFNULL(english,0) from stu;
select name,math,english,math + IFNULL(english,0) as 总分 from stu;#给列名起别名，as也可不写

条件查询

select * from student where age > 10 and age > 0;
#简化
select * from student where age between 20 and 30;
#or 和 in
select * from student where age=10 or age=20;
select * from student where age in (10,20);
#null
select * from studnet where math is null;
select * from studnet where math not null;

#like 模糊查询
# %多个占位符，_一个占位符
select * from student where name like "C%";
select * from student where name like "___";

2. 排序查询
   ASC 升序(默认)
   DESC 降序

order by 排序字段1 排序方式,...
#先数学升序，相同就英语降序
select * from stu order by math,english DESC;

3. 聚合函数

# count：计算个数 NULL 不会计算
select count(name) from stu;
select count(ifnull(name,0)) from stu;
select count(*) from stu;
# max
# min
# sum
# avg

4. 分组查询
   分组之后查询字段：分组字段，或者聚合函数，其他字段没有意义
   where 分组之前进行限定 where后不可加聚合函数
   having 分组之后限定 having后可以加聚合函数判定

select sex,AVG(math), from stu where math > 70 group by sex;
select sex,AVG(math), from stu where math > 70 group by sex having AVG(math) >60;

5. 分页查询
   不同数据库limit分页操作不同

limit 开始的索引，每页查询条数
# 每页显示三条记录
select * from student limit 0,3;

多表查询

现有table：books和city

select * from books,city;
返回两表笛卡尔积

1. 内链接查询

1.隐式内链接 表1，表2
select * from books,city where books.city=city.name;
加表名.属性 便于区分
select books.b_name,city.city_name from city,books where books.city=city.city_name;

2.显式内链接 表1 join 表2
select books.b_name,city.city_name from books join city on books.city=city.city_name;
结果相同

2. 外链接查询

1.左外链接 查询左表所有数据以及其交集部分
select b_name,city_name from books left join city on books.city=city.city_name;

2.右外链接 查询右表所有数据以及其交集部分
select b_name,city_name from books right join city on books.city=city.city_name;

3. 子查询
   查询中嵌套查询，嵌套的是子查询

1. 子查询结果单行单列（可以作为条件）
查询价格最贵的书的名字
select * from books where Price = (select max(Price) from books);
2. 子查询结果多行单列（作为条件使用）
查询国家在中国的所有书的信息
select * from books where city in (select city_name from city where country = 'China');
3. 多行多列（子查询结果为一张虚拟表）加入查询的from位置
select * from books t1,(select city_name from city where country = 'China') t2 where t1.city = t2.city_name;