MySQL笔记

最近再系统的看一下mysql，预计花半个月时间，所以做了一个笔记，以后查起来也方便！坚持吧！

1. MySQL数据库基本操作-DDL

DDL(Data Definition Language)，数据定义语言，该语言部分包括以下内容：

• 对数据库的常用操作
• 对表结构的常用操作
• 修改表结构
  

1.1 对数据库表的操作

-- 数据库操作之数据库查询
-- 查看所有数据库
show DATABASES;
-- 创建数据库
CREATE DATABASE mydb1;
-- 选择使用数据库
use mydb1;
-- 删除数据库
drop database if exists mydb1;

1.2 对表结构的常用操作

1.2.1 创建表

CREATE DATABASE mydb1;
-- 选择使用数据库
use mydb1;
-- 创建表
create table if not exists student(
	sid int UNSIGNED,  -- 无符号
	name VARCHAR(20),
	gender VARCHAR(1),
	age TINYINT,
	birth date,
	address VARCHAR(20),
	score double
);

刚创建的表：

1.2.2 数据类型

数据类型是指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来，使用数据类型的原则是:够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间。

• 数值类型
  
• 日期和时间类型
  
• 字符串类型
  

1.2.3 对表结构的常用操作—其他操作

-- 查看当前数据库的所有表名称
show tables;
-- 查看指定某个表的创建语句
show create table student；
-- 查看表结构
desc student;
-- 删除表
drop table studnet;

1.2.4 对表结构的常用操作-修改表结构格式

• 修改表添加列

alter table 表名 add 列名 类型(长度) [约束];

-- 为student表添加dept院系列
alter table studnet add dept VARCHAR(20); 

• 修改列名和类型

alter table 表名 change 旧列名 新列名 类型(长度) 约束; 

-- 为student表的dept字段更换为department varchar(30)
 alter table student change dept department VARCHAR(30); 

• 修改表删除列

alter table 表名 drop 列名;

-- 删除student表中department这列
alter table student DROP department;

• 修改表名

rename table 表名 to 新表名

-- 将表student改名成 stu
rename table student to stu; 

2. MySQL数据库基本操作-DML

DML是指数据操作语言，英文全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新。
关键字：

• 插入insert
• 删除delete
• 更新update

2.1 插入insert

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些
insert into 表 values (值1,值2,值3...);     //向表中插入所有列

insert into student(sid,name,gender,age,birth,address,score)
                       values(1001,'男',18,'1996-12-23','北京',83.5);
insert into student values(1001,'男',18,'1996-12-23','北京',83.5);

2.2 数据修改

update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

-- 将所有学生的地址修改为重庆 
update student set address = '重庆’; 

-- 讲id为1004的学生的地址修改为北京 
update student set address = '北京' where id = 1004 

-- 讲id为1005的学生的地址修改为北京，成绩修成绩修改为100 
update student set address = '广州',score=100 where id = 1005

2.3 数据删除

delete from 表名 [where 条件];
truncate table  表名 或者 truncate 表名

-- 1.删除sid为1004的学生数据
delete from student where sid  = 1004;
-- 2.删除表所有数据
delete from student;
-- 3.清空表数据
truncate table student;
truncate student;

注意：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table ，可以理解为是将整个表删除，然后再创建该表；

3. MySQL约束

约束实际上就是表中数据的限制条件。

表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。

分类

• 主键约束(primary key) PK
• 自增长约束(auto_increment)
• 非空约束(not null)
• 唯一性约束(unique)
• 默认约束(default)
• 零填充约束(zerofill)
• 外键约束(foreign key) FK

3.1 主键约束

• MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。
• 主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
• 每个表最多只允许一个主键
• 主键约束的关键字是：primary key
• 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

3.1.1 添加单列主键

