1. SQL常用种类
2. 数据库逻辑结构
3. 字符集
4. 排序规则
5. 数据类型
6. DDL 数据库定义语言
- 6.1 库的定义
创建一个库名为 test 字符集为 utf8mb4 大小写敏感
- 6.2 表的定义
7. DCL 数据库控制语言
8. DML 数据库操作语言
9. DQL 数据库查询语言
- 9.1 select基础语法及单表查询
- 9.2 多表查询
  - 9.2.1 内连接
10. show命令的大致用法

免登陆执行SQL ```sql mysql -uroot -p123qwe -e “select user,host from mysql.user”

通过 -e指令可以实现不登录的情况下使用SQL命令。


2. 导入数据
```sql
mysql -uroot -p123qwe < /root/date.sql

SQL的简单内置命令

help 打印帮助
\c 或者 ctrl+c 取消本条命令执行
\G 代替分号结尾，用于特别长的显示，会改成竖着显示的形式。
例子： select * from mysql.user\G
     select * from mysql.user;    
source date.sql #加载数据

1. SQL常用种类

DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据的查询语言

2. 数据库逻辑结构

库
库名
库属性：字符集，排序规则
表
表名
表属性：存储引擎类型，字符集，排序规则
列名
列属性：数据类型约束其他属性
数据行

3. 字符集

相当于一个密码本。用A密码本存放的数据，需要使用A密码本才能正常读取。
5.7默认使用的拉丁语.
查看支持的字符集： show charset;
常用的是：

utf8： 3个字节
utf8mb4(建议)： 4个字节，支持emoji。 8.0的默认字符集

4. 排序规则

show collation; 查看当前支持的排序规则

utf8mb4_general_ci  大小写不敏感
utf8mb4_bin     大小写敏感

5. 数据类型

5.1 数字

整数：tinyint：极小数据类型(0-255)
整数：int：存储10位的类型
超过十位的数字类型，使用字符串

5.2 字符串

chare(100):    定长的字符串类型；定义一个字符串的长度。 所占用的磁盘空间是固定的。不管存储的字符串多长，都立即分配100个长度的字符串空间， 未沾满的空间，使用空格来填充。   空格 不代表空。最大长度为255
varchare(100):  变“长” 字符串类型。 每次存储前，都会判断字符串的长度，按需分配磁盘空间。会单独申请一个字符长度的空间，用于存储字符长度（当字符串长度少于255的时候，需要一个字符空间，多于则需要两个字符空间）。 最大长度为65535 实际最大为65533
enum 枚举类型：可以提前定义相应的数据内容，而后通过字符内容对应的数字编号(下标索引)来引用。基于定值类型存储且为字符串类型.  在插入数据时，可以直接使用下标索引

选型

字符长度 是否固定，且是否定长
考虑到索引性能。
单独考虑性能， chare 大于varchare

5.3 时间

DATETIME：
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP： 
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

二进制
略

6. DDL 数据库定义语言

针对库表进行操作；

6.1 库的定义

```sql create database test charset utf8mb4 collate utf8mb4_bin;

创建一个库名为 test 字符集为 utf8mb4 大小写敏感

查看建库语句： show create database test;

```sql
drop database test;

有个注意点。 从小往大改。 因为字符占位的缘故。
create database test1 charset utf8 collate utf8_bin;
alter database test1 charset utf8mb4 collate utf8mb4_bin;
show create database test1;

定义库的规范

库名不能有大写字母   
建库要加字符集         
库名不能有数字开头
 库名要和业务相关

6.2 表的定义

use 库；
create table stu(
列1  属性（数据类型、约束、其他属性） ，
列2  属性，
列3  属性
)

PRIMARY KEY ：主键约束。每个表中只能有一个， 非空，且需要唯一。
NOT NULL ： 非空约束，不允许出现空值
UNIQUE KEY: 唯一键约束，不允许出现重复值
DEFAULT： 设定默认值， 一般和not null 一起用
UNSIGNED: 无符号，一般是数字列，表示不能是负数
COMMENT： 注释， 用于解释字段的含义
AUTO_INCREMENT: 自增长的列。

创建一个表

