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

转载请注明原始出处：http://blog.csdn.net/a464057216/article/details/52496916

简介

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 <database_name>

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

drop database <database_name>

删除名为<database_name>的数据库。

use <database_name>

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

show tables

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

create table <table_name> (<column_1> <type>, <column_2> <type>, ..)

创建数据表，其中<table_name>表示表名，<column_n>表示列名，<type>表示对应列的数据类型。

添加列的约束条件

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格式（也支持YYMMDD、YYYYMMDD、YYMMDDHHMMSS、YYYYMMDDHHMMSS）的时间戳，范围[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]。

time用来存储HH:MM:SS格式的日期，表示范围为[-839:59:59~839:59:59]。

year用来存储时间中“年”的部分，支持两位或4位数字。平时最好使用4位数字以免造成歧义，比如：

修改数据表属性

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

modify <column_name> <type>语句可以修改某一列的属性：

drop <column_name>语句可以删除一列：

drop table <table_name>

删除名为<table_name>的分组。

show create table <table_name>

查看名为<table_name>的表结构。

DML

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

insert into

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

select

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

对结果集的操作

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

select distinct

在查询结果中去重：

控制显示数目

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

where

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

操作符 描述 = 等于 <>(或!=) 不等于 > 大于 < 小于 >= 大于等于 <= 小于等于 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 <char>设置一个转义开关后对其进行转义，比如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 <column_name> [DESC | ASC]可以对结果排序，DESC表示降序，ASC表示升序（默认）：

设置别名

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

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

结果组合

比如如下两个数据表：

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 <table_name> set <column_1> = <value_1>[, <column_2> = <value_2>, ...] where <column_name> 运算符 <value>：

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

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

优化查询速度

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

查询索引

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

创建索引

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

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

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

• 在已经创建的数据表上创建索引

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

删除索引

如果觉得我的文章对您有帮助，欢迎关注我(CSDN:Mars Loo的博客)或者为这篇文章点赞，谢谢！