mysql

sql分类

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

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

DDL 语句

DDL 是数据定义语言的缩写,简单来说,就是对数据库内部的对象进行创建、删除、修改的
操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作,而不涉及到表的定
义、结构的修改,更不会涉及到其他对象。 DDL 语句更多的被数据库管理员(DBA)所使用,
一般的开发人员很少使用。

创建数据库

首先创建一个数据库：

create database databasename default character set utf8 collate utf8_general_ci;

删除数据库

drop database dbname;

创建表

在数据库中创建一张表的基本语法如下:

CREATE TABLE tablename (column_name_1 column_type_1 constraints,
column_name_2 column_type_2 constraints , ......column_name_n column_type_n
constraints)

例如,创建一个名称为 emp 的表。表中包括 3 个字段, ename (姓名)
, hiredate (雇用日期)、
sal(薪水),字段类型分别为 varchar(10)、date、int(2)(关于字段类型将会在下一章中
介绍):

mysql> create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
Query OK, 0 rows affected (0.02 sec)

查看 emp 表:

mysql> desc emp;

虽然 desc 命令可以查看表定义,但是其输出的信息还是不够全面,为了查看更全面的表定
义信息,有时就需要通过查看创建表的 SQL 语句来得到,可以使用如下命令实现:

mysql> show create table emp \G;
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(20) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL,
KEY `idx_emp_ename` (`ename`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.02 sec)
ERROR:
No query specified
mysql>

从上面表的创建 SQL 语句中,除了可以看到表定义以外,还可以看到表的 engine (存储引擎)
和 charset(字符集)等信息。
“\G”选项的含义是使得记录能够按照字段竖着排列,对于内
容比较长的记录更易于显示。

删除表

表的删除命令如下:

DROP TABLE tablename

例如,要删除数据库 emp 可以使用以下命令:

mysql> drop table emp;
Query OK, 0 rows affected (0.00 sec)

修改表

(1) 修改表类型,语法如下:

ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

例如,修改表 emp 的 ename 字段定义,将 varchar(10)改为 varchar(20):

mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.03 sec)

(2) 增加表字段,语法如下:

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]

例如,表 emp 上新增加字段 age,类型为 int(3):

mysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

(3)删除表字段,语法如下:

ALTER TABLE tablename DROP [COLUMN] col_name

例如,将字段 age 删除掉:

mysql> alter table emp drop column age;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

(4)字段改名,语法如下:

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]

例如,将 age 改名为 age1,同时修改字段类型为 int(4):

mysql> alter table emp change age age1 int(4) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

(5)修改字段排列顺序。
前面介绍的的字段增加和修改语法(ADD/CNAHGE/MODIFY)中,都有一个可选项 first|after
column_name,这个选项可以用来修改字段在表中的位置,默认 ADD 增加的新字段是加在
表的最后位置,而 CHANGE/MODIFY 默认都不会改变字段的位置。
例如,将新增的字段 birth date 加在 ename 之后:

mysql> alter table emp add birth date after ename;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

修改字段 age,将它放在最前面:

mysql> alter table emp modify age int(3) first;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

(6)表改名,语法如下:

ALTER TABLE tablename RENAME [TO] new_tablename

例如,将表 emp 改名为 emp1,命令如下:

mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.00 sec)

DML 语句

DML 操作是指对数据库中表记录的操作,主要包括表记录的插入(insert)、更新(update)、删除(delete)和查询(select),是开发人员日常使用最频繁的操作。下面将依次对它们进
行介绍。

插入记录

表创建好后,就可以往里插入记录了,插入记录的基本语法如下:

INSERT INTO tablename (field1,field2,......fieldn) VALUES(value1,value2,......valuesn);

例如,向表 emp 中插入以下记录: ename 为 zzx1, hiredate 为 2000-01-01, sal 为 2000, deptno
为 1,命令执行如下:

