Mysql-03-DML数据操作语言-DQL-基础查询

2023-02-01 13:13:32

DML数据操作语言

数据库操作语言DML(Data Manipulation Language )
常用语句: insert dateta update
文章目录

插入数据

INSERT INTO t_student VALUE(111,'聂','男','1999-01-01' ,122.1, 12311231,NOW());

方式一: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2……,值n);

INSERT INTO t_student(stu_num ,stu_name ,stu_sex) VALUE(111,'聂','男');

方式二:INSERT INTO 表名 set 列名1=值1,…列名n=值n;

INSERT INTO t_student SET stu_birth ='1999-01-01';

方式三:INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2……,值n),(值1,值2……,值n);

INSERT INTO t_student VALUE(111,'聂','男','1999-01-01' ,122.1, 12311231,NOW()),
(112,'聂','男','1999-01-01' ,122.1, 12311231,NOW()),
(112,'聂','男','1999-01-01' ,122.1, 12311231,NOW()),
(111,'聂','男','1999-01-01' ,122.1, 12311231,NOW()),
(112,'聂','男','1999-01-01' ,122.1, 12311231,NOW());

方式四:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)[套娃]

INSERT INTO t_student(stu_num ,stu_name ,stu_sex) SELECT stu_num ,stu_name ,stu_sex FROM t_student WHERE stu_score=122.1;

修改数据

UPDATE 表名 SET 列名 = ‘新值’WHERE 条件

UPDATE t_student SET  stu_name='康'  WHERE stu_num="112";

删除数据

delete from 表名 where 条件

truncate table表名清空整张表

删除整个数据

DROP TABLE t_student

#删除数据表结构还在
DELETE FROM t_student WHERE stu_num=111;

#清空整个数据表结构还在
TRUNCATE TABLE t_student

#删除整个数据表结构不在
DROP TABLE t_student

DQL-基础查询

DQL数据库查询语言查询时使用频率最高的一个操作,可以从一个表中查询数据,也可以从多个表中查询数据

基础查询:

语法:

select 查询列表 from 表名;

特点:

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

查询结果处理

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

函数:

类似于java中的方法:将一组逻辑语句事先在数据库中定义好,可以直接使用

好处:

隐藏了实现的细节
提高了代码的重用性质

调用:

select 函数名(实参列表) [from 表];

分类:

单行函数:如concat、length、ifnull等
分组函数:做统计使用，又称为统计函数、聚合函数、组函数

单行函数

字符函数:

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)：替换，替换所有的子串

-- 函数
-- 单行函数: 对每行的记录进行操作\
# 获取参数字节个数
SELECT	LENGTH(stu_name) FROM t_student

# 获取参数的字符个数
SELECT	CHAR_LENGTH(stu_name) FROM t_student

#upper()/lower()：将字符串变成大写/小写


# 拼接字符串
SELECT	CONCAT(stu_name,+":",stu_num) FROM t_student;

# 截取字符串 由1开始
SELECT  SUBSTRING( stu_num,1,2 )FROM t_student;

#返回子串第一次出现的索引，如果找不到返回0
SELECT  INSTR(stu_name,'a') FROM t_student;

#trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT  TRIM(stu_name) FROM t_student;

-- 用指定的字符串 左填充,右填充到指定的长度
SELECT  LPAD(stu_name,8,"ab")FROM student
SELECT  RPAD(stu_name,8,"ab")FROM student

-- 替换字符串
SELECT  REPLACE(stu_name,'1',"A")FROM student

-- 分组函数,聚合函数,统计函数  将多行处理返回一个结果
SELECT COUNT(*) FROM student

逻辑处理

case when 条件 then 结果1 else 结果2 end; 可以有多个when

语法:

(CASE WHEN stu_score>90 THEN ‘优秀’
WHEN stu_score>80 THEN ‘良好’
ELSE ‘不优秀’ END)

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



SELECT stu_num,
stu_name,
(CASE WHEN  stu_score>90 THEN '优秀' 
WHEN  stu_score>80 THEN '秀'
ELSE '不及格' END ) score_level
FROM t_student;



ALTER TABLE t_student ADD STATUS INT COMMENT '0-未审核,1-审核通过,2-不通过'
SELECT 
  stu_num,
  stu_name,
  (CASE WHEN STATUS=0 THEN '未审核'
        WHEN STATUS=1 THEN '审核通过'
        ELSE '不通过' END) score_level
FROM t_student;

ifnull---- if

ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值

# ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值

SELECT stu_num,IFNULL(stu_score,0) FROM t_student;
 
 
 -- if(条件,条件成立执行,不成立执行)
 SELECT stu_num,IF(stu_score>90,'优秀','不优秀') FROM t_student;

数学函数

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(big,small)：返回两个日期相差的天数

– 数学函数

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

– 日期函数

SELECT NOW();

SELECT CURDATE();

SELECT CURTIME();


 SELECT YEAR(stu_reg_time) FROM student
 SELECT MONTH(stu_reg_time) FROM student
 SELECT DAY(stu_reg_time) FROM student

 -- 将字符串日期 转为 日期格式
 SELECT STR_TO_DATE('2020-10-10','%Y,%m,%d') FROM student

 -- 将日期格式化为字符串
 SELECT DATE_FORMAT(stu_reg_time,'%Y年%m月')FROM student

 -- 计算两个日期之间的 天数差
 SELECT DATEDIFF('2021-1-12','2021-1-1');


%Y	年，4 位
%m	月，数值(00-12)
%d	月的天，数值(00-31)
%H	小时 (00-23)
%i	分钟，数值(00-59)
%s	秒(00-59)
%f	微秒
%T	时间, 24-小时 (hh:mm:ss)
%j	年的天 (001-366)
%w	周的天（0=星期日, 6=星期六）

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）
都会把为空的过滤掉

SELECT SUM(`stu_score`) FROM t_student;

SELECT MAX(`stu_score`) FROM t_student;

SELECT MIN(`stu_score`) FROM t_student;

SELECT AVG(`stu_score`) FROM t_student;

– count函数的一般使用count（*）用作统计行数 * 所有列,遇到一个为空的列,重新找不为空的进行统计
– 列的值为null不会被统计一般使用主键或则*

SELECT COUNT(stu_sex) FROM t_student;
 SELECT  stu_sex, COUNT (*) FROM t_student; GROUP BY stu_sex

码农公寓