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

DML数据操作语言

插入数据

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中的方法:将一组逻辑语句事先在数据库中定义好,可以直接使用

好处:

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

调用:

  • 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
上一篇:MySQL学习(五)—— DML :增删改表中数据


下一篇:TRUNCATE 和 DELETE的区别