Python操作MySQL-day11

# MySQL 5以上使用cmake进行 configure 操作
yum install cmake -y# 创建MySQL用户
useradd -M -s /sbin/nologin mysql# 下载
wget https://cdn.mysql.com//Downloads/MySQL-5.5/mysql-5.5.56.tar.gz# cmake进行配置（安装在/opt/soft/mysql目录）
cmake -DCMAKE_INSTALL_PREFIX=/opt/soft/mysql -DMYSQL_UNIX_ADDR=/opt/soft/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/opt/soft/mysql/data -DMYSQL_USER=mysql -DMYSQL_TCP_PORT=3306# 编译安装
make && make install# 初始化
cd /opt/soft/mysql
./scripts/mysql_install_db --user=mysql --datadir=/opt/soft/mysql/data
chown -R mysql.mysql /opt/soft/mysql/
cp support-files/my-medium.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld
chkconfig mysqld on# 启动数据库
/etc/init.d/mysqld start# 设置root密码
cd /opt/soft/mysql
./bin/mysqladmin -u root password 'new-password'# 检查
ps -ef |grep mysql
lsof -i :3306# 更新环境变量（编辑 /etc/profile）
export PATH=$PATH:/opt/soft/mysql/bin
. /etc/profile# 添加远程登录用户（用于在Windows上使用 Navicat for MySQL）
grant all privileges on *.* to 'admin'@'%' identified by 'xxxxxx' with grant option;
flush privileges;# 修改指定用户的密码
update mysql.user set password=password('新密码') where user="admin" and host="localhost";OK.

int[(m)][unsigned][zerofill] 整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295 特别的：整数类型中的m仅用于显示，对存储范围无限制。
例如： int(5),当插入数据2时，select 时数据显示为： 00002

小数:
float<-浮点型单精度，存储的不精准
double<-浮点型双精度，比float范围更大一些，存储的不精准
decimal<-存储的是精准的FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
有符号：
0
1.175494351E-38 to 3.402823466E+38 **** 数值越大，越不准确 ****DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。 无符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
有符号：
0
2.2250738585072014E-308 to 1.7976931348623157E+308
**** 数值越大，越不准确 ****decimal[(m[,d])] [unsigned] [zerofill]
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。 特别的：对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。

char (m)
char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。
其中m代表字符串的长度。即使数据小于m长度，也会占用m长度；
varchar(m)
varchars数据类型用于变长的字符串，可以包含最多达255个字符。
其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。
因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡；例如：name char(20)，插入一条数据：eric：
定长的 char(20) 会在eric后面补上16个空格；存储20个字符；
不定长的varchar(20) 则只存储eric这4个字符； char效率高，但存储空间占用多
varchar效率低，但节省存储空间

# 查看有哪些数据库
SHOW DATABASES;# 创建数据库（指定字符集为 utf8）
CREATE DATABASE IF NOT EXISTS school DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # utf-8
CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
# gbk
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;# 使用数据库
USE school;# 查看当前数据库的表
SHOW tables;# 删除数据库
drop database demo;# 用户管理
创建用户
create user '用户名'@'IP地址' identified by '密码';
删除用户
drop user '用户名'@'IP地址';
修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
修改密码
set password for '用户名'@'IP地址' = Password('新密码') PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）# 授权管理
show grants for '用户'@'IP地址' -- 查看权限
grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权
revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限# 将数据读取到内存中，从而立即生效。
FLUSH PRIVILEGES;

# MySQL添加额外的超级管理员
GRANT ALL PRIVILEGES ON *.* TO admin'@'localhost' IDENTIFIED BY 'somepass' WITH GRANT OPTION;

# 操作权限
SELECT
INSERT
UPDATE
DELETE
CREATE
DROP
REFERENCES
INDEX
ALTER
CREATE TEMPORARY TABLES
LOCK TABLES
EXECUTE
CREATE VIEW
SHOW VIEW
CREATE ROUTINE
ALTER ROUTINE
EVENT
TRIGGER# 授权范围
数据库名.* 数据库中的所有
数据库名.表 指定数据库中的某张表
数据库名.存储过程 指定数据库中的存储过程
*.* 所有数据库# 用户登录的范围
用户名@IP地址 用户只能在改IP下才能访问
用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意)
用户名@% 用户可以再任意IP下访问(默认IP地址为%)

# 启动免授权服务端
mysqld --skip-grant-tables# 客户端
mysql -u root -p# 修改用户名密码
update mysql.user set authentication_string=password('666') where user='root';
flush privileges;

一点笔记

create table 表名(
列名 类型 是否可以为空，
列名 类型 是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8

# 字段是否可以为空
not null - 不可为空
null - 可以为空# 默认值 - 创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(
nid int not null defalut 2,
num int not null
)# 自增 - 如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）
create table tb1(nid int not null auto_increment primary key,num int null)
或者
create table tb2(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; show global variables like 'auto_inc%';
set global auto_increment_increment=2;
set global auto_increment_offset=10;# 主键 - 一种特殊的唯一索引，不允许有空值；
PS：如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一；create table tb3(nid int not null auto_increment primary key,num int null)
或者
create table tb4(nid int not null,num int not null,primary key(nid,num))# 外键 - 一个特殊的索引，只能是指定内容；
create table color(
cid int not null primary key,
cname char(16) not null
)
create table fruit(
fid int not null primary key,
fname char(32) null,
color_id int not null,
constraint fk_cc FOREIGN KEY (color_id) references color(cid)
)
这样在新插入fruit数据的时候，color_id值就必须在 color表里存在的cid，否则是插不进去的；

drop table 表名

添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
alter table 表名 modify column 列名 类型; -- 类型
alter table 表名 change 原列名 新列名 类型; -- 列名，类型添加主键：
alter table 表名 add primary key(列名);
删除主键：
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

# 初开始的表结构
root@localhost 09:07:59> alter table stu add index index_dept(dept);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:08:21> desc stu;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)root@localhost 09:08:23># alter修改字段
root@localhost 09:08:23> alter table stu change dept dept varchar(200) not null default "TP";
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:10:25> desc stu;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)root@localhost 09:10:28> # alter添加列
root@localhost 09:19:41> alter table stu add column description longtext;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:21:58> desc stu;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)root@localhost 09:22:00> alter table stu add column date datetime;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:22:54> desc stu;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | YES | | NULL | |
| date | datetime | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)root@localhost 09:24:54> alter table stu change date date datetime not null comment '插入时间';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:24:58>
root@localhost 09:24:58> desc stu;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | YES | | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)root@localhost 09:27:22>

delete from 表名
truncate table 表名

# 员工信息表
create table department(
id int not null auto_increment primary key,
title char(32)
)engine=innodb default charset=utf8;# 部门表
create table userinfo(
id int not null auto_increment primary key,
name char(20),
department_id int,
constraint fk_user_depat foreign key (department_id) references department(id)
)engine=innodb default charset=utf8;# 每个部门对应多个员工，每个员工只能属于一个部门；

# 男生表
create table boy(
id int not null auto_increment primary key,
name char(20)
)engine=innodb default charset=utf8;# 女生表
create table girl(
id int not null auto_increment primary key,
name char(20)
)engine=innodb default charset=utf8;# 朋友关系表
create table b2g(
id int not null auto_increment primary key,
bid int,
gid int,
constraint fk1 foreign key (bid) references boy(id),
constraint fk2 foreign key (gid) references girl(id)
)engine=innodb default charset=utf8;INSERT into boy(name) VALUES('standby'),('eric');
INSERT into girl(name) VALUES('alice'),('sherry');
INSERT into b2g(bid,gid) VALUES(1,2),(2,1);# 每个男生可以跟多个女生交朋友，每个女生也可以交多个男朋友；

