MySQL语句学习（三）--DQL:SELECT语句基础用法

头发没了

已于 2023-03-08 19:07:27 修改

阅读量138

点赞数

分类专栏： MySQL 文章标签： mysql 学习数据库

于 2023-03-08 19:03:55 首次发布

本文链接：https://blog.csdn.net/automation666/article/details/129404907

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一、简单了解一下 mysql 的 sql 类型：

1、数据定义语言 DDL：Create、Drop、Alter 操作。用于定义库和表结构的。

2、数据操纵语言 DML：insert、update、delete。对行记录进行增删改操作。

3、数据查询语言 DQL：select。用于查询数据的。

4、数据控制语言 DCL：grant、revoke、commit、rollback。控制数据库的权限和事务。

二、数据查询语言 DQL：select。用于查询数据的。

2.1、SELECT基础使用

SELECT [DISTINCT] * 或者列1，列2...
    FROM 表名;

a、SELECT语句可以查全部列内容，用*，也可以指定查哪些列内容，把列名列出来就可以

b、FROM 指定从哪张表中查询

c、DISTINCT可选，指显示结果时，是否去掉重复数据

举例：

#创建一个student表
CREATE TABLE student
    (id INT NOT NULL DEFAULT 1,
     `name` VARCHAR(20) NOT NULL DEFAULT '', 
      chinese FLOAT NOT NULL DEFAULT 0.0, 
      english FLOAT NOT NULL DEFAULT 0.0, 
      math FLOAT NOT NULL DEFAULT 0.0
     );
INSERT INTO student(id,`name`,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,`name`,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,`name`,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,`name`,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,`name`,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,`name`,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,`name`,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,`name`,chinese,english,math) VALUES(8,'韩信',45,65,99);

#-- 查询表中所有学生的信息。
SELECT * FROM student;

#-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;

#过滤表中重复数据 distinct 
#值查询英语成绩一列，有相同的分数 会背合并去重
SELECT DISTINCT english FROM student;

#要查询的记录，每个字段都相同，才会去重
#查询的时学生名字和英语成绩，必须名字和英语分数都一样才会去重
SELECT DISTINCT `name`, english FROM student;

2.2、SELECT查询语句中使用表达式进行列运算约束

2.2.1、在 select 语句中可使用 as 语句

/*
SELECT 列名 AS 别名 
    FROM 表名
*/
-- 统计每个学生的总分
SELECT `name`, (chinese+english+math) FROM student; 
-- 在所有学生总分加 10 分的情况
SELECT `name`, (chinese + english + math + 10) FROM student; 
-- 使用别名表示学生分数。
SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score
    FROM student;

2.2.2、在 where 子句中经常使用的运算符--使用WHERE子句进行过滤查询

-- 查询英语成绩大于 90 分的同学
SELECT * FROM student
    WHERE english > 90

-- 查询总分大于 200 分的所有同学
SELECT * FROM student
    WHERE (chinese + english + math) > 200

-- 查询 math 大于 60 并且(and) id 大于 4 的学生成绩
SELECT * FROM student
    WHERE math >60 AND id > 4

-- 查询总分大于 200 分 并且 数学成绩小于语文成绩,的姓赵的学生. -- 赵% 表示 名字以赵开头的就可以
SELECT * FROM student
    WHERE (chinese + english + math) > 200 AND
           math < chinese AND `name` LIKE '赵%'

-- 查询所有姓李的学生成绩。
SELECT * FROM student
    WHERE `name` LIKE '李%'

-- 查询英语分数在 80－90 之间的同学。
SELECT * FROM student
    WHERE english >= 80 AND english <= 90;

-- between .. and .. 是 闭区间
SELECT * FROM student
    WHERE english BETWEEN 80 AND 90; 

-- 查询数学分数为 89,90,91 的同学。
SELECT * FROM student
    WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student
    WHERE math IN (89, 90, 91);

2.2.3、使用 order by 子句排序查询结果

SELECT 列1，列2，列3

FROM 表名

ORDER BY 列n ASC（升序排列，默认也是这个）或者DESC（降序排列）

a、ORDER BY 指定排序的列，排序的列既可以是表中的列名，也可以是SELECT语句后的列名

b、ASC（升序排列，默认也是这个）或者DESC（降序排列）

c、ORDER BY子句应位于SELECT语句的结尾

-- 演示 order by 使用
-- 对数学成绩排序后输出【升序】。
SELECT * FROM student
    ORDER BY math; 
