MySQL入门操作
1、连接MySQL
-- 格式 mysql [-h主机名 -P端口号] -u用户名 -p mysql -h主机名 -P端口号 -u用户名 -p 注意，-P端口号，中的P是大写的，要跟密码前面的p（小写）区分开 -- 示例 mysql -uroot -p mysql -hlocalhost -P3306 -uroot -p-h主机名，-P端口号，-u用户名 之间是可分开的，看个人使用习惯
即可以 -uroot，也可 -u root
2、一些基础操作
-- 查看MySQL版本 SELECT version(); -- 查看全部数据库 SHOW DATABASES; -- 使用数据库 USE 数据库名; -- 查看当前数据库下的全部表 SHOW TABLES; -- 退出MySQL EXIT
1、SQL通用语法
- SQL语句可以单行或多行书写，以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
- 注释:
单行注释: -- 注释内容 或 # 注释内容(MySQL特有)
多行注释: /*注释内容*/
2、SQL分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象(数据库，表，字段) |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
2.1、数据类型
2.2、DDL 数据定义语言
Data Definition Language
数据定义语言，用来定义数据库对象(数据库，表，字段)
2.2.1、操作数据库
1、创建数据库
-- 语法 CREATE DATABASE 数据库名; # 或 CREATE SCHEMA 数据库名; -- --------------------- -- 实例 CREATE DATABASE mydb; CREATE SCHEMA mydb;
2、使用数据库
-- 语法 USE 数据库名; -- 实例 USE mydb;
3、查看数据库
-- 语法 SHOW DATABASES;
4、删除数据库
-- 语法 DROP DATABASE 数据库名; # 或 DROP SCHEMA 数据库名; -- ------------------- -- 实例 DROP DATABASE mydb; DROP SCHEMA mydb;
5、查看建库语句
-- 语法 SHOW CREATE DATABASE 数据库名; -- 实例 SHOW CREATE DATABASE mydb;
2.2.2、操作表
1、创建表
-- 语法 CREATE TABLE 表名( 字段名 列类型 [属性] [索引] [注释], 字段名 列类型 [属性] [索引] [注释], ..... 字段名 列类型 [属性] [索引] [注释] )[表的类型][字符集设置][注释] -- --------------------------------------- -- 实例 CREATE TABLE student( id char(10) PRIMARY KEY COMMENT '学号', name varchar(50) NOT NULL COMMENT '姓名', age smallint UNSIGNED COMMENT '年龄', sex char(1) COMMENT '性别' ) COMMENT '学生表';
2、查看表
-- 语法 SHOW TABLES;
3、修改表名
-- 语法 ALTER TABLE 旧表名 RENAME TO 新表名; # 或 ALTER TABLE 旧表名 RENAME AS 新表名; -- -------------------------------- -- 实例 ALTER TABLE t1 RENAME TO t2; ALTER TABLE t1 RENAME AS t2;
4、删除表
-- 语法 DROP TABLE 数据库名; -- ---------------- -- 实例 DROP TABLE student;
5、查看建表语句
-- 语法 SHOW CREATE TABLE 表名; -- 实例 SHOW CREATE TABLE student;
6、修改表结构
6.1、字段设置
1、添加字段
-- 格式 ALTER TABLE 表名 ADD 字段名 类型(长度) 约束; -- 示例 ALTER TABLE student ADD Sname varchar(20) NOT NULL;将字段添加到某个字段后面
-- 格式 ALTER TABLE 表名 ADD 字段名 类型(长度) 约束 AFTER 某个字段; -- 示例 ALTER TABLE student ADD Sage smallint unsigned AFTER Sname;将字段添加为第一个字段
-- 格式 ALTER TABLE 表名 ADD 字段名 类型(长度) 约束 FIRST; -- 示例 ALTER TABLE student ADD Sno char(10) PRIMARY KEY FIRST;
2、修改字段
（1）修改字段的类型长度及约束
-- 格式 ALTER TABLE 表名 MODIFY 字段名 类型(长度) 约束; -- 示例 ALTER TABLE student MODIFY Sname varchar(50) NULL;（2）修改字段名和字段的类型长度及约束
-- 格式 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) 约束; -- 示例 ALTER TABLE student CHANGE Sname stuName varchar(50) UNIQUE;区别：modify只能修改字段的类型长度及约束，而change可以修改字段名和字段的类型长度及约束
3、删除字段
-- 格式 ALTER TABLE 表名 DROP 字段名; -- 示例 ALTER TABLE student DROP Sage;
6.2、约束
1、主码(Primary Fey)
1.1、添加主码
-- 格式
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
# 或
ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
-- 示例
ALTER TABLE student ADD CONSTRAINT Sno_pKey PRIMARY KEY (Sno);
ALTER TABLE student ADD PRIMARY KEY (Sno);1.2、修改主码
1、添加约束
-- 格式 ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型;（1）添加主码(Primary Fey)
（2）添加外码(Foreign Key)
-- 格式 ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (字段名) REFERENCES 被参照表 (主码); # 或 ALTER TABLE 表名 ADD FOREIGN KEY (字段名) REFERENCES 被参照表 (主码); -- 示例 ALTER TABLE sc ADD CONSTRAINT Sno_fKey FOREIGN KEY (Sno) REFERENCES student (Sno); ALTER TABLE sc ADD FOREIGN KEY (Sno) REFERENCES student (Sno);（3）添加唯一键(Unique或Unique Key)
-- 格式 ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名); ALTER TABLE 表名 ADD UNIQUE (字段名); -- 示例 ALTER TABLE student ADD CONSTRAINT Sname_unique UNIQUE (Sname); ALTER TABLE student ADD UNIQUE (Sname);（4）添加非空约束(NOT NULL)
-- 格式 ALTER TABLE 表名 MODIFY 字段名 字段类型(长度) NOT NULL; -- 示例 ALTER TABLE student MODIFY Sname varchar(20) NOT NULL;（5）添加检查约束(Check)
-- 格式 ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK ( 约束条件 ); -- 示例 #年龄大于0 ALTER TABLE student ADD CONSTRAINT c1 CHECK ( Sage > 0 ); #性别只能为男或女 ALTER TABLE student ADD CONSTRAINT c2 CHECK ( Ssex IN ('男','女') ); #成绩在0~100之间 ALTER TABLE sc ADD CONSTRAINT c3 CHECK ( grade BETWEEN 0 AND 100);
2.3、DML 数据操作语言
Data Manipulation Language
数据操作语言，用来对数据库表中的数据进行增删改
2.3.1、插入数据
-- 语法 -- 插入单条记录 Insert into 表名 [(字段1,字段2,......)] values (值1,值2,......); -- 插入多条记录 INSERT INTO 表名 [(字段1,字段2,......)] VALUES (值1, 值2, ......), ...... (值1, 值2, ......); -- 实例 -- 插入单条记录 INSERT INTO student VALUES ('201215121', '李勇', '男', 20, 'CS'); -- 插入多条记录 INSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('201215121', '李勇', '男', 20, 'CS'), ('201215122', '刘晨', '女', 19, 'CS'), ('201215123', '王敏', '女', 18, 'MA'), ('201215125', '张立', '男', 19, 'IS');
2.3.2、修改数据
-- 语法 UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2, ...... [WHERE 条件1 ......] [ORDER BY ......] [LIMIT ......]; -- 实例 -- 将所有人成绩改为 95 分 UPDATE SC SET grade = 95; -- 将学号为 1520220001 的学生成绩修改为 100 分 UPDATE SC SET grade = 100 WHERE Sno = '1520220001'; -- 将成绩前 5 名的学生成绩都改为 95 分 UPDATE SC SET grade = 95 ORDER BY grade DESC LIMIT 5;
2.3.3、删除数据
-- 语法 DELETE [IGNORE] FROM 表名 [WHERE 条件1, 条件2, ...] [ORDER BY ...] [LIMIT ...]; -- 实例 -- 将 SC 表中数据全部清空 DELETE FROM SC; -- 将成绩不及格的学生信息从 SC 表中删除 DELETE FROM SC WHERE grade < 60; -- 将成绩倒数前 5 的学生信息从 SC 表中删除 DELETE FROM SC ORDER BY grade ASC LIMIT 5;
2.4、DQL 数据查询语言
Data Query Language
数据查询语言，用来查询数据库中表的记录
2.4.1、基本语法
-- 语法 SELECT [ALL | DISTINCT] * | 字段1 , 字段2 , ...... FROM 表1 [as 别名] [left | right | inner join 表2] -- 联合查询 [WHERE ...] -- 指定结果需满足的条件 [GROUP BY ...] -- 指定结果按照哪几个字段来分组 [HAVING] -- 过滤分组的记录必须满足的次要条件 [ORDER BY ...] -- 指定查询记录按一个或多个条件排序 [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条
2.4.2、基本查询
-- 查看全部数据 SELECT * FROM student;
2.4.3、条件查询
-- 条件查询 SELECT * FROM student WHERE Sno = '152022001';
2.4.4、聚合函数
只能用在select、group by和having语句中，不能用在where语句中，这与几个关键字的执行顺序有关。
COUNT() -- 统计个数 MAX() -- 最大值 MIN() -- 最小值 SUM() -- 求和 AVG() -- 平均值
2.4.5、分组查询
-- 分组查询 SELECT Sdept FROM student GROUP BY Sdept HAVING count(*) >= 10;
2.4.6、连接查询
2.4.7、排序和分页
-- 排序展示 SELECT * FROM student WHERE Ssex = '男' ORDER BY Sno LIMIT 10;
2.4.8、子查询
一条select查询语句中包含另外一条select查询语句
2.4.8.1、WHERE子查询
子查询出现where条件中，跟在where语句后面充当查询条件，每执行一次where条件筛选，就会进行一次子查询，效率低下。像这种反复子查询就属于相关子查询，where语句的子查询都属于相关子查询
2.4.8.2、FROM子查询
子查询跟在from之后，通常这种子查询的结果集作为一个临时表，from子查询只会执行一次，不是相关子查询，所以查询效率高。
2.4.8.3、SELECT子查询
子查询跟在SELECT之后，SELECT子查询也是相关子查询，不推荐
2.4.9、select语句中各关键字执行顺序
- from [on , join]
- where （可以使用表的别名）
- group by (可以开始使用select中字段的别名（不是表的别名）后面的语句中都可以使用)
- having [聚合函数]
- select
- distinct
- order by
- limit
1 ~ 1 选择表
2 ~ 4 筛选条件
5 ~ 8 展示
2.5、DCL 数据控制语言
Data Control Language
数据控制语言，用来创建数据库用户、控制数据库的访问权限
2.5.1、用户操作
1、查看用户
-- 查看当前用户 SELECT user(); # 或 SELECT current_user(); -- 查看全部用户 SELECT User,Host FROM mysql.user; -- 查看用户名和ip SELECT * FROM mysql.user; -- 查看用户全部信息创建的用户都在 mysql 数据库中的 user 表中。
2、创建用户
2.1、普通创建
-- 格式： CREATE USER '用户名'@'登录IP或者域名' IDENTIFIED BY '密码'; --示例： CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456'; CREATE USER 'lisi'@'%' IDENTIFIED BY '123456';
- %是指任何IP地址都可以访问你的数据库。
- localhost是指只有你本机可以访问你的数据库。
2.2、创建密码每隔一段时间会过期的用户
-- 设置u1账号密码每90天过期 CREATE USER '用户名'@'登录IP或者域名' IDENTIFIED BY '密码' PASSWORD EXPIRE INTERVAL 90 DAY;
3、修改用户名
在root用户下进行修改操作
-- 格式 RENAME USER '用户名1'@'登录IP或者域名1' TO '用户名2'@'登录IP或者域名2'; # 或 UPDATE mysql.user SET User = '用户名2',Host = '用户名2''@''登录IP或者域名2' WHERE User = '用户名1'; FLUSH PRIVILEGES; -- 刷新MySQL的系统权限相关表 -- 示例 RENAME USER 'u1'@'localhost' TO 'u2'@'%'; -- 直接重命名 UPDATE mysql.user SET User = 'u2',Host = '%' -- 直接修改mysql.user表 WHERE User = 'u1' AND Host = 'localhost'; FLUSH PRIVILEGES;mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问，还有一种方法，就是重新启动mysql服务器，来使新设置生效。
4、修改密码
1、修改当前用户密码
1.1、 使用ALTER USER命令来修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password'; FLUSH PRIVILEGES;1.2、 使用SET语句来修改当前用户密码
SET PASSWORD='new_password'; FLUSH PRIVILEGES;2、修改其它用户密码
2.1、使用ALTER语句来修改普通用户的密码
ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…; FLUSH PRIVILEGES;2.2、 使用SET命令来修改普通用户的密码
SET PASSWORD FOR 'username'@'hostname' = 'new_password'; FLUSH PRIVILEGES;2.3、使用UPDATE语句修改普通用户的密码（不推荐）
UPDATE mysql.user SET authentication_string = PASSWORD("123456") WHERE User = "username" AND Host = "hostname"; FLUSH PRIVILEGES;
5、删除用户
方式1：使用DROP方式删除（推荐）
-- 格式 DROP USER '用户名'@'登录IP或者域名'; -- 示例 DROP USER 'zhangsan'@'localhost'; FLUSH PRIVILEGES;方式2：使用DELETE方式删除 (不推荐)
DELETE FROM mysql.user WHERE Host = 'hostname' AND User = 'username'; FLUSH PRIVILEGES; -- 使用FLUSH命令来使用户生效不推荐通过DELETE FROM USER u WHERE USER='zhangsan' 进行删除，系统会有残留信息保留。而DROP USER命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。
2.5.2、权限控制
2.5.2.1、权限列表
-- 查看权限列表
SHOW PRIVILEGES;-- 常见权限
| Select | Tables | To retrieve rows from table
| Create | Databases,Tables,Indexes | To create new databases and tables
| Create view | Tables | To create new views
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE
| Alter | Tables | To alter the table
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures
| Insert | Tables | To insert data into tables
| Update | Tables | To update existing rows
| Delete | Tables | To delete existing rows
| Drop | Databases,Tables | To drop databases, tables, and views
| Show view | Tables | To see views with SHOW CREATE VIEW
| Execute | Functions,Procedures | To execute stored routines
| File | File access on server | To read and write files on the server
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess
| Index | Tables | To create or drop indexes
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege)
| References | Databases,Tables | To have references on tables
| Trigger | Tables | To use triggers
2.5.2.2、权限授予
1、基本格式：
-- 格式 GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO '用户名'@'用户地址' [WITH GRANT OPTION]; FLUSH PRIVILEGES; -- 刷新系统权限相关表 /* ON:表示这些权限可以作用于哪些数据库和哪些表，*.*表示作用于所有数据库和所有表，格式为 数据库名.表名。 比如有一个test库，想让admin拥有查看test库所有表的权限，那么可以这样写：GRANT SELECT ON test.* TO 'admin'@'%'; TO：将权限授予哪个用户。 IDENTIFIED BY：指定用户的登录密码。 WITH GRANT OPTION：表示允许用户将自己的权限授权给其它用户。 */注意：8.0版本以后需要将创建用户和授权分步进行，否则会报错
错误示例：
GRANT ALL ON mydb.* TO 'u1'@'localhost' IDENTIFIED BY '123456';上述语句在授予权限的同时创建用户的方式是错误的，会报错，应该先创建用户，再进行授权。
2、全局级别
全局权限为拥有一个给定服务器中的所有数据库的全部操作权限。
-- 格式： GRANT ALL [PRIVILEGES] ON *.* TO '用户名'@'登录IP或者域名' [WITH GRANT OPTION]; FLUSH PRIVILEGES; -- 刷新系统权限相关表 -- ALL PRIVILEGES: 表示授权用户全部权限 -- *.*：表示授予所有数据库和所有表
3、数据库层次
数据库权限为拥有一个给定数据库中的所有表
GRANT ALL [PRIVILEGES] ON database_name.* TO '用户名'@'登录IP或者域名' [WITH GRANT OPTION]; FLUSH PRIVILEGES; -- 刷新系统权限相关表 -- ALL PRIVILEGES: 表示授权用户全部权限 -- database_name.*：表示授予database_name数据库的所有表
4、表层次
表权限为拥有一个给定表中的所有列
GRANT ALL [PRIVILEGES] ON database_name.table_name TO '用户名'@'登录IP或者域名' [WITH GRANT OPTION]; FLUSH PRIVILEGES; -- 刷新系统权限相关表 -- ALL PRIVILEGES: 表示授权用户全部权限 -- database_name.table_name：表示授予database_name数据库的table_name表
2.5.2.3、权限撤销
收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句）。
注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。
收回权限
-- 格式 REVOKE 权限1,权限2,…权限n ON 数据库名称.表名 FROM '用户名'@'用户地址'; FLUSH PRIVILEGES; -- 刷新系统权限相关表收回用户的with grant option权限
-- 格 REVOKE GRANT OPTION,权限2，权限3,…权限n ON 数据库名.表名 FROM '用户名'@'用户地址'; -- 示例 -- 撤销u1用户的SELECT和GRANT OPTION的权限 REVOKE GRANT OPTION,SELECT ON mydb.table1 FROM 'u1'@'localhost';
2.5.2.4、查看权限
1、查看当前用户权限
SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();2、查看其他用户权限
SHOW GRANTS FOR '用户名'@'用户地址';2.5.3、角色管理
2.5.3.1、角色的理解
引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。
2.5.3.2、创建角色
创建角色使用CREATE ROLE语句，语法如下：
-- 格式 CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]... -- 示例 CREATE ROLE 'role1'@'localhost';角色名称的命名规则和用户名类似。如果host_name省略，默认为% ， role_name不可省略，不可为空。
2.5.3.3、给角色赋予权限
创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法是：
-- 格式 GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO '角色名'@'用户地址' [WITH GRANT OPTION]; -- 示例 GRANT SELECT ON mydb.table1 TO 'role1'@'localhost';
2.5.3.4、查看角色权限
-- 格式 SHOW GRANTS FOR '角色名'@'用户地址'; -- 示例 SHOW GRANTS FOR 'role1'@'localhost';使用SHOW语句查询权限：
只要你创建了一个角色，系统就会自动给你一个“ USAGE ”权限，意思是连接登录数据库的权限。
2.5.3.5、撤销角色权限
-- 格 REVOKE 权限1, 权限2，权限3,…权限n ON 数据库名.表名 FROM '用户名'@'用户地址'; -- 示例 -- 撤销role1角色的SELECT和GRANT OPTION的权限 REVOKE GRANT OPTION,SELECT ON mydb.table1 FROM 'role1'@'localhost';注意：修改了角色的权限，会影响拥有该角色的账户的权限。
2.5.3.6、删除角色
-- 格式 DROP ROLE '角色'@'用户地址' [,'角色2'@'用户地址']... -- 示例 DROP ROLE 'role1'@'localhost';注意： 如果删除了角色，那么用户也就失去了通过该角色所获得的所有权限。
2.5.3.7、给用户赋予角色
角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。给用户添加角色可使用GRANT语句，语法形式如下：-- 格式 GRANT '角色'@'用户地址' [,'角色2'@'用户地址',...] TO '用户'@'用户地址' [,'用户2'@'用户地址',...]; -- 示例 GRANT 'role1'@'localhost','role2'@'%' TO 'u3'@'localhost';（1）查看用户u3权限：
（2）使用u3用户登录，然后查询当前角色激活状态，如果角色未激活，结果将显示NONE
2.5.3.8、激活角色(作用于用户)
方式1：使用set default role 命令激活角色
-- 在待激活的用户下执行该语句
-- 格式 #将赋给该用户的全部角色都激活 SET DEFAULT ROLE ALL TO '用户名'@'用户地址'; #只激活指定角色 SET DEFAULT ROLE '角色'@'角色地址' [,'角色2'@'角色地址', ...] TO '用户名'@'用户地址'; -- 示例 #将赋给该用户的全部角色都激活 SET DEFAULT ROLE ALL TO 'u3'@'localhost'; #只激活指定角色 SET DEFAULT ROLE 'role1'@'localhost' TO 'u3'@'localhost';注意：若此时立即再使用命令select current_role()，还是会显示NONE。我们应该先退出该用户，再重新登录该用户即可成功激活。
方式2：通过设置系统变量activate_all_roles_on_login的值进行激活
默认值：
设置：
SET GLOBAL activate_all_roles_on_login=ON;
- ON表示用户连接到服务器时自动激活授予的全部角色。
- OFF表示用户连接到服务器时，仅激活“SET DEFAULT ROLE”指定的角色。
这条 SQL 语句的意思是，对所有角色永久激活。运行这条语句之后，用户才真正拥有了赋予角色的所有权限。
2.5.3.9、撤销用户的角色
撤销用户角色的SQL语法如下：
-- 格式 REVOKE '角色名'@'角色地址' FROM '用户名'@'用户地址'; -- 示例 REVOKE 'role1'@'localhost' FROM 'u3'@'localhost';
2.5.3.10、设置强制角色(mandatory role)
使用强制性角色，服务器会为全部的账户默认赋予该角色，而不需要显示执行赋予角色。强制角色无法被revoke 或者 drop 。
方式1：服务启动前设置
即在配置文件my.ini中设置
-- 在my.ini中找到[mysqld]，在其下面设置该语句 [mysqld] mandatory_roles='role1,role2@localhost'保存之后，重新启动mysql80服务
方式2：运行时设置
#系统重启后仍然有效 SET PERSIST mandatory_roles = 'role1,role2@localhost'; #系统重启后失效 SET GLOBAL mandatory_roles = 'role1,role2@localhost';
2.5.4、数据库MD5加密
MD5信息摘要算法（MD5 Message-Digest Algorithm）
-- 创建示例表 CREATE TABLE user( id int AUTO_INCREMENT COMMENT '编号' , name varchar(50) UNIQUE NOT NULL COMMENT '用户名', pwd varchar(50) NOT NULL COMMENT '密码', PRIMARY KEY (id) )COMMENT '用户表'; -- 插入明文密码 INSERT INTO user (name,pwd) VALUES ('小明','123456'); -- 插入密文密码 INSERT INTO user (name,pwd) VALUES ('小红',MD5('123456')); -- 给所有用户加密 UPDATE user SET pwd = MD5(pwd); -- 用户登录校验:将用户传递进来的密码，进行MD5加密，然后对比加密后的值 SELECT * FROM user WHERE name = '小明' AND pwd = MD5('123456');
3、常见函数
3.1、字符串函数
| 函数 | 描述 |
|---|---|
| char_length(s) | 返回字符串 s 的字符数 |
| concat(s1,s2...sn) | 将字符串 s1,s2 等多个字符串合并为一个字符串 |
| concat_ws(x,s1,s2...sn) | 同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 |
| lower(s) / lcase(s) | 将字符串 s 的所有字母变成小写字母 |
| upper(s) / ucase(s) | 将字符串转换为大写 |
| replace(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 |
| reverse(s) | 将字符串s反转 |
| strcmp(s1,s2) | 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 |
| substr(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 |
| trim(s) | 去掉字符串 s 开始和结尾处的空格 |
| left(s,n) | 返回字符串 s 的前 n 个字符 |
| right(s,n) | 返回字符串 s 的后 n 个字符 |
3.2、数字函数
| 函数 | 描述 |
|---|---|
| abs(x) | 返回 x 的绝对值 |
| ceil(s) | 返回大于或等于 x 的最小整数 |
| floor(x) | 返回小于或等于 x 的最大整数 |
| exp(x) | 返回 e 的 x 次方 |
| pow(x,y) | 返回 x 的 y 次方 |
| rand() | 返回 0 到 1 的随机数 |
| sqrt(x) | 返回x的平方根 |
| round(x [,y]) | 返回离 x 最近的整数，可选参数 y 表示要四舍五入的小数位数， 如果省略，则返回整数。 |
| greatest(expr1, expr2, expr3, ...) | 返回列表中的最大值（数字列表或字符串列表） |
| least(expr1, expr2, expr3, ...) | 返回列表中的最小值（数字列表或字符串列表） |
| LOG(x) 或 LOG(base, x) | 返回自然对数(以 e 为底的对数) 如果带有 base 参数，则 base 为指定带底数。 |
3.3、日期函数
| 函数 | 描述 |
|---|---|
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 |
| sysdate() | 返回当前日期和时间 |
| date_add(d，INTERVAL expr type) | d：日期 ，interval：时间值 ，type：类型 |
| DATEDIFF(d1,d2) | 计算日期 d1 -> d2 之间相隔的天数：d1 - d2 |
| timediff(time1, time2) | 计算时间差值 |
| timestampdiff(unit,datetime_expr1,datetime_expr2) | unit为单位（如年月日），计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差 |
| year(d) | 返回年份 |
| month(d) | 返回月份 |
| day(d) | 返回天数 |
| hour(d) | 返回小时 |
| minute(d) | 返回分钟 |
| second(d) | 返回秒钟 |
3.4、高级函数
DATABASE()
返回当前数据库名
SELECT DATABASE();
USER() / CURRENT_USER()
返回当前用户
SELECT USER(); SELECT CURRENT_USER()
IF(expr,v1,v2)
如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2
SELECT IF(6 % 2 = 0,'偶数','奇数');
IFNULL(v1,v2)
如果 v1 的值不为 NULL，则返回 v1，否则返回 v2
SELECT IFNULL(NULL,'not null');
VERSION()
返回数据库的版本号
SELECT VERSION();
CONNECTION_ID()
返回当前连接的唯一连接 ID
SELECT CONNECTION_ID();
LAST_INSERT_ID()
返回表中已插入或更新的最后一行的 AUTO_INCREMENT id
SELECT LAST_INSERT_ID();
3.5、流程控制函数(语句)
3.5.1、分支结构
3.5.1.1、IF语句
IF 语句的语法结构是：
IF 表达式1 THEN 操作1 [ELSEIF 表达式2 THEN 操作2] ... [ELSE 操作N] END IF;
3.5.1.2、CASE语句
CASE语句的语法结构1：
#情况一：类似于switch CASE 表达式 WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号) ... ELSE 结果n或语句n(如果是语句，需要加分号) END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）CASE语句的语法结构2：
#情况二：类似于多重if CASE WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号) ... ELSE 结果n或语句n(如果是语句，需要加分号) END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
3.5.2、循环结构
3.5.2.1、LOOP语句
LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。
LOOP语句的基本格式如下：
[loop_label:] LOOP 循环执行的语句 END LOOP [loop_label]其中，loop_label表示LOOP语句的标注名称，该参数可以省略。
3.5.2.2、WHILE语句
WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。
WHILE语句的基本格式如下：
[while_label:] WHILE 循环条件 DO 循环体 END WHILE [while_label];while_label为WHILE语句的标注名称；如果循环条件结果为真，WHILE语句内的语句或语句群被执行，直至循环条件为假，退出循环。
3.5.2.3、REPEAT语句
REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
REPEAT语句的基本格式如下：
[repeat_label:] REPEAT 循环体的语句 UNTIL 结束循环的条件表达式 END REPEAT [repeat_label]repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至expr_condition为真。
3.5.2.4、三种循环结构对比
- 这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。
- LOOP：一般用于实现简单的"死"循环
WHILE：先判断后执行
REPEAT：先执行后判断，无条件至少执行一次。
3.5.3、跳转语句
3.5.3.1、LEAVE语句
LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。
基本格式如下：
LEAVE 标记名其中，label参数表示循环的标志。LEAVE和BEGIN ... END或循环一起被使用。
3.5.3.2、ITERATER语句
ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为
continue，意思为“再次循环”。
基本格式如下：
ITERATE labellabel参数表示循环的标志。ITERATE语句必须跟在循环标志前面。
参考文章——MySQL高级篇-流程控制语句
https://zhuanlan.zhihu.com/p/473261002
---------------------------------------------------------------------------------------------------------------------------------
未完待续......
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