# 创建数据库
CREATE DATABASE IF NOT EXISTS school DEFAULT CHARSET utf8 COLLATE utf8_general_ci;# 使用数据库
use school;# 创建数据表
create table class(
id int not null auto_increment primary key,
name char(20)
)engine=innodb default charset=utf8;create table teacher(
id int not null auto_increment primary key,
name char(20)
)engine=innodb default charset=utf8;create table student(
id int not null auto_increment primary key,
name char(20),
gender char(1) default '男',
class_id int,
constraint fk_student_class foreign key (class_id) references class(id)
)engine=innodb default charset=utf8;create table course(
id int not null auto_increment primary key,
name char(20),
teacher_id int,
constraint fk_course_teacher foreign key (teacher_id) references teacher(id)
)engine=innodb default charset=utf8;create table score(
id int not null auto_increment primary key,
student_id int,
course_id int,
number int,
constraint fk_score_student foreign key (student_id) references student(id),
constraint fk_score_course foreign key (course_id) references course(id)
)engine=innodb default charset=utf8;

# class
INSERT INTO class(name) VALUES('七年一班');
INSERT INTO class(name) VALUES('七年二班'),('七年三班');# teacher
INSERT INTO teacher(name) VALUES('胡适'),('鲁迅'),('陈渠珍'),('霍达'),('鬼谷子');
INSERT INTO teacher(name) VALUES("陈演恪"),("陈真"),("霍元甲")# student
INSERT into student(name,gender,class_id) VALUES('小明','男',1),('小杰','男',1),('小丽','女',1),('小瑞','女',2),('西原','女',2);
INSERT into student(name,gender,class_id) VALUES('小强','男',2),('小斌','男',3),('egon','男',3),('alex','男',2),('小新','男',3);# course
INSERT into course(name,teacher_id) VALUES('国学',1),('文学',2),('社会学',3),('军事',4);
INSERT into course(name,teacher_id) VALUES('春秋战国历史',5),('深入浅出Linux内核',6),('临床医学',7),('考古学',8);# score
INSERT into score(student_id,course_id,number) VALUES(1,1,80),(2,1,70),(3,1,99),(4,1,88);
INSERT into score(student_id,course_id,number) VALUES(1,2,70),(4,2,73),(5,2,90),(6,2,38);
INSERT into score(student_id,course_id,number) VALUES(2,5,60),(3,5,56),(6,5,67),(7,5,82);
INSERT into score(student_id,course_id,number) VALUES(8,6,67),(9,6,86),(10,6,69),(7,6,42);
INSERT into score(student_id,course_id,number) VALUES(9,7,60),(1,7,56),(3,8,39),(6,8,92);

mysql> select * from score where id = 20;
+----+------------+-----------+--------+
| id | student_id | course_id | number |
+----+------------+-----------+--------+
| 20 | 6 | 8 | 92 |
+----+------------+-----------+--------+
1 row in set (0.00 sec)mysql> delete from score where id = 20;
Query OK, 1 row affected (0.03 sec)mysql> select * from score where id = 20;
Empty set (0.00 sec)mysql>

mysql> insert into score(student_id,course_id,number) values(10,1,99),(5,2,67),(10,3,88),(4,4,100);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select * from score where id > 19;
+----+------------+-----------+--------+
| id | student_id | course_id | number |
+----+------------+-----------+--------+
| 21 | 10 | 1 | 99 |
| 22 | 5 | 2 | 67 |
| 23 | 10 | 3 | 88 |
| 24 | 4 | 4 | 100 |
+----+------------+-----------+--------+
4 rows in set (0.00 sec)mysql> update score set number = 89 where id = 21;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from score where id = 21;
+----+------------+-----------+--------+
| id | student_id | course_id | number |
+----+------------+-----------+--------+
| 21 | 10 | 1 | 89 |
+----+------------+-----------+--------+
1 row in set (0.00 sec)mysql>

mysql> select * from student;
+----+--------+--------+----------+
| id | name | gender | class_id |
+----+--------+--------+----------+
| 1 | 小明 | 男 | 1 |
| 2 | 小杰 | 男 | 1 |
| 3 | 小丽 | 女 | 1 |
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 6 | 小强 | 男 | 2 |
| 7 | 小斌 | 男 | 3 |
| 8 | egon | 男 | 3 |
| 9 | alex | 男 | 2 |
| 10 | 小新 | 男 | 3 |
+----+--------+--------+----------+
10 rows in set (0.00 sec)mysql> update student set name = '李明' where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> update student set name = '张丽' where id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> update student set name = '刘斌' where id = 7;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> update student set name = 'standby' where id = 10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from student;
+----+---------+--------+----------+
| id | name | gender | class_id |
+----+---------+--------+----------+
| 1 | 李明 | 男 | 1 |
| 2 | 小杰 | 男 | 1 |
| 3 | 张丽 | 女 | 1 |
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 6 | 小强 | 男 | 2 |
| 7 | 刘斌 | 男 | 3 |
| 8 | egon | 男 | 3 |
| 9 | alex | 男 | 2 |
| 10 | standby | 男 | 3 |
+----+---------+--------+----------+
10 rows in set (0.00 sec)mysql>

# 修改表字段
mysql> desc admin_info;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_name | varchar(30) | NO | | NULL | |
| user_pass | varchar(100) | NO | | NULL | |
| user_email | varchar(50) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)mysql> alter table admin_info change user_name admin_name varchar(30) not null default 'admin';
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table admin_info change user_pass admin_pass varchar(100) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table admin_info change user_email admin_email varchar(50) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc admin_info;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| admin_name | varchar(30) | NO | | admin | |
| admin_pass | varchar(100) | NO | | NULL | |
| admin_email | varchar(50) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql>

# 给表增加新字段
mysql> desc department_info;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| depart_name | varchar(50) | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)mysql> alter table department_info add description varchar(500);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc department_info;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| depart_name | varchar(50) | NO | | NULL | |
| description | varchar(500) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)mysql>

mysql> select * from student;
+----+---------+--------+----------+
| id | name | gender | class_id |
+----+---------+--------+----------+
| 1 | 李明 | 男 | 1 |
| 2 | 小杰 | 男 | 1 |
| 3 | 张丽 | 女 | 1 |
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 6 | 小强 | 男 | 2 |
| 7 | 刘斌 | 男 | 3 |
| 8 | egon | 男 | 3 |
| 9 | alex | 男 | 2 |
| 10 | standby | 男 | 3 |
+----+---------+--------+----------+
10 rows in set (0.00 sec)mysql> select name,gender from student;
+---------+--------+
| name | gender |
+---------+--------+
| 李明 | 男 |
| 小杰 | 男 |
| 张丽 | 女 |
| 小瑞 | 女 |
| 西原 | 女 |
| 小强 | 男 |
| 刘斌 | 男 |
| egon | 男 |
| alex | 男 |
| standby | 男 |
+---------+--------+
10 rows in set (0.00 sec)mysql> select name,gender from student where gender = '女';
+--------+--------+
| name | gender |
+--------+--------+
| 张丽 | 女 |
| 小瑞 | 女 |
| 西原 | 女 |
+--------+--------+
3 rows in set (0.00 sec)mysql> select name as 姓名,gender as 性别 from student where gender = '女'; -- as 起个别名
+--------+--------+
| 姓名 | 性别 |
+--------+--------+
| 张丽 | 女 |
| 小瑞 | 女 |
| 西原 | 女 |
+--------+--------+
3 rows in set (0.00 sec)mysql> select * from student where id in (4,5,10); -- in 接范围
+----+---------+--------+----------+
| id | name | gender | class_id |
+----+---------+--------+----------+
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 10 | standby | 男 | 3 |
+----+---------+--------+----------+
3 rows in set (0.00 sec)mysql> select * from student where id not in (1,2,3,4,5,6,7,10); -- not in 接范围
+----+------+--------+----------+
| id | name | gender | class_id |
+----+------+--------+----------+
| 8 | egon | 男 | 3 |
| 9 | alex | 男 | 2 |
+----+------+--------+----------+
2 rows in set (0.00 sec)mysql> select * from student where id between 4 and 7; -- between 接范围
+----+--------+--------+----------+
| id | name | gender | class_id |
+----+--------+--------+----------+
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 6 | 小强 | 男 | 2 |
| 7 | 刘斌 | 男 | 3 |
+----+--------+--------+----------+
4 rows in set (0.00 sec)mysql>