3.1.2 添加多列主键(联合主键）

3.1.3 通过修改表结构添加主键

3.1.4 删除主键约束

3.2 自增长约束(auto_increment)

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加 auto_increment 属性来实现主键自增长
特点：

• 默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
• auto_increment约束的字段必须具备 NOT NULL 属性。
• 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等。
• auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

3.2.1 指定自增字段初始值

3.3 非空约束(not null)

MySQL 非空约束（not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。
语法：

方式1：<字段名><数据类型> not null;
方式2：alter table 表名 modify 字段 类型 not null;

3.4 唯一性约束(unique)

唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。
语法：

方式1：<字段名> <数据类型> unique
方式2： alter table 表名 add constraint 约束名 unique(列);

3.5 默认约束(default)

MySQL 默认值约束用来指定某列的默认值。
语法：

方式1： <字段名> <数据类型> default <默认值>;
方式2: alter table 表名 modify 列名 类型 default 默认值; 

3.6 零填充约束(zerofill)

• 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
• zerofill默认为int(10)
• 当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128_{+127，无符号为0}256。

3.7 外键约束(foreign key) FK

4. MySQL数据库基本操作-DQL

• 数据库管理系统一个重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示。
• MySQL提供了功能强大、灵活的语句来实现这些操作。
• MySQL数据库使用select语句来查询数据。

4.1 基本查询

语法格式:

select 
  [all|distinct]
  <目标列的表达式1> [别名],
  <目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名> 
[having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

简化版语法”

select *| 列名 from 表 where 条件

4.1.1 数据准备

创建数据库和表：

-- 创建数据库
create database if not exist mydb2;
use mydb2;
-- 创建商品表：
create table product(
 pid int primary key auto_increment, -- 商品编号
 pname varchar(20) not null , -- 商品名字
 price double,  -- 商品价格
 category_id varchar(20) -- 商品所属分类
);

添加数据：

insert into product values(null,'海尔洗衣机',5000,'c001');
insert into product values(null,'美的冰箱',3000,'c001');
insert into product values(null,'格力空调',5000,'c001');
insert into product values(null,'九阳电饭煲’,200,'c001');
insert into product values(null,'啄木鸟衬衣',300,'c002');
insert into product values(null,'恒源祥西裤',800,'c002');
insert into product values(null,'花花公子夹克',440,'c002');
insert into product values(null,'劲霸休闲裤',266,'c002');
insert into product values(null,'海澜之家卫衣',180,'c002');
insert into product values(null,'杰克琼斯运动裤',430,'c002');
 
insert into product values(null,'兰蔻面霜',300,'c003');
insert into product values(null,'雅诗兰黛精华水',200,'c003');
insert into product values(null,'香奈儿香水',350,'c003');
insert into product values(null,'SK-II神仙水',350,'c003');
insert into product values(null,'资生堂粉底液',180,'c003');
 
insert into product values(null,'老北京方便面',56,'c004');
insert into product values(null,'良品铺子海带丝',17,'c004');
insert into product values(null,'三只松鼠坚果',88,null);

4.1.2 简单查询

-- 1.查询所有的商品.  
select *  from product;
-- 2.查询商品名和商品价格. 
select pname,price from product;
-- 3.别名查询.使用的关键字是as（as可以省略的）.  
-- 3.1表别名: 
select * from product as p;
-- 3.2列别名：
select pname as pn from product; 
-- 4.去掉重复值.  
select distinct price from product;
-- 5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname,price+10 from product;

4.1.3 运算符

4.1.4 排序查询

4.1.5 聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

4.1.6 分组查询-group by

4.1.7 分页查询-limit

4.1.8 INSERT INTO SELECT语句

4.1.9 SELECT INTO FROM语句

4.2 正则表达式

正则表达式(regular expression)描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。
MySQL通过REGEXP关键字支持正则表达式进行字符串匹配。

-- ^ 在字符串开始处进行匹配
SELECT  'abc' REGEXP '^a';
 
-- $ 在字符串末尾开始匹配
SELECT  'abc' REGEXP 'a$';
SELECT  'abc' REGEXP 'c$’;

-- . 匹配任意字符
SELECT  'abc' REGEXP '.b';
SELECT  'abc' REGEXP '.c';
SELECT  'abc' REGEXP 'a.';
 
-- [...] 匹配括号内的任意单个字符
SELECT  'abc' REGEXP '[xyz]';
SELECT  'abc' REGEXP '[xaz]';

-- [^...] 注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
SELECT  'a' REGEXP '[^abc]';
SELECT  'x' REGEXP '[^abc]';
SELECT  'abc' REGEXP '[^a]';
 
-- a* 匹配0个或多个a,包括空字符串。 可以作为占位符使用.有没有指定字符都可以匹配到数据
 
SELECT 'stab' REGEXP '.ta*b';
SELECT 'stb' REGEXP '.ta*b';
SELECT '' REGEXP 'a*';
 
-- a+  匹配1个或者多个a,但是不包括空字符
SELECT 'stab' REGEXP '.ta+b';
SELECT 'stb' REGEXP '.ta+b';

-- a?  匹配0个或者1个a
SELECT 'stb' REGEXP '.ta?b';
SELECT 'stab' REGEXP '.ta?b';
SELECT 'staab' REGEXP '.ta?b';
 
-- a1|a2  匹配a1或者a2，
SELECT 'a' REGEXP 'a|b';
SELECT 'b' REGEXP 'a|b';
SELECT 'b' REGEXP '^(a|b)';
SELECT 'a' REGEXP '^(a|b)';
SELECT 'c' REGEXP '^(a|b)';
 
-- a{m} 匹配m个a
 
SELECT 'auuuuc' REGEXP 'au{4}c';
SELECT 'auuuuc' REGEXP 'au{3}c';

 -- a{m,n} 匹配m到n个a,包含m和n
 
SELECT 'auuuuc' REGEXP 'au{3,5}c';
SELECT 'auuuuc' REGEXP 'au{4,5}c';
SELECT 'auuuuc' REGEXP 'au{5,10}c';
 
-- (abc) abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号，所以括号适合上面的所有情况。
SELECT 'xababy' REGEXP 'x(abab)y';
SELECT 'xababy' REGEXP 'x(ab)*y';
SELECT 'xababy' REGEXP 'x(ab){1,2}y';

5. MySQL的多表操作

5.1 多表关系

5.2 多表联合查询

6. 函数

在MySQL中，函数非常多，主要可以分为以下几类:

• 聚合函数
• 数学函数
• 字符串函数
• 日期函数
• 控制流函数
• 窗口函数

6.1 聚合函数

• 在MySQL中，聚合函数主要有:count,sum,min,max,avg。另外一个函数:group_concat()，该函数用户实现行的合并。
• group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。

6.2 数学函数

6.3 字符串函数

6.4 日期函数

函数名	描述	实例
UNIX_TIMESTAMP()	返回从1970-01-01 00:00:00到当前毫秒值	select UNIX_TIMESTAMP() -> 1632729059
UNIX_TIMESTAMP(DATE_STRING)	将制定日期转为毫秒值时间戳	SELECT UNIX_TIMESTAMP(‘2011-12-07 13:01:03’);

6.5 控制流函数

6.5.1 if逻辑判断语句

6.5.2 case when语句

6.6 窗口函数

• MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点.
• 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

7. 视图

8. 存储过程

9. 存储函数

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。
存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。

存储函数与存储过程的区别

• 1.存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
• 2.存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
• 3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；
• 4.存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
• 5.存储过程可以调用存储函数。但函数不能调用存储过程。
• 6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

10. 触发器

11. 索引

12. 存储引擎

13. 事务

14. 锁机制

15. 日志

16. 优化

17.JDBC操作

18. pymysql操作

PyMySQL是一个纯 Python 实现的 MySQL 客户端库，支持兼容 Python 3，用于代替 MySQLdb。

import pymysql
 
conn = pymysql.connect(host='localhost', port=3306, user='root',password='123456',database='mydb17_pymysql', charset='utf8')
# 获取游标
cursor = conn.cursor()
 
# 执行ＳＱＬ语句 返回值就是ＳＱＬ语句在执行过程中影响的行数
sql = "select * from student;"
 
row_count = cursor.execute(sql)
print("ＳＱＬ语句执行影响的行数%d" % row_count)

# 取出结果集中一行  返回的结果是一行
# print(cursor.fetchone())
 
# 取出结果集中的所有数据　　返回一行数据
for line in cursor.fetchall():
    print(line)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()