mysql
1.数据库管理系统（DBMS）：
是一种操纵和管理数据库的大型软件，用于建立，使用和维护数据库，简称：DBMS
1、关系型数据库和非关系型数据库的区别：
关系型数据库：关系型数据库通过外键来建立表与表之间的关系
非关系型数据库：非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定
2.mysql数据库
0.linux下mysql 的安装和简单实用
安装：yum insatll mysql-server
启动服务端：mysql.server start
客户端连接：mysql -h host -u user -p
退出：QUIT 或者 control + D
1.数据库： 相当于文件夹
2.数据表： 相当于文件
3.数据行： 相当于文件中的某一行数据
3.操作数据库的常用命令
0.创建数据库： create database (数据库名称) default charset utf8 collate utf8_general_ci;
1.show databases; 查看当前的mysql中有哪些数据库
2.use 数据库； 打开某一个数据库
3. show tables; 查看某一数据库中有哪些数据表
4.select * from (数据表)； 查看数据表中的所有数据
5.insert into (数据表)（数据列名...） values（对应数据列的值）； 往数据表中插入数据
6.create table (数据表)（数据列 数据类型....); 在数据库中创建新的数据流
7.desc (数据表)； 查看数据表中的所有列
8.查看数据库的存放的物理位置： show global variables like "%datadir%";
9.删除数据表： drop table (数据表)；
10.清空数据表：delete from (数据表)；
11.清空数据表：truncate table (数据表)；
12.添加列：alter table （数据表）add （列名， 类型）
13.删除列： alter table （数据表）drop column（列名）
14.修改列：alter table （数据表） modify column（列名， 类型）
15.修改列：alter table （数据表）change （原列名， 新列明， 类型）
16.添加主键：alter table (数据表) add primary key(列名)
17.删除主键：alter table (数据表) drop primary key
18.删除主键：alter table (数据表) modify （列名） int ,drop primary key
19.修改默认值： alter table (数据表) alter (列名) set default（新的默认值）
20.删除默认值： alter table (数据表) alter (列名) drop default
21.修改数据列：update (数据表) set (数据列)=... where (条件)
22.删除数据列： delete from （数据表） where (条件)
23.删除某一行数据： delete from （数据表） where （条件）
其他重要命令操作：
条件： where （条件）， <=, >=, != , >, < ,in , not in ,and, or
分页：
select * from （数据表）limit (开始行)，（总共显示的数据行）
select * from （数据表）limit (总共显示的数据行) offset （开始行）
通配符：
在模糊搜索数据的时候可以使用
‘%’ 可以匹配多个字符
‘_’ 只能匹配一个字符
排序：
正序输出; select * from (数据表) order by (数据列) asc
倒叙输出： select * from（数据表）order by（数据列）desc
组合：
将两个数据表中的数据，组合在一起然后输出
select * from (数据表) union select * from （另一个数据表） 注意：这条命令是有自动去重功能的
select * from （数据表） union all select * from （另一个数据表） 注意： 这条命令是全部输出，没有去重功能
连表：
将具有外键的数据表按照约束关系对应输出，也就是两个有约束关系的数据表
select * from (数据表)，（另一个数据表） where 数据表.数据列=数据表.数据列
select * from （数据表）left join （另一个数据表） on 数据表.数据列=数据表.数据列
创建数据表： create table （数据表名称)(数据列，数据类型.....) ENGINE=InnoDB DEFULT charset = utf8；
创建数据表的时候需要注意：
1.默认值
2，可以设置数据列是否为null
3.自增列（必须是数字类型的，必须是索引---主键）（auto_increment primary key）
主键索引：
1.一张表只能有一个主键
2.不能重复
3.不能为null
创建数据表实例：
外键：（foreign key）
添加外键：alter table (数据表) add constraint **_*_*(外键的名称) foreign key 数据表（对应字段） references 数据表（对应字段）
删除外键：alter table （数据表）drop foreign key (外键名称)；
添加外键实例：
首先创建两个数据表，然后再建立约束
4.启动服务器的方法：
1.将mysql固定到Windows服务中后，执行net start mysql
2.cd 到 mysql的目录中，
3.mysql.server start
4.linux /etc/init.d/mysql start
5.连接客户端的方法：
1.mysql路径 -u (用户名) -p
6.授权
1. 用户管理的特殊命令
创建用户： create user '用户名'@'ip地址' identified by '密码'；
删除用户： drop user '用户名'@‘IP地址’；
修改用户： rename user ‘用户名’@ ‘IP地址’； to ‘新用户名’@‘新ip地址’；；
修改密码： set password for '用户名'@ ‘IP地址’ = password（‘新密码’）；
2.权限管理
一个新的子用户没有任何访问权限
查看权限格式：show grant for '用户名'@ ‘IP地址’
设置权限格式：grant （权限） on （数据库.数据表） 用户名@ip地址
取消权限格式：revoke （权限） on （数据库.数据表） 用户名@ip地址
常见权限：
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用
数据类型：
1.数值
整数：
int(有符号)， unsinged（无符号）
浮点数：
精确的（decimal）
2.字符串
char （定长） 浪费内存，查找速度快
varchar（变长） 节省内存，但是查找的速度会相对的慢一点
3.时间
time (只是显示时间的格式)
data （只是显示日期的格式）
datatime（日期和时间都显示）
4.枚举
enum(只能选其中的一种)
5.集合
set（可以选择其中的多个）
练习： mysql建立外键约束并且连接输出数据表:
mysql练习：
1、自行创建测试数据
/* 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;
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
3、查询平均成绩大于60分的同学的学号和平均成绩和对应的学生姓名；
SELECT student_id,avg(num),student.sname from score LEFT JOIN student on score.student_id = student.sid GROUP BY student_id HAVING avg(num) > 60;
4、查询所有同学的学号、姓名、选课数、总成绩；
select student_id,COUNT(course_id),student.sname,SUM(num) from score LEFT JOIN student on score.student_id = student.sid GROUP BY student_id
5、查询姓“李”的老师的个数；
SELECT tname from teacher WHERE tname like '李%'
6、查询没学过“叶平”老师课的同学的学号、姓名；
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select b.student_id, student.sname from (SELECT e.student_id, count(e.course_id) from (SELECT student_id, course_id from score where course_id =1 or course_id =2) as e GROUP BY student_id HAVING count(e.course_id) = 2) as b LEFT JOIN student ON b.student_id = student.sid
11、查询没有学全所有课的同学的学号、姓名；
select t.student_id, student.sname from (SELECT e.student_id, count(e.course_id) from (select student_id, course_id from score) as e GROUP BY e.student_id having count(e.course_id) < 4) as t LEFT JOIN student on t.student_id = student.sid
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、查询两门以上不及格课程的同学的学号及其平均成绩；
SELECT * from (SELECT e.student_id, student.sname, e.num from (SELECT student_id, count(course_id), num from score WHERE num < 60 GROUP BY student_id having count(course_id) >= 2) as e LEFT JOIN student on e.student_id = student.sid) as a LEFT JOIN (select student_id, avg(num) from score GROUP BY student_id) as T on a.student_id = T.student_id
40、检索“004”课程分数小于60，按分数降序排列的同学学号；
41、删除“002”同学的“001”课程的成绩；
delete from score WHERE student_id = 2 and course_id = 1
pymysql模块的使用
1.安装pymysql
pip install pymysql
pycharm操作mysql的基本流程
使用pymysql模块建立mysql连接对象——》建立游标对象——》对于mysql的操作——》关闭游标对象——》关闭连接对象
2.使用pymysql创建mysql连接并且插入一条数据
注意：在执行mysql命令时千万不能使用字符串拼接的办法来执行命令，会产生sql注入非常的危险
import pymysql # 创建和mysql连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='nishishei', db='test', charset='utf8') # 创建游标 cursor = conn.cursor() # 写出需要执行的sql语句 cursor.execute('insert into student(name, gender, class_id) values("kang", "man", 3)') # 执行语句 conn.commit() # 关闭连接对象 conn.close()
3.插入多条数据
cursor_obj(游标对象).executemany(mysql语句)
实例代码
import pymysql conn_obj = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='nishishei', db='test', charset='utf8') cursor_obj = conn_obj.cursor() info_tuple = ( ('大牛', 1, 3), ('2牛', 1, 3), ('3牛', 1, 3), ('4牛', 1, 3), ) cursor_obj.executemany('insert into userinfo(name, age, part_nid) values(%s, %s, %s)', info_tuple) conn_obj.commit() cursor_obj.close() conn_obj.close()
运行结果
4.查看数据
游标对象.fetchall() 全部查看
游标对象.fetchone() 一个一个按照顺讯查看（内存中的指针永远帮助我们维护拿出的数据的打印顺序）
游标对象.fetchmany(size) 自定义查看行数
实例代码
import pymysql conn_obj = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='nishishei', db='test', charset='utf8') cursor_obj = conn_obj.cursor() res = cursor_obj.execute('show databases') print(res) # 输出受影响的数据行数 result = cursor_obj.fetchall() print(result) # 输出数据内容
5.操作指针
游标对象.scroll(num , mode='relative') # mode参数的值为relative的时候，称为相对当前位置的移动光标
游标对象.scroll(num, mode='absolute') # mode参数的值为absolute的时候，称为相对绝对位置的移动光标
6.获取新建数据行的自增列id
游标对象.lastrowid
实例代码
import pymysql conn_obj = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='nishishei', db='test', charset='utf8') cursor_obj = conn_obj.cursor() cursor_obj.execute('insert into userinfo(name, age, part_nid) values(%s, %s, %s)', ('alibaba', '1', '1')) conn_obj.commit() self_add_id = cursor_obj.lastrowid print(self_add_id) cursor_obj.close() conn_obj.close()
ORM框架：SQLAlchemy
概念：关系对象映射， 将SQL语句操作简单化的一个框架
作用：
1.提供简单的规则
2.自动转换成SQL语句