mysql> select * from student;
+----+---------+--------+----------+
| id | name | gender | class_id |
+----+---------+--------+----------+
| 1 | 李明 | 男 | 1 |
| 2 | 小杰 | 男 | 1 |
| 3 | 张丽 | 女 | 1 |
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 6 | 小强 | 男 | 2 |
| 7 | 刘斌 | 男 | 3 |
| 8 | egon | 男 | 3 |
| 9 | alex | 男 | 2 |
| 10 | standby | 男 | 3 |
+----+---------+--------+----------+
10 rows in set (0.00 sec)mysql> select * from teacher;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 胡适 |
| 2 | 鲁迅 |
| 3 | 陈渠珍 |
| 4 | 霍达 |
| 5 | 鬼谷子 |
| 6 | 陈演恪 |
| 7 | 陈真 |
| 8 | 霍元甲 |
+----+-----------+
8 rows in set (0.00 sec)mysql> select * from student where id in (select id from teacher); -- 一个语句的输出作为另一个语句的条件输入
+----+--------+--------+----------+
| id | name | gender | class_id |
+----+--------+--------+----------+
| 1 | 李明 | 男 | 1 |
| 2 | 小杰 | 男 | 1 |
| 3 | 张丽 | 女 | 1 |
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 6 | 小强 | 男 | 2 |
| 7 | 刘斌 | 男 | 3 |
| 8 | egon | 男 | 3 |
+----+--------+--------+----------+
8 rows in set (0.00 sec)mysql>

mysql> select * from score;
+----+------------+-----------+--------+
| id | student_id | course_id | number |
+----+------------+-----------+--------+
| 1 | 1 | 1 | 80 |
| 2 | 2 | 1 | 70 |
| 3 | 3 | 1 | 99 |
| 4 | 4 | 1 | 88 |
| 5 | 1 | 2 | 70 |
| 6 | 4 | 2 | 73 |
| 7 | 5 | 2 | 90 |
| 8 | 6 | 2 | 38 |
| 9 | 2 | 5 | 60 |
| 10 | 3 | 5 | 56 |
| 11 | 6 | 5 | 67 |
| 12 | 7 | 5 | 82 |
| 13 | 8 | 6 | 67 |
| 14 | 9 | 6 | 86 |
| 15 | 10 | 6 | 69 |
| 16 | 7 | 6 | 42 |
| 17 | 9 | 7 | 60 |
| 18 | 1 | 7 | 56 |
| 19 | 3 | 8 | 39 |
| 21 | 10 | 1 | 89 |
| 22 | 5 | 2 | 67 |
| 23 | 10 | 3 | 88 |
| 24 | 4 | 4 | 100 |
+----+------------+-----------+--------+
23 rows in set (0.00 sec)mysql> select * from score order by number asc; -- 升序
+----+------------+-----------+--------+
| id | student_id | course_id | number |
+----+------------+-----------+--------+
| 8 | 6 | 2 | 38 |
| 19 | 3 | 8 | 39 |
| 16 | 7 | 6 | 42 |
| 18 | 1 | 7 | 56 |
| 10 | 3 | 5 | 56 |
| 9 | 2 | 5 | 60 |
| 17 | 9 | 7 | 60 |
| 13 | 8 | 6 | 67 |
| 22 | 5 | 2 | 67 |
| 11 | 6 | 5 | 67 |
| 15 | 10 | 6 | 69 |
| 5 | 1 | 2 | 70 |
| 2 | 2 | 1 | 70 |
| 6 | 4 | 2 | 73 |
| 1 | 1 | 1 | 80 |
| 12 | 7 | 5 | 82 |
| 14 | 9 | 6 | 86 |
| 4 | 4 | 1 | 88 |
| 23 | 10 | 3 | 88 |
| 21 | 10 | 1 | 89 |
| 7 | 5 | 2 | 90 |
| 3 | 3 | 1 | 99 |
| 24 | 4 | 4 | 100 |
+----+------------+-----------+--------+
23 rows in set (0.00 sec)mysql> select * from score order by number desc; -- 降序
+----+------------+-----------+--------+
| id | student_id | course_id | number |
+----+------------+-----------+--------+
| 24 | 4 | 4 | 100 |
| 3 | 3 | 1 | 99 |
| 7 | 5 | 2 | 90 |
| 21 | 10 | 1 | 89 |
| 23 | 10 | 3 | 88 |
| 4 | 4 | 1 | 88 |
| 14 | 9 | 6 | 86 |
| 12 | 7 | 5 | 82 |
| 1 | 1 | 1 | 80 |
| 6 | 4 | 2 | 73 |
| 2 | 2 | 1 | 70 |
| 5 | 1 | 2 | 70 |
| 15 | 10 | 6 | 69 |
| 11 | 6 | 5 | 67 |
| 22 | 5 | 2 | 67 |
| 13 | 8 | 6 | 67 |
| 17 | 9 | 7 | 60 |
| 9 | 2 | 5 | 60 |
| 18 | 1 | 7 | 56 |
| 10 | 3 | 5 | 56 |
| 16 | 7 | 6 | 42 |
| 19 | 3 | 8 | 39 |
| 8 | 6 | 2 | 38 |
+----+------------+-----------+--------+
23 rows in set (0.00 sec)mysql> select * from score order by number desc limit 5; -- 取前5个值
+----+------------+-----------+--------+
| id | student_id | course_id | number |
+----+------------+-----------+--------+
| 24 | 4 | 4 | 100 |
| 3 | 3 | 1 | 99 |
| 7 | 5 | 2 | 90 |
| 21 | 10 | 1 | 89 |
| 23 | 10 | 3 | 88 |
+----+------------+-----------+--------+
5 rows in set (0.00 sec)mysql> select * from score order by number asc limit 5;
+----+------------+-----------+--------+
| id | student_id | course_id | number |
+----+------------+-----------+--------+
| 8 | 6 | 2 | 38 |
| 19 | 3 | 8 | 39 |
| 16 | 7 | 6 | 42 |
| 18 | 1 | 7 | 56 |
| 10 | 3 | 5 | 56 |
+----+------------+-----------+--------+
5 rows in set (0.00 sec)mysql> select * from student limit 4;
+----+--------+--------+----------+
| id | name | gender | class_id |
+----+--------+--------+----------+
| 1 | 李明 | 男 | 1 |
| 2 | 小杰 | 男 | 1 |
| 3 | 张丽 | 女 | 1 |
| 4 | 小瑞 | 女 | 2 |
+----+--------+--------+----------+
4 rows in set (0.00 sec)mysql> select * from student limit 3,4; -- 切片，类似于网站分页显示
+----+--------+--------+----------+
| id | name | gender | class_id |
+----+--------+--------+----------+
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 6 | 小强 | 男 | 2 |
| 7 | 刘斌 | 男 | 3 |
+----+--------+--------+----------+
4 rows in set (0.00 sec)mysql>

