本文共 12709 字，大约阅读时间需要 42 分钟。
Session是什么？ 会话：创建一个连接 --> 建立了一个会话长连接：一直的连着不断开，除非人为的关闭 --> mysql和ssh连接短连接：先建立3次握手 --> 传输数据 --> 四次断开root@test mysql>show processlist; #注：查看有哪些人连接（几个session）#注：一个连接里面创建很多的事务
buffer：缓存 仍然是一个临时存放数据的地方（写操作） 内存 --> buffer --> 磁盘cache：缓存 读操作 临时存放数据的地方 磁盘 --> cache --> 内存缓存的作用确实起到加速的作用，特别是cache减少系统CPU和进程、内存资源的消耗本质上，从进程的调度来思考作用：节约资源；频繁io影响系统性能（操作系统层面分析；进程方面考虑）
数据的修改都是在内存中进行的（缓冲池）缓冲池大小 最大 内存的80%page页buffer pool是mysql内部的的缓存池，相当于mysql内部存放数据的地方buffer pool最大的大小可以到达整个物理内存的80%root@(none) mysql>show variables like "innodb_buffer_pool_size";| Variable_name | Value || innodb_buffer_pool_size | 536870912 |
Buffer Pool LRU AlgorithmLRU算法 最近最少使用算法 LRU是Least Recently Used的缩写，即最近最少使用，是一种常用的页面置换算法，选择最近最久未使用的页面予以淘汰页化（page）（内存里的单位） mysql里1页 16k 磁盘 文件系统的块 默认4k
脏读 mysql里不允许脏读，因为事务是隔离的其它用户（事务）不能查看当前用户（事务）所做的DML操作的结果。这叫做不允许脏读（dirty read）脏读：一个事务读到了另一个事务未提交的数据已修改但未提交的数据叫做赃数据（注：脏数据在内存中）表中受影响的行被锁定，其它用户（事务）不能在受影响的行上修改数据
锁 颗粒度 表锁 行锁 innodb 行为 读锁 备份的时候 写锁 lock tables t1 write; 共享锁 排他锁
为什么要加锁？ 大并发情况下，为了保持数据的一致性。需要对资源进行管控，需要使用锁，解决资源竞争的问题死锁 死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。 如何解决死锁？ 1 .使用另外的程序来检查是否存在死锁，干预，杀死某些进程 2 .设计程序的时候，考虑获得条件的先后顺序 3 .队列活锁 活锁指的是任务或者执行者没有被阻塞，由于某些条件没有满足，导致一直重复尝试—失败—尝试—失败的过程。处于活锁的实体是在不断的改变状态，活锁有可能自行解开。读锁 --> 读数据的时候，给某行加一个锁 备份的时候写锁 --> 写数据的时候，给某行加一个锁 lock tables t1 write;意向锁共享锁 --> 读锁互斥锁 --> 写锁
表锁READ：当前会话和其它会话都可以读表，但是不能修改表WRITE：当前会话可以读写表，但是其它会话既不能读也不能写
#示例：锁表 （写锁）root@test mysql>lock tables t1 write; #注：写锁是互斥锁，其他的会话不能查和写。写锁会影响到并发#示例：把锁释放root@test mysql>unlock tables;#示例：查询时加锁 添加共享锁root@test mysql>select * from t1 lock in share mode;
事务的4个问题
并发事务的4个问题 脏读 不可重复度 修改了数据 update 幻读 记录是不一样 insert或者delete 丢失更新
事务的4个隔离级别
每个事务都有一个隔离级别（isolation level），它规定了并发运行的两个事务之间是否允许发生上面的问题。 MySQL有4种事务隔离级别：repeatable read 可重复读 默认级别read committed 读已提交read uncommitted 读未提交serializable 串行化
查看数据库及当前会话的事务隔离级别（2种方法） SELECT @@GLOBAL.tx_isolation, @@tx_isolation; root@test mysql>SELECT @@GLOBAL.tx_isolation, @@tx_isolation; root@test mysql>show variables like "tx_isolation";在配置文件中指定数据库的事务隔离级别[mysqld] transaction-isolation = REPEATABLE-READ
#示例：备份jd库[root@cPen ~]# mysqldump -ucali -p'123456' jd >jd.sql#示例：备份所有的数据库[root@cPen ~]# mysqldump -h 192.168.31.165 -ucali -p'123456' --all-databases >jd.sql
#示例：备份TENNIS库[root@cPen ~]# mysqldump -uroot -p'Sanchuang123#' --databases TENNIS >tennis.sqlroot@test mysql>drop database TENNIS; #注：删除TENNIS库#示例：导入TENNIS库[root@cPen ~]# mysql -uroot -p'Sanchuang123#'
事务处理 在Python代码里的经典应用
Python pymysql操纵MySQL时，需要commit提交 #注：自动开启一个事务，自己需要敲db.commit()提交 利用事务 来处理往数据库里写数据[root@cPen ~]# vim sc.py import pymysql# 打开数据库连接db = pymysql.connect(host="192.168.31.165", user="cali", passwd="123456", db="test")# 使用cursor()方法获取操作游标cursor = db.cursor()# SQL 插入语句sql = """INSERT INTO t1(id,name) VALUES (300, 'yiyiqiang')"""try: # 执行sql语句 cursor.execute(sql) # 提交到数据库执行 db.commit()except: # 如果发生错误则回滚 db.rollback()# 关闭数据库连接db.close()[root@cPen ~]# python3 sc.py
Python代码里，自动开启commit --> autocommit的开启conn = pymysql.connect( user = 'cali', passwd = '123456', db = 'test', host = '127.0.0.1', port = 3306, charset = 'utf8', autocommit = True # 自动提交确认)
SELECT语句返回的结果叫做结果集（RESULTSET）。它是一个表格 #注：是一个元组结果集的列标题由SELECT子句中的列名或表达式决定，大小写和列名的大小写保持一致。列标题是左对齐的#示例：Python从数据库里取出来root@(none) mysql>grant all on *.* to 'cali'@'%' identified by '123456';>>> import pymysql>>> db = pymysql.connect(... host = "192.168.31.165",... user = "cali",... passwd = "123456"... )>>> cursor.execute("use test")>>> sql = "select * from t1";>>> cursor.execute(sql)>>> data = cursor.fetchall()>>> type(data) >>> print(data)((1, None), (2, 'zhangheng'), (100, 'zhang'), (300, 'yiyiqiang'), (300, 'yiyiqiang'), (300, 'yiyiqiang'))>>> data[1](2, 'zhangheng')>>> data[1][0]2 root@test mysql>desc totals; #注：查看表结构@@sql_mode 这个变量的作用：就是规定了在mysql里我们写sql语句的时候，哪些是允许 哪些是不允许的操作，也就是一些规矩root@test mysql>select @@sql_mode;+------------------------------------------------------------------------------------------------------------------------+| @@sql_mode |+------------------------------------------------------------------------------------------------------------------------+| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
global 全局变量session 当前会话里的变量 --> 局部变量 全局设置影响从那时起新连接的所有客户端，设置SESSION变量只影响当前的客户端
# MySQL是多线程的服务[root@cPen ~]# yum install psmisc -y[root@cPen ~]# pstree -p|grep mysql #注：pstree 查看进程树 |-mysqld_safe(1056)---mysqld(1270)-+-{ mysqld}(1502) | |-{ mysqld}(1550) | |-{ mysqld}(1551) | |-{ mysqld}(1552)#注：-p 显示pid号 字面量（常量）字面量literal：就是一个不变的、固定的值。字面量总是有一个数据类型 字符串字面量：使用双引号或者单引号括起来的字母或数字的组合强烈建议使用日期字面量时，采用字符串的写法，并且年部分写4位数字强烈建议使用时间字面量时，采用字符串的写法，并且冒号作为分隔符
日期时间字面量和时间戳字面量的区别 年部分的取值范围：前者年的部分是1000---9999，后者年的部分是1970---2037 时间戳字面量在存取时会自动根据时区进行转换：在保存到表中时，自动按UTC（0时区）做转换；当从表中查询时，自动按客户端会话的时区做转换
#注：mysql会和系统是相同时区 查看当前客户端的时区设置root@test mysql>select @@time_zone; #注：当前的时间变量+-------------+| @@time_zone |+-------------+| SYSTEM |+-------------+1 row in set (0.00 sec)root@test mysql>select @@SYSTEM_time_zone; #注：查看时区；系统的时间变量+--------------------+| @@SYSTEM_time_zone |+--------------------+| CST |+--------------------+1 row in set (0.00 sec)
#注：指定时间戳类型root@test mysql>CREATE TABLE user_login(login_time TIMESTAMP,username varchar(20));root@test mysql>insert into user_login values(now(),'cali'); #注：now()函数root@test mysql>select * from user_login;+---------------------+----------+| login_time | username |+---------------------+----------+| 2021-03-13 23:34:25 | cali |+---------------------+----------+1 row in set (0.01 sec)
将客户端的时区修改为东10区（澳大利亚悉尼）：SET time_zone='+10:00';SELECT @@time_zone;SELECT * FROM user_login;
重复的行使用DISTINCT关键字可以去掉结果集中的重复行 （去重） SELECT town FROM players; SELECT DISTINCT town FROM players; SELECT street, town FROM players; SELECT DISTINCT street, town FROM players;
DISTINCT 去重：去除重复的行
写SQL语句 关键字是大小写不敏感的 数据库名、表名、表别名在Linux下是大小写敏感的（可以配置成不敏感的） 列名、列别名是大小写不敏感的
例1：查询所有的罚款信息，要求显示所有的列表：罚款表字段：*条件：select * from PENALTIES;select AMOUNT,PLAYERNO from PENALTIES;select AMOUNT 金额,PLAYERNO 球员编号 from PENALTIES; #注：alias 列起别名select AMOUNT as 金额,PLAYERNO as 球员编号 from PENALTIES;
使用算术运算符
例3：查询所有的罚款信息，要求显示球员编号、罚款金额以及罚款金额加上300之后的结果 select playerno,amount ,amount + 300 from penalties;
使用小括号可以改变运算的优先级 SELECT playerno, amount , 12*amount + 100 FROM penalties; SELECT playerno, amount , 12*(amount + 100) FROM penalties;
NULL值 NULL值，又叫做空值，它表示“未知的”意思。一个列具有NULL值，表示该值是未知的、不确定的NULL值不等于数字0或者空字符串’’，甚至 null != NULL SELECT playerno, name, leagueno FROM players; SELECT playerno, name, leagueno FROM players WHERE leagueno is not null;
如果算术表达式中包含null值，则整个表达式的计算结果就为nullroot@test mysql>select 100*12*NULL;+-------------+| 100*12*NULL |+-------------+| NULL |+-------------+
列别名列别名（alias）：给列取的另一个名字列别名直接跟在列名后面，中间可以加一个AS关键字，也可以不加列别名中如果包含空格或特殊字符，必须使用双引号或单引号括起来。建议使用双引号 SELECT last_name 'full-Name', 12*salary "Annual Salary" FROM employees; SELECT last_name 姓名, commission_pct 佣金 FROM employees;
字符串连接
concat_ws()和concat()的区别？ 两个都是字符串拼接函数，只是concat_ws可以指定分隔符
要把两个字符串值首尾相连的连接起来，如果@@sql_mode中没有设置“PIPES_AS_CONCAT”，就不能使用oracle中的||操作符，只能使用concat函数或者concat_ws函数函数的参数就是需要连接的字符串值。可以有n个参数例11：SELECT concat(last_name, job_id) "Employees" FROM employees;注意：concat函数在连接字符串的时候，只要其中一个是NULL，那么将返回NULL例12： select concat('11','22',null); root@test mysql>SELECT NAME,SEX FROM PLAYERS;root@test mysql>SELECT CONCAT(NAME,SEX) FROM PLAYERS;root@test mysql>SELECT CONCAT(NAME,' ',SEX) FROM PLAYERS;root@test mysql>SELECT CONCAT(NAME,'#',SEX) FROM PLAYERS;
concat_ws()函数, 表示concat with separator,即有分隔符的字符串连接例13：连接后以逗号分隔 SELECT concat_ws(',' , '11','22','33'); root@test mysql>SELECT CONCAT_ws('#',NAME,SEX,town) information FROM PLAYERS; 查看表结构1 .desc DESC PLAYERS;2 .SHOW CREATE TABLE PLAYERS;
使用WHERE子句如果想限制查询返回的行，需要使用WHERE子句 WHERE条件又叫做过滤条件，它从FROM子句的中间结果中去掉所有条件conditions不为TRUE（而为FALSE或者NULL）的行 WHERE子句跟在FROM子句后面 不能在WHERE子句中使用列别名
例1：查询住在Stratford（斯特拉福德，威廉-莎士比亚的出生地）的球员的编号select 表：球员字段：编号条件：住在Stratford townselect PLAYERNO from PLAYERS,town WHERE town = "Stratford";WHERE中出现的字符串和日期字面量必须使用引号括起来这里，字符串字面量写成大写或小写结果都一样，即不区分大小写进行查询。这和ORACLE不同，ORACLE中WHERE条件中的字面量是区分大小写的
字符集和校对规则root@test mysql>show character set; #注：查看mysql里的字符集
collation 校对规则 --> 排序规则A a B b每个字符集有一个默认校对规则。例如，utf8默认校对规则是utf8_general_ci*_bin: binary case sensitive collation，区分大小写 ，直接比较字符的编码*_cs: case sensitive collation，区分大小写，相同字符靠近排列 *_ci: case insensitive collation，不区分大小写
#注：指定字符集root@test mysql>create table guozy1(id int) default charset=utf8;root@test mysql>show create table guozy1;编译安装MySQL时，指定字符集 或者 建库的时候指定字符集
查看数据库支持的所有字符集 show character set;查看数据库支持的所有校对规则 show collation;查看当前字符集和校对规则设置 show variables like 'collation_%'; show variables like 'character_set_%';
比较运算符
例2：得到联盟会员号码为7060的球员的编号SELECT playerno FROM players WHERE leagueno='7060';
例3： 得到没有联盟会员号码的球员的编号SELECT playerno,leagueno FROM players WHERE leagueno <=> NULL;或者：SELECT playerno,leagueno FROM players WHERE leagueno IS NULL;
例4：找出获胜局数等于2并且输掉局数等于3的比赛的编号表：比赛表字段：编号条件：获胜局数等于2并且输掉局数等于3select matchno from MATCHES WHERE LOST = 3 AND WON = 2;SELECT matchno FROM matches WHERE (won,lost) = (2,3); MySQL在内部把条件重写为(won=2) and (lost=3)
例5：找出1962—1964年之间出生的球员的编号、出生日期表：球员字段：编号、出生日期条件：1962—1964年之间出生SELECT playerno,birth_date FROM playersWHERE birth_date BETWEEN ‘1962-01-01’ AND ‘1964-01-01‘;SELECT playerno,birth_date FROM playersWHERE birth_date >= ‘1962-1-1’ and birth_date <= ‘1964-1-1’;#注：BETWEEN …AND… 包含了等于
IN操作符 测试一个列值是否在一个值列表中。值列表中可以是字面量、列或 标量子查询
例6：得到那些出生在1962、1963或1970的球员的编号、出生年份SELECT playerno,year(birth_date) FROM players WHERE year(birth_date) IN (1962,1963,1970);#注：year()函数 可以取出年份 内置函数#注：内置函数 year() 取出年份 month() 取出月份 day() 取出天份
表：球员字段：编号、出生年份条件：出生在1962、1963或1970select playerno,year(birth_date) from PLAYERS WHERE YEAR(BIRTH_DAYE) IN (1962,1963,1970);
例6：得到那些和baker或者Hope同年的球员的编号、姓名select playerno,name from PLAYERS where year(birth_date) in (select year(birth_date) from PLAYERS where name = ‘baker’ or name = ‘Hope’);select playerno,name from PLAYERS where year(birth_date) in (select year(birth_date) from PLAYERS where name in (‘baker’,’Hope’));
例7：对于那些有两局获胜或者两局输掉的所有比赛，得到比赛编号、获胜的局数和输掉的局数SELECT matchno,won,lost FROM matches WHERE 2 IN (won, lost);在mysql内部，in转换成or操作，not in转换成and操作
IN也可以进行成对比较例8：对于那些比分为3比1和3比2的所有比赛，得到比赛编号、获胜局数、输掉局数SELECT matchno, won, lost FROM matches WHERE (won, lost) IN ((3,1),(3,2));
LIKE操作符
实现模式匹配查询或者模糊查询 %：表示0个或者任意多个字符 _：只表示一个任意字符
例9：找出名字以大写字母B开头的球员的名字和编号 SELECT name, playerno FROM players WHERE NAME LIKE 'B%';
例10：得到名字以小写字母r结尾的球员的名字和编号 SELECT name, playerno FROM players WHERE name LIKE '%r';
例11：得到名字的第二个字母为e的球员的名字和编号SELECT name, playerno FROM playersWHERE name LIKE '_e%';
root@(none) mysql>show variables;root@(none) mysql>show variables like "%innodb%"; #注：包含innodb的变量root@(none) mysql>show variables like "time_zone"; #注：精确匹配
REGEXP操作符
^ 代表以什么开头$ 代表以什么结尾[1234abcd] 匹配其中的任意一个[^123] 不是123的任意一个字符[0-9] 取其一[a-z][A-Z][0-z][0-Z]men{3} n出现3次cali{3,6} i出现3-6次liu{5,} u出现5次以上 例13：得到名字以ba开头的球员的编号和名字SELECT playerno, name FROM players WHERE name REGEXP '^ba';
例14：得到名字包含了字母a、b或c的球员的编号和名字SELECT playerno, name FROM players WHERE name REGEXP '[abc]';
例15：对于名字中有字母m、e、n中的一个，并且连续出现2次的球员的编号和名字SELECT playerno,name FROM players WHERE name REGEXP '[men][men]';
逻辑操作符
使用逻辑操作符有4个：and、 or、 not、 xor，用于在WHERE条件中把多个条件组合起来 逻辑与、逻辑或、逻辑非、逻辑异或
AND操作符例16：得到1970年之后出生的每个男球员的编号、名字、性别和出生日期SELECT playerno, name, sex, birth_date FROM players WHERE birth_date > '1970-12-31' AND sex = 'M';
OR操作符例17：得到住在Plymouth或者Eltham的球员的编号、姓名和居住城市SELECT playerno, name, town FROM playersWHERE town = 'Plymouth' OR town = 'Eltham';
NOT操作符 用在一个条件的前面。经常和其它操作符一起使用。例如：not in、not between 、not like、is not null例18：得到那些未住在Stratford的球员的编号、姓名SELECT playerno, name FROM playersWHERE NOT (town = 'Stratford');例19：查找罚款金额不是25美元或者50美元的球员的编号SELECT playerno FROM penaltiesWHERE amount NOT IN (25,50);
XOR操作符例21：得到那些住在Stratford或者出生于1963年的球员的编号、姓名、出生日期，但是不包括那些住在Stratford并且出生于1963年的球员SELECT playerno, name, birth_date FROM playersWHERE (town = 'Stratford' ) XOR (year(birth_date)='1963');
转载地址：http://tjtuk.baihongyu.com/