MySql教程
MySQL查询
本文主要是介绍MySQL查询，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
查询关键字
表准备
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
sex 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,sex,age,hire_date,post,salary,office,depart_id) values
('liyang','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);
查询关键字之where
# 1.查询id大于等于3小于等于6的数据 select * from emp where id>=3 and id<=6; select * from emp where id between 3 and 6; # 2.查询id小于3或者大于6的数据 select * from emp where id<3 or id>6; select * from emp where id not between 3 and 6; # 3.查询薪资是20000或者18000或者17000的数据 select * from emp where salary=20000 or salary=18000 or salary=17000; select * from emp where salary in (20000,18000,17000); ''' 模糊查询： 关键字；like 关键符号： % : 匹配任意个数的任意字符 _ : 匹配单个个数的任意字符 show variables like '%mode%'; ''' # 4.查询姓名中带有字母o的员工姓名和薪资 select name,salary from emp where name like '%o%'; # 5.查询姓名由四个字符组成的员工姓名和薪资 select name,salary from emp where name like '____'; select name,salary from emp where char_length(name)=4; # 6.查询薪资不在20000，18000，17000范围的数据 select * from emp where salary not in (20000,18000,17000); # 7.查询岗位描述为空的员工名与岗位名(针对null不能用等号，只能用is) select name,post from emp where post_comment is NULL;
查询关键字之group by分组
分组
将单个单个的个体按照指定的条件分成一个个整体
"""
分组之后默认只能直接获取到分组的依据
其他字段无法再直接获取(可以间接获取)
"""
# 默认的sql_mode
NO_ENGINE_SUBSTITUTION
# 严格模式
STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by
# 如何设置
set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by';
set global sql_mode='NO_ENGINE_SUBSTITUTION';
# 聚合函数(max min sum avg count)(分组之后需要频繁使用)
1.每个部门的最高薪资
select post,max(salary) from emp group by post;
2.每个部门的最低薪资
select post,min(salary) from emp group by post;
3.每个部门的平均薪资
select post,avg(salary) from emp group by post;
4.每个部门的人数
select post,count(*) from emp group by post;
5.每个部门的月工资总和
select post,sum(salary) from emp group by post;
给字段起别名(as)
select post as '部门',sum(salary) as '部门工资共和' from emp group by post;
'''
严格模式下无法查询非分组的字段，可以通过group_concat()获取分组以外的字段数据，并且支持拼接操作(括号内写要查询的字段)
'''
# 查询分组之后的部门名称和每个部门下所有的员工姓名
select post,group_concat(name) from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
'''
未分组之前使用的拼接功能: concat() -----> (括号内写要查询的字段)
'''
# 查询表中的姓名和名别字段
select concat(name,':',sex) from emp;
# concat_ws 指定分割符号
select concat_ws(':',name,sex,salary,age) from emp;
查询关键字之having过滤
'功能上having与where类似,但是使用位置上有所不同：where在分组之前使用,having在分组之后使用' # 统计各部门年龄在20岁以上的员工平均工资，并且保留平均工资大于10000的部门 1.先筛选出所有20岁以上的员工 select post,avg(salary) from emp where age>20; 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;
查询关键字之distinct去重
去重有一个非常严格的前提条件 数据必须是完全一样 如果数据带有主键那么肯定无法去重 select distinct age from emp;
查询关键字之order by排序
select * from emp order by salary; # 默认是升序
select * from emp order by salary asc; # 升序关键字 可以不写
select * from emp order by salary desc; # 降序
# 排序也可以指定多个字段
select * from emp order by age desc,salary asc;
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
where age>10
group by post
having avg(salary)>1000
order by avg(salary);
查询关键字之limit分页
用来限制数据的展示条数 select * from emp limit 5; # 前五条 select * from emp limit 5,5; # 起始位置、条数 # 查询工资最高的人的详细信息 # 先按照工资排序 然后限制展示条数 select * from emp order by salary desc limit 1;
查询关键字之regexp正则
正则表达式 用一些特殊符号的组合去字符串中筛选出符合条件的数据 select * from emp where name regexp '^j.*(n|y)$'; # '^j.*(n|y)$' j开头 中间无所谓 n或者y结尾
多表查询思想
1.子查询
分步解决问题
将一条SQL语句的查询结果用括号括起来当作另外一条SQL语句的查询条件
2.连表操作
先将所有需要用到的表拼接到一起(一张表)
然后就是转换成单表查询
前期表准备
#建表
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),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
子查询
# 查询egon所在的部门名称
# 第一步 先获取jason所在的部门id
select dep_id from emp where name='jason';
# 第二步 根据id号去部门表中筛选
select * from dep where id = 200;
# 完整句式
select * from dep where id=(select dep_id from emp where name='jason');
连表操作
# 前戏(了解)
select * from emp,dep;
# 基于上表筛选数据(了解)
'''为了避免字段冲突 可以在字段名前面加表名明确'''
select * from emp,dep where emp.dep_id=dep.id;
########################掌握############################
inner join 内连接 拼接公共的部分
select * from emp inner join dep on emp.dep_id=dep.id;
left join 左连接 以左表为基准展示所有数据 没有的null填充
select * from emp left join dep on emp.dep_id=dep.id;
right join 右连接 以右表为基准展示所有数据 没有的null填充
select * from emp right join dep on emp.dep_id=dep.id;
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;
Navicat可视化软件
可以充当很多数据库软件的客户端 封装了很多快捷方法
该软件默认也是收费的 需要破解
正版不破解免费试用14天
破解版(老版本):https://pan.baidu.com/s/1bpo5mqj
1.下载与安装
2.使用方法
创建库 表 记录
注意主键
外键字段
逆向数据库到模型
转储SQL文件
查询
SQL文件
/*
数据导入：
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;
多表查询题
1、 查询所有的课程的名称以及对应的任课老师姓名
4、 查询平均成绩大于八十分的同学的姓名和平均成绩
7、 查询没有报李平老师课的学生姓名
8、 查询没有同时选修物理课程和体育课程的学生姓名
9、 查询挂科超过两门(包括两门)的学生姓名和班级
#####################关键字习惯都用大写###############################
# 建议:在书写SQL语句的时候一定不要想着一次性成功 写一点看一点再写一点 慢慢拼凑起来
-- 1、 查询所有的课程的名称以及对应的任课老师姓名
# 1.先明确需要的表 course表 teacher表
-- select * from course;
-- select * from teacher;
# 2.连表操作 明确字段
-- SELECT
-- course.cname,
-- teacher.tname
-- FROM
-- course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、 查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先查看成绩表
-- select * from score;
# 2.求所有学生的平均成绩
-- select score.student_id,avg(num) from score group by score.student_id;
# 3.筛选出大于80分
-- select score.student_id,avg(num) as 'avg_num' from score group by score.student_id having avg(num)>80
-- ;
# 4.学生表与上述查询出来的表连接
-- SELECT
-- student.sname,
-- t1.avg_num
-- FROM
-- student
-- INNER JOIN ( SELECT score.student_id, avg( num ) AS 'avg_num' FROM score GROUP BY score.student_id HAVING avg( num )> 80 ) AS t1 ON student.sid = t1.student_id;
-- 7、 查询没有报李平老师课的学生姓名
# 1.正向思路:课下可以尝试一下
# 2.反向思路:先找所有报了李平老师课程的学生 再取反
# 1.先查询李平老师教授的课程id号
-- select tid from teacher WHERE tname='李平老师';
-- select cid from course where teacher_id in (select tid from teacher WHERE tname='李平老师');
# 2.去成绩表中筛选出所有报了李平老师课程的学生id号
-- select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher WHERE tname='李平老师'));
# 3.去学生表中 取反获取没有报李平老师课程的学生姓名
-- 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 IN ( SELECT tid FROM teacher WHERE tname = '李平老师' )));
-- 8、 查询没有同时选修物理课程和体育课程的学生姓名(只要报了一门的 两门和都不报都不要)
# 1.先查询物理 和 体育课程的id号
-- select cid from course where cname in ('物理','体育');
# 2.去成绩表中先筛选出所有报了课程的数据(报了一门 报了两门)
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'));
# 3.按照学生id分组 统计每个学生报了的课程数目
-- 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
-- ;
# 4.去学生表中根据id获取学生姓名
-- 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);
-- 9、 查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先去成绩表中 筛选出分数小于60分的数据
-- select * from score where num<60;
# 2.按照学生id分组 然后统计个数
-- select student_id from score where num<60 group by student_id
-- having count(num) >= 2
-- ;
# 3.将班级表与学生表拼接起来
SELECT
class.caption,
student.sname
FROM
class
INNER JOIN student ON class.cid = student.class_id
WHERE
student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( num ) >= 2 );
习题
1.查询平均年轻在25岁以上的部门名(表为之前查询关键字表) 分别运用子查询和连表操作完成上述题目 2.数据库练习题大礼包(能做几道是几道 内附答案) https://www.cnblogs.com/Dominic-Ji/p/10875493.html
表字段操作补充
# 1.添加表字段
alter table 表名 add 字段名 字段类型 约束条件; # 默认尾部追加
alter table 表名 add 字段名 字段类型 约束条件 after 已经存在的字段名;
alter table 表名 add 字段名 字段类型 约束条件 first; # 了解
# 2.修改字段
"""modify只能改字段数据类型完整约束，不能改字段名，但是change可以！"""
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 数据类型 [完整性约束条件…];
# 3.删除字段
ALTER TABLE 表名
DROP 字段名;
python操作MySQL(掌握)
1.下载模块
pip3 install pymysql
"""
python默认下载模块的地址是国外的网站
速度有时候会非常的慢 如果想要提升速度我们可以切换下载源
"""
（1）阿里云 http://mirrors.aliyun.com/pypi/simple/
（2）豆瓣http://pypi.douban.com/simple/
（3）清华大学 https://pypi.tuna.tsinghua.edu.cn/simple/
（4）中国科学技术大学 http://pypi.mirrors.ustc.edu.cn/simple/
（5）华中科技大学http://pypi.hustunique.com/
# 1.命令行切换源
pip3 install pymysql -i 源地址
# 2.pycharm永久切换
file
setting
interpreter
双击
manage 仓库
2.基本使用
import pymysql
# 创建链接对象
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='1',
database='db4_3',
charset='utf8'
)
# 生成游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 括号内不写参数是元祖组织不够明确
# 定义SQL语句
# sql = 'show tables'
sql = 'select * from teacher'
# 执行sql语句
affect_rows = cursor.execute(sql)
print(affect_rows) # 执行SQL语句所影响的行数
# 获取结果
res = cursor.fetchall()
print(res)
SQL注入
利用特殊符号的组合绕过相应的机制 如何解决 不要自己手动处理敏感数据 tom' -- fdsfdsfsdf xxx' or 1=1 -- fffdf sql = "select * from userinfo where name=%s and password=%s" # 执行sql语句 cursor.execute(sql,(username,password)) # 交由execute自动拼接 自动筛选
其他操作
针对增删改查
查重要程度很低 无序二次确认
增改删重要程度很高 都需要二次确认
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='1',
database='db5',
charset='utf8',
autocommit=True # 自动二次确认
)
这篇关于MySQL查询的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
MySQL读写分离入门：轻松掌握数据库读写分离技术12-07
-
MySQL读写分离入门教程12-07
-
MySQL分库分表入门详解12-07
-
MySQL分库分表入门指南12-07
-
MySQL慢查询入门：快速掌握性能优化技巧12-07
-
MySQL入门：新手必读的简单教程12-07
-
MySQL入门：从零开始学习MySQL数据库12-07
-
MySQL索引入门：新手快速掌握MySQL索引技巧12-07
-
BinLog学习：MySQL数据库BinLog入门教程12-06
-
Binlog学习：MySQL数据库的日志管理入门教程12-06
-
MySQL入门教程：从零开始掌握MySQL数据库12-06
-
MySQL读写分离入门教程：实现高性能数据库架构12-06
-
MySQL分库分表入门教程12-06
-
MySQL慢查询优化入门教程12-06
-
MySQL索引入门教程：提升查询效率的必备技能12-06
栏目导航
