MySQL8.0随笔整理

Mysql

目录

数据库操作

CREATE SCHEMA
IF
	NOT EXISTS db_test CHARACTER 
	SET gbk;
/*创建数据库,设置默认编码*/
CREATE DATABASE
IF
	NOT EXISTS db_test_1;
/*创建数据库*/
SHOW DATABASES LIKE 'db_%';
/*显示匹配该文本的数据库名称*/
SHOW SCHEMAS;
/*显示数据库名称*/
USE db_test;
/*进入数据库*/
ALTER DATABASE db_test DEFAULT CHARACTER 
SET gbk DEFAULT COLLATE gbk_chinese_ci;
/*修改数据库默认编码,数据库校对规则*/
DROP DATABASE
IF
	EXISTS db_test;
/*如果存在则删除数据库*/
DROP SCHEMA
IF
	EXISTS db_test_1;
/*如果存在则删除数据库*/

存储引擎及数据类型

SHOW VARIABLES;
/*显示数据库信息*/
SHOW ENGINES;
/*显示存储引擎信息*/
数字类型
类型 属性
FLOAT 单精度浮点数
DOUBLE 双精度浮点数
DEC 自定义长度浮点数
INT 整数
日期和时间数据类型
类型 属性
DATE 日期，格式YYYY-MM-DD
TIME 时间，格式HH：MM：SS
DATETIME 日期和时间，格式YYYY-MM-DD HH：MM：SS
TIMESTAMP 时间戳
YEAR 年份可指定两位数字和四位数字的格式
字符串类型
类型 属性
CHAR 固定长度
VARCHAR 长度可变
SET 集合
ENUM 枚举
TEXT 长文本
BLOB 二进制长文本

操作数据表

DROP TABLE
IF
	EXISTS tb_test,
	tb_test_1;
/*如果存在该数据表则删除数据表*/
CREATE TABLE
IF
	NOT EXISTS tb_test (
		id INT auto_increment PRIMARY KEY COMMENT 'COMMENT编写注释',
		users VARCHAR ( 30 ) NOT NULL,
		email VARCHAR ( 30 ) NOT NULL,
		text VARCHAR ( 30 ) DEFAULT 'DEFAULT设置默认值',
	time datetime 
	) ENGINE = INNODB AUTO_INCREMENT = 0 CHARSET = utf8;
/*如果不存在该数据表则创建数据表，设置字段名称，字段属性,ENGINE设置数据库引擎,AUTO_INCREMENT设置自增从多少开始,CHARSET设置默认字符集写成DEFAULT CHARSET也可以*/
SHOW CREATE TABLE tb_test;
/*SHOW CREATE TABLE返回创建表时的sql语句*/
CREATE TABLE tb_test_1 AS SELECT
* 
FROM
	tb_test;
/*基于数据表属性构建新数据表*/
ALTER TABLE db_test.tb_test 
CHANGE COLUMN text text_1 VARCHAR ( 40 ) NULL DEFAULT NULL,
ALTER COLUMN users 
SET DEFAULT "test",
ADD text_2 VARCHAR ( 50 ),
MODIFY time YEAR,
DROP email;
/*修改数据表字段，字段属性，增删字段*/
SHOW COLUMNS 
FROM
	tb_test 
FROM
	db_test;
/*查询数据表字段属性*/
DESC tb_test_1;
/*查询数据表字段属性*/
DESC tb_test id;
/*查询数据表指定字段属性*/

运算符与流程控制语句

运算符

  • 算术运算符
    |+|-|*|\|MOD
    |-|-|-|-|-
    |加|减|乘|除|求余

  • 比较运算符

    运算符 名称 示例 ------ 运算符 名称 示例
    = 等于 Id=5 Is not null n/a Id is not null
    > 大于 Id>5 Between n/a Id between1 and 15
    < 小于 Id<5 In n/a Id in (3,4,5)
    => 大于等于 Id=>5 Not in n/a Name not in (shi,li)
    <= 小于等于 Id<=5 Like 模式匹配 Name like (‘shi%’)
    !=或<> 不等于 Id!=5 Not like 模式匹配 Name not like (‘shi%’)
    Is null n/a Id is null Regexp 常规表达式 Name正则表达式

  • 逻辑运算符

    符号 作用
    &&或AND 与
    ||或OR 或
    !或NOT 非
    XOR 异或

  • 位运算符

    符号 作用
    & 按位与。进行该运算时，数据库系统会先将十进制的数转换为二进制的数。然后对应操作数的每个二进制位上进行与运算。1和1相与得1，与0相与得0。运算完成后再将二进制数变回十进制数
    | 按位或。将操作数化为二进制数后，每位都进行或运算。1和任何数进行或运算的结果都是1，0与0或运算结果为0
    ~ 按位取反。将操作数化为二进制数后，每位都进行取反运算。1取反后变成0，0取反后变成1
    ^ 按位异或。将操作数化为二进制数后，每位都进行异或运算。相同的数异或之后结果是0，不同的数异或之后结果为1
    << 按位左移。“m<<n”表示m的二进制数向左移n位，右边补上n个0。例如，二进制数001左移1位后将变成0010
    >> 按位右移。“m>>n”表示m的二进制数向右移n位，左边补上n个0。例如，二进制数011右移1位后变成001，最后一个1直接被移出

  • 运算符优先级

    1. !
    2. ~
    3. ^
    4. *,/,DIV,%,MOD
    5. +,-
    6. >>,<<
    7. &
    8. |
    9. =,<=>,<,<=,>,>=,!=,<>,IN,IS,NULL,LIKE,REGEXP
    10. BETWEEN AND,CASE,WHEN,THEN,ELSE
    11. NOT
    12. &&,AND
    13. ||,OR,XOR
    14. :=
