【详细版本】MySQL数据库部分语法，很容易就对应上

INSERT语句

语法

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ] VALUES (值1) [… , (值n) ];

INSERT INTO <表名> SET <列名1> = <值1>,  <列名2> = <值2>, …

UPDATE 语句

语法

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

DELETE

语法

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

清空表记录

语法

TRUNCATE [TABLE] 表名

TRUNCATE 和 DELETE 的区别

从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，但是在某些情况下，两者在使用上有所区别。

• DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
• DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
• DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
• DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
• DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
• DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。

总结

当不需要该表时，用 DROP；

当要删除部分记录时，用 DELETE。

当仍要保留该表，但要删除所有记录时，用 TRUNCATE；

过滤重复数据

语法

SELECT DISTINCT <字段名> FROM <表名>;

正则表达式查询

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头的字符串	book、big、banana、bike
$	匹配文本的结束字符	‘st$’ 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	‘b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配零个或多个在它前面的字符	‘f*n’ 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
<字符串>	匹配包含指定字符的文本	‘fa’ 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]’ 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串{n,}	匹配前面的字符串至少 n 次	‘b{2}’ 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串 {n,m}	匹配前面的字符串至少 n 次， 至多 m 次	‘b{2,4}’ 匹配最少 2 个，最多 4 个 b	bbb、bbbb

语法

属性名 REGEXP '匹配方式'
SELECT * FROM tb_students_info  WHERE 
name REGEXP '^J';

连接

内连接

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
SELECT s.name,c.course_name FROM tb_students_info s 
INNER JOIN    tb_course c  
ON            s.course_id = c.id;

左连接

SELECT  <字段名> FROM <表1> 
LEFT JOIN <表2> 
ON <表1> .column_name=<表2> .column_name

右连接

SELECT  <字段名> FROM <表1> 
RIGHT JOIN  <表2> 
ON <表1> .column_name= <表2> .column_name

全连接

SELECT  <字段名> FROM <表1> 
FULL JOIN <表2>  
ON <表1> .column_name= <表2> e2.column_name

主键

删除主键

语法： alter table 表名 drop PRIMARY KEY  主键约束名
 
举例： ALTER TABLE `hr` DROP PRIMARY KEY `PK_HR`

添加主键

语法： alter  表名 add  constraint  主键约束名 primary key(列名)

举例： alter table `hr` add constraint `PK_HR` primary key (hrno)

外键

删除外键

语法：alter table 表名 drop FOREIGN KEY 外键约束名称

举列：ALTER TABLE `hr` DROP FOREIGN KEY `fk_hr_deptno`

添加外键

语法：alter table 表名 add constraint 外键约束名 foreign key(列名) references 引用外键表(列名)

举列：ALTER TABLE `hr`ADD CONSTRAINT `fk_hr_deptno` freign key(deptno) references dept(deptno)

举列：在sc表的课号列建立外键约束fk_cno，参照course表的课号列的取值，要求实现级联更新。

alter table sc add constraint `fk_cno` fregin key(con) references course(cno) on upadte cascades;

视图

语法

CREATE VIEW <视图名> AS <SELECT语句>

完整性

在表创建完成后，定义实体完整性

alter table 表名 add constraint 主键名称 primary key (字段)

alter table 表1 add constraint 外键名称 FOREIGN KEY(字段) REFRENCES 表2(字段);

UNIQUE 约束

1.创建表的的时候指定
    create table stu(
        id int not null primary key,
        idCard varchar(18) not null unique, 
         ……
    )
2.创建表后单独添加
	alter table stu add constaint uq_name unique(name)

CHECK约束

添加

1.创建表的时候添加
	create table course(
    	cno int not null primary key,
        record int check(record between 1 and 100),
        ……
    )
2.创建表后单独添加
	alert table course add constraint che_recode check(record between 1 and 100);

删除

DROP CHECK che_recode

用户

创建用户

方法一：
CREATE USER <用户> [ IDENTIFIED BY [ PASSWORD ] '密码' ] [ ,用户 [ IDENTIFIED BY [ PASSWORD ] '密码' ]]
CREATE USER 'caiyuan'@'localhost' IDENTIFIED BY '123456';
方法二：
GRANT 权限 ON 新用户的权限范围 TO 用户名和主机名构成 [IDENTIFIED BY [PASSWORD] '密码']
GRANT SELECT ON*.* TO 'caiyuan'@'localhost' IDENTIFIED BY '123456';
“*.*” 表示所有数据库下的所有表

删除用户

DROP USER <用户1> [ , <用户2> ]…
DROP USER 'caiyuan'@'localhost';

授权

用户授权

GRANT 权限类型 [(column_list)] ON 权限的级别
TO 用户名和主机名构成 [IDENTIFIED BY [PASSWORD] 'password'][, user[IDENTIFIED BY [PASSWORD] '密码']] ...
[WITH GRANT OPTION]
WITH GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户

