SQL基础应用

最新推荐文章于 2024-05-23 20:16:10 发布

姬凤

最新推荐文章于 2024-05-23 20:16:10 发布

阅读量120

点赞数

分类专栏： DBA 文章标签： mysql

本文链接：https://blog.csdn.net/qq_42944130/article/details/106113914

版权

DBA 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

SQL基础应用

1.1 什么是SQL？

结构化查询语言。MySQL内置的命令。数据库管理、对象操作。
是关系型数据库通用的语言。
SQL规范: SQL89 SQL92 SQL99 SQL03

1.2 SQL 常用种类（填空题）

1.2.1 DDL ：数据定义语言

库：增、删、改
表：增、删、改
主要是针对以上对象的结构、属性的定义。

1.2.2 DCL ：数据控制语言

对于权限相关管理。
grant revoke

1.2.3 DML ：数据操作语言

数据行：增、删、改、查

1.2.4 DQL ：数据查询语言（oldguo）

select
show

1.3 学习前导内容

1.3.1 SQL_MODE

用来规范SQL使用的模式。在操作SQL语句是能够符合现实常识和科学的逻辑。
例如：
日期：2009-07-20
年： 1000-9999
月： 01-12
日： 1-31

计算： 1/0 除数不能为0

严格模式参数：
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

1.3.2 对象属性–字符集和校对规则

作用对象：库，表
字符集是存储数据的编码格式。
UTF8 : 不是传统意义的UTF8,只支持最大3个字节的字符。不支持emoji。
UTF8MB4 ：传统意义的UTF8,支持最大4个字节长度的字符。例如emoji。

校对规则：影响到数据的排序

Acd
abc
Bcd
ABB

敏感：ASCII

ABB
Acd
Bcd
abc

不敏感：ASCII

ABB
ABC
ACD
BCD

1.3.3 对象属性–数据类型

作用对象：

表。
常用数据类型：
数字类型：
tinyint : 1字节： 8位二进制数字 --》10进制 00000000-11111111 2^8-1 0-255,-2^7-27-1,3位数
int ：4个字节： 32位二进制数字–》10进制 0-2^32-1 , -2^31~231-1，10位数
bigint ：8个字节： 64位二进制数字–》10进制 0-2^64-1 , -2^63~263-1，20位数
字符串：
char(20) : 最多存储20个字符。最多支持255个字符。定长。
varchar(20)：最多存储20个字符。最多支持65535个字符。变长。额外占用1-2字节长度存储字符长度。
abcdef—》6个字节 + 1字节
虽然理论上varchar(65535) 但是我们建议，还是不要超过255，会自动转换成TEXT，导致无法创建
BTREE索引，只能使用全文索引。我们建议使用ES数据库存储大字段。

彩蛋一：varchar(255)的问题？

1. InnoDB存储引擎的表索引单一字段或前缀长度，最长是767字节(bytes)。
存中文时，utf8字符集，那么767/3=255,

如果是utf8mb4应当是，767/4=191.

2. 字符串长度值视实际数据长度，需占用 1 或 2 个字节存储。
当实际数据长度 <= 255 时，varchar字段长度 = 1 + N
当实际数据长度 > 255 时，varchar字段长度 = 2 + N

总结：其实是很多开发人员的一些习惯吧，但是往往不知道原因。所以呢带大家普及一下。

彩蛋二： char和varchar选择

  1. 将来存储的字符串是定长，更加建议使用char().
	2. 变长的字符串，更加建议varchar().
	
数据类型的选择，将来可能影响到索引的应用。特别是大表的索引。
为什么呢？ ----》 索引章节揭晓。


enum('bj','sh') 枚举类型

id  name   telnum   addr enum('bj','sh')
1    zs    1110     0     0     1 
1    zs    1110     1
1    zs    1110     2
                    .....

时间  ： 
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会受到时区的影响

1.3.3 对象属性–约束和其他属性

