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

编程入门行业动态更新时间:2024-10-22 18:36:04

【详细版本】MySQL数据库部分语法，<a href=https://www.elefans.com/category/jswz/34/1769351.html style= 很容易就对应上"/>

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

文章目录

- - INSERT语句
  - - 语法
  - UPDATE 语句
  - - 语法
  - DELETE
  - - 语法
  - 清空表记录
  - - 语法
  - TRUNCATE 和 DELETE 的区别
  - - 总结
  - 过滤重复数据
  - - 语法
  - 正则表达式查询
  - - 语法
  - 连接
  - - 内连接
    - 左连接
    - 右连接
    - 全连接
  - 主键
  - - 删除主键
    - 添加主键
  - 外键
  - - 删除外键
    - 添加外键
  - 视图
  - - 语法
  - 完整性
  - - 在表创建完成后，定义实体完整性
  - UNIQUE 约束
  - CHECK约束
  - - 添加
    - 删除
  - 用户
  - - 创建用户
    - 删除用户
  - 授权
  - - 用户授权
    - 删除授权
  - 角色
  - - 创建角色
    - 删除角色
    - 授权角色
    - 收回角色权限
  - 存储过程
  - - 创建存储过程
    - - 举例子
    - 调用
    - 删除
  - 函数
  - - 创建
    - 举例
    - 调用
    - 修改
    - 删除
  - 触发器
  - - 查询已经有的触发器
    - 创建触发器
    - 创建只有一个执行语句的触发器
    - 创建有多个执行语句的触发器
    - 删除触发器
  - 断言
  - - 断言创建
    - 举例子
  - 数据库的备份
  - 数据库恢复

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))BEGINSELECT hr.`name` FROM hr,hr_role,roleWHERE 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))
beginselect student.sname, max(grade),min(grade),avg(grade) from student,scwhere 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 BEGINDECLAREi INT DEFAULT 1;DECLAREcount INT DEFAULT 0;WHILEi <= 100 DOSET 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:loopif 条件 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 THENLEAVE loop_name;-- 判断条件成立则结束循环  好比java中的 boeakEND IF;SET count = count + i;SET i = i + 1;END LOOP;RETURN count;-- 函数写return,储存过程写select countEND $$
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 ROWINSERT 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
BEGINUPDATE 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.cnoand 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，对应上了吗？点个关注♥，点个赞👍吧！！！

更多推荐

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

本文发布于:2023-07-28 17:02:37，感谢您对本站的认可！

本文链接:https://www.elefans.com/category/jswz/34/1257254.html

很容易语法版本数据库详细

上一篇： jquery / javascript：未捕获的TypeError？(jquery / javascript: Uncaught TypeError?)
下一篇：将字符串插入到MySQL中

发布评论取消回复

评论列表（有 0 条评论）

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