流程控制语句

IF语句


delimiter //
CREATE PROCEDURE test_if ( IN x INT ) BEGIN
	IF
		x = 1 THEN
		SELECT
			1;
		
		ELSEIF x = 2 THEN
		SELECT
			2;
		ELSE SELECT
			3;
		
	END IF;

END // CALL test_if ( 1 );

CASE语句


delimiter //
CREATE PROCEDURE test_case ( IN x INT ) BEGIN
	CASE
			x 
			WHEN 1 THEN
		SELECT
			1;
		
		WHEN 2 THEN
		SELECT
			2;
		ELSE SELECT
			3;
		
	END CASE;

END // CALL test_case ( 2 );

WHILE语句


delimiter //
CREATE PROCEDURE test_while ( OUT sum INT ) BEGIN
	DECLARE
		i INT DEFAULT 1;
	DECLARE
		s INT DEFAULT 0;
	WHILE
			i <= 100 DO
			
			SET s = s + i;
		
		SET i = i + 1;
		
	END WHILE;
	
	SET sum = s;
	
END // CALL test_while ( @s );
SELECT
	@s;

LOOP语句


delimiter //
CREATE PROCEDURE test_loop ( OUT sum INT ) BEGIN
	DECLARE
		i INT DEFAULT 1;
	DECLARE
		s INT DEFAULT 0;
	loop_label :
	LOOP
			
			SET s = s + i;
		
		SET i = i + 1;
		IF
			i > 100 THEN
				LEAVE loop_label;
			
		END IF;
		
	END LOOP;
	
	SET sum = s;
	
END // CALL test_loop ( @s );
SELECT
	@s;

REPEAT语句


delimiter //
CREATE PROCEDURE test_repeat ( OUT sum INT ) BEGIN
	DECLARE
		i INT DEFAULT 1;
	DECLARE
		s INT DEFAULT 0;
	REPEAT
			
			SET s = s + i;
		
		SET i = i + 1;
		UNTIL i > 100 
	END REPEAT;
	
	SET sum = s;
	
END // CALL test_repeat ( @s );
SELECT
	@s;

表数据的增删改操作

基于操作数据表的代码后

TRUNCATE TABLE db_test.tb_test;
/*清空数据表内容*/
DELETE 
FROM
	tb_test_1;
/*清空数据表内容*/
INSERT INTO tb_test
VALUES
	( 1, 'a', 'aa', NOW( ), 'aaa' );
/*插入数据表内容*/
INSERT INTO tb_test ( users, text_1, text_2 )
VALUES
	( 'b', 'bb', 'bbb' ),( 'd', 'dd', 'ddd' );
/*插入数据表内容*/
INSERT INTO tb_test 
SET users='c',text_1='cc',text_2= 'aaa';
/*插入数据表内容*/
INSERT INTO db_test.tb_test_1 ( id, users, email, text ) SELECT
id,
users,
text_1,
text_2 
FROM
	tb_test;
/*向数据表插入另一个数据表的内容*/
DELETE 
FROM
	tb_test 
WHERE
	text_2 = 'aaa';
/*删除数据表指定内容*/
UPDATE db_test.tb_test 
SET text_2 = '123' 
WHERE
	text_1 = 'bb';
/*修改数据表内容*/
SELECT
	* 
FROM
	tb_test,
	tb_test_1;

数据查询

SELECT
	id,
	users,
	SUM( id ) AS SUM,
	AVG( id ),
	MAX( id ),
	MIN( id ),
	GROUP_CONCAT( text ) 
FROM
	tb_test_1 
