MySQL基础篇学习笔记

ps:笔记源于宋红康哔哩哔哩视频。感触是多做题，不然忘得快

8.0的端口号我设置的是3306 5.7是13306

show databases;

show tables;

select * from …;

去除重复数据： select distinct …from…; #单独使用，不然再加一个就失去意义了，无法精确去第一个的重，因为受第二个影响
改改表名： select … “”, … “” from…; #""内不就是…改成的名字

插入数据集：用工具里面的sql脚本；或者在cmd输入source d：…

ifnull(…,0) #如果…是null，就用0代替

显示表结构：describe …;

下划线-（txt输不出来）替代一个字符是在like（模糊查询）用；点.替代一个字符是在regexp（正则表达式）用

and先于or，例如：（）and（） or （）and（） #意思是前俩或者后俩

in(a,b)是等于a或者等于b的意思；between…and…才是在…到…之间（前一个…比后一个…小）

null是没有的意思，0是0，null是null， 例如where … is（<=>） null 意思是…没有 或者用安全等于号

\是转义字符

如果没有使用排序，默认查询排序按照当初添加的顺序显示

order by …asc；order by…desc；如果order by后面没指明，默认升序

可以使用列的别名进行排序；但是列的别名只能在order by种使用，不能在where种使用
如果where和oder by同时出现，where在前面，例如：select…from…where…order by…;因为实际上是先from…完了最后再执行select，但from和where是一起的，order by却不是，即先from再select再order by

order by的使用可以：order by …desc，…asc；（二级排序）

每页显示pageSize条记录，此时显示第pageNo页
公式：limit（pageNo-1）*pageSize，pageSize；例如显示32和33条：limit 31，2；#因为第一条是0，所以32是31
8.0比5.7版本多一个写法： limit 2 offset 31；#把上面俩数换了，中间换成offset

limit位置垫底；order by写出位置倒数第二行

LIMIT [位置偏移量,] 行数

多表查询和子查询的代码量较大，较难驾驭，要好好学习：

笛卡尔积（交叉连接）的错误：每个员工与配个部分都匹配了一遍
正确的多表查询方式：需要有连接条件
select a,b from c,d where c.a=d.a;

从sql优化角度，建议多表查询时，每个字段前都指明其所在的表，例如：select c.a，d.b…

一旦给表起了别名，即from a b，…那么在where和select种就只能用别名了，不然就报错了，因为from是第一步的

n个表实现多表查询，至少有n-1个连接条件

角度1.等值链接 vs 非等值连接 角度2.自连接 vs 非自链接 角度3.内连接 vs 外连接 #他丫的这里角度其实就是名称的意思，不同角度就是不同称呼嘛
一旦出现”所有的“，并且来自不同的表，一定是外连接

7种sql join的实现：
#MySQL不支持SQL92语法中外连接的写法，但Oracle支持，即：where ···.··· = ···.···（+）；#括号带+就是补齐右边（空着），即左外连接
SQL99语法实现内连接：select a,b from c join d on c.a = d.a #即用join…on方式,join是原来c,d处的逗号，然后on后面跟随的是条件
若是select a,b,g from c join d on c.a = d.a join e on d.e = g.e #即叠加join on即可
其实上面 join on 是inner join … on …# 内连接 只不过省略了inner
左外连接即可写成：left join… on…#左外连接 也省略了 left outer join… on…的outer
#MySQL不支持full outer join on…的满外连接
#例如mysql的左外连接是两个圈的交集和左侧表非交集部分（即左边一个整表）

union与union all:前者去重，后者不去重，常用后者，效率高
union all用于两个select from where 整体语句之间，连起来，其中join，用union all玩并集的组合

join on后面可以加个and 即为两个=的条件
sql99语法新特性1.natural join，它会自动查询两张表中所有相同字，然后进行等值链接，即省去了and
sql99语法新特性2.joing using(…),这个就是把on后面的词放到了括号里（记住是两表共有的那个单词） #了解就好了

