## MySQL专题二：[DDL与DML语法](https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/index.html)
[TOC]
### 2.1. 操作数据库
> 新增数据库
```bash
mysql> create database menagerie;
Query OK, 1 row affected (0.12 sec)
```
> 使用数据库
```bash
mysql> use menagerie;
Database changed
```
> 删除数据库
```bash
mysql> drop database menagerie;
Query OK, 0 rows affected (0.41 sec)
```
### 2.2. 操作表
显示数据库中所有表
```bash
mysql> show tables;
Empty set (0.13 sec)
```
> 创建表
```bash
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.47 sec)
```
显示数据库中所有表
```bash
mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| pet |
+---------------------+
1 row in set (0.05 sec)
```
显示表的信息
```bash
mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.13 sec)
```
### 2.3. 数据插入（INSERT）
~~~
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
~~~
>插入数据
```bash
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.18 sec)
```
### 2.4. 数据查询（SELECT）
~~~
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
~~~
>选择所有行列
```bash
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | f | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | m | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | NULL | 1997-12-09 |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)
```
>条件选择某些行（WHERE 语法）
WHERE condition1 [AND [OR]] condition2.....
| 操作符 | 说明 |
| --- | --- |
| = | 两个值相等返回1，否则返回0；只要有一个值为NULL，返回NULL |
| <=>| 两个值相等返回1，否则返回0；2个都为NULL，返回1；只有一个为NULL，返回0|
| <> | 两个值不相等返回1，否则返回0；只要有一个值为NULL，返回NULL |
| != | 两个值不相等返回1，否则返回0；只要有一个值为NULL，返回NULL |
| < | 左边值小于右边值返回1，否则返回0；只要有一个值为NULL，返回NULL |
| > | 左边值大于右边返回1，否则返回0；只要有一个值为NULL，返回NULL |
| <= | 左边值小于或等于右边值返回1，否则返回0；只要有一个值为NULL，返回NULL |
| >= | 左边值大于或等于右边值返回1，否则返回0；只要有一个值为NULL，返回NULL |
```bash
mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)
```
AND用法
```bash
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | f | 1990-08-27 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
```
OR用法
```bash
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Chirpy | Gwen | bird | m | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | NULL | 1997-12-09 |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
```
>选择某些列
```bash
mysql> SELECT name, birth FROM pet;
+----------+------------+
| name | birth |
+----------+------------+
| Puffball | 1999-03-30 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1979-08-31 |
| Chirpy | 1998-09-11 |
| Whistler | NULL |
| Slim | 1996-04-29 |
+----------+------------+
9 rows in set (0.00 sec)
```
>选择某些列和某些行
```bash
mysql> SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1979-08-31 |
+--------+---------+------------+
5 rows in set (0.00 sec)
```
>排序选择（ORDER BY 语法）
单个字段升序排序(默认状态)
```bash
mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name | birth |
+----------+------------+
| Whistler | NULL |
| Bowser | 1979-08-31 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.02 sec)
```
单个字段降序排序
```bash
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name | birth |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy | 1998-09-11 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Buffy | 1989-05-13 |
| Bowser | 1979-08-31 |
| Whistler | NULL |
+----------+------------+
9 rows in set (0.06 sec)
```
多个字段排序（例: 先按species升序排序，再按birth降序排序）
```bash
mysql> SELECT name, species, birth FROM pet
-> ORDER BY species, birth DESC;
+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | NULL |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Buffy | dog | 1989-05-13 |
| Bowser | dog | 1979-08-31 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+
9 rows in set (0.03 sec)
```
>分组选择（GROUP BY 语法）
统计男女的人数
```bash
mysql> select sex, count(*) from pet group by sex;
+------+----------+
| sex | count(*) |
+------+----------+
| f | 3 |
| m | 4 |
| NULL | 1 |
+------+----------+
3 rows in set (0.09 sec)
```
>NULL的处理
```bash
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
1 row in set (0.00 sec)
```
```bash
mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
| 0 | 1 | 0 | 1 |
+-----------+---------------+------------+----------------+
1 row in set (0.00 sec)
```
>计数（COUNT 语法）
```bash
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
1 row in set (0.06 sec)
```
```bash
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
1 row in set (0.06 sec)
```
>模糊匹配（LIKE 语法）
%代表一个或多个字符
```bash
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.03 sec)
```
_代表一个字符
```bash
mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
```
>正则表达式匹配（REGEXP_LIKE语法）
匹配以b开头的name的所有记录
```bash
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.10 sec)
```
### 2.5. 数据更新（UPDATE）
~~~
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
~~~
```bash
mysql> select * from pet where name = 'Puffball'
-> ;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
1 row in set (0.03 sec)
mysql> select * from pet where name = 'Puffball';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> update pet set sex='m' where name = 'Puffball';
Query OK, 1 row affected (0.20 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pet where name = 'Puffball';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | m | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
```
### 2.6. 数据删除（DELETE）
~~~
DELETE FROM table_name [WHERE Clause]
~~~
```bash
mysql> select * from pet where name = 'Puffball';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | m | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> delete from pet where name = 'Puffball';
Query OK, 1 row affected (0.11 sec)
mysql> select * from pet where name = 'Puffball';
Empty set (0.00 sec)
```
- JavaCook
- Java专题零：类的继承
- Java专题一：数据类型
- Java专题二：相等与比较
- Java专题三：集合
- Java专题四：异常
- Java专题五：遍历与迭代
- Java专题六：运算符
- Java专题七：正则表达式
- Java专题八：泛型
- Java专题九：反射
- Java专题九（1）：反射
- Java专题九（2）：动态代理
- Java专题十：日期与时间
- Java专题十一：IO与NIO
- Java专题十一（1）：IO
- Java专题十一（2）：NIO
- Java专题十二：网络
- Java专题十三：并发编程
- Java专题十三（1）：线程与线程池
- Java专题十三（2）：线程安全与同步
- Java专题十三（3）：内存模型、volatile、ThreadLocal
- Java专题十四：JDBC
- Java专题十五：日志
- Java专题十六：定时任务
- Java专题十七：JavaMail
- Java专题十八：注解
- Java专题十九：浅拷贝与深拷贝
- Java专题二十：设计模式
- Java专题二十一：序列化与反序列化
- 附加专题一：MySQL
- MySQL专题零：简介
- MySQL专题一：安装与连接
- MySQL专题二：DDL与DML语法
- MySQL专题三：工作原理
- MySQL专题四：InnoDB存储引擎
- MySQL专题五：sql优化
- MySQL专题六：数据类型
- 附加专题二：Mybatis
- Mybatis专题零：简介
- Mybatis专题一：配置文件
- Mybatis专题二：映射文件
- Mybatis专题三：动态SQL
- Mybatis专题四：源码解析
- 附加专题三：Web编程
- Web专题零：HTTP协议
- Web专题一：Servlet
- Web专题二：Cookie与Session
- 附加专题四：Redis
- Redis专题一：数据类型
- Redis专题二：事务
- Redis专题三：key的过期
- Redis专题四：消息队列
- Redis专题五：持久化