作用对象：表。

（1）Primary KEY：主键约束 PK
特点：非空、唯一
一般建议是自增长的数字列。
AUTO_INCREMENT
每张表只能一个主键。
（2）NOT NULL 非空
（3）Unique KEY 唯一约束
（4）unsigned 无符号约束，只是针对数字类型。
（5）default 默认值
（6）comment 注释
（7）表字符集，引擎，校对规则，表的注释。

1. SQL基础

1.1 mysql连接工具内置功能

每一个客户端连接都会开启一个线程,并显示状态
1.1.1 专用参数
-u -p -h -P -S -e <
mysql -uroot -p123 -S /tmp/mysql.sock
mysql -uoldguo -p123 -h10.0.0.51 -P3306
mysql -uroot -p123 -S /tmp/mysql.sock -e “select @@port”
mysql -uroot -p123 -S /tmp/mysql.sock </root/world.sql

1.1.2 mysql 命令窗口自带命令

List of all MySQL commands:
Note that all text commands must be first on line and end with ‘;’
? (?) Synonym for `help’.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don’t write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (#) Rebuild completion hash.
source (.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don’t show warnings after every statement.

日常功能：

help 打印帮助
ctrl+c 结束上个命令运行
ctrl+d 退出mysql会话窗口
\G 数据行以键值对方式，垂直显示。
tee 记录操作日志
system 调用OS命令
use 进入到某个库

1.2 DDL 数据定义语言

1.2.1 操作对象

库、表。

1.2.2 库的定义
--- 1. 创建库
CREATE DATABASE wordpress CHARSET utf8mb4;
--- 2. 查询库定义
SHOW DATABASES; -- 查看库名
SHOW CREATE DATABASE wordpress; -- 查看建库的具体信息
--- 3. 修改库定义 
SHOW CREATE DATABASE oldboy;
ALTER DATABASE oldboy CHARSET utf8mb4;
--- 4. 删库
DROP DATABASE oldboy;

开发规范：

	1. 库名： 
	业务有关、
	不要有数字开头、
	不能有大写字母、
	不要超过18个字符、
	不要用系统关键字。
	2. 必须设置字符集。建议使用UTF8MB4。
	3. 修改字符集需要严格超集。
    4. 生产业务中禁用DROP操作。

# 1.2.3 表的定义

— 1. 创建表

use school;
CREATE TABLE `sch_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(64) NOT NULL COMMENT '学生姓名',
  `age` tinyint(4) NOT NULL DEFAULT '0' COMMENT '学生年龄',
  `gender` char(1) NOT NULL DEFAULT 'N' COMMENT '学生性别',
  `addr` enum('北京市','上海市','天津市','重庆市') NOT NULL DEFAULT '北京市' COMMENT '学员地址',
  `intime` datetime DEFAULT NULL COMMENT '入学时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 comment '学生表';

建表规范：

	1. 表名： 
	业务有关、
	不要有数字开头、
	不能有大写字母、
	不要超过18个字符、
	不要用系统关键字。
    2. 列名： 和业务功能有关，不要超过18字符
	3. 选择合适的、简短的、足够的数据类型。
	4. 必须要有主键
	5. 每个列进行非空约束
	6. 设置默认值。
    7. 每个列要加注释。
    8. 表属性：引擎、字符集、表注释

DBA的职责，SQL审核工作及修改建议。

— 克隆表（表结构）

CREATE TABLE t1 LIKE sch_student;

— 2. 查询表定义

DESC sch_student;
SHOW CREATE TABLE sch_student;
SHOW TABLES;

— 表状态查询

SHOW TABLE STATUS LIKE 't%';            --  表状态
SELECT * FROM mysql.innodb_table_stats; --  统计信息

— 3. 修改表定义

--- 添加列
ALTER TABLE t1 ADD telnum CHAR(11) NOT NULL UNIQUE KEY COMMENT '手机号';
DESC t1;

