2.SQL 基础

2.1 数值类型

tinyint: -128~127
int :-2^31~2^31-1 
手机号码 一般使用char类型

2.2 字符类型

char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(11):
变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。
enum('bj','tj','sh')：
枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。

2.3 时间类型

DATE   YYYY-MM-DD 2020-11-10
TIME   hh:mm:ss[.uuuuuu] 08:59:02.123456
DATETIME  YYYY-MM-DD hh:mm:ss[.uuuuuu]  上面两个的综合
TIMESTAMP 跟上面一样 但是会根据时区变化
YEAR YYYY 2020

2.4 表属性

约束(一般建表时添加):
**primary key** ：主键约束
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
**not null**      ：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
**unique key** ：唯一键
列值不能重复
**unsigned** ：无符号
针对数字列，非负数。

其他属性:
**key** :索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加
**default**           :默认值
列中，没有录入值时，会自动使用default的值填充
**auto_increment**:自增长
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
**comment ** : 注释

2.5 字符集和校对规则

utf8
utf8mb4

3. DDL语言

3.1 创建数据库

CREATE DATABASE school CHARSET utf8;  ##创建数据库 指定字符集
CREATE SCHEMA sch;
SHOW CHARSET;  ##查看指定的字符集
SHOW COLLATION; ##查看字符集指定的排序方式 例如utf8_general_ci 正常排序 utf8_bin 排序支持大小写
CREATE DATABASE stu CHARSET utf8mb4 COLLATE utf8mb4_bin; ##指定排序规则

建库规范：
1.库名不能有大写字母   
2.建库要加字符集   默认拉丁文      
3.库名不能有数字开头
4. 库名要和业务相关

3.2 删除数据库

DROP DATABASE sch;

3.3 修改数据库名

alter database school charset utf8mb4; ##字符集 从小的改成大的
3306 [(none)]>show create database school; ##查看创建的命令
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.01 sec)

3.4 表的定义

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

样例