mysql> select * from teacher;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 胡适 |
| 2 | 鲁迅 |
| 3 | 陈渠珍 |
| 4 | 霍达 |
| 5 | 鬼谷子 |
| 6 | 陈演恪 |
| 7 | 陈真 |
| 8 | 霍元甲 |
+----+-----------+
8 rows in set (0.00 sec)mysql> select * from teacher where name like '陈%'; -- 通配符 %，可匹配一个或多个字符
+----+-----------+
| id | name |
+----+-----------+
| 3 | 陈渠珍 |
| 6 | 陈演恪 |
| 7 | 陈真 |
+----+-----------+
3 rows in set (0.00 sec)mysql> select * from teacher where name like '%谷%';
+----+-----------+
| id | name |
+----+-----------+
| 5 | 鬼谷子 |
+----+-----------+
1 row in set (0.00 sec)mysql> select * from teacher where name like '%真';
+----+--------+
| id | name |
+----+--------+
| 7 | 陈真 |
+----+--------+
1 row in set (0.00 sec)mysql> select * from teacher where name like '霍%';
+----+-----------+
| id | name |
+----+-----------+
| 4 | 霍达 |
| 8 | 霍元甲 |
+----+-----------+
2 rows in set (0.00 sec)mysql> select * from teacher where name like '霍_'; -- 通配符，匹配一个字符
+----+--------+
| id | name |
+----+--------+
| 4 | 霍达 |
+----+--------+
1 row in set (0.00 sec)mysql> select * from teacher where name like '霍__';
+----+-----------+
| id | name |
+----+-----------+
| 8 | 霍元甲 |
+----+-----------+
1 row in set (0.00 sec)mysql>

# 不使用 join 实现的连表mysql> select student.name as 姓名, student.gender as 性别, class.name as 班级 from student,class where student.class_id = class.id;
+---------+--------+--------------+
| 姓名 | 性别 | 班级 |
+---------+--------+--------------+
| 李明 | 男 | 七年一班 |
| 小杰 | 男 | 七年一班 |
| 张丽 | 女 | 七年一班 |
| 小瑞 | 女 | 七年二班 |
| 西原 | 女 | 七年二班 |
| 小强 | 男 | 七年二班 |
| alex | 男 | 七年二班 |
| 刘斌 | 男 | 七年三班 |
| egon | 男 | 七年三班 |
| standby | 男 | 七年三班 |
+---------+--------+--------------+
10 rows in set (0.00 sec)mysql>

join连表操作

# 两张表连起来
mysql> select * from student;
+----+---------+--------+----------+
| id | name | gender | class_id |
+----+---------+--------+----------+
| 1 | 李明 | 男 | 1 |
| 2 | 小杰 | 男 | 1 |
| 3 | 张丽 | 女 | 1 |
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 6 | 小强 | 男 | 2 |
| 7 | 刘斌 | 男 | 3 |
| 8 | egon | 男 | 3 |
| 9 | alex | 男 | 2 |
| 10 | standby | 男 | 3 |
+----+---------+--------+----------+
10 rows in set (0.00 sec)mysql> select * from class;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 七年一班 |
| 2 | 七年二班 |
| 3 | 七年三班 |
+----+--------------+
3 rows in set (0.00 sec)mysql> select * from student left join class on student.class_id = class.id;
+----+---------+--------+----------+------+--------------+
| id | name | gender | class_id | id | name |
+----+---------+--------+----------+------+--------------+
| 1 | 李明 | 男 | 1 | 1 | 七年一班 |
| 2 | 小杰 | 男 | 1 | 1 | 七年一班 |
| 3 | 张丽 | 女 | 1 | 1 | 七年一班 |
| 4 | 小瑞 | 女 | 2 | 2 | 七年二班 |
| 5 | 西原 | 女 | 2 | 2 | 七年二班 |
| 6 | 小强 | 男 | 2 | 2 | 七年二班 |
| 7 | 刘斌 | 男 | 3 | 3 | 七年三班 |
| 8 | egon | 男 | 3 | 3 | 七年三班 |
| 9 | alex | 男 | 2 | 2 | 七年二班 |
| 10 | standby | 男 | 3 | 3 | 七年三班 |
+----+---------+--------+----------+------+--------------+
10 rows in set (0.00 sec)mysql> select student.name as 姓名,student.gender as 性别,class.name as 班级 from student left join class on student.class_id = class.id;
+---------+--------+--------------+
| 姓名 | 性别 | 班级 |
+---------+--------+--------------+
| 李明 | 男 | 七年一班 |
| 小杰 | 男 | 七年一班 |
| 张丽 | 女 | 七年一班 |
| 小瑞 | 女 | 七年二班 |
| 西原 | 女 | 七年二班 |
| 小强 | 男 | 七年二班 |
| 刘斌 | 男 | 七年三班 |
| egon | 男 | 七年三班 |
| alex | 男 | 七年二班 |
| standby | 男 | 七年三班 |
+---------+--------+--------------+
10 rows in set (0.00 sec)mysql>

# 多张表连起来
mysql> select * from student;
+----+---------+--------+----------+
| id | name | gender | class_id |
+----+---------+--------+----------+
| 1 | 李明 | 男 | 1 |
| 2 | 小杰 | 男 | 1 |
| 3 | 张丽 | 女 | 1 |
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 6 | 小强 | 男 | 2 |
| 7 | 刘斌 | 男 | 3 |
| 8 | egon | 男 | 3 |
| 9 | alex | 男 | 2 |
| 10 | standby | 男 | 3 |
+----+---------+--------+----------+
10 rows in set (0.00 sec)mysql> select * from course;
+----+-------------------------+------------+
| id | name | teacher_id |
+----+-------------------------+------------+
| 1 | 国学 | 1 |
| 2 | 文学 | 2 |
| 3 | 社会学 | 3 |
| 4 | 军事 | 4 |
| 5 | 春秋战国历史 | 5 |
| 6 | 深入浅出Linux内核 | 6 |
| 7 | 临床医学 | 7 |
| 8 | 考古学 | 8 |
+----+-------------------------+------------+
8 rows in set (0.00 sec)mysql> select * from score;
+----+------------+-----------+--------+
| id | student_id | course_id | number |
+----+------------+-----------+--------+
| 1 | 1 | 1 | 80 |
| 2 | 2 | 1 | 70 |
| 3 | 3 | 1 | 99 |
| 4 | 4 | 1 | 88 |
| 5 | 1 | 2 | 70 |
| 6 | 4 | 2 | 73 |
| 7 | 5 | 2 | 90 |
| 8 | 6 | 2 | 38 |
| 9 | 2 | 5 | 60 |
| 10 | 3 | 5 | 56 |
| 11 | 6 | 5 | 67 |
| 12 | 7 | 5 | 82 |
| 13 | 8 | 6 | 67 |
| 14 | 9 | 6 | 86 |
| 15 | 10 | 6 | 69 |
| 16 | 7 | 6 | 42 |
| 17 | 9 | 7 | 60 |
| 18 | 1 | 7 | 56 |
| 19 | 3 | 8 | 39 |
| 21 | 10 | 1 | 89 |
| 22 | 5 | 2 | 67 |
| 23 | 10 | 3 | 88 |
| 24 | 4 | 4 | 100 |
+----+------------+-----------+--------+
23 rows in set (0.00 sec)mysql> select student.name as 姓名,student.gender as 性别,course.name as 课程,score.number as 成绩 from score left join student on score.student_id = student.id left join course on score.course_id = course.id;
+---------+--------+-------------------------+--------+
| 姓名 | 性别 | 课程 | 成绩 |
+---------+--------+-------------------------+--------+
| 李明 | 男 | 国学 | 80 |
| 小杰 | 男 | 国学 | 70 |
| 张丽 | 女 | 国学 | 99 |
| 小瑞 | 女 | 国学 | 88 |
| 李明 | 男 | 文学 | 70 |
| 小瑞 | 女 | 文学 | 73 |
| 西原 | 女 | 文学 | 90 |
| 小强 | 男 | 文学 | 38 |
| 小杰 | 男 | 春秋战国历史 | 60 |
| 张丽 | 女 | 春秋战国历史 | 56 |
| 小强 | 男 | 春秋战国历史 | 67 |
| 刘斌 | 男 | 春秋战国历史 | 82 |
| egon | 男 | 深入浅出Linux内核 | 67 |
| alex | 男 | 深入浅出Linux内核 | 86 |
| standby | 男 | 深入浅出Linux内核 | 69 |
| 刘斌 | 男 | 深入浅出Linux内核 | 42 |
| alex | 男 | 临床医学 | 60 |
| 李明 | 男 | 临床医学 | 56 |
| 张丽 | 女 | 考古学 | 39 |
| standby | 男 | 国学 | 89 |
| 西原 | 女 | 文学 | 67 |
| standby | 男 | 社会学 | 88 |
| 小瑞 | 女 | 军事 | 100 |
+---------+--------+-------------------------+--------+
23 rows in set (0.00 sec)mysql>