create database student charset utf8 collate utf8_bin;
use student；
CREATE TABLE     student (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '人员号',
name VARCHAR(255) NOT NULL COMMENT '名字',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT "年龄",
gender ENUM('m','f','n') NOT NULL DEFAULT 'n'  COMMENT '性别',
intiem DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE INNODB CHARSET utf8mb4;

MySQL [student]>show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

定义表的规范

表名小写
不能是数字开头
注意字符集和存储引擎
表名和业务有关
选择合适的数据类型
每个列都要有注释
每个列设置为非空，无法保证非空，用0来填充。

建表语句的查看

show create table student;

查看表结构

desc student;

创建一个结构一样的表；复制表结构

create table test like student;

删除表；

drop table test;

修改表结构;
类似语句，会锁表，因此尽量不要在高峰期使用，且数据量越大锁表时间越长；
解决办法：

复制一个结构一样的表。替换掉当前使用的表。 通过表名的替换；
PT-OSC 工具可以直接完成该操作
https://www.jianshu.com/p/c739d12afbef

alter table student add qq varchar(20) not null unique comment 'QQ账号';

修改表属性;

alter table student modify name varchar(64) not null comment '姓名';
需要把相关的其他信息也都重新写一遍，默认是会覆盖的原来的属性的；

修改列名；

alter table student  change gender sex char(4) not     null comment '性别';

删除列；

alter table student drop qq;

了解业务结构

和开发沟通
ER图(用于多表间存在复杂关系的情况下)
desc , show create table
select * from city limit 5;

7. DCL 数据库控制语言

总体就两个
grant 授权
grant all on *.* to test@'10.0.0.1' identified by '123qwe';
revoke 删除授权
revoke drop on *.* from test@'10.0.0.1';

8. DML 数据库操作语言

就是对表数据的操作。增删改查
insert 插入数据

insert into  student(name,age) values  ('peanut',23);
value的数字可以直接写，而字符串需要引号

insert into  student(name,age) values  ('peanut',23),('root',22);

delete from student where name = 'root';

9. DQL 数据库查询语言

select 
show

9.1 select基础语法及单表查询

9.1.1 select 的单独应用

select可以通过@@加参数ID 直接查询定义好的基础参数；
select @@port;
select @@basedir;
select @@server-id;

select databases(); # 查看当前所在的库
select now(); #查看当前的时间

9.1.2 select的通用语法(单表)

select  字段1,字段2 from 表
where 过滤，
group by 分组条件，
having  过滤，
limit n; 限制查询条数select 列 

顺序严格，错误会导致查询失败

测试表：

create database test charset utf8mb4 collate utf8mb4_bin;
use test;
create table emp(
   id int not null unique auto_increment comment 'ID',
   name char(20) not null comment '名字',
   sex enum("male","female") not null default "male" comment '性别',
   age int(3) unsigned not null default 28 comment '年龄',
   hire_date date not null comment '入职日期',
   Position char(9) not null comment '职位',
   depart_id int comment '部门ID');

插入数据

insert into emp(name,sex,age,hire_date,Position,depart_id) values
('程咬金','male',18,'20170301','teacher',1),
('程咬银','male',78,'20150302','teacher',1),
('程咬铜','female',48,'20150311','sale',2),
('程咬铁','female',38,'20101101','sale',2),
('程咬镍','male',28,'20160311','operation',3),
('程咬锌','male',18,'19970312','operation',3)
;

9.1.3 select 配合from使用

FROM 后面跟 需要查的表名
use 库;
select * from 表名;   #查询表中的所有信息。  生产几乎用不到的命令。当数据库的数据量比较大时，绝对不能使用， 查询很慢，会拖数据库的性能
select 列1,列2,列3 from 表名; 同样会查询出海量数据，因此也不常用，

9.1.4 where

用于限制查询的内容， 使查到的数据量变小，且精确。
select 列1,列2,列3 from 表名 where 过滤条件; 
where 后面的条件 可以是比较值  也可以是逻辑连接符


(大于> 小于< 大于等于>= 小于等于<= 等于= 不等于 != )  #比较值
and or #逻辑连接符
like #模糊值

和shell 语句思路差不多
比较运算:
#找出ID等于1的人姓名和年龄
MySQL [test]> select name,id,age from emp where id = 1;
+-----------+----+-----+
| name      | id | age |
+-----------+----+-----+
| 程咬金    |  1 |  18 |
+-----------+----+-----+

#找出ID不等于1的人姓名和年龄
MySQL [test]>select name,id,age from emp where id != 1;
+-----------+----+-----+
| name      | id | age |
+-----------+----+-----+
| 程咬银    |  2 |  78 |
| 程咬铜    |  3 |  48 |
| 程咬铁    |  4 |  38 |
| 程咬镍    |  5 |  28 |
| 程咬锌    |  6 |  18 |
+-----------+----+-----+
5 rows in set (0.00 sec)

#找出ID大等于1的人姓名和年龄
MySQL [test]>select name,id,age from emp where id >= 1;
+-----------+----+-----+
| name      | id | age |
+-----------+----+-----+
| 程咬金    |  1 |  18 |
| 程咬银    |  2 |  78 |
| 程咬铜    |  3 |  48 |
| 程咬铁    |  4 |  38 |
| 程咬镍    |  5 |  28 |
| 程咬锌    |  6 |  18 |
+-----------+----+-----+
6 rows in set (0.00 sec)

逻辑运算：
#找出ID大于1小于3的人姓名和年龄
MySQL [test]>select name,id,age from emp where id > 1 and id < 3 ;
+-----------+----+-----+
| name      | id | age |
+-----------+----+-----+
| 程咬银    |  2 |  78 |
+-----------+----+-----+
1 row in set (0.00 sec)

#找出ID等于 和 等于 2的人姓名和年龄
MySQL [test]>select name,id,age from emp where id = 1 or id = 2 ;
+-----------+----+-----+
| name      | id | age |
+-----------+----+-----+
| 程咬金    |  1 |  18 |
| 程咬银    |  2 |  78 |
+-----------+----+-----+
2 rows in set (0.00 sec)
或者
MySQL [test]>select name,id,age from emp where id in (1,2);
+-----------+----+-----+
| name      | id | age |
+-----------+----+-----+
| 程咬金    |  1 |  18 |
| 程咬银    |  2 |  78 |
+-----------+----+-----+
2 rows in set (0.00 sec)

找出年龄大于18 且职位为teacher的人的信息
MySQL [test]>select name,id,age,Position from emp where Position = 'teacher' and  age > 18;
+-----------+----+-----+----------+
| name      | id | age | Position |
+-----------+----+-----+----------+
| 程咬银    |  2 |  78 | teacher  |
+-----------+----+-----+----------+
1 row in set (0.00 sec)

模糊值
找到姓程人的信息
MySQL [test]>select name,id,age from emp where name like '程%';
+-----------+----+-----+
| name      | id | age |
+-----------+----+-----+
| 程咬金    |  1 |  18 |
| 程咬银    |  2 |  78 |
| 程咬铜    |  3 |  48 |
| 程咬铁    |  4 |  38 |
| 程咬镍    |  5 |  28 |
| 程咬锌    |  6 |  18 |
+-----------+----+-----+
6 rows in set (0.00 sec)
% 在MySQL中代表匹配所有
但业务中 不能出现 前后都带有%号的语句，查询性能极差。 如果必要的话 会用到ES

9.1.5 group by

group by 将某列中具有共同特点的数据行，分成一组，然后进行数据聚合的操作

MAX();
MIN();
AVG();
CONUT();计数
SUM(); 求和
GROUP_CONUT(); 显示组内的具体信息，将列拼接成行
DISTINCT 列; 去重
CONCAT(); 将行拼接成列
# select concat(user,"@",host) from mysql.user;
MySQL [test]>select concat(user,"@",host) from mysql.user;
+-------------------------+
| concat(user,"@",host)   |
+-------------------------+
| test@10.0.0.1           |
| mysql.session@localhost |
| mysql.sys@localhost     |
| root@localhost          |
+-------------------------+
5 rows in set (0.00 sec)

示例

MySQL [test]>select Position,count(Position) from emp group by Position;
+-----------+-----------------+
| Position  | count(Position) |
+-----------+-----------------+
| operation |               2 |
| sale      |               2 |
| teacher   |               2 |
+-----------+-----------------+
3 rows in set (0.00 sec)

MySQL [test]>select Position,group_concat(name) from emp group by Position;
+-----------+---------------------+
| Position  | group_concat(name)  |
+-----------+---------------------+
| operation | 程咬镍,程咬锌       |
| sale      | 程咬铜,程咬铁       |
| teacher   | 程咬金,程咬银       |
+-----------+---------------------+
3 rows in set (0.00 sec)

MySQL [test]>select concat(Position,':',group_concat(name)) from emp group by Position;
+-----------------------------------------+
| concat(Position,':',group_concat(name)) |
+-----------------------------------------+
| operation:程咬镍,程咬锌                 |
| sale:程咬铜,程咬铁                      |
| teacher:程咬金,程咬银                   |
+-----------------------------------------+

它是先把 条件列 进行了一个去重，随后再进行分组

group by语句的显示条件是苛刻的、

select name,Position,count(Position) from emp group by Position;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.emp.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

当我们在查询的信息内 多加了一列name时， SQL执行就报错了。 
MySQL [test]>select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
是因为 sql_mode中ONLY_FULL_GROUP_BY这个字段，定义了设定严格模式，即分组的语句内，只能查看作为分组条件的列，其他的非主键列是无法直接查到的 要么该列是 groupby的条件列。要么包裹在函数中。  这个条件 大概是在5.7 版本以后 变成了默认值。但5.6 和 8.0的版本是默认没有这个参数限制的

正常查询该两个列时，得到的值如下。
MySQL [test]>select name,Position from emp;
+-----------+-----------+
| name      | Position  |
+-----------+-----------+
| 程咬金    | teacher   |
| 程咬银    | teacher   |
| 程咬铜    | sale      |
| 程咬铁    | sale      |
| 程咬镍    | operation |
| 程咬锌    | operation |
| 程咬镉    | operation |
+-----------+-----------+
7 rows in set (0.00 sec)
而当我们根据Position 分组以后， 默认它会执行一个去重操作。 分组的列会变成这样。 
+-----------+-----------+
| name      | Position  |
+-----------+-----------+
| 程咬金    | teacher   |
| 程咬银    | 
| 程咬铜    | sale      |
| 程咬铁    |     
| 程咬镍    | operation |
| 程咬锌    |
| 程咬镉    |  
+-----------+-----------+
从而最终能查出来的数据 变成了这样。 但 实际上不是这么回事儿。 
+-----------+-----------+
| name      | Position  |
+-----------+-----------+
| 程咬金    | teacher   |
| 程咬铜    | sale      |
| 程咬镍    | operation |
+-----------+-----------+
所以 才会有了ONLY_FULL_GROUP_BY这个限制。

如果非要强制这么操作，则需要修改默认的sqlmode

[mysqld] 
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
把 ONLY_FULL_GROUP_BY 去掉即可。

9.1.6 having

where 应用于先查询后分组的情况
having 应用于先分组后查询的情况

插入一行新数据
Insert into emp(name,sex,age,hire_date,Position,depart_id) values ('程咬镉','male',18,'19970312','operation',3);  #operation 运营
MySQL [test]>select Position,count(name) from emp  group by Position  having count(name) > 2;
+-----------+-------------+
| Position  | count(name) |
+-----------+-------------+
| operation |           3 |
+-----------+-------------+
1 row in set (0.00 sec)
错误示例1： 参照执行顺序语法 这个句子本身就是错的
MySQL [test]>select Position,count(name) from emp 
group by Position 
where count(name) > 2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where count(name) > 1' at line 1
把执行顺序规范后，
MySQL [test]>select Position,count(name) from emp 
where count(name) > 2
group by Position;
ERROR 1111 (HY000): Invalid use of group function
这里有一个悖论， count(name) 是在完成where条件后才进行计算的，而where本身不支持函数的使用，所以这个会有语法类的错误， 而having就是专门用来应对这种情况的

having 语句 不走索引

9.1.7 order by

最后进行一个排序

MySQL [test]>select Position,count(name) from emp  group by Position  having count(name) > 1 order by count(name);
+-----------+-------------+
| Position  | count(name) |
+-----------+-------------+
| teacher   |           2 |
| sale      |           2 |
| operation |           3 |
+-----------+-------------+
3 rows in set (0.00 sec)

select Position,count(name) from emp  group by Position  having count(name) > 1 order by count(name) asc;默认为升序。 asc写不写都行
MySQL [test]>select Position,count(name) from emp  group by Position  having count(name) > 1 order by count(name) desc;  #desc降序
+-----------+-------------+
| Position  | count(name) |
+-----------+-------------+
| operation |           3 |
| teacher   |           2 |
| sale      |           2 |
+-----------+-------------+
3 rows in set (0.00 sec)

9.1.8 limit

限制显示的行的数量或者行号

limit 3 # 只显示三行
limit 3,3 #跳过前三行 显示三行

9.1.9 union union all

MySQL [test]>select name,id,age from emp where id = 1 or id = 2 ;
+-----------+----+-----+
| name      | id | age |
+-----------+----+-----+
| 程咬金    |  1 |  18 |
| 程咬银    |  2 |  78 |
+-----------+----+-----+
2 rows in set (0.00 sec)

可以改写为
MySQL [test]>select name,id,age from emp where id = 1
union all
select name,id,age from emp where id = 2;
+-----------+----+-----+
| name      | id | age |
+-----------+----+-----+
| 程咬金    |  1 |  18 |
| 程咬银    |  2 |  78 |
+-----------+----+-----+
2 rows in set (0.00 sec)

union 和 union all 的作用为 查看多个语句的执行结果。
区别在于 union 会对重复行进行去重，而 union all不会。所以union all 用的会多一点，且其性能优于 union 和 or的用法(索引章节提到)

9.1.10 总结

找出职位上人数大于2的部门
先把简单框架写出来
select  from emp 
再补充条件
select Position,count(name) from emp 
再补充
以此类推

9.2 多表查询

准备表；

use test;
create table dep(
dep_id int not null primary key comment 'ID',
dep_name varchar(20) comment '名字'
);

create table tmp(
id int primary key auto_increment comment 'ID' ,
name varchar(20),
sex enum('male','female') not null default 'male' comment '性别',
age int(3) comment '年龄',
dep_id int(3)
);

insert into dep values
(200,'技术部'),
(201,'人力资源部'),
(202,'销售部'),
(203,'运营部');

insert into tmp(name,sex,age,dep_id) values
('程咬金','male',18,200),
('程咬银 ','female',48,201),
('程咬铜','male',38,201),
('程咬铁','female',28,202),
('程咬镍','male',18,200),
('程咬锌','female',18,204)
;

9.2.1 内连接

单表数据无法满足查询需求时，会用到。

关键字 程咬金 在tmp表里
而部门名在dep表里 
但是在 tmp表中能查询到 程咬金的部门ID
MySQL [test]>select dep_id from tmp where name = '程咬金';
+--------+
| dep_id |
+--------+
|    200 |
+--------+
1 row in set (0.00 sec)
然后 dep表内 有部门ID和部门名称的对照关系， 因此可以用部门ID 去找到部门名
MySQL [test]>select dep_name from dep where dep_id = 200;
+-----------+
| dep_name  |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)
但是实际情况 肯定不能这查询 效率过低

