【MySQL 数据库】2、MySQL 的数据控制语言、函数和约束

偏执的太偏执、 2023-10-10 20:14 42阅读 0赞

#### 目录 ####

*  一、DCL
 *  二、DCL 权限控制
 *  三、函数
 *   *  (1) 字符串函数
     *  (2) 数值函数
     *  (3) 日期函数
     *  (4) 流程控制函数
 *  四、约束
 *   *  (1) 约束概述
     *  (2) 使用约束
     *  (3) 外键约束

## 一、DCL ##

![在这里插入图片描述][4d1685460f8f44248cadf302c3935f38.png]

(1) 查询用户

SELECT
    	* 
    FROM
    	mysql.USER;

![在这里插入图片描述][e30d6277476848b78cc32ce414b7d229.png]

(2) 创建用户  
`创建用户 chenMingJiu, 只能够在当前主机localhost访问, 密码123456`

CREATE USER 'chenMingJiu' @'localhost' IDENTIFIED BY '123456';

`创建用户zgq, 可以在任意主机访问该数据库, 密码123456`

CREATE USER 'zgq' @'%' IDENTIFIED BY '123456';

`修改用户zgq的访问密码为1234`

ALTER USER 'zgq' @'%' IDENTIFIED WITH mysql_native_password BY '1234';

`删除 zgq@% 用户`

DROP USER 'zgq' @'%';

![在这里插入图片描述][cc73156e1be94f1cb343e7578960187f.png]

## 二、DCL 权限控制 ##

![在这里插入图片描述][86d9657650a1404998986324431a086f.png]

(1) 查询权限

show grants for 'chenMingJiu'@'localhost';

![在这里插入图片描述][c6585e6d5cd745578546b1e37be37653.png](2) 授予权限

grant all on study_mysql.* to 'chenMingJiu'@'localhost';

![在这里插入图片描述][944dab60d1ae411a83036c5a112d3053.png]

(3) 撤销权限

revoke all on study_mysql.* from 'chenMingJiu'@'localhost';

![在这里插入图片描述][b070ce8b7c1e45558686e5becdcb4268.png]

![在这里插入图片描述][d4c84ae08af14aeaac3cbb59972a5b3c.png]

## 三、函数 ##

### (1) 字符串函数 ###

![在这里插入图片描述][a91da62bdcfe47a1824e271f1ba0c0a2.png]

SELECT
    	concat( 'I', 'Love', 'You', '!' ) '字符串拼接';
    	
    SELECT
    	lower( 'LOVE' ) '转换为小写';
    	
    SELECT
    	upper( 'love' ) '转换为大写';
    	
    SELECT
    	lpad( '55', 6, '8' ) '左填充';
    	
    SELECT
    	rpad( '55', 6, '8' ) '右填充';
    	
    SELECT
    	trim( '   Love  You  ' ) '去除首尾空格';
    	
    SELECT
    	substring( 'ILoveYou', 2, 4 ) '截取字串';

--------------------

![在这里插入图片描述][e057d394f831468bb34a9355754321e4.png]

UPDATE emp 
    SET workno = lpad( workno, 5, '0' );

### (2) 数值函数 ###

![在这里插入图片描述][c1ed0896fc984910af5e05fe8b3369bf.png]

select ceil(1.09) '向上取整, 结果是: 2';
    
    select floor(2.999) '向下取整, 结果是: 2';
    
    select mod(5, 3) '取余数, 结果是: 2';
    
    select rand() '随机0到1之间的浮点数';
    
    select round(1.289, 2) '四舍不入, 结果是: 1.29';

![在这里插入图片描述][89d1efbe5fd1403b84847f138d18aea5.png]

SELECT
    	lpad( round( rand( ) * 1000000, 0 ), 6, '0' );

### (3) 日期函数 ###

![在这里插入图片描述][475f08cf94374851a4ef3cc470c4fb0d.png]

select CURRENT_DATE() '年月日（如: 2020-05-20）';
    select curdate() 'yyyy-mm-dd';
    
    select CURRENT_TIME() '时分秒';
    select curtime() 'hh::MM:ss';
    
    select NOW() '年月日 时分秒';

SELECT YEAR(NOW());
    
    SELECT MONTH(NOW());
    
    SELECT DAY(NOW());

select DATE_ADD(now(),INTERVAL 7 DAY);
    
    select DATE_ADD(now(),INTERVAL 1 YEAR);
    
    select DATE_ADD(now(),INTERVAL 2 MONTH);
    
    
    select DATEDIFF(curdate(), '1999-09-30') '计算两个日期相差多少天（第一个参数的日期减去第二个参数的日期）';

![在这里插入图片描述][c739c71350b34cd582539b88be28ca2e.png]

SELECT
    	`name`,
    	datediff( curdate( ), entrydate ) 'entrydays' 
    FROM
    	emp 
    ORDER BY
    	entrydays DESC;

