SQL基础

DDL 语句

DDL - 数据库定义语言。这些语句对数据库内部对象进行创建、删除、修改等操作。

1. 查看建表sql

show create table user \G;

\G:使得记录能够按照字段竖向排列。

1. 修改字段类型
   emp 表的名字

alter table emp modify [column] ename varchar(20) [first/after col_name];

2. 修改字段名称

alter table emp change [column] name uname char(10) [first/after col_name];

3. 增加字段

alter table emp add [column] age int(3) [first/after col_name];

4. 删除字段

alter table emp drop [column] age [first/after col_name];

5. 更改表名

alter table `emp` rename [to] `new_tablename`;

总结：modify, change, add, drop 对应改字段类型，改字段名称，新增，删除。[]表示可选项。[first/after col_name]指定字段排序。
如修改age字段把它放在最前面

alter table emp add column age int(3) first;

6. 添加唯一索引

alter table user add unique index u_name(uname);

u_name -- 索引名称
uname -- 添加索引的字段

第二种方式：

create index idx_email on customer(email);

idx_email -- 索引名称
customer -- 表名
email -- 创建索引的字段名

DML 语句

DML - 数据库操作。简单说就是我们熟知的增删改查。

1. 插入记录

insert into `tablename` (field1, field2, field3...) values (val1, val2,val3...);

2. 更新语句

update tablename set filed1 = val1, field2 = val2, field3 = val3 ... [where condition];

3. 删除语句

delete from tablename [where condition];

mysql可以一次删除多个表数据

delete t1, t2, t3... from t1, t2, t3... [where condition];

select 语句

聚合

很多情况下，用户需要进行一些汇总操作，这时会用到sql的聚合操作。
聚合语句语法如下：

SELECT [field1, field2,...,fieldn] fun_name
FROM tablename
[WHERE where_condition]
[GROUP BY field1, field2, ... , fieldn]
[WITH ROLLUP]
[HAVING where_condition]

参数说明：

1. fun_name 表示要做的聚合操作，也就是聚合函数，常用的有 sum(求和)，count(*)，max(最大值)、min（最小值）。
2. group by 关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在 group by 后面。
3. with rollup 是可选语法，表示是否对分类聚合后的结果进行再汇总。
4. having 关键字表示对分类后的结果再进行条件的过滤。
   注意：having 和 where的区别在于，having是对聚合后的结果进行条件的过滤，而where是在聚合前对记录进行过滤。如果逻辑允许，我们尽可能先用where来先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用having 进行再过滤。

创建实验表emp,并插入数据:

Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL COMMENT '员工名',
  `depno` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

insert into emp(name, depno) values('zhangsan', 1),('wuji',2),('jinyong',2),('lisi',1),('meichaofeng',2),('shiqiu',3),('jinmaoshiwang',2),('zhangsanfeng',2),('xiaoxifeidao',3),('rabbitmq',4),('mysql',4),('ini',5);

alter table emp add sal decimal(8,2);
update emp set sal = 10000.0 where id = 1;
update emp set sal = 12000.0 where id = 2;
update emp set sal = 12000.0 where id in (3,4);
update emp set sal = 22000.0 where id in (5,6,12);
update table emp set sal = 18000.00 where id = 8;
update emp set sal = 20000.00 where id in (7,9,10,11);


alter table emp add hiredate date;
alter table emp change name ename varchar(30);
update emp set hiredate = '2005-09-18' where id in (12,11,2);
update emp set hiredate = '2000-01-09' where id in (1,3,4,6,8);
update emp set hiredate = '2008-02-09' where id in (5,7);
update emp set hiredate = '2010-10-09' where id in (9,10);

查询不重复的记录，用 distinct 关键字来实现。

select distinct depno from emp;

例如:

1. 要在emp表中统计公司的总人数：

 select count(1) from emp;

2. 在此基础上统计各个部门的人数

select depno, count(1) from emp group by depno;

3. 更细致一些，即要统计各部门人数，又要统计总人数：

select depno, count(1) from emp 
    group by depno with rollup;

4. 统计人数大于1人的部门

select depno, count(1) from emp group by depno having count(1) > 1;

update emp set hiredate = '2010-10-09' where id in (9,10);

5. 统计所有员工的薪水总额、最高和最低薪水：

select sum(sal), max(sal), min(sal) from emp;

表连接

建表

create table dept (
    deptno tinyint primary key auto_increment,
    depname varchar(20)) engine= innodb default charset=utf8;

insert into dept values(1, 'tech'), (2, 'sale'), (3, 'hr'), (4, 'tel'), (5, 'manager');

alter table emp depno deptno tinyint;

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

1. 查询出索引雇员的名字和所在的部门名称，因为官员名称和部门分别存放在表 emp 和 dept 中。
   因此使用表连接查询：

select ename, depname from emp, dept where emp.deptno = dept.deptno order by depname;

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

准备:

delete from dept where deptno in (4,5);

2. 查询emp表中所有雇员名及所在部门名称：

 select ename , depname from emp left join dept on emp.deptno = dept.deptno;

比较这个查询和上例中国年的查询，都是查询雇员名和部门名，两者的区别在于本例中列出了所有的用户名，即使有的用户名并不存在合法的部门名称；而上例是列出了存在合法部门的雇员名和部门名称。

select ename , depname from emp left join dept on dept.deptno = emp.deptno;

3. 右连接和左连接类似，两者之间可以互相转化，例如上面的例子可以改写成如下的右连接：

select ename, depname from dept right join emp on dept.deptno = emp.deptno; 

子查询

某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候，就要用到子查询。用于子查询的关键字包括 in, not in, =, !=, exists, not exists 等。某些情况下，子查询可以转化为表连接。

记录联合

我们经常碰到这样的应用，将两个表的数据按照一定的条件查询出来后，将结果合并懂啊一起现实出来，这是好，就需要用 union 和 union all 关键字来实现这样的功能。

union 和 union all 的主要区别是 union all 是把结果集直接合并在一起，而 union 是 将 union all 后的结果进行一次distinct，去除重复记录后的结果。

select deptno from emp union  select deptno from dept;