4.SQL基础-1 SQL语句和单表查询

最新推荐文章于 2024-03-22 17:34:12 发布

xiaoleinb

最新推荐文章于 2024-03-22 17:34:12 发布

阅读量434

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/xiaoleinb/article/details/111997665

版权

mysql 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

学习准备
1.1 什么是SQL
结构化查询语言.关系型数据库通用语言.

1.2 SQL标准
SQL89
SQL92
SQL99
SQL03

1.3 SQL种类
DDL : 数据定义语言
DCL : 数据控制语言
DML : 数据操作

1.4 SQL_MODE
例如:
日期 : 不能出现 1920-01-00
除法 : 除数不能为0
在MYSQL存储和应用数据时,能够保证数据时准确有效的.
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

彩蛋:

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

1.5 字符集及校对(排序)规则
字符集(charset): 编码对应表.
排序规则(collation): 影响数据排序结果.

1.6 数据类型
a. 数字存储长度范围有负数
tinyint （存3位数） 1字节(8位) 0-255 -128~127 -2^7~27-1
int （存10位数） 4字节(32位) 0-2^32-1 -2^31~231-1
bigint （存20位数） 8字节 0-2^64-1

12434.5678 —> 10000----> 12345678

彩蛋:

tinyint int bigint 区别
相同：都是整数类型
不同：存储不同范围不同
浮点数存储是怎么做的.
存时先把浮点数数放大，取用数据时再将其缩小

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

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

彩蛋: char varchar 的区别?
char类型是定长数据类型.
varchar 类型是变长的类型.按需分配存储空间.varchar会有额外的1-2字节存储字符长度.

enum() 枚举类型
enum(‘山东省’,‘河北省’,‘陕西省’,…)
0 1 2

c. 时间类型
timestamp 4
datetime 8

补充:
合适简短足够

1.7 表约束及属性
约束:
PK : primary key 主键.非空且唯一,一张表只能一个主键.
UK : unique key 唯一键.
NN : not null 非空约束.

属性:
auto_increment : 自增
default : 默认值
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 查看定义
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. 添加列
mysql> alter table stu add telnum char(11) not null unique comment ‘手机号’;

mysql> alter table stu add a int not null after sname;
mysql> alter table stu add b int not null first;

b. 删除列(危险!!!)
mysql> alter table stu drop a;
mysql> alter table stu drop b;

c. 修改数据类型
mysql> alter table stu modify sname varchar(64) not null comment ‘姓名’;

d. 修改列名和属性
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 只清空数据,保留表结构

彩蛋:
DDL 规范(标准化)

建库: 库名,和业务有关.不要大写字母,不能用系统关键字,不能数字开头.显式的设置字符集.
建表:
a. 表名,和业务有关,要有表注释.
例如:jifeidb ----> jf_t1
b. 表名长度,16个字符以内.
c. 显式的设置存储引擎和字符集
d. 列名,要有业务相关度.
e. 数据类型: 合适简短足够
f. 每个表必须要创建主键,最好是业务无关列,最好是数字自增列.
g. 尽量对每个列进行Not null,并设置默认值
h. 每个列要有注释.
i. 修改表结构(Online-DDL),要在业务不繁忙去做,8.0建议使用pt-osc gh-ost.
DCL语句

grant
revoke

DML语句

4.1 增
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’);

彩蛋:
mysql> create table a like stu;
mysql> insert into a select * from stu;

4.2 改
mysql> update stu set sage=18 where sid=5;

彩蛋: 安全更新参数
mysql> select @@sql_safe_updates;
mysql> set global sql_safe_updates=1;

说明: 在update/delete语句在更新时,必须要加where条件(索引列)

4.3 删
mysql> delete from stu where sid=6;

彩蛋:

伪删除: 用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;

面试题: drop truncate delete 区别?

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

SELECT
5.1 作用
按需获取表中数据行.

5.2 select单独用(MySQL独有的功能)
a. select 查询变量
mysql> select @@datadir;
mysql> select @@innodb_flush_log_at_trx_commit;

