MySQL-第三章 基础与源数据获取

1.SQL的介绍

SQL标准：SQL-92，SQL-99
SQL_mode

2.SQL的常用分类

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

3.表的核心属性介绍

3.1表属性

3.1.1存储引擎
engine=innodb（默认）
3.1.2字符集及校对规则

字符集：

gbk
utf8（utf8mb3）：中文字符占3个字符
utf8mb4 ：中文字符占4个字符

区别：
emoji 字符支持
拼音yu
校对（排序）规则：
校对规则是跟着字符集走的

例如：utf8mb4
show collation;
utf8mb4_general_ci -->通用的校对，默认值，不区分大小写
utf8mb4_bin -->区分大小写

3.2列属性

3.2.1数据类型

数字类型：

---tinyint    128-127
---int         -2^31~2^31-1

字符类型：

---char（10）
---varchar（10）

----说明：
以上数据类型，必须要定义最长字符长度，用括号中的数字表示。
----char和varchar的区别：
char类型：
是定长类型，存储数据是，无关字符长度，不管存储多长的数据，都立即分配10个字符唱的的存储空间,无法占满的部分，使用空来填充
varchar类型：
可变长度，按需分配存储空间，每次都要计算字符串长度
----如何选择char和varchar类型
变长列，推荐varchar
定长列，推荐char

---enum（'bj','sh','sz',......）
---枚举类型

时间类型：

---datetime
---timetamp

二进制型：
（一般不使用）图片，视频等存储

3.2.2约束
not null  非空
unique  key   唯一
primary  key   主键（非空且唯一）

说明：
1.一个表有且一个主键列，最好是一个无关列数字列，一般会在表中设置自增长的id列
2.尽量每个列非空，如果无法保证，可以追加默认值
3.手机号，身份证号，银行卡号……种类的列设定为唯一

3.2.3其他属性
属性 解释
unsigned 无符号，一般是在int或tinyint后添加的附加属性
default 设定more值
auto_increment 数字列自增长
commnet 注释

4.DDL语句

4.1库定义

4.1.1创建数据库
CREATE DATABASE xiaopengyou CHARSET utf8mb4;

建库的规范：
1.库名是小写
2.库名不能是数字开头
3.库名要和业务有关
4.建库是要添加字符集

4.1.2删库
DROP DATABASE xiaopengyou;
4.1.3查库
show create database  ku;查看创建库语句
show databases;查看所有库
4.1.4改库
alter database ku charset utf8mb4;更改库字符集

4.2表定义

4.2.1建表
例：
CREATE TABLE xs (
id INT  NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
NAME VARCHAR(64) NOT NULL COMMENT '学生姓名',
age TINYINT NOT NULL DEFAULT 0 COMMENT '年龄',
gender CHAR(1) NOT NULL DEFAULT 'n' COMMENT '性别',
shengfen ENUM('bj','sh','cq','tj') NOT NULL DEFAULT 'bj' COMMENT '省份',
TIME DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4;

建表规范：
1.表名小写，无数字开头，与业务有关
2.必须要有主键，一般是一个自增长的无关例
3.选择合适的数据类型，字符长度要适中
4.每个列都非空，并设定默认值
5.每个列必须要有注释
6.必须设置存储引擎和字符集

4.2.2改表

1）添加 列

DESC xs  #查看表结构
SHOW CREATE TABLE xs; # 查看建表语句

1.在xs表中添加列 shouji

ALTER TABLE xs ADD shouji CHAR(11) NOT NULL UNIQUE KEY COMMENT '手机'

2.在xs表中，在gender后面添加‘微信’列

ALTER TABLE xs ADD 微信 VARCHAR(30) NOT NULL UNIQUE KEY COMMENT '微信号' AFTER gender

3.在第一列位置添加QQ号列

ALTER TABLE xs ADD QQ VARCHAR(30) NOT NULL UNIQUE KEY COMMENT 'QQ号' FIRST

2）删除 列

ALTER TABLE xs DROP shouji;（危险操作！）

3）更改 列属性
只改属性,不改列名

ALTER TABLE xs MODIFY ssname  VARCHAR(64) NOT NULL COMMENT '姓名';

列名和属性都修改

ALTER TABLE xs CHANGE sname ssname VARCHAR(32) NOT NULL COMMENT '姓名';
4.2.2删表
drop table xuesheng；表定义和数据全部删除
truncate table xs；清空表的区，数据清空，表定义保留
4.2.3查表
show tables;
show create table xs;
desc xs;

5.DCL语句

grant  权限  on  范围  to   用户   identified    by  ‘密码’；

revoke  权限  on  范围  from  用户；

6.DML语句

用作表的数据行的增，删，改，查

6.1 insert

6.1.1最规范的录入方法
INSERT INTO xs(id,NAME,age,gender,shengfen,TIME) VALUES(1,'张三',18,'m','bj',NOW())

说明：
INSERT INTO                             插入到
xs(id,NAME,age,gender,shengfen,TIME)    对应列名
VALUES                                  值
(1,'张三',18,'m','bj',NOW())            详细录入信息
6.1.2简化写法
INSERT INTO xs VALUES(2,'李四',22,'m','tj',NOW())
6.1.3 针对性的录入数据
INSERT INTO xs(NAME,age,sex,shengfen)
VALUES('wangwu',20,'f','tj');
6.1.4批量录入
INSERT INTO
xs(NAME,age,gender,shengfen)
VALUES
('a',20,'f','tj'),
('b',20,'f','tj'),
('c',20,'f','tj'),
('d',20,'f','tj');

6.2update

UPDATE xs SET age=20 WHERE id=1;

说明：
update   xs  修改xs表
set   age=20   更改的值
where  id=1    过滤到id为1的那一列

注意！：update 语句必须要加where条件

6.3delete

DELETE FROM xs WHERE id=5;

注意！：delete语句必须要加where条件

面试题：以下语句的区别？

truncate ：
1)是DDL语句，清空整表的所有数据，按照区来删除的，属于物理删除，性能高
2)表所占用的空间，会立即释放

