赞
踩
对于某一条指令的操作;
比如: SELECT * FROM users;
执行原理: 本质是通过3306这个端口号进入到DBMS,然后进入到相应的数据库中,找到对应的某一张表格进行查询操作;
为什么要学习sql语句呢?
# 演示创建数据库操作
CREATE DATABASE hsp_db01;
# 删除数据库指令
DROP DATABASE hsp_db01;
# 演示删除和查询数据库
# 查看当前数据库服务器中的所有数据库
SHOW DATABASES;
# 查看前面创建的hsp_db01数据库的定义信息; 反引号的作用:规避关键字;
SHOW CREATE DATABASE hsp_db01;
# 删除前面创建的hsp_db01数据库
# DROP DATABASE hsp_db01;
注意⚠️: WHERE和HAVING作用的区别:HAVING是用来指定组的条件,WHERE是用来指定行的条件;
给了一张sql表怎么备份到自己项目中: 打开sql语句, 拷贝重新执行一遍; 也有相应的语句供操作, 见资料图片
CREATE TABLE `users` (
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
`birthday` DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
解释:
数据类型的演示操作TINYINT
# 如果没有指定unsinged, 则表示有符号, 否则表示无符号;
# 演示整形的使用
CREATE TABLE t3 (
id TINYINT);
CREATE TABLE t4 (
id TINYINT UNSIGNED);
INSERT INTO t3 VALUES(-128);
INSERT INTO t4 VALUES(-128);
数据类型的演示操作; 小数型号
# 演示decimal、float、double的使用
# 创建表; DECIMAL可以存放非常大的数据;
CREATE TABLE t6 (
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30, 20));
# 添加数据
INSERT INTO t6 VALUES(88.1234567891234, 88.1234567891234, 88.1234567891234);
字符串数据类型的基本使用
# 字符串的基本使用 CHAR、VARCHAR
CREATE TABLE t7 (
`name` CHAR(255));
CREATE TABLE t8 (
`name` VARCHAR(10000));
DROP TABLE t8;
注意⚠️: 如果varchar不够用,可以尝试使用mediumtext,或者longtext, 如果想简单点,可以直接使用text;
日期类型的基本使用
# 演示时间相关的类型
CREATE TABLE t9(
birthday DATE, -- 生日
job_time DATETIME, -- 记录年月日,时分秒
login_time TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
SELECT * FROM t9;
INSERT INTO t9(birthday, job_time) VALUES('2022-11-11', '2022-11-11 10:10:10');
创建表练习
# 创建表练习
CREATE table `emp` (
id INT,
`name` VARCHAR(32),
sex CHAR(1),
birthday DATE,
entry_data DATETIME,
job VARCHAR(32),
salary DOUBLE,
`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
# 添加一条
INSERT INTO `emp` VALUES(100, 'hk', '男', '2000-11-11', '2010-11-10 11:11:11', '开心的', 20000, '自信');
修改表练习; 修改表的字段: alter
# 修改表的操作练习 ALTER TABLE emp ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER resume; # 查看表的所有列 DESC emp; # 修改job列, 使其长度为60 ALTER TABLE emp MODIFY job VARCHAR(60) NOT NULL DEFAULT ''; # 删除sex列 ALTER TABLE emp DROP sex; # 表名改为employee RENAME TABLE emp TO employee; # 修改表的字符集为 utf8 ALTER TABLE employee CHARACTER SET utf8; -- 将列名name修改为user_name ALTER TABLE employee CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''; DESC employee;
修改表的相关操作总结
注意⚠️: 插入操作的话,可以写具体的列也可以不写具体的列(需要一一对应清楚);
CREATE TABLE `goods` (
id INT,
goods_name VARCHAR(10),
price DOUBLE NOT NULL DEFAULT 100); -- 给该列一个固定的字段,并给予默认值,如果没有设置列值的话就使用默认值
DROP TABLE goods;
-- 添加数据
INSERT INTO `goods` (id, goods_name, price)
VALUES(10, '华为手机', 2000);
INSERT INTO `goods` (id, goods_name, price)
VALUES(20, '苹果手机', 5999);
SELECT * FROM goods;
通常为update+set+where(来进行限制约束操作)
-- 1. 将所有员工薪水修改为5000元[如果没有带where条件,会修改所有记录,因此要小心]
UPDATE employee SET salary = 5000;
-- 2. 将姓名为小妖怪的员工薪水修改为3000
UPDATE employee SET salary = 3000
WHERE user_name = 'hk';
-- 3. 将kb的薪水在原有基础上增加1000元
INSERT INTO employee
VALUES(2, 'kb', '1990-11-11', '2023-11-11 11:11:11', '打球的', 5000, 'hello', '图片路径');
UPDATE employee SET salary = salary + 1000 WHERE user_name = 'kb';
SELECT * FROM employee;
通常表现为 delete+from+where
-- 删除语句
-- 1. 删除表中名称为 'kb'的记录
DELETE FROM employee WHERE user_name = 'kb';
-- 2. 删除表中所有记录,提醒: 一定要小心; 并非删除表
DELETE FROM employee;
-- 3. DELETE语句不能删除某一列的值(可以使用update设为null或者'')
UPDATE employee SET job = '' WHERE user_name = 'hk';
-- 4. 要删除某张表, 而不是某张记录
DROP TABLE employee;
SELECT * FROM employee;
-- 创建一张新的学生表 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, 'hk', 99, 99, 99); INSERT INTO student(id, name, chinese, english, math) VALUES(2, 'hkk', 11, 22, 33); INSERT INTO student(id, name, chinese, english, math) VALUES(3, 'kk', 44, 55, 66); INSERT INTO student(id, name, chinese, english, math) VALUES(4, 'mm', 77, 88, 99); INSERT INTO student(id, name, chinese, english, math) VALUES(5, 'nn', 67, 68, 69); INSERT INTO student(id, name, chinese, english, math) VALUES(6, 'jj', 19, 39, 99); INSERT INTO student(id, name, chinese, english, math) VALUES(7, 'zz', 79, 99, 99); DELETE FROM student; // 从某一张表当中删除; SELECT * FROM student; -- SELECT语句的使用1 -- 1. 查询表中所有学生的信息 SELECT * FROM student; -- 2. 查询表中所有学生的姓名和对应的英语成绩 SELECT `name`, english from student; -- 3. 过滤掉表中重复数据distinct; 使用distinct来表示唯一的字段操作; SELECT DISTINCT english FROM student; -- 4. 要查询的记录,每个字段都相同,才会去重 SELECT DISTINCT `name`, english FROM student; -- SELECT语句的使用2 -- 1. 统计每个学生的总分 SELECT `name`, (chinese+english+math) FROM student; -- 2. 在所有学生总分加10分的情况 SELECT `name`, (chinese+english+math+10) FROM student; -- 3. 使用别名表示学生分数; 别名设置 AS SELECT `name`, (chinese+english+math+10) AS total_score FROM student; -- SELECT语句的使用3 -- 1. 查询姓名为hk的学生成绩 SELECT * FROM student WHERE `name` = 'hk'; -- 2. 查询英语成绩大于90分的同学 SELECT * FROM student WHERE english > 90; -- 3. 查询总分大于200分的所有同学 SELECT * FROM student WHERE (chinese + english + math) > 200; -- SELECT语句的使用4 -- 1. 查询math大于60并且(and)id大于4的学生成绩 SELECT * FROM student WHERE math > 60 AND id > 4; -- 2. 查询英语成绩大于语文成绩的同学 SELECT * FROM student WHERE english > chinese; -- 3. 查询总分大于200分并且数学成绩大于语文成绩的姓n的学生 SELECT * FROM student WHERE (chinese + english + math) > 200 AND (math > chinese) AND `name` LIKE 'n%'; LIKE表示一种模糊查询的操作; 在这个例子中,'n%' 使用了通配符 %,它匹配以字母 'n' 开头的任何文本。 -- SELECT语句的使用5 -- 1. 查询英语分数在80-90之间的学生成绩 SELECT * FROM student WHERE english >= 80 AND english <= 90; SELECT * FROM student WHERE english BETWEEN 80 AND 90; -- BETWEEN...AND...是闭区间 -- 2. 查询数学分数为89,90,91的的同学 SELECT * FROM student WHERE math = 89 OR math = 90 OR math = 91; SELECT * FROM student WHERE math IN (89, 90, 91); -- 3. 查询所有姓h的同学 SELECT * FROM student WHERE `name` LIKE 'h%'; -- SELECT语句的使用6 -- ORDER BY 的使用 -- 1. 对数学成绩排序后输出[升序] SELECT * FROM student ORDER BY math; -- 2. 对总分按从高到低的顺序输出[降序]; 还可以通过别名排序 SELECT `name`, (chinese+english+math) AS total_score FROM student ORDER BY total_score DESC; -- 3. 对姓h的学生成绩[总分]排序输出(升序) WHERE + ORDER BY SELECT `name`, (chinese+english+math) AS total_score FROM student WHERE `name` LIKE 'h%' ORDER BY total_score; -- SELECT语句的使用7 -- 统计函数的使用 -- 1. 统计一个班级有多少学生 SELECT COUNT(*) FROM student; -- 2. 统计数学成绩大于90的学生有多少个? SELECT COUNT(*) FROM student WHERE math > 90; -- 3. 统计总分大于250的人数有多少? SELECT COUNT(*) FROM student WHERE (math + english + chinese) > 250; -- 4. 解释count(*) 和 count(列)的区别 -- count(*)返回满足条件的记录的行数, count(列)统计满足条件的列有多少个; -- sum函数的使用 -- 1. 统计一个班级数学总成绩? SELECT SUM(math) FROM student; -- 2. 统计一个班级语文、数学、英语各科的总成绩 SELECT SUM(math) AS math_total_score, SUM(english), SUM(chinese) FROM student; -- 3. 统计一个班语文、英语、数学的成绩总和 SELECT SUM(math + english + chinese) FROM student; -- 4. 统计一个班级语文成绩平均分 SELECT SUM(chinese) / COUNT(*) FROM student; -- Avg函数的使用 -- 1. 求一个班级数学平均分 SELECT AVG(math) from student; -- 2. 求一个班级总分平均分 SELECT AVG(math + english + chinese) FROM student; -- max和min的使用 -- 求班级最高分和最低分 SELECT MAX(math + english + chinese), MIN(math + english + chinese) FROM student; -- 部门表 CREATE TABLE dept( deptno MEDIUMINT 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','BOSHTON'); SELECT * FROM dept; -- 员工表 CREATE TABLE `emp` ( `empno` mediumint(8) unsigned NOT NULL DEFAULT '0', `ename` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '""', `job` varchar(9) COLLATE utf8_bin NOT NULL DEFAULT '""', `mgr` mediumint(8) unsigned DEFAULT NULL, `hiredate` date NOT NULL, `sal` decimal(7,2) NOT NULL, `comm` decimal(7,2) DEFAULT NULL, `deptno` mediumint(8) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- 添加测试数据 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',7968,'1991-2-22',1250.00,500.00,30), (7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20), (7654,'MARTIN','SALESMAN',7968,'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,'1991-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,'1991-1-23',1300.00,NULL,10); SELECT * FROM emp; 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), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999) -- 演示GROUP BY + HAVING -- 1. 如何显示每个部门的平均工资和最高工资 -- 2. 按照部分来分组查询 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 -- SELECT AVG(sal), deptno FROM emp GROUP BY deptno; -- SELECT AVG(sal), deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2000; -- 取别名; 使用别名能更加简化相应的步骤及操作; SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptno HAVING avg_sal < 2000;
注意点⚠️
-- 演示字符串相关函数的使用, 使用emp表来演示 -- 1; CHARSET(str) 返回字串字符集 SELECT CHARSET(ename) FROM emp; -- 2; CONCAT(str1,str2,...), 连接字串, 将多个列拼接成一列 SELECT CONCAT(ename, '工作是', job) FROM emp; -- 3. INSTR(str,substr); 返回substr在str中出现的位置, 没有返回0 -- 从位置1开始; -- dual为亚元表, 系统表, 可以作为测试表使用; SELECT INSTR('hk', 'h') FROM DUAL; -- 4. UCASE(str); 转换成大写 SELECT UCASE(ename) FROM emp; -- 5. LCASE(str); 转换成小写 SELECT LCASE(ename) FROM emp; -- 6. LEFT(str,len); 从str中的左边起取length个字符 SELECT LEFT(ename,2) FROM emp; -- 7. LENGTH(str); str长度(按照字节) SELECT LENGTH(ename) FROM emp; SELECT LENGTH('hk') FROM DUAL; -- 2; 按照字节来返回操作 -- 8. REPLACE(str,from_str,to_str); 在str中, 使用to_str来替换from_str SELECT * FROM emp; -- 如果是manager, 就替换成经理; SELECT 后面可以添加多个字段, SELECT ename, REPLACE(job, 'MANAGER', '经理') FROM emp; -- 9. STRCMP(expr1,expr2); 逐字符比较两字串大小 SELECT STRCMP('hk', 'hkk') FROM DUAL; -- -1表示不相等, 1表示相等 -- 10. SUBSTRING(str,pos,len); 从str的pos位置开始[从1开始计算], 取length个字符 -- 从ename列的第一个位置开始取出2个字符 SELECT SUBSTRING(ename, 1, 2) FROM emp; -- 11. LTRIM(str), RTRIM(str), TRIM([remstr FROM] str) 去除空格 SELECT LTRIM(' hk') FROM DUAL;
练习
-- 练习: 以首字母小写的方式显示所有员工emp表的姓名;
SELECT * FROM emp;
-- 方法1
-- 思路: 先取出ename的第一个字符, 转成小写的, 把他和后面的字符串进行拼接输出即可
SELECT CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTRING(ename, 2)) AS new_name FROM emp;
数学相关函数
-- 数学相关函数 -- 1. ABS(num) 绝对值 SELECT ABS(-10) FROM DUAL; -- 2. BIN(N) 十进制转位二进制 SELECT BIN(10) FROM DUAL; -- 3. CEILING(X)) 向上取整, 得到比num2大的最小整数 SELECT CEILING(10.1) FROM DUAL; SELECT CEILING(-1.11) FROM DUAL; -- -1 -- 4. CONV(N,from_base,to_base) 进制转换 SELECT CONV(10,10,2) FROM DUAL; -- 十进制的十转换为二进制 -- 5. FLOOR(X) 向下取整, 得到比x小的最大整数 SELECT FLOOR(-1.1) FROM DUAL; -- -2 -- 6. FORMAT(X,D[,locale]) 保留小数点后面几位[四舍五入] SELECT FORMAT(78.112345,2) FROM DUAL; -- 7. HEX(N_or_S) 转换成十六进制 -- 8. LEAST(value1,value2,...)。求最小值 SELECT LEAST(0,-1,10, 11) FROM DUAL; -- 9. MOD(N,M) 求余 -- 10. RAND(); 说明如果rand(seed)加了种子,后面就是固定的随机数 SELECT RAND() FROM DUAL;
-- 日期时间相关函数; 对于时间表的相关操作添加时间等可以直接使用; -- 1. CURRENT_DATE 当前日期 SELECT CURRENT_DATE() FROM DUAL; -- 2. CURRENT_TIME 当前时间 SELECT CURRENT_TIME() FROM DUAL; -- 3. CURRENT_TIMESTAMP 当前时间戳 SELECT CURRENT_TIMESTAMP() FROM DUAL; -- 4. NOW() 当前日期和时间 SELECT NOW() 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 * FROM mes; -- 应用实例 -- 1. 显示所有新闻信息, 发布日期只显示日期, 不用显示时间 SELECT id, content, DATE(send_time) FROM mes; -- 2. 请查询在10分钟内发布的新闻 SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW(); SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(),INTERVAL 1000 MINUTE); -- 3. 请在mysql的sql语句中求出2011-11-11和1990-1-1相差多少天; SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL; -- 4. 请用mysql的sql语句求出你活了多少天; SELECT DATEDIFF(NOW(),'1999-05-17') FROM DUAL; SELECT DATEDIFF(NOW(),'1999-05-17')/365 FROM DUAL; -- 5. 如果你能活80岁, 求出你还能活多少天 -- 先求出活到80岁的时候,是什么日期; -- 然后使用datediff(x, NOW()) -- YEAR可以是day等等 SELECT DATEDIFF(DATE_ADD('1999-05-17',INTERVAL 80 YEAR), NOW()) FROM DUAL; -- 6. 相差时间 SELECT TIMEDIFF('10:11:11', '14:15:16') FROM DUAL; -- -04:04:05 -- 额外的相关函数 -- YEAR() MONTH() DAY() SELECT YEAR(NOW()) FROM DUAL; SELECT MONTH(NOW()) FROM DUAL; SELECT DAY(NOW()) FROM DUAL; -- 两个注意点 -- UNIX_TIMESTAMP(); 返回的是 1970-1-1到现在的秒数 SELECT UNIX_TIMESTAMP() FROM DUAL; -- FROM_UNIXTIME(); 可以把一个unix_timestamp秒数, 转成指定格式的日期 -- 意义: 在实际开发中, 通常使用int来保存一个unix时间戳, 然后使用from_unixtime()进行转换, 这是非常有实用价值的; SELECT FROM_UNIXTIME(1702535284,'%Y-%m-%d %H:%i:%s') FROM DUAL;
-- 演示加密和系统函数 -- 1. USER() 查询用户; 以及登陆的IP SELECT USER() FROM DUAL; -- 用户@IP地址; root@localhost -- 2. DATABASE() 查询当前使用数据库的名称; SELECT DATABASE() FROM DUAL; -- 3. MD5(str) 为字符串算出一个MD5 32的字符串, 常用(用户密码)加密 -- root密码是hsp -> 加密md5 -> 在数据库中存放的是加密后的密码; SELECT MD5('175511') FROM DUAL; SELECT LENGTH(MD5('hsp')) FROM DUAL; -- 演示用户表 CREATE TABLE hsp_user( id INT, `name` VARCHAR(32) NOT NULL DEFAULT '', pwd CHAR(32) NOT NULL DEFAULT ''); INSERT INTO hsp_user VALUES(100,'hk',MD5('hk')); SELECT * FROM hsp_user; -- 查询用户 SELECT * FROM hsp_user WHERE `name`='hk' AND pwd = MD5('hk'); -- 4. PASSWORD(str) -- 加密函数; 8以上版本已经移除了; -- SELECT PASSWORD('hk') FROM DUAL;
-- 流程控制函数 SELECT * FROM emp; -- 1. IF(expr1,expr2,expr3) SELECT IF(TRUE,'北京','上海'); -- 2. IFNULL(expr1,expr2); 如果expr1不为空,则返回expr1, 否则返回expr2 SELECT IFNULL(NULL,'hk'); -- 3. select CASE -- 类似于多分枝 -- WHEN when_value THEN -- statement_list -- ELSE -- statement_list -- END CASE; SELECT CASE WHEN TRUE THEN 'hk' ELSE 'kk' 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;
-- 单表查询; 查询加强 -- 1. 使用where子句, 如何查询1992.1.1后入职的员工 -- 说明: 在mysql中, 日期类型可以直接比较; SELECT * FROM emp WHERE hiredate > '1991-01-01'; -- 2. 使用like操作符进行模糊查询; -- % 表示0个到多个任意字符; _ 表示单个任意字符 -- 如何显示首字母为S的员工姓名和工资 SELECT ename, sal FROM emp WHERE ename LIKE 'S%'; -- 如何显示第三个字符为大写O的所有员工的姓名和工资 SELECT ename, sal FROM emp WHERE ename LIKE '__O%'; -- 3. 如何显示没有上级的雇员的情况 SELECT * FROM emp WHERE mgr IS NULL; -- 4. 查询表结构 DESC emp; -- 5. ORDER BY -- 使用按照工资的从低到高的顺序, 显示雇员信息 SELECT * FROM emp ORDER BY sal; -- 按照部门升序而雇员的工资降序排列, 显示雇员信息; 多个排序条件需要清楚; SELECT * FROM emp ORDER BY deptno ASC, sal DESC;
-- 分页查询
-- 按雇员的id号升序取出, 每页显示3条记录, 请分别显示第1页, 第2页, 第3页
-- SELECT... LIMIT start,rows; 表示从start+1行开始取,取出rows行,start从0开始计算
-- 第1页
SELECT * FROM emp ORDER BY empno LIMIT 0, 3;
-- 第2页
SELECT * FROM emp ORDER BY empno LIMIT 3, 3;
-- 第3页
SELECT * FROM emp ORDER BY empno LIMIT 6, 3;
-- 推导一个公式
SELECT * FROM emp ORDER BY empno LIMIT 每页显示记录数*(第几页-1), 每页显示记录数
-- 练习
-- 按雇员的empno号降序取出, 每页显示5条记录, 请分别显示第3页, 第5页对应的sql语句
SELECT * FROM emp ORDER BY empno desc LIMIT 20, 5;
group by的使用
-- 增强 GROUP BY 的使用
-- 1. 显示每种岗位的雇员总数、平均工资
SELECT COUNT(*), AVG(sal), job FROM emp GROUP BY job;
-- 2. 显示雇员总数, 以及获得补助的雇员数
-- 注意: count(列); 如果该列的值为null, 是不会统计的
SELECT COUNT(*), COUNT(comm) FROM emp;
-- 老师的一个扩展要求: 统计没有获取到补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL,1,NULL)) FROM emp;
SELECT COUNT(*), COUNT(*)-COUNT(comm) FROM emp;
-- 3. 显示管理者的总人数
SELECT COUNT(DISTINCT mgr) FROM emp;
-- 4. 显示雇员工资的最大差额;
-- 思路: max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal) FROM emp;
SELECT * FROM emp;
group by的解释
注意⚠️: 1、count(); 括号中可以书写表达式; 2、书写顺序: group by; having; order by; limit;
SELECT deptno, AVG(sal) AS avg_sal FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0,2;
解释: 一般select后面的内容作为后面条件判断的条件来使用;
-- 多表查询 -- 显示雇员名, 雇员工资以及所在部门的名字[笛卡尔积] -- 分析: 雇员名、雇员工资来自emp表; 部门名字来自dept表 -- 需求: 对emp和dept进行查询操作; -- 注意: 当我们需要指定某个列时, 需要 表.列表的形式来处理 -- 小技巧: 多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔积 SELECT ename, sal, dname, emp.deptno FROM emp, dept WHERE emp.deptno = dept.deptno; -- 如何显示部门号为10的部门名、员工名和工资 SELECT ename, sal, dname, emp.deptno FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.deptno = 10; -- 显示各个员工的姓名, 工资, 以及工资的级别; -- 思路: 姓名、工资来自emp 13; 工资级别来自salgrade 5 -- 写 sql, 由简单到复杂; SELECT ename, sal, grade FROM emp, salgrade WHERE sal BETWEEN losal AND hisal;
-- 多表查询之自连接;
-- 思考题: 显示公司员工名字和他的上级的名字
-- 分析: 员工名字在emp; 上级的名字的名字 emp
-- 员工和上级是通过emp表的mgr列关联; 注意中英文
-- 自连接特点: 1、把同一张表当作两张表来使用 2、需要给表取别名; 表名 表别名 3、列名不明确可以指定列的别名
SELECT worker.ename AS '职员名', boss.ename AS '上级名'
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;
注意⚠️: 多表自连接; 即把同一张表格当作两张表来使用;
-- mysql表子查询 /* 请思考: 如何显示于SMITH同一部门的所有员工 1. 先查询到smith的部门号得到 2. 把上面的select语句当作一个子查询来使用; */ -- SELECT deptno FROM emp WHERE ename = 'SMITH'; -- 扩了起来, 里面只有一行, 也被称为单行子查询 SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH'); -- 多行子查询 /* 如何查询和部门10的工作相同的雇员的 名字、岗位、工资、部门号; 但是不含10号部门自己的雇员 */ -- 1. 查询到10号部门有哪些工作 SELECT DISTINCT job FROM emp WHERE deptno = 10; -- 2. 把上面查询的结果当作子查询使用 -- 下面语句完整; SELECT ename, job, sal, deptno FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10) AND deptno <> 10;
-- 子查询作为临时表
-- 查询 商品表
/*
查询商品表, 先得到各个类别中, 价格最高的商品 max
*/
-- all和any的使用 -- 1. 请思考: 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号; SELECT ename, sal, deptno FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30); SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); -- any的使用 -- 2. 请思考: 显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号; SELECT ename, sal, deptno FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30); SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30);
/*
如何查询与ALLEN的部门和岗位完全相同的所有雇员(并且不含ALLEN本人)
(字段1, 字段2, ...) = (select 字段1, 字段2, from ...)
*/
-- 分析1: 得到ALLEN的部门和岗位
SELECT deptno, job FROM emp WHERE ename = 'ALLEN';
-- 分析2: 把上面的查询当作子查询来使用, 并且使用多列子查询的语法来进行
SELECT * FROM emp
WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'ALLEN')
AND ename != 'ALLEN';
-- 多列子查询的练习 /* 查找每个部门工资高于本部门平均工资的人的资料 这里要用到数据查询的小技巧, 把一个子查询当作一个临时表使用 */ -- 1. 先得到每个部门的部门号和对应的平均工资; SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno; -- 2. 把上面的结果当作子查询, 和emp进行多表查询 SELECT ename, sal, temp.avg_sal, emp.deptno FROM emp, (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) temp WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal; /* 查找每个部门工资最高的人的详细资料 */ SELECT ename, sal, temp.max_sal, emp.deptno FROM emp, (SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno) temp WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal; /* 查询每个部门的信息(包括: 部门号,编号,地址)和人员数量, */ -- 1. 部门名、编号、地址来自dept表 -- 2. 各个部门的人员数量 -> 构建一个临时表 SELECT * FROM dept; SELECT COUNT(*), deptno FROM emp GROUP BY deptno; SELECT dname, dept.deptno, loc, temp.per_sum AS '人数' FROM dept, (SELECT COUNT(*) AS per_sum, deptno FROM emp GROUP BY deptno) temp WHERE dept.deptno = temp.deptno; -- 还有一种写法; 表.* 表示将该表所有列都显示出来 -- 注意: 在多表查询中, 当多个表的列不重复时,才可以直接写列名 SELECT temp.*, dname,loc FROM dept, (SELECT COUNT(*) AS per_sum, deptno FROM emp GROUP BY deptno) temp WHERE dept.deptno = temp.deptno;
相关数据记的使用distinct进行去重操作
-- 表的复制 -- 为了对某个sql语句进行效率测试, 我们需要海量数据时, 可以使用此表创建海量数据 CREATE TABLE my_tab01( id INT, `name` VARCHAR(32), sal DOUBLE, job VARCHAR(32), deptno INT); DESC my_tab01; -- 演示如何自我复制 -- 1. 先把emp表的记录复制到my_tab01 INSERT INTO my_tab01 (id, `name`, sal, job, deptno) SELECT empno, ename, sal, job, deptno FROM emp; SELECT * FROM my_tab01; -- 2. 自我复制 INSERT INTO my_tab01 SELECT * FROM my_tab01; /* 如何删除掉一张表中的重复记录 1. 先创建一张表my_tab02, 2. 让my_tab02有重复的记录 */ CREATE TABLE my_tab02 LIKE emp; -- 把emp表的结构(列), 复制到my_tab02; INSERT INTO my_tab02 SELECT * FROM emp; -- 插入两次,会出现重复纪律的表 SELECT * FROM my_tab02; /* 考虑去重的问题; 思路: 1. 先创建一张临时表my_tmp, 该表的结构和my_tab02一样 2. 把my_tab02的记录 通过 DISTINCT 关键字处理后 把记录复制到my_tmp 3. 清除掉 my_tab02记录 4. 把my_tmp表的记录复制到my_tab02 5. drop掉临时表my_tmp */ -- 1. 先创建一张临时表my_tmp, 该表的结构和my_tab02一样 CREATE TABLE my_tmp LIKE my_tab02; -- 2. 把my_tab02的记录 通过 DISTINCT 关键字处理后 把记录复制到my_tmp INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02; -- 3. 清除掉 my_tab02记录; DELETE FROM my_tab02; -- 4. 把my_tmp表的记录复制到my_tab02 INSERT INTO my_tab02 SELECT * FROM my_tmp; -- 5. drop掉临时表my_tmp DROP TABLE my_tmp; SELECT * FROM my_tab02;
-- 合并查询
SELECT ename, sal, job FROM emp WHERE sal>2500; -- 5条记录
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; -- 3条记录
-- UNION ALL 就是将两个查询结果合并, 不会去重
SELECT ename, sal, job FROM emp WHERE sal>2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; -- 8条记录
-- UNION 就是将两个查询结果合并, 会去重
SELECT ename, sal, job FROM emp WHERE sal>2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; -- 6条记录
-- 外表连接 /* 例如:列出部门名称和这些部门的员工名称和工作 同时要求 显示出那些没有员工的部门 */ -- 创建stu CREATE TABLE stu( id INT, `name` VARCHAR(32)); INSERT INTO stu VALUES(1, 'jack'), (2, 'tom'),(3, 'kitty'),(4, 'nono'); SELECT * FROM stu; -- 创建exam CREATE TABLE exam( id INT, grade INT); INSERT INTO exam VALUES(1, 56), (2, 76),(11, 8); SELECT * FROM exam; /* 使用左连接 (显示所有人的成绩,如果没有成绩,也要显示该人的姓名和id号,成绩显示为空) */ SELECT `name`, stu.id, grade FROM stu, exam WHERE stu.id = exam.id; -- 改成左外连接 SELECT `name`, stu.id, grade FROM stu LEFT JOIN exam ON stu.id = exam.id; -- 右外连接; 显示所有成绩,如果没有名字匹配,显示空 -- 即: 右边的表(exam)和左表没有匹配的记录,也会把右表的记录显示出来; SELECT `name`, stu.id, grade FROM stu RIGHT JOIN exam ON stu.id = exam.id; /* 课堂练习 列出部门名称和这些部门的员工信息(名字和工作) 同时列出那些没有员工的部门名 */ -- 1. 使用左外连接实现; 把左表没有完全匹配的显示出来 SELECT dname, ename, job FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno; -- 2. 使用右外连接实现; SELECT dname, ename, job FROM emp RIGHT JOIN dept ON dept.deptno = emp.deptno; -- 总结: 在实际的开发中,绝大多数情况下使用的是前面学过的连接;
-- 主键使用
/*
细节
1. PRIMARY KEY不能重复而且不能为null
2. 一张表最多只能声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/不正经/article/detail/475640
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。