【MySQL】DML语言和DQL语言

数据操纵语言DML（Data Manipulation Language）

语法简介

1.插入数据

方式1: INSERT INTO 表名(列1,列2,列n) VALUES(值1,值2,值n);
方式2: INSERT INTO 表名 set 列名1=值1,列名n=值n;
方式3: INSERT INTO 表名(列1,列2,列n) VALUES(值1,值2,值n),(值1,值2,值n);
方式4: INSERT INTO 表名(列1,列2,列n) 查询语句(查询的列数与插入列数匹配)

2.修改数据

UPDATE  表名    SET 列名  = '新的值' WHERE  [条件]

3.删除数据

-- 删除某条数据
DELETE  FROM   [表名]   WHERE   [条件]
-- 清空整张表
TRUNCATE  TABLE [表名]; 

数据查询语言DQL（Data Query Language)

语法：

​ select 查询列表 from 表名;

特点：

​ 查询列表可以是：表中的字段、常量、表达式、函数
​ 查询的结果是一个虚拟的表格

查询结果处理

查询常量值 SELECT  100;
查询表达式： select 100*98;
查询函数： select 函数;
特定列查询: select column1,column2 from table
全部列查询: select *  from table
排除重复行: select distinct column1,column2 from table
算数运算符: + - * /

单行函数

length()： -- 获取参数值的字节个数
char_length() -- 获取参数值的字符个数
concat(str1,str2,.....)：-- 拼接字符串
upper()/lower()：-- 将字符串变成大写/小写
substring(str,pos,length)：-- 截取字符串   位置从1开始
instr(str,指定字符)：-- 返回子串第一次出现的索引，如果找不到返回0
trim(str)：-- 去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
lpad(str,length,填充字符)：-- 用指定的字符实现左填充将str填充为指定长度
rpad(str,length,填充字符)：-- 用指定的字符实现右填充将str填充为指定长度
replace(str,old,new)：替换，-- 替换所有的子串

-- 逻辑处理
case  when   条件  then    结果1   else  结果2  end;  -- 可以有多个when
ifnull(被检测值,默认值)-- 函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值
if函数：if else的效果    if(条件,结果1,结果2)

-- 数学函数
round(数值)：-- 四舍五入
ceil(数值)：-- 向上取整，返回>=该参数的最小整数
floor(数值)：-- 向下取整，返回<=该参数的最大整数
truncate(数值,保留小数的位数)：-- 截断，小数点后截断到几位
mod(被除数,除数)：-- 取余，被除数为正，则为正；被除数为负，则为负
rand()：-- 获取随机数，返回0-1之间的小数

-- 日期函数
now()：-- 返回当前系统日期+时间
curdate()：-- 返回当前系统日期，不包含时间
curtime()：-- 返回当前时间，不包含日期
-- 可以获取指定的部分，年、月、日、小时、分钟、秒         
YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期) SECOND(日期) 
str_to_date：-- 将日期格式的字符转换成指定格式的日期
SELECT   STR_TO_DATE('1998-3-2', '%Y-%m-%d');      
date_format：-- 将日期转换成字符串
SELECT DATE_FORMAT(NOW(), '%y年%m月%d日)') AS output ;
datediff：-- 返回两个日期相差的天数

1.函数使用举例(字符函数)

#获取长度
SELECT LENGTH(sname) FROM smgs_student;
#拼接字符串
SELECT CONCAT(sname,"-->",ssex) FROM smgs_student;
#大小写转化
SELECT UPPER(sname) FROM smgs_student;
SELECT LOWER(sname) FROM smgs_student;
#截取字符串
SELECT SUBSTRING(CONCAT(sname,"->",ssex),1,4) FROM smgs_student;
#返回指定字符的位置
SELECT INSTR(sname,'甘') FROM smgs_student;
#去掉空格或指定的字符
SELECT TRIM(sname) FROM smgs_student;
SELECT TRIM('-->' FROM CONCAT(sname,ssex,'-->')) FROM smgs_student;
#左添加
SELECT LPAD(sname,8,'璃月:     ') FROM smgs_student;
#右添加
SELECT RPAD(sname,8,'-->欸嘿    ') FROM smgs_student;
#替换
SELECT REPLACE(CONCAT(sname,"-->",ssex),"-->"," is a ") FROM smgs_student;
#新增学生数据
SELECT  sname FROM smgs_student WHERE sname LIKE '甘%';

2.函数使用举例(逻辑处理)

SELECT 
  stu_num,
  stu_name,
  (CASE WHEN stu_score>90 THEN '优秀'
        WHEN stu_score>80 THEN '良好'
        ELSE '不优秀' END) score_level
FROM student

