mysql 笔记 （up=黑马程序员）
一、 笔记内容
-- it黑马教程 --
-- part1 基础教程
USE mytest;
DROP TABLE info;
CREATE TABLE IF NOT EXISTS `info`(
`info_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`info_name` VARCHAR(40),
`info_tel` VARCHAR(40),
`info_address` VARCHAR(100),
PRIMARY KEY(`info_id`)
)ENGINE=INNODB CHARSET=utf8mb4;
-- 数据插入： 一次性插入多个数据
INSERT INTO info VALUES
(NULL, "laohu", "13612341001", "Gansu"),
(NULL, "wit", "13612341002", "Liaoning"),
(NULL, "book", "13612341003","Gansu"),
(NULL, "will", "13612341004", "Liaoning");
INSERT INTO info(info_id, info_name, info_tel, info_address) VALUES
(NULL, "cat", "13612341005", "Gansu"),
(NULL, "monkey", "13612341006", "Liaoning"),
(NULL, "dog", "13612341007", "Gansu"),
(NULL, "bird", "13612341008", "Liaoning");
-- 表的赋值语句，很特别的格式
INSERT INTO info(info_name, info_tel, info_address) SELECT info_name, info_tel, info_address FROM info WHERE info_id=3;
UPDATE info SET info_date = NOW() WHERE info_id=9;
SELECT * FROM info;
UPDATE info SET info_tel="18794846006";
-- 显示表的结构
DESC info;
-- 表的结构： 修改字段
ALTER TABLE info ADD info_tel VARCHAR(100);
UPDATE info SET info_tel="18794846006";
ALTER TABLE info ADD info_date DATETIME;
UPDATE info SET info_date=NOW();
-- 表的结构： 删除字段
ALTER TABLE info DROP tel;
-- 创建索引
CREATE INDEX index_info_name ON info(info_name);
SHOW INDEX FROM info ;
DROP INDEX index_info_name ON info;
DROP TABLE salary;
CREATE TABLE IF NOT EXISTS `salary`(
`s_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`s_name` VARCHAR(40),
`s_tel` VARCHAR(40),
`s_salary` VARCHAR(40),
PRIMARY KEY(`s_id`)
)ENGINE=INNODB CHARSET=utf8mb4;
INSERT INTO salary VALUES
(NULL, "laohu", "13612341001", "2000"),
(NULL, "wit", "13612341002", "4000"),
(NULL, "book", "13612341003","6000"),
(NULL, "will", "13612341004", "8000"),
(NULL, "cat", "13612341005", "10000"),
(NULL, "monkey", "13612341006", "12000"),
(NULL, "dog", "13612341007", "14000"),
(NULL, "bird", "13612341008", "16000");
SELECT * FROM salary;
-- 子查询
SELECT * FROM salary WHERE s_name IN (SELECT info_name FROM info WHERE info_tel LIKE "187%");
-- 正则表达式
SELECT * FROM salary WHERE s_name REGEXP "[bo]";
-- 多个表的数据合并
SELECT * FROM info
UNION
SELECT * FROM salary;
SELECT * FROM info
WHERE info_name IN (SELECT s_name FROM salary WHERE s_salary > 6000 );
SELECT * FROM info WHERE info_address REGEXP "^[Gs]";
-- like 占位符， “_” 代表一个任意字符
SELECT * FROM info WHERE info_name LIKE '___';
-- 聚合函数avg
SELECT AVG(s_salary) FROM salary;
SELECT info_address, COUNT(info_address) FROM info GROUP BY info_address;
SHOW GRANTS FOR 'root'@'localhost' ;
SELECT info_id, info_name, UPPER(info_address) AS info_address FROM info;
-- 多个表单查询
SELECT * FROM info INNER JOIN salary ON info.`info_id`=salary.`s_id`;
SELECT * FROM info LEFT JOIN salary ON info.`info_id`=salary.`s_id`;
SELECT * FROM info RIGHT JOIN salary ON info.`info_id`=salary.`s_id`;
SELECT * FROM info WHERE info_id IN (SELECT s_id FROM salary WHERE s_salary >= 8000);
SELECT * FROM info WHERE info_name IN (SELECT s_name FROM salary WHERE s_salary >= 8000);
SELECT * FROM (SELECT * FROM salary WHERE s_salary >8000) AS sub;
-- sql语句格式化
SELECT
*
FROM
info
INNER JOIN salary
ON salary.s_id = info.`info_id` ;
SELECT
s.`s_id` AS sid,
i.`info_id`,
i.`info_name`
FROM
info i
INNER JOIN salary s
ON s.s_id = 1
OR s.`s_id` = 4
ORDER BY s.`s_id` ASC ;
-- 事务
SELECT @@autocommit;
SELECT @@TRANSACTION_ISOLATION;
SELECT @@TRANSACTION;
SELECT @@transaction_isolation;
-- part 2 进阶教程
-- 显示表的创建语句
SHOW CREATE TABLE info ;
-- 存储引擎
-- 体系结构： 连接层、服务层、引擎层(index)、存储层(硬盘)
-- 主要存储引擎：
-- innodb： 支持： 事务 行级锁 外键
-- myisam： x （表级别的锁） x
-- 基本操作：
-- 查看存储引擎： show engines;
SHOW ENGINES;
-- 查看表的搜索引擎： SHOW CREATE TABLE table_name；
SHOW CREATE TABLE info;
CREATE INDEX info_idx_nt ON info(info_name, info_tel);
DROP INDEX info_idx_nt ON info;
EXPLAIN SELECT * FROM info WHERE info_name="wit" AND info_tel LIKE "136%";
SHOW INDEX FROM info;
EXPLAIN SELECT * FROM info WHERE info_id = 3;
-- 去除重复项
SELECT DISTINCT info_address FROM info;
SELECT COUNT(DISTINCT info_address) email_num FROM info;
-- 截取字符
SELECT info_id, info_name, SUBSTRING(info_tel, 1,3) tel, SUBSTRING(info_address,1,1) addr FROM info;
-- load 使用
-- 登录: mysql --local-infile -uroot -p
-- 查询是否开启local-infile： select @@local_infle; 默认状态：local_infile=0;
SELECT @@local_infile ;
-- 开启local_infle: set global local_infle=1;
SET GLOBAL local_infile=1;
-- 关闭local_infile: set global local_infile=0;
SET GLOBAL local_infile=0;
-- local 使用： load data loca infile '/path/load_data_file_name' into table 'table_name'
-- field terminated by '' // 域分隔符（字段分隔符）
-- lines terminated by ''; // 行分隔符（row分隔符，记录分隔符）
EXPLAIN SELECT info_address, COUNT(info_address) FROM info GROUP BY info_address;
SELECT i.*
FROM info i, (SELECT s_id FROM salary WHERE s_salary >= 4000 LIMIT 0, 2) s
WHERE i.info_id =s.s_id;
-- 这条语句很重要，因为查询结果作为表。“查询结果”作为表，必须有‘别名’。
SELECT s.* FROM (SELECT * FROM salary WHERE s_salary >= 4000) s;
SELECT COUNT(1) recorder_num FROM salary;
SELECT s.* , CURTIME() FROM salary s;
-- view
-- create view
-- create or relace view 'view_name' as select... with...
CREATE OR REPLACE VIEW info_view
AS SELECT info_id, info_name, info_tel FROM info WHERE info_address LIKE 'G%';
-- search view
SELECT * FROM info_view WHERE info_name = 'book';
-- alter view
-- create or repalce view 'view_name' as select... with...
-- alter view 'view_name' as select... with...
ALTER VIEW info_view AS SELECT * FROM info;
-- delete view
DROP VIEW IF EXISTS info_view;
-- 存储过程
-- 存储过程： 创建
-- create procedure 'procedure_name'(parameter_list)
-- begin
-- SQL_sequences
-- end;
-- 示例： 存储过程定义没成功，不知道为什么
DELIMITER ;;
CREATE PROCEDURE ds()
BEGIN
DECLARE usr_age INT DEFAULT 0;
IF usr_age >= 0 THEN
SET usr_age := 30;
ELSE
SET usr_age := 1;
END IF
SELECT * FROM info;
END;;
DELIMITER ;
CALL ds;
-- 存储过程： 调用
-- cal procedure_name(parameter_list);
-- 存储过程： 查看
-- show create procedure 'procedure_name';
-- 存储过程： 删除
-- drop procedure if exists 'procedure_name';
-- 存储过程： 参数类型：
-- 语法格式： create procedure 'proc_name'([in|out|inout] var_name var_type) begin end
-- in（默认的参数类型）: 该类型的参数作为输入，也就是调用“过程”时，需要传入的值；
-- out: 该类型的参数作为输出，也就是该参数可以作为“过程”的“返回值”；
-- inout: 该参数既可以作为输入参数，也可以作为输出参数；
-- 示例：
-- proc_bk(in var_data int, out ret_result varchar(40))
-- @ret_reuslt： 用户自定义变量
DROP PROCEDURE IF EXISTS bk;
DELIMITER ;;
CREATE PROCEDURE bk(IN var_data INT, OUT ret_result VARCHAR(40))
BEGIN
IF var_data >= 30 THEN
SET ret_result="ok";
ELSE
SET ret_result="fail";
END IF
END ;;
DELIMITER ;
CALL bk(40, @ret_result);
SELECT @ret_result;
-- proc_change(inout usr_data double)
DROP PROCEDURE IF EXISTS proc_change;
DELIMITER ;;
CREATE PROCEDURE proc_change(INOUT usr_data DOUBLE)
BEGIN
SET usr_data := usr_data * 0.5;
END;;
DELIMITER ;
SET @usr_data := 150;
CALL proc_change(@usr_data);
SELECT @usr_data;
-- proc_repeat
DROP PROCEDURE IF EXISTS proc_repeat ;
DELIMITER ;;
CREATE PROCEDURE proc_repeat(IN input INT, OUT output INT)
BEGIN
DECLARE psum INT DEFAULT 0;
REPEAT
SET psum := psum + 1;
SET input := input - 1;
UNTIL input <= 0;
END REPEAT
SET output := psum;
END;;
DELIMITER ;
SET @output := 0;
CALL proc_repeat(30, @output);
-- 存储过程： 控制
-- if: if condition1 then elseif condition2 ...else... end if
-- case:
-- 语法1
-- case
-- when 'condition1' then 'sql_statement'
-- when 'condition2' then 'sql_statement'
-- else 'sql_statement'
-- end case;
-- 语法2
-- case variable_name
-- when 'constant_value1' then 'sql_statement'
-- when 'constant_value2' then 'sql_statement'
-- else 'sql_statement'
-- end case;
-- while:
-- 语法1
-- while 'condition' do
-- 'sql_statement'
-- end while;
-- repeat（until:满足条件时，退出循环；不满足条件时，进行循环）:
-- 语法1
-- repeat
-- 'sql_statement'
-- until 'condition'
-- end repeat
-- loop:
-- 说明：
-- leave: 直接退出循环； 相当于break；
-- iterate: 结束本次循环，进入下次循环；相当于continue；
-- 语法1：
-- [label: ] loop
-- 'sql_statement'
-- leave label; (类似break)
-- iterate label; (类似continue)
-- end loop [label];
-- (存储类型：游标)cursor:
-- 说明： cursor，用来存储“多值”。cursor是用来存储“查询结果集”，
-- 在存储过程和函数中可以使用游标对结果集进行循环处理。
-- 语法1：
-- 声明游标: declare cursor_name cursor for 'select ...from...';
-- 打开游标： open cursor_name;
-- 获取游标： fetch cursor_name into var1 [, var2, ...];
-- 关闭游标： close cursor_name;
-- (条件处理程序)handler:
-- 变量【系统变量，会话变量（当前连接为‘会话’），局部变量】
-- 系统变量
-- 查看系统变量
-- show [session | global] variables;
-- show [session | global] variables like... ;
-- select @@[session | global] 系统变量名称;
-- 设置系统变量
-- set [session | global] 系统变量名=变量值;
-- set @@[session | global] 系统变量名=变量值;
-- 用户自定义变量(当前会话内定义的变量)
-- 自定义定义变量的定义
-- 不用提前声明变量，直接使用，格式为： @variable_name
-- 自定义变量的赋值(推荐赋值符号 ：= )
-- set @variable_name1 = value1 [,@variable_name2 = value2 ,...]
SET @data1 ='book_mysql', @data2=3;
SELECT @data1, @data2;
-- set @variable_name1 := value1 [,@variable_name2 := value2 ,...]
SET @data3 :='book_java', @data4=30;
SELECT @data3, @data4;
-- select @variable_name1 := value1 [,@variable_name2 := value2 ,...];
SELECT @data5:='book_c', @data6:=300;
SELECT @data5, @data6;
-- select field1 [,field2 , ...] into @variable_name1 [,@variable_name2 ,...] from tblname;
-- 说明：上面需要返回一行数据； 如果返回多行数据，我还不知道怎么处理
SELECT info_id, info_name INTO @data7, @data8 FROM info WHERE info_id=1;
SELECT @data7, @data8;
-- 局部变量（在过程的变量）
-- 局部变量的声明（关键字： declare）
-- 变量声明的位置： begin ... end
-- declare 'var_name' type [default 'value'];
-- type = int, bigint, char, varchar, data, ...
-- declare usr_name varchar(40) default 'makabaka';
-- 局部变量的赋值：
-- 说明： 没有符号 @
-- set var_name1 = value1 [,@var_name2=value2 ,...];
-- set var_name1:=value1 [,@var_name2:=value2 ,... ];
-- select field1 [, field2, ...] into var_name1 [, var_name2 ,...] from 'tbl_name';
-- 局部变量应用示例
DELIMITER ;;
CREATE PROCEDURE proc_msg()
BEGIN
DECLARE usr_name VARCHAR(40) DEFAULT 'empty';
SET usr_name := 'makabaka';
SELECT info_address INTO usr_name FROM info WHERE info_id=1;
SELECT usr_name;
END;;
DELIMITER ;
CALL proc_msg();
-- 触发器
-- 触发器语法：定义
-- create trigger trigger_name
-- before/after insert/update/delete
-- on tbl_name for each row
-- begin
-- 'sql_statement';
-- end;
-- 触发器语法：查看
-- show triggers;
-- 触发器语法：删除(删除'特定数据库'下的trigger; 不指定数据库的名字，删除当前数据库下的trigger)
-- drop trigger [database_name, ]trigger_name;
-- 锁（多个终端/多个数据库的连接）
-- 锁的类型
-- 全局锁， 锁定数据库中的所有表。
-- 说明： 只能读，不能写；
-- 应用场景： 数据库的备份；
-- 应用示例(数据备份; 需要两个终端):
-- (myslq提示符) flush tables with read lock;
-- (cmd提示符) mysqldump -uroot -p database_name > db.sql;
-- (myslq提示符) unlock tables;
-- 表级锁， 每次操作锁定整个表。
-- 说明：
-- 表共享锁（read lock）: 阻止其他客户端的“写”，不阻止其他客户端的“读”。
-- 表独占锁（write lock）： 阻止其他客户端的“读”和“写”。
-- 元数据锁（meta data lock, MDL）： MDL加锁过程有系统自动控制，在访问一张表的时候自动加锁。MDL锁，
-- 用于维护表的元数据一致性，在表上有事务活动的时候，不可以对元数据进行写入操作。
-- 为了避免DML和DDL冲突，保证读写的正确。
-- 查看元数据锁：
-- select object_type, object_schema, object_name, lock_type, lock_duration
-- from performance_schema.metadata_locks;
-- 意向锁：
-- 说明： 为了避免DML在执行时，加的行锁和表锁的冲突，在InnnoDB中引入意向锁，
-- 使得表级锁不用检查每行数据是否加锁，
-- 使用意向锁来减少表锁的检查。
-- 目的： 解决‘行锁’和‘表锁’的冲突。
-- 意向锁的类型：
-- 意向共享锁（is, 类似read）: 由语句 select ... lock in share mode 添加；
-- 说明： 与表锁共享锁(read)兼容，与表锁排他锁（write）互斥；
-- 意向排他锁（ix, 类似write）： 由insert/update/delete/select ... for update 添加。
-- 说明： 与表锁共享锁（read)及排他锁(write)都互斥。 意向锁之间，不会互斥。
-- 意向锁的查询：
-- select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
-- from performance_schema.data_locks;
-- 意向锁的使用：
-- (加行锁的共享锁，同时也为这张表加上意向共享锁)意向共享锁:
-- select * from info where info_id =1 lock in share mode; // 行共享锁
-- （加行锁的排他锁，insert/update/delete语句自动意向排他锁）意向排他锁：
-- insert/update/delete 语句自动加上“意向排他锁”
-- 语法1：
-- 加锁： lock tables tbl_name1 read/write [, tbl_name2 read/write ,...] ;
-- 解锁： unlock tables ; /客户端断开连接；
-- 行级锁， 每次操作锁定对应的整行数据。
-- 说明： ‘行锁’是对‘索引项’加锁； ‘行锁’不是对“记录（整行）”加锁。
-- 分类：
-- 行锁（record lock）： 锁定“整行”记录；
-- 说明： InnoDB的行锁是针对‘索引’加的锁，不通过‘索引’检索数据，那么InnoDB将对表中的所有记录加锁，此时就升级为表锁。
-- （insert/delete/update）只有把“索引”作为检索条件时，加的锁才是“行锁”；否则，加的锁时“表锁”。
-- 共享锁（s）: 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。共享锁之间兼容；共享锁和排他锁之间不兼容。
-- 排他锁（x）： 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。排他锁之间不兼容；排他锁和共享锁之间不兼容。
-- 加锁情况：
-- (加)排他锁： insert/update/delete/select ... for update;
-- （加）共享锁： select ... lock in share mode;
-- 不加任何锁： select ... from ...;
-- 间隙锁（gap lock）: 锁定“索引记录的间隙”，确保索引记录‘间隙’不变；
-- 防止其他事务在这个间隙进行insert，产生幻读。
-- 在‘RR’隔离级别下都支持。
-- 临键锁（next-key lock）： ‘行锁’和‘间隙锁’组合，同时锁住数据，兵锁住数据前面的‘间隙(gaplock)’。在'RR'隔离级别下支持。
-- 语法：
-- InnoDB 引擎（进阶篇）
-- Mysql 管理工具
-- mysql
-- 功能： mysql 客户端管理工具
-- 语法：
-- mysql -h -P -u -p dbname -e "sql_statement"
-- 应用示例： （常用于脚本）
-- G:\tmpWorkspaces>mysql -uroot -p mytest -e "select * from salary"
-- Enter password: ********
-- mysqladmin
-- 功能（常用于脚本）： 数据库管理
-- mysqlbinlog
-- 功能（常用于脚本）： 查看二进制文件
-- mysqlshow
-- 功能（用于脚本）： 客户端对象查找工具，查看存在哪些数据库、表、表中的列和索引等。
-- 选项：
-- -- count 显示数据库及其表的统计信息（数据库和表可以不指定）
-- 应用示例： mysqlshow -uroot -p --count
-- - i 显示指定数据库或者表的状态信息
-- 语法格式(db_name库名 tbl_name表明)： mysqlshow -uroot -p db_name tbl_name -i
-- 应用示例(mytest数据库)： mysqlshow -uroot -p mytest -i
-- mysqldump
-- 功能（用于脚本）： 数据库的备份和还原
-- 语法格式：
-- mysqldump [option1,...] db_name tbl_name
-- 应用示例： mysqldump -uroot -p mytest info > e:/mytest_info.sql
-- mysqlimport/source
-- 功能（用于脚本）： 数据库(.sql)/数据文件（.txt）的导入
-- 语法1：
-- mysqlimport [option1,...] db_name xx1.txt [,xx2.txt ,...]
-- 语法2：
-- mysql> use db_name; source /path/xx.sql
-- 运维 --
-- 日志
-- 错误日志：
-- 查看日志是否开启： show variables like '%log_error%' ;
SHOW VARIABLES LIKE '%log_error%';
-- 错误日志配置： /etc/my.cnf; systemctl restart mysqld
-- 二进制日志（主从复制，基于此日志）：
-- 功能： 二进制日志（binlog）记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（select/show）语句。
-- 查看二进制日志是否开启： show variables like '%log_bin%';
-- 日志格式： /etc/my.cnf; systemctl restart mysqld
-- 查看日志格式： show variables like '%binlog_format%';
-- statement: 记录sql语句，对数据修改的sql语句也会记录在日志文件中；
-- row: 基于‘行’的日志记录，记录的是‘每一行的数据变更’。（默认）。
-- mixed: 混合statement和rox，默认采用statement。在默写特殊情况下会自动切换为row进行记录。
-- 查看二进制文件：
-- mysqlbinlog [option] logfilename
-- -d 指定数据名称，列出指定的数据库相关操作；
-- -o 忽略掉日志的前n行命令；
-- -v 将行事件（数据变更）重构为sql语句；
-- -w 将行事件（数据变更）重构为sql语句，并输出注释；
-- 二进制文件的删除：
-- reset master 删除全部日志；删除后日志编号从binlog.00001重新开始；
RESET MASTER;
-- purge master logs 'binlog.xxxx' 删除‘编号之前’的所有日志；
-- purge master logs before 'yyyy-mm-dd HH:MM:SS' 删除（时间点）“yyyy-mm-dd HH:MM:SS”之前产生的所有日志；
-- 自动删除二进制文件（设置二进制日志文件的过期时间）
-- 查看是否开启‘二进制日志文件的过期时间’： show variables like '%binlog_expire_second%';
-- 查询日志：记录客户端的所有操作语句
-- 查看‘查询日志’是否开启： show variables like '%general_log%';
-- 开启和关闭“查询日志”的配置： /etc/my.cnf; systemctl restart mysqld
-- 慢查询日志：
-- 查看‘慢查询日志’语句： show variables like '%slow_query%';
SHOW VARIABLES LIKE '%slow_query%';
-- 开启“慢查询日志”： /etc/my.cnf; systemctl restart mysqld
-- 开启慢查询日志： slow_query_log=1
-- 设置‘慢查询时间阈值’（查询时间超过这个数值，就要将日志记录到‘慢查询日志’，单位:秒）： long_query_time=2
-- 主从复制
-- “主从复制”的含义： 将‘主数据库’的‘二进制日志文件’传到‘从属数据库’，“从属数据库”执行从“主数据库”传送来的“二进制日志文件”；
-- 目的是保证“主数据库”和“从属数据库”的数据一致。
-- 分库分表
-- 分库分表的核心思想： 将‘数据’分散存储到多台服务器，使得单一的数据库/表的数据量变小，来环节单一数据库的性能问题。
-- 数据库的拆分方法：
-- 垂直拆分：
-- 垂直拆分特点： 拆分后的数据库保存了原始数据库的“部分”表（分库）和字段（分表）；拆分后的数据库保留了原始数据库的表的完整数据（分库）或字段的完整数据（分表）；
-- 垂直分库： 拆分“数据库”，以“表”为拆分单位，进行拆分；把一个数据库中的“表”拆分到不同的“数据库”之中；
-- 垂直分表： 拆分“表”，以表的“字段”为拆分单位，进行拆分；把“一张表”拆分为“多张表”，将“拆分后的多张表”分散到不同的数据库；
-- 水平拆分：
-- 水平拆分的思想： 以表的“行（记录）”为单位，将“一张表的数据”拆分为“N组数据”，然后将“N组数据”分配到“N个数据库”；
-- 水平拆分的特点： 拆分后的数据库保留着原始数据库的所有的表的结构，只是拆分后的数据库只保留原始数据库的一部分数据而已；
-- 水平分库： 将原始数据库中所有表的数据“按记录（行）分为N组”（N为分库后的数据库数量），然后将“N组数据”分配到N个数据库中；
-- 水平分表： 将“一张表的数据”拆分为“N组数据”，然后将“N组数据”分配到“N个数据库”；
-- mycat：
-- mycat: 数据库分库分表的中间件。
-- 配置文件“xx.xml”，英文区分大小写。
-- 读写分离
-- 读写分离：基于主从复制；
--
二、参考资料：
1、(up = 黑马程序员) 黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括 https://www.bilibili.com/video/BV1Kr4y1i7ru/
本文由 lnlidawei 原创、整理、转载，本文来自于【博客园】； 整理和转载的文章的版权归属于【原创作者】； 转载或引用时请【保留文章的来源信息】：https://www.cnblogs.com/lnlidawei/p/16830519.html