替代方案:
mysql> show variables like ‘%trx%’;

b. 调用函数
mysql> select version();
mysql> select user();
mysql> select concat(user ,"@",host)from mysql.user;

c. 计算器
mysql> select 410241024*1024;

5.3 标准用法
5.3.1 基本语法 (单表)
select select_list
from
where
group by
having
order by
limit

5.3.2 学习环境准备
source /root/world.sql

world 世界
city 城市
mysql> desc city;
ID 城市编号
Name 城市名
CountryCode 国家编码(USA,JPN,CHN)
District 区域(省,州…)
Population 人口

5.3.2 SELECT + FROM 使用
– 查询city表中所有数据 ----> cat 大文件
USE world;
SELECT * FROM world.city;
SELECT id,NAME,countrycode, district ,population FROM world.city;
– 只查询name和population列值 -----> Awk $2 ,$5
SELECT NAME,population FROM world.city;

5.3.3 SELECT + FROM + WHERE 使用
mysql> DESC city;
ID 城市编号
NAME 城市名
CountryCode 国家编码(USA,JPN,CHN)
District 区域(省,州…)
Population 人口

– where 比较判断符(= ,> ,< ,>= , <=,!=)
— 查询中国所有的城市信息
SELECT * FROM city WHERE countrycode=‘CHN’;
— 查询人口数小于100人的城市
SELECT * FROM city WHERE Population<100;

– where 逻辑连接符(and , or , in , between and )
— 查询中国河北省的城市信息
SELECT
*
FROM
city
WHERE countrycode = ‘CHN’
AND district = ‘hebei’ ;

- - 查询中国人口数在100w到200w之间的.
    SELECT

FROM
city
WHERE countrycode = ‘CHN’
AND population >= 1000000
AND population <= 2000000 ;

SELECT
*
FROM
city
WHERE countrycode = ‘CHN’ AND
population BETWEEN 1000000 AND 2000000;

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

SELECT *FROM city
WHERE
countrycode=‘CHN’
OR
countrycode=‘USA’;

SELECT *FROM city
WHERE
countrycode IN (‘CHN’,‘USA’);

SELECT * FROM city WHERE countrycode = ‘CHN’
UNION ALL
SELECT * FROM city WHERE countrycode = ‘USA’;

— 彩蛋: UNION 和 UNION ALL 区别?
UNION 自动去重复行 ,会有更多性能消耗(去重复意味着要排序)

5.3.4 FROM + WHERE + GROUP BY + 聚合函数(COUNT(),SUM(),AVG(),MAX(),MIN(),GROUP_CONCAT() )
— GROUP BY + 聚合函数的执行逻辑
取数据—>排序—>去重----> 聚合

— 统计每个国家的城市个数
a. 分组列

SELECT countrycode,COUNT(id)
FROM city
GROUP BY countrycode ;

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

SELECT district,COUNT(id)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district ;

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

SELECT countrycode,SUM(population)
FROM city
GROUP BY countrycode;

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

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.3.5 ORDER BY
— 查询中国所有城市信息,并按人口数排序输出结果
SELECT * FROM city
WHERE countrycode = ‘CHN’
ORDER BY population ASC;

SELECT * FROM city
WHERE countrycode = ‘CHN’
ORDER BY population DESC ;

— 统计每个国家的总人口,过滤输出总人口超过5000w的信息
SELECT countrycode,SUM(population)
FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC ;

5.3.6 LIMIT 使用

— 统计每个国家的总人口,过滤输出总人口超过5000w的信息,只显示前三名
SELECT countrycode,SUM(population)
FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3 ;

4-6名:

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 ;

5.3.7 别名使用
a. 表别名
SELECT a.countrycode AS ct,SUM(a.population) AS sum_p
FROM city AS a
GROUP BY a.countrycode
HAVING sum_p>50000000
ORDER BY sum_p DESC
LIMIT 3 OFFSET 3 ;

b. 列别名

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