day02-SQL语句

SQL语句

1.什么是sql

 关系型数据库当中通用的查询语言。全名：结构化查询语言。 

2.SQL标准

   SQL89 
   SQL92
   SQL99 
   SQL03

各个版本平台软件通用。关系型数据库方便移至，厂家定义的标准

3.SQL种类

  这三大类，所有数据库都具备的。

​	DDL  : 数据定义语言    删库
​	DCL  : 数据控制语言   
​	DML  : 数据操作     分为增删改查对表内容

4.SQL_MODE模板

   **sql_mode的模式：** 

  例子： 日期  不能出现  1920-01-00  

​        除法  : 除数不能为0 

 5.7+ 之后采用的是严格模式。 

$$
**作用：**        为了让我们SQL在执行时更加严谨、有意义，符合常识、逻辑、符合科学等。。。 在MYSQL存储和应用数据时,能够保证数据时准确有效的.如果我们在存数据的时候，发送了时间对不上呀什么的这个就会报错，更加严格。
$$
  **例子：** 日期  不能出现  1920-01-00  

​        除法  : 除数不能为0 


 查看SQL_MODE: 
 mysql> select @@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:  mysql> set sql_mode='';  # 去控制这个sql_mode 的里边内容
 mysql> select @@sql_mode;      # 查询

彩蛋:

# 各个版本的区别 
5.7版本之后加入了ONLY_FULL_GROUP_BY. 回答影响.回答为什么要这么做. 故障案例

5.字符集及校对规则

字符集 charset

# 编码对应表.  简单来说字符集就是翻译，一个编码格式，我们在存数据的时候最底层都是存二进制的，都会准换成二进制。

 utf8 和 utf8mb4 区别？

例如：
	utf8不完整，emoji表情字符是不支持， utf8mb4是支持的。
	根本原因是，utf8 字符最大长度为3字节，utf8mb4是4字节。

校对规则（排序规则）collation

 # 作用： 影响到了字符串的排序。 

6.数据类型

数字类型

             存储字节量                         范围 
tinyint   	   1                 	    0~255      -127~128
int            4                         0~2^32-1   -2^31 ~2^31-1
bigint         8                         0-2^64-1    

彩蛋: 
1. tinyint  int bigint  区别 都是整数类型
2. 浮点数存储是怎么做的. 
列：12434.5678 ---> 10000----> 12345678 
我们可以把他放大10000倍放到，存到整形里，取的时候我们 在放小10000倍.

字符类型

存放字符串的数据，很多数据都可转成字符串也可以存放到这里，只要是键盘有的都可以。

char(字符长度)
varchar(字符长度)

例子:  
           最多存储长度(utf8，utf8mb4)        额外占用     最大限制(最大字节)
char(10)           30                          0           255
varchar(10)        30                          1-2         65535

说明: 
	如果char(10) 和varchar(10) 都存满中文字符,char占用30字节 varchar占31
	

彩蛋:  char  varchar 的区别? 
char类型是定长数据类型.，对于数据插入的速度比较块，在有大量插入需求的业务（比如zabbix监控）推荐使用char类型

varchar 类型是变长的类型.按需分配存储空间.varchar会有额外的1-2字节存储字符长度.
对于数据查看的速度比较快，对于一般的互联网企业，查看数据的需求比较大，推荐使用。

enum() 枚举类型

#我们可以把一些字符串文字，可以把所有可能出现的值，定义好。这样可以数据库减少的字节数
 字符串我们都知道占3个字节，在这里我们把它分为按照数字来存放，也就不管字符串多长，都占一个字节，   但是如果这个字符串随时发生变化，就不要放这里面了，会带来不必要的麻烦。
 
应用场景： 列中的数据，有限个数的值的时候，并且是有规律。
例：
enum('山东省','河北省','陕西省',......)
		0        1        2
		

时间类型

#都是年月日时分秒的存储

timestamp     4个字节     定义出来的时间就是占4个字节    
datetime      8个字节     定义出来的时间就是占5个字节 

## 说明：
datetime 可以存储任意时间的时间戳，使用情况比较多。
timestamp 只能存储1970年1月1日之后的时间信息，且和时区有关。

补充:

 ## 
 合适的 
 简短的 
 足够的

7.表约束及属性

约束:

##
PK  : primary key 主键.非空且唯一,一张表只能一个主键.
UK  : unique  key 唯一键.  唯一约束，不能重复值 
NN  : not null    非空约束. 非空约束，必须录入值

属性:

##
auto_increment : 针对数字列，自动增长，一般配合主键
default        : 设置默认值，一般配合not null 使用
unsigned       : 针对数字列无符号
comment        : 注释 列或者表进行注释

DDL语句

2.1 库定义