USE school;
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(255) NOT NULL COMMENT '学生',
sage TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
sgender ENUM ('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
sfz CHAR(18) NOT NULL UNIQUE COMMENT '身份证',
intime TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';

3.5 表的删除

drop table stu;

3.6 表的修改

MariaDB [school]> show create table stu;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `sname` varchar(255) NOT NULL COMMENT '学生',
  `sage` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `sgender` enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
  `sfz` char(18) NOT NULL COMMENT '身份证',
  `intime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sfz` (`sfz`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='学生表'                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

表中添加 字段

注意 添加或者删除 字段 会锁表
ALTER TABLE stu ADD qq VARCHAR(20) UNIQUE NOT NULL COMMENT 'QQ号';  ##在最后添加字段
ALTER TABLE stu ADD wxchat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;  ## 在sname后添加
ALTER TABLE stu ADD sid INT NOT NULL  COMMENT '数字' FIRST; ## 在首行添加数据

表中删除 字段

ALTER TABLE stu DROP sid;

表中修改 字段

ALTER TABLE stu MODIFY sname VARCHAR(128) NOT NULL COMMENT '学生';  ##注意 修改字段属性 需要将之前的表属性都带上 默认覆盖
ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' COMMENT '性别'; ##修改字段 名字 属性 用change

CREATE TABLE ceshi LIKE stu; ##创建一个表跟stu表一样的结构

4 DML语句 对数据操作

4.1 数据插入 insert

DESC stu;
录入一条记录
INSERT INTO stu VALUES(2,'zs',18,'m','123456',NOW());
针对性录入
INSERT INTO stu (sname,sfz) VALUES ('ww','3456568');
批量录入
INSERT INTO stu (sname,sfz) VALUES 
('w5','5656565665'),
('w6','565656e665'),
('w7','5656565365'),
('w8','5656565w65');
查询
SELECT * FROM stu;

4.2 更新 update

DESC stu;
查询
SELECT * FROM stu;
根据条件修改 不然全表跟新
UPDATE stu SET sname='zhaoshi' WHERE id=2;

4.3 删除 delete

危险 删除 必须带where 删除后只是将数据标记为可覆盖 但是实际数据没有删除 会有碎片 缝隙
DELETE FROM stu WHERE id=3;

TRUNCATE TABLE stu ;  清空表  相当于dd命令 无法恢复
DELETE   FROM  stu ;  删除表 空间没有释放 相当于 vim 

区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
truncate: DDL操作,对与表段中的数据页进行清空,速度快.

伪删除

添加状态列
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ;

UPDATE stu SET state=0 WHERE id=2;

SELECT * FROM stu WHERE state=1;

5 DQL语言 对数据行查询

5.1 select 用法

--- SELECT @@xxx 查看系统参数
 SELECT @@port ;
 SELECT @@datadir ;
 SELECT @@socket ;
 SELECT @@server_id ;
 SELECT @@log_error ;
 SELECT @@log_bin_basename ;
 
--- SHOW VARIABLES 模糊查询参数
 SHOW VARIABLES LIKE "innodb%";
 SHOW VARIABLES LIKE "port";

---SELECT 函数
SELECT NOW();
SELECT DATABASE();
SELECT USER();
SELECT CONCAT("Hello Word!"); ##拼接

SELECT USER,HOST FROM mysql.user;
SELECT CONCAT(USER,'@',HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,'@',HOST) FROM mysql.user; ##将多列整成一列

5.2 from

SELECT 列1,列2 FROM 表
SELECT  *  FROM 表

例如

SELECT * FROM stu;
SELECT sname,sfz FROM stu WHERE id<2;

5.3 where

等值查询

SELECT sname,sfz FROM stu WHERE id=2;

where配合比较操作符(> < >= <= <>)

SELECT sname,sfz FROM stu WHERE id<2;

where配合逻辑运算符(and or )

SELECT * FROM stu WHERE sg='n' AND sage>2;

where 模糊查询 like

SELECT * FROM city WHERE district LIKE "guang%";

where 配合in 语句 下面两个例子 结果一样

SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
SELECT * FROM city WHERE countrycode IN ('CHN','USA')

where 配合 between and

SELECT * FROM city WHERE population >100000 AND population <200000;
SELECT * FROM city WHERE population BETWEEN 100000 AND 200000;

5.4 group by +聚合函数

常用聚合函数

**max()**      ：最大值
**min()**      ：最小值
**avg()**      ：平均值
**sum()**      ：总和
**count()**    ：个数
group_concat() : 列转行

例如

SELECT countrycode,SUM(population) FROM city GROUP BY countrycode; ##统计各个国家的人口总数
查找       国家        个数         从     表   根据      国家   分类
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district; ##统计中国每个省的人数
SELECT countrycode,COUNT(NAME) FROM city  GROUP BY countrycode; ##统计各个国家城市的个数 count
SELECT district,GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district; ##统计中国 各个省的城市
SELECT district,AVG(population) FROM city WHERE countrycode='CHN' GROUP BY district; ##统计中国每个省的人数平均值

having group by 后再次统计 注意 having后 不走索引
where|group|having

SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)<10000000 ; ##统计中国每个省的人数 再统计人口总数小于100w的信息

5.5 order by

顺序 SELECT    FROM    WHERE    GROUP BY    HAVING    ORDER BY   LIMIT

例如：

SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population   DESC ; ##ASC 从小到大
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY population DESC;  ##统计各个省的数量 并排序
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population) >5000000 ORDER BY SUM(population)  DESC; ##统计各个省的个数 并找出大于500W 排序

5.6 limit 显示几个

SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population) >5000000 ORDER BY SUM(population) DESC LIMIT 3; 

LIMIT n,m 从n开始 显示m个
LIMIT M OFFSET N  跳过N行 显示M行  跟上一样效果

5.7 DISTINCT distinct

DISTINCT 去重复

SELECT DISTINCT(countrycode) FROM city; ## 显示所有国家名称

5.7 union 联合

UNION ALL 不检查数据中重复行
UNION 检查数据中重复的行 去重
SELECT * FROM city WHERE countrycode IN ('CHN','USA')
SELECT * FROM city WHERE countrycode='CHN' UNION ALL  SELECT * FROM city WHERE countrycode='USA' ;
一般情况下 将AND 或 OR 转成 UNION ALL  提高 执行效率速度快

5.8 多表查询

use school
student ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

-- 项目构建
drop database school;
CREATE DATABASE school CHARSET utf8;
USE school

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL  COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');


INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

例如

统计 国土小于100的 城市名 国家名 国土面积
SELECT city.name,country.name,country.surfacearea 
FROM city JOIN country
ON city.countrycode=country.code 
WHERE city.population <100;

