SQL基础应用

1.1 连接数据库

mysql常用参数：
-u? ? ? ? ? ? ?用户
-p? ? ? ? ? ? ?密码
-h? ? ? ? ? ? ?IP
-P? ? ? ? ? ? ?端口
-S? ? ? ? ? ? ?socket文件
-e? ? ? ? ? ? ?免交互执行命令
<? ? ? ? ? ? ? 导入SQL脚本

例子:

1. mysql -uroot -p -S /tmp/mysql.sock
2. mysql -uroot -p -h10.0.0.51 -P3306
3. -e 免交互执行sql语句

[root@db01 ~]# mysql -uroot -p -e "show databases;"

1. < 恢复数据
   [root@db01 ~]# mysql -uroot -p123 < /root/world.sql
   1.2 内置命令
   help 打印mysql帮助
   \c ctrl+c 结束上个命令运行
   \q quit; exit; ctrl+d 退出mysql
   \G 将数据竖起来显示
   source 恢复备份文件(source /root/world.sql)

2、SQL基础应用

2.1 SQL介绍

结构化的查询语言
关系型数据库通用的命令
遵循SQL92的标准(SQL_MODE)

2.2 SQL常用种类

DDL 数据定义语言 ???? create 和 drop
DCL 数据控制语言 grant 和 revoke
DML 数据操作语言 insert update 和 delete
DQL 数据查询语言???? select 和 show

3、SQL引入-数据库的逻辑结构

库
库名字
库属性:字符集,排序规则

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

4、字符集 (charset)

相当于MySQL的密码本(编码表)

show charset;
utf8 : 3个字节
utf8mb4 (建议): 4个字节,支持emoji

5、排序规则: collation

mysql> show collation;

对于英文字符串的,大小写的敏感
utf8mb4generalci 大小写不敏感
utf8mb4_bin 大小写敏感(存拼音,日文)

6、数据类型介绍

6.1 作用

保证数据的正确性和标准性

6.2 数值类型

整数
tinyint ： -128~127
int ：-2^31~231-1
浮点数
略

说明：手机号是无法存储到int的。一般是使用char类型来存储收集号

6.3 字符类型

char(100)
定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充
varchar(100)
变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间.
会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)

如何选择这两个数据类型?

1. 少于255个字符串长度,定长的列值,选择char
2. 多于255字符长度,变长的字符串,可以选择varchar

enum 枚举数据类型
address enum(‘sz‘,‘sh‘,‘bj‘.....)
1 2 3
悬念,以上数据类型可能会影响到索引的性能

6.4 时间类型

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。

列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
unique key ：唯一键
列值不能重复
unsigned ：无符号
针对数字列，非负数。

其他属性:
key :索引
可以在某列上建立索引，来优化查询

6.5 二进制类型

7、DDL 数据定义语言的应用

7.1 DDL-库的定义

7.1.1 创建数据库

CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin;

7.1.2 查看库情况

SHOW DATABASES;
SHOW CREATE DATABASE zabbix;

7.1.3 删除数据库(记住:不要在生产环境中操作)

DROP DATABASE syxk;

7.1.4 修改数据库字符集

注意: 一定是从小往大了改,比如utf8--->utf8mb4.
目标字符集一定是源字符集的严格超级.
CREATE DATABASE syxk;
SHOW CREATE DATABASE syxk;
ALTER DATABASE syxk CHARSET utf8mb4;

7.1.5 关于库定义规范 *

1.库名使用小写字符
2.库名不能以数字开头
3.不能是数据库内部的关键字
4.必须设置字符集.

7.2 DDL-表的定义

表名,列名,列属性,表属性

7.2.1 语法格式

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

7.2.2 列属性

PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
NOT NULL : 非空约束,不允许空值
UNIQUE KEY : 唯一键约束,不允许重复值
DEFAULT : 一般配合 NOT NULL 一起使用.
UNSIGNED : 无符号,针对数字列,非负数
COMMENT : 注释
AUTO_INCREMENT : 自增长的列

