43.结构化查询语言SQL及MySQL（一）

2025-07-22 10:00 点击:0

简介

SQL是ANSI标准下访问和处理数据库的结构化查询语言。SQL可以在各个RDBMS（关系型数据库管理系统，如MySQL、Microsoft Access）中使用，各个RDBMS也有自己独有的扩展。
RDBMS中，不同数据存储在不同的数据库中。每个数据库通常包含多个表，多个表由表名区分。表由行和列组成，每一行对应一条数据，每一列对应数据的一个属性。

下面通过MySQL介绍SQL语言。首先查看MySQL状态：

mysql> status;--------------/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.7.15, for osx10.11 (x86_64) using  EditLine wrapperConnection id:        1779Current database:    lmzCurrent user:        root@localhostSSL:            Not in useCurrent pager:        lessUsing outfile:        ''Using delimiter:    ;Server version:        5.7.15 MySQL Community Server (GPL)Protocol version:    10Connection:        Localhost via UNIX socketServer characterset:    utf8Db     characterset:    utf8Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:        /tmp/mysql.sockUptime:            4 hours 34 min 45 secThreads: 1  Questions: 161  Slow queries: 0  Opens: 120  Flush tables: 1  Open tables: 101  Queries per second avg: 0.009--------------

如果字符集的配置不是utf8，建议改成utf8：

mysql> set character_set_client=utf8;mysql> set character_set_connection=utf8;mysql> set character_set_results=utf8;mysql> set character_set_server=utf8;

set命令后面的参数可以通过如下命令查询：

mysql> show variables like "character%";+--------------------------+---------------------------------------------------------+| Variable_name            | Value                                                   |+--------------------------+---------------------------------------------------------+| character_set_client     | utf8                                                    || character_set_connection | utf8                                                    || character_set_database   | utf8                                                    || character_set_filesystem | binary                                                  || character_set_results    | utf8                                                    || character_set_server     | utf8                                                  || character_set_system     | utf8                                                    || character_sets_dir       | /usr/local/mysql-5.7.15-osx10.11-x86_64/share/charsets/ |+--------------------------+---------------------------------------------------------+8 rows in set (0.11 sec)

把除了character_set_filesystem和character_sets_dir之外的变量都设置成utf8。

SQL语言

SQL语言分为两部分：数据定义语言（DDL）和数据操作语言（DML）。

DDL

包括创建/删除/修改数据库/数据表的方法、定义索引、添加表约束等：

show databases

列出全部数据库。

create database se_name>se_name>

se_name>

创建名为se_name>的数据库，比如create database lmz;。se_name>

se_name>

drop database se_name>se_name>

se_name>

删除名为se_name>的数据库。se_name>

se_name>

use se_name>se_name>

se_name>

使用某个数据库（然后才能操作其中的表），比如use lmz;。

show tables

查看数据库中所有数据表。

create table ( , , ..)

创建数据表，其中表示表名，表示列名，表示对应列的数据类型。

添加列的约束条件

not null

not null表示该列不接受空，比如：

NULL表示没有值，和取值为0不同。

unique

unique限制某一列或者多个列元素的唯一性（如果该列允许为NULL的话，则唯一性无法限制存在多个NULL的情况，所以如果限制为unique，最好同时限制not null），比如：

也可以为unique的规则命名，比如：

使用规则的名字可以删除这条规则，比如：

如果unique规则在创建时没有命名，删除方法如下：

