表属性
表的属性
1存储引擎: 2InnoDB（默认的） 3字符集和排序规则: 4utf8 5utf8mb4
列的属性
1约束(一般建表时添加): 2primary key ：主键约束 3设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。 作为聚簇索引 4not null ：非空约束 5列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0 6unique key ：唯一键 7列值不能重复 8unsigned ：无符号 9针对数字列，非负数。 10 11其他属性: 12key :索引 13可以在某列上建立索引，来优化查询,一般是根据需要后添加 14default :默认值 15列中，没有录入值时，会自动使用default的值填充 16auto_increment:自增长 17针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量） 18comment : 注释
sql_mode
作用：影响sql执行行为，规范SQL语句的书写方式(例如除数不能为0)
可以使用select @sql_mode查看(各版本有所出入)
字符集(charset)及校对规则(Collation)
字符集：
- utf8：最大存储长度，单个字符最多3字节
- utf8mb4：最大存储长度，单个字符最多4字节
常用于建库建表时
1create database dbname charset utf8mb4; 2# 查看数据库的字符集合 3show create database dbname;
校对规则
每种字符集，有多种校对规则(排序)，例如常见的ASCII编码表
1show collation;
作用：影响排序的操作
数据类型
text类型
Number类型
*:这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。
Date类型
*即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
什么是SQL语句
SQL语句是结构化查询语言(Structured Query Language)的简称,是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
SQL语句的类型
数据查询语言（DQL:Data Query Language）：其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其它类型的SQL语句一起使用。
数据操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。
事务控制语言（TCL）：它的语句能确保被DML语句影响的表的所有行及时得以更新。包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
数据控制语言（DCL）：它的语句通过GRANT或REVOKE实现权限控制，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
数据定义语言（DDL）：其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；为表加入索引等。
指针控制语言（CCL）：它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。
比较常用的有DDL(数据定义语言)`DCL(数据控制语言)\DML(数据操作语言)\DQL(数据查询语言) ¨K65K ¨K66K ¨G4G ¨K67K ¨K68K 在日常中DDL多用于库、表的管理. **库名与库属性** ¨G5G ¨K32K **表** ¨G6G ¨K35K ¨K69K 控制就是操作权限，而在DCL之中，主要有两个语法：GRANT,REVOKE ¨G7G  **DCL** 授权 ¨G8G 权限: ALL: 管理员(不包含“ Grant option”，给他人授权) 权限1，权限2，权限3...：普通人员(开发人员) Grant option 对象范围: 库，表 | "." | ---> chmod -R 755 / | 管理员 | | :---------: | :----------------------------: | :------: | | userName.* | ---> chmod -R 755 userName/ | 普通用户 | | userName.t1 | ---> chmod -R 755 userName/t1 | | ¨G9G Mysql授权表 | user | *.* | | | :---------: | :--------------: | ---- | | db | db.* | | | Tables_priv | db.table | | | Colums | 列 | | | Procs_priv | 存储过程中的权限 | | 回收权限 ¨G10G 拓展，忘记root密码了该怎么办？¨G11G **原理探究** 说到这个，那就不得不从mysql的server`层说起了，mysql的架构图如下(仅关键部分)
当我们忘记密码的时候,改怎么办呢？
这就对于我们平时对于mysql的模型有所考察了，如果你知道mysql的他内部到底是如何运行的,那么只需要在合适的地方，按照我们所想的给他“绕道而行”，是否就可以绕过这个密码验证了呢？答案是当然可以的。
首先我们介绍一下上面这副图中执行流程，当我们启动mysql服务的时候，系统会自动帮我们做一下这些事儿
- 首先提供可连接的协议，也就是提供服务
- 打开用户与密码校验，以处理将要连接的客户
- 验证成功，分配独立的连接线程
如果我们需要跳过密码校验,那么只需要做以下几件事。
- 让系统重启
- 在重启的过程中停掉用户与密码校验
这样我们就可以连接了，但是还不够。尽然已经停掉了。此时的我们无法修改密码.(跳过验证，而不是把验证功能移除了)
那么我们此时还需要把验证功能加载进来，然后对验证的表进行修改。
DML 数据操作语言
这个也是我们日常中用的最多的地方，应为建库表，改权限，改密码。修改等等什么的并不是每次都要嘛.这个也很好理解
DML 数据操作语言 对表中的数据行进行增、删、改
insert
1# 语法 2单行数据 3INSERT INTO tableName(key1, key2, key3..) VALUES(value1, value2,value3...) [SELECT * FROM tableName] 4 5# 多行数据 6INSERT INTO tableName(key1, key2, key3..) VALUES \ 7(value1, value2,value3...) 8(value1, value2,value3...) 9(value1, value2,value3...); 10... 11[SELECT * FROM tableName]
插入时, key1,key2,key3 必须与value1，value2， value3 数量一致
插入对应字段
1INSERT INTO tableName(key1, key3..) VALUES(value1, value3...) [SELECT * FROM tableName]
update
1# 更新前我们一般都会先查表内数据 2# 查询出对应表已存在所有行 3DESC tableName; 4# 查询对应表已存在数据 5SELECT * FROM tableName; # * 可替换成字段名，查对应字段 6 7# 更新数据 8UPDATE student SET 字段名='新值' [WHERE 限定条件];
Eg:
创建一张新的student表
1# 建表 2CREATE TABLE `student` ( 3 `id` int NOT NULL AUTO_INCREMENT COMMENT '学号', 4 `sname` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '姓名', 5 `sage` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '年龄', 6 `intime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间', 7 PRIMARY KEY (`id`) 8) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表' 9 10# 插入演示数据 11INSERT INTO student(sname,sage) VALUES("赵一", 1), 12("王二", 2), 13("张三", 3), 14("李四", 4);
数据库
需求一:
李四改名为“里斯”;
1UPDATE student SET sname="里斯" WHERE sname = "李四"; 2 3# 或者 4UPDATE student SET sname="里斯" WHERE id=4; 5UPDATE student SET sname="里斯" WHERE sage=4;
修改后，如下所示
需求二:
将所有表内成员的年龄+10;
1UPDATE student SET sage=sage + 10 2# UPDATE student SET sage+=10(错误写法,开发时候用的什么sage ++， sage +=，在这里都不允许)
需求三:将所有表内成员的年龄+10,除了里斯
1UPDATE student SET sage=sage + 10 WHERE sname != "里斯"; 2# 当然也可以这样写; 3UPDATE student SET sage=sage + 10 WHERE sname = "赵一" OR sname = "王二" OR sname="张三" ;
AND: 执行均满足
OR: 满足其一执行
where 见下文
delete
1# 删除指定数据 2DELETE FROM tableName [WHERE id=1]; 3 4# 清空标中所有数据 5DELETE FROM student; 6truncate table student;
区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
truncate: DDL操作,对与表段中的数据页进行清空,速度快.
伪删除：用update来替代delete，最终保证业务中查不到（select）
11.添加状态列 2ALTER TABLE stuent ADD state TINYINT NOT NULL DEFAULT 1 ; 3SELECT * FROM stuent; 42. UPDATE 替代 DELETE 5UPDATE stuent SET state=0 WHERE id=6; 63. 业务语句查询 7SELECT * FROM stu WHERE state=1;
拓展
1DELETE FROM student; 2DROP TABLE student; 3truncate table student;
以上三条删除语句有何区别？
同：三者都是删除语句，均可删除
异:
DELETE FROM student:
逻辑上
逐行删除，数据过多，操作很慢
并没有真正的从磁盘上删除，知识在磁盘上打上标记，磁盘空间不立即释放。HWM高位线不会降低
DROP TABLE student;
将表结构(元数据)和数据行，物理层次删除
truncate truncate table student;
清空表段中的所有数据页，物理层次删除全表数据，磁盘空间立即释放。HWM高位线降低
DQL 数据查询语言
show类
1show databases; 2show CREATE DATABASE databaseName; 3 4show table; 5show CREATE TABLE table; 6 7desc tableName;
select类
获取表中的数据行
1# select @@xxx 查看系统参数 2SELECT @@port; 3SELECT @@basedir; 4SELECT @@datadir; 5SELECT @@socket; 6SELECT @@server_id; 7 8# select 函数 9SELECT NOW(); 10SELECT DATABASE(); 11SELECT USER(); 12SELECT CONCAT("hello world"); 13SELECT CONCAT(USER,"@",HOST) FROM mysql.user; 14SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
手册 https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html
select配合子句
1select 2FROM 表1，表2..., 3WHERE 过滤条件1，过滤条件2，过滤条件3 ... 4GROUP BY 条件列1，条件列2，条件列3 ... 5 # selct_list 列名 6HAVING 过滤条件1，过滤条件2，过滤条件3 ... 7ORDER BY 条件列1，条件列2，条件列3 ... 8LIMIT 限制条件;
单表子句-from
1SELECT 列1,列2 FROM 表 2SELECT * FROM 表 3 4# EG 5# 查询student中所有的数据(不要对大表进行操作) 6SELECT * FROM stu ; 7# 查询stu表中,学生姓名和入学时间 8SELECT sname , intime FROM stuent;
单表子句-where
1SELECT col1,col2 FROM TABLE WHERE colN 条件; 2# where 操作符(>、<、>=、 <=、 <>、in、like、and、or) 3SELECT col1,col2 FROM TABLE WHERE = 条件; 4 5# where 模糊查询 6SELECT * FROM city WHERE district LIKE 'guang%'; 7% : 表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。 8_ : 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句 9[] : 表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。 10 11# where配合between...and... 12SELECT * FROM city WHERE population >1000000 AND population <2000000; 13SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;
group by
根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列
未分组分组列，使用聚合函数
聚合函数
1**max()** ：最大值 2**min()** ：最小值 3**avg()** ：平均值 4**sum()** ：总和 5**count()** ：个数 6group_concat() : 列转行
HAVING
需要在group by 之后，在做判断过滤使用(类似于where)
order by
实现先排序，by后添加条件列(默认从小到大)
逆序：后加DESC
distinct：去重复
1SELECT countrycode FROM city ; 2SELECT DISTINCT(countrycode) FROM city ;
联合查询- union all
1-- 中国或美国城市信息 2 3SELECT * FROM city 4WHERE countrycode IN ('CHN' ,'USA'); 5 6SELECT * FROM city WHERE countrycode='CHN' 7UNION ALL 8SELECT * FROM city WHERE countrycode='USA' 9 10说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能 11UNION 去重复 12UNION ALL 不去重复
LIMIT 限制条件
限制查询
1select * FROM 表名 LIMIT 限制条件 2eg: 3--- 只输出前1000条 4select * FROM 表名 LIMIT 1000 5--- 只输出前1000-2000条 6select * FROM 表名 LIMIT 1000, 2000 7 8select * FROM 表名 LIMIT 1000, 2000 9相当于 10select * FROM 表名 LIMIT 2000 OFFSET 1000
join 多表连接查询
内连接
查询li4家的地址
1SELECT A.name,B.address FROM 2A JOIN B 3ON A.id=B.id --- 关联列 4WHERE A.name='li4' 5 6 7--- 相当于 8 9SELECT A.name,B.address FROM 10A JOIN B 11ON A.id=B.id 12WHERE A.name='li4'
外连接
驱动表建议使用 数据少的表 为驱动表
1SELECT A.name,B.address FROM 2A JOIN B 3ON A.id=B.id --- 关联列 4WHERE A.name='li4' 5 6 7--- 相当于 8 9SELECT A.name,B.address FROM 10A left JOIN B 11ON A.id=B.id 12WHERE A.name='li4' 13 14--- A left JOIN B 其中a位驱动表