7.2.3 建表规范 *

1. 表名小写字母,不能数字开头,
2. 不能是保留字符,使用和业务有关的表名
3. 选择合适的数据类型及长度
4. 每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
5. 没个列设置注释
6. 表必须设置存储引擎和字符集
7. 主键列尽量是无关列数字列,最好是自增长
8. enum类型不要保存数字,只能是字符串类型

7.2.4 建表

CREATE TABLE stu (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
sname VARCHAR(255) NOT NULL COMMENT ‘姓名‘,
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘年龄‘,
gender ENUM(‘m‘,‘f‘,‘n‘) NOT NULL DEFAULT ‘n‘ COMMENT ‘性别‘,
intime DATETIME NOT NULL DEFAULT NOW() COMMENT ‘入学时间‘
)ENGINE INNODB CHARSET utf8mb4 COMMENT ‘学生表‘;

7.2.5 查询建表信息

SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu; #查看表结构

7.2.6 创建一个表结构一样的表

CREATE TABLE test LIKE stu;

7.2.7 删表(生产中禁用命令，危险！)

DROP TABLE test;

7.2.8 修改表

1.在stu表中添加qq列
DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT ‘qq号‘;

2.在sname后加微信列
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT ‘微信号‘ AFTER sname;

3.在id列前加一个新列num
ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT ‘身份证‘ FIRST ;
DESC stu;

4.把刚才添加的列都删掉(危险,生产中禁用操作)
ALTER TABLE stu DROP num;
DESC stu;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;

5.修改sname数据类型的属性
DESC stu;
ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT ‘姓名‘;

6.将gender 改为 sex 数据类型改为 CHAR 类型 ***
ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT ‘性别‘;

说明：修改字符集，修改后的字符集一定是原字符集的严格超集

7.2.9 注意事项

情况1：ALTER 语句是在原有数据的表中增加一个列，是需要对表结构进行更改，如果在生产环境中，这个表有可能是在频繁的访问的，还可能有一堆的业务过来，比如往里面插入行（增删改查），这种在线更改的DDL语句，是会进行锁表操作的。也就是这种语句是不合适在一个繁忙的时刻操作的（一定要避开生产的繁忙时刻） （建议：避开业务繁忙期）

情况 2：若紧急上线。操作流程

方式一：先将表的数据创建一个临时表，就是一模一样的表，copy出来（copy是不会锁表的，等于复制了一份，生成一个临时表）。然后进行临时表的变更，最后对原表进行替换。

方式二：pt-osc （工具）进行在线DDL，解决锁表的时间（另外8.0版本已经解决了这个问题）

?

8、DCL 数据控制语言的应用

8.1 grant

mysql> grant SELECT,INSERT,UPDATE,DELETE on wordpress.* to wordpress@‘10.0.0.%‘ identified by ‘123‘;

8.2 revoke

mysql> revoke delete on wordpress.* from ‘wordpress‘@‘10.0.0.%‘;
mysql> show grants for wordpress@‘10.0.0.%‘;

9、DML 数据操作语言的应用

9.1 insert

DESC stu;

1.最偷懒
INSERT stu VALUES(1,‘zs‘,18,‘m‘,NOW());
SELECT * FROM stu;

2.最规范
INSERT INTO stu(id,sname,age,sex,intime)
VALUES (2,‘ls‘,19,‘f‘,NOW());

3.针对性的录入数据
INSERT INTO stu(sname,age,sex)
VALUES (‘w5‘,11,‘m‘);

4.一次性录入多行
INSERT INTO stu(sname,age,sex)
VALUES
(‘aa‘,11,‘m‘),
(‘bb‘,12,‘f‘),
(‘cc‘,13,‘m‘);

9.2 update(一定要加where条件)

UPDATE stu SET sname=‘aaa‘;
SELECT * FROM stu;
UPDATE stu SET sname=‘bb‘ WHERE id=6;

