一、SQL语言的分类
SQL语言分为四大类：
DDL (Data Definition Language) 数据定义语言
DML (Data Manipulation Language) 数据操纵语言
DQL (Data Query Language) 数据查询语言
DCL (Data Control Language) 数据控制语言二、DDL数据定义语言
DDL的针对对象是：库、表
主要有以下几个SQL命令：CREATE、DROP、USE、SHOW、ALTER、ADD
1.针对库的操作
1.1 创建库 CREATE
查看语法
mysql> HELP CREATE DATABASE
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: https://dev.mysql.com/doc/refman/5.6/en/create-database.html建库语句
在库的操作中，database和schema是同等意思。
- 使用database建库
mysql> CREATE DATABASE db_test1;
Query OK, 1 row affected (0.01 sec)- 使用schema建库
mysql> CREATE SCHEMA db_test2;
Query OK, 1 row affected (0.00 sec)- 当库已经存在，建库时忽略报错
一般来说，如果库存在，再建库时会提示报错，如下：
mysql> CREATE DATABASE db_test1;
ERROR 1007 (HY000): Can't create database 'db_test1'; database exists使用选项IF NOT EXISTS，如果库已存在则忽略报错信息
mysql> CREATE DATABASE IF NOT EXISTS db_test1;
Query OK, 1 row affected, 1 warning (0.00 sec)- 指定字符集建库
mysql> CREATE DATABASE db_test3 CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)查看建库语句
mysql> show create database db_test3;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| db_test3 | CREATE DATABASE `db_test3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)1.2 删除库 DROP
mysql> DROP DATABASE db_test2;
Query OK, 0 rows affected (0.00 sec)1.3 修改库 ALTER
先查看库，确定要修改什么
mysql> SHOW CREATE DATABASE db_test1;
+----------+---------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------+
| db_test1 | CREATE DATABASE `db_test1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)修改db_test1这个表的字符集为utf8
mysql> ALTER DATABASE db_test1 CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)查看修改后的db_test1的字符集
mysql> SHOW CREATE DATABASE db_test1;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| db_test1 | CREATE DATABASE `db_test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)2.针对表操作
2.1 创建表 CREATE TABLE
查看语法
mysql> HELP CREATE TABLE
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }建表语句
- 建表添加单列
mysql> CREATE TABLE tb1(id int);
Query OK, 0 rows affected (0.00 sec)- 建表时添加多列
mysql> CREATE TABLE tb2(
-> id int,
-> name varchar(10),
-> age tinyint);
Query OK, 0 rows affected (0.01 sec)- 查看表的结构
mysql> DESC tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> DESC tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)==注意：==建表需要先进入库中，而且建表时至少有一列字段且要指定数据类型，否则建表都会失败。
用表结构属性建表
- 表结构属性
NOT NULL 不为空
PRIMARY KEY 主键（唯一且非空的）
AUTO_INCREMENT 自增
UNIQUE KEY 唯一键（唯一的）
DEFAULT 默认值
UNSIGNED 非负数
COMMENT 注释- 创建表
tb3
mysql> CREATE TABLE tb3(
-> id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
-> name VARCHAR(10) NOT NULL COMMENT '姓名',
-> sex ENUM('man','woman') NOT NULL COMMENT '性别',
-> age TINYINT UNSIGNED NOT NULL COMMENT '年龄',
-> status ENUM('0','1') DEFAULT '0' COMMENT '学生是否毕业');
Query OK, 0 rows affected (0.53 sec)查看tb3表的结构
mysql> DESC tb3;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| sex | enum('man','woman') | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| status | enum('0','1') | YES | | 0 | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)- 查看建表语句
mysql> SHOW CREATE TABLE tb3\G
*************************** 1. row ***************************
Table: tb3
Create Table: CREATE TABLE `tb3` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(10) NOT NULL COMMENT '姓名',
`sex` enum('man','woman') NOT NULL COMMENT '性别',
`age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
`status` enum('0','1') DEFAULT '0' COMMENT '学生是否毕业',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)2.2 插入语句 INSERT
插入数据方法一：每一列都插入数据
- 插入单行数据
mysql> INSERT INTO tb2 VALUES(1,'whb',111);
Query OK, 1 row affected (0.00 sec)- 插入多行数据
mysql> INSERT INTO tb2 VALUES
-> (2,'qh',112),
-> (3,'hk',113),
-> (4,'cp',114),
-> (5,'xt',115),
-> (6,'zyc',116);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0- 查看表的数据
mysql> SELECT * FROM tb2;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | whb | 111 |
| 2 | qh | 112 |
| 3 | hk | 113 |
| 4 | cp | 114 |
| 5 | xt | 115 |
| 6 | zyc | 116 |
+------+------+------+
6 rows in set (0.00 sec)插入数据方法二：在指定列插入数据
- 插入数据
mysql> INSERT INTO tb3(name,sex,age) VALUES
-> ('whb','man',111),
-> ('qh','man',112),
-> ('hk','man',113),
-> ('jll','woman',114);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0- 查看表
tb3数据
mysql> SELECT * FROM tb3;
+----+------+-------+-----+--------+
| id | name | sex | age | status |
+----+------+-------+-----+--------+
| 1 | whb | man | 111 | 0 |
| 2 | qh | man | 112 | 0 |
| 3 | hk | man | 113 | 0 |
| 4 | jll | woman | 114 | 0 |
+----+------+-------+-----+--------+
4 rows in set (0.00 sec)2.3 修改表
- 修改表名
mysql> ALTER TABLE tb3 RENAME students;- 添加列
添加单个字段（列）
mysql> ALTER TABLE students ADD enrollment DATETIME DEFAULT now();添加多个字段（列）
mysql> ALTER TABLE students ADD ad1 VARCHAR(10), ADD ad2 VARCHAR(10);- 添加字段到最前面
mysql> ALTER TABLE students ADD ad3 VARCHAR(10) FIRST;- 添加字段到指定字段后
mysql> ALTER TABLE students ADD ad4 VARCHAR(10) AFTER ad3;- 删除指定字段
删除单个字段
mysql> ALTER TABLE students DROP ad1;删除多个字段
mysql> ALTER TABLE students DROP ad2, DROP ad3;- 修改字段属性
mysql> ALTER TABLE students MODIFY ad4 INT;- 修改字段的名字
mysql> ALTER TABLE students CHANGE ad4 uid int;三、DCL数据控制语言
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视，用户，权限，事务等。
1.授权命令 GRANT
1.1 授权用户权限
- 给用户授权
给用户root@'%'授权所有权限
mysql> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY '123'; - 查看用户的权限
mysql> SHOW GRANTS FOR root@'%';1.2 mysql中的所有权限
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE1.3 授权的扩展权限
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connections_per_hour：一个用户每小时可连接到服务器的次数
max_user_connections：允许同时连接数量- 扩展权限的用法
mysql> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY '123' WITH MAX_USER_CONNECTIONS 1; 2.回收权限 REVOKE
mysql> REVOKE DROP ON *.* FROM root@'%';3.授权一个超级管理员（权限等同于root）
mysql> GRANT ALL PRIVILEGES ON *.* TO whb@'%' IDENTIFIED BY '123' WITH GRANT OPTIONS;四、DML数据操作语言
操作对象：行
常用命令：INSERT、UPDATE、DELETE
1.数据插入命令 INSERT
先查看表tb2结构，确定需要插入的数据
mysql> DESC tb2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)1.1 每个字段都插入数据（不推荐）
这样插入数据，必须保证每个字段都要有值，否则会插入失败
mysql> INSERT INTO tb2 VALUES(7,'zyc',123);1.2 在指定字段插入数据
- 插入单条数据
mysql> INSERT INTO tb2(name,age) VALUES('ysl',22);- 插入多条数据
mysql> INSERT INTO tb2(name,age) VALUES
-> ('ysl',22),
-> ('ysl',22);2.修改表数据 UPDATE
不加条件的修改方法
mysql> UPDATE tb2 SET age=111;==注意：==不加任何条件的修改，容易误改数据。因为它会把表中age字段的数据值都修改了。
加条件条件的修改
mysql> UPDATE tb2 SET age=22 WHERE name='ysl';3.删除数据 DELETE
不加条件删除数据
这样删除数据会删除表中的所有数据，是非常危险的用法。
mysql> DELETE FROM tb2;加条件删除数据
相对来说比较安全，只删除满足条件的数据
mysql> DELETE FROM tb2 WHERE name='ysl';TRUNCATE和DELETE的区别：
TRUNCATE是删除表，再重新创建这个表。属于DDL，DELETE是一条一条删除表中的数据，属于DML。
伪删除（使用UPDATE替代DELETE）
开发中很少使用DELETE,删除有物理删除和逻辑删除，其中逻辑删除可以通过给表添加一个字段(isDel)，若值为1，代表删除；若值为0，代表没有删除。
此时，对数据的删除操作就变成了DELETE操作了。
- 添加isDel字段
mysql> ALTER TABLE tb2 ADD isDel ENUM('0','1') DEFAULT '0';- 使用
UPDATE删除数据
mysql> UPDATE tb2 SET isDel='1' WHERE id=7;- 查看数据
在查看数据的时候，加上isDel='0'的条件，就可以实现伪删除
mysql> SELECT * FROM tb2 WHERE isDel='0';五、DQL数据查询语言
首先查看下表和表结构
mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)city表
mysql> DESC city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)country表
mysql> DESC country;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
+----------------+-------------+------+-----+---------+-------+
14 rows in set (0.01 sec)countrylanguage表
mysql> DESC countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | float(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)1.查询命令 SELECT
1.1 查询表中的所有数据
mysql> SELECT * FROM students;1.2 查询指定字段的数据
mysql> SELECT name,sex,age FROM students;1.3 根据条件查询数据
mysql> SELECT name,sex,age FROM students WHERE id=1;1.4 查询表中数据的总数
mysql> SELECT COUNT(*) FROM students;2.数据排序 ORDER BY
2.1 根据城市人口进行排序
- 升序排序
mysql> SELECT Name,Population FROM city ORDER BY Population;- 降序排序
mysql> SELECT Name,Population FROM city ORDER BY Population DESC;3.数据分页显示 LIMIT
- 按城市人口排序，人数最少的前十个城市
mysql> SELECT Name,Polulation FROM city ORDER BY Population LIMIT 10;- 指定步长为20查看数据
mysql> SELECT Name,Population FROM city LIMIT 20;
mysql> SELECT Name,Population FROM city LIMIT 20,40;
mysql> SELECT Name,Population FROM city LIMIT 40,60;4.WHERE 子句
在查询数据的时候，如需有条件的选取数据，就可将WHERE子句用到SELECT语句中。
语法：
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....操作符列表：
| 操作符 | 描述 | 实例 |
|---|---|---|
| = | 等号，检测两个值是否相等，如果相等返回true | (A = B) 返回false。 |
| <>, != | 不等于，检测两个值是否相等，如果不相等返回true | (A != B) 返回 true。 |
| > | 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
| < | 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
| >= | 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
| <= | 小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
4.1 查询中国城市人口
mysql> SELECT Name,Population FROM city WHERE Countrycode='CHN';4.2 查询中国上海市的人口
mysql> SELECT Name,Population FROM city WHERE Name='Shanghai';4.3 查找人口数量大于10000000的城市
mysql> SELECT Name,Population FROM city WHERE Population > 10000000;4.4 查看中国和美国城市人口数量
mysql> SELECT Name,Population FROM city WHERE countrycode='CHN' OR countrycode='USA';
mysql> SELECT Name,Population FROM city WHERE countrycode IN ('CHN','USA');5.LIKE 子句
like 匹配/模糊匹配，会与 % 和 _ 结合使用。
'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
'_a_' //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的5.1 查询国家代码是以C开头的城市
mysql> SELECT * FROM city WHERE CountryCode LIKE '%C';5.2 查询国家代码以N结尾的城市
mysql> SELECT * FROM city WHERE CountryCode LIKE 'C%';5.3 查询国家代码是包含H的城市
mysql> SELECT * FROM city WHERE CountryCode LIKE '%C%';5.4 查询国家代码以C为中间字符的城市
mysql> SELECT * FROM city WHERE CountryCode LIKE '_C_';6. 连接的使用
自连接 NATURAL JOIN
自动匹配多个表中相同的字段，且表中的字段和字段值相同
- 查找人口数量大于100万的城市、国家代码和语言，并根据人口数量排序
mysql> SELECT city.Name,city.CountryCode,countrylanguage.Language,city.Population
FROM city NATURAL JOIN countrylanguage WHERE Population > 1000000 ORDER BY Population;内连接 INNER JOIN
INNER JOIN：获取两个表中字段匹配关系的记录。一般会省略INNER，因为INNER JOIN等效于JOIN。
- 两表连查
查询世界上小于100人的城市是哪个国家的？
mysql> SELECT country.Name AS Country,city.Name AS City,city.Population FROM city JOIN country ON city.CountryCode=country.Code where city.Population < 100;- 三表联查
查询世界上小于100人的城市是哪个国家的，说什么语言？
mysql> SELECT country.Name AS Country,city.Name AS City,countrylanguage.Language,city.Population FROM city JOIN country ON city.CountryCode=country.Code JOIN countrylanguage ON country.Code=countrylanguage.CountryCode where city.Population < 100;==注意：==一般在FROM后的表要比JOIN后的表小
外连接
-
左连接
LEFT JOINLEFT JOIN获取左表所有记录，即使右表没有对应匹配的记录。
尝试下面实例，city为左表，country为右表，只获取左表的数据
mysql> SELECT city.Name,city.CountryCode,country.Name FROM city LEFT JOIN country on city.CountryCode=country.code and city.Population<100;- 右连接
RIGHT JOIN
RIGHT JOIN 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
city为左表，country为右表
mysql> SELECT city.Name,city.CountryCode,country.Name FROM city RIGHT JOIN country on city.CountryCode=country.code and city.Population<100;7.UNION 操作符
UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）
参数：
expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions:** 可选， 检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。UNION 实例
从city表和countrylanguage中选取所有不同的CountryCode（只有不同的值）：
mysql> SELECT CountryCode FROM city UNION SELECT CountryCode FROM countrylanguage;注意：UNION 不能用于列出两个表中所有的CountryCode，它选取的是不同的值。
UNION ALL 实例
从city表和countrylanguage中选取所有的CountryCode（也有重复的值）：
mysql> SELECT CountryCode FROM city UNION ALL SELECT CountryCode FROM countrylanguage;8.分组 GROUP BY
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
- 在city表中按国家代码（CountryCode）分组，并统计每个代码有多少条记录
mysql> SELECT CountryCode,COUNT(*) FROM city GROUP BY CountryCode;WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
- 按以上的国家代码进行分组，在统计每个代码对应人口数
mysql> SELECT CountryCode,SUM(Population) FROM city GROUP BY CountryCode WITH ROLLUP;HAVING
- 在city表中按国家代码（CountryCode）分组，统计代码重复数量大于200的记录
mysql> SELECT CountryCode,COUNT(*) AS Repetitions FROM city GROUP BY CountryCode HAVING Repetitions > 100;
+-------------+-------------+
| CountryCode | Repetitions |
+-------------+-------------+
| BRA | 250 |
| CHN | 363 |
| IND | 341 |
| JPN | 248 |
| USA | 274 |
+-------------+-------------+
5 rows in set (0.00 sec)