DDL语句

# 库命令相关
# 创建数据库
create database DB_NAME;
# 查看数据库
show databases;
# 选择数据库
use DB_NAME;
# 删除数据库
drop database DB_NAME;
# 更改数据库名(不支持)
rename OLD_DB_NAME to NEW_DB_NAME;

# 表命令相关
# 创建表
create table tb_name(col_name_1 col_type_1 constraints,col_name_2 col_type_2 constraints,...,col_name_n col_type_n constraints)
#注：mysql的表名是以目录的形式存在于磁盘上，所以表名的字符可以用任何目录名允许的字符。col_name是列名，
#col_type是数据类型，constraints是约束条件。
#示例
create table emp(ename varchar(10), hiredate date,sal decimal(10,2),deptno int(2));
# 查看数据库中的表
show tables;
# 查看表结构
desc tb_name;
show create table tb_name \G;#"\G"选项使记录能够按照字段竖着排列。
# 删除表
drop table tb_name;
# 修改表名
alter table old_tb_name rename [TO] new_tb_name;
# 修改表字段
alter table tb_name MODIFY|ADD|DROP|CHANGE [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(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
#修改列字段类型
alter table emp modify ename varchar(20);
#增加表字段
alter table emp add age int(3);
#增加到指定位置
alter table emp add birth date after ename;
#删除表字段
alter table emp drop ename;
#字段改名
alter table emp change sal salary decimal(10,2);

DML语句

+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

# 插入记录
insert into tb_name(field1,field2,...,fieldn) values(value1,value2,...,valuen);
#　指定字段名
insert into emp(ename,hiredate,sal,deptno) values(‘Long‘,‘2020-07-11‘,5000,1);
# 不指定字段名，但是values的顺序必须和表中的字段排列顺序一致
insert into emp values(‘Ming‘, ‘2020-07-11‘,5050, 2);
# 对于含可空字段、非空但是含有默认值的字段、自增字段，可以不用再insert后的字段列表里面出现，
# values后面直接跟对应字段的名称value
insert into emp (ename,sal) values(‘Ting‘, 5000);
# 批量插入(Mysql特性)
insert into tb_name (field1,field2,...,fieldn) values
(record1_value1,record1_value2,...,record1_valuen),
(record2_value1,record2_value2,...,record2_valuen),
...
(recordn_value1,recordn_value2,...,recordn_valuen);

# 更新记录
update tb_name set field1=value1,field2=value2,...,fieldn =valuen [WHERE CONDITION];

# 删除记录
delete from tb_name [WHERE CONDITION];

#　查询记录
# [WHERE CONDITION]条件查询，where后面的条件可以使用比较运算符(>,<,>=,<=,!=...)，
# 多个条件之间可以用and、or等逻辑运算符进行多条件联合查询
select * from tb_name [WHERE CONDITION];
select field1,field2,..,fieldn from tb_name [WHERE CONDITION];
# 查询不重复记录[去重] distinct
select distinct field from tb_name [WHERE CONDITION];
# 排序和限制
# ORDER BY后跟排序字段，可以跟多个；DESC|ASC是排序关键字，不写此关键字默认是升序排列。
# 如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序，以此类推。如果只有一个
# 排序字段，则这些字段相同的记录会无序排列。
select * from tb_name [WHERE CONDITION] [ORDER BY field1 [DESC|ASC],field2 [DESC|ASC],...fieldn [DESC|ASC]]
# 限制或分页查询
# offset_start表示记录的起始偏移量，row_count表示显示的行数
# 默认情况下，起始偏移量为0，只需要写记录行数就可以，这时候显示的实际就是前n条记录
select ...[LIMIT offset_start,row_count]
# 聚合
# 很多情况下，我们需要进行一些汇总操作，比如统计整个公司的人数或者统计每个部门的人数，
# 这时候就需要用到SQL的聚合操作。
select [field1,field2,...fieldn] fun_name
from tb_name
[WHERE where_condition]
[GROUP BY field1,field2,...fieldn]
[WITH ROLLUP]
[HAVING where_condition]
# 参数说明
# fun_name 表示要做的聚合操作，也就是聚合函数，常用的有sum、count(*)、max、min。
# GROUP BY关键字表示要进行分类聚合的字段。
# WITH ROLLUP是可选语法，表示是否对分类聚合后的结果进行在汇总。
# HAVING关键字表示对分类后的结果在进行条件的过滤。
#注意：having是对聚合后的记过进行条件的过滤，where是在聚合之前就对记录进行过滤。
#如果逻辑允许，我们尽可能用where先过滤记录，之后再根据螺距看是否用having再过滤。
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| Long  | 2020-07-11 | 6001.00 |      1 |
| Xiu   | 2020-07-11 | 5001.00 |      2 |
| Dong  | NULL       | 2000.00 |   NULL |
| Ting  | 2020-07-10 | 3000.00 |      5 |
| Lin   | 2020-07-10 | 3000.00 |      1 |
| Bi    | 2020-07-10 | 3200.00 |      1 |
| Au    | 2020-07-10 | 3300.00 |      2 |
+-------+------------+---------+--------+
# with rollup表示对聚合后的结果求总数
select deptno,count(1) number from emp group by deptno with rollup;
+--------+--------+
| deptno | number |
+--------+--------+
|   NULL |      1 |
|      1 |      3 |
|      2 |      2 |
|      5 |      1 |
|   NULL |      7 |
+--------+--------+
# 统计人数大于1的部门
mysql> select deptno,count(1) from emp group by deptno having count(1) > 1; 
+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        3 |
|      2 |        2 |
+--------+----------+
# 统计员工薪水
mysql> select max(sal),min(sal),avg(sal) from emp;
+----------+----------+-------------+
| max(sal) | min(sal) | avg(sal)    |
+----------+----------+-------------+
|  6001.00 |  2000.00 | 3643.142857 |
+----------+----------+-------------+
# 按部门号排序[默认asc]
mysql> select * from emp order by deptno;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| Dong  | NULL       | 2000.00 |   NULL |
| Long  | 2020-07-11 | 6001.00 |      1 |
| Lin   | 2020-07-10 | 3000.00 |      1 |
| Bi    | 2020-07-10 | 3200.00 |      1 |
| Xiu   | 2020-07-11 | 5001.00 |      2 |
| Au    | 2020-07-10 | 3300.00 |      2 |
| Ting  | 2020-07-10 | 3000.00 |      5 |
+-------+------------+---------+--------+
# 按部门号排序，部门号相同的则按sal降序排序
mysql> select * from emp order by deptno, sal desc;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| Dong  | NULL       | 2000.00 |   NULL |
| Long  | 2020-07-11 | 6001.00 |      1 |
| Bi    | 2020-07-10 | 3200.00 |      1 |
| Lin   | 2020-07-10 | 3000.00 |      1 |
| Xiu   | 2020-07-11 | 5001.00 |      2 |
| Au    | 2020-07-10 | 3300.00 |      2 |
| Ting  | 2020-07-10 | 3000.00 |      5 |
+-------+------------+---------+--------+
# 连接查询
# 表连接分为内连接和外连接，他们之间的主要区别是内连接仅选出两张表中互相匹配的记录，
# 而外连接会选出其他不匹配的记录。常用内连接。
mysql> select * from dept; 
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
mysql> select ename,deptname from emp,dept where emp.deptno = dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| Long  | tech     |
| Xiu   | sale     |
| Lin   | tech     |
| Bi    | tech     |
| Au    | sale     |
+-------+----------+
# 外连接又分为左外连接和右外连接
# 左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
# 右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
mysql> select ename,deptname from emp left join dept on emp.deptno = dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| Long  | tech     |
| Lin   | tech     |
| Bi    | tech     |
| Xiu   | sale     |
| Au    | sale     |
| Dong  | NULL     |
| Ting  | NULL     |
+-------+----------+
mysql> select deptname,ename from emp right join dept on emp.deptno = dept.deptno;
+----------+-------+
| deptname | ename |
+----------+-------+
| tech     | Long  |
| sale     | Xiu   |
| tech     | Lin   |
| tech     | Bi    |
| sale     | Au    |
| hr       | NULL  |
+----------+-------+
# 子查询：查询的条件是另外一个select语句的结果
# 子查询的关键字包括：in、not in、=、!=、exists、not exists等
mysql> select * from emp where deptno in (select deptno from dept);
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| Long  | 2020-07-11 | 6001.00 |      1 |
| Xiu   | 2020-07-11 | 5001.00 |      2 |
| Lin   | 2020-07-10 | 3000.00 |      1 |
| Bi    | 2020-07-10 | 3200.00 |      1 |
| Au    | 2020-07-10 | 3300.00 |      2 |
+-------+------------+---------+--------+
# 如果子查询记录数唯一，可以用=代替in
# 某些情况下，子查询可以转化为表连接

# 记录联合（不常用）
# 将两表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来。就需要使用union和union all关键字。
# UNION ALL：将所有结果集直接合并在一起
# UNION：将UNION ALL后的结果进行一次DISTINCT，去除重复记录后的结果
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 |
+--------+
|      1 |
|      2 |
|   NULL |
|      5 |
|      1 |
|      1 |
|      2 |
|      1 |
|      2 |
|      3 |
+--------+
10 rows in set (0.00 sec)

mysql> select deptno from emp union select deptno from dept;                  
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|   NULL |
|      5 |
|      3 |
+--------+

DCL语句

# 创建用户 
# ip字段：(1)localhost (2)公网ip(指定ip段的话使用‘%‘) (3)% 所有ip,username不加引号可以，password必须加引号
create user ‘username‘@‘ip‘ identified by ‘password‘;
# 删除用户
drop user username @‘ip‘；
# 修改用户密码
alter user ‘username‘@‘ip‘ identified by ‘new_password‘;
# 刷新权限，否则可能会导致更改密码无效
flush privileges;
# 利用DML更改用户密码
use mysql;
update user set authentication_string=PASSWORD(‘new_password‘) where user=‘用户名‘;
flush privileges;//刷新权限，否则可能会导致更改密码无效 

# 用户授权
grant pri1,pri2,...prin on {DB_NAME.*|*.*} to username @‘ip‘;
# 授权的同时创建用户
grant pri1,pri2,...prin on {DB_NAME.*|*.*} to username @‘ip‘ identified by ‘password‘;
#注：支持通配符，‘*’代表所有；授予所有的权限可用 all 或 all privileges
# 单用户授予多库权限，多个grant分开授予
# 撤销权限
revoke pri1,pri2...prin on {DB_NAME.*|*.*} from username@‘ip‘
# 查看用户权限
show grants for username@‘ip‘;

# 总结：对用户相关的权限、密码、配置等信息的都是修改的mysql下的user表，所以说，我们通过DCL创建用户，实际就是想mysql.user下插入了一条记录，换言之，我们也可以通过DML直接向表中插入，这是可以的，更改用户密码也可以通过update等操作实现，但通常我们不这样做。而是通过DCL来操作。因为DBA控制着DB的运转。

# 下面进行实操 云服务器
mysql> use mysql;
Database changed
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
# 创建远端登录用户(指定IP)，并授予权限【也可以设置IP段 112.38.%.%】
mysql> grant all on test.* to stronger@‘112.38.54.240‘ identified by ‘123‘;
mysql> select user,host from user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| stronger      | 112.38.54.240 |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
# 防止创建不能及时生效
mysql> flush privileges;
# 本地登录测试
/user/dos>mysql -ustronger -h39.97.181.76 -p
/user/dos>Enter password: ***
# 上述操作报错的解决方案（可参考）
# whereis my.cnf 定位到my.cnf所在文件，查看my.cnf是否有bind-address=127.0.0.1,将其注释或删去或者改为bind-address=0.0.0.0。

帮助的使用

mysql命令和虽不及linux命令多，但是全部都记住是...，在而mysql上使用帮助，不单单是查看这个命令怎么用，也可以查看当前版本下的mysql支持哪些函数和功能或者在该版本上某些命令是否还支持。

# 显示可供查询的分类，然后可以继续？选择某个分类查看
mysql> ? contents

# 关键字快速查询
mysql> ? keyword
# 查询create命令的相关使用
mysql> ? create
Many help items for your request exist.
To make a more specific request, please type ‘help <item>‘,
where <item> is one of the following
topics:
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   CREATE FUNCTION UDF
   CREATE INDEX
   CREATE LOGFILE GROUP
   CREATE PROCEDURE
   CREATE SERVER
   CREATE TABLE
   CREATE TABLESPACE
   CREATE TRIGGER
   CREATE USER
   CREATE VIEW
   SHOW
   SHOW CREATE DATABASE
   SHOW CREATE EVENT
   SHOW CREATE FUNCTION
   SHOW CREATE PROCEDURE
   SHOW CREATE TABLE
   SPATIAL

2. SQL基础