特别的：group by 必须在where之后，order by之前

# max | min | avg | sum 函数
mysql> select max(number) from score;
+-------------+
| max(number) |
+-------------+
| 100 |
+-------------+
1 row in set (0.00 sec)mysql> select min(number) from score;
+-------------+
| min(number) |
+-------------+
| 38 |
+-------------+
1 row in set (0.00 sec)mysql> select avg(number) from score;
+-------------+
| avg(number) |
+-------------+
| 71.1304 |
+-------------+
1 row in set (0.01 sec)mysql> select sum(number) from score;
+-------------+
| sum(number) |
+-------------+
| 1636 |
+-------------+
1 row in set (0.00 sec)mysql> # group by 集合 count() 函数
mysql> select * from student;
+----+---------+--------+----------+
| id | name | gender | class_id |
+----+---------+--------+----------+
| 1 | 李明 | 男 | 1 |
| 2 | 小杰 | 男 | 1 |
| 3 | 张丽 | 女 | 1 |
| 4 | 小瑞 | 女 | 2 |
| 5 | 西原 | 女 | 2 |
| 6 | 小强 | 男 | 2 |
| 7 | 刘斌 | 男 | 3 |
| 8 | egon | 男 | 3 |
| 9 | alex | 男 | 2 |
| 10 | standby | 男 | 3 |
+----+---------+--------+----------+
10 rows in set (0.00 sec)mysql> select * from student group by class_id; -- 分组显示,会去重,需要使用聚合函数来统计重复的次数；
+----+--------+--------+----------+
| id | name | gender | class_id |
+----+--------+--------+----------+
| 1 | 李明 | 男 | 1 |
| 4 | 小瑞 | 女 | 2 |
| 7 | 刘斌 | 男 | 3 |
+----+--------+--------+----------+
3 rows in set (0.00 sec)mysql> select class_id,count(class_id) from student group by class_id; -- 计算重复的次数
+----------+-----------------+
| class_id | count(class_id) |
+----------+-----------------+
| 1 | 3 |
| 2 | 4 |
| 3 | 3 |
+----------+-----------------+
3 rows in set (0.00 sec)mysql> select number,count(*),max(number),min(number),avg(number) from score group by number;
+--------+----------+-------------+-------------+-------------+
| number | count(*) | max(number) | min(number) | avg(number) |
+--------+----------+-------------+-------------+-------------+
| 38 | 1 | 38 | 38 | 38.0000 |
| 39 | 1 | 39 | 39 | 39.0000 |
| 42 | 1 | 42 | 42 | 42.0000 |
| 56 | 2 | 56 | 56 | 56.0000 |
| 60 | 2 | 60 | 60 | 60.0000 |
| 67 | 3 | 67 | 67 | 67.0000 |
| 69 | 1 | 69 | 69 | 69.0000 |
| 70 | 2 | 70 | 70 | 70.0000 |
| 73 | 1 | 73 | 73 | 73.0000 |
| 80 | 1 | 80 | 80 | 80.0000 |
| 82 | 1 | 82 | 82 | 82.0000 |
| 86 | 1 | 86 | 86 | 86.0000 |
| 88 | 2 | 88 | 88 | 88.0000 |
| 89 | 1 | 89 | 89 | 89.0000 |
| 90 | 1 | 90 | 90 | 90.0000 |
| 99 | 1 | 99 | 99 | 99.0000 |
| 100 | 1 | 100 | 100 | 100.0000 |
+--------+----------+-------------+-------------+-------------+
17 rows in set (0.00 sec)mysql>

# 聚合示例1：计算每个班有多少学生
mysql> select class.name as 班级,count(student.id) as 学生人数 from class left join student on class.id = student.class_id group by class.id;
+--------------+--------------+
| 班级 | 学生人数 |
+--------------+--------------+
| 七年一班 | 3 |
| 七年二班 | 4 |
| 七年三班 | 3 |
+--------------+--------------+
3 rows in set (0.00 sec)mysql># 聚合示例2：获取每个班级有多少人，并且选出学生人数大于3的班级
# 注意：如果针对 group by 的结果进行筛选，那么需要使用 having 不能在使用 where；
mysql> select class.name as 班级,count(student.id) as 学生人数 from class left join student on class.id = student.class_id group by class.id having count(student.id) > 3;
+--------------+--------------+
| 班级 | 学生人数 |
+--------------+--------------+
| 七年二班 | 4 |
+--------------+--------------+
1 row in set (0.00 sec)mysql> select class.name as 班级,count(student.id) as 学生人数 from class left join student on class.id = student.class_id group by class.id having 学生人数 > 3;
+--------------+--------------+
| 班级 | 学生人数 |
+--------------+--------------+
| 七年二班 | 4 |
+--------------+--------------+
1 row in set (0.00 sec)mysql> # 聚合示例3：计算出每个课程的不及格的人数
mysql> select course.name,count(score.id) from course left join score on course.id = score.course_id where score.number < 60 group by course.name;
+-------------------------+-----------------+
| name | count(score.id) |
+-------------------------+-----------------+
| 临床医学 | 1 |
| 文学 | 1 |
| 春秋战国历史 | 1 |
| 深入浅出Linux内核 | 1 |
| 考古学 | 1 |
+-------------------------+-----------------+
5 rows in set (0.00 sec)mysql> # 把考古学那个学生的成绩改为91分
mysql> update score set number=91 where student_id=3 and course_id=8;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select student.name as 姓名,student.gender as 性别,course.name as 课程,score.number as 成绩 from score left join student on score.student_id = student.id left join course on score.course_id = course.id;
+---------+--------+-------------------------+--------+
| 姓名 | 性别 | 课程 | 成绩 |
+---------+--------+-------------------------+--------+
| 李明 | 男 | 国学 | 80 |
| 小杰 | 男 | 国学 | 70 |
| 张丽 | 女 | 国学 | 99 |
| 小瑞 | 女 | 国学 | 88 |
| 李明 | 男 | 文学 | 70 |
| 小瑞 | 女 | 文学 | 73 |
| 西原 | 女 | 文学 | 90 |
| 小强 | 男 | 文学 | 38 |
| 小杰 | 男 | 春秋战国历史 | 60 |
| 张丽 | 女 | 春秋战国历史 | 56 |
| 小强 | 男 | 春秋战国历史 | 67 |
| 刘斌 | 男 | 春秋战国历史 | 82 |
| egon | 男 | 深入浅出Linux内核 | 67 |
| alex | 男 | 深入浅出Linux内核 | 86 |
| standby | 男 | 深入浅出Linux内核 | 69 |
| 刘斌 | 男 | 深入浅出Linux内核 | 42 |
| alex | 男 | 临床医学 | 60 |
| 李明 | 男 | 临床医学 | 56 |
| 张丽 | 女 | 考古学 | 91 |
| standby | 男 | 国学 | 89 |
| 西原 | 女 | 文学 | 67 |
| standby | 男 | 社会学 | 88 |
| 小瑞 | 女 | 军事 | 100 |
+---------+--------+-------------------------+--------+
23 rows in set (0.01 sec)mysql> select course.name,count(score.id) from course left join score on course.id = score.course_id where score.number < 60 group by course.name;
+-------------------------+-----------------+
| name | count(score.id) |
+-------------------------+-----------------+
| 临床医学 | 1 |
| 文学 | 1 |
| 春秋战国历史 | 1 |
| 深入浅出Linux内核 | 1 |
+-------------------------+-----------------+
4 rows in set (0.00 sec)mysql>

把两个SQL的结果进行组合(上下合并)
select * from student
union / union all
select * from teacher;
注意上下两个表中的列数要统一
注意：
1、如果所有数据都一致，那么union会对结果进行去重
2、union all ,会保存所有的

