MySQL基础管理05之SQL基础应用

MySQL基础管理05之SQL基础应用

1.SQL常识

  • SQL介绍
属于关系型数据库产品中专用的语句。结构化查询语句。
  • SQL标准
SQL-89  \ SQL-92  \  SQL-99   \SQL-03 .....
  • sql_mode
- 作用
影响到了SQL语句的执行行为。为了让数据库在存、取能够满足生活的常识、科学的逻辑,让这些数据有意义。
例子: 
	现实生活中常识:
		日期: 1000-9999 年 1-12 月 1-31日 
	科学逻辑: 		
		除法:除数不能为0
- 查看sql_mode 
mysql> select @@sql_mode;
ONLY_FULL_GROUP_BY          :  针对group by 语句执行时的规范
STRICT_TRANS_TABLES         :  针对事务表启动严格模式
NO_ZERO_IN_DATE             : 2010-00-10
NO_ZERO_DATE                : 0000-00-00
ERROR_FOR_DIVISION_BY_ZERO  : 除数不能为0
NO_AUTO_CREATE_USER         : 是否自动创建用户
NO_ENGINE_SUBSTITUTION      :  建表是使用了一个不支持的存储引擎报错。

2.数据库对象属性介绍

  • 数据库对象
databases
	库名
	库属性:字符集、校对规则

tables
	表名 
	列 :
		列名
		列属性:数据类型、约束、其他属性
	行
	表属性:引擎、字符集、校对规则、其他
  • 字符集
# 作用 
MySQL 中的 “密码本”
2.2.2 种类
utf8    not  UTF-8  
utf8mb4 is   UTF-8(万国码)

# 特点 : 
1. utf8   , 最大支持3字节的字符。不支持emoji字符
2. utf8mb4, 最大支持4字节的字符。支持emoji字符。

8.0之前,默认字符集latin1,8.0之后是utf8mb4。
我们建议使用utf8mb4.

# 设置方法
 默认字符集参数
 建库
 建表
  • 校对规则
# 作用 
影响到数据的排序方式。
	ASCII码
a    97
b    98
c    99
d    100

A    65
B    66
C    67
D    68

# 查看
mysql> show collation;
  • 列属性
数据类型 
#  作用 
尽可能保证数据是有意义的。
#  种类 
## 数字类型
整数:
	tinyint   1字节  =  8位 = 00000000 - 11111111  = 2^8个  = 0  - 255     , -127-128    
	int       4字节  =  32位                       = 2^32个 = 0  - 2^32-1  ,-2^31-2^31-1 ,10位数
	bigint    8字节                                           0  - 2^64-1  ,-2^63-2^63-1 ,20位数
浮点数:
	decimal(m,n)
	
## 字符串类型
char(N)    : 
	N字符个数,最大不超过255
	定长的字符串类型。
	例如: char(10) ,最多存10个字符,只要10个字符以内,都10个字符长度的存储空间。剩余用空格填充。
varchar(M) :
	M字符个数,最大不超过65535
	变长的字符串类型。会额外占用1-2字节存储字符长度。255字符之内,额外1字节,255以上,额外2字节
	例如: varchar(10),最多存10个字符,按需分配存储空间。
	abc 3             =4
	asdasdadasd 1000  =1002
	
enum('bj','sh','tj','heibei','henan',.....)
district enum('bj','sh','tj','heibei','henan',.....)

## 时间类型 
datetime    8字节 
timestamp   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。
timestamp会受到时区的影响

## 二进制类型
## json 数据类型

数据类型选型: 合适的、足够的、简短的
# 约束 
# 主键 primary key (PK)  : 
1. 一张表只能有一个,可以有多个列构成
2. 特点: 非空+唯一
3. 建议每张表都设置主键,有利于索引的应用,通常是使用自增的数字列更佳。

# 非空 not null :
1. 特点: 必须录入值。
2. 建议: 每个列最好设置为,有利于索引的应用。

# 唯一 unique key :
1. 特点: 不能有重复值。

# 无符号 unsigned
1. 特点: 针对数字列,无符号设定。
# 其他
表属性 : 
		engine  : 存储引擎设置,默认是innodb,也是我们建议的。
		charset : utf8mb4 
		comment :  注释。
