MySQL
MySQL：是用于管理文件的一个软件
— 服务端软件
— socket服务器
— 本地文件操作
— 解析指令【SQL语句】
— 客户端软件（各种各样）
— socket客户端
— 发送指令
— 解析指令【SQL语句】
连接：
show databases；展示数据库
use 数据库名称；
show table；展示列表
select * from 表名；展示列表里面全部内容
select name，age，id from 表名；
mysql 数据库user表
use mysql；
select user，host from user；
学习SQL语句规则
操作文件夹
create databases db1；创建文件夹
create dababases db1 default charset utf8;******
show databases；
drop databases db2；
操作文件
show tables；创建表
create table t1(id int,name,char(10)) default charset utf8;
creare table t1(id int,name char(10)) engine=innodb default charset=utf8;
create table t3(id int auto_increment,name chae(10))engine=innodb default charset=utf8;******
creat table t1(
列名 类型 null，
列名 类型 not null，
列名 类型 not null auto_increment primary key，
id int，
name char（10）
)engine=innodb default charset=utf8;
innodb 支持事物，原子性操作 myisam auto_increment 表示：自增 primary：表示约束（不能重复且不能为空）；加速查找 not null：是否为空 数字类型： 数字： tinyint：小整数，数据类型用于保存一些范围的整数数值范围： int：整数，数据类型用于保存一些范围的整数数值范围： bigint：大整数，数据类型用于保存一些范围的整数数值范围： FLOAT(M,D)：单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。 DOUBLE(M,D)：双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。 decimal[(m[,d])]：准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。特别的：对于精确数值计算时需要用此类型。 decaimal能够存储精确值的原因在于其内部按照字符串存储。 字符串： char(10) （定长）速度快，空的位置补空值 ****** varchar(10) 节省空间 PS：创建数据表定长列往前放 create table t1( id int not null auto_increment primary key, num decimal(10,5), name char(10), )engine=innodb default charset=utf8; 清空表： delete from t1; truncate table t1; 删除表： drop table t1;
操作文件中的内容
插入数据： insert into t1(id,name) values(1,'alex'); 删除： delete from t1 where id>6; 修改： update t1 set age=18; update t1 set age=18 where age=17; 查看数据： select * from t1;
外键：
create table userinfo( uid int auto_increment primary key, name varchar(32), department_id int, student_id int, constraint fk_user_depar foreirn key (department_id) references color(id) )engine=innodb default charset=utf8; create table department( id bigint auto_increment primary key, title char(5) )engine=innodb default charset=utf8;
主键：
主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。 create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null, num int not null, primary key(nid,num) )
自增：
自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列） create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null auto_increment, num int null, index(nid) ) 注意：1、对于自增列，必须是索引（含主键）。 2、对于自增可以设置步长和起始值 show session variables like 'auto_inc%'; set session auto_increment_increment=2; set session auto_increment_offset=10; shwo global variables like 'auto_inc%'; set global auto_increment_increment=2; set global auto_increment_offset=10;
表内容操作：
增
1 insert into 表 (列名,列名...) values (值,值,值...) 2 insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) 3 insert into 表 (列名,列名...) select (列名,列名...) from 表
删1 deletefrom表
2 delete from 表 where id＝1 and name＝'alex' 改
1 update 表 set name ＝ 'alex' where id>1 查
1 select * from 表
2 select * from 表 where id > 1 3 select nid,name,gender as gg from 表 where id > 1其它
1、条件
select * from 表 where id > 1 and name != 'alex' and num = 12;
select * from 表 where id between 5 and 16;
select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select nid from 表)
2、通配符 select * from 表 where name like 'ale%' - ale开头的所有（多个字符串） select * from 表 where name like 'ale_' - ale开头的所有（一个字符） 3、限制 select * from 表 limit 5; - 前5行 select * from 表 limit 4,5; - 从第4行开始的5行 select * from 表 limit 5 offset 4 - 从第4行开始的5行 4、排序 select * from 表 order by 列 asc - 根据 “列” 从小到大排列 select * from 表 order by 列 desc - 根据 “列” 从大到小排列 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序 5、分组 select num from 表 group by num select num,nid from 表 group by num,nid select num,nid from 表 where nid > 10 group by num,nid order by nid desc select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid select num from 表 group by num having max(id) > 10 **** 如果对于聚合函数结果进行二次筛选时，必须使用having ****
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
特别的：group by 必须在where之后，order by之前 6、连表 select * from userinfo5,department5 where userinfo5.part_id = department5.id
select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
# userinfo 左边全部显示
select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
# department5右边全部显示
select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
# 将出现null时一行隐藏
无对应关系则不显示 select A.num, A.name, B.name from A,B Where A.nid = B.nid 无对应关系则不显示 select A.num, A.name, B.name from A inner join B on A.nid = B.nid A表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A left join B on A.nid = B.nid B表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A right join B on A.nid = B.nid 7、组合 组合，自动处理重合 select nickname from A union select name from B 组合，不处理重合 select nickname from A union all select name from BMySQL练习题
1、自行创建测试数据 2、查询“生物”课程比“物理”课程成绩高的所有学生的学号； 3、查询平均成绩大于60分的同学的学号和平均成绩； 4、查询所有同学的学号、姓名、选课数、总成绩； 5、查询姓“李”的老师的个数； 6、查询没学过“叶平”老师课的同学的学号、姓名； 7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； 8、查询学过“叶平”老师所教的所有课的同学的学号、姓名； 9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； 10、查询有课程成绩小于60分的同学的学号、姓名； 11、查询没有学全所有课的同学的学号、姓名； 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名； 13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名； 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名； 15、删除学习“叶平”老师课的SC表记录； 16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； 17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分； 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分； 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序； 20、课程平均分从高到低显示（现实任课老师）； 21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 22、查询每门课程被选修的学生数； 23、查询出只选修了一门课程的全部学生的学号和姓名； 24、查询男生、女生的人数； 25、查询姓“张”的学生名单； 26、查询同名同姓学生名单，并统计同名人数； 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列； 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩； 29、查询课程名称为“数学”，且分数低于60的学生姓名和分数； 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 31、求选了课程的学生人数 32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩； 33、查询各个课程及相应的选修人数； 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩； 35、查询每门课程成绩最好的前两名； 36、检索至少选修两门课程的学生学号； 37、查询全部学生都选修的课程的课程号和课程名； 38、查询没学过“叶平”老师讲授的任一门课程的学生姓名； 39、查询两门以上不及格课程的同学的学号及其平均成绩； 40、检索“004”课程分数小于60，按分数降序排列的同学学号； 41、删除“002”同学的“001”课程的成绩；
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;
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
先查找每个学生的生物成绩：
select student_id,num from score left join course on score.course_id=course.cid where course.cname='生物'；
再查找每个学生的物理成绩：
select student_id,num from score left join course on score.course_id=course.cid where course.cname='物理';
把两个成绩表进行合并：
select * from
(select student_id,num from score left join course on score.course_id=course.cid where course.cname='生物') as A
left join
(select student_id,num from score left join course on score.course_id=course.cid where course.cname='物理') as B
on A.student_id = B.student_id;
最后合并：
select A.student_id,A.num,B.num from
(select student_id,num from score left join course on score.course_id=course.cid where course.cname='生物') as A
left join
(select student_id,num from score left join course on score.course_id=course.cid where course.cname='物理') as B
on A.student_id = B.student_id
where A.num>B.num;
3、查询平均成绩大于60分的同学的学号和平均成绩；
先查看每个同学的平均分数：
select student_id,avg(num) from score group by student_id;
再筛选成绩大于60分的同学的学号和平均成绩；
select student_id,avg(num) as anum from score group by student_id having anum > 60;
4、查询所有同学的学号、姓名、选课数、总成绩；
先查看每个同学的总成绩：
select student_id,sum(num) from score group by student_id;
学生和课程的关系只有成绩表中存在，因此要获取每个学生选择的课程，需要通过score表：
select count(sid),student_id from score group by student_id;
合并上面的两步：
select sum(num),count(sid),student_id from score group by student_id;
将学生的信息和成绩选课情况拼在一起：
select score.student_id,student.sname,count(score.student_id),sum(score.num)
from score left join student on score.student_id = student.sid
group by score.student_id;
5、查询姓“李”的老师的个数；
select count(tid) as cou_tid from teacher where tname like '李%';
6、查询没学过“李平老师”课的同学的学号、姓名；
找到李平老师的id：
select tid from teacher where tname='李平老师';
找到所有学习这门课的学生id：
select student_id from score where course_id in
(select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));
找到没有学过这门课的学生对应的学生学号、姓名：
select sid,sname from student where sid not in
(select student_id from score where course_id in
(select cid from course where teacher_id=(select tid from teacher where tname='李平老师')));
7、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
首先找出学过课程1的学生学号：
select * from score where course_id = 1；
找出学过课程2的学生学号：
select * from score where course_id =2;
把这两张表按照学生的id 内连接起来 去掉只学习某一门课程的学生：
select t1.student_id from
(select student_id from score where course_id = 1) t1
inner join
(select student_id from score where course_id = 2) t2
on t1.student_id = t2.student_id；
根据学号在学生表中找到对应的姓名：
select t1.student_id,student.sname from (select * from score where course_id = 1) as t1
inner join
(select * from score where course_id =2) as t2
on t1.student_id = t2.student_id
left join
student on t1.student_id = student.sid;
8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
首先查询课程2的所有学生的成绩：
select student_id,num from course left join score on cid = course_id where course_id = 2;
然后查询课程1的所有学生的成绩：
select student_id,num from course left join score on cid = course_id where course_id = 1;
把两个表合并并且查出课程2成绩比课程1成绩低的学生学号：
select * from
(select A.student_id,A.num,B.num1 from
(select student_id,num from course left join score on cid = course_id where course_id = 2) as A
left join
(select student_id,num as num1 from course left join score on cid = course_id where course_id = 1) as B
on A.student_id = B.student_id) as C
where C.num < C.num1;
然后和student表合并：
select student.sid,student.sname from
(select * from
(select A.student_id,A.num,B.num1 from
(select student_id,num from course left join score on cid = course_id where course_id = 2) as A
left join
(select student_id,num as num1 from course left join score on cid = course_id where course_id = 1) as B
on A.student_id = B.student_id) as C
where C.num < C.num1) as D
left join student
on D.student_id=student.sid;
10、查询有课程成绩小于60分的同学的学号、姓名；
先查询成绩小于60分的同学的学号：
select student_id,num from score where num < 60；
再查询有课程成绩小于60分的同学的学号、姓名:
select student.sid,student.sname from (select student_id,num from score where num < 60) as A
left join
student on A.student_id = student.sid;
11、查询没有学全所有课的同学的学号、姓名；
首先查询所有学生选修的课程数
select student_id,count(1) from score group by student_id;
然后查询所有的课程数：
select count(cid) from course;
再进行比较：
select student_id,count(1) from score group by student_id
having count(1) < (select count(cid) from course);
最后和student表合并：
select student.sid,student.sname from
(select student_id,count(1) from score group by student_id
having count(1) < (select count(cid) from course)) as A
left join student on student_id = student.sid;
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
首先查询学号为1的同学学到所有课程号：
select course_id from score where student_id=1;
然后查询其他同学的课程有跟1号同学课程一样的同学学号：
select student_id from score where student_id !=1 and course_id in
(select course_id from score where student_id=1) group by student_id;
最后跟student表合并：
select student.sid,student.sname from
(select student_id from score where student_id != 1
and course_id not in
(select course_id from score where student_id = 1) group by student_id) as A
left join student on student.sid=A.student_id;
13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
首先查询每个课程的最高成绩：
select course_id,max(num) as max_num from score group by course_id;
然后查询每个课程的最低成绩：
select course_id,min(num) as min_num from score group by course_id;
然后合并：
select A.course_id,A.max_num,B.min_num from
(select course_id,max(num) as max_num from score group by course_id) as A
left join
(select course_id,min(num) as min_num from score group by course_id) as B
on A.course_id = B.course_id;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select course_id, avg(num) as avgnum,sum
(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent
from score group by course_id order by avgnum asc,percent desc;
=====================================================================================
select C.course_id,C.avg_num,D.c1,D.c2 from
(select course_id,avg(num) as avg_num from score group by course_id order by avg_num asc) as C
left join
(SELECT A.course_id,A.c1,B.c2 FROM
(select course_id,count(1) as c1 from score where num > 60 group by course_id) as A
left join
(select course_id,count(1) as c2 from score where num < 60 group by course_id) as B
on a.course_id = b.course_id) as D
on C.course_id = D.course_id
20、课程平均分从高到低显示（现实任课老师）；
select B.avg_num,C.tname from
(select course_id,avg(num) as avg_num from score group by course_id order by avg_num desc) as B
left join
((select * from (select * from teacher left join course on tid=teacher_id) as A
left join score on A.cid = score.course_id group by A.cid)) as C
on B.course_id = C.tid group by B.course_id order by B.avg_num desc
================================================================
select avg(if(isnull(score.num),0,score.num)) as A,teacher.tname from course
left join score on course.cid = score.course_id
left join teacher on course.teacher_id = teacher.tid
group by score.course_id order by A desc
22、查询每门课程被选修的学生数；
select course_id,count(1) from score group by course_id;
23、查询出只选修了一门课程的全部学生的学号和姓名；
首先查询只选修了一门课的学生ID：
select student_id,count(1) as cou from score group by student_id having cou =1
然后合并student表：
select student.sid,student.sname from
(select student_id,count(1) as cou from score group by student_id having cou =1) as A
left join student on A.student_id=student.sid
24、查询男生、女生的人数；
首先分别查询男生、女生的个数：
select sid,count(1) as c1 from student where gender = '男';
select sid,count(1) as c2 from student where gender = '女';
然后再用union进行上下连接表格：
select A.sid,A.c1 from
(select sid,count(1) as c1 from student where gender = '男') as A
union
select B.sid,B.c2 from
(select sid,count(1) as c2 from student where gender = '女') as B
25、查询姓“张”的学生名单；
select * from student where sname like '张%';
26、查询同名同姓学生名单，并统计同名人数；
select sid,sname,count(1) as con from student group by sname;
27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select course_id,avg(if(isnull(num),0,num)) as avg_num from score group by course_id
order by avg_num asc,avg_num asc;
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
首先查询平均成绩大于85的所有同学：
select student_id,avg(num) as avg_num from score group by student_id having avg_num > 85;
然后和student表进行合并：
select student.sid,student.sname,A.avg_num from
(select student_id,avg(num) as avg_num from score group by student_id having avg_num > 85) as A
left join student on A.student_id = student.sid;
29、查询课程名称为“生物”，且分数低于60的学生姓名和分数；
首先查询生物课程的课程ID：
select cid from course where cname = '生物';
然后查询成绩低于60的学生学号和成绩：
select student_id,score.num from
(select cid from course where cname = '生物') as A left join score on A.cid = score.course_id
where score.num < 60;
再和student表进行合并：
select student.sname,B.num from
(select student_id,score.num from
(select cid from course where cname = '生物') as A left join score on A.cid = score.course_id
where score.num < 60) as B
left join student on B.student_id = student.sid;
Python操作MySQL
下载安装
pip3 install pymysql -i https://pypi.douban.com/simple
使用操作
import pymysql # 创建连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='t1') # 创建游标 cursor = conn.cursor() # 执行SQL，并返回收影响行数 effect_row = cursor.execute("update hosts set host = '1.1.1.2'") # 执行SQL，并返回受影响行数 #effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,)) # 执行SQL，并返回受影响行数 #effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) # 提交，不然无法保存新建或者修改的数据 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close()
import pymysql
user = input("username:")
pwd = input("password:")
conn = pymysql.connect(host="localhost",user='root',password='',database="db1")
cursor = conn.cursor()
sql = "select * from userinfo where username=%s and password=%s"
cursor.execute(sql,(user,pwd))
# cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{'u':user,'p':pwd})
result = cursor.fetchone()
cursor.close()
conn.close()
if result:
print('登录成功')
else:
print('登录失败')
插入数据
import pymysql
conn = pymysql.connect(host='localhost',user = 'root',password='',database='db1')
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('jinboss','123')"
r = cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
获取查询数据
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts")
# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
获取新创建数据自增ID
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()
# 获取最新自增ID
new_id = cursor.lastrowid
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
fetch数据类型
关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='t1')
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")
result = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。
1、创建视图
--格式：create view 视图名称 as SQL语句;
create view v1 as select * from A where nid > 3;
PS：虚拟
2、修改视图
--格式：alter view 视图名称 as SQL语句;
alter view v1 as select A.nid,B.name from A left join B on A.id = B.id
left join C on A.id = C.id where A.id > 2 and C.nid < 5;
3、删除视图
drop view 视图名称;
drop view v1;
4、使用视图
使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。
select * from v1;
触发器
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。
delimiter // create trigger t1 before insert on student for each row begin insert into teacher(tname) values(new.sname); end // delimiter; insert into student(gender,class_id,sname) values('女',1,'小明'),('男',1,'小花')；
-- new，代指新数据
-- old，代指老数据
函数
MySQL提供了很多内置函数，例如：
CHAR_LENGTH(str) 返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。 对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 CONCAT(str1,str2,...) 字符串拼接 如有任何一个参数为NULL ，则返回值为 NULL。 CONCAT_WS(separator,str1,str2,...) 字符串拼接（自定义连接符） CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。 CONV(N,from_base,to_base) 进制转换 例如： SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示 FORMAT(X,D) 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。 例如： SELECT FORMAT(12332.1,4); 结果为： '12,332.1000' INSERT(str,pos,len,newstr) 在str的指定位置插入字符串 pos：要替换位置其实位置 len：替换的长度 newstr：新字符串 特别的： 如果pos超过原字符串长度，则返回原字符串 如果len超过原字符串长度，则由新字符串完全替换 INSTR(str,substr) 返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len) 返回字符串str 从开始的len位置的子序列字符。 LOWER(str) 变小写 UPPER(str) 变大写 LTRIM(str) 返回字符串 str ，其引导空格字符被删除。 RTRIM(str) 返回字符串 str ，结尾空格字符被删去。 SUBSTRING(str,pos,len) 获取字符串子序列 LOCATE(substr,str,pos) 获取子序列索引位置 REPEAT(str,count) 返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。 若 count <= 0,则返回一个空字符串。 若str 或 count 为 NULL，则返回 NULL 。 REPLACE(str,from_str,to_str) 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 REVERSE(str) 返回字符串 str ，顺序和字符顺序相反。 RIGHT(str,len) 从字符串str 开始，返回从后边开始len个字符组成的子序列 SPACE(N) 返回一个由N空格组成的字符串。 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。 mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx' 部分内置函数
select ctime,count(1) from blod group ctime
select date_format(ctime,"%Y-%m"),count(1) from blog group date_format(ctime,"%Y-%m");
2018-11-1 1
2018-11-2 1 ------> 2018-11 2
2018-10-1 1 ------> 2018-10 2
2018-10-2 1
自定义函数（有返回值）：
delimiter \
create function fa(
i1 int,
i2 int)
return int
begin
declare num int default 0;
set num = i1 + i2;
return(num);
end \
delimiter;
执行函数：
select f1(1,100);
获取返回值：
declare @i varchar(32);
select upper('alex') into @i;
select @i;
在查询中使用：
select f1(l1,nid),name from tb2;
存储过程
保存在MySQL上的一个别名 => 一坨SQL语句 别名（） 用于替代程序员写SQL语句 方式一： MySQL:存储过程 程序：调用存储过程 方式二： MySQL：不执行 程序：写SQL语句 方式三： MySQL:不执行 程序员：类和对象（SQL语句）
1、简单
-- 创建存储过程：
delimiter \ create procedure p1() begin select * from student; insert into teacher(tname) values("ct"); end \
delimiter ;
-- 执行存储过程：
call p1()
pymysql: cursor.callproc('p1')
2、传参数（in,out,inout）
delimiter //
create procedure p1(
in n1 int,
in n2 int
)
begin
select * from student where sid > n1;
end //
delimiter ;
call p2(12,2)
pymysql: cursor.callproc('p2',(12,2))
3、参数 out
delimiter //
create procedure p3(
in n1 int,
inout n2 int
)
begin
set n2 = 123;
select * from student where sid > n1;
end //
delimiter ;
set @ v1 = 10;
call p2(12,@v1)
selecr @v1;
set @_p3_0 =12
set @-p3_1 =2
call p3(@-p3_0,@-p3_1)
select @_p3_0,@_p3_1
pymysql: cursor.callproc('p3',(12,2))
r1 = cursor.fetchall()
print(r1)
cursor.execute('select @_p3_0,@_p3_1')
r2 = cursor.fetchall()
print(r2)
======> 特殊
可传参：in out inout
cursor.callproc('p3',(12,2))
1 = cursor.fetchall()
print(r1)
cursor.execute('select @_p3_0,@_p3_1')
r2 = cursor.fetchall()
print(r2)
为什么有结果集又有out伪造的返回值？
delimiter //
create procedure p3(
in n1 int,
out n2 int 用于标识存储过程的执行结果 1,2
)
begin
insert into vv(..)
insert into vv(..)
insert into vv(..)
end //
delimiter ;
4、事务
delimiter //
create procedure p4(
out status int
)
begin
1. 声明如果出现异常则执行{
set status = 1;
rollback;
}
开始事务
-- 由秦兵账户减去100
-- 方少伟账户加90
-- 张根账户加10
commit;
结束
set status = 2;
end //
delimiter;
==========================================
delimiter \
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
START TRANSACTION;
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT;
-- SUCCESS
set p_return_code = 2;
END\
delimiter ;
5、游标
delimiter //
create procedure p6()
begin
declare row_id int; -- 自定义变量1
declare row_num int; -- 自定义变量2
declare done INT DEFAULT FALSE;
declare temp int;
declare my_cursor CURSOR FOR select id,num from A;
declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open my_cursor;
xxoo: LOOP
fetch my_cursor into row_id,row_num;
if done then
leave xxoo;
END IF;
set temp = row_id + row_num;
insert into B(number) values(temp);
end loop xxoo;
close my_cursor;
end //
delimter ;
6、动态执行SQL（防SQL注入）
delimiter //
create procedure p7(
in tpl varchar(255),
in arg int
)
begin
1. 预检测某个东西 SQL语句合法性
2. SQL =格式化 tpl + arg
3. 执行SQL语句
set @xo = arg;
PREPARE xxx FROM 'select * from student where sid > ?';
EXECUTE xxx USING @xo;
DEALLOCATE prepare prod;
end //
delimter ;
call p7("select * from tb where id > ?",9)
=======>
delimiter \
CREATE PROCEDURE p8 (
in nid int
)
BEGIN
set @nid = nid;
PREPARE prod FROM 'select * from student where sid > ?';
EXECUTE prod USING @nid;
DEALLOCATE prepare prod;
END\
delimiter ;
索引
索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构，类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。
插入10000条数据测试：
import pymysql i = 1 while True: name = 'alex'+str(i) email = 'alex%s@qq.com'%i if i%2==1: gender = '男' else: gender = '女' conn = pymysql.connect(host='localhost',user = 'root',password='',database='db2') cursor = conn.cursor() sql = "insert into userinfo(name,email,gender) values(%s,%s,%s)" r = cursor.executemany(sql,[(name,email,gender)]) conn.commit() i+=1 if i==10001:break cursor.close() conn.close()
作用： -- 约束 -- 加速查找 索引： -- 主键索引：加速查找 + 不能为空 + 不能重复 -- 普通索引：加速查找 -- 唯一索引：加速查找 + 不能重复 -- 联合索引（多列）： -- 联合主键索引 -- 联合唯一索引 -- 联合普通索引 -- 无索引：从前到后依次查找
索引种类（某种格式存储）：
hash索引：
单值快，范围值慢
btree索引：二叉树
建立索引：
-- 额外的文件保存特殊的数据结构
-- 查询快；插入更新和删除慢
-- 命中索引
select * from userinfo3 where email='alex';
select * from userinfo3 where email like 'alex'; -- 慢
主键索引
创建表和创建主键：
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) ==================================== create table in1( nid int not null auto_increment, name varchar(32) not null, email varchar(64) not null, extra text, primary key(ni1), index ix_name (name) )
创建主键：
alter table 表名 add primary key(列名);
删除主键：
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
普通索引：
创建表和索引： create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) 创建索引： — create index 索引名称 on 表名（列名） create index index_name on table_name(column_name) 查看索引： show index from table_name; 删除索引； -- drop index 索引名称 on 表名 show index from table_name;
唯一索引
创建表和唯一索引： create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, unique ix_name (name) ) 创建唯一索引： — create unique index 索引名称 on 表名（列名） 删除唯一索引： — drop unique index 索引名称 on 表名
组合索引（最左前缀匹配）
创建表： create table in3( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text ) 创建组合索引： — create unique index 索引名称 on 表名（列名，列名） create index ix_name_email on in3(name,email); — 最左前缀匹配： select * from userinfo3 where name = 'alex'; select * from userinfo3 where name = 'alex' and email = 'asdf'; 组合索引效率 > 索引合并 组合索引 — （name，email） select * from userinfo3 where name='alex' and email='asdf'; select * from userinfo3 where name='alex';
索引合并 — name，— email select * from userinfo3 where name='alex' and email='asdf'; select * from userinfo3 where name='alex'; select * from userinfo3 where email='alex'; 名词： 覆盖索引： — 在索引文件中直接获取数据 索引合并： — 把多个单列索引合并使用
频繁查找的列创建索引
— 创建索引
— 命中索引 ******
正确使用索引：
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效：
—— like '%xx'
select * from tb1 where name like '%cn';
—— 使用函数
select * from tb1 where reverse(name) = 'wupeiqi';
—— or
select * from tb1 where nid = 1 or email = 'seven@live.com';
特别的：当or条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
—— 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where name = 999;
—— !=
select * from tb1 where name != 'alex'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
—— >
select * from tb1 where name > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
—— order by
select email from tb1 order by name desc;
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
—— 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
时间
执行计划：让mysql预估执行操作（一般正确） all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const 慢： select * from userinfo3 where name = 'alex' explain select * from userinfo3 where name = 'alex' type：ALL(全表扫描) select * from userinfo3 limit1；（快） 快： select * from userinfo3 where email = 'alex' type：const（走索引）
执行计划
explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化
id 查询顺序标识 如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B; +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL | | 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ 特别的：如果使用union连接气值可能为null select_type 查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果 ... table 正在访问的表名 type 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const ALL 全表扫描，对于数据表从头到尾找一遍 select * from tb1; 特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from tb1 where email = 'seven@live.com' select * from tb1 where email = 'seven@live.com' limit 1; 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 INDEX 全索引扫描，对索引从头到尾找一遍 select nid from tb1; RANGE 对索引列进行范围查找 select * from tb1 where name < 'alex'; PS: between and in > >= < <= 操作 注意：!= 和 > 符号 INDEX_MERGE 合并索引，使用多个单列索引搜索 select * from tb1 where name = 'alex' or nid in (11,22,33); REF 根据索引查找一个或多个值 select * from tb1 where name = 'seven'; EQ_REF 连接时使用primary key 或 unique类型 select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid; CONST 常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select nid from tb1 where nid = 1) as A; possible_keys 可能使用的索引 key 真实使用的 key_len MySQL中使用索引字节长度 rows mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值 extra 该列包含MySQL解决查询的详细信息 “Using index” 此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 “Using temporary” 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。 “Range checked for each record(index map: N)” 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
DBA工作
慢日志 - 执行时间 > 10 - 未命中索引 - 日志文件路径 配置： - 内存 show variables like '%query%' set global 变量名 = 值 - 配置文件 mysqld --defaults-file='E:wupeiqimysql-5.7.16-winx64mysql-5.7.16-winx64my-default.ini' my.conf内容： slow_query_log = ON slow_query_log_file = D:/.... 注意：修改配置文件之后，需要重启服务
****** 分页 *******
select * from userinfo3 limit 20,10; 面试问： — 网页中只显示有限个数据 — 索引表中扫描 select * from userinfo3 where id in(select id from userinfo3 limit 200000,10);
— id不连续，所以无法直接使用id范围进行查找
— 方案： 记录当前页最大或最小ID
1、页面只有上一页，下一页
max_id
min_id
上一页：
select * from userinfo3 where id > max_id limit 10;
select * from tb1 where
nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc
limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1)
order by nid desc limit 10;
========================================================================
select * from tb1 where
nid < (select nid from (select nid from tb1 where nid < 970 order by nid desc
limit 40) A order by A.nid asc limit 1)
order by nid desc limit 10;
下一页;
select * from userinfo3 where id < min_id order by id desc linit 10;
select * from tb1 where
nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc
limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1)
order by nid desc limit 10;
=================================================================
select * from tb1 where
nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc
limit 20) A order by A.nid desc limit 1)
2、上一页 192 193 194 195 [196] 197 198 199 下一页：
select * from userinfo where id in
(select id from (select id from userinfo3 where id > max_id limit 30)
as N order by N.id desc limit 10)
类和面向对象回顾
1、函数编程：数据和逻辑分离 a = 123 b = 456 c = 789 def exc3(proc_name): callproc(xxx) return xxx def exc4(proc_name): callproc(xxx) return xxx 2、面向对象：数据和逻辑（属性和行为）组合在一起 class SqlHelper: self.host = '' self.port = '' self.db = '' def exc1(self,SQL): # 连接 conn(self.host,) execute('inser') return xx def exc2(self,proc_name): callproc(xxx) return xxx 一类事物共同具有：属性和行为 class Person: def __init__(self,name): self.name = name def speak(self): pass 1、提取共性 2、分类 3、模板"约束" 4、当一类函数公用同样参数时候，可以转变成类进行 — 分类 面向对象：数据和逻辑（属性和行为）组合在一起 函数编程：数据和逻辑分离 3、分类示例： 类 = 表 对象 = 行 class Userinfo: def __init__(self,id,name): """'约束'每个对象中只有两个字段，即：每个行数据都有id和name列""" self.id = id self.name = name def add(self,name): pass # row1 = Userinfo(1,'alex') # 第一行 # row2 = Userinfo(2,'alex') # 第二行 特殊方法：(比较重要的几个“双下方法”) class Foo: def __init__(self,name): self.name = name def show(self): print (self.name) def __call__(self): pass def __getitem__(self,key): pass def __setitem__(self,key,value): pass def __delitem__(self,key): pass obj1 = Foo('erio') obj1() obj1['k'] obj1['k'] = 123 del obj1[k] obj.__dict__
ORM框架：SQLAlchemy
— 作用：
1、提供简单的规则
2、自动转换成SQL语句
— DB first：手动创建数据库以及表 —> ORM框架 —>自动生成类
— code first：手动创建类和数据库 —> ORM框架 —>自动生成表
— 功能
— 创建数据库表
— 连接数据库（非SQLAlchemyl连接，是由pysql，mysqldb，等连接）
— 操作数据行
增
删
改
查
创建表
创建单表
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy import create_engine engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/db5',max_overflow=5) Base = declarative_base() #创建单表 class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True) name = Column(String(32)) extra = Column(String(16)) __table_args__ = ( UniqueConstraint('id','name',name='uix_id_name'), Index('ix_id_name','name','extra') ) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) Session = sessionmaker(bind=engine) session = Session() # 插入数据 obj = User(name='alex1',extra='钻石玩家') session.add(obj) session.add_all([ User(name='alex2',extra='铂金玩家'), User(name='alex3',extra='黑金玩家') ]) session.commit()
一对多
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy import create_engine engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/db5',max_overflow=5) Base = declarative_base() #一对多 class Favors(Base): __tablename__ = 'favors' nid = Column(Integer,primary_key=True) caption = Column(String(50),default='red',unique=True) class Persons(Base): __tablename__ = 'persons' nid = Column(Integer,primary_key=True) name = Column(String(32),index=True,nullable=True) favors_id = Column(Integer,ForeignKey('favors.nid')) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) Session = sessionmaker(bind=engine) session = Session() #插入数据 obj = Favors(caption='普通用户') session.add(obj) session.add_all([ Favors(caption='铂金用户'), Favors(caption='黑金用户') ]) session.commit()
多对多
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy import create_engine engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/db5',max_overflow=5) Base = declarative_base() #多对多 class Groups(Base): __tablename__ = 'groups' id = Column(Integer,primary_key=True) name = Column(String(64),unique=True,nullable=False) port = Column(Integer,default=22) class Servers(Base): __tablename__ = 'servers' id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(32),unique=True,nullable=False) class ServersToGroups(Base): __tablename__ = 'serverstogroups' nid = Column(Integer,primary_key=True,autoincrement=True) servers_id = Column(Integer,ForeignKey('servers.id')) group_id = Column(Integer,ForeignKey('groups.id')) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) Session = sessionmaker(bind=engine) session = Session() # 插入数据 obj = Servers(hostname='少司命') session.add(obj) session.add_all([ Servers(hostname='项羽'), Servers(hostname='高月') ]) session.commit()
查
# print(session.query(UserType)) # user_type_list = session.query(UserType).all() # for row in user_type_list: # print(row.id,row.title) # select xxx UserType where # user_type_list = session.query(UserType.id,UserType.title).filter(UserType.id > 2) # for row in user_type_list: # print(row.id,row.title)
删除
session.query(UserType.id,UserType.title).filter(UserType.id > 2).delete()
修改
# session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({"title" : "黑金"}) # session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({UserType.title: UserType.title + "x"}, synchronize_session=False) # session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({"num": Users.num + 1}, synchronize_session="evaluate")
其他
# 分组，排序，连表，通配符，子查询，limit，union,where,原生SQL、 # ret = session.query(Users, UserType) # select * from user,usertype; # # ret = session.query(Users, UserType).filter(Users.usertype_id==UserType.id) # select * from user,usertype whre user.usertype_id = usertype.id # result = session.query(Users).join(UserType) # print(result) # result = session.query(Users).join(UserType,isouter=True) # print(result) # 1. # select * from b where id in (select id from tb2) # 2 select * from (select * from tb) as B # q1 = session.query(UserType).filter(UserType.id > 0).subquery() # result = session.query(q1).all() # print(result) # 3 # select # id , # (select * from users where users.user_type_id=usertype.id) # from usertype; # session.query(UserType,session.query(Users).filter(Users.id == 1).subquery()) # session.query(UserType,Users) # result = session.query(UserType.id,session.query(Users).as_scalar()) # print(result) # result = session.query(UserType.id,session.query(Users).filter(Users.user_type_id==UserType.id).as_scalar()) # print(result) # 问题1. 获取用户信息以及与其关联的用户类型名称(FK,Relationship=>正向操作) # user_list = session.query(Users,UserType).join(UserType,isouter=True) # print(user_list) # for row in user_list: # print(row[0].id,row[0].name,row[0].email,row[0].user_type_id,row[1].title) # user_list = session.query(Users.name,UserType.title).join(UserType,isouter=True).all() # for row in user_list: # print(row[0],row[1],row.name,row.title) # user_list = session.query(Users) # for row in user_list: # print(row.name,row.id,row.user_type.title) # 问题2. 获取用户类型 # type_list = session.query(UserType) # for row in type_list: # print(row.id,row.title,session.query(Users).filter(Users.user_type_id == row.id).all()) # type_list = session.query(UserType) # for row in type_list: # print(row.id,row.title,row.xxoo)
参考：https://www.cnblogs.com/wupeiqi/articles/5713330.html