MySQL

1、MySQL环境部署：

1、MySQL安装包下载：

解压安装包至指定文件目录，目录名不能为中文

在bin目录下有两个执行文件：

mysql.exe #mysql的客户端mysqld.exe #mysql的服务端

2、以管理员身份打开终端

win + s 打开应用搜索输入 cmd 搜索以管理员身份打开终端

3、从终端进入mysql安装路径下的bin目录

cd F:\MySQL\mysql-5.7.16-winx64\bin

4、初始化mysql服务端

 mysqld --initialize-insecure #初始化代码

此时已经可以使用mysql数据库，但需要启动服务端和客户端两个终端，需要打开两个黑窗口。

进入bin目录下：

mysqld #打开服务端mysql -u用户名 -p密码 #打开客户端。

5、将mysql编辑到系统环境变量

右击“我的电脑”>>>选择“属性”>>>选择“高级系统设置”>>>选择“环境变量”>>>选择系统变量中的Path变量>>>点击编辑>>>新建>>>将mysql的bin目录的绝对路径输进去>>>点击确定。

6、将mysqld服务端设定为系统自启

# 使用 管理员身份打开cmdmysqld --install# 将msyql做成系统服务net start mysql# 启动服务

如果设置失败有可能是之前启动的mysqld服务端进程没有退出，需要先将进程退出

# 查看一个具体的进程tasklist | findstr mysqld# 杀死进程taskkill /F /PID 

7、设置mysql密码

mysqladmin -uroot password 123456

8、配置ini文件，以自动登录mysql

在bin目录下创建my.ini文件，并输入如下内容保存：

[mysqld]character-set-server=utf8collation-server=utf8_general_ci[client]default-character-set=utf8[mysql]user=rootpassword=123456default-character-set=utf8

重启服务即可。

2、MySQL的基本操作

2.1 SQL语句：

1. mysql中的SQL语句是以分号为结束标志
2. 当你在写SQL语句的时候发现你写错了有不想要他报错 我们就是使用\c取消
3. SQL语句是不区分大小写的 建议书写 关键字大写 表名 数据 使用小写我们上课 全部小写
4. 客户端退出语法可以不加分号 quit

2.2 库操作

创建数据库：

create database 库名;create database 库名 charset 编码语言;

查询数据库：

show databases;#查看所有数据库select database();#查看当前使用的库show create database db1;# 查询创建的数据库的语法

修改数据库编码：

alter database 库名 charset 编码类型；

删除数据库：

dropdatabase 库名；

指定当前使用的数据库：

use 库名

2.3 表操作

2.3.1 mysql的数据类型

2.3.2.1数值型：

1、整数型：

2、浮点型：

float(255, 30)# 总共有255位 小数占用30位double(255, 30)# 总共有255位 小数占用30位decimal(65, 30)# 总共有65位 小数占用30位

2.3.2.2 日期时间类型

2.3.2.3 字符串类型

char(4) # 超过四个字符直接报错 不够四个字符 空格补全varchar(4) # 超过四个字符直接报错 不够有几个存几个char 缺点:浪费空间 ;优点:存储都方便varchar 优点:有点节省空间;缺点:存取的麻烦 他是不定长的

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

2.3.2.4 4、枚举类型集合类型

enum：单选，enum()括号内为可选项

set：多选，set()括号内为可选项

create table studens(id int(8) primary key,name char(8) not null,sex enum('男','女') )；

2.3.2 约束条件

默认值：default

无符号类型：unsigned

不为空：not null

唯一：unique

主键：primary key

