SQL语句大全

SQL语句大全:MySQL

0. 前言

数据库语言有四大类：DDL，DML，DQL，DCL；
本文是为了自己去理解记忆常用SQL！

对于sql语句：SQL语言有命令动词、子句、运算符和统计函数构成。这些元素结合起来组成语句，用来对数据库进行各种操作，包括创建、更新、查询及一些其他功能。

数据库我们常用的是CRUD四类；今天换一个方式全盘记忆四大类语言

1. DCL ： 数据 （control）控制语言

首先我们需要控制用户 和 用户权限

1.1 操作用户

-- 添加用户
create user '用户名'@'主机名' identified by '密码'
-- 例：
create user 'chendi'@'localhost_3306' identified by '20010418'

-- 删除用户
drop user '用户名'@'主机名'

1.2 操作权限

-- 授权
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'
-- 例：
grant all on *.* to 'chendi'@'%' -- 赋予 chendi用户 所有主机 在 所有库的所有表 所有权限
-- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'
-- 例：
revoke all on db1.1 from 'chendi'@'localhost_3306' -- 删除 chendi 对 db1的1表的所有权限

总结：现有用户再控制权限
用户：create 、 drop
权限：grant 、 revoke

2. DDL ： 数据（definition）定义语言

• 首先我们需要有数据库，那么操作库的语言有

2.1 操作库

--创建库
create database db1;
-- 注意：如果库已经存在系统会给出错误信息！考虑下列语句
create database if not exists db1; -- 判断库是否存在
--创建库带上字符集和校对信息
create database if not exists db1
default character set utf8 
default collate utf8_general_ci;--注意排序规则是否存在

--查看库
-- 查看所有库
show databases; -- 注意复数
-- 查看某个数据库的定义信息
show create database db1;
-- 修改数据库字符信息
alter database db1 character set utf8;
-- 删除数据库
drop database db1;

总结：对于库的操作，命令动词：create、show、alter、drop ；分别为增删改查
名词：database、character、collate

2.2 操作表

• 以上是关于库的操作，有了库之后我们需要有对表的操作

创建表

-- 创建表格式
CREATE TABLE [IF NOT EXISTS] `表名`（
	`字段名` 列类型[属性][索引][注释],
	`字段名` 列类型[属性][索引][注释],
	`字段名` 列类型[属性][索引][注释] (最后一句可以不要逗号)

	在最后设置主键	
）[表类型][表的字符集设置][注释]``
-- 不为空：not null
-- 自增：auto_increment
-- 默认值：DEFAULT ‘xxx’：默认值xxx
-- 注释：COMMENT 'xx',
-- 主键：primary key('字段名')

-- 创建表 例子：
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT'密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT'性别',
    `birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
    `address` VARBINARY(100) DEFAULT NULL COMMENT'家庭住址',
    `email` VARBINARY(50) DEFAULT NULL COMMENT'邮箱',
     PRIMARY KEY(`id`) --设置主键
)ENGINE=INNODB DEFAULT CHARSET=utf8  --设置编码

其他对表操作

-- 查看表结构
desc student;
-- 查看创建表的SQL语句
show create table student;
-- 修改表名
alter table student rename to student1
-- 添加列
alter table student add age int
-- 删除列
alter table student drop age
-- 删除 所有行
truncate table 表名
-- 删除表
drop table student
drop table if exists student

总结：对于表的操作，创建表时属性需要根据实际需求考虑；除了 not null 与 null、auto_increment；还有 无符号：unsigned ；
增删改查：create、drop | truncate、alter、show | desc
在 alter 中可以 对表进行操作：表名、列……

3. DML：数据（Manipulation）操作语言

主要是对数据库表数据的增删改；查的话查看DQL；
DML应该是开发过程中最常用的；此处做简单介绍

3.1 增加：insert into

insert into 表名 (列名1,列名2,列名3) values (值1,值2,值3) 

3.2 删除： delete 、truncate

delete from 表名where 条件 -- 删除符合条件数据；不带条件全部删除
truncate table 表名; -- 等同于 delete 不带条件;是DDL语句

删除：truncate与delete 的区别：SQL 中delete与truncate的区别

3.2 修改

update 表名 set 列名 = 值 where 条件;

总结： 这一块还是很常用的，主要注意删除中的 truncate 与 delete 区别

4. DQL：数据（Query）查询语言

4.1 基础语法

select 列1,avg(列2),count(列3),……
from 表 
where 条件 
order by 排序列 desc|asc
group by 分组列 having 条件
limit 起始页,页面大小
……

查询语句太多了：聚合函数、排序查询、模糊查询、分组查询、分页查询、内外连接、子查询

4.3 基础关键字

BETWEEN…AND （在什么之间）、 IN（集合）、 is null（不为null值） 、 like（模糊查询）、distinct（去除重复值）

4.2 聚合函数

作用：进行列计算，一列数据为一个整体

count、max、min、avg、sum
特殊：
count_big：返回值 bigint型、还有方差等函数
可见 参考链接 6

• 注意：除了count，其他的忽略null；用IsNull(列，0) 将null 作为0 来算；例如 avg 时！！！

4.3 排序、分页、分组

排序：select * from table order by 列 asc|desc 短升长降
分页：select * from table limit 3,4 第三页，每页4行
分组： 如下面的例子

--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
-- 理解：where 是 分组的前提 having 是 分组后 的条件
-- 即：分组的前后条件！！！

4.4 内外连接与子查询

1. 内连接

• 隐式内连接

select * from a,b where a.id = b.id
-- 会进行内连接：使用where条件消除无用数据

• 显示内连接

select * from a [inner] join b where a.id = b.id
-- inner 可以不写  

2. 外连接

• 左外连接

-- 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
-- 例子：
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT  t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

-- 分析：员工表数据全要 作为表1 ，部门表 部分不显示；
-- 即：获取员工与部门交集+员工

• 右外连接

-- 和左外一样，表换个位置就可以了
-- 语法：
select 字段列表 from 表1 right [outer] join 表2 on 条件；
-- 例子：
SELECT  * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

3. 子查询

此处略，一个查询作为另一个查询的条件

参考链接：

1. MySQL创建数据库（CREATE DATABASE语句）
2. mysql用户权限
3. SQL语言的组成
4. mysql sql常用语句大全
5. SQL 中delete与truncate的区别
6. SQL中的聚合函数介绍