GRANT SELECT,INSERT ON *.*
TO 'caiyuan'@'localhost' IDENTIFIED BY '123456'
WITH GRANT OPTION;

删除授权

REVOKE 权限的类型 [(column_list)]...
ON 权限的级别
FROM 用户名和主机名构成 [, user]...

REVOKE INSERT ON *.* FROM 'caiyuan'@'localhost';

角色

创建角色

-- 角色名和帐户名相同，也是名字+host，如果没有写host，默认为'%'
CREATE ROLE '角色1', '角色2', '角色3';   --创建了3个角色

CREATE ROLE 'admin'@'localhost', 'manager'@'localhost', 'personnel'@'localhost'; 

删除角色

DROP USER  'manager'@'localhost';

授权角色

GRANT <权限>[,<权限>]... ON <对象类型>对象名 TO <角色>[,<角色>]...

GRANT ALL ON yhr.* TO 'admin'@'localhost';  		-- 给yhr数据库中所有表的所有权限
GRANT SELECT ON yhr.* TO 'personnel'@'localhost';   -- yhr数据库中所有表的查询权限
GRANT INSERT, UPDATE, DELETE ON yhr.* TO 'manager'@'localhost';   --yhr数据库中所有表的修改权限

收回角色权限

REVOKE <权限>[,<权限>]... ON <对象类型>对象名 FROM <角色>[,<角色>]...

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM  'manager'@'localhost';

存储过程

创建存储过程

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。

通常使用 DELIMITER命令将结束命令修改为其他字符。语法格式如下：

DELIMITER $$

语法说明如下：

• $$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
• 当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。

在 MySQL 命令行客户端输入如下 SQL 语句。

mysql > DELIMITER ??

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。

若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可：

mysql > DELIMITER ;

注意：DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时，必须具有 CREATE ROUTINE 权限。

ok，进入正题

举例子

这样理解得快，可能有点乱，但是思路还是蛮清晰的，希望你能对应的上

• 创建名称为 ShowHr 的存储过程，存储过程的作查询所有用户，输入的 mysql 语句和执行过程如下所示。

DELIMITER $$
CREATE PROCEDURE ShowHr()
BEGIN
SELECT * FROM hr;
END $$

结果显示 ShowHr存储过程已经创建成功。

• MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。语法格式如下：

DROP PROCEDURE [ IF EXISTS ] <过程名>

DROP PROCEDURE IF EXISTS  ShowHr

• 创建名称为 GetHrNameByRole的存储过程，输入参数是角色名称。存储过程的作用是通过输入的角色名称从用户表查询出对应的用户，输入的 SQL 语句和执行过程如下所示。

DELIMITER $$
CREATE PROCEDURE GetHrNameByRole(IN name VARCHAR(64))
 BEGIN
		SELECT hr.`name` FROM hr,hr_role,role
		WHERE role.`name`=name AND role.id = hr_role.rid AND hr.id = hr_role.hrid;
 END $$

• 使用stu_course数据库中的student表、sc表。
  • 创建存储过程stu_name，当任意输入一个学生的姓名时，查看其所选课程的最高分、最低分、平均分。
  • 调用存储过程stu_name。
  • 删除存储过程stu_name。

delimiter $$
create procedure stu_name(in cin_name varchar(64))
begin
	select student.sname, max(grade),min(grade),avg(grade) from student,sc
	where student.sname=cin_name and sc.sno = student.sno;
end $$
delimiter ; 

调用

call stu_name("小源同学");

删除

drop stu_name;

函数

函数和储存过程几乎一样

创建

    CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])

    RETURNS {STRING|INTEGER|REAL}

    runtime_body
    
简单说就是：
    CREATE FUNCTION 函数名称(参数列表)

    RETURNS 返回值类型

    函数体

举例

• 定义一个函数，输出hello。（提示：使用select显示输出）

create function say_hello() RETURNS VARCHAR(64);
RETURN "hello"; 

select say_hello(); 

drop FUNCTION say_hello;

• 定义一个函数，使用While语句求1到100之间的累加和，并输出结果。（提示：使用select显示输出）

方法一：
while循环语法：
   while 条件 DO
               循环体;
   end while;
-------------------------------------------
DELIMITER $$
CREATE FUNCTION add_num () RETURNS INTEGER BEGIN
	DECLARE
		i INT DEFAULT 1;
	DECLARE
		count INT DEFAULT 0;
	WHILE
			i <= 100 DO
			
			SET count = count + i;
		
		  SET i = i + 1;
		
	END WHILE;-- 循环结束 
	return count;
END $$ 
DELIMITER ; 
 
 -- 执行存储过程[call的话执行函数会出错,一般都是执行`储存过程`，select没有出错]
select add_num();
 -- 删除存储过程
drop procedure if exists add_num; 