外键：foreign key…列名 references 表1(列名）；外键用在一对多关系时，定在多的表中

create table dep(id int primary key auto_increment,dep_name char(16) not null,dep_desc char(16) not null);create table emp(id int primary key auto_increment,name char(16) not null,dpt_id int,foreign key(dpt_id) references dep(id)//同步删除on delete cascade//同步更新on update cascade);

自增：auto_increment

2.3.3 表操作

2.3.3.1 创建表

create table 表名(列名1 数据类型(数据类型宽度) 约束条件，列名2 数据类型(数据类型宽度) 约束条件···);

#例：create table employer(id int(8),name char(20));#整型括号里面的数字不是表示限制位数 是显示长度#也就是说 如果数字没有超过8位 那么默认使用空格填充到8位#如果数字超过了8位 有几位存几位#char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

查看当前数据库下所有表：

show tables;

查看表的结构：

DESC 表名;

2.3.3.2 修改表：

• 修改表名：

alter table 表名 rename 新表名;

• 添加列

alter table 表名 add 列名 数据类型 约束条件，add 列名 数据类型 约束条件...;#添加列到最后面：alter table 表名 add 列名 数据类型 约束条件 first;#添加列到最前面alter table 表名 add 列名 数据类型 约束条件 after已有的列名;#添加列到任意位置

• 删除列

alter table 表名 drop 列名;

• 修改列

alter table 表名 modify 列名 新数据类型;#修改列的数据类型：alter table 表名 change 旧列名 新列名 新数据类型 新约束；#修改列的列名和数据类型：

• 删除表

drop table 表名;

2.3.4 数据操作（增删改查）

2.3.4.1 插入数据（INSERT INTO)

语法：

• 插入单行数据：

insert into 表名 values (值1, 值2, 值3, 值3, ...., 值n); 

• 插入多行数据：

insert intO 表名 values(值1, 值2, 值3, 值3, ...., 值n),(值1, 值2, 值3, 值3, ...., 值n),(值1, 值2, 值3, 值3, ...., 值n);

• 指定列插入数据：

insert into 表名(字段1,字段2, 字段3) values (值1, 值2, 值3)；

注：插入数据的字段有几个，values后面的数据就要有几个。

2.3.4.2 修改数据(UPDATE)

update 表名 set 列名=新值 where 条件；

2.3.4.3 删除数据（DELETE)

delete from 表名 where 条件；

2.3.4.4 查询数据（SELECT）：

• SELECT查询语法：

SELECT * FROM TABLE1;#*表示查询表内所有列SELECT 字段1,字段2,字段3 FROM TABLE1;#查询字段1，字段2，字段3内的内容

• WHERE子句：通过一定条件运算条件进行数据的筛选、查询、删除、修改

  select * from 表名 where 条件运算（运算符）；

  • 运算符：

    • 算术运算符：

    加：+ ；减：- ；乘：* ；除：/ ；取整：DIV ；取余：MOD

    SELECT 2+4; #加>6SELECT 6-4; #减>2SELECT 2*4; #乘>8SELECT 8/4; #除>2SELECT 10 DIV 4; #取整>2SELECT 10 MOD 3; #取余>1

    • 比较运算符

    符号	描述	符号	描述
    =	等于	!=	不等于
    <	小于	<=	小于等于
    >	大于	>=	大于等于
    	不等于		两边为NULL为1，否则为0
    BETWEEN…AND…	在两数之间	NOT BETWEEN…AND…	不在两数之间
    IN	在集合中	NOT IN	不在集合中
    IS NULL	为空	IS NOT NULL	不为空

    • 逻辑运算符

    运算符	描述	运算符	描述
    NOT或！	逻辑非	AND	逻辑与
    OR	逻辑或	XOR	逻辑异或

    • 位运算符

    运算符	描述	运算符	描述
    &	按位与	|	按位或
    ^	按位异或	!	取反
    <<	左移	>>	右移

• **排序（ORDER BY）：**按某列进行排序，默认排序方式升序（asc），降序为desc

select * from 表名 ORDER BY ID;#按id进行升序排序select * from 表名 ORDER BY ID DESC；#按id进行降序排序

• 限制(LIMIT)：LIMIT限制显示的记录数量