mysql> insert into emp (ename,hiredate,sal,deptno) values('zzx1','2000-01-01','2000',1);
Query OK, 1 row affected (0.00 sec)

也可以不用指定字段名称,但是 values 后面的顺序应该和字段的排列顺序一致:

mysql> insert into emp values('lisa','2003-02-01','3000',2);
Query OK, 1 row affected (0.00 sec)

在 MySQL 中,insert 语句还有一个很好的特性,可以一次性插入多条记录,语法如下:

INSERT INTO tablename (field1, field2,......fieldn)
VALUES
(record1_value1, record1_value2,......record1_valuesn),
(record2_value1, record2_value2,......record2_valuesn),
......
(recordn_value1, recordn_value2,......recordn_valuesn)
;

更新记录

对于表里的记录值,可以通过 update 命令进行更改,语法如下:

UPDATE tablename SET field1=value1,field2.=value2,......fieldn=valuen [WHERE CONDITION]

例如,将表 emp 中 ename 为“lisa”的薪水(sal)从 3000 更改为 4000:

mysql> update emp set sal=4000 where ename='lisa';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

在 MySQL 中,update 命令可以同时更新多个表中数据,语法如下:

UPDATE t1,t2...tn set t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]

在下例中,同时更新表 emp 中的字段 sal 和表 dept 中的字段 deptname:

mysql>update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;
Query OK, 3 rows affected (0.04 sec)
Rows matched: 5 Changed: 3 Warnings: 0

删除记录

如果记录不再需要,可以用 delete 命令进行删除,语法如下:

DELETE FROM tablename [WHERE CONDITION]

例如,在 emp 中将 ename 为‘dony’的记录全部删除,命令如下:

mysql> delete from emp where ename='dony';
Query OK, 1 row affected (0.00 sec)

在 MySQL 中可以一次删除多个表的数据,语法如下:

DELETE t1,t2...tn FROM t1,t2...tn [WHERE CONDITION]

在下例中,将表 emp 和 dept 中 deptno 为 3 的记录同时都删除:

mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;
Query OK, 2 rows affected (0.04 sec)

查询记录

数据插入到数据库中后,就可以用 SELECT 命令进行各种各样的查询,使得输出的结果符合
我们的要求。由于 SELECT 的语法很复杂,所有这里只介绍最基本的语法:

SELECT * FROM tablename [WHERE CONDITION]

其中“*”表示要将所有的记录都选出来,也可以用逗号分割的所有字段来代替,例如,以
下两个查询是等价的:

mysql> select ename,hiredate,sal,deptno from emp;

(1)查询不重复的记录。
有时需要将表中的记录去掉重复后显示出来,可以用 distinct 关键字来实现:

mysql> select distinct deptno from emp;

(2)条件查询。
在很多情况下,用户并不需要查询所有的记录,而只是需要根据限定条件来查询一部分数据,
用 where 关键字可以来实现这样的操作。
例如,需要查询所有 deptno 为 1 的记录:

mysql> select * from emp where deptno=1;

(3)排序和限制。
我们经常会有这样的需求,取出按照某个字段进行排序后的记录结果集,这就用到了数据库
的排序操作,用关键字 ORDER BY 来实现,语法如下:

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] , field2
[DESC|ASC],......fieldn [DESC|ASC]]

其中,DESC 和 ASC 是排序顺序关键字,DESC 表示按照字段进行降序排列,ASC 则表示升序
排列,如果不写此关键字默认是升序排列。ORDER BY 后面可以跟多个不同的排序字段,并
且每个排序字段可以有不同的排序顺序。
例如,把 emp 表中的记录按照工资高低进行显示:

mysql> select * from emp order by sal;

例如,把 emp 表中的记录按照部门编号 deptno 字段排序:

mysql> select * from emp order by deptno;

对于 deptno 相同的前两条记录,如果要按照工资由高到低排序,可以使用以下命令:

mysql> select * from emp order by deptno,sal desc;

