MySQL笔记2

上篇笔记简单总结了MySQL的基本特点以及书写规则和简单的查询语句，本篇笔记将演示MySQL的四种基本语言（包括DQL语言）和五种基本函数的使用。

DDL数据定义语言：该语言用于对数据库、表、索引、视图等数据库对象的定义，也可用于对数据库、表的创建、修改和删除。

主要包括CREATE、DROP、ALTER等。

本篇笔记将用DDL对数据库和表进行修改、创建。

# DDL数据定义语言，对数据库和表、索引、视图等数据库对象进行操作
SHOW DATABASES; -- 显示当前有多少数据库
USE my_db2;  -- 使用数据库

SELECT DATABASE(); -- 当我们写了多条指令，忘记所处的数据库时，可用该语句查询当前所处的数据库

SHOW TABLES ; -- 显示在my_db2数据库内有多少表

CREATE TABLE practice(  -- 创建表名为practice的数据表
    major varchar(4) COMMENT '学科',
    scores INT UNSIGNED COMMENT'成绩' -- 定义无符号整型成绩字段
)COMMENT'成绩表';

DESC practice;   -- 展示表practice的简略信息
SHOW CREATE TABLE practice; -- 展示表practice的详细信息

ALTER TABLE practice ADD name VARCHAR(5) COMMENT'姓名'; -- 更改表的字段或数据类型用alter，ADD增加字段
ALTER TABLE practice ADD id CHAR(5) COMMENT'学号'; -- 更改表的字段或数据类型用alter，ADD增加字段

ALTER TABLE practice CHANGE major  major VARCHAR(6);-- 修改字段或数据类型，此处修改了major的数据类型为VARCHAR(6)，如果要修改字段，只需将原字段、原数据类型、新字段、新数据类型依次加在CHANGE后即可。

ALTER TABLE practice MODIFY major VARCHAR(4); -- MODIFY修改字段的数据类型，此处修改了major的数据类型为VARCHAR(4)

DML数据操作语言，用于添加、删除、更新和查询数据库记录，并检查数据完整性。

主要的语句关键字包括INSERT、DELETE、UPDATE、SELECT等。

# DML数据操作语言，对表内的数据进行增删改（查）,由于查找语言的内容过多，一般将查找语言单拎出来作为DQL语言

INSERT INTO practice(name,major,scores)VALUES('李华','数学','70'),('王芳','化学','74'),('刘欢','语文','74');      -- INSERT INTO 对practice批量插入数据

INSERT INTO practice(name,major,scores)VALUE('方雅','数学','81'); -- 对practice插入数据

INSERT INTO practice(name,major)VALUE('方雅','数学'); -- 对practice插入数据

UPDATE practice SET scores=80 WHERE name='李华';   -- UPDATE可以修改满足条件的的字段数据，此处修改了李华的scores为80
UPDATE practice SET id='1' WHERE name='李华'; -- 设置了四个同学的id
UPDATE practice SET id='2' WHERE name='王芳'; 
UPDATE practice SET id='3' WHERE name='刘欢'; 
UPDATE practice SET id='4' WHERE name='方雅'; 

DELETE FROM practice WHERE scores IS NULL; -- 删除满足条件的表数据，此处删除了表practice 中scores为null的数据

DROP TABLE practice; -- 删除practice表（DDL语言）


#DQL语言，对表内的数据进行查找
SELECT * FROM  practice;-- 查询practice表中的所有字段，*号代表所有字段
SELECT scores FROM practice; -- 查询practice表中的scorce字段

SELECT * FROM practice WHERE scores>75; -- 查询practice表中的满足条件的数据

SELECT major,COUNT(major) '各学科数量' FROM practice GROUP BY major; -- 将表中的字段分组，然后查询practice表中满足条件的数据

SELECT * FROM practice LIMIT 0,2; -- 分页查询practice，数字分别为起始页码，（起始页码从0开始，如果是起始页码可以省略0，直接写查询记录数，）查询记录数
SELECT * FROM practice LIMIT 2,2; -- 起始页是（页码-1）*查询记录数

DCL数据控制语言，用于定义数据库、表、字段、用户的访问权限和安全级别。

要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。

# DCL语言 用户管理
USE mysql;
SELECT * FROM user;
CREATE USER 'itest'@'%'identified BY'123456'; -- 创建一个能在任意主机(%代表任意主机)上登录的的用户itest，密码为123456

ALTER USER 'itest'@'%'IDENTIFIED WITH mysql_native_password BY'1234'; -- 修改用户itest密码为1234