WHERE
	id BETWEEN 1 
	AND 3 
	AND text NOT LIKE 'b%' 
	AND time IS NULL 
	AND id < SOME ( SELECT id FROM tb_test_1 ) 
	AND id < ANY ( SELECT id FROM tb_test_1 ) 
	AND id < ALL ( SELECT id FROM tb_test_1 ) 
	OR id IN ( 1, 4, 5 ) 
	OR text REGEXP '[a-d]' 
GROUP BY
	text;
/*常用查询数据聚合函数和筛选条件和分组*/
SELECT
	* 
FROM
	tb_test 
ORDER BY
	id DESC;
/*ORDER BY排序，DESC降序*/
SELECT
	* 
FROM
	tb_test 
ORDER BY
	id ASC 
	LIMIT 1,
	2;
/*LIMIT后跟1个数字输出前几行，2个数字输出从第m个数字开始后面n行*/
SELECT
	* 
FROM
	tb_test t,
	tb_test_1 t1 
WHERE
	t1.id = t.id 
	AND EXISTS ( SELECT * FROM tb_test );
/*两个数据表拼接，EXISTS判断是否有数据，返回真或假*/
SELECT
	* 
FROM
	tb_test
	JOIN tb_test_1 ON tb_test_1.id = tb_test.id;
/*两个数据表链接*/

常用函数

SELECT
	ABS( 5 ),
	ABS( - 5 );
/*ABS返回绝对值*/
SELECT
	FLOOR( 1.4 ),
	FLOOR( 1.5 ),
	FLOOR( - 2.4 ),
	FLOOR( - 2.5 );
/*FORMAT四舍五入，必须定保留几位小数,ROUND四舍五入，可指定保留几位小数，FLOOR向下取整，CEILING向上取整*/
SELECT
	RAND( ),
	RAND( );
/*RAND( )返回一个0-1之间的随机小数*/
SELECT
	ROUND( RAND( ) * 10 ),
	FLOOR( RAND( ) * 10 ),
	CEILING( RAND( ) * 10 );
/*组合函数获得随机整数*/
SELECT
	PI( );
/*PI( )返回圆周率*/
SELECT TRUNCATE
	( 2.124, 2 ),
	TRUNCATE ( 2.125, 2 ),
	TRUNCATE ( - 2.1234, 3 ),
	TRUNCATE ( - 2.1235, 3 );
/*TRUNCATE去尾保留指定位小数*/
SELECT
	SQRT( 16 ),
	SQRT( 25 );
/*SQRT返回开平方后的数*/
SELECT INSERT
	( 'abcde', 3, 2, 'fgh' ),
/*abfghe*/
	INSERT ( 'abcde', 2, 3, 'fgh' );
/*afghe*/
/*INSERT在第一个参数字符中，从第二个参数的位置开始数第三个参数个字符用第四个参数字符替代*/
SELECT
	UPPER( 'abc' ),
	UCASE( 'abc' );
/*UPPER,UCASE返回大写字母*/
SELECT LEFT
	( 'abcde', 2 );
/*LEFT返回左边指定个数个字符*/
SELECT
	CONCAT( 'a', 'b', RTRIM( '   c   ' ), 'd   ', 'e' );
/*CONCAT拼接字符串,RTRIM去除右边空格*/
select SUBSTRING('abcdefg',3,4);
/*SUBSTRING返回第一个参数字符中第二个参数开始第三个参数个字符*/
select REVERSE('abcd');
/*REVERSE返回反转的字符串*/
SELECT
	FIELD( 'a', 'a', 'b', 'a' ),
	FIELD( 'b', 'a', 'b', 'a' ),
	FIELD( 'c', 'a', 'b', 'a' );
/*FIELD从第二个参数为1开始检索后面的参数是否匹配第一个参数,匹配成功立刻返回对应数值,全部不匹配返回0*/
SELECT
	CURDATE( );
/*CURDATE( )返回现在日期*/
SELECT
	CURTIME( ),
	CURRENT_TIME ( );
/*CURTIME( ),CURRENT_TIME( )返回当前时间*/
SELECT
	NOW( ),
	CURRENT_TIMESTAMP ( ),
	LOCALTIME ( ),
	SYSDATE( );
/*NOW( ),CURRENT_TIMESTAMP ( ),LOCALTIME ( ),SYSDATE( ),返回当前日期时间*/
SELECT
	LOCATE( 'a', 'abcde' ),
	LOCATE( 'b', 'abcde' ),
	LOCATE( 'z', 'abcde' ),
	INSTR( 'abcde', 'a' ),
	INSTR( 'abcde', 'b' ),
	INSTR( 'abcde', 'z' );