mysql> create table a (id int(5), op varchar(15), unique (id));Query OK, 0 rows affected (0.03 sec)mysql> show create table a;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                    |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+| a     | CREATE TABLE `a` (  `id` int(5) DEFAULT NULL,  `op` varchar(15) DEFAULT NULL,  UNIQUE KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> alter table a drop index `id`;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table a;+-------+-----------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                          |+-------+-----------------------------------------------------------------------------------------------------------------------+| a     | CREATE TABLE `a` (  `id` int(5) DEFAULT NULL,  `op` varchar(15) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

对于已经创建的表，可以使用alter table语句增加unique限制，比如给上面的数据表a恢复之前的unique限制：

另外，限制为unique的列会自动添加唯一性索引。

primary key

primary key用来创建主键，主键用来标识数据表中数据的唯一性，每个数据表只能有一个主键，但是可以有多条unique规则，主键列不能包含null值，主键自动带有unique规则。

比如：

主键同样可以包含多个列，也可以对主键规则命名（但是对主键命名的意义不大，因为表只有一个主键，删除primary key时也不提供主键名这个参数）、删除主键规则等等：

mysql> create table a (id int(5), op varchar(15), constraint record_id primary key(id));Query OK, 0 rows affected (0.03 sec)mysql> show create table a;+-------+-----------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                            |+-------+-----------------------------------------------------------------------------------------------------------------------------------------+| a     | CREATE TABLE `a` (  `id` int(5) NOT NULL,  `op` varchar(15) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+-----------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> alter table a drop primary key;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table a;+-------+-------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                      |+-------+-------------------------------------------------------------------------------------------------------------------+| a     | CREATE TABLE `a` (  `id` int(5) NOT NULL,  `op` varchar(15) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+-------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> alter table a add primary key (id, op);Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table a;+-------+------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                             |+-------+------------------------------------------------------------------------------------------------------------------------------------------+| a     | CREATE TABLE `a` (  `id` int(5) NOT NULL,  `op` varchar(15) NOT NULL,  PRIMARY KEY (`id`,`op`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

foreign key

foreign key用来在两个表之间建立关联，foreign key必须指向另一个表的具有唯一性约束的列（一个或多个），比如：

mysql> select * from a;+-----+-----+---------------+| id1 | id2 | op            |+-----+-----+---------------+|   1 |   1 | coordinate 1,1 |+-----+-----+---------------+1 row in set (0.00 sec)mysql> show create table a;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                             |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| a     | CREATE TABLE `a` (  `id1` int(5) NOT NULL,  `id2` int(5) NOT NULL,  `op` varchar(15) DEFAULT NULL,  PRIMARY KEY (`id1`,`id2`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> create table b (ida1 int(5), ida2 int(5), record varchar(20), constraint fb foreign key (ida1, ida2) references a(id1, id2));Query OK, 0 rows affected (0.04 sec)mysql> show create table b;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                                        |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| b     | CREATE TABLE `b` (  `ida1` int(5) DEFAULT NULL,  `ida2` int(5) DEFAULT NULL,  `record` varchar(20) DEFAULT NULL,  KEY `fb` (`ida1`,`ida2`),  ConSTRAINT `fb` FOREIGN KEY (`ida1`, `ida2`) REFERENCES `a` (`id1`, `id2`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> insert into b values (1, 2, "insert 1,2");ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`lmz2`.`b`, ConSTRAINT `fb` FOREIGN KEY (`ida1`, `ida2`) REFERENCES `a` (`id1`, `id2`))mysql> insert into b values (1, 1, "insert 1,1");Query OK, 1 row affected (0.01 sec)

一个数据表中，可以含有多个外键，所以删除外键时需要提供外键规则的名字：

也可以使用alter table语句为已经创建的数据表添加外键，比如针对上面的数据表b：

default

default用来给数据表的某一列添加默认值，比如：

删除某一列的default规则：

为已经存在的表的某一列设置default规则：

auto_increment

auto_increment用来限制主键列的数据是自动增加的，默认从1开始增加，MySQL中auto_increment必须用在主键中，比如：

mysql> create table g (id int(5) not null auto_increment, op varchar(10), primary key (id));Query OK, 0 rows affected (0.04 sec)mysql> insert into g (op) values ("insert");Query OK, 1 row affected (0.00 sec)mysql> select * from g;+----+--------+| id | op     |+----+--------+|  1 | insert |+----+--------+1 row in set (0.00 sec)

数据类型

整型

integer(size)/int(size)、tinyint(size)、smallint(size)、mediumint(size)、bigint(size)，其中size表示显示宽度，显示时默认右对齐。
如果设置了zerofill，右对齐时不足的位数用零填充，超过的位数正常显示。比如：create table n (v tinyint(5) zerofill unsigned);只允许插入无符号整数(0~255)，且显示时右对齐、零填充：

常用整型数据类型如下：

类型	存储空间	最小值	最大值
	(字节)	(Signed/Unsigned)	(Signed/Unsigned)
tinyint	1	-128	127
		0	255
smallint	2	-32768	32767
		0	65535
mediumint	3	-8388608	8388607
		0	16777215
int	4	-2147483648	2147483647
		0	4294967295
bigint	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

浮点型

decimal(precision, scale)/numeric(precision, scale)，其中precision规定有效数字位数，scale规定小数点后数字长度，比如：

v这列数字的范围是-999.99~999.99，如果插入数据的小数点后数字长度超过2，会自动四舍五入后插入。

字符串型

char(size)存储最大长度固定的字符串，varchar(size)存储变长字符串。char(size)类型固定占用size个字符的空间，不足的右侧补空格，输出时移除右侧空格（除非设置了PAD_CHAR_TO_FULL_LENGTH，输出时才不会移除右侧空格），size的范围是0~255。varchar(size)类型size的范围是0~65535，如果长度不超过255，需要额外1字节记录长度，如果超过255，需要额外两字节记录长度。

set sql_mode=""用来恢复char(size)类型输出时删除右侧空格的默认行为。

时间型

date用于存储YYYY-MM-DD格式的日期，比如：

timestamp用于存储YYYY-MM-DD HH:mm:ss格式的时间戳，范围[1970-01-01 00:00:00~2037-12-31 23:59:59]，比如在数据库中设置初次插入时间及更新时间：

mysql> create table record (id int(5), op char(10), st timestamp default current_timestamp, lm timestamp default current_timestamp on update current_timestamp);Query OK, 0 rows affected (0.05 sec)mysql> insert into record (id, op) values (1, "insert");Query OK, 1 row affected (0.01 sec)mysql> select * from record;+------+--------+---------------------+---------------------+| id   | op     | st                  | lm                  |+------+--------+---------------------+---------------------+|    1 | insert | 2016-09-12 08:10:41 | 2016-09-12 08:10:41 |+------+--------+---------------------+---------------------+1 row in set (0.00 sec)mysql> update record set op="update" where id=1;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from record;+------+--------+---------------------+---------------------+| id   | op     | st                  | lm                  |+------+--------+---------------------+---------------------+|    1 | update | 2016-09-12 08:10:41 | 2016-09-12 08:11:05 |+------+--------+---------------------+---------------------+1 row in set (0.00 sec)

datetime也可以表示时间戳，且表示的范围为[1000-01-01 00:00:00~9999-12-31 23:59:59]。

修改数据表属性

alter语句可以修改已经存在的数据表的属性，如主键、外键、索引、列等等。比如为数据表添加新的列：

modify 语句可以修改某一列的属性：

drop 语句可以删除一列：

drop table

删除名为的分组。

show create table

查看名为的表结构。

DML

包括查询、更新、插入、删除数据等操作：

insert into

向数据表中插入数据，语法格式为insert into values (value_1, value_2, ...)或者指定为某几列赋值insert into (column_x, column_y, ...) values (value_x, value_y, ...)。

select

从数据表中查询数据，结果存储在一个结果表（结果集）中，语法格式为select * from 或者select from 。

对结果集的操作

结果集支持更多的操作以实现对查询结果的更多维度地分析。

select distinct

在查询结果中去重：

控制显示数目

在select语句的后面增加limit 可以限制显示前n条数据，limit , 限制从start位置（下标从0开始）开始，length条数据。

where

限制更精细的查询条件，格式为运算符值，其中运算符支持列表如下：

操作符	描述
=	等于
<>(或!=)	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
like	搜索简单模式
rlike	搜索正则模式
in	在某个列表中
between … and	在某个范围内
is null	是NULL
is not null	不是NULL
not	否定

在书写字面量时，字符串应该用引号包围，数值量不应该用引号包围：

like

like操作符可以实现简单的模糊匹配，%匹配多个字符，_匹配单个字符，比如：

mysql> select * from c;+------+| v    |+------+| lmz  || mars || loo  || sus  |+------+4 rows in set (0.00 sec)mysql> select * from c where v like "l%";+------+| v    |+------+| lmz  || loo  |+------+2 rows in set (0.02 sec)mysql> select * from c where v like "l_o";+------+| v    |+------+| loo  |+------+1 row in set (0.00 sec)

如果确实要查询%或_字符本身，可以配合escape 设置一个转义开关后对其进行转义，比如select * from c where v like "lm/%%" escape "/"在c中匹配v列以lm%开头的字符串。

rlike

rlike操作符用来实现正则匹配，语法与标准正则表达式相同。

in

in操作符用来在一个括号包围，逗号分隔的列表中查找指定的值，比如：

between …and

between …and操作符用来在指定范围内查找数据，可以是数值、字符串、日期等，区间范围左闭右开：

is null

is null判断该列的某一行的值为NULL（即没有赋值）。NULL的判断不能使用=运算符。

is not null

is not null判断该列的某一行的值不为NULL。

not

not操作符用来翻转查询条件的语义，实现反向查找，比如：

组合查询条件

可以使用and或者or组合查询条件，也可以使用括号实现查询条件的复杂组合。

结果排序

使用order by [DESC | ASC]可以对结果排序，DESC表示降序，ASC表示升序（默认）：

设置别名

数据表名字过长时可以设置别名：select from as ，比如：

为列名设置别名，会影响结果集的显示：

结果组合

比如如下两个数据表：

mysql> select * from staff;+------+-------+------+| id   | name  | jid  |+------+-------+------+|    1 | mars  |    1 ||    2 | loo   |    2 ||    3 | suson | NULL |+------+-------+------+3 rows in set (0.01 sec)mysql> select * from occupation;+------+-------------+| id   | description |+------+-------------+|    1 | teacher     ||    2 | nurse       ||    3 | doctor      |+------+-------------+3 rows in set (0.00 sec)

查询staff表中每个人的工作，可以使用：

mysql> select s.name, o.description as job from staff as s, occupation as o where s.jid=o.id;+------+---------+| name | job     |+------+---------+| mars | teacher || loo  | nurse   |+------+---------+2 rows in set (0.00 sec)

或者可以使用join语句。

join

join用于根据多个表中的列之间的关系，从这些表中查询数据，注意查询条件的语句是用on不是where：

join也可以写成inner join。

left join

left join用于列出左侧表格中的所有数据，及右侧表格符合匹配条件的数据（如果有的话），比如：

right join

right join用于列出右侧表格中的所有数据，及左侧表格符合匹配条件的数据（如果有的话），比如：

union

union用来组合两个select语句的查询结果，要求结果集中字段个数相同，每个字段的类型相同。会对重复结果去重，且查询结果的列名以前面的命名为准，比如有如下两个数据表：

mysql> select * from occupation;+------+-------------+| id   | description |+------+-------------+|    1 | teacher     ||    2 | nurse       ||    3 | doctor      |+------+-------------+3 rows in set (0.00 sec)mysql> select * from job;+------+--------+| id   | name   |+------+--------+|    1 | driver ||    2 | chef   ||    3 | doctor |+------+--------+3 rows in set (0.00 sec)

使用union查询结果如下：

union all

上面的两张数据表，使用union all查询结果如下：

MySQL不支持full join，所以可以配合union（根据业务场景可能会使用union all）+lef join+right join实现。

备份结果集

将查询结果备份到一个新表中的方法是：

将查询结果备份到其他数据库中的另一张表中的方法是：

update：更新数据表中某一行数据，语法格式update set = [, = , ...] where 运算符：

delete：删除数据表中的数据，语法格式为delete from where 运算符。删除所有行的语法为delete from 。

trunctate：清空数据表中的数据，语法格式为trunctate table ，与delete的不同之处在于，该操作不记录日志，是无法回滚的，效率更高，而delete操作是可以回滚的，记录日志，效率稍低。

优化查询速度

在经常做查询操作的数据表的那些列上创建索引可以优化查询速度。

查询索引

使用show index from 或者show keys from 可以查询某个数据表上创建了哪些索引。

创建索引

在创建数据表时创建索引：

使用unique key (column_1, ..)或者unique index (column_1, ..)可以创建唯一性索引，即建立索引的这一列或这几列不允许重复（最好同时限制not null约束，可以避免同时存在多个NULL的情况，unique约束无法限制多个NULL的情况）。

使用key (column_1, ..)或者index (column_1, ..)可以创建索引。

在已经创建的数据表上创建索引为已经创建的数据表添加索引

删除索引

se_name>se_name>se_name>se_name>se_name>

简介

SQL语言

DDL

show databases

create database se_name>se_name>

drop database se_name>se_name>

use se_name>se_name>

show tables

create table ( , , ..)

添加列的约束条件

not null

unique

primary key

foreign key

default

auto_increment

数据类型

整型

浮点型

字符串型

时间型

修改数据表属性

drop table

show create table

DML

insert into

select

对结果集的操作

select distinct

控制显示数目

where

like

rlike

in

between …and

is null

is not null

not

组合查询条件

结果排序

设置别名

结果组合

join

left join

right join

union

union all

备份结果集

优化查询速度

查询索引

创建索引

删除索引

修改数据表属性