如果不用外连接，sql92也是ok的，即直接 select from where …=… and…=… and…=…

查询部门为sales或IT的员工信息：select…from…join…on…where …in(‘sales’,‘IT’) #写这条笔记是为了记这个where…in（）

单行函数与多行函数：

取随机数：rand（） #若括号内没字符则为每一个rand（）各自随机取值，若有字符则随机取同一值
四舍五入，截断操作：round（…，.） #…为数值，.为保留.小数，如round（123.556，0）为124，即保留0位小数
与round（）类似的还有一个truncate（） #truncate不会四舍五入，如truncate(123.556,0)就是123
cell（）天花板 floor（）地板 #一个往上取整，一个向下取整

单行函数可以嵌套，例如truncate（round（132.321，2），0）位132

角度换弧度：radians（） 弧度换角度：degrees（） 指数：power（,） 对数：exp（）

进制间的转换： 二进制bin（） 别的进制什么的用时再查吧。。。

round和truncate和concat用的频率较高，其余单行函数了解就好，较少用

IF(value,value1,value2) #如果value的值为TRUE，返回value1，否则返回value2

IFNULL(value1, value2) #如果value1不为NULL，返回value1，否则返回value2
#select ifnull（note，‘zongliang’） from…
group by note with rollup #这个rollup就是zongliang的数值

CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2… [ELSE resultn] END #条件是例如：‘什么’小于’某某’
CASE … WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN值1 … [ELSE 值n] END #常量就是’某某’，case后面已经写了’什么’
case可以用在select后面，要灵活运用啊

五大常用聚合函数：
AVG() SUM() MAX() MIN() COUNT()
#可以对数值型数据使用AVG 和 SUM 函数，毕竟字符串不能数学运算；可以对任意数据类型的数据使用 MIN 和 MAX 函数，字符串排序按首字母a最小z最大。

可以使用GROUP BY子句将表中的数据分成若干组
切记：from后面接where，再才是group by，然后接having；having的作用是弥补where后面不能写聚合函数的缺点
因为where是先筛选后连接，而having则相反，所以where更高效，能在where声明的就别放到having后面

过滤分组：HAVING子句

1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