/*LOCATE返回第一个参数在第二个参数的第几个,如果不在返回0，INSTR返回第二个参数在第一个参数的第几个,如果不在返回0*/
SELECT
	POSITION( 'a' IN 'abcde' ),
	POSITION( 'b' IN 'abcde' ),
	POSITION( 'z' IN 'abcde' );
/*LOCPOSITION( IN )返回第一个参数在第二个参数的第几个,如果不在返回0*/
SELECT
	DATEDIFF( '2020-01-05', '2020-01-01' );
/*DATEDIFF返回两个日期相差天数*/
SELECT
	ADDDATE( '2020-01-01', 3 );
/*ADDDATE返回第一个参数日期增加第二个参数的天数后的日期*/
SELECT
	ADDDATE( '2011-07-01', INTERVAL '1''3' YEAR_MONTH );
/*ADDDATE( INTERVAL YEAR_MONTH )返回第一个参数日期增加第二个参数的年和月后的日期*/
SELECT
	SUBDATE( '2020-01-01', 1 );
/*SUBDATE返回第一个参数日期减去第二个参数的天数后的日期*/
SELECT
	VERSION( ),
	CONNECTION_ID( );
/*VERSION( )返回数据库版本号,CONNECTION_ID( )返回当前客户端ID*/
SELECT DATABASE
	( ),
	SCHEMA ( );
/*DATABASE( ),SCHEMA ( )返回数据库名称*/
SELECT USER
	( ),
	SYSTEM_USER( ),
	SESSION_USER( ),
	CURRENT_USER ( ),
	CURRENT_USER;
/*USER( ),SYSTEM_USER( ),SESSION_USER( ),CURRENT_USER ( ),CURRENT_USER返回当前用户和相应权限*/
SELECT
	CHARSET( 'a' ),
	CHARSET( CONVERT ( 'a' USING gbk ) ),
	COLLATION ( 'a' );
/*CHARSET返回当前数据库字符集编码，CONVERT (  USING  )指定字符集编码，COLLATION返回字符集排序规则*/
SELECT PASSWORD
	( 'abcd' ),
	MD5( 'abcd' );
/*PASSWORD,MD5返回加密后密文*/
SELECT
	CAST( NOW( ) AS DATE ),
	CONVERT ( NOW( ), TIME );
/*CAST( NOW( ) AS DATE )把日期时间切割，返回日期，CONVERT ( NOW( ), TIME )把日期时间切割，返回时间*/


CREATE TABLE `tb_test_10` ( `id` INT ( 1 ) );
INSERT INTO tb_test_10
VALUES
	( 1 );
INSERT INTO tb_test_10
VALUES
	( 2 );
INSERT INTO tb_test_10
VALUES
	( 3 );
SELECT
	id,
CASE
		
		WHEN id > 2 THEN
		'c' 
		WHEN id < 3 AND id >= 2 THEN
		'b' ELSE 'a' 
END LEVEL 
FROM
	tb_test_10;
/*比较运算符的应用*/

结果

id level
1 a
2 b
3 c

索引与约束

设定值 说明
NOT NULL 非空约束
UNIQUE 唯一约束
FULLTEXT KEY 主键约束
FOREIGN KEY 外键约束
CHECK 检查(MySQL中没有实际意义,Oracle有相应作用)

索引用于加快搜索速度，可在创建表的时候同时创建，可以在表中创建修改删除

CREATE TABLE tb_test_11_01 ( id INT, username CHAR ( 32 ), INDEX ( id DESC ) );
/*INDEX创建单列索引*/
CREATE TABLE tb_test_11_02 ( id INT, username CHAR ( 32 ), INDEX info ( id, username ) );
/*INDEX创建多列索引*/
CREATE TABLE tb_test_11_03 ( id INT, username CHAR ( 32 ), UNIQUE INDEX info ( id ASC ), FULLTEXT KEY username ( username ) );
/*UNIQUE INDEX创建唯一性索引，ASC升序DESC降序排序, FULLTEXT KEY创建全文索引，全文索引只能在文本字段创建*/
CREATE TABLE tb_test_11_04 ( id INT ( 11 ), goods geometry NOT NULL, SPATIAL INDEX info ( goods ) ) ENGINE = MyISAM;
/*SPATIAL 创建空间索引，需设置ENGINE = MyISAM时，字段是空间类型非空的时候才能创建成功*/
DROP INDEX info ON tb_test_11_04;
/*删除索引*/
CREATE SPATIAL INDEX info_2 ON tb_test_11_04 ( goods );
/*在已创建的表上创建索引*/
ALTER TABLE tb_test_11_01 ADD INDEX info(id, username);
/*在已创建的表上增加索引*/

视图

