一、管理MySQL的命令

1、use 数据库名;

选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

use 数据库名;

例：

mysql> use test;
Database changed

2、show databases;

列出 MySQL 数据库管理系统的数据库列表。

show databases;

例：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

3、显示表

3.1、show tables;

显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。

show tables;

例：

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students       |
+----------------+
1 row in set (0.14 sec)

3.2、show columns from students;

show columns from  数据表;

显示数据表的属性，属性类型，主键信息，是否为 NULL，默认值等其他信息。

例：

mysql> show columns from students;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
| class | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.06 sec)

3.3、show index from students;

show index from  数据表;

显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

例：

mysql> show index from two;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| two   |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.07 sec)

3.4、show table status from 数据库名;

show table status from 数据库名;

显示数据库中所有表的信息

例：

mysql> show table status from test;
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| students | MyISAM |      10 | Fixed      |    5 |             65 |         390 | 18295873486192639 |         1024 |        65 |              1 | 2020-06-13 15:00:45 | 2020-06-15 09:14:57 | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.27 sec)

3.5、show table status from test like ‘stu%’;

show table status from test like 'stu%';

显示数据库 test 中，表名以stu开头的表的信息

例：

mysql> show table status from test like 'stu%';
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| students | MyISAM |      10 | Fixed      |    5 |             65 |         390 | 18295873486192639 |         1024 |        65 |              1 | 2020-06-13 15:00:45 | 2020-06-15 09:14:57 | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

3.6、show table status from test like ‘stu%’\G;

show table status from test like 'stu%'\G;

加上 \G，查询结果按列打印

例：

mysql> show table status from test like 'stu%'\G;
*************************** 1. row ***************************
           Name: students
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 5
 Avg_row_length: 65
    Data_length: 390
Max_data_length: 18295873486192639
   Index_length: 1024
      Data_free: 65
 Auto_increment: 1
    Create_time: 2020-06-13 15:00:45
    Update_time: 2020-06-15 09:14:57
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

3.7、show table status from test like ‘stu%’\g;

show table status from test like 'stu%'\g;

加上 \g，查询结果按表的形式打印

例：

mysql> show table status from test like 'stu%'\g;
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| students | MyISAM |      10 | Fixed      |    5 |             65 |         390 | 18295873486192639 |         1024 |        65 |              1 | 2020-06-13 15:00:45 | 2020-06-15 09:14:57 | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

ERROR:
No query specified

3.8、查看表结构

desc 表名;		（单独查看一张表）

3.9、查看建表语句

show create table 表名

4、MySQL 创建数据库

我们可以在登陆 MySQL 服务后，使用 create 命令创建数据库，语法如下:

1	create database 数据库名;

例：