组合，自动处理重合
select nickname
from A
union
select name
from B 组合，不处理重合
select nickname
from A
union all
select name
from B

# UNION 组合示例
mysql> select id,name from student union select id,name from teacher;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 李明 |
| 2 | 小杰 |
| 3 | 张丽 |
| 4 | 小瑞 |
| 5 | 西原 |
| 6 | 小强 |
| 7 | 刘斌 |
| 8 | egon |
| 9 | alex |
| 10 | standby |
| 1 | 胡适 |
| 2 | 鲁迅 |
| 3 | 陈渠珍 |
| 4 | 霍达 |
| 5 | 鬼谷子 |
| 6 | 陈演恪 |
| 7 | 陈真 |
| 8 | 霍元甲 |
+----+-----------+
18 rows in set (0.00 sec)mysql> # UNION all 和 UNION 对比示例
mysql> select * from teacher;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 胡适 |
| 2 | 鲁迅 |
| 3 | 陈渠珍 |
| 4 | 霍达 |
| 5 | 鬼谷子 |
| 6 | 陈演恪 |
| 7 | 陈真 |
| 8 | 霍元甲 |
+----+-----------+
8 rows in set (0.00 sec)mysql> insert into teacher(name) values('egon');
Query OK, 1 row affected (0.02 sec)mysql> select * from teacher;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 胡适 |
| 2 | 鲁迅 |
| 3 | 陈渠珍 |
| 4 | 霍达 |
| 5 | 鬼谷子 |
| 6 | 陈演恪 |
| 7 | 陈真 |
| 8 | 霍元甲 |
| 9 | egon |
+----+-----------+
9 rows in set (0.00 sec)mysql> # UNION all 不去重
mysql> select name from student union all select name from teacher;
+-----------+
| name |
+-----------+
| 李明 |
| 小杰 |
| 张丽 |
| 小瑞 |
| 西原 |
| 小强 |
| 刘斌 |
| egon | -- 学生里的egon
| alex |
| standby |
| 胡适 |
| 鲁迅 |
| 陈渠珍 |
| 霍达 |
| 鬼谷子 |
| 陈演恪 |
| 陈真 |
| 霍元甲 |
| egon | -- 老师里的egon
+-----------+
19 rows in set (0.00 sec)

# UNION 去重
mysql> select name from student union select name from teacher;
+-----------+
| name |
+-----------+
| 李明 |
| 小杰 |
| 张丽 |
| 小瑞 |
| 西原 |
| 小强 |
| 刘斌 |
| egon | -- 只保留一个egon
| alex |
| standby |
| 胡适 |
| 鲁迅 |
| 陈渠珍 |
| 霍达 |
| 鬼谷子 |
| 陈演恪 |
| 陈真 |
| 霍元甲 |
+-----------+
18 rows in set (0.00 sec)mysql>

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”课程的成绩；

数据库：
主机表
IP 端口 用户名 密码 上架时间
用户表
用户名 性别 密码 邮箱 部门ID 入职时间
管理员表
昵称 密码 邮箱 入职时间
部门表
部门名称 部门描述 部门创建时间
关系表
用户ID 主机ID 创建时间功能：
1.管理员模块
0:添加新的管理员用户
1.添加新的DevOps用户
2.添加新部门
3.添加新的服务器
4.查看DevOps详细信息以及所管理的主机列表
5.查看服务器信息
6.查看部门信息
7.更改密码
8.退出 2.用户模块
0.查看个人信息
1.更改密码
2.查看所管理的机器列表
3.查看指定机器的详细信息
4.远程操作主机
- 批量执行(futures.ThreadPoolExecutor实现)
- 执行命令
- 上传文件
- 选择执行(输入IP，单台执行)
- 执行命令
- 上传文件
5.退出

代码结构

# run.py#!/usr/bin/python3import os,sys
parent_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(parent_dir)
from etc.config import *
from src.common import *while True:
print(Welcome_info)
option = input('请输入角色序号>>>\t').strip()
if option not in rule_dict:
print('Input invalid, again please...')
else:
rule_dict[option]()

# config.py#!/usr/bin/python3mysql_server = {
'host':'xxx',
'port':xxx,
'database':'xxx',
'charset':'utf8',
'user':'xxx',
'password':'xxx'
}Welcome_info = '''
1.DevOps角色
2.Admin角色
3.退出
'''
devops_info = '''
0.查看个人信息
1.更改密码
2.查看所管理的机器列表
3.查看某台机器的详细信息
4.远程操作主机
5.退出
'''
cmd_option = '''
1.批量执行(归属在使用者名下的所有主机并发执行)
2.选择执行(选择一台进行执行)
3.退出
'''
cmd_dict_info = '''
1.远程执行命令(command)
2.上传文件(file_you_wanna_put remote_dir)
'''admin_info = '''
0:添加新的管理员用户
1.添加新的DevOps用户
2.添加新部门
3.添加新的服务器
4.查看DevOps详细信息以及所管理的主机列表
5.查看服务器信息
6.查看部门信息
7.更改密码
8.退出
'''