CREATE TABLE tb_student (
	id INT NOT NULL AUTO_INCREMENT,
	sno VARCHAR ( 64 ) NOT NULL,
	sname VARCHAR ( 64 ) NOT NULL,
	PRIMARY KEY ( id ) 
) ENGINE = INNODB;
/*创建学生表*/
INSERT INTO tb_student
VALUES
	( 1, '0312315', '刘小华' );
INSERT INTO tb_student
VALUES
	( 2, '0312316', '金星星' );
INSERT INTO tb_student
VALUES
	( 3, '0312317', '黄小全' );
INSERT INTO tb_student
VALUES
	( 4, '0312318', '李小林' );
/*插入学生数据*/
CREATE TABLE tb_score (
	id INT NOT NULL AUTO_INCREMENT,
	sid INT DEFAULT NULL,
	yw INT NOT NULL,
	wy INT NOT NULL,
	sx INT NOT NULL,
	PRIMARY KEY ( id ),
	KEY info ( sid ),
	CONSTRAINT info FOREIGN KEY ( sid ) REFERENCES tb_student ( id ) ON DELETE CASCADE ON UPDATE 
	SET NULL 
	) ENGINE = INNODB;
/*创建成绩表和外键*/
INSERT INTO tb_score
VALUES
	( 1, 1, 88, 60, 94 );
INSERT INTO tb_score
VALUES
	( 2, 2, 60, 85, 76 );
INSERT INTO tb_score
VALUES
	( 3, 3, 56, 90, 75 );
INSERT INTO tb_score
VALUES
	( 4, 4, 76, 86, 78 );
/*插入成绩数据*/
CREATE 
	OR REPLACE ALGORITHM = TEMPTABLE VIEW student_score_view AS SELECT
	sno,
	sname,
	sid,
	yw,
	wy,
	sx 
FROM
	tb_student
	JOIN tb_score ON tb_score.sid = tb_student.id;
/*创建视图，视图名称后可跟括号里面重命名字段名，AS SELECT后跟SELECT查询语句
CREATE OR REPLACE 如果存在，会替换，不存在则照常创建
视图的ALGORITHM
ALGORITHM = MERGE/TEMPTABLE/UNDEFINED
MERGE:当使用视图时，会把查询视图的语句和创建视图的语句合并起来，形成一条语
句，最后再从基表中查询
TEMPTABLE:当使用视图时，会把创建视图的语句的查询结果当成一张临时表，再从临时表中进行筛选
UNDEFINED:未定义，自动，让系统帮你选*/
CREATE 
	OR REPLACE VIEW student_score_view2 AS SELECT
	sno,
	sname,
	sid,
	yw,
	wy,
	sx 
FROM
	tb_student
	JOIN tb_score ON tb_score.sid = tb_student.id WITH CHECK OPTION;
/*没有WITH CHECK OPTION语句的视图，可以插入符合数据库语法约束的数据，可是不会在视图里显示出来
有WITH CHECK OPTION语句的视图，它会阻止可见行更新为不可见的行，
1.对于update,有with check option，要保证update后，数据要被视图查询出来
2.对于delete,有无with check option都一样
3.对于insert,有with check option，和update相同 要保证insert后，数据要被视图查询出来
4.对于没有where 子句的视图，也就没有视图自己的约束，因此使用with check option是多余的*/
ALTER VIEW student_score_view2 ( sid ) AS SELECT
sno 
FROM
	tb_student WITH CHECK OPTION;
/*修改视图，需存在WITH CHECK OPTION语句*/
UPDATE student_score_view4 SET sname='张小荣' WHERE sid=1;
/*修改视图*/
DROP VIEW IF EXISTS student_score_view2;
/*DROP VIEW删除视图，IF EXISTS存在则操作，否则不操作*/
SHOW TABLE STATUS;
/*查询视图和表的详细属性，后面可跟LIKE筛选名称*/
DESCRIBE student_score_view;
DESC student_score_view;
/*查看字段属性*/
SHOW CREATE VIEW student_score_view;
/*查看视图创建sql语句和字符集，字符集排序规则*/

数据完整性约束

从表的列引用了主表的主键，从表的该列为外键

插入规则：从表插入了一列，该列的外键必须是主表主键的值

更新规则：可选择设定下面规则

删除规则：可选择设定下面规则

外键约束操作