对于排序后的记录,如果希望只显示一部分,而不是全部,这时,就可以使用 LIMIT 关键字
来实现,LIMIT 的语法如下:

SELECT ......[LIMIT offset_start,row_count]

例如,显示 emp 表中按照 sal 排序后的前 3 条记录:

mysql> select * from emp order by sal limit 3;

如果要显示 emp 表中按照 sal 排序后从第二条记录开始,显示 3 条记录:

mysql> select * from emp order by sal limit 1,3;

(4)聚合。
很多情况下,我们需要进行一些汇总操作,比如统计整个公司的人数或者统计每个部门的人数,这个时就要用到 SQL 的聚合操作。
聚合操作的语法如下:

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

对其参数进行以下说明。

• fun_name 表示要做的聚合操作,也就是聚合函数,常用的有 sum(求和)、count(*)(记
  录数)、max(最大值)、min(最小值)。
• GROUP BY 关键字表示要进行分类聚合的字段,比如要按照部门分类统计员工数量,部门
  就应该写在 group by 后面。
• WITH ROLLUP 是可选语法,表明是否对分类聚合后的结果进行再汇总。
• HAVING 关键字表示对分类后的结果再进行条件的过滤。

having 和 where 的区别在于 having 是对聚合后的结果进行条件的过滤,而 where 是在聚
合前就对记录进行过滤,如果逻辑允许,我们尽可能用 where 先过滤记录,这样因为结果
集减小,将对聚合的效率大大提高,最后再根据逻辑看是否用 having 进行再过滤。
例如,要 emp 表中统计公司的总人数:

mysql> select count(1) from emp;

在此基础上,要统计各个部门的人数:

mysql> select deptno,count(1) from emp group by deptno;

更细一些,既要统计各部门人数,又要统计总人数:

mysql> select deptno,count(1) from emp group by deptno with rollup;

统计人数大于 1 人的部门:

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

最后统计公司所有员工的薪水总额、最高和最低薪水:

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

(5)表连接。
当需要同时显示多个表中的字段时,就可以用表连接来实现这样的功能。
从大类上分,表连接分为内连接和外连接,它们之间的最主要区别是內连接仅选出两张表中
互相匹配的记录,而外连接会选出其他不匹配的记录。我们最常用的是内连接。
例如,查询出所有雇员的名字和所在部门名称,因为雇员名称和部门分别存放在表 emp 和
dept 中,因此,需要使用表连接来进行查询:

mysql> select ename,deptname from emp,dept where emp.deptno=dept.deptno;

外连接有分为左连接和右连接,具体定义如下。

• 左连接:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
• 右连接:包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录

例如,查询 emp 中所有用户名和所在部门名称:

mysql> select ename,deptname from emp left join dept on emp.deptno=dept.deptno;

(6)子查询。
某些情况下,当我们查询的时候,需要的条件是另外一个 select 语句的结果,这个时候,就
要用到子查询。用于子查询的关键字主要包括 in、not in、=、!=、exists、not exists 等。
例如,从 emp 表中查询出所有部门在 dept 表中的所有记录:

mysql> select * from emp where deptno in(select deptno from dept);

(7)记录联合。
我们经常会碰到这样的应用,将两个表的数据按照一定的查询条件查询出来后,将结果合并
到一起显示出来,这个时候,就需要用 union 和 union all 关键字来实现这样的功能,具体语
法如下:

SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
......
UNION|UNION ALL
SELECT * FROM tn;

UNION 和 UNION ALL 的主要区别是 UNION ALL 是把结果集直接合并在一起,而 UNION 是将
UNION ALL 后的结果进行一次 DISTINCT,去除重复记录后的结果。
来看下面例子,将 emp 和 dept 表中的部门编号的集合显示出来:

mysql> select deptno from emp
	-> union all
	-> select deptno from dept;

如果希望将结果去掉重复记录后显示:

mysql> select deptno from emp
	-> union
	-> select deptno from dept;