9.3 delete (一定要有where条件)（危险！）

DELETE FROM stu;
DELETE FROM stu WHERE id=9;

生产中屏蔽delete功能
使用update替代delete
ALTER TABLE stu ADD isdel TINYINT DEFAULT 0 ;
UPDATE stu SET isdel=1 WHERE id=7;
SELECT * FROM stu WHERE is_del=0;

10、DQL 数据查询语言应用

select
show

10.1 select 语句的应用

10.1.1 select单独使用的情况

mysql> select @@basedir; #查看软件安装目录
mysql> select @@port; #查看当前数据库的端口
mysql> select @@innodb_flush_log_at_trx_commit;
mysql> show variables like ‘innodb%‘; #模糊查询
mysql> select database(); #查看当前的库
mysql> select now(); #查看当前的时间

10.1.2 select通用语法(单表)

select 列1,列2 from 表 where 条件 group by 条件 having 条件 order by 条件 limit

select user,count(name) from 表 where 列 group by user having 列 order by 列 limit;

语句顺序即如下：（不能乱） 可以单独使用.但这个顺序不能乱,不然语法错误。
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit

10.1.3 练习环境的说明

World 数据库
city 城市表
country 国家表
countrylanguage 国家的语言

city表结构 desc city;

ID : 城市序号/ID(1-...)
name : 城市名字
countrycode : 国家代码,例如:CHN,USA
district : 区域: 中国 省 美国 洲
population : 人口数

如何熟悉数据库业务?

1. 快速和研发人员打好关系 （方式/方法）
2. 找到领导要ER图 （表与表之间有连接关系，及多表关联）
3. DESC ,show create table
4. select * from city limit 5; 而要注意：select * from city （这个命令不要在生产环境中使用）

10.1.4 SELECT 配合FROM子句使用

select 列,列,列 from 表
例子:

1. 查询表中所有的信息(生产中几乎是没有这种需求的)
   USE world ;
   SELECT id,NAME ,countrycode ,district,population FROM city;
   或者:
   SELECT * FROM city; （这个命令最好不要在生产环境中使用）
2. 查询表中 name和population的值
   SELECT NAME ,population FROM city;

10.1.5 SELECT 配合WHERE 子句使用

select 列,列,列 from 表 where 过滤条件

1. where等值条件查询
   例子:

   查询中国所有的城市名和人口数
   SELECT NAME,population FROM city
   WHERE countrycode=‘CHN‘;

2. where 配合比较判断查询(> < >= <=)
   例子:

   世界上小于100人的城市名和人口数
   SELECT NAME,population FROM city

• WHERE population<100;

1. where 配合 逻辑连接符(and or)
   例子:

   查询中国人口数量大于1000w的城市名和人口
   SELECT NAME,population FROM city
   WHERE countrycode=‘CHN‘ AND population>8000000;

   查询中国或美国的城市名和人口数
   SELECT NAME,population FROM city
   WHERE countrycode=‘CHN‘ OR countrycode=‘USA‘;

   查询人口数量在500w到600w之间的城市名和人口数
   SELECT NAME,population FROM city
   WHERE population>5000000 AND population<6000000;
   或者:
   SELECT NAME,population FROM city
   WHERE population BETWEEN 5000000 AND 6000000;

2. where 配合 like 子句 模糊查询
   例子:

   查询一下contrycode中带有CH开头,城市信息

   SELECT * FROM city
   WHERE countrycode LIKE ‘CH%‘;

注意:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差
如果业务中有大量需求,我们用"ES(数据库)"来替代

1. where 配合 in 语句

例子:

查询中国或美国的城市信息.
SELECT NAME,population FROM city
WHERE countrycode=‘CHN‘ OR countrycode=‘USA‘;
或者:
SELECT NAME,population FROM city
WHERE countrycode IN (‘CHN‘ ,‘USA‘);

10.1.6 SELECT 配合GROUP BY 子句使用