设定值 说明
CASCADE 级联，从父表删除或更新会自动删除或更新子表中匹配的行
SET NULL 从父表删除或更新行，会设置子表中的外键列为NULL，但必须保证子表列没有指定NOT NULL，也就是说子表的字段为DEFAULT NULL才行
RESTRICT/NO ACTION 如果从父表删除主键，如果子表引用了，则拒绝对父表的删除或更新操作
ENGINE=MyISAM or ENGINE=InnoDB
MyISAM强调性能，不提供事务支持以及外键等高级数据库功能
InnoDB则牺牲性能提供外键等高级数据库功能 
ALTER TABLE tb_score DROP FOREIGN KEY info;
/* DROP FOREIGN KEY [外键名]删除外键*/
ALTER TABLE tb_score ADD CONSTRAINT info FOREIGN KEY ( sid ) REFERENCES tb_student ( id ) ON DELETE CASCADE ON UPDATE 
SET NULL;
/* ADD CONSTRAINT [外键名] FOREIGN KEY在创建好的数据表上增加外键*/

存储过程与存储函数

数据库放置逻辑运算代码的地方,不推荐使用,建议数据库只当数据仓库使用,逻辑代码在程序代码里实现

CREATE PROCEDURE p1 ( OUT count_num INT ) READS SQL DATA BEGIN
	SELECT
		count( * ) INTO count_num 
	FROM
		tb_student;

END
CREATE PROCEDURE p2 ( INOUT count_num INT ) READS SQL DATA BEGIN
	SELECT
		sno INTO count_num 
	FROM
		tb_student 
	WHERE
		id = count_num;

END
/*创建存储过程
OUT返回变量INOUT接收并返回变量，用@参数传参,INTO在存储过程获得返回参数*/
CALL p1 ( @x );
SELECT
	@x;
SET @y = 1;
CALL p2 ( @y );
SELECT
	@y;
/*CALL调用存储过程*/
CREATE PROCEDURE p3 ( ) BEGIN
	
	SET @t = 1;
	BEGIN
			
			SET @t = 2;
		SELECT
			@t;
		
	END;
	SELECT
	@t;
END;
/*无参存储过程*/
CALL p3 ( );
/*调用无参存储过程*/
CREATE FUNCTION t1 ( std_id INT ) RETURNS VARCHAR ( 50 ) BEGIN
	RETURN ( SELECT sname FROM tb_student WHERE id = std_id );

END
/*创建存储函数*/
SELECT
	t1 ( 1 );
/*调用存储函数*/
CREATE PROCEDURE p3 ( ) BEGIN
	DECLARE
		x CHAR ( 10 ) DEFAULT 'outer ';
	BEGIN
		DECLARE
			x CHAR ( 10 ) DEFAULT 'inner ';
		SELECT
			x;
		
	END;
	SELECT
	x;
END;
/*存储过程变量的作用域*/
DROP PROCEDURE p3;
/*删除存储过程*/
SHOW CREATE PROCEDURE p1;
/*查看存储过程创建信息*/
SHOW CREATE FUNCTION t1;
/*查看存储函数创建信息*/
ALTER FUNCTION t1 MODIFIES SQL DATA SQL SECURITY INVOKER;
/*修改存储函数读写声明和执行权限*/
DROP FUNCTION t1;
/*删除存储函数*/
SHOW PROCEDURE STATUS ;
/*查看所有存储过程*/
SHOW FUNCTION STATUS ;
/*查看所有存储函数*/
SELECT * FROM information_schema.Routines;
/*查看所有存储过程，存储函数信息*/
ALTER PROCEDURE p1 MODIFIES SQL DATA SQL SECURITY INVOKER;
/*修改存储过程读写声明和执行权限*/
参数 说明
CONTAINS SQL 表示子程序包含SQL语句，但不包含读写数据的语句
NO SQL 表示子程序不包含SQL语句
READS SQL UPDATE 表示子程序包含读数据的语句
MODIFIES SQL DATA 表示子程序包含写数据的语句
-
SQL SECURITY INVOKER 表示调用者可执行
SQL SECURITY DEFINER 表示只有创建者可执行

触发器

CREATE TABLE timelog ( savetime VARCHAR ( 64 ) NOT NULL, info VARCHAR ( 64 ) NOT NULL );
/*创建触发器日志表
AFTER为操作后，BEFORE为操作前，支持INSERT，DELETE，UPDATE的触发，BEGIN...END可以支持多句SQL语句，触发器不支持return语句
*/
CREATE TRIGGER t1 AFTER INSERT ON tb_student FOR EACH ROW
INSERT INTO timelog ( savetime, info )
VALUES
	( now( ), 'AFTER INSERT' );
/*创建插入后触发器*/
CREATE TRIGGER t2 BEFORE INSERT ON tb_student FOR EACH ROW
INSERT INTO timelog ( savetime, info )
VALUES
	( now( ), 'BEFORE INSERT' );
/*创建插入前触发器*/
CREATE TRIGGER t3 AFTER DELETE ON tb_student FOR EACH ROW
BEGIN
		INSERT INTO timelog ( savetime, info )
	VALUES
		( now( ), 'AFTER DELETE' );