多表查询的前提：必须两个表有关联的列
书写时：表名.列
所有需要查询的列都放在select后。所有条件都放在on后面
基础语法

select A.h B.* C.* from A 
join B
on A.j = B.k
join C
on B.l = C.m
join D
on C.n = D.p
where *

上述例子可以改写为

select 
dep.dep_name 
from tmp
inner join 
dep 
on tmp.dep_id = dep.dep_id 
where tmp.name = '程咬金';

列别名

select 
dep.dep_name  as '部门名'
from tmp
inner join 
dep 
on tmp.dep_id = dep.dep_id 
where tmp.name = '程咬金';


as 可以调整最终显示的名字；
MySQL [test]>select 
    -> dep.dep_name  as '部门名'
    -> from tmp
    -> inner join 
    -> dep 
    -> on tmp.dep_id = dep.dep_id 
    -> where tmp.name = '程咬金';
+-----------+
| 部门名    |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)

在having where 可以进行调用

表别名

select 
d.dep_name 
from tmp as t
inner join 
dep  as d 
on t.dep_id = d.dep_id 
where t.name = '程咬金';

一旦设置了表别名 整个句中的 都需要进行更改 --

10. show命令的大致用法

show  databases;                          #查看所有数据库
show tables;                                          #查看当前库的所有表
SHOW TABLES FROM                        #查看某个指定库下的表
show create database world                #查看建库语句
show create table world.city                #查看建表语句
show  grants for  root@'localhost'       #查看用户的权限信息
show  charset；                                   #查看字符集
show collation                                      #查看校对规则
show processlist;                                  #查看数据库连接情况
show index from                                 #表的索引情况
show status                                         #数据库状态查看
SHOW STATUS LIKE '%lock%';         #模糊查询数据库某些状态
SHOW VARIABLES                             #查看所有配置信息
SHOW variables LIKE '%lock%';          #查看部分配置信息
show engines                                       #查看支持的所有的存储引擎
show engine innodb status\G               #查看InnoDB引擎相关的状态信息
show binary logs                                    #列举所有的二进制日志
show master status                                 #查看数据库的日志位置信息
show binlog evnets in                             #查看二进制日志事件
show slave status \G                             #查看从库状态
SHOW RELAYLOG EVENTS               #查看从库relaylog事件信息
desc  (show colums from city)               #查看表的列定义信息

SQL基础