#查询书名达到10个字符的书，不包括里面的空格
select name from 表
where char_length(replace(name,’ ‘,’'))>=10 #这一行是重点，用了replace函数

where和limit后面不能跟聚合函数

子查询:

注意事项：子查询要包含在括号内；将子查询放在比较条件的右侧；单行操作符对应单行子查询，多行操作符对应多行子查询

IN 等于列表中的任意一个
ANY 需要和单行比较操作符一起使用，和子查询返回的某一个值比较 (SOME ：实际上是ANY的别名，作用相同，一般常使用ANY)
ALL 需要和单行比较操作符一起使用，和子查询返回的所有值比较

如果子查询简单，可以试着从外往里写；如果要迭代好几层，一般从内往外写，内可以先查一下

相关子查询：表起名然后在子查询里面和外面联系起来

#切记：having和limit后面不能跟子查询；其余都可以跟。 一般都是select和from

题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

创建和管理表：

create database …;
show create database …;

如果是5.7版本，创建数据库记得加上 character set ‘utf8’ #因为5.7默认的是拉丁
create database if not exists … character set ‘utf8’;
show databases;

切换数据库： use …；

查看当前数据库中保存的数据表：show tables;

查看当前使用是数据库：select database() from dual;

查看指定数据库下保存的数据表：show tables from …；

修改数据库：alter database … character set ‘utf8’

删除数据库：drop database if exists …; #如果存在则删除成功，如果不存在则默默结束不会报错

创建数据表：
先显示：use …;
show create database …; #默认使用的是utf8
show tables;
create table if not exists … （…int，…varchar（…），… date） #需要用户具备创建表的权限，括号里面三个单词是…的类型，varchar括号里写字节长度

创建表的两种方式，一种白手起家，一种基于现有的表同时导入数据
白手起家就是上面那个if not exists巴拉巴拉的 基于现有表是 create table … as select … from …where…

修改表：
添加一个字段：alter table … add … double（…，…）（double（…，…）是字符串类型，还有varchar（）、int等）
修改一个字段：ALTER TABLE … MODIFY… VARCHAR(…);
重命名：ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型； #modify与change的区别是前者不能修改列名，但前者在不修改列名的时候运用起来不用写两个列名，较为方便
删除一个列：ALTER TABLE 表名 DROP column 字段名
删除表：drop table… #drop不能回滚

清空表：
truncate是ddl的操作，一旦执行，不能rollback #因为执行了ddl会自动执行一次commit，这次commit不受set…的影响
dml默认也是不能回滚的，但是如果有set autocommit=false，则可以回滚，用delete
delete是from 另外俩ddl语言的是直接单词+table+名字

#打个比方,delete 是单杀,truncate 是团灭,drop 是把电脑摔了。执行的速度上：drop 是神舟火箭,truncate 是和谐号动车,delete 是自行车。

更改表面：RENAME TABLE …TO …;ALTER table …RENAME [TO] … ; # [TO]可以省略

mysql8.0的新特性：ddl的原子化 #即删除表的时候要么同时删除，要是有一张表不存在就删除失败，但执行成功，只是处理原则更人性化，都能删就删除，否则则先留着

将某字段移动到某某字段后面：alter table… modify … after… #这个after就是后面

#添加字段alter… add… 和增加数据insert into…values不是一个意思啊，字段是列名，数据是列名下面的空格里面的值

dml之添加更新删除数据：

insert into …values（…，…，…，…）#括号里面是数据，要和表中数据及数据类型对应。但不能更改加入数据的前后位置
inser into …（…，…，…，…） values（…，…，…，…） #前后两个括号里面的类型及数据对应就可以了
方式二：用union all，相对于上面可以省去括号
例如：select ‘啥’，‘啥啥’，‘啥啥啥’，union all #就加进表了，最后一行不用写union all

查询的字段一定要与添加到表的字段对应

表中要添加数据的字段的长度不能低于表中查询字段的长度
如果低于了，就有添加不成功的风险

更新数据（可以多行）： update表 set… where… #可以实现批量修改数据，添加数据有可能不成功，是约束造成的，set后面可以更改多个项目用逗号分隔开就行了
删除数据（可以多行）：delete from 表 where… #删除数据也有可能因为约束的影响，导致删除失败

dml操作默认情况下都会自动提交数据。如果希望之星后不自动提交数据，要使用set autocommit = false

mysql8.0的新特性：计算列
create table 表（
a int，
b int，
c int generated always as （a+b） virtual #字段c即为计算列
）

整型数据类型讲解：

浮点数：float（m，d） double（m，d） 定点数：decimal（m，d）
浮点数会有误差，定点数是精确的

date是日期 datetime是日期+时间
datetime用的多，但在需要计算的时候用毫秒数timestamp更多，timestamp可以更改时区，例如用set time_zone ='+9:00’可以让timestamp计算时间增加九个小时

char()固定长度，varchar()是可变字符串
char用的情况：存储很短的信息；固定长度的；十分频繁的改变column
myisam数据存储引擎和数据列用char；memory存储引擎和数据列两者都可；innodb存储引擎用varchar

blob类型可以存图片

虽然效率不一定最高，但从可靠性角度建议小数用decimal（m，d），整数用int，时间类用datetime

约束： （ key 索引 ） 约束不是索引，但两者是交相辉映的？

#这个key在这不是键值对的键 #索引是…index…（…）

约束的种类：① not null (非空约束)
② unique (唯一性约束)
③ primary key (主键约束)
④ foreign key (外键约束)

⑤ check (检查约束)
⑥ default (默认值约束)

列级约束：例如表里面第一行 id int unique #就是id是整型且有唯一性约束
表级约束：CONSTRAINT起名 UNIQUE(列名) #写在标内最后一行
在创建的唯一性约束的时候，如果不给唯一性约束起名，就默认和列明相同

可以向声明为unique的字段上添加null值，而且可以多次添加null #就是说null在唯一性约束里面可以不唯一

两个合一起建立唯一性约束，就得两者都一样才会报错。实际应用场景例如不同的人可以发一样的工资。使用方法就是表内最后一行把之前表级约束后面unique（列名，列名）

主键约束是比唯一性约束更强的一种约束，即primary key比unique约束性更强在非空且唯一，而且表中只能用一个主键约束

当我们向主键（含auto_increment）的字段上添加0或null，实际上会自动地往上添加指定的字段
#开发中，一旦主键作用的字段上声明有 auto_increment，则我们在添加数据时，就不要给主键对应的字段去赋值了
5.7的auto_increment是放在内存里面的，要是重启会断开（前面有12，插入了34，重启后再插入发现序号是3），8.0是持久化的，重启后序号也不会断开（重启后再插入，序号是5）

视图：

更新视图的数据，会导致基表中数据的修改；同理，更新表中的数据，也会导致视图中的数据的修改

查看视图的信息状态：SHOW TABLE STATUS LIKE ‘…’;

create 或者alter可能存在，可以直接用 create or replace view…

存储过程与函数：

举例1：创建存储过程select_all_data()，查看 emps 表的所有数据

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END $
DELIMITER ;

#前后各一个delimiter 前面的delimiter与end带//呼应，后一个delimiter用；结束 此外begin 与end形成过程 create procedure 是创建存储过程

调用用call

FUNCTION中总是默认为IN参数。 存储函数有两个return

使用SHOW CREATE语句查看存储过程和函数的状态信息

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]

日期到字符串是格式化，反之为解析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4vfriPfk-1663491892808)(C:\Users\asus\AppData\Roaming\Typora\typora-user-images\image-20220622145911882.png)]