END
/*创建删除触发器*/
CREATE TRIGGER t4 AFTER UPDATE ON tb_student FOR EACH ROW
BEGIN
		INSERT INTO timelog ( savetime, info )
	VALUES
		( now( ), 'AFTER UPDATE' );

END
/*创建修改触发器*/
INSERT INTO tb_student ( sno, sname )
VALUES
	( '0312319', '张小友' );
SELECT
	* 
FROM
	timelog;
UPDATE tb_student 
SET sno = '0312320' 
WHERE
	sno = '0312319';
DELETE 
FROM
	tb_student 
WHERE
	id = 5;
SELECT
	* 
FROM
	timelog;
/*操作数据表触发触发器*/
DROP TRIGGER t1;
/*删除触发器*/
SHOW CREATE TRIGGER t1;
/*查询触发器创建信息*/
SHOW TRIGGERS;
/*查询所有触发器*/

事务的应用

START TRANSACTION;
/*开始事务*/
COMMIT;
/*提交事务*/
ROLLBACK;
/*事务回滚*/
LOCK TABLES tb_student WRITE;
/*只写锁表，不能读*/
LOCK TABLES tb_student READ;
/*只读锁表，不能写*/
UNLOCK TABLE;
/*解锁*/
SET autocommit = 0;
/*禁止自动提交*/
SET autocommit = 1;
/*开启自动提交*/
SELECT
	@@transaction_isolation;
/*查看当前会话的默认事务隔离级别，8.0前的使用@@tx_isolation*/
SELECT
	@@SESSION.transaction_isolation;
/*查看当前系统的事务隔离级别，8.0前的使用@@SESSION.tx_isolation*/
SELECT
	@@GLOBAL.transaction_isolation;
/*查看全局的事务隔离级别，8.0前的使用@@GLOBAL.tx_isolation*/	
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/*设置全局的事务隔离级别*/
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/*设置当前系统的事务隔离级别*/

脏读：一个事务读取了另一个未提交的并行事务写的数据。

不可重复读：一个事务重新读取前面读取过的数据，发现该数据已经被另一个已提交的事务修改过。

幻读：一个事务重新执行一个查询，返回一套符合查询条件的行，发现这些行因为其他最近提交的事务而发生了改变。

事务隔离级别 说明
READ UNCOMMITTED 幻读，不可重复读和脏读均允许；
READ COMMITTED 允许幻读和不可重复读，但不允许脏读；
REPEATABLE READ 允许幻读，但不允许不可重复读和脏读；
SERIALIZABLE 幻读，不可重复读和脏读都不允许；
ORACLE默认的是 READ COMMITTED。

MYSQL默认的是 REPEATABLE READ。

事务隔离级别与性能成反比

事件

SHOW VARIABLES LIKE 'event_scheduler';
/*查看事件调度器是否开启*/
SELECT @@event_scheduler;
/*查看事件调度器是否开启*/
SHOW PROCESSLIST;
/*显示用户正在运行的线程状态信息*/
SET GLOBAL event_scheduler = ON;

SET @@GLOBAL.event_scheduler = ON;

SET GLOBAL event_scheduler = 1;

SET @@GLOBAL.event_scheduler = 1;
/*开启全局事件调度器*/
SET GLOBAL event_scheduler = OFF;

SET @@GLOBAL.event_scheduler = OFF;

SET GLOBAL event_scheduler = 0;

SET @@GLOBAL.event_scheduler = 0;
/*开启全局事件调度器*/
CREATE EVENT
IF
	NOT EXISTS e1 ON SCHEDULE EVERY '2' MINUTE STARTS '2020-01-01 00:00:00' ENDS '2021-01-01 00:00:00' ON COMPLETION PRESERVE DISABLE COMMENT '这是注释' DO
	INSERT INTO timelog ( savetime, info )
VALUES
	( now( ), 'root' );
/*CREATE EVENT创建事件，EVERY为间隔多少时间执行一次，AT为指定时间，STARTS为开始时间，ENDS为结束时间，ON COMPLETION PRESERVE为结束后状态改为DISABLE ON SLAVE,不设置则删除事件，DISABLE，默认为ENABLE生效，事件结束后系统自动改为DISABLE ON SLAVE，DISABLE*/
ALTER EVENT e1 ENABLE;
/*修改事件*/
DROP EVENT IF EXISTS e1;
/*删除事件*/

备份与恢复