select * from 表名 where 条件 limit n；#显示n条数据#可与OFFSET配合使用select * from 表名 where 条件 limit n OFFSET M；#跳过M条数据，显示n条数据

OFFSET：跳过多少条数据。

• IFNULL语句：

SELECT IFNULL(A,B) AS somename；#如果A为空，则显示B，否则显示A。

• 分组(GROUP BY) :按某列数据进行分组

SELECT post FROM EMPLOYEE GROUP BY post;#按post这列进行分组

• HAVING子句：

  在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

  HAVING 子句可以让我们筛选分组后的各组数据。

  • 聚合函数

  函数	说明	函数	说明
  avg(列名)	查该列的平均值	sum(列名)	查该列的和
  max(列名)	查该列的最大值	count(列名)	查该列有数据的记录数
  min(列名)	查该列的最小值	group_concat()	显示分组里的数据详情

  ​

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value;

• 去重语句：(DISTINCT)

select DISTINCT 列名 from 表名;

• 子查询：select语句里面包含一个select语句

select 列名 from(select * from 表名 where 条件) as 替代名 where 替代名.条件;select 列名 from 表名 where 列名>(select 列名 from表名 where 条件）；

注：子查询作为一个值使用时需返回一个确定的值。

• UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同

SELECT column_name(s) FROM table1UNIONSELECT column_name(s) FROM table2;#注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。#注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。SELECT column_name(s) FROM table1UNION ALLSELECT column_name(s) FROM table2;

带有where的union语句：

SELECT country, name FROM WebsitesWHERE country='CN'UNION ALLSELECT country, app_name FROM appsWHERE country='CN'ORDER BY country;

列转行:case when

select name,sum(case when subject = '语文' then score else 0 end) '语文',sum(case when subject = '数学' then score else 0 end) '数学',sum(case when subject = '体育' then score else 0 end) '体育'from studentgroup by name;

行转列：union

select *from(select t.name,'语文' as 'subject',max(t.语文) as 'score'from temp_student tgroup by t.nameunion select t.name,'数学' as 'subject',max(t.数学) as 'score'from temp_student tgroup by t.nameunion select t.name,'体育' as 'subject',max(t.体育) as 'score'from temp_student tgroup by t.name) sorder by s.name;

2.3.4.4.2 多表联合查询

• 内联接（INNER JOIN tablename ON …)：INNER JOIN关键字在表中存在至少一个匹配时返回行。

ELECT column_name(s)FROM table1INNER JOIN table2ON table1.column_name=table2.column_name;

• 外联接（OUTER JOIN)

  • 左表联接(LEFT JOIN table2 ON…)：从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

  SELECT column_name(s)FROM table1LEFT JOIN table2ON table1.column_name=table2.column_name;

  • 右表联接（RIGHT JOIN table2 ON…)：从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

  SELECT column_name(s)FROM table1RIGHT JOIN table2ON table1.column_name=table2.column_name;

  • 全联接（FULL OUTER JOIN）：只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

  SELECT column_name(s)FROM table1FULL OUTER JOIN table2ON table1.column_name=table2.column_name;

  **注释：**FULL OUTER JOIN 关键字返回左表（Websites）和右表（access_log）中所有的行，无论数据在左右表有没有匹配。

2.3.5字符串函数

[mysql官方文档]：[https://www.mysqlzh.com/doc/116.html]

• CONCAT(str1,str2,…)

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。 如果所有参数均为非二进制字符串，则结果为非二进制字符串。 如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如： SELECT CONCAT(CAST(int_col AS CHAR), char_col)

mysql> SELECT CONCAT('My', 'S', 'QL');-> 'MySQL'mysql> SELECT CONCAT('My', NULL, 'QL');-> NULLmysql> SELECT CONCAT(14.3);-> '14.3'

• SUBSTRING(str,pos) , SUBSTRING(str FROM pos) ，SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。

mysql> SELECT SUBSTRING('Quadratically',5);-> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4);-> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6);-> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3);-> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3);-> 'aki'mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);-> 'ki'