3.函数使用举例(数学函数和日期函数)

SELECT ROUND(4.1);-- 四舍五入
SELECT CEIL(4.1); -- 向上取整
SELECT FLOOR(4.9);-- 向下
SELECT TRUNCATE(3.3353,2); -- 截断 保留指定位数的小数
SELECT RAND();-- 获得0-1之间的随机数
 
SELECT YEAR(stu_reg_time) FROM student
SELECT MONTH(stu_reg_time) FROM student
SELECT DAY(stu_reg_time) FROM student

分组函数

-- 功能：用作统计使用，又称为聚合函数或统计函数或组函数
-- 分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）
1.sum， avg-- 一般用于处理数值型max，min，count可以处理任何类型
2.以上分组函数都忽略nul1值
3.可以和distinct搭配实现去重的运算
4.count-- 函数的单独介绍一般使用count（*）用作统计行数
5.和分组函数一同查询的字段要求是group by后的字段

分组函数使用举例

例题:

CREATE TABLE hw_homework1(
stu_num INT PRIMARY KEY COMMENT'id',
stu_name VARCHAR(6) COMMENT'姓名',
stu_subject VARCHAR(6) COMMENT'学课',
stu_score INT COMMENT'成绩'
);

INSERT INTO hw_homework1 VALUE
(1,'张三','语文',70),
(1,'张三','数学',80),
(1,'张三','英语',66),
(2,'李四','语文',50),
(2,'李四','数学',75),
(2,'李四','英语',80),
(3,'王五','语文',77),
(3,'王五','数学',55),
(3,'王五','英语',88),
(3,'王五','物理',90);
#2.查询每个人的总成绩并按从高到低排名(要求显示字段：姓名，总成绩，学号) 
SELECT stu_num,stu_name,SUM(stu_score)sum_score FROM hw_homework1 GROUP BY  stu_num,stu_name ORDER BY sum_score DESC; 
#3.查询每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）
SELECT stu_num,stu_name,(SUM(stu_score)/COUNT(*)) AS sum_score FROM hw_homework1 GROUP BY stu_num,stu_name;
#4.查询各门课程的平均成绩（要求显示字段：课程，平均成绩）
SELECT stu_subject,(SUM(stu_score)/COUNT(*)) AS sum_subgrade FROM hw_homework1 GROUP BY stu_subject;
#5.查询学生数学成绩的排名,从高到低显示（要求显示字段：学号，姓名，成绩，排名
SELECT stu_name,stu_subject,stu_score FROM hw_homework1 WHERE stu_subject = '数学' ORDER BY stu_score DESC;	
#6.查询学生成绩,按照如下格式展示, 小于60分为C,60-79为B,80-100为A
SELECT stu_name,stu_subject,
(CASE	WHEN hw_homework1.stu_score >= 80 THEN 'A'
	WHEN hw_homework1.stu_score >= 60 THEN 'B' ELSE 'C' END) AS LEVEL
FROM hw_homework1;
#7.只查询每门成绩都及格的学生(要求显示字段: 学号，姓名，课程，最高成绩)
SELECT stu_num,stu_name,MIN(stu_score)c,MAX(CONCAT(stu_subject,'-->',stu_score))AS 最高学课成绩
FROM hw_homework1
WHERE stu_score >=60
GROUP BY stu_name,stu_num
HAVING c >= 60;
#8.查询出选择课程数量大于3的学生(显示学生学号,姓名,课程总数)
SELECT stu_num,stu_name,COUNT(stu_subject)选课数 FROM hw_homework1 GROUP BY stu_num,stu_name HAVING 选课数>3;

#对该表实现分页数据查询,每页显示3条数据,分别写出sql语句查询第一页,第二页,第三页数据.
SELECT * FROM hw_homework1 LIMIT 0,3;
SELECT * FROM hw_homework1 LIMIT 3,3;
SELECT * FROM hw_homework1 LIMIT 6,3;

特殊查询

条件查询

LIKE：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值或数值型.
通配符：  %  任意多个字符，包含0个字符  _ 任意单个字符
between and 两者之间,包含临界值；
in  判断某字段的值是否属于in列表中的某一项
IS NULL（为空的）或 IS NOT NULL（不为空的）

子查询

子查询就是查询的表来自于另一个select语句

-- 子查询连接
SELECT 表1.日期,表1.胜,表2.负 
FROM 
(SELECT 日期,胜=COUNT(*) FROM tmp WHERE shengfu='胜'GROUP BY rq)表1 
INNER JOIN
(SELECT 日期,负=COUNT(*) FROM tmp WHERE shengfu='负'GROUP BY rq)表2 
ON 表1.日期=表2.日期;