--- 修改列的数据类型
ALTER TABLE t1 MODIFY NAME VARCHAR(32) NOT NULL COMMENT '学员姓名';
--- 修改列名name为sname
ALTER TABLE t1 CHANGE NAME sname VARCHAR(64) NOT NULL COMMENT '学员姓名';
--- 删除列 
ALTER TABLE t1 DROP telnum;

— 4. 删除表

DROP TABLE t1;
SHOW TABLES;

注意：  Online-DDL处理细节
	修改表定义，即对表的结构修改： 添加、删除列、修改列属性、修改列名，索引管理等。
	在操作时，是需要进行表的加锁。在业务繁忙期间是不建议做的。
	如果必须要做的话，建议ONLINE DDL操作。我们更推荐使用第三方的工具，例如：PT-OSC,GH-OST工具进行
	Online DDL的操作，减少对线上业务的影响。

扩展作业： pt-osc 和 gh-ost应用及工作原理
https://www.jianshu.com/p/c97228b6f60c

面试题：有DDL操作紧急上线，处理方案。

以上SQL语句均属于对xxx 表的添加列的DDL操作
语句在执行过程中会产生matedata lock+ table相关锁
建议执行此类语句时，5.6版本以上，建议开启OnLine-DDL
或者pt相关工具功能进行操作。尽量在业务不繁忙期间运行。以减少对于线上业务影响。
根据题目意思，需要紧急上线。所以建议使用PT-OSC或者GH-OST的工具进行DDL操作。
以减少对于线上业务影响。

1.3 DML

1.3.1 作用

针对表的数据行，进行的增、删、改、查操作
1.3.2 简单数据查询

mysql> select user ,host from mysql.user;
mysql> select *  from mysql.user;

1.3.3 插入数据

--- 标准的方式
USE school;
DESC sch_student;
INSERT INTO 
sch_student(id,NAME,age,gender,addr,intime)
VALUES(1,'zhang3',18,'M','北京市','2020-03-17 9:00:00');
SELECT * FROM sch_student;
--- 简化的插入方法
INSERT INTO 
sch_student
VALUES(2,'zhang4',18,'F','北京市','2020-03-16 9:00:00');

--- 按列插入数据
INSERT INTO 
sch_student(NAME,intime)
VALUES('li4',NOW())

SELECT * FROM sch_student;

--- 批量插入数据
INSERT INTO 
sch_student(NAME,intime)
VALUES('a',NOW()),('b',NOW())
,('c',NOW());

1.3.4 更新 update

UPDATE sch_student 
SET NAME='wang5' 
WHERE id=4 ;
SELECT * FROM sch_student;
注意： update语句必须加where条件。

1.3.5 删除 delete

DELETE FROM sch_student WHERE id=6;
SELECT * FROM sch_student;
注意： 必须要加where条件。

— 彩蛋1：能不能不删除？删除操作，有没有替代方案？
SELECT * FROM sch_student ;
— 1. 原来业务语句
DELETE FROM sch_student WHERE id=5;
SELECT * FROM sch_student ;
— 2. 添加一个状态列
ALTER TABLE sch_student
ADD state TINYINT NOT NULL DEFAULT 1
COMMENT ‘1代表存在,0代表删除！’;
— 3. 替换原来业务语句
UPDATE sch_student SET state=0 WHERE id=5;
SELECT * FROM sch_student WHERE state=1;

— 彩蛋2：请说明以下语句的区别？
DROP TABLE t1; : 删除的是表定义+所有数据行。物理。立即释放磁盘空间
DELETE FROM t1; ：逐行删除表中的数据。逻辑。仅仅是打上了删除的标签，不会立即释放空间而且会产生大量日志。
trucate TABLE t1; 清空表的数据页。物理。立即释放磁盘空间

1.4 DQL 数据查询语言

1.4.1 作用

获取表中数据行。

1.4.2 类型

select
show