变量、流程控制与游标：

变量分为系统变量以及用户自定义变

系统变量分为全局系统变量（需要添加global 关键字）以及会话系统变量（需要添加 session 关键字）

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以一个“@” 开头。根据作用范围不同，又分为会话用户变量和局部变量。
会话用户变量：作用域和会话变量一样，只对当前连接会话有效。
局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用。

定义：可以使用DECLARE 语句定义一个局部变量
作用域：仅仅在定义它的 BEGIN … END 中有效
位置：只能放在 BEGIN … END 中，而且只能放在第一句

定义条件与处理程序：

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。

执行指定的处理语句，语句可以是像“ SET 变量 = 值”这样的简单语句，也可以是使用BEGIN … END 编写的复合语句。

#方法2：捕获mysql_error_value 😊😊😊#就是continue这个错误让后继续执行完程序，最后输出一个用户变量
DECLARE CONTINUE HANDLER FOR 1146 SET @info = ‘NO_SUCH_TABLE’;

流程控制：

if else写在存储过程里面，if后面还可以带and，写完后要加个end if来结束if语句

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-adQ6WI7T-1663491892809)(C:\Users\asus\AppData\Roaming\Typora\typora-user-images\image-20220624091146761.png)]

声明+赋值+判断

case when then结构，和之前单行函数里面的差不多，只不过这个在存储过程里面，end后面也要加个case。类似存储过程里面的if结构

🤦‍♂️🤦‍♂️🤦‍♂️循环结构，凡是循环结构，一定有四要素。1.初始化条件。2.循环条件。3.循环体。4.迭代条件

loop只是一个循环结构。意味着在这begin和end之间除了用loop语句还可以再加别的结构

游标：

游标的声明放在声明变量下面，切一定有四步骤：1.声明游标，2.打开游标，3.使用游标，4.关闭游标

游标的声明declare…cursor for…；这个…可以是select…from…，即…可以是一个完整的查询语句

触发器：

创建触发器的语法结构是：

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW

8.0新特性：

窗口函数：

窗口函数的语法结构有以下两种：

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

共用表表达式（通用表表达式）：

普通公用表表达式的语法结构是：
WITH CTE名称
AS （子查询）
SELECT|DELETE|UPDATE 语句;