5.9 别名 as

AS 列别名 为了好看

SELECT city.name AS 城市,
country.name AS 国家,
country.surfacearea  AS 国土面积 
FROM city JOIN country
ON city.countrycode=country.code 
WHERE city.population <100;

AS 表别名

查询沈阳的人口 国土面积 国家名
SELECT a.name,b.name,b.surfacearea 
FROM city AS a JOIN country AS b
ON a.countrycode=b.code 
WHERE a.name="shenyang"

例如

张三 学习几门课
USE school
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

SELECT a.sname,COUNT(b.cno)
FROM student AS a JOIN sc AS b
ON a.sno=b.sno
WHERE a.sname="zhang3"

张三 学习课程名称
SELECT a.sname,GROUP_CONCAT(c.cname)
  FROM
    student AS a
    JOIN sc AS b
      ON a.sno = b.sno
    JOIN course AS c
      ON b.cno = c.cno
  WHERE a.sname = "zhang3"

 查询oldguo 所交课程的平均分数
USE school
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;
  
SELECT a.`tname`,AVG(c.`score`)
FROM teacher AS a 
JOIN course AS b
ON a.tno=b.tno
JOIN sc AS c
ON b.cno=c.cno
WHERE a.tname="oldguo"

 查询oldguo所教的不及格的学生姓名
SELECT a.tname,d.sname,c.score
FROM teacher AS a
JOIN course AS b
ON a.tno=b.tno
JOIN sc AS c
ON b.cno=c.cno
JOIN student AS d 
ON c.sno=d.sno
WHERE c.score <60 AND tname="oldguo"; 

查询平均成绩大于60分的同学的学号和平均成绩；
SELECT a.sno,a.sname,AVG(b.score)
FROM student AS a 
JOIN sc AS b
ON a.sno=b.sno
WHERE b.score>60
GROUP BY b.score;

查询所有同学的学号、姓名、选课数、总成绩；
SELECT a.sno,a.sname,GROUP_CONCAT(c.cname),SUM(b.score)
FROM student AS a 
JOIN sc AS b
ON a.sno=b.sno
JOIN course AS c 
ON b.cno=c.cno
GROUP BY a.sname

查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT a.cno AS 课程ID,
       MAX(b.score) AS 最高分,
       MIN(b.score) AS 最低分
FROM course AS a 
JOIN sc AS b
ON a.cno=b.cno
GROUP BY b.cno

6.视图 information_schema.tables视图

视图库 虚拟库

USE school 
CREATE VIEW zhangsan AS  ##创建视图
SELECT student.sname,GROUP_CONCAT(course.cname)
  FROM
    student 
    JOIN sc 
      ON student.sno = sc.sno
    JOIN course 
      ON sc.cno = course.cno
  WHERE student.sname = "zhang3"

information_schema.TABLES 存储整个mysql中所有的表信息 属性

DESC information_schema.TABLES 
TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）
ENGINE --->代表引擎
表占用空间 TABLE_ROWS  * AVG_ROW_LENGTH +INDEX_LENGTH 

例如：

查询整个数据库中所有库和所对应的表信息
SELECT table_schema,GROUP_CONCAT(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

统计所有库下的表个数
SELECT table_schema,COUNT(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

查询所有innodb引擎的表及所在的库
SELECT table_schema,GROUP_CONCAT(table_name),ENGINE
FROM  information_schema.tables
WHERE ENGINE="innodb"
GROUP BY table_schema;

统计world数据库下每张表的磁盘空间占用
SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,"KB") AS size 
FROM information_schema.tables
WHERE table_schema="world";

统计所有数据库的总的磁盘空间占用
SELECT 
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS size 
FROM information_schema.tables
GROUP BY table_schema;

生成整个数据库下的所有表的单独备份语句

模板语句：
mysqldump -uroot -p123 world city >/tmp/world_city.sql

SELECT CONCAT('mysqldump -uroot -p123 ',table_schema," ",table_name," >/bak/",table_schema,"_",table_name,".sql")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/world.sh'
vim /etc/my .conf
secure-file-priv=/tmp ##指定安全路径 

107张表，都需要执行以下2条语句
ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';

show 命令

help 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)               #查看表的列定义信息
http://dev.mysql.com/doc/refman/5.7/en/show.html