列属性 : 
	    default       :  默认值。一般是在not null 配合使用
		auto_increment: 数字列自增长。一般是在主键列配合使用
		comment       : 列的注释,建议每个列都有

3.SQL种类

DDL  : 数据定义语言
    对于: 库 、表(元数据)的 增、删、改
    建库、删库、修改库、建表、删表、修改表
DCL  : 数据控制语言
    grant 
    revoke 
DML  : 数据操作语言
	表的数据行进行的增、删、改、查
DQL  : 数据查询语言(属于DML)
    对于表数据行查看
    对于元数据查看

4.DDL语句

数据定义语言,对于: 库 、表(元数据)的 增、删、改
建库、删库、修改库、建表、删表、修改表

①库定义

  • 库定义规范
1. 库名不能数字开头
2. 库名要和业务有关
3. 库名不要有大写字符
   原因:为了多平台兼容。
4. 建库需要显示指定字符集。建议是utf8mb4。	
5. 生产中禁用普通用户的drop database权限。
  • 建库
CREATE DATABASE school CHARSET utf8mb4;
  • 字符集设置
# 默认字符集设置
vim /etc/my.cnf
[mysqld]
character_set_server=utf8mb4
init_connect = "SET NAMES 'utf8mb4'"
  • 改库
CREATE DATABASE oldli ;
SHOW CREATE DATABASE oldli;
ALTER DATABASE oldli CHARSET utf8mb4;
SHOW CREATE DATABASE oldli;
  • 删库跑路
DROP DATABASE oldli;
  • 查库
show databases;
SHOW CREATE DATABASE oldli;

②表定义

  • 表定义规范
1. 建表规范  
	a. 表名: 
		不能数字开头
		业务有关
		不要大写字母
		不要超过18字符
		不能是关键字
	b. 存储引擎使用InnoDB
	c. 5.7版本以后,字符集使用utf8mb4
	d. 列名要和业务有关,不要超过18个字符
	e. 选择合适、足够、简短数据类型
	f. 建议每个列设置not null
	g. 每个列要有注释
	h. 每个表要有主键
	i. 针对not null 列,可以设定默认值。
	j. 表注释	

2. 修改表 
	a. 添加列,使用追加式添加列 
	b. 修改列属性,尽量使用modify语句
	c. 修改表定义,建议在业务不繁忙期间进行。尽量采用pt-osc或者gh-ost工具减少业务影响。

推荐工具软件(了解): 
yearing   开源SQL审核工具。
inception SQL审核工具。
  • 建表
