SQL 分类

SQL 语句主要可以划分为以下 3 个类别:

1. DDL(Data Definition Languages)语句:数据定义语言，这些语句定义了不同的数据段、 数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter 等。
2. DML(Data Manipulation Language)语句:数据操纵语句，用于添加、删除、更新和查 询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和 select 等。
3. DCL(Data Control Language)语句:数据控制语句，用于控制不同数据段直接的许可和 访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的 语句关键字包括 grant、revoke 等。

1.DDL 语句

1.1数据库操作

1. 创建数据库

create dababase dbname;

2. 使用数据库

use dbname;

3. 显示所有表

show tables;

4. 删除数据库

drop database dbname;

1.2 表的基本操作

1. 创建表

create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));

2. 查看表

desc tablename

/** 或者通过查看创建表的 SQL 语句查看表
        “\G”选项的含义是使得记录能够按照字段竖着排列，
        对于内 容比较长的记录更易于显示。
 */
show create table emp \G;

3. 删除表

drop table tablename;

4. 修改表
   1. 修改表类型

   alter table emp modify ename varchar(20);
   

   2. 增加表字段

   alter table emp add column age int(3);
   

   3. 删除表字段

   alter table emp drop column age;
   

   4. 修改字段名（包括其类型）

   alter table emp change deptno deptno2 int(2);
   

   5. 修改表字段排序

   alter table emp add birth date after ename;
   
   alter table emp modify deptno2 int(4) first;
   

   注意:CHANGE/FIRST|AFTER COLUMN 这些关键字都属于 MySQL 在标准 SQL 上的扩展，在 其他数据库上不一定适用。
   6. 表改名

   alter table emp rename emp2;
   

2. DML 语句

2.1 插入记录

insert into emp(deptno,ename,hiredate,sal) values(1,'Mary','2017-05-04',9000);

// 也可以不用指定字段名称，但是 values 后面的顺序应该和字段的排列顺序一致:
insert into emp values(2,'Kangkang','2017-06-21',7200);

// 对于含可空字段、非空但是含有默认值的字段、自增字段，可以不用在 insert 后的字段列表 里面出现
insert into emp(ename,sal) values('Tom', 5000);

// 在 MySQL 中，insert 语句还有一个很好的特性，可以一次性插入多条记录，语法如下:
insert into emp(ename,sal) values('Delphi', 5400),('Jone', 10000);

2.2 更新记录

update emp set sal = 2500 where ename = 'Jone';

// 多个表同时操作
update emp a, dept b set a.sal = a.sal * b.deptno, b.teachername = a.ename where a.deptno = b.deptno;

注意:多表更新的语法更多地用在了根据一个表的字段，来动态的更新另外一个表的字段

2.3 删除记录

delete from dept where teachername = 'Miss Zhang';

// 将表 emp 和 dept 中 deptno 为 3 的记录同时都删除:
delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

2.4 查询记录

1. 查询不重复的记录

   select distinct deptno from emp;
   

2. 条件查询

   select * from emp where deptno=1 and sal<10000;
   

3. 排序和限制

   // desc 降序，asc 升序（默认）
   select * from emp order by sal asc;
   
   // 显示 emp 表中按照 sal 排序后的前 3 条记录
   select * from emp order by sal limit 3;
   
   // 显示 emp 表中按照 sal 排序后从第二条记录开始，显示 3 条记录:
   select * from emp order by sal limit 1,3;
   

   注意:limit 属于 MySQL 扩展 SQL92 后的语法，在其他数据库上并不能通用。

4. 聚合

   注意:having 和 where 的区别在于 having 是对聚合后的结果进行条件的过滤，而 where 是在聚 合前就对记录进行过滤，如果逻辑允许，我们尽可能用 where 先过滤记录，这样因为结果 集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用 having 进行再过滤。

   // 要统计各个部门的人数
   select deptno,count(1) from emp group by deptno;
   
   // 既要统计各部门人数，又要统计总人数:
   select deptno, count(1) from emp group by deptno;
   
   // 统计人数大于 1 人的部门:
   select deptno, count(1) from emp group by deptno having count(1) > 1;
   
   // 统计公司所有员工的薪水总额、最高和最低薪水:
   select sum(sal), max(sal), min(sal) from emp;
   

5. 表连接
   从大类上分，表连接分为内连接和外连接，它们之间的最主要区别是內连接仅选出两张表中 互相匹配的记录，而外连接会选出其他不匹配的记录。我们最常用的是内连接。

   // 查询出所有雇员的名字和所在部门名称
   select ename, teachername from emp, dept where emp.deptno = dept.deptno;
   

   外连接有分为左连接和右连接，具体定义如下。
    左连接:包含所有的左边表中的记录甚至是右边 表中没有和它匹配的记录
    右连接:包含所有的右边表中的记录甚至是左边 表中没有和它匹配的记录

   // 查询 emp 中所有用户名和所在部门名称:
   select ename, teachername from emp left join dept on emp.deptno = dept.deptno;
   

   比较这个查询和上例中的查询，都是查询用户名和部门名，两者的区别在于本例中列出了所 有的用户名，即使有的用户名(dony)并不存在合法的部门名称(部门号为 4，在 dept 中 没有这个部门);而上例中仅仅列出了存在合法部门的用户名和部门名称。

6. 子查询

   // 从 emp 表中查询出所有部门在 dept 表中的所有记录:
   select * from emp where deptno in(select deptno from dept);
   
   // 某些情况下，子查询可以转化为表连接，例如:
   select emp.* from emp, dept where emp.deptno = dept.deptno;
   

   注意:子查询和表连接之间的转换主要应用在两个方面:

   1. MySQL4.1以前的版本不支持子查询，需要用表连接来实现子查询的功能 
   2. 表连接在很多情况下用于优化子查询

7. 记录联合

   // 将 emp 和 dept 表中的部门编号的集合显示出来:
   select deptno from emp union all select deptno from dept;
   
   // 如果希望将结果去掉重复记录后显示:
   select deptno from emp union select deptno from dept;
   

3. DCL 语句

DCL 语句主要是 DBA 用来管理系统中的对象权限时所使用，一般的开发人员很少使用。

// 创建一个数据库用户 panzhangbao，具有对 dbtest 数据库中所有表的 SELECT/INSERT 权限:
grant select, insert on dbtest.* to 'panzhangbao'@'localhost' identified by '123456';
exit
mysql -upanzhangbao -p123456

// 取消插入数据库权限
revoke insert on dbtest.* from 'panzhangbao'@'localhost';