MySQL深入浅出

MySQL深入浅出

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

DDL（Data Definition Languages）语句：

数据定义语言，这些语句定义了不同的数据段、
数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括create、drop、alter
等。

DML（Data Manipulation Language）语句：

数据操纵语句，用于添加、删除、更新和查
询数据库记录，并检查数据完整性，常用的语句关键字主要包括insert、delete、udpate 和
select 等。

DCL（Data Control Language）语句：

数据控制语句，用于控制不同数据段直接的许可和
访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的
语句关键字包括grant、revoke 等。

修改表

修改表类型，语法如下：

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);

增加表字段，语法如下：

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

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

mysql> alter table emp add column age int(3);

删除表字段，语法如下：

ALTER TABLE tablename DROP [COLUMN] col_name

字段改名，语法如下：

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) ;

表改名，语法如下：

ALTER TABLE tablename RENAME [TO] new_tablename

例如，将表emp 改名为emp1，命令如下：

mysql> alter table emp rename emp1;

DML 语句

插入记录

表创建好后，就可以往里插入记录了，插入记录的基本语法如下：
INSERT INTO tablename (field1,field2,……fieldn) VALUES(value1,value2,……valuesn);

在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)
;

下面的例子中，对表dept 一次插入两条记录：

mysql> insert into dept values(5,'dept5'),(6,'dept6');

更新记录

对于表里的记录值，可以通过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';

删除记录

如果记录不再需要，可以用delete 命令进行删除，语法如下：
DELETE FROM tablename [WHERE CONDITION]

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

mysql> delete from emp where ename='dony';

如果from 后面的表名用别名，则delete 后面的也要用相应的别名，否则会提示语法错误。

mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

查询记录

对于排序后的记录，如果希望只显示一部分，而不是全部，这时，就可以使用LIMIT 关键字
来实现，LIMIT 的语法如下：
SELECT ……[LIMIT offset_start,row_count]
其中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;

聚合

很多情况下，我们需要进行一些汇总操作，比如统计整个公司的人数或者统计每个部门的人
46
数，这个时就要用到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 ename,deptname from emp left join dept on emp.deptno=dept.deptno;

日期时间类型

• 如果要用来表示年月日，通常用DATE 来表示。
• 如果要用来表示年月日时分秒，通常用DATETIME 表示。

• 如果只用来表示时分秒，通常用TIME 来表示。

• YYYY-MM-DD HH:MM:SS 或YY-MM-DD HH:MM:SS 格式的字符串。允许“不严格”
  语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，“98-12-31
  11:30:45”、“98.12.31 11+30+45”、“98/12/31 11*30*45”和“98@12@31 11^30^45”
  是等价的。对于包括日期部分间割符的字符串值，如果日和月的值小于10，不需
  要指定两位数。

字符串类型

CHAR 和VARCHAR 很类似，都用来保存MySQL 中较短的字符串。二者的主要区别在于存储
方式的不同：CHAR 列的长度固定为创建表时声明的长度，长度可以为从0～255 的任何值；
而VARCHAR 列中的值为可变长字符串，长度可以指定为0～255（5.0.3 以前）或者65535（5.0.3
以后）之间的值。在检索的时候，CHAR 列删除了尾部的空格，而VARCHAR 则保留这些空格。