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、整数型:

类型大小范围(有符号)范围(无符号)用途
TINYINT1 Bytes(-128,127)(0,255)小整数值
SMALLINT2 Bytes(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 Bytes(-8 388 608,8 388 607)(0,16 777 215)大整数值
NT或INTEGER4 Bytes(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值
BIGINT8 Bytes(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值

2、浮点型:

类型大小范围(有符号)范围(无符号)用途
FLOAT4 Bytes(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度浮点数值
DOUBLE8 Bytes(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度浮点数值
DECIMAL对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值
float(255, 30)# 总共有255位 小数占用30位double(255, 30)# 总共有255位 小数占用30位decimal(65, 30)# 总共有65位 小数占用30位
2.3.2.2 日期时间类型
类型大小( bytes)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值,时间戳
2.3.2.3 字符串类型
类型大小用途
CHAR0-255 bytes定长字符串
VARCHAR0-65535 bytes变长字符串
TINYTEXT0-255 bytes短文本字符串
TEXT0-65 535 bytes长文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
BLOB0-65 535 bytes二进制形式的长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
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之间的天数。Exprexpr2 为日期或 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)

事务隔离级别脏读不可重复读幻读是否加锁
读未提交(ED)
读提交(READ COMMITTED)
重复读(REPEATABLE READ)
可串行化(SERIALIZABLE)

事务隔离级别操作指令:

#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读写,访问非常快,但是一旦服务关闭,表中的数据就会丢失,保留表结构。

特点MYISAMInnoDBMemory
批量插入的速度
事务安全支持
全文索引支持
锁机制表锁行锁表锁
存储限制64TB有,基于内存
B树索引支持支持支持
哈希索引支持支持
集群索引支持
数据缓存支持支持
索引缓存支持支持支持
数据可压缩支持
空间使用
内存使用
支持外键支持

指定存储引擎操作指令:

创建表时指定存储引擎,可使用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):