• LENGTH(str)

返回值为字符串str 的长度，单位为字节。一个多字节字符算作多字节。这意味着 对于一个包含5个2字节字符的字符串， LENGTH() 的返回值为 10, 而 CHAR_LENGTH()的返回值则为5。

mysql> SELECT LENGTH('text');-> 4

• LEFT(str,len)

返回从字符串str 开始的len 最左字符。

mysql> SELECT LEFT('foobarbar', 5);-> 'fooba'

• RIGHT(str,len)

从字符串str 开始，返回最右len 字符。

mysql> SELECT RIGHT('abc',1);-> 'c'

• REVERSE(str)

返回字符串 str ，顺序和字符顺序相反。

mysql> SELECT REVERSE('abc');-> 'cba'

• UPPER(str)

返回字符串str， 以及根据最新字符集映射转化为大写字母的字符 (默认为cp1252 Latin1).

mysql> SELECT UPPER('Hej');-> 'HEJ'

• LOWER(str)

返回字符串 str 以及所有根据最新的字符集映射表变为小写字母的字符 (默认为 cp1252 Latin1)。

mysql> SELECT LOWER('QUADRATICALLY');-> 'quadratically'

• REPLACE(str,from_str,to_str)REPLACE(str,from_str,to_str)REPLACE(str,from_str,to_str)

返回字符串 str 以及所有根据最新的字符集映射表变为小写字母的字符 (默认为 cp1252 Latin1)。

mysql> SELECT LOWER(‘QUADRATICALLY’);

​ -> ‘quadratically’

返回字符串str 以及所有被字符串to_str替代的字符串from_str 。

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');-> 'WwWwWw.mysql.com'

• RTRIM(str)

返回字符串 str ，结尾空格字符被删去。

mysql> SELECT RTRIM('barbar '); -> 'barbar'

• LTRIM(str)

返回字符串 str ，其引导空格字符被删除。

mysql> SELECT LTRIM('barbar');-> 'barbar'

2.3.6 日期时间函数

• DATEDIFF(expr2,expr)

DATEDIFF() 返回起始时间 expr和结束时间expr2之间的天数。Expr和expr2 为日期或 date-and-time 表达式。计算中只用到这些值的日期部分。

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');-> 1

• CURDATE()

将当前日期按照’YYYY-MM-DD’ 或YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。

mysql> SELECT CURDATE();-> '1997-12-15'mysql> SELECT CURDATE() + 0;-> 19971215

• CURTIME()

将当前时间以’HH:MM:SS’或 HHMMSS 的格式返回， 具体格式根据函数用在字符串或是数字语境中而定。

mysql> SELECT CURTIME();-> '23:50:26'mysql> SELECT CURTIME() + 0;-> 235026

• DATE_ADD(date,INTERVAL expr type) 添加
• DATE_SUB(date,INTERVAL expr type) 减去

这些函数执行日期运算。 date 是一个 DATETIME 或DATE值，用来指定起始时间。 expr 是一个表达式，用来指定从起始日期添加或减去的时间间隔值。 Expr是一个字符串;对于负值的时间间隔，它可以以一个 ‘-’开头。 type 为关键词，它指示了表达式被解释的方式。

关键词INTERVA及 type 分类符均不区分大小写。

2.5 数据库索引：

2.5.1索引类型：

1. 主键索引，主键自动的为主索引（primary key）
2. 唯一索引，UNIQUE
3. 普通索引 INDEX
4. 全文索引 FULLTEXT【适用于MyISAM引擎】，开发中考虑使用全文搜索Solr和ElasticSearch（ES )

2.5.12添加索引

基本语法：

索引名一般为：字段名_index

• 添加主键索引：