mysql> create database test1;
Query OK, 1 row affected (0.14 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test1              |
+--------------------+
6 rows in set (0.00 sec)

5、drop 命令删除数据库

drop 命令格式：

1	drop database <数据库名>;

例：

mysql> drop database test1;
Query OK, 0 rows affected (0.22 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

二、MySQL 创建数据表

1、创建表

创建MySQL数据表需要以下信息：

表名
表字段名
定义每个表字段

语法：

create table table_name (column_name column_type) engine myisam charset utf8;

engine myisam charset utf8 防乱码，可以不写

2、MySQL 数据类型

MySQL中定义数据字段的类型对数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

字节(Byte)=8位(bit)

2.1、数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

mysql字段定义中INT(x)中的x仅仅指的是显示宽度。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧用0填满宽度。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。所以x的定义与存储空间没有任何关系都是4个字节。若想显示补充的0可以在设置zerofill，即id int(5) zerofill,

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

2.2、日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

2.3、字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

char表示定长、长度固定，varchanr表示变长，即长度可变。

即char类型是规定多少字长则必须存储多少字长，超过的长度的字段则只能截取出对应的长度进行存储，相对于要求字长长度不够的字段则用空格补齐。
而varchar类型则是只要在规定字长之内，有多少存多少，无需补齐；超出的部分和char一样，舍去即可。（由perfix来实现）
对于char类型来说，最多只能存放的字符个数为255，和编码无关。
varchar最多能存放65532个字符。VARCHAR的最大有效长度由最大行大小和使用的字符集来确定。整体最大长度是65532字节。
- 由字符集来确定，字符集分单字节和多字节

Latin1  一个字符占一个字节，最多能存放 65532 个字符
GBK     一个字符占两个字节， 最多能存 32766 个字符
UTF8    一个字符占三个字节， 最多能存 21844 个字符

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

3、表字段类型属性

3.1、auto_increment

auto_increment定义列为自增的属性，一般用于主键，数值会自动加1。

3.2、primary key

主键：承担标识作用的列，能够确定一条记录的唯一标识

primary key关键字用于定义列为主键。可以使用多列来定义主键，列间以逗号分隔。

主键是不能重复的，不能为空，可以区分每一行的列。

删除主键约束：

alter table 表名 drop primary key;

3.3、foreign key

外键：是另一个表的主键，外键可以有重复的，可以为空值，用来和其他表建立连接用的，一般谈到了外键，一定是至少涉及到了两个表。

foreign key 外键，用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性

创建外键：

create table T (id int(10) primary key,E_id int(2),foreign key(E_id) references E(id)) engine myisam charset utf8;

创建外键时，E_id为想要把该表的E_id字段设置成外键，E（id）为对应表E的主键字段

删除外键约束：

alter table 表名 drop foreign key 外键（区分大小写）;

3.4、索引

索引：使用索引可以快速访问数据库表中的特定信息，数据库索引好比是一本前面的目录，能加快数据库的查询速度。

3.4.1、索引种类

普通索引：仅加速查询
唯一索引：加速查询 + 列唯一值（可以有null）
主键索引：加速查询 + 列唯一值（不可以有null） + 表中只有一个
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索

3.4.2、语法

创建普通索引：

create index 索引名字 on 表名（列名）

修改表结构(添加索引)：

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定：

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
 );

创建唯一索引：

1	create unique index 索引名字 on 表名（列名）

创建普通组合索引：

create index 索引名字 on 表名（列1，列2）

删除索引的语法：

1	DROP INDEX [indexName] ON mytable;

3.4.3、索引的使用原则

经常在查询中用作条件的列应当添加索引，频繁进行排序或分组的列（即进行order by 或 group by 的列），应当为其添加索引；一个列的值域很大时应当为其添加索引。

3.5、zerofill

zerofill     int(x)型数据实际数据长度小于x，自动在左边补0，直到长度为x

3.6、enum

enum 单选，枚举插入数据时可按照选项序号进行插入

create table table_B (id int(2),name char(5),sex enum('男','女')) engine myisam charset utf8;

insert into table_B (id,name,sex) values (1,'张三',1);

insert into table_B (id,name,sex) values (2,'李四',3);	# 不存在第三个数据，此时age为空

3.7、set

set 多选

create table table_C (id int(3),result set('优','差','良'),time date) engine myisam charset utf8;

insert into table_C (id,result,time) values (1,'优,差','2020-06-16');

使用数字代表，每个选项依次为1,2,4,8,16，……

例：

优	        1
良	        2
差	        4
优，良	        3
优，差	        5
良，差	        6
优，良，差	7
……

3.8、not null

not null

空值是不占用空间的

mysql中的NULL其实是占用空间的

NOT NULL 的字段是不能插入NULL的，只能插入“空值”

很多表都包含可为NULL的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性（TIMESTAMP除外），然而通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引统计和值比较更加复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊的处理。当可为NULL的字段被索引时，每个索引记录需要一个额外的字节，在MyASIM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

通常把可为NULL的列改为NOT NULL 带来的性能提升比较小，所以（调忧时）没有必要首先在现有schema中查找并修改这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计为NULL的列。当然也有一些例外，例如值得一提的是，InnoDB使用单独的位（Bit）存储NULL值，所以对于稀疏数据（很多值为NULL，只有少数行是非NULL）有很好的空间效率。但这一点不适用于MyISAM

3.9、default

default 添加默认值

create table one (id int(4),name char(10) default ‘小明’) engine myisam charset utf8;

3.10、comment

comment 字段注释

create table one (id int(4) comment ‘学号’,name char(10)) engine myisam charset utf8;

3.11、engine charset

engine 设置存储引擎，charset 设置编码。

例1：创建表

mysql> create table one (id int(4)) engine myisam charset utf8;
Query OK, 0 rows affected, 2 warnings (0.41 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| one            |
| students       |
+----------------+
2 rows in set (0.00 sec)

mysql> desc one;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

例2：加入not null，防止数据为空时，出现NULL

mysql> create table two (id int not null auto_increment,name char(10) not null, class char(10),primary key (id)) engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> select * from two;
Empty set (0.04 sec)

mysql> desc two;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int      | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | NO   |     | NULL    |                |
| class | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

例3：加入自增，加入主键

mysql> create table five (
    -> id int not null auto_increment,
    -> name char(10) not null,
    -> class char(10),
    -> primary key (id)
    -> ) engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> desc five;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int      | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | NO   |     | NULL    |                |
| class | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

例4：加入主键，可以直接在要设置的列名后添加

mysql> create table one (id int(4) primary key,name char(10)) engine myisam charset utf8;

注意：MySQL命令终止符为分号 ; 。

注意： -> 是换行符标识，不要复制。

4、MySQL 删除数据表

MySQL中删除数据表是非常容易操作的，但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

语法：

drop table table_name ;				删除一个表
drop table table_name1,table_name2 ;		删除多个表

例1：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| five           |
| one            |
| students       |
| three          |
| two            |
+----------------+
5 rows in set (0.00 sec)

mysql> drop table one;
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| five           |
| students       |
| three          |
| two            |
+----------------+
4 rows in set (0.00 sec)

例2：

mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| a               |
| b               |
| c               |
+-----------------+
3 rows in set (0.00 sec)

mysql> drop table a,b;
Query OK, 0 rows affected (0.24 sec)

mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| c               |
+-----------------+
1 row in set (0.00 sec)

5、修改表名

rename table 旧表名 to 新表名
alter table 旧表名 rename to 新表名

七、MySQL 插入数据

1、插入一行或多行数据

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

语法：

insert into 表名称(列1，列2，列3，.....)  values  (值1，值2，值3，....),(值1，值2，值3，....),(值1，值2，值3，....),(值1，值2，值3，....);

insert into 表名称 set 列1=值1，列2=值2，……;

insert into 表名称 values  (值1，值2，值3，....),(值1，值2，值3，....),(值1，值2，值3，....),(值1，值2，值3，....);

如果数据是字符型，必须使用单引号或者双引号，如：”value”。

例：

mysql> insert into two (name,class) values ('张三','三班'),('李四','四班');
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from two;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 三班   |
|  2 | 李四   | 四班   |
+----+--------+--------+
2 rows in set (0.00 sec)

注意：使用箭头标记 -> 不是 SQL 语句的一部分，它仅仅表示一个新行，如果一条SQL语句太长，我们可以通过回车键来创建一个新行来编写 SQL 语句，SQL 语句的命令结束符为分号 ;。

在以上实例中，我们并没有提供 id 的数据，因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。所以，该字段会自动递增而不需要我们去设置。

2、插入列

alter table 表名 add 列名 列类型;		(新增一列，列在新增时需要附带该列的类别）

添加到指定列后面：

alter table 表名 add （column） 列名 varchar(30) after 列名;

添加到第一列：

alter table 表名 add （column） 列名 varchar(30) first;

注：column可写可不写

3、查询一个表的内容插入到另一个表中

insert into 表1 （列名1，列名2，……） select 列名1，列名2，…… from 表2;

八、删

使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

语法：

delete from table_name [where Clause]

如果没有指定 where 子句，MySQL 表中的所有记录将被删除。

可以在 where 子句中指定任何条件

可以在单个表中一次性删除记录。

删除一行数据

1	delete from 表 where 列=值（条件）;

删除空数据

delete from A where id is NULL;

1、删除一行数据

1	delete from 表 where 列=值（条件）;

mysql> select * from two;
+----+------+--------+------+
| id | name | class  | age  |
+----+------+--------+------+
|  1 | aaa  | NULL   | NULL |
|  2 | bbb  | NULL   | NULL |
|  3 | aaa  | 一班   |  687 |
|  4 | ccc  | NULL   | NULL |
+----+------+--------+------+
4 rows in set (0.00 sec)

mysql> delete from two where id=1;
Query OK, 1 row affected (0.09 sec)

mysql> select * from two;
+----+------+--------+------+
| id | name | class  | age  |
+----+------+--------+------+
|  2 | bbb  | NULL   | NULL |
|  3 | aaa  | 一班   |  687 |
|  4 | ccc  | NULL   | NULL |
+----+------+--------+------+
3 rows in set (0.00 sec)

2、删除空数据

delete from A where name is null;

mysql> select * from two;
+----+------+--------+------+
| id | name | class  | age  |
+----+------+--------+------+
|  2 | bbb  | NULL   | NULL |
|  3 | aaa  | 一班   |  687 |
|  4 | ccc  | NULL   | NULL |
+----+------+--------+------+
3 rows in set (0.00 sec)

mysql> delete from two where name='NULL';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from two;
+----+------+--------+------+
| id | name | class  | age  |
+----+------+--------+------+
|  2 | bbb  | NULL   | NULL |
|  3 | aaa  | 一班   |  687 |
|  4 | ccc  | NULL   | NULL |
+----+------+--------+------+
3 rows in set (0.00 sec)

mysql> delete from two where class is NULL;
Query OK, 2 rows affected (0.05 sec)

mysql> select * from two;
+----+------+--------+------+
| id | name | class  | age  |
+----+------+--------+------+
|  3 | aaa  | 一班   |  687 |
+----+------+--------+------+
1 row in set (0.00 sec)

3、删除多行数据

delete from 表名 where id=1 or id=2 or id=3;
delete from 表名 where id in (1,2,3);

4、删除一个单元格

删除一个单元格可以通过修改一个单元格为空来实现

1	update 表名 set 列名=null where 条件;

5、删除列

alter table 表名 drop （column） 列名;

6、清空表数据

delete from 表名

例如，设置主键自增的表，删除表内容后，再次添加数据时，主键自增的数据会接着原来的数据继续添加

truncate table 表名	（清空表里所有行，比delete更彻底，不能跟where子句）

例如，设置主键自增的表，删除表内容后，再次添加数据时，主键自增的数据会直接从最开始的值添加

7、drop 命令删除数据库

1	drop database <数据库名>;

8、MySQL 删除数据表

MySQL中删除数据表是非常容易操作的，但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

语法：

drop table table_name ;				删除一个表
drop table table_name1,table_name2 ;		删除多个表

九、改

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。

语法：

update table_name set field1=new-value1, field2=new-value2 [WHERE Clause]

可以同时更新一个或多个字段。

可以在 WHERE 子句中指定任何条件。

可以在一个单独表中同时更新数据。

当需要更新数据表中指定行的数据时可以使用 WHERE 子句

1、只更新一个数据

mysql> update two set name='王二' where id=1;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from two;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 王二   | 三班   |
|  2 | 李四   | 四班   |
|  3 | aaa    | 一班   |
+----+--------+--------+
3 rows in set (0.00 sec)

2、更新同一行的多个数据

mysql> update two set name='王二',class='二班' where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from two;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 王二   | 二班   |
|  2 | 李四   | 四班   |
|  3 | aaa    | 一班   |
+----+--------+--------+
3 rows in set (0.00 sec)

3、更新同一列为相同数据

mysql> update two set name='王二';
Query OK, 2 rows affected (0.05 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> select * from two;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 王二   | 二班   |
|  2 | 王二   | 四班   |
|  3 | 王二   | 一班   |
+----+--------+--------+
3 rows in set (0.00 sec)

4、更新多行多列数据

方法1： on duplicate key update 语法

on duplicate key update 语法的官方说明

它会先执行插入操作，碰到有主键或唯一索引的列发生冲突时，对冲突的这一行，执行update操作，更新sql语句中指定的某几列。如果所有的列都不冲突，此语法和简单的insert into语法效果一样。

例：

mysql> select * from two;
+----+--------+--------+------+
| id | name   | class  | age  |
+----+--------+--------+------+
|  1 | 王二   | 二班   |   11 |
|  2 | 王二   | 四班   |   22 |
|  3 | 王二   | 一班   |   33 |
+----+--------+--------+------+
3 rows in set (0.00 sec)

mysql> insert into two (id,name,age) values (2,'李四',2222),(3,'aaa',3333) on duplicate key update name=values(name),age=values(age);
Query OK, 4 rows affected (0.29 sec)
Records: 2  Duplicates: 2  Warnings: 0

mysql> select * from two;
+----+--------+--------+------+
| id | name   | class  | age  |
+----+--------+--------+------+
|  1 | 王二   | 二班   |   11 |
|  2 | 李四   | 四班   | 2222 |
|  3 | aaa    | 一班   | 3333 |
+----+--------+--------+------+
3 rows in set (0.00 sec)

方法2： replace into 语法

replace into 语法的官方说明

replace和insert所作的工作完全相同，区别是当碰到有主键或唯一索引的列发生冲突时，对冲突的这一行，在insert前会先对这行数据执行delete操作。效果是这一行中没有被指定值的列会被更新成本列的默认值，如果所有的列都不冲突，此语法和简单的insert into语法效果一样。

例：

mysql> select * from two;
+----+--------+--------+------+
| id | name   | class  | age  |
+----+--------+--------+------+
|  1 | 王二   | 二班   |  687 |
|  2 | 李四   | 四班   |  687 |
|  3 | aaa    | 一班   |  687 |
+----+--------+--------+------+
3 rows in set (0.00 sec)

mysql> replace into two (id,name) values(1,'aaa'),(2,'bbb');
Query OK, 4 rows affected (0.07 sec)
Records: 2  Duplicates: 2  Warnings: 0

mysql> select * from two;
+----+------+--------+------+
| id | name | class  | age  |
+----+------+--------+------+
|  1 | aaa  | NULL   | NULL |
|  2 | bbb  | NULL   | NULL |
|  3 | aaa  | 一班   |  687 |
+----+------+--------+------+
3 rows in set (0.00 sec)

5、修改一个单元格为空

1	update 表名 set 列名=null where 条件;

6、修改列名

alter table 表名 change 旧列名 新列名 列类型;

列属性和原属性一致，则列中数据不变
列属性和原属性不同，则列中数据变为新类型的默认值
新列名和旧列名相同，即为修改列属性

7、修改列属性

alter table 表名 modify 列名 列属性;

8、修改表名

rename table 旧表名 to 新表名

alter table 旧表名 rename to 新表名

十、单表查询

1、MySQL 查询数据

MySQL 数据库使用SQL SELECT语句来查询数据。

语法：

select column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [ OFFSET M]

select 查询内容 from 表名;

select 查询内容 from 表名 where 列=值（条件）;

查询语句中可以使用一个或者多个列，列之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

SELECT 命令可以读取一条或者多条记录。

可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

可以使用 WHERE 语句来包含任何条件。

可以使用 LIMIT 属性来设定返回的记录数。

可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

例1：

mysql> select * from two;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 三班   |
|  2 | 李四   | 四班   |
+----+--------+--------+

例2：

mysql> select name from two;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
+--------+
2 rows in set (0.00 sec)

例3：

mysql> select name from two where id=1;
+--------+
| name   |
+--------+
| 张三   |
+--------+
1 row in set (0.00 sec)

例4：

A表：

id	name	age	score
1	王华	18	60.5
2	张小龙	22	100
3	小明	19	66

查询A表所有信息

1	Select * from A;

查询名字叫王华人的所有的信息

Select   *   from   A  where  name=’王华’;

查询名字叫王华的年龄，分数

Select   age,score  from  A   where name=’王华’;

查看前五行：

SQLserver：	select top 5 * from A；
Mysql：    	Select * from A limit 5；
Oracle：   	Select * from A where rownum <=5;

2、where 子句

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

语法：

select field1, field2,...fieldN from table_name1, table_name2... [where condition1 [and [or]] condition2.....

查询语句中可以使用一个或者多个表，表之间使用逗号, 分割，并使用where语句来设定查询条件。

可以在 WHERE 子句中指定任何条件。

可以使用 AND 或者 OR 指定一个或多个条件。

WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。

WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

例：

mysql> select name from two where id=1;
+--------+
| name   |
+--------+
| 张三   |
+--------+
1 row in set (0.00 sec)

MySQL 的 where 子句的字符串比较是不区分大小写的。可以使用 binary 关键字来设定 where 子句的字符串比较是区分大小写的。

例：

mysql> select name from two where id=1;
mysql> select * from two;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 三班   |
|  2 | 李四   | 四班   |
|  3 | aaa    | 一班   |
+----+--------+--------+
3 rows in set (0.00 sec)

mysql> select * from two where name='AAA';
+----+------+--------+
| id | name | class  |
+----+------+--------+
|  3 | aaa  | 一班   |
+----+------+--------+
1 row in set (0.00 sec)

mysql> select * from two where binary name='AAA';
Empty set (0.05 sec)

3、like 模糊查询

如果没有使用百分号 %, like 子句与等号 = 的效果是一样的。

语法

1
2
3

select 查询内容 from 表名 where 列名 like ’%a’;

select 查询内容 from 表名 where 列名 not like ‘%a’;

‘%a’		以a结尾的数据
‘a%’		以a开头的数据
‘%a%’	        包含a的数据
‘_a_’		三位且中间是a的数据
‘_a’		两位且结尾是a的数据
‘a_’		两位且开头是a的数据
‘a__b’	        四位以a开头且以b结尾的数据

查询张姓开头的人的分数：

select score from A where name like ‘张%’;

查询名字优三位数，且中间一个字为强的人的id：

select id from A where name like ‘_强_’;

查询不以张姓开头的人的分数：

select score from A where name not like ‘张%’;

mysql> select * from two;
+----+------+--------+------+
| id | name | class  | age  |
+----+------+--------+------+
|  3 | bbb  | 二班   |   21 |
|  1 | aaa  | 一班   |  687 |
|  2 | aab  | 二班   |   22 |
+----+------+--------+------+
3 rows in set (0.00 sec)

mysql> select * from two where name like 'a%';
+----+------+--------+------+
| id | name | class  | age  |
+----+------+--------+------+
|  1 | aaa  | 一班   |  687 |
|  2 | aab  | 二班   |   22 |
+----+------+--------+------+
2 rows in set (0.05 sec)

4、限制条件的格式

运算符：

<	小于
>	大于
>=	大于等于
<=	小于等于
<>	不等于
!=	不等于

运算符连接多条数据进行比较时，无法直接比较，可以在多条数据前添加any或all

in 在范围之内

在嵌套语句中，如果嵌套部分查询的结果为多条时，只能用 in；如果只有一条结果，可以用“in”或者“=”

select * from A where sal = (select max(sal) from A);

select * from A where sal in (select max(sal) from A group by class);

注意：

在嵌套语句中 in 和嵌套语句中包含 limit的语句不能一起使用

可以写为：

1	select * from goods where click_count in (select click_count from ( select * from goods group by click_count order by click_count desc limit 2,3) as t);

not in 不在范围内

连接符号：

and 和，且
or 或

and 和or 同时使用的时候，先运算and，再运算or

如果需要先运算or，需要加括号

xxx and xxx or xxx and xxx

(xxx or xxx) and (xxx or xxx)     也可以写为 a in (xxx,yyy) and b in (xxx,yyy)

between ... and ... 在…和…之间

result 的值在50-60之间，用 result >=50 and result <=60 表示

或 result between 50 and 60

not between ... and ... 不在…和…之间

result 的值在50-60之外，用 result <50 or result >60 表示

或 result not between 50 and 60

is
is not

设置name为null

update 表名 set name=null where id=1;

查询姓名为空的信息

select * from 表名 where name is null;

null 不能用 ‘!=’，’=’，’<>’ 来判断虽然不会报错，但数据不正确。

应该用，is not null 或 is null

5、as 取别名

取别名：as 对一个数据库表字段取别名

1
2
3

select 要查的列名 as ‘别名’ from 表名;

select 要查的列名 ‘别名’ from 表名;

as可以不写，别名可以不带引号

例：

mysql> select name from AAA;
+--------------+
| name         |
+--------------+
| 小花龙       |
| 小等待龙     |
| 小李         |
+--------------+
3 rows in set (0.00 sec)

mysql> select name '姓名' from AAA;
+--------------+
| 姓名         |
+--------------+
| 小花龙       |
| 小等待龙     |
| 小李         |
+--------------+
3 rows in set (0.00 sec)

from型嵌套的表必须要取别名

6、group by 分组

group by 语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。

聚合函数中select字句中的列名必须为分组列或列函数。列函数对于group by子句定义的每个组各返回一个结果。

group by只取各分组中的第一个数据。

group by可以对多个列进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

在分组后可以直接跟 asc/desc 对分组的列进行排序，默认为asc，可以省略

GROUP BY 语法:

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

6.1分组逻辑

对一个列进行分组：

select * from CCC  group by class

对两个列进行分组：

select * from CCC  group by sex,class

6.2、添加演示实例

菜鸟教程MySQL GROUP BY 语句

本实例使用到了以下表结构及数据，使用前我们可以先将以下数据导入数据库中。

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
--  Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
--  Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

导入成功后，执行以下 SQL 语句：

mysql> set names utf8;
mysql> select * from employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明   | 2016-04-22 15:25:33 |      1 |
|  2 | 小王   | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽   | 2016-04-19 15:26:02 |      2 |
|  4 | 小王   | 2016-04-07 15:26:14 |      4 |
|  5 | 小明   | 2016-04-11 15:26:40 |      4 |
|  6 | 小明   | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

6.3、使用group by

接下来我们使用 GROUP BY 语句将数据表按名字进行分组，并统计每个人有多少条记录：

mysql> select name, count(*) from   employee_tbl group by name;
+--------+----------+
| name   | count(*) |
+--------+----------+
| 小明   |        3 |
| 小王   |        2 |
| 小丽   |        1 |
+--------+----------+
3 rows in set (0.00 sec)

6.4、使用with rollup

with rollup 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽   |            2 |
| 小明   |            7 |
| 小王   |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.02 sec)

其中记录 NULL 表示所有人的登录次数。

6.5、使用 coalesce

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：

如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

以下实例中如果名字为空我们使用总数代替：

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数')   | singin_count |
+--------------------------+--------------+
| 小丽                     |            2 |
| 小明                     |            7 |
| 小王                     |            7 |
| 总数                     |           16 |
+--------------------------+--------------+
4 rows in set (0.00 sec)

7、having 筛选

having语句通常与group by语句联合使用，用来过滤由group by语句返回的记录集。

having语句的存在弥补了where关键字不能与聚合函数联合使用的不足，当存在聚合函数为条件的sql中，需要把where换成having

查询（每个班级）平均分大于60的班级名字

select class from A group by class having avg(score)>60;

8、order by 排序

order by 列名 desc/asc

desc 代表倒序

asc 代表正序不填写则默认为正序

可以对多列进行排序：

order by class，name desc 	 （对class正序排列，再对name倒序排列）

例：

mysql> select * from one order by name asc;
+------+-----------+--------+-----------+
| id   | name      | class  | grade     |
+------+-----------+--------+-----------+
|    3 | 孙一孙    | 一班   | 二年级    |
|    4 | 李一李    | 二班   | 二年级    |
|    1 | 赵一赵    | 一班   | 一年级    |
|    2 | 钱一钱    | 二班   | 一年级    |
+------+-----------+--------+-----------+
4 rows in set (0.00 sec)

mysql> select * from one order by name desc;
+------+-----------+--------+-----------+
| id   | name      | class  | grade     |
+------+-----------+--------+-----------+
|    2 | 钱一钱    | 二班   | 一年级    |
|    1 | 赵一赵    | 一班   | 一年级    |
|    4 | 李一李    | 二班   | 二年级    |
|    3 | 孙一孙    | 一班   | 二年级    |
+------+-----------+--------+-----------+
4 rows in set (0.00 sec)

9、limit

放在sql语句尾，用来决定展示前多少条的数据 limit x,y

limit x 
代表展示前x行

limit x,y
x代表从第x行开始
y代表展示y行

注意！若查询一个范围内的行，比如2-7行：

此时，x为2-1，y为7-x

查看前3行的数据：

1	Select * from A limit 3

查看从第3行开始，往后5行的数据：

Select * from A limit 2,5；

查看后两行：

1	select * from CCC order by score desc limit 2;

查看前五行：

SQLserver：	select top 5 * from A；
Mysql：    	Select * from A limit 5；
Oracle：   	Select * from A where rownum <=5;

注意：

在嵌套语句中 in 和嵌套语句中包含 limit的语句不能一起使用

可以写为：

1	select * from goods where click_count in (select click_count from ( select * from goods group by click_count order by click_count desc limit 2,3) as t);

10、去重 distinct

1	select distinct 列名 from 表名;

单列去重：

查询学生表里的学生字段并去重

select distinct name from student;

多列去重：

查询学生表里的学生、班级、分数字段并对其去重

select distinct name,class,score from A;

对全部字段进行去重
select distinct * from A;

11、union（全连接）

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

union必须满足取出的列数相同，列名称未必一致，默认以第一条sql的列名称为准。

语法:

select expression1, expression2, ... expression_n
from tables
[where conditions]
union [ALL | DISTINCT]
select expression1, expression2, ... expression_n
from tables
[where conditions];

参数：

expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
where conditions: 可选， 检索条件。
distinct: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
all: 可选，返回所有结果集，包含重复数据。

演示数据库：

mysql> select * from one;
+------+-----------+--------+-----------+
| id   | name      | class  | grade     |
+------+-----------+--------+-----------+
|    1 | 赵一赵    | 一班   | 一年级    |
|    2 | 钱一钱    | 二班   | 一年级    |
|    3 | 孙一孙    | 一班   | 二年级    |
|    4 | 李一李    | 二班   | 二年级    |
+------+-----------+--------+-----------+
4 rows in set (0.00 sec)

mysql> select * from four;
+------+-----------+--------+-----------+
| id   | name      | class  | grade     |
+------+-----------+--------+-----------+
|    1 | 周四周    | 一班   | 一年级    |
|    2 | 吴四吴    | 二班   | 一年级    |
|    3 | 郑四郑    | 一班   | 二年级    |
|    4 | 王四王    | 二班   | 二年级    |
+------+-----------+--------+-----------+
4 rows in set (0.00 sec)

例1：SQL union 实例

从 “one” 和 “four” 表中选取所有不同的class（只有不同的值）：

mysql> select class from one
    -> union
    -> select class from four
    -> order by class;
+--------+
| class  |
+--------+
| 一班   |
| 二班   |
+--------+
2 rows in set (0.32 sec)

例2：SQL union all 实例

从 “one” 和 “four” 表中选取所有的class（也有重复的值）：

mysql> select class from one
    -> union all
    -> select class from four
    -> order by class;
+--------+
| class  |
+--------+
| 一班   |
| 一班   |
| 一班   |
| 一班   |
| 二班   |
| 二班   |
| 二班   |
| 二班   |
+--------+
8 rows in set (0.00 sec)

例3：带有 where 的 SQL union all

从 “one” 和 “four” 表中选取所有的年级（一年级）（也有重复的值）：

mysql> select grade,name from one where grade='一年级'
    -> union all
    -> select grade,name from four where grade='一年级'
    -> order by grade;
+-----------+-----------+
| grade     | name      |
+-----------+-----------+
| 一年级    | 赵一赵    |
| 一年级    | 钱一钱    |
| 一年级    | 周四周    |
| 一年级    | 吴四吴    |
+-----------+-----------+
4 rows in set (0.00 sec)

例4：查询每个班级中分数最高的人的信息

mysql> select * from CCC;                                                               
+----+-----------------------+-------+-------+------+
| id | name                  | class | score | sex  |
+----+-----------------------+-------+-------+------+
|  1 | 小芳                  | 1班   |    80 | 男   |
|  2 | 小这芳                | 2班   |    90 | 女   |
|  3 | 小明                  | 1班   |    50 | 女   |
|  4 | 小花                  | 1班   |    90 | 女   |
|  5 | 张晓明山阿什顿        | 3班   |    80 | 男   |
|  6 | 小明                  | 3班   |    60 | 女   |
|  7 | 小龙                  | 3班   |    75 | 男   |
|  8 | 小李                  | 1班   |    90 | 男   |
|  9 | 小明                  | 2班   |   100 | 女   |
+----+-----------------------+-------+-------+------+
9 rows in set (0.00 sec)

mysql> select * from CCC where class=’1班’ and score=(select max(score) from CCC where class='1班')
    -> union
    -> select * from (select * from CCC where class='2班' order by score desc) as A where score=(select max(score) from CCC where class='2班')
    -> union
    -> select * from CCC where class=’3班’ and score=(select score from CCC where class='3班' order by score desc limit 1);
+----+-----------------------+-------+-------+------+
| id | name                  | class | score | sex  |
+----+-----------------------+-------+-------+------+
|  4 | 小花                  | 1班   |    90 | 女   |
|  8 | 小李                  | 1班   |    90 | 男   |
|  9 | 小明                  | 2班   |   100 | 女   |
|  5 | 张晓明山阿什顿        | 3班   |    80 | 男   |
+----+-----------------------+-------+-------+------+
4 rows in set (0.00 sec)

12、聚合函数

统计函数关键字：	                count（列名）统计一列非空数据的行数
求和关键字;			sum（列名）
求平均数：			avg（列名）
求最大值：			max（列名）
求最小值：			min（列名）

例1：

统计班级的人的个数：
select count(id) from A;

统计班级分数的总和：
select sum(score) from A;

统计班级的人的平均年龄：
select avg(age) from A;

统计班级的所有人的最大年龄：
select max(age) from A;

统计班级的所有人的最小年龄：
select min(age) from A;

例2：

查询最大的分数（两种方法）
select max(score) 最大分数 from A;
select score from A order by score desc limit 1;

查询最小的分数（两种方法）
select min(score) 最小分数 from A;
select score from A order by score limit 1;

查询平均工资
select avg(sal) 平均工资 from A;

统计A表中有多少条数据
select count(id) from A;
select count(*) from A;
select count(class) from A;

查询所有人的薪资总和
select sum(sal) from A;

查询工资加奖金的总和（两种方法）
select sum(sal+comm) 总和 from A;
select sum(sal)+sum(comm) 总和 from A;

查询每个人的工资与奖金的和
select sal+comm 总和 from A;

13、单表查询最终格式

select 查询内容
from 表
where 限制条件
group by 列名		# 分组
having 限制条件		# 等同于where
order by 列名 desc/asc		# 排序
limit 行数				# 查询前/后几行

查看前五行： SQLserver：select top5* from A；
           Mysql：    Select from A limit5；
           Oracle：   Select from A where rownum  <=5;

几个关键字的使用顺序为：

where、group by、having、order by、limit

select name as ‘名字’ from student where age=15 group by class having avg(score) order by score desc limit 3;

十一、多表查询

在多表查询时，先找查询内容，再看跟哪几个表，最后再找限制条件（查询内容和限制条件在哪几个表里面，就需要把这几个表关联到一起）

多表查询必须指定字段所在的表，只有一个表拥有的字段可以不加表名（一般都会加上）

关联多表后，如果后面需要跟限制条件，需要使用and链接，同时，如果该限制条件中存在or的用法，则需要把该限制条件用括号统一包起来

如：

select A.name from A,B where A.id=B.id and (A.score>90 or A.score<30);

1、内连接

inner join…… on……

查询两张表A，B中所有符合where条件限制的结果，如果这两张表中存在至少一个匹配项，就返回该匹配项的结果。

两表：

1
2
3

select 内容 from A inner join B on A.主键=B.外键 where 列=值（条件）;

select 内容 from A,B where A.主键=B.外键 and 列=值（条件）;

三表：

1
2
3

select 内容 from A inner join B on A.主键=B.外键 inner join C on A.主键=C.外键 where 条件;

select 内容 from A,B,C where A.主键=B.外键 and A.主键=C.外键 and 列=值（条件）;

查询名字为小明的人的年龄：

select B.age from A inner join B on A.id=B.id where A.name=’小明’;

select B.age from A,B where A.id=B.id and A.name=’小明’;

2、左连接

left join…… on……

以左表为主，查询左表中的所有数据以及左表所有符合where条件的结果，即使右表中没有匹配项，也从左表中返回所有的数据行。

两表：

1	select 内容 from A left join B on A.主键=B.外键 where 条件;

三表：

1	select 内容 from A left join B on A.主键=B.外键 left join C on A.主键=C.外键 where 条件;

3、右连接

right join…… on……

以右表为主，查询右表中的所有数据以及右表所有符合where条件的结果，即使左表中没有匹配项，也从右表中返回所有的数据行。

两表：

1	select 内容 from A right join B on A.主键=B.外键 where 条件;

三表：

1	select 内容 from A right join B on A.主键=B.外键 right join C on A.主键=C.外键 where 条件;

十二、视图

对视图的增加删除和修改的操作会影响到基础的物理表，创建视图可以保证原表数据的安全性。

1、创建视图

1	create view 视图名 as 后面跟select语句

单表：

1	create view A as select * from C;

多表：

create view A as select A.name,B.class from A,B where A.id=B.id;

create view A as select A.name as name1,B.name as name2 from A,B where A.id=B.id;

与多表联查不同的是，创建视图不会把第二个表相同的字段默认添加“（）”，所以需要起别名保证字段不重复，多表联查创建的视图也可以任意操作，原来表也会发生改变。

2、操作

对视图重命名：

rename table st to stt;

删除视图：

1	drop view stt;

视图可以正常对数据进行增删改查，但是不能更改表结构

3、作用

① 简化了操作，把经常使用的数据定义为视图

② 安全性，用户只能查询和修改能看到的数据

③ 逻辑上独立，屏蔽了真实表的结构带来的影响

十三、alter 语句整理

1、删除主键约束

alter table 表名 drop primary key;

2、删除外键约束

alter table 表名 drop foreign key 外键（区分大小写）;

3、添加索引

创建普通索引
create index 索引名字 on 表名（列名）

修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)

4、修改表名

alter table 旧表名 rename to 新表名

rename table 旧表名 to 新表名

5、插入列

alter table 表名 add 列名 列类型;		(新增一列，列在新增时需要附带该列的类别）

添加到指定列后面：
        alter table 表名 add （column） 列名 varchar(30) after 列名;

添加到第一列：
        alter table 表名 add （column） 列名 varchar(30) first;

注：column可写可不写

6、删除列

alter table 表名 drop （column） 列名;

7、修改列名

alter table 表名 change 旧列名 新列名 列类型;

列属性和原属性一致，则列中数据不变
列属性和原属性不同，则列中数据变为新类型的默认值
新列名和旧列名相同，即为修改列属性

8、修改列属性

alter table 表名 modify 列名 列属性;

9、修改表名

alter table 旧表名 rename to 新表名

rename table 旧表名 to 新表名