2.1.1 增

#建库
CREATE DATABASE oldboy CHARSET utf8mb4;
CREATE SCHEMA oldguo CHARSET utf8mb4;

2.1.2 删

#删库
DROP DATABASE oldboy;

2.1.3 查询

#查库
SHOW DATABASES;
SHOW CREATE DATABASE oldguo;

2.1.4 改

#修改字符集
mysql> alter database test charset utf8;

2.2 表定义

2.2.1 增

可以用sqlyog，也可以在命令行：

USE test;   #进入库
CREATE TABLE stu(   #传创建表
sid      INT  NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',       
sname    VARCHAR(32) NOT NULL COMMENT '姓名',
sage     TINYINT UNSIGNED NOT NULL DEFAULT 99 COMMENT '年龄',
sgender  CHAR(1) NOT NULL DEFAULT 'M' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';

2.2.2 查看定义

查表定义
SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu; #desc后面是表名
---------------------------------------------------------------------------
# mysql> show create table student;
| student | CREATE TABLE `student` (
  `stuid` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `stuname` varchar(32) NOT NULL COMMENT '姓名',
  `stuage` tinyint unsigned NOT NULL DEFAULT '99' COMMENT '年龄',
  `gender` char(1) NOT NULL DEFAULT 'm' COMMENT '性别',
  `district` enum('bj','sh','tj','sz') NOT NULL DEFAULT 'bj' COMMENT '区域',
  PRIMARY KEY (`stuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;  

# mysql> desc stu;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| sid     | int              | NO   | PRI | NULL    | auto_increment |
| sname   | varchar(32)      | NO   |     | NULL    |                |
| sage    | tinyint unsigned | NO   |     | 99      |                |
| sgender | char(1)          | NO   |     | M       |                |
+---------+------------------+------+-----+---------+----------------+

2.2.3 修改

a. 添加列

--#1.  在最后添加一行。
 mysql> alter table stu（库表） add telnum char（类型）(11) not null（非空） unique comment '手机号'; 

-- #2. 在sname后添加a列
mysql> alter table stu add a int not null after sname; #指定位置出入一行这里是在sname后用after。

-- #3. 在第一列前添加b列
ALTER TABLE stu ADD   b INT NOT NULL  FIRST ;

b. 删除列(危险!!!)

#stu就是表后面接列
mysql> alter table stu drop a;   #删除a列
mysql> alter table stu drop b;   #删除b列

c. 修改数据类型

#修改sname的数据类型
#w我们在修改的时候，记住要把之前的参数有个要加所，例如你原本参数有不为空，你修改的时候没加，这个参数就会丢失。

mysql> alter table stu modify sname varchar(64) not null comment '姓名';

d. 修改列名 和 属性

#用change 
mysql> alter table stu change telnum tel char(12) not null unique comment '手机号'; 

注意:
8.0之前, alter 语句在业务繁忙期间做,会有很严重的锁的等问题

​ 5.6 5.7中变更时,做好是使用pt-osc 或者gh-ost 工具

2.2.4 删除表 (危险)

# drop table student;
# truncate table stu; 

# 区别:  
	a. drop      连表结构+数据全部清空
	b. truncate  只清空数据,保留表结构

3. DDL 规范(标准化）

键库：

CREATE DATABASE test CHARSET utf8mb4;

	#1.库名要与业务有关要有表注释. 

	#2.库名不使用大写字母、数字开头。

	#3.不要使用内置关键字

	#4.建库要指定字符集。

	#5.生产中禁止使用删库操作。 

建表:

CREATE TABLE stu (
id     INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname  VARCHAR(64) 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 COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
--------------------------------------------------------------------------
#1.表名：与业务有关,例如：wp_users，不使用大写字母、数字开头，不要太长（16以下）

#2.设置存储引擎、字符集、表注释

#3.表名、列名要使用内置关键字 

#4.列名要有意义，长度（16以下） 列名,要有业务相关度.

#5.数据类型：合适的、足够的、简短

#6.每个表必须要创建主键,最好是业务无关列,最好是数字自增列.

#7.每个列尽量是not null ，可以配合default（默认值）

#8.每个列要有注释

#9.修改定义的操作，要在业务不繁忙期间去做。如果紧急可以使用pt-osc 。
  #修改表结构,要在业务不繁忙去做,8.0建议使用pt-osc  gh-ost. 开发工具

DCL语句

是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

##  grant     授予用户权限    
##  revoke    撤销用户权限

DML语句

 ## 数据操作语言

1、作用：

    ##  对表中的数据行进行增、删、改

2.增 insert

#values 插一行
mysql> insert into stu values(1,'oldboy',48,'M','110');

#指定列插入 ，前面和后面是对应的
mysql> insert into stu(sname,sgender,tel) values('oldxu','F','119');

#批量录入数据，
mysql> insert into stu(sname,sgender,tel) values('oldguo','M','120'),('oldqiang','M','122');

彩蛋:

#创建个a表，like 复制的意思。
mysql> create table a like stu;

#复制stu表，的数据，复制到a表里，前提是表的结构要一样。
mysql> insert into a select * from stu;

3.改 update

#set设置的意思  where过滤的意思。，如果不加where全部更改，很危险
mysql> update stu set sage=18 where sid=5;  #把sid5这一行中的sage,改成18.
#注意：update语句必须要加where。

彩蛋: 安全更新参数

#开启安全功能，会提示你加where，不加会提示语法不正确。
mysql> select @@sql_safe_updates;

#生效命令，执行完重新连接数据库。
mysql> set global sql_safe_updates=1;

#我们可以在启动数据库的谁会加上，它就是个参数。


#说明: 在update/delete语句在更新时,必须要加where条件(索引列 #索引就是一本的目录。

4.delete删除

mysql> delete from stu where sid=6; 
#删除stu这个库里的sid=6这一行，删除了但是我们sid排序的时候，会跳过6这一行往下自增也就是直接是7.

彩蛋:

防止误删除的方案就是删除，看不见岂不是就是删除了吗，所以就是把它隐藏起来。

1. 伪删除: 用update替代delete

a. 添加状态列
mysql> alter table stu add state tinyint not null default 1 comment '状态列,1存在,0不存在';

b. 原删除语句替换
原来: delete from stu where sid=7;
替换为: update stu set state=0 where sid=7;

c. 原业务语句查询替换
原来:  select * from stu;
替换为: select * from stu where state=1;

#伪装删除，就是我们只是做了掩饰并没有真的删除，用初始的select * from stu;查看，还可以看到我们删除的哪一行，select * from stu where state=1; 用这个查看就看不到了。

面试题: drop truncate delete 区别?

### 
drop  table t1     : 表结构+数据. 立即释放空间
truncate table t1  : 清空数据,保留表结构,降低高水位.立即释放空间.
delete from t1     : 逻辑删除(删除标记).不会降低高水位.不会立即释放磁盘空间.

DQL应用(select)

1.SELECT

1.1）作用：

   ## 按需获取表中数据行. 

1.2）select单独用(MySQL独有的功能)

​ a. select 查询变量

mysql> select @@innodb_flush_log_at_trx_commit;

-- select @@xxx 查看系统参数
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;

#替代方案:
#查看全部变量
mysql> show variables；
#查看全部变量 like 过滤出我们能记住的字母 like == grop
mysql> show variables like '%trx%';

​ b. 调用函数

#select 可以调用函数，

#查看当前数据的版本
mysql> select version();

#查看当前登陆的用户
mysql> select user();

#concat 拼接，可以自定义在字符串，这里@可以随便变。
#作用：后期拼接一些语句。
mysql> select concat(user ,"@",host)from mysql.user;

​ c. 计算器

#只能做一些简单的运算。
、mysql> select 4*1024*1024*1024;

2.基本语法 (单表)

	## select 必须配合from使用。

2.1单表语句-from

在生产环境中，禁止使用 select * from

#select 必须配合from使用。

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

例子:

– 查询stu中所有的数据(不要对大表进行操作)

## SELECT * FROM stu ;

– 查询stu表中,学生姓名和入学时间

## SELECT sname , intime FROM stu;

学习环境准备

去官方文档可以咋找到，下载后直接拖到虚拟机里，进入数据库执行source /root/world.sql会生成一个world库。

#库
world   		世界

#库里面的表
city         	 城市
country      	 国家
countrylanguage  国家语言

#city（城市）表里面的内容。
mysql> desc city;
ID           	 城市编号
Name          	 城市名
CountryCode   	 国家编码(USA,JPN,CHN)
District      	 区域(省,州...)
Population    	 人口

3.单表子句-where

语法：

where 比较判断符(= ,> ,< ,>= , <=,!=)

where逻辑连接符（and 、or 、in 、between and ）
                并且   或           与
                in 从中选择其一。
# select 第一列，第二列 from 表 where 指定查看说明，类似grep
 
 select id,name from city where countrycode= 'CHN'; #查看city这个库里面的id和name，where指定查看CountryCode='CHN' 过滤式chn的展示他的id和名字。

示例：

where配合等值查询

— 查询中国所有的城市信息

#select * from city where countrycode= 'chn';

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

— 查询人口数小于100人的城市

#mysql> select * from city where population < 100;
+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 2912 | Adamstown | PCN         | –        |         42 |
+------+-----------+-------------+----------+------------+

– where 逻辑连接符(and , or , in , between and )

— 查询 中国 河北省的城市信息

#mysql> select * from city where countrycode= 'chn' and district= 'hebei';

—查询中国人口数在100w到200w之间的城市。

方法一：
mysql> select * from city where countrycode= 'CHN' and population >= 1000000 and population <= 2000000;

方法二：
# BETWEEN and 的意思式 在什么什么之间  前提是这两个必须是一致的，就是说取的东西是一样的
mysql> select * from city where countrycode= 'chn' and population BETWEEN 1000000 and 2000000 ; 

— 查询中国或者美国城市信息

方法一：
#or 或者的意思如果两个都存在则都打印出来。
mysql> select * from city where countrycode='chn' or countrycode='usa';

方法二：
#in 的意思是 in 和for是一样的都是或者的意思。
mysql> select * from city where countrycode in ('chn','usa');

方法三：
#
mysql> select * from city where countrycode='chn' union all select * from city where countrycode='usa';

彩蛋：UNION 和 UNION ALL 区别?

## UNION 自动去重复行 ,会有更多性能消耗(去重复意味着要排序)

 union 和 union all 是两个或多个查询结果的合并  只不过 union 是自动去重的意思，但是会很浪费  union all 就是不去重，有啥就什么相加 

where配合模糊查询

- 查询省的名字前面带guang开头的

注意：这个不能用在查看大表内容，系统容易崩溃。

mysql> select * from city where district like 'guang%';

#在大表中禁止出现双百分号的情况。会出事情。
mysql> select * from city where district like '$guang%';

4.group by + 常用聚合函数

作用：

## 根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

常用聚合函数

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

用法逻辑

--- GROUP BY + 聚合函数的执行逻辑
取数据--->排序--->去重----> 聚合
#先取数据 然后  把取到的数据进行排序，去重  然后在用聚合把剩下的在进行聚合。

示例：

— 统计每个国家的城市个数

#先把国家和id这两行提出来，然后执行，去重，排序，count是自增的意思。
mysql> select countrycode,count(id) from city group by countrycode;

— 统计中国每个省的城市个数

mysql> select district,count(id) from city where countrycode='chn' group by district;

— 统计每个国家的总人口数.

mysql> select countrycode,sum(population) from city group by countrycode;

— 统计中国每个省的城市个数和城市名

mysql> select district,count(id),group_concat(name) from city WHERE countrycode='CHN' GROUP BY district ;

面试题: 5.7+ 的 SQL_mode=only_full_group_by

ERROR 1055 (42000):
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘world.city.Name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解:

	1. 如果select 后的查询列表,不是group by 的条件,又不在聚合函数中存在.就会和SQL_mode不兼容 .
	如果group by 后的列是主键或唯一键时,可以忽略.
	2. 原理: 
		group by 执行过程
		取数据 ---> 对group by 的列进行排序和去重,在对其他列进行聚合,如果出现没有聚合的条件列值,结果集就会出现
		1个值对多个值,出现不规则表结构.

5.having

作用：

##  HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

##  AVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

例子：

统计中国每个省的总人口数，只打印总人口数小于10000000

mysql> select district,sum(population) from city where countrycode='chn' group by district having sum(population)< 10000000;

6.ORDER BY

作用：

##它可以直接加载where之后 也可以在having之后 按需添加。
   实现先排序，by后添加条件列

示例：

— 查询中国所有城市信息,并按人口数排序输出结果

#后面的asc 是从正序排序，默认就就是asc
mysql> select * from city where countrycode='chn' order by population asc ;

#后面的desc 是从倒序排序。
mysql> select * from city where countrycode='chn' order by population desc ;

— 统计每个国家的总人口,过滤输出总人口超过5000w的信息

##  mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc;

7.limit使用

作用：

#一般配合order by 使用，先排序然后在取行。
#limit 都是在最后的时候用，主要是取行例如前3 行 都是 limit 3.

例子：

— 统计每个国家的总人口,过滤输出总人口超过5000w的信息,只显示前三名

##  mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3;

— 统计每个国家的总人口,过滤输出总人口超过5000w的信息,只显示4到6名

方法一：
#3.3 的意思就是先跳过三行，在数三行
mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3,3;

方法二：
mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3 offset 3;

8.别名使用

怎么定义别名

AS 后面变量名

表别名

#这里我们把city表改为了 a 
SELECT countrycode,sum(population) 
FROM city AS a
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC 
LIMIT 3 OFFSET 3 ;

列别名

#这里我们把countrycode 定义成国家 SUM（population）定义成总人口。
select countrycode  AS '国家'，SUM（population） AS '总人口'
from city
group by countrycode
having 总人口>500000000
order by 总人口 DESC
limit 3,3;