### (4) 流程控制函数 ###

![在这里插入图片描述][5e021a8a2db542dea0e6221e8cc4ac1a.png]

select IFNULL('121','211');
    select IFNULL('','211') '结果为空字符串';
    select IFNULL(null,'me');

![在这里插入图片描述][c8dd22376502482a8c6a56a41e12fe02.png]

SELECT
    	`name`,
    	(
    CASE
    	workaddress 
    	WHEN '北京' THEN
    	concat( workaddress, '(一线城市)' ) 
    	WHEN '上海' THEN
    	concat( workaddress, '(一线城市)' ) ELSE concat( workaddress, '(二线城市)' ) 
    END 
    	) AS '工作地址' 
    FROM
    	emp;

![在这里插入图片描述][a3365bd5e8404ee9a422df079166acc2.png]

--------------------

CREATE TABLE student (
    id INT COMMENT 'ID',
    `name` VARCHAR ( 20 ) COMMENT '姓名',
    math INT COMMENT '数学',
    english INT COMMENT '英语',
    chinese INT COMMENT '语文' 
    ) COMMENT '学员成绩表';
    
    INSERT INTO student ( id, NAME, math, english, chinese )
    VALUES
    	( 1, 'Tom', 67, 88, 95 ),
    	( 2, 'Rose', 23, 66, 90 ),
    	( 3, 'Jack', 56, 98, 76 );

![在这里插入图片描述][841408e724264a778118ca8b4ca64acf.png]

> 需求：统计 student 表中每个学生的成绩。展示规则如下：  
> ① 分数大于等于85，在分数后面加上“优秀”；  
> ② 分数大于等于60，在分数后面加上“及格”；  
> ③ 否则，在分数后面加上“不及格”

SELECT
    	id,
    	`name`,
    	(
    CASE
    	
    	WHEN math >= 85 THEN
    	concat( math, '(优秀)' ) 
    	WHEN math >= 60 THEN
    	concat( math, '(及格)' ) ELSE concat( math, '(不及格)' ) 
    END 
    	) '数学',
    	(
    	CASE
    			
    			WHEN english >= 85 THEN
    			concat( english, '(优秀)' ) 
    			WHEN english >= 60 THEN
    			concat( english, '(及格)' ) ELSE concat( english, '(不及格)' ) 
    		END 
    		) '英语',
    		(
    		CASE
    				
    				WHEN chinese >= 85 THEN
    				concat( chinese, '(优秀)' ) 
    				WHEN chinese >= 60 THEN
    				concat( chinese, '(及格)' ) ELSE concat( chinese, '(不及格)' ) 
    			END 
    			) '语文' 
    	FROM
    student;

## 四、约束 ##

### (1) 约束概述 ###

![在这里插入图片描述][8c3a72e371274138b14441a5ff153fdb.png]

![在这里插入图片描述][248fc7c4df0a4e9e80ed7ed11b656afd.png]

### (2) 使用约束 ###

![在这里插入图片描述][9e640ca49ae4497595fe33292157a282.png]

CREATE TABLE people (
    	id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
    	`name` VARCHAR ( 10 ) NOT NULL UNIQUE COMMENT '姓名',
    	age INT COMMENT '年龄',
    	`status` CHAR ( 1 ) DEFAULT '1' COMMENT '状态',
    	gender CHAR ( 1 ) COMMENT '性别' 
    );

![在这里插入图片描述][2a2fc5c1eb894e319bfb6206b80a9917.png]

### (3) 外键约束 ###

![在这里插入图片描述][ca881587f00f4eeb89654374449430ee.png]