1.4.3 select 语句应用

— 1. 单独使用

--- 1.1 查询数据库参数
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES;
SHOW VARIABLES LIKE '%trx%';

— 1.2 调用函数

USE mysql
SELECT DATABASE();
SELECT NOW();
SELECT CONCAT("hello !!")
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;

2. SELECT 单表标准语法

2.1 SELECT 语句的多子句执行逻辑

SELECT 
--> FROM     表1,表2,...
--> WHERE 
--> GROUP BY 
--> HAVING 
--> ORDER BY 
--> LIMIT

— 彩蛋：如何了解业务？
— 最好的办法是有注释。
— world.city
USE world;
SHOW TABLES;
— city 城市信息表
DESC city;
---- id : 主键，城市编号。
---- NAME : 城市名
---- countrycode : 国家编码？CHN、USA
---- district : 区域省
---- population : 人口

SELECT * FROM city WHERE NAME=‘shenyang’;
SELECT * FROM city WHERE NAME=‘jinan’;

2.2 SELECT 配合 FROM 使用

--- 例子1： 查询city表中所有数据(不代表生产操作！！！)
SELECT * FROM world.city;  
或者：
USE world;
SELECT * FROM city;
--- 注意：大表不要使用全表查询。

--- 例子2： 查询city表中每个城市的人口数。
SELECT NAME,population FROM world.city;

--- 2.3 SELECT + FROM + WHERE 应用
--- 2.3.1 WHERE 等值条件查询 
--- 例子3：查询中国所有城市信息 
SELECT  *  FROM world.city 
WHERE countrycode='CHN';

--- 2.3.2 WHERE 条件判断符( > < >= <= != <> )
--- 例子4： 查询城市人口数小于100人的城市
SELECT  *  FROM world.city 
WHERE population<100;

--- 2.3.3 WHERE 逻辑连接符( AND OR ,... )
--- 例子5: 查询中国，山东省的城市信息。
SELECT * FROM city 
WHERE countrycode='CHN' 
AND district='shandong';

--- 例子6：查询中国或美国的城市信息
SELECT * FROM city 
WHERE countrycode='CHN'  OR countrycode='USA';


--- 2.3.4 where 模糊查询(like)
--- 例子7：查询 城市名为 qing开头的城市信息
select * from city where name like 'qing%';
SELECT * FROM city WHERE NAME LIKE '%qing%'; ---> 不走索引，生产中禁用


--- 2.3.5 WHERE 其他 (IN ,NOT IN, BETWEEN AND )
--- 例子8：查询中国或美国的城市信息
SELECT * FROM city 
WHERE countrycode IN ('CHN','USA');


SELECT * FROM city WHERE countrycode='CHN' 
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

聚合查询： 把结果集合并到一起显示。
union all ： 只合并不去重复 
union     ： 合并并去重复
性能会有提升或下降趋势。



--- 例子9：查询中国或美国之外的城市信息
SELECT * FROM city 
WHERE countrycode NOT IN ('CHN','USA');

--- 例子10：查询人口数在1000w到2000w的城市信息
SELECT * FROM city
WHERE population > 10000000 AND population<20000000;

SELECT * FROM city 
WHERE population BETWEEN 10000000 AND 20000000;
--- 说明： BETWEEN AND 是包含头尾，一般针对数字列。

姬凤

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL基础应用

SQL基础应用1.1 什么是SQL？结构化查询语言。MySQL内置的命令。数据库管理、对象操作。是关系型数据库通用的语言。SQL规范: SQL89 SQL92 SQL99 SQL031.2 SQL 常用种类（填空题）1.2.1 DDL ：数据定义语言库：增、删、改表：增、删、改主要是针对以上对象的结构、属性的定义。1.2.2 DCL ：数据控制语言对于权限相关管理。grant revoke1.2.3 DML ：数据操作语言数据行：增、删、改、查1.2.4 D
复制链接

扫一扫