方法二： 
loop 循环语法：
  loop_name:loop
          if 条件 THEN -- 满足条件时离开循环
               leave loop_name;  -- 和 break 差不多都是结束训话
       end if；
  end loop; 
------------------------------------------- 
DELIMITER $$
create FUNCTION add_nums() RETURNS INTEGER BEGIN DECLARE i INT DEFAULT 1; DECLARE count INT DEFAULT 0; 

	loop_name : LOOP 
				IF i > 100 THEN
					LEAVE loop_name;-- 判断条件成立则结束循环  好比java中的 boeak
				END IF;
			
				SET count = count + i;
		
				SET i = i + 1;
			
			END LOOP;
			
	RETURN count;-- 函数写return,储存过程写select count
	
END $$
DELIMITER ;

select add_nums();

drop procedure if exists add_nums; 

结果示例：

调用

call/select function_name(parameter_value,...)

修改

alter function 重命名一个函数
alter function 过程名1 rename to 过程名2;

alter function 重新编译一个函数
alter function 函数名 compile;

删除

DROP FUNCTION function_name

触发器

MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。

• INSERT 触发器

  • 在 INSERT 语句执行之前或之后响应的触发器。

  • 使用 INSERT 触发器需要注意以下几点：
    	在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
    	在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
    	对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。
    

• UPDATE 触发器

  • 在 UPDATE 语句执行之前或之后响应的触发器。注意：当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许。

  • 使用 UPDATE 触发器需要注意以下几点：
    	在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
    	在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
    	在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
    	OLD 中的值全部是只读的，不能被更新。
    

• DELETE 触发器

  • 在 DELETE 语句执行之前或之后响应的触发器。

  • 使用 DELETE 触发器需要注意以下几点：
    	在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
    	OLD 中的值全部是只读的，不能被更新。
    

查询已经有的触发器

SHOW TRIGGERS 

创建触发器

CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE > ON <表名> 
FOR EACH ROW
<触发器主体>

举例：在course表上创建触发器del_trig，当course表上删除一门课程时，级联删除sc表该课程的记录。
删除course表的一条记录，查看sc表相应记录是否被自动删除。
Create trigger del_trig after 
delete on course 
for each row 
delete from sc where kehao=select kehao from deleted;

• 创建一个触发器，触发的条件是向数据表 salary中插入数据之前，对新插入的 allSalary字段值进行求和计算【总发出的工资】。输入的 SQL 语句和执行过程如下所示。

 
 CREATE TRIGGER trig_hr1 AFTER INSERT ON hr 
 FOR EACH ROW
 INSERT INTO log VALUES ( NOW());

创建只有一个执行语句的触发器

 --在向用户表INSERT数据时，用户数增加，DELETE用户时，用户数减少
CREATE TRIGGER trigger_hr_count_insert AFTER INSERT ON hr 
FOR EACH ROW
UPDATE hr_count SET hr_count=hr_count+1; 

CREATE TRIGGER trigger_hr_count_delete AFTER DELETE ON hr 
FOR EACH ROW 
UPDATE hr_count SET hr_count=hr_count-1;

CREATE TABLE hr_count (
  hr_count INT(11) DEFAULT 0
);
INSERT INTO hr_count VALUES(0);
SELECT * FROM hr_count;

insert.....
delete.....
SELECT * FROM hr_count;

创建有多个执行语句的触发器

--定义一个触发器，在DELETE时首先减少用户总人数，然后判断删除的用户是哪个部门的，再减少对应部门的用户总数：
DELIMITER $$
CREATE TRIGGER trigger_hr_count_delete AFTER DELETE ON hr 
FOR EACH ROW
BEGIN
	UPDATE hr_count SET hr_count=hr_count-1 WHERE hr_dept=0;
	UPDATE hr_count SET hr_count=hr_count-1 WHERE hr_dept= OLD.hr_dept;
END $$
DELIMITER ;

删除触发器

DROP TRIGGER IF EXISTS  [trigger_name]

断言

断言创建

CREATE ASSERTION <断言名> <CHECK 字句>

举例子

-- 限制数据库课程最多60名学生选修
CREATE ASSERTION ASSE_SC_DB_NUM 
CHECK(
    60>=(SELECT COUNT(*) FROM coourse,sc 
         where sc.cno = course.cno
         and course.name = '数据库') 
)

数据库的备份

打开命令行（cmd）窗口，输入备份命令和密码，运行过程如下：

因为早期版本的MySQL数据库的information_schema数据库中没有名为COLUMN_STATISTICS的数据表。新版的mysqldump默认启用了一个新标志，通过--column-statistics=0来禁用他。

mysqldump --column-statistics=0 -h127.0.0.1 -uroot -p --databases yhr > e:\yhrs.sql

数据库恢复

 mysqldump --column-statistics=0 -h127.0.0.1 -uroot -p --databases yhr < e:\yhrs.sql

ok，对应上了吗？点个关注♥，点个赞👍吧！！！