# models.py'''
CREATE DATABASE IF NOT EXISTS day11 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
USE day11;create table server_info(
id INT not null auto_increment primary key,
ser_ip char(16) not NULL,
ser_port INT not NULL,
ser_user VARCHAR(20) not NULL,
ser_pass VARCHAR(100) not NULL
)engine=innodb default charset=utf8;create table department_info(
id INT not null auto_increment primary key,
depart_name VARCHAR(50) not NULL
)engine=innodb default charset=utf8;create table user_info(
id INT not null auto_increment primary key,
user_name VARCHAR(30) not NULL,
user_sex TINYINT(1) not NULL,
user_passVARCHAR(100) not NULL,
user_email VARCHAR(50),
department_id int not NULL,
constraint fk_user2department foreign key (department_id) references department_info(id)
)engine=innodb default charset=utf8;create table relation(
id int not null auto_increment primary key,
user_id int not NULL,
server_id int not NULL,
constraint fk_relation2user foreign key (user_id) references user_info(id),
constraint fk_relation2server foreign key (server_id) references server_info(id)
)engine=innodb default charset=utf8;create table admin_info(
id INT not null auto_increment primary key,
user_name VARCHAR(30) not NULL,
user_passVARCHAR(100) not NULL,
user_email VARCHAR(50) not NULL
)engine=innodb default charset=utf8;
'''

# common.py#!/usr/bin/python3import os
import getpass
import hashlib
import paramiko
from etc.config import *
from src.user import Admin,Department,DevOps,Server
from concurrent import futuresdef md5_str(string):
m = hashlib.md5()
m.update(string.encode('utf-8'))
new_str = m.hexdigest()
return new_strdef bye():
print('See you')
exit(0)
def byebye(obj):
print('See you')
exit(0)def devops():
user_name = input('请输入用户名>>>\t').strip()
user_pass = getpass.getpass('请输入密码>>>\t').strip()
status,result = DevOps.login(user_name,md5_str(user_pass))
if status:
print('{} 登录成功'.format(user_name))
else:
print('登录失败：用户名或密码错误！')
return
while True:
print(devops_info)
cmd_index = input('请输入命令序号>>>\t').strip()
if cmd_index not in devops_dict:
print('Input invalid, again please...')
else:
user_obj = DevOps(result['user_name'], result['user_sex'], result['user_pass'], result['user_email'], result['department_id'])
devops_dict[cmd_index](user_obj)def admin():
admin_name = input('请输入管理员昵称>>>\t').strip()
admin_pass = getpass.getpass('请输入管理员密码>>>\t').strip()
status,result = Admin.login(admin_name,md5_str(admin_pass))
if status:
print('{} 登录成功'.format(admin_name))
else:
print('登录失败：用户名或密码错误！')
return
while True:
print(admin_info)
cmd_index = input('请输入命令序号>>>\t').strip()
if cmd_index not in admin_dict:
print('Input invalid, again please...')
else:
admin_obj = Admin(result['admin_name'],result['admin_pass'],result['admin_email'])
admin_dict[cmd_index](admin_obj)def add_department(admin_obj):
depart_name = input('请输入部门名称>>>\t').strip()
description = input('请添加部门介绍信息>>>\t').strip()
depart_obj = Department(depart_name,description)
res = depart_obj.save()
if res:
print('新部门：{} 添加成功！'.format(depart_name))
else:
print('新部门：{} 添加失败！！！'.format(depart_name))def add_devops(admin_obj):
user_name = input('请输入员工姓名>>>\t').strip()
user_sex = input('请输入员工性别>>>\t').strip()
user_pass = input('请输入员工初始化密码>>>\t').strip()
user_email = input('请输入员工邮件地址>>>\t').strip()
department_list = Department.get_department_list()
department_id_list = []
for department in department_list:
department_id_list.append(department['id'])
while True:
print('======已有部门信息列表如下======')
show_department_info(admin_obj)
department_id = input('请输入员工归属部门序号>>>\t').strip()
if int(department_id) not in department_id_list:
print('部门ID输入有误')
else:
break
devops_obj = DevOps(user_name,user_sex,md5_str(user_pass),user_email,department_id)
res = devops_obj.save()
if res:
print('新员工：{} 添加成功！'.format(user_name))
else:
print('新员工：{} 添加失败！！！'.format(user_name))def add_admin(admin_obj):
admin_name = input('请输入管理员昵称>>>\t').strip()
admin_pass = input('请输入管理员初始化密码>>>\t').strip()
admin_email = input('请输入管理员邮件地址>>>\t').strip()
admin_obj = Admin(admin_name, md5_str(admin_pass), admin_email)
res = admin_obj.save()
if res:
print('新管理员：{} 添加成功！'.format(admin_name))
else:
print('新管理员：{} 添加失败！！！'.format(admin_name))
def add_server(admin_obj):
ser_ip = input('请输入服务器IP>>>\t').strip()
ser_port = input('请输入服务器登录端口>>>\t').strip()
ser_user = input('请输入服务器登录用户>>>\t').strip()
ser_pass = getpass.getpass('请输入服务器登录密码>>>\t').strip()
server_obj = Server(ser_ip,ser_port,ser_user,ser_pass)
res = server_obj.save()
if res:
print('新机器：{} 添加成功！'.format(ser_ip))
else:
print('新机器：{} 添加失败！！！'.format(ser_ip))def show_devops_info(admin_obj):
devops_list = DevOps.get_devops_list()
for user in devops_list:
print('{}\t{}\t{}\t{}'.format(user['user_name'],user['user_sex'],user['user_email'],user['depart_name']))
def show_department_info(admin_obj):
department_list = Department.get_department_list()
for department in department_list:
print('{}\t{}\t{}'.format(department['id'],department['depart_name'],department['description']))
def change_admin_pass(admin_obj):
old_pass = getpass.getpass('请输入当前登录密码>>>\t').strip()
new_pass = getpass.getpass('请输入新密码>>>\t').strip()
if '' != new_pass:
res = admin_obj.change_pass(md5_str(old_pass),md5_str(new_pass))
if res:
print('密码更改成功！')
else:
print('密码更改失败！！！')
else:
print('密码不能为空')
def show_user_info(user_obj):
status, private_info = user_obj.get_private_info()
if status:
print('姓名：{}\n性别：{}\n邮箱：{}\n部门：{}\n入职时间：{}\n'.format(private_info['user_name'],\
private_info['user_sex'],private_info['user_email'],\
private_info['depart_name'],private_info['date']))
else:
print('查询个人信息失败！！！')
def change_user_pass(user_obj):
old_pass = getpass.getpass('请输入当前登录密码>>>\t').strip()
new_pass = getpass.getpass('请输入新密码>>>\t').strip()
if '' != new_pass:
res = user_obj.change_pass(md5_str(old_pass), md5_str(new_pass))
if res:
print('密码更改成功！')
else:
print('密码更改失败！！！')
else:
print('密码不能为空')
def show_server_info(user_obj):
server_list = Server.get_server_list()
for server in server_list:
print('{}\t{}\t{}'.format(server['ser_ip'], server['ser_port'], server['ser_user']))
def user_have_ip(user_obj,ip):
server_info_list = user_obj.get_my_servers()
server_list = []
for server in server_info_list:
if server['ser_ip'] not in server_list:
server_list.append(server['ser_ip'])
if ip not in server_list:
return False,[]
else:
for server in server_info_list:
if ip == server['ser_ip']:
server_info = server
return True,server_info
def show_server_info_by_ip(user_obj):
ip = input('请输入要查找的主机IP>>>\t').strip()
status,server_info = user_have_ip(user_obj,ip)
if status:
print('服务器IP:{}\t端口:{}\t登录用户:{}\t登录密码:{}\t'.format(server_info['ser_ip'], server_info['ser_port'], \
server_info['ser_user'], server_info['ser_pass']))
else:
print('此IP：{} 不在{}名下！！！'.format(ip, user_obj.user_name))
def command_to_exec(user_obj):
while True:
print(cmd_option)
cmd_index = input('请选择执行方式(1/2)>>>\t').strip()
if '3' == cmd_index:
break
if cmd_index not in cmd_option_dict:
print('Input invalid, again please...')
else:
cmd_option_dict[cmd_index](user_obj)def get_my_servers(user_obj):
server_info_list = user_obj.get_my_servers()
for server in server_info_list:
print('服务器IP:{}\t端口:{}\t登录用户:{}\t登录密码:{}\t'.format(server['ser_ip'],server['ser_port'],\
server['ser_user'],server['ser_pass']))def ssh_cmd(ip,port,user,password,cmd):
ssh_conn = paramiko.SSHClient()
ssh_conn.set_missing_host_key_policy(paramiko.AutoAddPolicy())
try:
ssh_conn.connect(ip,int(port),user,password,timeout=3)
except Exception as e:
print('连接服务器：{} 失败，发生异常：{}'.format(ip,e))
stdin, stdout, stderr = ssh_conn.exec_command(cmd)
# ret = []
# ret.append(ip)
res = stderr.read().decode('utf-8')
if not res:
res = stdout.read().decode('utf-8')
# ret.append(res)
return res
def cmd_to_run(server_info,command):
cmd = command
if cmd:
res = ssh_cmd(server_info['ser_ip'], server_info['ser_port'], server_info['ser_user'], \
server_info['ser_pass'],cmd)
# print(res[0])
# print(res[1])
return res
else:
print('不允许为空！！')
def file_to_upload(server_info,command):
file_to_put = command.split()[0]
remote_dir = command.split()[1]
file_path = r'D:\soft\work\Python_17\day11\homework\file'
try:
trans = paramiko.Transport((server_info['ser_ip'],int(server_info['ser_port'])))
trans.connect(username=server_info['ser_user'], password=server_info['ser_pass'])
sftp = paramiko.SFTPClient.from_transport(trans)
sftp.put(localpath=os.path.join(file_path,file_to_put),remotepath=r'%s/%s' % (remote_dir,file_to_put))
trans.close()
except Exception as e:
print('上传文件发生异常: %s' % e)
exit(-1)
return '{} 已成功上传至 {}:{}；'.format(os.path.join(file_path,file_to_put),server_info['ser_ip'],remote_dir)def concurrent_run(user_obj):
print('名下服务器列表如下：')
get_my_servers(user_obj)
print(cmd_dict_info)
command = input('请按要求输入命令>>>\t').strip()
option = input('请输入操作选项>>>\t').strip()
if option not in cmd_dict:
print('Input invalid, again please...')
else:
server_info_list = user_obj.get_my_servers()
pool = futures.ThreadPoolExecutor()
future_list = dict((pool.submit(cmd_dict[option],server_info,command),server_info) for server_info in server_info_list)
for future in futures.as_completed(future_list):
ip = future_list[future]['ser_ip']
if future.exception() is not None:
print('{}\n{}'.format(ip,future.exception()))
else:
print('{}\n{}'.format(ip,future.result()))def single_run(user_obj):
ip = input('请输入要操作的主机IP>>>\t').strip()
status,server_info = user_have_ip(user_obj,ip)
if status:
print(cmd_dict_info)
command = input('请按要求输入命令>>>\t').strip()
option = input('请输入操作选项>>>\t').strip()
if option not in cmd_dict:
print('Input invalid, again please...')
else:
res = cmd_dict[option](server_info,command)
print(res)
else:
print('此IP：{} 不在{}名下，无权操作！！！'.format(ip, user_obj.user_name))cmd_option_dict = {
'1':concurrent_run,
'2':single_run
}
cmd_dict = {
'1':cmd_to_run,
'2':file_to_upload
}
devops_dict = {
'0':show_user_info,
'1':change_user_pass,
'2':get_my_servers,
'3':show_server_info_by_ip,
'4':command_to_exec,
'5':byebye
}
admin_dict = {
'0':add_admin,
'1':add_devops,
'2':add_department,
'3':add_server,
'4':show_devops_info,
'5':show_server_info,
'6':show_department_info,
'7':change_admin_pass,
'8':byebye
}
rule_dict = {
'1': devops,
'2': admin,
'3': bye,
}

# user.py#!/usr/bin/python3import os
import pymysql
from etc.config import *class base():
def __init__(self):
pass
@classmethod
def connect_mysql(cls):
conn = pymysql.Connect(host=mysql_server['host'], port=mysql_server['port'], database=mysql_server['database'], \
charset=mysql_server['charset'], user=mysql_server['user'],
password=mysql_server['password'])
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
return conn,cursor
@classmethod
def close(cls,conn,cursor):
cursor.close()
conn.close()class Admin(base):
def __init__(self,admin_name,admin_pass,admin_email):
self.admin_name = admin_name
self.admin_pass = admin_pass
self.admin_email = admin_email
def save(self):
res = False
conn,cursor = self.connect_mysql()
try:
cursor.execute('insert into admin_info(admin_name,admin_pass,admin_email) values(%s,%s,%s)', \
[self.admin_name, self.admin_pass, self.admin_email])
conn.commit()
res = True
except Exception as e:
print('增加新管理员时发生异常：{}'.format(e))
res = False
finally:
base.close(conn, cursor)
return res
@classmethod
def login(cls,admin_name,admin_pass):
conn,cursor = base.connect_mysql()
cursor.execute('select * from admin_info where admin_name=%s and admin_pass=%s', [admin_name,admin_pass])
result = cursor.fetchone()
if result:
cls.close(conn, cursor)
return True,result
else:
cls.close(conn, cursor)
return False,None
def change_pass(self,old_pass,new_pass):
res = False
conn, cursor = base.connect_mysql()
try:
cursor.execute('select admin_pass from admin_info where admin_email=%s', [self.admin_email])
if old_pass == cursor.fetchone()['admin_pass']:
cursor.execute('update admin_info set admin_pass=%s where admin_email=%s', [new_pass, self.admin_email])
conn.commit()
res = True
else:
print('当前密码输入有误！')
except Exception as e:
print('更改管理员密码发生异常：{}'.format(e))
res = False
finally:
self.close(conn, cursor)
return resclass Department(base):
def __init__(self,depart_name,description):
self.depart_name = depart_name
self.description = description
@classmethod
def get_department_list(cls):
conn,cursor = base.connect_mysql()
try:
cursor.execute('select * from department_info')
result = cursor.fetchall()
return result
except Exception as e:
print('查询部门信息的时候发生异常：{}'.format(e))
finally:
base.close(conn, cursor)
def save(self):
res = False
conn,cursor = self.connect_mysql()
try:
cursor.execute('insert into department_info(depart_name,description) values(%s,%s)', \
[self.depart_name,self.description])
conn.commit()
res = True
except Exception as e:
print('增加新部门时发生异常：{}'.format(e))
res = False
finally:
self.close(conn,cursor)
return resclass DevOps(base):
def __init__(self,user_name,user_sex,user_pass,user_email,department_id):
self.user_name = user_name
self.user_sex = user_sex
self.user_pass = user_pass
self.user_email = user_email
self.department_id = department_id
def save(self):
res = False
conn,cursor = self.connect_mysql()
try:
cursor.execute('insert into user_info(user_name,user_sex,user_pass,user_email,department_id) values(%s,%s,%s,%s,%s)', \
[self.user_name,self.user_sex,self.user_pass,self.user_email,self.department_id])
conn.commit()
res = True
except Exception as e:
print('增加新员工时发生异常：{}'.format(e))
res = False
finally:
self.close(conn, cursor)
return res
@classmethod
def get_devops_list(cls):
conn, cursor = base.connect_mysql()
try:
cursor.execute('select user_name,user_sex,user_email,depart_name from user_info left join department_info on user_info.department_id=department_info.id;')
result = cursor.fetchall()
return result
except Exception as e:
print('查询用户信息的时候发生异常：{}'.format(e))
finally:
base.close(conn, cursor)
def get_my_servers(self):
conn, cursor = base.connect_mysql()
try:
cursor.execute('select ui.user_name,si.ser_ip,si.ser_port,si.ser_user,si.ser_pass\
from user_info as ui left join relation as re on ui.id=re.user_id left join server_info as si \
on re.server_id=si.id where ui.user_email=%s;',[self.user_email])
result = cursor.fetchall()
return result
except Exception as e:
print('查询名下服务器信息的时候发生异常：{}'.format(e))
finally:
base.close(conn, cursor) def change_pass(self,old_pass,new_pass):
res = False
conn, cursor = base.connect_mysql()
try:
cursor.execute('select user_pass from user_info where user_email=%s', [self.user_email])
if old_pass == cursor.fetchone()['user_pass']:
cursor.execute('update user_info set user_pass=%s where user_email=%s', [new_pass, self.user_email])
conn.commit()
res = True
else:
print('当前密码输入有误！')
except Exception as e:
print('更改用户密码发生异常：{}'.format(e))
res = False
finally:
self.close(conn, cursor)
return res
@classmethod
def login(cls,user_name,user_pass):
conn, cursor = base.connect_mysql()
cursor.execute('select * from user_info where user_name=%s and user_pass=%s', [user_name, user_pass])
result = cursor.fetchone()
if result:
cls.close(conn, cursor)
return True, result
else:
cls.close(conn, cursor)
return False, None
def get_private_info(self):
conn, cursor = base.connect_mysql()
cursor.execute('select ui.user_name,ui.user_sex,ui.user_email,ui.date,di.depart_name from user_info as ui LEFT JOIN department_info as di on ui.department_id=di.id where user_email=%s', [self.user_email])
result = cursor.fetchone()
if result:
self.close(conn, cursor)
return True, result
else:
self.close(conn, cursor)
return False, Noneclass Server(base):
def __init__(self,ser_ip,ser_port,ser_user,ser_pass):
self.ser_ip = ser_ip
self.ser_port = ser_port
self.ser_user = ser_user
self.ser_pass = ser_pass
def save(self):
res = False
conn, cursor = self.connect_mysql()
try:
cursor.execute('insert into server_info(ser_ip,ser_port,ser_user,ser_pass) values(%s,%s,%s,%s)', \
[self.ser_ip, self.ser_port, self.ser_user, self.ser_pass])
conn.commit()
res = True
except Exception as e:
print('增加新服务器时发生异常：{}'.format(e))
res = False
finally:
self.close(conn, cursor)
return res
@classmethod
def get_server_list(cls):
conn, cursor = base.connect_mysql()
try:
cursor.execute('select * from server_info')
result = cursor.fetchall()
return result
except Exception as e:
print('查询服务器信息时发生异常：{}'.format(e))
finally:
base.close(conn, cursor)