作用：根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列。
常用的聚合函数：
max()：最大值
min()：最小值
avg()：平均值
sum()：总和
count()：个数
group_concat():该函数返回带有来自一个组的连接的非NULL值的字符串结果。（列转行） 在数据表中1对多是不允许的。如：
name age
张三 14
张三 15
在实际生活中，类似这种重名的现象是有的。但将其转为1行显示，及
name age
张三 14,15
这一种现象是不被允许的,在数据表中都是1对1的关系存储。遇见这类情况就需要使用到聚合函数group_concat().
GROUP BY + 聚合函数公式：
1.遇到统计想函数
2.形容词前 GROUP BY
3.函数中央是名词
4.列名select后添加

GROUP BY将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作.
例子:

1. 统计每个国家城市的个数
   SELECT countrycode ,COUNT(id) FROM city
   GROUP BY countrycode;
2. 统计每个国家的总人口数.
   SELECT countrycode,SUM(population) FROM city
   GROUP BY countrycode;
3. 统计每个国家省 的个数
   SELECT countrycode,COUNT(DISTINCT district) FROM city
   GROUP BY countrycode; #说明：第一个 DISTINCT 去重 第二个 district 省份
4. 统计中国每个省的总人口数
   SELECT district, SUM(population) FROM city
   WHERE countrycode=‘CHN‘
   GROUP BY district ;
5. 统计中国每个省城市的个数
   SELECT district, COUNT(NAME) FROM city
   WHERE countrycode=‘CHN‘
   GROUP BY district ;
6. 统计中国每个省城市的名字列表 guangdong guangzhou,shenzhen,foshan.... （使用GROUP_CONCAT()）

• SELECT district, GROUP_CONCAT(NAME) FROM city
  WHERE countrycode=‘CHN‘
  GROUP BY district ;
• #现象：将整个列显示的转换为行显示到一块(案例：如将班级中所有的人名打印到一行显示)

1. 小扩展

• anhui : hefei,huaian ....
  SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME)) FROM city
  WHERE countrycode=‘CHN‘
  GROUP BY district ;

10.1.7 SELECT 配合HAVING子句使用

语句顺序：where之后是group之后是having.不能是having之后有group

例子：

1.统计所有国家的总人口数量，将总人口数大于1亿的过滤出来。

SELECT countrycode.SUM(population) FROM city GROUP BY countrycode

HAVING SUM(population)>100000000;

10.1.8 SELECT 配合ORDER BY子句使用

例子:

1. 统计所有国家的总人口数量,
   将总人口数大于5000w的过滤出来,
   并且按照从大到小顺序排列
   SELECT countrycode,SUM(population) FROM city
   GROUP BY countrycode
   HAVING SUM(population)>50000000
   ORDER BY SUM(population) DESC ;

DESC ：从大到小

ASC : 从小到大

10.1.9 SELECT 配合LIMIT子句使用

例子:

1. 统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列,只显示前三名.

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3;

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3,3; #跳过前三行（从第四行开始），一共显示三行。

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3 OFFSET 3;

LIMIT M,N : 跳过M行,显示一共N行
LIMIT Y OFFSET X: 跳过X行,显示一共Y行

10.1.10 练习题

1. 统计中国每个省的总人口数，只打印总人口数小于100w的
   SELECT district ,SUM(population) FROM city
   WHERE countrycode=‘CHN‘
   GROUP BY district
   HAVING SUM(population)<1000000;
2. 查看中国所有的城市，并按人口数进行排序(从大到小)
   SELECT * FROM city WHERE countrycode=‘CHN‘
   ORDER BY population DESC;
3. 统计中国各个省的总人口数量，按照总人口从大到小排序
   SELECT district ,SUM(population) FROM city
   WHERE countrycode=‘CHN‘
   GROUP BY district
   ORDER BY SUM(population) DESC ;

1. 统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名
   SELECT district ,SUM(population) FROM city