use oldli;
Create table `oldli`.`student`
( 
`id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
 `name` varchar(64) NOT NULL COMMENT '学生姓名',
 `age` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '学生年龄',
 `gender` CHAR(1) NOT NULL DEFAULT 'n' COMMENT '学生性别',
 `address` enum('北京','天津','上海','重庆','河北','河南','山东','未知') NOT NULL DEFAULT '未知' COMMENT '省份', 
 `intime` datetime NOT NULL default now() COMMENT '入学时间',
 `shenfenzheng` CHAR(18) NOT NULL COMMENT '身份证',
 `stunum` int(10) UNSIGNED ZEROFILL NOT NULL COMMENT '学号', 
 primary key (`id`) 
) ENGINE=InnoDB charset=utf8mb4 collate=utf8mb4_bin; 
  • 克隆表
# 仅克隆表结构
use oldli;
create table stu like student;
  • 查看表或表结构
use oldli;
show tables;
show create table student;  # 命令行
desc student;
  • 修改表定义
# 即是修改表定义的元数据
-- 添加列
use oldli
alter table oldli.student 
add column telnum CHAR(11) not null unique key default '0000' comment '手机号' ;

# 不推荐方式
# after和first添加列,代价大,慎用!
alter table oldli.student 
add column a CHAR(11) not null unique key default '0' comment '手机号' after gender ;
desc student;
alter table oldli.student 
add column b CHAR(11) not null unique key default '0' comment '手机号' first ;
desc student;

-- 删除列 (不代表生产操作,危险!!!!)
alter table student drop  telnum;
  • 修改表属性
-- 修改表名 
alter table student rename to st;

-- 修改引擎  *****
create table t1(id int)engine=myisam;
show create table t1;
alter table t1 engine=innodb;

-- 修改字符集
create table t2(id int)charset=utf8;
show create table t2;
alter table t2 charset=utf8mb4;
  • 修改列属性
desc st;
--- 修改列名
alter table st change shenfenzheng cardnum CHAR(18) not null default '0' comment '身份证';
# change可以修改所有列的属性(通过重建表来修改),代价大!
# modify 代价比change小很多,性能高

--- 修改默认值
alter table st change cardnum cardnum CHAR(18) not null default '1' comment '身份证';

--- 修改数据类型 *****
alter table st modify cardnum CHAR(20) not null default '1' comment '身份证';


#  删表(危险操作!! 普通用户禁用 drop 操作)
drop table stu;

5.DML语句

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

①insert

insert into 
st(id,name,age,gender,address,intime,cardnum,num)
values(1,'张飞',18,'m','北京','2020-04-27','666666',10);
select * from st;

# 表字段均为非空,但是基本设置了默认值,所以可以省略掉,不需要再插入记录的时候写出来所有字段
insert into 
    st(name,intime,num)
values('诸葛亮',NOW(),1111);
select * from st;

②update

update st set name='郭奉孝'  where id=4;
select * from st;

update st set name='张文远' , age=21  where id=4;
select * from st;

③delete

-- 删除记录
delete from st  where id=5;
select * from st;

-- 伪删除: update 替代 delete ,添加状态列,1带表存在,0代表删除
alter table student 
add column status tinyint(1) not null default 1 comment '状态列:0表示删除,1表示存在' ;
update student set status=0 where id =4;
select * from student where status = 1;

④select见DQL

⑤彩蛋

drop table t1 ,truncate table t1 , delete from t1 区别 ?

  • drop table t1;
作用: 1. 删除所有表数据,删除整个表段(rm ibd  ),属于物理性质,会释放磁盘空间。
	  2. 删除表定义 (rm  frm , 元数据也会被删除)
  • truncate table t1 ;
作用: 保留表结构,清空表段中的数据页。属于物理删除,会释放磁盘空间。	
  • delete from t1;
作用: 删除数据行。逐行删除。保留表结构,属于逻辑性质删除。只是标记删除,不会立即释放磁盘空间。
所以delete 操作会产生碎片,可以通过日志反转来恢复,也就是insert

6.DQL语句

数据查询语句
实验SQL脚本下载

①查询数据库服务器配置参数

select @@port;
select @@server_id;
select @@basedir;
select @@datadir;
select @@socket;
select @@innodb_flush_log_at_trx_commit;

show variables;
show variables like '%trx%';

②查询内置函数

select DATABASE();
select NOW();
select USER();
select CONCAT("hello world");
select user,host from mysql.user;
select CONCAT("数据库用户:",user,"@",host,plugin,";") from mysql.user;

③select使用

  • 查看表定义
mysql>use world;
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

生产中熟悉业务: 
  1. comment 
  2. desc table_name,简单查询表中数据,对比与猜测字段含义
  3. E-R关系图
  • select配合from子句使用
select name,population from city where id < 10 order by id;
select * from city where countrycode = 'CHN' order by id;
update city set population='24281400' where id=1890;
select * from city where population < 100;
  • select + from + where 子句使用
-- where 配合 比较 操作符(= != > < >= <=)
-- 例子:查询city表中,所有中国的城市信息。 
select *  from city where countrycode = 'CHN';

-- 例子:查询人口数小于100人城市信息
select * from city where population<100;

-- where 配合逻辑连接符(and, or , between and),实现多条件过滤
-- 例子:查询中国,人口数超过500w的所有城市信息
select * from city where countrycode='CHN' and  population>5000000;

-- 例子:查询中国或美国的城市信息。
 select * from city where countrycode='CHN'  or countrycode='USA' ;
# 或者
 select * from city where countrycode in ('CHN','USA');

-- 例子:查询人口数为100w-200w(包括两头)城市信息
select * from city where population >= 1000000 and population <= 2000000 
# 或者: 
select * from city where population between 1000000 and  2000000 ;

-- 例子 :查询中国或美国,人口数大于500w的城市 
SELECT * FROM city WHERE (countrycode='CNH' OR countrycode='USA' ) AND population>5000000;
select * from world.city where countrycode in ('CHN','USA') and population >= 5000000;
  • where 配合 like 语句实现模糊查询
-- 模糊查询like(字符串列)
select * from city where name like 'qing%';

-- 下面的查询生产慎用,不走索引,需要把整个表加载内存后查询,资源消耗甚大!!
-- 如果业务用用到很多一下的匹配,可以使用es来解决!!!
-- 同理,正则使用于sql查询,也是资源开销大,不走索引,慎用!
select * from city where name like '%qing%';
  • select + from + group by + 聚合函数
# 聚合函数: 
count()            统计数量
sum()              求和
avg()              平均数
max()              最大值
min()              最小值
group_concat()     列转行
--- group  by  分组功能原理
1. 按照分组条件进行排序
2. 进行分组列的去重复
3. 聚合函数将其他列的结果进行聚合。
--- SQL_MODE=only_full_group 来保证group by语句准确性。
-- 例子: 统计city表的行数
select COUNT(*) from city;

-- 例子: 统计中国城市的个数
select COUNT(*) from city where countrycode='CHN';

-- 例子: 统计中国的总人口数。
select SUM(population) from city where countrycode='CHN';

-- 例子:统计每个国家的城市个数。
select countrycode,COUNT(name) from city group by countrycode;
--- 练习: 统计每个国家的总人口数。
--- 练习: 中国每个省的城市个数,和总人口数。
--- 练习: 统计中国每个省的城市个数及城市名列表
select CountryCode as '国家',count(name) as '城市总数',sum(population) as '人口总数' 
from city group by CountryCode;
select CountryCode as '国家',sum(population) as '人口总数' from city group by CountryCode;

select district as '省份/直辖市',count(name) as '城市总数',sum(population) as '省/直辖市人口总数' 
from city 
where countrycode='chn' group by  district;

select district as '省份/直辖市',count(name) as '城市总数',group_concat(name) as '城市列表' 
from city 
where countrycode='chn' group by  district;

注意: MySQL远程连接工具会屏蔽掉sql_mode

--- 练习3: 统计中国每个省的城市个数及城市名列表
mysql> select district, COUNT(name),name
    -> from city 
    -> where countrycode='CHN'  group by district ;
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
mysql> 

报错说明: 5.7 之后的SQL对于group by语句的限制。
1. 没有在group by 后
2. 同时没有在函数中聚合操作,违反了SQL_MODE=only_full_group_by

如果在远程工具中,会默认取name的第一个值显示出来。
  • having子句
having子句  不走索引,影响性能,受结果集影响。慎用!
--- 类似where,但是和where的执行顺序不一样,后过滤,应用在group by之后做判断。
# SQL语句执行顺序
--- select select_list from  where  group by  having  order by limit
---   4     1           2      3        5        6       7       8
# 显示总人口超过1亿人的国家
select countrycode,sum(population)
from city
group by countrycode
having sum(population)>100000000;
  • order by语句
# 不走索引,查询代价有点高啊,让开发去该需求去!
--- 查询城市信息 并按照人口数排序
select * from city order by population;

select * from city where countrycode='chn' order by population desc;

select countrycode,sum(population)
from city
group by countrycode
having sum(population)>50000000
order by sum(population) asc limit 10;
  • limit分页
--- limit 分页,配合order by使用
--- limit M,N : 跳过M行,显示N行
--- limit N offset M : 跳过M行,显示N行
--- limit 5000000,10 大表中这种语句非常耗费资源,需要先过滤前面500w的数据,最终显示10行数据,不要使用
# desc 降序
# asc 升序
-- 例子: 查询中国所有的城市信息,并按照人口数从大到小排序输出,只显示前十名。
select * 
from city 
where countrycode = 'CHN' 
order by population desc 
limit 10 ;

-- 显示6-10名
select * 
from city 
where countrycode = 'CHN' 
order by population desc 
limit 5,5

-- 后五名
select * from city 
where countrycode='chn'
order by population asc limit 5;