CREATE TABLE dept ( id INT auto_increment COMMENT 'ID' PRIMARY KEY, NAME VARCHAR ( 50 ) NOT NULL COMMENT '部门名称' ) COMMENT '部门表';
    INSERT INTO dept ( id, NAME )
    VALUES
    	( 1, '研发部' ),
    	( 2, '市场部' ),
    	( 3, '财务部' ),
    	( 4, '销售部' ),
    	( 5, '总经办' );
    	
    CREATE TABLE emp2 (
    id INT auto_increment COMMENT 'ID' PRIMARY KEY,
    NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名',
    age INT COMMENT '年龄',
    job VARCHAR ( 20 ) COMMENT '职位',
    salary INT COMMENT '薪资',
    entrydate date COMMENT '入职时间',
    managerid INT COMMENT '直属领导ID',
    dept_id INT COMMENT '部门ID' 
    ) COMMENT '员工表';
    
    INSERT INTO emp2 ( id, NAME, age, job, salary, entrydate, managerid, dept_id )
    VALUES
    	( 1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5 ),
    	( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
    	( 3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1 ),
    	( 4, '韦一笑', 48, '开
    	发', 11000, '2002-02-05', 2, 1 ),
    	( 5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1 ),
    	( 6, '小昭', 19, '程
    	序员鼓励师', 6600, '2004-10-12', 2, 1 );

`添加外键：`  
![在这里插入图片描述][703bad18b03c4ea595a6added57a2cf9.png]

![在这里插入图片描述][54a94126f47543b48d0b5912384f6d9c.png]

![在这里插入图片描述][da92c76d598d41ab9d7386db1b7461ed.png]

![在这里插入图片描述][bdaa0d5e74d64e0496c2468d89d6f5a3.png]

[4d1685460f8f44248cadf302c3935f38.png]: https://img-blog.csdnimg.cn/4d1685460f8f44248cadf302c3935f38.png
[e30d6277476848b78cc32ce414b7d229.png]: https://img-blog.csdnimg.cn/e30d6277476848b78cc32ce414b7d229.png
[cc73156e1be94f1cb343e7578960187f.png]: https://img-blog.csdnimg.cn/cc73156e1be94f1cb343e7578960187f.png
[86d9657650a1404998986324431a086f.png]: https://img-blog.csdnimg.cn/86d9657650a1404998986324431a086f.png
[c6585e6d5cd745578546b1e37be37653.png]: https://img-blog.csdnimg.cn/c6585e6d5cd745578546b1e37be37653.png
[944dab60d1ae411a83036c5a112d3053.png]: https://img-blog.csdnimg.cn/944dab60d1ae411a83036c5a112d3053.png
[b070ce8b7c1e45558686e5becdcb4268.png]: https://img-blog.csdnimg.cn/b070ce8b7c1e45558686e5becdcb4268.png
[d4c84ae08af14aeaac3cbb59972a5b3c.png]: https://img-blog.csdnimg.cn/d4c84ae08af14aeaac3cbb59972a5b3c.png
[a91da62bdcfe47a1824e271f1ba0c0a2.png]: https://img-blog.csdnimg.cn/a91da62bdcfe47a1824e271f1ba0c0a2.png
[e057d394f831468bb34a9355754321e4.png]: https://img-blog.csdnimg.cn/e057d394f831468bb34a9355754321e4.png
[c1ed0896fc984910af5e05fe8b3369bf.png]: https://img-blog.csdnimg.cn/c1ed0896fc984910af5e05fe8b3369bf.png
[89d1efbe5fd1403b84847f138d18aea5.png]: https://img-blog.csdnimg.cn/89d1efbe5fd1403b84847f138d18aea5.png
[475f08cf94374851a4ef3cc470c4fb0d.png]: https://img-blog.csdnimg.cn/475f08cf94374851a4ef3cc470c4fb0d.png
[c739c71350b34cd582539b88be28ca2e.png]: https://img-blog.csdnimg.cn/c739c71350b34cd582539b88be28ca2e.png
[5e021a8a2db542dea0e6221e8cc4ac1a.png]: https://img-blog.csdnimg.cn/5e021a8a2db542dea0e6221e8cc4ac1a.png
[c8dd22376502482a8c6a56a41e12fe02.png]: https://img-blog.csdnimg.cn/c8dd22376502482a8c6a56a41e12fe02.png
[a3365bd5e8404ee9a422df079166acc2.png]: https://img-blog.csdnimg.cn/a3365bd5e8404ee9a422df079166acc2.png
[841408e724264a778118ca8b4ca64acf.png]: https://img-blog.csdnimg.cn/841408e724264a778118ca8b4ca64acf.png
[8c3a72e371274138b14441a5ff153fdb.png]: https://img-blog.csdnimg.cn/8c3a72e371274138b14441a5ff153fdb.png
[248fc7c4df0a4e9e80ed7ed11b656afd.png]: https://img-blog.csdnimg.cn/248fc7c4df0a4e9e80ed7ed11b656afd.png
[9e640ca49ae4497595fe33292157a282.png]: https://img-blog.csdnimg.cn/9e640ca49ae4497595fe33292157a282.png
[2a2fc5c1eb894e319bfb6206b80a9917.png]: https://img-blog.csdnimg.cn/2a2fc5c1eb894e319bfb6206b80a9917.png
[ca881587f00f4eeb89654374449430ee.png]: https://img-blog.csdnimg.cn/ca881587f00f4eeb89654374449430ee.png
[703bad18b03c4ea595a6added57a2cf9.png]: https://img-blog.csdnimg.cn/703bad18b03c4ea595a6added57a2cf9.png
[54a94126f47543b48d0b5912384f6d9c.png]: https://img-blog.csdnimg.cn/54a94126f47543b48d0b5912384f6d9c.png
[da92c76d598d41ab9d7386db1b7461ed.png]: https://img-blog.csdnimg.cn/da92c76d598d41ab9d7386db1b7461ed.png
[bdaa0d5e74d64e0496c2468d89d6f5a3.png]: https://img-blog.csdnimg.cn/bdaa0d5e74d64e0496c2468d89d6f5a3.png