DROP USER 'itest'@'%'; -- 删除用户itest

SHOW GRANTS FOR'itest'@'%';    -- 查询itest在所有数据库，所有表中的权限，此处为USAGE仅能连接

GRANT ALL ON mysql.* TO'itest'@'%';   -- 授予itest在mysql数据库中的所有表格中有所有的权限

REVOKE ALL ON mysql.* FROM 'itest'@'%';  -- 删除itest在mysql数据库中的所有表格中有所有的权限

GRANT ALTER ON mysql.*TO 'itest'@'%';  -- 授予itest在mysql数据库中的所有表格中修改表的权限

聚合函数

#聚合函数
SELECT COUNT(name) FROM practice; -- 查询name的个数
SELECT MAX(scores) FROM practice; -- 查询scores的最大值，同理有MIN查询最小数
SELECT SUM(scores) FROM practice; -- 查询scores的总和
SELECT AVG(scores) FROM practice; -- 查询scores的平均值

字符串函数

# sql语言的字符串函数学习
SELECT CONCAT('这是一个','美好的开端');  -- CONCAT可以实现对多个字符串的拼接

SELECT LOWER('LOWER');  -- LOWER可以实现将字符串的大写字母转换成小写，结果为‘lower’

SELECT UPPER('upper'); -- UPPER可以实现将字符串的小写转成大写，结果为‘UPPER’

SELECT LPAD('1',4,'0'); -- LPAD可以实现对字符串的的左填充，括号内第一位是需要填充的字符串，第二位是目标位数，需要字符串设置为几位就设几，最后一位为填充的内容，结果为‘0001’

SELECT RPAD('1',4,'0'); -- RPAD可以实现对字符串的的右填充，此处为‘1000’

SELECT TRIM('  1  2  3  '); -- TRIM可以实现对字符串的两端空格进行去除

SELECT SUBSTRING('ST ST ST ST',1,4); -- SUBSTRING可以对字符串进行截取，空格也算一个字符，第一位为目标字符串，第二为截取起始位，第三为截取位数，由于索引从1开始，此处结果为‘ST S’

UPDATE practice SET id=LPAD(id,5,'0');  -- 将字符串和前面的语句结合起来，就能实现在具体的例子中完成数据的操作，此处为id字段的数据填充位数，使得id格式更整齐规范，此处结果为‘00001’

日期时间函数

#sql语言的日期时间函数
SELECT NOW();  -- 查询当前时间（年月日时分秒）
SELECT CURDATE();  -- 返回当前日期（年月日）
SELECT CURTIME(); -- 返回当前时间（时分秒）
SELECT YEAR(NOW()); -- 返回指定时间的年份
SELECT MONTH(now());-- 返回指定时间的月份
SELECT DAY(now());-- 返回指定时间的日

SELECT DATE_ADD(now(),INTERVAL 5 HOUR); -- 在指定日期上加减时间，单位可以是年、月、日、时、分、秒

SELECT DATEDIFF('2023-1-1',NOW()); -- 求两个时间的时间间隔，前一个时间减后一个时间

数值函数

#mysql数值函数
SELECT CEIL(2.4);  -- 向上取整，结果为3
SELECT FLOOR(2.4); -- 向下取整，结果为2
SELECT MOD(2,4); -- 求2/4的模，结果为2
SELECT ROUND(3.456,2); -- 四舍五入，后一位为保留的位数

SELECT RAND();  -- 求0~1之间的任意值，结果为小数，可将其乘以一个数值或和其他的数值函数组合使用可以得到所需的随机数

SELECT RPAD(ROUND(RAND()*100),2,'0'); -- 将RAND和ROUND（四舍五入）和RPAD组合就可以随机生成一个所需范围的任意数，此处为任意的两位数

流程函数

#流程函数
SELECT IF(2>1,'真','假');    -- 判断条件是否为真，为真返回前一个值，为假返回第二个值，和三元表示类似，此处输出‘真’

SELECT IFNULL(NULL,'非空');  -- 判断条件是否为空，为空输出后一个值，非空输出前一个值，空格不算空值，此处输出‘非空’

SELECT name ,(CASE scores WHEN 81 THEN '优秀' WHEN 80 THEN '良好'  WHEN 74 THEN '合格' END )FROM practice; -- 相当于case语句，判断具体的值

SELECT  name ,(CASE WHEN scores>79 THEN '优秀'WHEN scores>60 THEN '及格'END) FROM practice; -- 相当于if语句，判断范围