SQL语句查询关键字
目录
SQL语句查询关键字
1.SQL语句查询关键字
select
指定需要查询的字段信息
select *:查所有字段
select name:查name字段
select char_length(name):支持对 字段做处理
from
指定需要查询的表信息
from mysql.user
from t1
SQL语句只能怪关键字的执行顺序和编写顺序并不是一致的，可能会错乱
eg:
select id,name from userinfo;
我们先写的select在写的from，但是执行的时候是先执行的from在执行select
对应关键字的编写顺序和执行顺序我们没有必要过多的在意，熟练之后会非常自然的编写
我们只需要把注意力放在每个关键字的功能上即可
2.前期数据准备
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
gender enum('male','female') not null default 'male',
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
3.查询关键字之where筛选
1).查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <=6; 支持逻辑运算符
select * from emp where id between 3 and 6;
2).查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in(20000,18000,17000); 支持成员运算
3).查询id小于3大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;
4).查询员工姓名中包含字母o的员姓名与薪资
条件不够精确的查询 称之为>>>:模糊查询
模糊查询的关键字是>>>:
like
模糊查询的关键字是>>>:
%:匹配任意个数的任意字符
eg:
%o%(o jason tony tom owen)
%o(o aaao bbbo)
_:匹配单个个数得到任意字符
_o_(aox wob lol)
o_(oi ok ol)
select * from emp where name like '%o%';
5).查询员工姓名是由四个字符组成的员工姓名与薪资
select * from emp where name like '____';
select * from emp where char_length(name) = 4;
6).查询岗位秒速为空的员工名与岗位名称针对null不能用等号，只能用is
select * from emp where post_comment=NULL; 不可以
select * from emp where post_comment is NULL; 可以
"""
在MySQL中也有很多内置方法 我们可以通过帮助手册学习
help 方法名
"""
4.查询关键字之group by分组
分组：按照指定的条件将单个单个的数据组成一个个整体
eg：
将班级学生按照性别分组
将全国人民按照民族分组
将全世界人民按照肤色分组
分组的目的是为了更好的统计相关数据
eg：
每个班级的男女比例
每个民族的总占比
每个部门的平均薪资
聚合函数
专门用于分组之后的数据统计
max、min、sum、avg、count
最大值、最小值、求和、平均值、计数
1).将员工数据按照部门分组
select * from emp group by post;
"
MySQL5.6默认不会报错
set global sql_mode='strict_trans_tables,only_full_group_by'
MySQL5.7及8.0默认都会直接报错
原因是分组之后 select后面默认只能直接填写分组的依据 不能再写其他字段
select post from emp group by post;
select age from emp group by age;
分组之后默认的最小单位就应该是组 而不应该再是组内的单个数据单个字段
"
2).获取每个部门的最高工资
'要不要分组我们完全可以从题目的需求中分析出来尤其是出现关键字 每个 平均'
select post,max(salary) from emp group by post;
针对sql语句执行后之后的结果 我们是可以修改字段名称的 关键字as 也可以省略
select post as '部门',max(salary) as '最高薪资' from emp group by post;
3).一次获取部门薪资相关统计
select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
4).统计每个部门的人数
select post,count(id) from emp group by post;
5).统计每个部门的部门名称以及部门下的员工姓名
'分组以外的字段无法直接填写 需要借助于方法'
1.select post,name from emp group by post 不可以
2.select post,group_concat(name) from emp group by post;
3.可以使用各种拼接符
select post,group_concat(name,'|',age) from emp group by post;
post,group_concat(name,'_NB') from emp group by post;
select post,group_concat('DSB_',name,'_NB') from emp group by post;
5.查询关键字之having过滤
having与where本质是一样的 都是用来对数据做筛选
只不过where用在分组之前(首次筛选)，having用在分组之后(二次筛选)
统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
'
稍微复杂一点的SQL，跟写代码几乎一样，也需要提前想好大致思路
每条SQL的结果可以直接看成是一张表，基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
'
步骤1:先筛选出年龄大于30岁30的员工数据
select * from emp where age > 30;
步骤2:再对筛选出来的数据按照部门分组统计平均薪资
select post,avg(salary) from emp where age > 30 group by post;
步骤3:针对分组统计之后的结果做二次筛选
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
6.查询关键字之distinct去重
去重有一个必须得到条件也是很容易被忽略的条件
数据必须一模一样也才可以去重
select distinct id,age from emp; 没有意义
关键字针对的是多个字段组合的结果
select distinct age from emp; 达不到明确的目的，获取数据不清晰
select distinct age,post from emp;
7.查询关键字之order by排序
1).可以是单个字段排序
select * from emp order by age; 默认升序
select * from emp order by age asc; 默认升序(asc可以省略)
select * from emp order by age desc; 默认降序
2).也可以是多个字段排序
先按年龄排序，相同情况下在按照薪资排序
select * from emp order by age,salary desc;
3).统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
1.先筛选所有年龄大于10岁的员工
select * from emp where age > 10;
2.在对他们按照部门分组统计平均薪资
select post,avg(salary) from emp where age > 10 group by post;
3.针对分组的结果做二次筛选
select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000;
4.最后按照指定字段排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000 order by avg(salary);
"""
当一条SQL语句中很多地方都需要使用聚合函数计算之后的结果 我们可以节省操作(主要是节省了底层运行效率 代码看不出来)
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;
"""
8.查询关键字之limit分页
当表中数据特别多的情况下 我们很少会一次性获取所有的数据
很多网站也是做了分页处理 一次性只能看一点点
select * from emp limit 5; 直接限制展示的条数
select * from emp limit 5,5; 从第5条开始往后读取5条
查询工资最高的人的详细信息
'''千万不要关系思维 一看到工资最高就想着用分组聚合'''
select * from emp order by salary desc limit 1;
9.查询关键字之regexp正则表达式
SQL语句的模糊匹配如果用不习惯 也可以自己写正则批量查询
select * from emp where name regexp '^j.*?(n|y)$';
10.多表查询的思路
表数据准备
create table dep(
id int primary key auto_increment,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
select * from emp,dep; 会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据
11.多表查询的两种方法
方式1:连表操作
inner join 内连接
select * from emp inner join dep on emp.dep_id=dep.id;
只连接两张表中共有的数据部分
left join 左连接
select * from emp left join dep on emp.dep_id=dep.id;
以左表为基准，展示左表所有的数据，如果没有对应项则用NULL填充
right join 右连接
select * from emp right join dep on emp.dep_id=dep.id;
以右表为基准，展示右表所有的数据，如果没有对应项则用NULL填充
union 全连接
select * from emp left join dep on emp.dep_id=dep.id
union
select *from emp right join dep on emp.dep_id=dep.id;
以左表为基准，展示所有的数据，各自没有的全部NULL填充
"""
学会了连表操作之后也就可以连接N多张表
思路:将拼接之后的表起别名当成一张表再去与其他表拼接，再起别名当一张表，再去与其他表拼接，其次往复即可
"""
方式2:子查询
将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件
题目:求姓名是jason的员工部门名称
子查询类似于我们日常生活中解决问题的方式>>>:分布操作
步骤1:先根据jason获取部门编号
select dep_id from emp where name='jason';
步骤2:再根据部门编号获取部门名称
select name from dep where id=200;
总结:
select name from dep where id=(select dep_id from emp where name='jason');
'''
很多时候多表查询需要结合实际情况判断用哪种，跟多时候甚至是相互配合使用
'''
小知识点补充说明
1.concat与concat_ws
concat用于分组之前的字段拼接操作
select concat(name,'$',sex) from emp;
concat_ws拼接多个字段并且中间的连接符一致
select concat_ws('|',name,sex,age,dep_id) from emp;
2.exists
sql1 exists sql2
sql2有结果的情况下才会执行sql1，否则不执行sql1，返回空数据
3.表相关SQL补充
alter table 表名 rename 新表名; # 修改表名
alter table 表名 add 字段名 字段类型(数字) 约束条件; # 添加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;
alter table 表名 add 字段名 字段类型(数字) 约束条件 first; # 修改字段
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新字段类型(数字) 约束条件; # 修改类型
alter table 表名 drop 字段名; # 删除字段
12.可视化软件Navicat
第三方开发的用来充当数据库客户端的简单快捷的操作界面
无论第三方软件有多么的花里胡哨，底层的本质还是SQL
能够操作数据库的第三方可视化软件有很多其中针对MySQL最出名的就是Navicat
下载及使用：
1.浏览器搜索Navicat直接下载
版本很多、能够充当的数据库客户端也很多
2.破解方式
先试用在破解、直接下载破解版(老版本)、修改使用日期
3.常用操作
有些功能可能需要自己修改SQL预览
创建库、表、记录、外键
逆向数据库到模型、模型创建
新建查询可以编写SQL语句并且自带提示功能
SQL语句注释语法
--、#、/**/
运行、转储SQL文件
13.多表查询练习题
数据准备
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
练习题
"""
编写复杂的SQL不要想着一口气写完
一定要先明确思路，然后一步步写一步步查一步步补
"""
1.查询所有课程的名称以及对应的任课老师姓名
2.查询平均成绩大于八十分的同学的姓名和平均成绩
3.查询没有报李平老师课的学生姓名
4.查询没有同时选修物理课程和体育课程的学生姓名
5.查询挂科超过两门(包括两门)的学生姓名和班级
1.查询所有课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表 课程表 老师表
# 2.预览表中的数据 做到心中有数
-- select * from course;
-- select * from teacher;
# 3.确定多表查询的思路 连表 子查询 混合操作
-- select teacher.tname,course.cname from course inner join teacher on course.teacher_id = teacher.tid;
美化后
SELECT
teacher.tname,
course.cname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
2.查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表 学生表 分数表
# 2.预览表中数据
-- select * from student;
-- select * from score;
# 3.根据已知条件80分 选折切入点 分数表
# 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
-- select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
# 4.确定最终的结果需要几张表 需要两张表采用连表更加合适
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg_num > 80 ) AS t1 ON student.sid = t1.student_id;
3.查询没有报李平老师课的学生姓名
# 1.先确定需要用到几张表 老师表 课程表 分数表 学生表
# 2.预览每张表的数据
# 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
# 步骤1：先获取李平老师教授的课程id
-- select tid from teacher where tname = '李平老师';
-- select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
# 步骤2：根据课程id筛选出所有报了李平老师的学生id
-- select distinct student_id from score where course_id in(select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'));
select sname from student where sid not in(select distinct student_id from score where course_id in(select cid from course where teacher_id = (select tid from teacher where tname = '李平老师')));
4.查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表 学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理 体育相关的学生id
select student_id from score where course_id in (select cid from course where cname in ('物理','体育'));
# 6.统计每个学生报了的课程数 筛选出等于1的
select student_id from score where course_id in (select cid from course where cname in ('物理','体育')) group by student_id having count(course_id) = 1;
# 7.子查询获取学生姓名即可
select sname from student where sid in (select student_id from score where course_id in (select cid from course where cname in ('物理','体育')) group by student_id having count(course_id) = 1;)
5.查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表 分数表 学生表 班级表
# 2.预览表数据
select * from class；
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
select student.sname,class.caption from student.class_id = class.cid where student.sid in (select student_id from score where num < 60 group by student_id having count(course_id) >= 2);
python操作MySQL
pymysql模块
pip3 install pymysql
import pymysql
# 1.连接MySQL服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db4_03',
charset='utf8mb4'
)
# 2.产生游标对象
# cursor = conn.cursor() # 括号内不填写额外参数 数据是元组 指定性不强 [(),()]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # [{},{}]
# 3.编写SQL语句
# sql = 'select * from teacher;'
sql = 'select * from score;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql) # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)
14.pymysqsl补充说明
1.获取数据
fetchall() 获取所有的结果
fetchone() 获取结果集的第一个数据
fetchmany() 获取指定数量的结果集
ps:注意三者都有类似于文件光标移动的特性
cursor.scroll(1,'relative') # 基于当前位置往后移动
cursor.scroll(0,'absolute') # 基于数据的开头往后移动
2.增删改查
autocommit=True # 针对增 删 改 自动确认(直接配置)
conn.commit() # 针对 增 删 改 需要二次确认(代码确认)