delete ：
1）是DML语句，清空整表的所有数据，按照行来删除，属于逻辑删除，性能低
2）表所占用的空间，不会立即释放

6.4使用update 替代delete 实现伪删除

原语句:

DELETE FROM xs WHERE id=6;

改写后:

UPDATE xs SET state=0 WHERE id=6;

业务语句进行调整

select * from xs;

改为:

select * from xs where state=1;

7.DQL语句基础应用

7.1.select 语句应用

select （单表）的执行逻辑
执行顺序 语句 说明
1 select 列1 ，列2
2 from 表
3 where 条件
4 group by 条件
5 having 条件
6 order by 条件
7 limit 条件
7.1.1select 单独使用的情况（MySQL独家）
SELECT @@参数名
例子：
SELECT @@datadir;
SELECT @@port
SELECT @@socket
SHOW VARIABLES LIKE '%trx%';模糊查找参数
7.1.2SELECT 函数();
SELECT NOW();
USE MYSQL;
SELECT DATABASE();

SELECT USER();
SELECT 16*16
SELECT CONCAT("HELLO WORD!!");
SELECT CONCAT(USER,"@",HOST) FROM MYSQL.USER;

7.2 FROM子句应用

USE WORLD;
SELECT * FROM CITY;
SELECT NAME,COUNTRYCODE FROM CITY;
DESC CITY;

7.3WHERE 字句应用

7.3.1等值查询
查询中国城市信息
SELECT * FROM city WHERE countrycode='CHN'
7.3.2 不等值查询
-- 查询人口数量少于100人城市.
SELECT * FROM city WHERE population<100;
-- 查询ID小于10的城市信息
SELECT * FROM city WHERE id<10;
-- 查询不是中国的城市信息(尽量不使用不等于,可能不走索引)
SELECT * FROM city WHERE countrycode!='CHN';
7.3.3模糊查询
查询国家代号为ch打头的城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%';
SELECT * FROM city WHERE countrycode LIKE '%CH%';
7.3.4 AND 和 OR

与 或

7.3.5 where配合between AND 的使用
---查询人口数在100w-200w区间的城市信息（包含头尾）
SELECT *  FROM city WHERE population BETWEEN 1000000 AND 2000000
7.3.6WHERE 配合 IN 使用
---查看山东或河北的城市信息
SELECT *  FROM city
WHERE district IN ('shandong','hebei')

7.4 GROUP BY 子句

7.4.1什么是分组

按照某个列进行分组

7.4.2常用的聚合函数
函数 含义
COUNT() 计数
MAX() 最大值
MIN() 最小值
AVG() 平均值
SUM() 求和
GROUP_CONCAT() 列转行 
---例子
统计每个国家的城市个数
SELECT countrycode,COUNT(id) FROM city
GROUP BY countrycode; 
统计每个国家的总人口数

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode;
统计中国每个省的城市个数及总人口数
SELECT district,COUNT(NAME),SUM(population)  
FROM city WHERE countrycode='CHN'
GROUP BY  district

统计各个国家的城市名列表
SELECT countrycode,GROUP_CONCAT(NAME) FROM city GROUP BY countrycode;

7.5 having子句的使用(后过滤条件)

--统计中国每个省的城市个数及总人口数
--且只显示人口大于800w的城市
SELECT district,COUNT(NAME),SUM(population)  
FROM city 
WHERE countrycode='CHN'
GROUP BY  district
HAVING SUM(population)>8000000

7.6ORDER BY 子句

--以上例子 ，将人口数进行排列输出

SELECT district,COUNT(NAME),SUM(population)  
FROM city 
WHERE countrycode='CHN'
GROUP BY  district
HAVING SUM(population)>8000000
ORDER BY SUM(population) DESC;

末尾desc 从大到小排序

查询中国所有城市信息，并以人口数就降序输出
SELECT NAME,population FROM city
WHERE countrycode='CHN'
ORDER BY population;

7.7. limit 应用

-- 查询中国所有城市信息,并以人口数降序输出,只显示前五名
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population  DESC
LIMIT 5 ;

SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population  DESC
LIMIT 10 OFFSET 0;


SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population  DESC
LIMIT 5 OFFSET 5

SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population  DESC
LIMIT 3,5;

-- 跳过前N行,显示M行(N和M代表的是数字)
LIMIT M offet N
LIMIT N,M

8.多表连接查询

8.1作用

业务需要的数据来自多张表时

8.2多表连接查询基本语法

内连接 *****
外连接*
全连接#
笛卡尔#

8.3多表连接的基本语法（内连接）

传统连接**
自连接**
join uing**
join on ****

8.4join on 的语法

多表连接的套路：
1.根据需求找关联列
2.找到表与表的关联列
3.列名调用时，需要添加表前缀 ，例如a.id b.name

8.5多表连接案例

8.5.1查询人口数量少于100人的城市所在：国家明，国土面积，城市名，人口数
SELECT 
country.name ,country.SurfaceArea,city.name,city.Population
FROM city 
JOIN country
ON city.CountryCode=country.Code
WHERE city.Population<100;
8.5.2张三学习了哪些课程
biao
SELECT student.sname,COUNT(sc.cno)
FROM student
JOIN sc
ON student.sno=sc.sno
WHERE student.sname='zhang3';
8.5.3张三学习了哪些课程名称
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'; 
group by student.sname
8.5.4oldguo老师教了学生的个数
SELECT teacher.tname,COUNT(student.sno)
FROM teacher 
JOIN course
ON teacher.tno=course.`tno`
JOIN sc
ON course.cno=sc.`cno`
JOIN student 
ON sc.sno=student.`sno`
WHERE teacher.tname='oldguo';
8.5.5 每位老师所教课程的平均分,并按平均分排序
SELECT teacher.tname,AVG(sc.score)
FROM teacher 
JOIN course
ON teacher.tno=course.`tno`
JOIN sc
ON course.cno=sc.`cno`
GROUP BY teacher.tname
ORDER BY AVG(sc.score)DESC;
8.5.6 查询oldguo所教的不及格的学生姓名
SELECT  teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher 
JOIN course
ON teacher.tno=course.`tno`
JOIN sc
ON course.cno=sc.`cno`
JOIN student 
ON sc.sno=student.`sno`
WHERE sc.score<'60' AND  teacher.tname='oldguo';
8.5.7 查询所有老师所教学生不及格的信息
SELECT teacher.tname,student.sno,student.`sage`,student.`sname`,student.`ssex`,sc.`score`
FROM teacher 
JOIN course
ON teacher.tno=course.`tno`
JOIN sc
ON course.cno=sc.`cno`
JOIN student 
ON sc.sno=student.`sno`
WHERE sc.score<60;
GROUP BY teacher.tname

8.6别名的使用

8.6.1表别名

SELECT a.tname,GROUP_CONCAT(student.sname)
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 a.tname='oldguo';

说明:
表别名一般是在 FROM的表的别名,或者join后的表的别名
在 where, group by ,select后的列,having,order by

8.6.2列别名
  例子:
SELECT a.tname AS 讲师,AVG(c.score) AS 平均分
FROM teacher AS a 
JOIN course  AS b
ON a.tno=b.tno
JOIN sc AS c 
ON b.cno=c.cno
GROUP BY a.tno
ORDER BY 平均分 ;
OVER   !   !   !

说明:
列别名一般是在select后的列,定义的别名
作用:

  1. 结果集显示会以别名形式展示
  2. 在 having 和order by中可以调用列别名

8.7外连接简介

left join
左表中所有的信息及匹配到右表的内容进行显示
right join

结论(优化小提示):

  1. 多表连接中,小表驱动大表
  2. 通过left join 强制选定驱动表

9.元数据获取

”基表“ 》》》》
数据字典信息（列结构frm），
系统状态，
对象状态

9.1show 语句 ）

语句 含义
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) #查看表的列定义信息
help show #查看帮助

9.2 information_schema 虚拟库

information_schema ---> views 视图
将查询元数据的语句封装成视图，当要获取此数据时，直接调用此视图。

9.2.1 详解TABLS 作用和结构

作用：存储整个数据库中，所有表的元数据的查询方式
desc tables; >>>>>>>>>

视图 含义
TABLE_SCHEMA 表所在的库
TABLE_NAME 表名
ENHING 存储引擎
TABLE_ROWS 表的行数
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度

例：对MySQL的数据库进行分库分表备份

#命令行语句
-- mysqldump -uroot -p123  world city >/backup/world_city.sql
#SQL语句，按表备份所有库
SELECT CONCAT("mysqldump -uroot -p123456 ",table_schema ," ",table_name ," >/backup/",table_schema,
"_",table_name,".sql")
FROM information_schema.tables INTO OUTFILE '/tmp/bak1.sql';
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,324评论 6赞 498
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,356评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,328评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,147评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,160评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,115评论 1赞 296
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,025评论 3赞 417
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,867评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,307评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,528评论 2赞 332
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,688评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,409评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,001评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,657评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,811评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,685评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,573评论 2赞 353