MySQL快速入门——SQL基础DDL（Data Definition Languages）语句：数据定义语言，这些语句

启动和关闭MySQL服务

启动服务

net start mysql
关闭服务

net stop mysql

SQL分类

DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter 等。
DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和 select 等。
DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

DDL语言

创建数据库

语法：CREATE DATABASE dbname

例：create database test1;

查看已有数据库

show databases;
选择要操作的数据库

语法：USE dbname

例：use test1
显示所有数据表 show tables;
删除数据库 drop database dbname; 例：drop database test1;

创建表语法：CREATE TABLE tablename (column_name_1 column_type_1 constraints， column_name_2 column_type_2 constraints ， ……column_name_n column_type_n constraints）column_name 是列的名字，column_type 是列的数据类型，contraints 是这个列的约束条件

mysql> use test1;
Database changed
mysql> create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
Query OK, 0 rows affected, 1 warning (0.63 sec)

mysql> desc emp;   //查看表定义
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table emp \G;  //详细查看表定义，\G竖向排列
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

修改表修改表类型语法：ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] 示例：

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

增加表字段语法： ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]

示例：

mysql> alter table emp add column age int(3);
Query OK, 0 rows affected, 1 warning (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除表字段语法：ALTER TABLE tablename DROP [COLUMN] col_name 示例：

mysql> alter table emp drop column age;
Query OK, 0 rows affected (0.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

字段改名语法： ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]

示例：

mysql> alter table emp change ename ename1 varchar(20);
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename1   | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改字段排列顺序:前面介绍的的字段增加和修改语法（ADD/CNAHGE/MODIFY）中，都有一个可选项 first|after column_name，这个选项可以用来修改字段在表中的位置，默认 ADD 增加的新字段是加在表的最后位置，而 CHANGE/MODIFY 默认都不会改变字段的位置。

示例：

mysql> alter table emp add age int(3) after ename;
Query OK, 0 rows affected, 1 warning (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
mysql> alter table emp add age1 int(3) first;
Query OK, 0 rows affected, 1 warning (0.72 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age1     | int           | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table emp modify deptno int(3) first;
Query OK, 0 rows affected, 1 warning (0.63 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| deptno   | int           | YES  |     | NULL    |       |
| age1     | int           | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

表改名语法：ALTER TABLE tablename RENAME [TO] new_tablename

mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.50 sec)

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

DML语句

插入记录 INSERT INTO tablename (field1,field2,……fieldn) VALUES(value1,value2,……valuesn); 示例：

mysql> insert into emp (ename,hiredate,sal,deptno) values('vba','2020-1-1','10000',2);
Query OK, 1 row affected (0.41 sec)

mysql> select * from emp;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 10000.00 |      2 |
+-------+------------+----------+--------+
1 row in set (0.00 sec)

mysql> insert into emp values('java','2020-2-2','15000',1);  //需保持顺序一致
Query OK, 1 row affected (0.40 sec)

mysql> insert into emp (ename,sal) values('php','12000'); //部分插入，其余为空
Query OK, 1 row affected (0.43 sec)

mysql> select * from emp; //显示整表
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 10000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | NULL       | 12000.00 |   NULL |
+-------+------------+----------+--------+
3 rows in set (0.00 sec)

一次性插入多条语句：

INSERT INTO tablename (field1, field2,……fieldn) VALUES (record1_value1, record1_value2,……record1_valuesn), (record2_value1, record2_value2,……record2_valuesn), …… (recordn_value1, recordn_value2,……recordn_valuesn) ;

mysql> insert into emp values('java','2020-2-2','15000',1),('java','2020-2-2','15000',1),('java','2020-2-2','15000',1);
Query OK, 3 rows affected (0.41 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 10000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | NULL       | 12000.00 |   NULL |
| java  | 2020-02-02 | 15000.00 |      1 |
| java  | 2020-02-02 | 15000.00 |      1 |
| java  | 2020-02-02 | 15000.00 |      1 |
+-------+------------+----------+--------+
6 rows in set (0.00 sec)

更新记录

UPDATE tablename SET field1=value1，field2.=value2，……fieldn=valuen [WHERE CONDITION]

mysql> update emp set hiredate='2020-1-2',deptno='3' where ename='php';
Query OK, 1 row affected (0.45 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 10000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | 2020-01-02 | 12000.00 |      3 |
| java  | 2020-02-02 | 15000.00 |      1 |
| java  | 2020-02-02 | 15000.00 |      1 |
| java  | 2020-02-02 | 15000.00 |      1 |
+-------+------------+----------+--------+
6 rows in set (0.00 sec)

多表更新：UPDATE t1,t2…tn set t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]

mysql> select * from emp;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 10000.00 |      2 |
| php   | 2020-01-02 | 12000.00 |      3 |
| java  | 2020-02-02 | 15000.00 |      1 |
+-------+------------+----------+--------+
3 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tobbaco  |
|      2 | gg       |
|      3 | dt       |
+--------+----------+
3 rows in set (0.00 sec)

mysql> update emp a,dept b set a.sal=b.deptno*a.sal,b.deptname=a.deptno where a.deptno=b.deptno;
Query OK, 5 rows affected (0.43 sec)
Rows matched: 6  Changed: 5  Warnings: 0

mysql> select * from emp;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
| php   | 2020-01-02 | 36000.00 |      3 |
| java  | 2020-02-02 | 15000.00 |      1 |
+-------+------------+----------+--------+
3 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | 1        |
|      2 | 2        |
|      3 | 3        |
+--------+----------+
3 rows in set (0.00 sec)
mysql> update emp a,dept b set b.deptname=a.ename where b.deptno=a.deptno;
Query OK, 3 rows affected (0.45 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | java     |
|      2 | vba      |
|      3 | php      |
+--------+----------+
3 rows in set (0.00 sec)

删除记录

DELETE FROM tablename [WHERE CONDITION]

多表删除DELETE t1,t2…tn FROM t1,t2…tn [WHERE CONDITION]

mysql>  delete a,b from emp a,dept b where a.deptno=b.deptno and b.deptno=3;
Query OK, 2 rows affected (0.42 sec)

mysql> select * from emp;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
+-------+------------+----------+--------+
2 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | java     |
|      2 | vba      |
+--------+----------+
2 rows in set (0.00 sec)

查询记录

SELECT * FROM tablename [WHERE CONDITION]

例：

mysql> select * from emp;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | 2020-01-03 | 10000.00 |      3 |
+-------+------------+----------+--------+
3 rows in set (0.00 sec)

mysql> select sal,hiredate from emp;//查询部分列
+----------+------------+
| sal      | hiredate   |
+----------+------------+
| 20000.00 | 2020-01-01 |
| 15000.00 | 2020-02-02 |
| 10000.00 | 2020-01-03 |
+----------+------------+
3 rows in set (0.00 sec)

查询不重复记录，在列字段名前加distinct关键词：

mysql> select distinct * from emp;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | 2020-01-03 | 10000.00 |      3 |
+-------+------------+----------+--------+
3 rows in set (0.00 sec)

mysql> select distinct deptno,sal from emp;
+--------+----------+
| deptno | sal      |
+--------+----------+
|      2 | 20000.00 |
|      1 | 15000.00 |
|      3 | 10000.00 |
+--------+----------+
3 rows in set (0.00 sec)

条件查询where关键词实现：

mysql> select * from emp where deptno>1;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
| php   | 2020-01-03 | 10000.00 |      3 |
+-------+------------+----------+--------+
2 rows in set (0.00 sec)

mysql> select * from emp where deptno>1 and deptno<3;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
+-------+------------+----------+--------+
1 row in set (0.00 sec)

排序和限制

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC]，field2 [DESC|ASC]，……fieldn [DESC|ASC]]

DESC 和 ASC 是排序顺序关键字，DESC 表示按照字段进行降序排列，ASC 则表示升序排列，如果不写此关键字默认是升序排列。

mysql> select * from emp order by deptno;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| java  | 2020-02-02 | 15000.00 |      1 |
| vba   | 2020-01-01 | 20000.00 |      2 |
| php   | 2020-01-03 | 10000.00 |      3 |
+-------+------------+----------+--------+
3 rows in set (0.00 sec)

mysql> select * from emp order by sal desc;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | 2020-01-03 | 10000.00 |      3 |
+-------+------------+----------+--------+
3 rows in set (0.00 sec)

对于排序后的记录，如果希望只显示一部分，而不是全部，这时，就可以使用 LIMIT 关键字来实现，LIMIT 的语法如下： SELECT ……[LIMIT offset_start,row_count]其中 offset_start 表示记录的起始偏移量，row_count 表示显示的行数。

mysql> select * from emp order by deptno limit 2;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| java  | 2020-02-02 | 15000.00 |      1 |
| vba   | 2020-01-01 | 20000.00 |      2 |
+-------+------------+----------+--------+
2 rows in set (0.00 sec)

mysql> select * from emp order by sal desc limit 1,2;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | 2020-01-03 | 10000.00 |      3 |
+-------+------------+----------+--------+
2 rows in set (0.00 sec)

聚合查询

SELECT [field1,field2,……fieldn] fun_name FROM tablename [WHERE where_contition] [GROUP BY field1,field2,……fieldn [WITH ROLLUP]] [HAVING where_contition]

fun_name 表示要做的聚合操作，也就是聚合函数，常用的有 sum（求和）、count(*)（记录数）、max（最大值）、min（最小值）。
GROUP BY 关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在 group by 后面。
WITH ROLLUP 是可选语法，表明是否对分类聚合后的结果进行再汇总。
HAVING 关键字表示对分类后的结果再进行条件的过滤。

mysql> select * from emp;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | 2020-01-03 | 10000.00 |      3 |
| go    | 2020-01-01 | 10000.00 |      3 |
+-------+------------+----------+--------+
4 rows in set (0.00 sec)

mysql> select count(1) from emp; //统计总记录数
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> select deptno,count(1) from emp group by deptno;//统计由deptno聚合后的各分项数量
+--------+----------+
| deptno | count(1) |
+--------+----------+
|      2 |        1 |
|      1 |        1 |
|      3 |        2 |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select deptno,count(1) from emp group by deptno with rollup; //聚合数量+总数量
+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        1 |
|      2 |        1 |
|      3 |        2 |
|   NULL |        4 |
+--------+----------+
4 rows in set (0.00 sec)

mysql> select deptno,count(1) from emp group by deptno having count(1)>1; //聚合后再条件查询
+--------+----------+
| deptno | count(1) |
+--------+----------+
|      3 |        2 |
+--------+----------+
1 row in set (0.00 sec)

mysql> select sum(sal),max(sal),min(sal) from emp;//统计
+----------+----------+----------+
| sum(sal) | max(sal) | min(sal) |
+----------+----------+----------+
| 55000.00 | 20000.00 | 10000.00 |
+----------+----------+----------+
1 row in set (0.00 sec)

表连接

表连接分为内连接和外连接，它们之间的最主要区别是內连接仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录。

mysql> select * from emp;
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | 2020-01-03 | 10000.00 |      3 |
+-------+------------+----------+--------+
3 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select ename,deptname from emp,dept where emp.deptno=dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| java  | tech     |
| vba   | sale     |
| php   | hr       |
+-------+----------+
3 rows in set (0.00 sec)

左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录

mysql> select ename,deptname from emp left join dept on emp.deptno=dept.deptno; //左连接
+-------+----------+
| ename | deptname |
+-------+----------+
| vba   | sale     |
| java  | tech     |
| php   | hr       |
| go    | sale     |
+-------+----------+
4 rows in set (0.00 sec)

mysql> select ename,deptname from dept right join emp on dept.deptno=emp.deptno;//右连接，等价于上左连接
+-------+----------+
| ename | deptname |
+-------+----------+
| vba   | sale     |
| java  | tech     |
| php   | hr       |
| go    | sale     |
+-------+----------+
4 rows in set (0.00 sec)
mysql>select emp.*,dept.deptname from emp left join dept on dept.deptno=emp.deptno;//全表连接
+-------+------------+----------+--------+----------+
| ename | hiredate   | sal      | deptno | deptname |
+-------+------------+----------+--------+----------+
| vba   | 2020-01-01 | 20000.00 |      2 | sale     |
| java  | 2020-02-02 | 15000.00 |      1 | tech     |
| php   | 2020-01-03 | 10000.00 |      3 | hr       |
| go    | 2020-01-03 |  8000.00 |      2 | sale     |
+-------+------------+----------+--------+----------+
4 rows in set (0.00 sec)

子查询

某些情况下，当我们查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。用于子查询的关键字主要包括 in、not in、=、!=、exists、not exists 等。

mysql> select * from emp where deptno in(select deptno from dept);
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | 2020-01-03 | 10000.00 |      3 |
| go    | 2020-01-03 |  8000.00 |      2 |
+-------+------------+----------+--------+
4 rows in set (0.00 sec)

mysql> select emp.* from emp ,dept where emp.deptno=dept.deptno;  //等价于表连接
+-------+------------+----------+--------+
| ename | hiredate   | sal      | deptno |
+-------+------------+----------+--------+
| vba   | 2020-01-01 | 20000.00 |      2 |
| java  | 2020-02-02 | 15000.00 |      1 |
| php   | 2020-01-03 | 10000.00 |      3 |
| go    | 2020-01-03 |  8000.00 |      2 |
+-------+------------+----------+--------+
4 rows in set (0.00 sec)

记录联合

SELECT * FROM t1 UNION|UNION ALL SELECT * FROM t2 …… UNION|UNION ALL SELECT * FROM tn;

mysql> select deptno from emp
    -> union all
    -> select deptno from dept;
+--------+
| deptno |
+--------+
|      2 |
|      1 |
|      3 |
|      2 |
|      1 |
|      2 |
|      3 |
+--------+
7 rows in set (0.00 sec)
mysql> select deptno from emp
    -> union
    -> select deptno from dept;
+--------+
| deptno |
+--------+
|      2 |
|      1 |
|      3 |
+--------+
3 rows in set (0.00 sec)

DCL语句

DCL 语句主要是 DBA 用来管理系统中的对象权限时所使用，最常用的是grant 和 revoke 分别授出和收回了用户权限。

授予权限grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';

收回权限revoke insert on sakila.* from 'z1'@'localhost';

查看帮助

“？contents”命令来显示所有可供查询的的分类：

mysql> ? contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Components
   Compound Statements
   Contents
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

进一步查询数据类型：

mysql> ? data types
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
  ......
   YEAR DATA TYPE

mysql> ? int  //进一步查询int
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

URL: https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html


mysql> help int  //?等价于help
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]
......