-- 对总分按从高到低的顺序输出 [降序] -- 使用别名排序
SELECT `name` , (chinese + english + math) AS total_score 
    FROM student
    ORDER BY total_score DESC;
 -- 对姓李的学生成绩[总分]排序输出(升序) where + order by
SELECT `name`, (chinese + english + math) AS total_score
    FROM student
    WHERE `name` LIKE '李%' ORDER BY total_score;

2.3、合计/统计函数

2.3.1、count函数--返回行的总数

SELECT COUNT(*) 或者COUNT(列名)

FROM 表名

WHERE ......

-- 演示 mysql 的统计函数的使用
-- 统计一个班级共有多少学生？
SELECT COUNT(*) FROM student;

-- 统计数学成绩大于 90 的学生有多少个？
SELECT COUNT(*) FROM student
    WHERE math > 90

-- 统计总分大于 250 的人数有多少？
SELECT COUNT(*) FROM student
    WHERE (math + english + chinese) > 250

-- count(*) 和 count(列) 的区别：

count(*) 返回满足条件的记录的行数

count(列): 统计满足条件的某列有多少个，但是会排除为 null 的情况

CREATE TABLE t15 (`name` VARCHAR(20));

INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);
SELECT * FROM t15;

SELECT COUNT(*) FROM t15; -- 4
SELECT COUNT(`name`) --3，不统计内容为null的行

2.3.2、sum函数--返回满足WHERE条件的列中值的和，一般使用在数值列

-- 演示 sum 函数的使用
-- 统计一个班级数学总成绩？
SELECT SUM(math) FROM student; 

-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student;
 
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student; 

-- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/ COUNT(*) FROM student;

2.3.3、avg函数--返回满足WHERE条件的一列的平均值，一般使用在数值列

SELECT AVG(列名)

FROM 表名

WHERE ......

-- 演示 avg 的使用
-- 练习：
-- 求一个班级数学平均分？
SELECT AVG(math) FROM student; 
-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;

2.3.4、max/min函数--返回满足WHERE条件的一列的最大/最小值，一般使用在数值列

SELECT MAX/MIN(列名)

FROM 表名

WHERE ......

-- 演示 max 和 min 的使用
-- 求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(math + english + chinese), MIN(math + english + chinese)
    FROM student;

-- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre
    FROM student;

2.4、使用 group by 子句对列进行分组，having 子句对分组后的结果进行过滤 [先创建测试表]

SELECT 列1，列2...

FROM 表名

GROUP BY 列名

HAVING ...

创建三张表（部门表dept、员工信息表emp、级别表salgrade），并添加测试数据

/*部门表*/
CREATE TABLE dept
( 
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, 
    dname VARCHAR(20) NOT NULL DEFAULT "", 
    loc VARCHAR(13) NOT NULL DEFAULT ""
);

INSERT INTO dept VALUES
    (10, 'ACCOUNTING', 'NEW YORK'), 
    (20, 'RESEARCH', 'DALLAS'), 
    (30, 'SALES', 'CHICAGO'), 
    (40, 'OPERATIONS', 'BOSTON');

-- 员工表
CREATE TABLE emp
(
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
    ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
    job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
    mgr MEDIUMINT UNSIGNED ,/*上级编号*/
    hiredate DATE NOT NULL,/*入职时间*/
    sal DECIMAL(7,2) NOT NULL,/*薪水*/
    comm DECIMAL(7,2) ,/*红利 奖金*/
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);
-- 添加测试数据
INSERT INTO emp VALUES
(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), 
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30), 
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30), 
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20), 
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30), 
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10), 
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20), 
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10), 
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30), 
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30), 
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20), (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);