mysql -u root -p --html -e "select * from tb_student" db_test >D:/tb_student.html  # 导出数据表为html格式
mysql -u root -p -e"SELECT * FROM tb_student" db_test > D:/tb_student.txt  # 导出数据表为txt格式
mysqldump -u root -p --xml db_test tb_student >D:/tb_student.xml  # 导出数据表为xml格式
mysqldump -u root -p db_test tb_student >D:\tb_student.sql  # 备份数据表
mysqldump -u root -p -T D:\ db_test timelog "--lines-terminated-by=\r\n" "--fields-terminated-by=、" "--fields-optionally-enclosed-by=""  # 同时导出sql备份和txt文件
mysqldump -u root -p --all-databases  >D:\all.sql  # 备份全部Mysql数据库
mysql -u root -p <D:/tb_student.sql  # 恢复数据表的数据，注意要在备份的sql文件里加入USE `databasename`;
mysql -u root -p --default-character-set=utf8 <D:\all.sql  # 恢复数据库,要注意编码集
mysqlimport -u root -p db_test D:\timelog.txt "--lines-terminated-by=\r\n" "--fields-terminated-by=、" "--fields-optionally-enclosed-by=\""  # 接着原数据表内容后面导入数据，--lines-terminated-by设置换行符，--fields-terminated-by设置换格符,--fields-optionally-enclosed-by设置字符包围符

SHOW GLOBAL VARIABLES LIKE '%secure%';
/*查看导入设置*/
SELECT
	* 
FROM
	tb_score INTO OUTFILE "D:/tb_score.txt" FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\"' LINES STARTING BY '1' TERMINATED BY '\r\n';
/*导出数据表为txt格式TERMINATED BY,设置换格符，ENCLOSED BY设置字符包为符，STARTING BY设置每行开始符，TERMINATED BY设置每行结束符*/
LOAD DATA INFILE 'D:/tb_score.txt' INTO TABLE tb_score FIELDS TERMINATED BY '\,' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
/*接着原数据表内容后面导入数据*/

导入导出权限不足的可以找到my.ini文件进行导入设置,增加一行secure_file_priv=''后，重启数据库服务即可

Mysql性能优化

SHOW VARIABLES LIKE '%query_cache%';
/*查询高速缓存配置信息*/
SHOW STATUS LIKE 'Connections';
/*目前连接数*/
SHOW STATUS LIKE 'Com_select';
/*本次MySQL启动后执行的SELECT语句的次数*/
SHOW STATUS LIKE 'Slow_queries';
/*查看查询时间超过long_query_time秒的查询的个数*/
EXPLAIN SELECT
	* 
FROM
	tb_student;
/*EXPLAIN等同于DESC(DESCRIBE),查询数据表信息 */
ANALYZE TABLE tb_student;
/*设置优化元件,优化索引，提高查询速度，使用LIKE搜索，第一个字符使用%索引会失效，使用and时只有第一个索引有效，使用or时，条件均有索引，索引才有效*/
OPTIMIZE TABLE tb_student;
/*设置优化元件，适用于数据表经过大量删除后空间碎片整理*/
SELECT SQL_CACHE
	* 
FROM
	timelog;
/*使用缓存查询，Mysql8.0已取消该功能*/
  • 索引优化规则
    • 1.使用最左前缀规则
    • 2.模糊查询不能利用索引(like '%XX'或者like '%XX%')
    • 3.不要过多创建索引
    • 4.索引长度尽量短
    • 5.索引更新不能频繁
    • 6.索引列不能参与计算
  • 查询时的优化
    • 小表驱动大表
    • 避免全表扫描
    • 避免MySQL放弃索引查询
    • 使用覆盖索引，少使用SELECT * FROM
    • ORDER BY的索引生效
    • 不正确的使用导致索引失效
    • FOR UPDATE锁表
  • 其他优化
    • 开启慢查询
    • 实时获取有性能问题的SQL
    • 垂直分割
    • 拆分执行时间长的DELETE或INSERT语句

权限管理及安全控制

SELECT
	* 
FROM
	mysql.USER;
/*查询用户信息*/
CREATE USER 'test'@'%' IDENTIFIED BY '123';
/*创建新用户,设置用户组和密码，%代表任何客户机都可以连接，
localhost代表只可以本机连接*/
RENAME USER test TO test2;
/*用户名改名*/
ALTER USER 'test2' @'%' IDENTIFIED BY '321';
/*修改用户和密码*/
SET PASSWORD = '321';
/*修改当前用户密码*/
DROP USER test;
/*删除用户*/
GRANT ALL PRIVILEGES ON *.* TO 'test' @'%';
/*修改用户权限*.*代表全部权限*/

对mysql.user表格进行修改同样有效

posted @ 2020-09-01 21:11  _Biko  阅读(260)  评论(0)  编辑  收藏  举报