#1创建表时，进行添加CREATE TABLE tb1 (`id` INT PRIMARY KEY,`name` VARCHAR(32));#2创建完表后添加主键索引ALTER TABLE tb1 ADD PRIMARY KEY (id);

• 添加唯一索引

#方法一：CREATE UNIQUE INDEX 索引名 ON 表名 (列名)；#方法二：ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (列名);

• 添加普索引

#方法一：CREATE INDEX 索引名 ON 表名 (列名)；#方法二：ALTER TABLE 表名 ADD INDEX 索引名 (列名);

2.5.3 删除索引

基本语法：

#删除普通索引或者唯一索引#法一：DROP INDEX 索引名 ON 表名；#法二ALTER TABLE 表名 DROP INDEX 索引名;#删除主键索引：ALTER TABLE 表名 DROP PRIMARY KEY;

2.5.4 查询索引：

SHOW INDEXES FROM 表名;

2.5.5 索引使用细节：

1. 频繁查询的条件字段应该创建索引；
2. 唯一性太差的字段不适合创建索引
3. 更新频繁的字段不适合创建索引
4. 不会出现在WHERE子句中的字段，不该创建索引

2.6 MYSQL事务

2.6.1概念：

事务用于保证数据的一致性，它是由一组相关的dml（增删改）语句组成，该组的dml语句要么全部成功，要么全部失败。

事务和锁：

当执行事务操作时（dml语句），mysql会在表上加锁防止其他用户修改表的数据。

2.6.2 操作事务的指令：

#开启事务start transaction;#或者set autocommit=off;#设置保存点：savepoint 保存点名;#回退事务到某个保存点rollback to 保存点名;#回退所有事务rollback#提交事务（结束事务），不能再回退。commit

2.6.3 事务使用细节：

1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回退。
2. 如果开始一个事务，没有创建保存点，那么使用rollback，回退到事务开始时的状态。
3. 可以在事务没有提交前，创建多个保存点。
4. 在创建了多个保存点后，可以选择回退到任意一个保存点，
5. mysql的事务机制需要innoDB存储引擎支持。

2.6.4 事务的隔离级别（ISOLATION LEVEL)

事务隔离级别操作指令：

#1、查看当前会话的隔离级别select @@tx_isolation;#2、查看系统当前隔离级别select @@global.tx_isolation;#3、设置当前会话隔离级别set session transaction isolation level 隔离级别;#4、设置系统当前隔离级别set global transaction isolation level 隔离级别;

2.6.5 mysql 存储引擎

mysql 常用的三种存储引擎：InnoDB，MYISAM，MEMORY。

区别：

MYISAM不支持事务，也不支持外键，使用表级锁，但访问速度快。

InnoDB支持事务和外键，行级锁，但是写的处理效率差，并且会占用更多的磁盘空间以保留数据和索引。

MEMORY使用内存来存储表，每个memory表实际对应一个磁盘文件，存在内存中没用io读写，访问非常快，但是一旦服务关闭，表中的数据就会丢失，保留表结构。

指定存储引擎操作指令：

创建表时指定存储引擎，可使用ENGINE参数：

CREATE TABLE students(id int,name char) ENGINE = InnoDB;

更改已有表的存储引擎，可使用ALTER TABLE语句：

ALTER TABLE students ENGINE = MyISAM;

3、MySQL与Python的交互使用

安装pymysql模块

pip install pymysql

导入pymysql模块

import pymysql

连接数据库（pymysql.connect）：

import pymysql#连接数据库conn = pymysql.connect(user='root',password='123456',host='127.0.0.1', #服务器ip地址database='practice',#数据库名字port=3306,#端口号charset='utf8')cursor = p.cursor(pymysql.cursors.DictCursor)#创建sql语句执行光标对象sql = 'select * from workers;'#执行sql语句res = cursor.execute(sql)print(cursor.fetchall())#获取所有行

创建sql语句执行光标对象（conn.cursor）：