#工资级别表
CREATE TABLE salgrade
(
    grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/
    losal DECIMAL(17,2) NOT NULL, /* 该级别的最低工资 */
    hisal DECIMAL(17,2) NOT NULL /* 该级别的最高工资*/
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

演示 group by + having

GROUP by 用于对查询的结果分组统计

-- having 子句用于限制分组显示结果 .

#如何显示每个部门的平均工资和最高工资？
#1、按照部分来分组查询
#2、avg(sal) max(sal)
SELECT AVG(sal), MAX(sal) , deptno
    FROM emp 
    GROUP BY deptno;

#显示每个部门的每种岗位的平均工资和最低工资
#1、显示每个部门的平均工资和最低工资
#2、显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal) , deptno, job
    FROM emp 
    GROUP BY deptno, job;

#显示平均工资低于 2000 的部门号和它的平均工资 // 别名
#1. 显示各个部门的平均工资和部门号
#2、在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000
#3、使用别名进行过滤

SELECT deptno,avg(sal) as "avg_sal" 
    FROM emp
    GROUP BY deptno
    HAVING avg_val <2000;

2.5、字符串相关函数

-- 演示字符串相关函数的使用 ， 使用 emp 表来演示
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp;

-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp;

-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0
-- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('hanshunping', 'ping') FROM DUAL;

-- UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp; 

-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp;

-- LEFT (string2 ,length )从 string2 中的左边起取 length 个字符
-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符
SELECT LEFT(ename, 2) FROM emp;

-- LENGTH (string )string 长度[按照字节]
SELECT LENGTH(ename) FROM emp; -- REPLACE (str ,search_str ,replace_str )
-- 在 str 中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;

-- SUBSTRING (str , position [,length ])
-- 从 str 的 position 开始【从 1 开始计算】,取 length 个字符
-- 从 ename 列的第一个位置开始取出 2 个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp;

-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM('    您好，世界') FROM DUAL;
SELECT RTRIM('您好，世界    ') FROM DUAL;
SELECT TRIM('   您好，世界   ') FROM DUAL;

练习: 以首字母小写的方式显示所有员工 emp 表的姓名

-- 方法 1

-- 思路先取出 ename 的第一个字符，转成小写的

-- 把他和后面的字符串进行拼接输出即可

SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2)) AS new_name
    FROM emp;

SELECT CONCAT(LCASE(LEFT(ename,1)), SUBSTRING(ename,2)) AS new_name
    FROM emp;

2.6、数学相关函数

2.7、时间日期相关函数

-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL; 

-- CURRENT_TIME ( )当前时间
SELECT CURRENT_TIME() FROM DUAL; 

-- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;

-- 创建测试表 信息表
CREATE TABLE mes
(
    id INT ,content VARCHAR(30), 
    send_time DATETIME
);
-- 添加一条记录
INSERT INTO mes VALUES( 1, '北京新闻', CURRENT_TIMESTAMP()); 
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());

-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间.
SELECT id,content,DATA(send_time)
    FROM mes;

-- 请查询在 10 分钟内发布的新闻, 思路一定要梳理一下
SELECT content FROM mes
    WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW()
#或者：
SELECT content FROM mes
    WHERE send_time >= DATE_SUB(NOW(),INTERVAL 10 MINUTE)


-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天
#DATEDIFF(日期1，日期2)，日期1，日期2相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;

-- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;

-- FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过 FROM_UNIXTIME 转换
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;

2.8、加密和系统函数

-- USER() 查询用户
-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP
SELECT USER() FROM DUAL; -- 用户@IP 地址

-- DATABASE()查询当前使用数据库名称
SELECT DATABASE();

-- MD5(str) 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密
-- root 密码是 123 -> 加密 md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('123') FROM DUAL;

-- 演示用户表，存放密码时，是 md5
CREATE TABLE user1
    (id INT , `name` VARCHAR(32) NOT NULL DEFAULT '', pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO user1
    VALUES(100, '小明', MD5('123'));
SELECT * FROM hsp_user -- SQL 注入问题
    WHERE `name`='小明' AND pwd = MD5('123')


-- PASSWORD(str) -- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密

2.9、流程控制函数

# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;

# IFNULL(expr1,expr2) 如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2
SELECT IFNULL( NULL, '韩顺平教育') FROM DUAL;

# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果 expr1 为 TRUE,则返回 expr2,如果 expr2 为 t, 返回 expr4, 否则返回 expr5

SELECT CASE
    WHEN TRUE THEN 'jack' -- jack
    WHEN FALSE THEN 'tom' ELSE 'mary' 
    END

1. 查询 emp 表, 如果 comm 是 null , 则显示 0.0
   判断是否为 null 要使用 is null, 判断不为空 使用 is not
SELECT ename, IF(comm IS NULL , 0.0, comm)
    FROM emp;
SELECT ename, IFNULL(comm, 0.0)
    FROM emp;
2. 如果 emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
   如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename, (SELECT CASE
               WHEN job = 'CLERK' THEN '职员' 
               WHEN job = 'MANAGER' THEN '经理' 
               WHEN job = 'SALESMAN' THEN '销售人员' 
               ELSE job END) AS 'job' 
        FROM emp;