写在前面
包子有肉，不在褶上；
前言
存储和数据库是整个网站架构的瓶颈
- 关系型数据库(MySQL | Oracle)
- 类似于excel表格
- 用结构化查询语言SQL进行存取的
- 非关系型数据库(NoSQL：not only SQL)
- NoSQL存储数据不需要固定的表结构，通常也不存在链接操作
- 动态请求越来越多，对数据库要求变高了，所以导致了NoSQL的产生
- 非关系型数据库种类
- key-value
- Memcache，纯内存的NoSQL数据库
- Redis，持久化的NoSQL数据库（内存+磁盘）
- 列存储数据库
- Cassandra (Facebook | 360 在使用，是开源的)
- HBase (新浪在使用)
- 面向文档的数据库（将数据以文档的形式存储）
- MongoDB
- key-value
- 关系型数据库
- Oracle 适用于传统行业
- MySQL 适用于大、中、小行互联网企业
- MariaDB 是MySQL的一个分支，由MySQL创始人主导开发的
- SQL Server
- Access
- Memcache (key-value)
数据都存储在内存中； 容易丢失数据，新浪在这基础上搞了个MemcacheDB，是持久化的；
- Redis (key-value)
数据都会缓存在内存中，但是Redis会周期性的把更新的数据写入到磁盘或者把修改操作写入追加的记录文件
- MongoDB 是介于关系型数据库和非关系型数据库之间的一个产品
它支持的数据结构非常松散，类似json的bjson格式； 它支持的查询语言非常强大
一、MySQL安装部署
# 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.
二、MySQL基本数据类型
- MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型；
- 参考1：https://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html
- 参考2：http://www.runoob.com/mysql/mysql-data-types.html
1.数值
- 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词；
- MySQL中无布尔值，使用tinyint(1)构造；
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能够存储精确值的原因在于其内部按照字符串存储。
2.字符串
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 效率低，但节省存储空间
3.日期和时间
- 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR；
- 每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值；
三、MySQL基本操作
1.库操作
# 查看有哪些数据库
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地址为%)
- MySQL忘记root密码的解决办法
# 启动免授权服务端
mysqld --skip-grant-tables
# 客户端
mysql -u root -p
# 修改用户名密码
update mysql.user set authentication_string=password('666') where user='root';
flush privileges;
2.表操作
- 创建表
1.列名 2.数据类型 3.是否可以为空 4.默认值 5.自增（一张表只能有一个自增列） 6.主键 - 约束：不能为空且不能重复 - 索引：加快查找速度 7.事物: 数据库宕机 -> 需要回滚 - 需要把引擎设置为 innodb 8.字符编码 - 创建数据库 - 创建数据表 9.外键 - 关联另一张表，约束只能是某个表中某列已经存在的数据
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: 0 root@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: 0 root@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: 0 root@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: 0 root@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: 0 root@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 表名
3.表之间关系
- 一对多
# 员工信息表 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);
# 每个男生可以跟多个女生交朋友，每个女生也可以交多个男朋友；
4.表内容操作
# 创建数据库 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: 0 mysql> 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: 0 mysql> 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: 0 mysql> update student set name = '张丽' where id = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update student set name = '刘斌' where id = 7; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update student set name = 'standby' where id = 10; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 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>
# 修改表字段 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: 0 mysql> 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: 0 mysql> 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: 0 mysql> 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: 0 mysql> 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>
- 升序/降序（ORDER BY+ASC/DESC） 和 limit
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>
- 模糊匹配：like % 和 like _
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>
- 多表查询：LEFT JOIN ... ON ...
# 不使用 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>
1 无对应关系则不显示 2 select A.num, A.name, B.name 3 from A,B 4 Where A.nid = B.nid 5 6 无对应关系则不显示 7 select A.num, A.name, B.name 8 from A inner join B 9 on A.nid = B.nid 10 11 A表所有显示，如果B中无对应关系，则值为null 12 select A.num, A.name, B.name 13 from A left join B 14 on A.nid = B.nid 15 16 B表所有显示，如果B中无对应关系，则值为null 17 select A.num, A.name, B.name 18 from A right join B 19 on A.nid = B.nid 20 21 22 23 -- LEFT JOIN <- 以左边的表为主表显示 24 -- RIGHT JOIN <- 以右边的表为主表显示 25 -- INNER 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 和 having
特别的：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: 0 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 | | 张丽 | 女 | 考古学 | 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>
- 联合：UNION 和 UNION ALL
1 把两个SQL的结果进行组合(上下合并) 2 select * from student 3 union / union all 4 select * from teacher; 5 注意上下两个表中的列数要统一 6 注意： 7 1、如果所有数据都一致，那么union会对结果进行去重 8 2、union all ,会保存所有的
1 组合，自动处理重合 2 select nickname 3 from A 4 union 5 select name 6 from B 7 8 组合，不处理重合 9 select nickname 10 from A 11 union all 12 select name 13 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>
5.小试牛刀
- 1.找出课程id=1的所有学生姓名；
- 2.找出选文学课的所有学生姓名；
- 3.找出'胡适'教授的课程的所有学生；
四.练习
1.表关系（自行创建表及相关外键约束）
2.练习题
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”课程的成绩；
更多参考：http://www.cnblogs.com/wupeiqi/articles/5729934.html
五、MySQL进阶操作
1.视图
2.触发器
3.存储过程
4.函数
5.事物
6.索引
7.其他操作
六、Python操作MySQL
1.pymysql模块
2.pymysql基本操作
3.pymysql防SQL注入
七、练习
作业要求：
数据库：
主机表
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.退出
代码实现：
1 # 代码结构 2 主机管理 3 │ 4 ├─bin 5 │ └─ run.py 6 │ 7 ├─etc 8 │ └─ config.py 9 │ 10 ├─file(要上传的文件在这个目录下) 11 │ 12 ├─models 13 │ └─ models.py 14 │ 15 └─src 16 │ common.py 17 └─ user.py
# run.py
#!/usr/bin/python3
import 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('请输入角色序号>>> ').strip()
if option not in rule_dict:
print('Input invalid, again please...')
else:
rule_dict[option]()
# config.py
#!/usr/bin/python3
mysql_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_pass VARCHAR(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_pass VARCHAR(100) not NULL, user_email VARCHAR(50) not NULL )engine=innodb default charset=utf8; '''
# common.py
#!/usr/bin/python3
import os
import getpass
import hashlib
import paramiko
from etc.config import *
from src.user import Admin,Department,DevOps,Server
from concurrent import futures
def md5_str(string):
m = hashlib.md5()
m.update(string.encode('utf-8'))
new_str = m.hexdigest()
return new_str
def bye():
print('See you')
exit(0)
def byebye(obj):
print('See you')
exit(0)
def devops():
user_name = input('请输入用户名>>> ').strip()
user_pass = getpass.getpass('请输入密码>>> ').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('请输入命令序号>>> ').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('请输入管理员昵称>>> ').strip()
admin_pass = getpass.getpass('请输入管理员密码>>> ').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('请输入命令序号>>> ').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('请输入部门名称>>> ').strip()
description = input('请添加部门介绍信息>>> ').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('请输入员工姓名>>> ').strip()
user_sex = input('请输入员工性别>>> ').strip()
user_pass = input('请输入员工初始化密码>>> ').strip()
user_email = input('请输入员工邮件地址>>> ').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('请输入员工归属部门序号>>> ').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('请输入管理员昵称>>> ').strip()
admin_pass = input('请输入管理员初始化密码>>> ').strip()
admin_email = input('请输入管理员邮件地址>>> ').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>>> ').strip()
ser_port = input('请输入服务器登录端口>>> ').strip()
ser_user = input('请输入服务器登录用户>>> ').strip()
ser_pass = getpass.getpass('请输入服务器登录密码>>> ').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('{} {} {} {}'.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('{} {} {}'.format(department['id'],department['depart_name'],department['description']))
def change_admin_pass(admin_obj):
old_pass = getpass.getpass('请输入当前登录密码>>> ').strip()
new_pass = getpass.getpass('请输入新密码>>> ').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('姓名：{}
性别：{}
邮箱：{}
部门：{}
入职时间：{}
'.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('请输入当前登录密码>>> ').strip()
new_pass = getpass.getpass('请输入新密码>>> ').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('{} {} {}'.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>>> ').strip()
status,server_info = user_have_ip(user_obj,ip)
if status:
print('服务器IP:{} 端口:{} 登录用户:{} 登录密码:{} '.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)>>> ').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:{} 端口:{} 登录用户:{} 登录密码:{} '.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:softworkPython_17day11homeworkfile'
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('请按要求输入命令>>> ').strip()
option = input('请输入操作选项>>> ').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('{}
{}'.format(ip,future.exception()))
else:
print('{}
{}'.format(ip,future.result()))
def single_run(user_obj):
ip = input('请输入要操作的主机IP>>> ').strip()
status,server_info = user_have_ip(user_obj,ip)
if status:
print(cmd_dict_info)
command = input('请按要求输入命令>>> ').strip()
option = input('请输入操作选项>>> ').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/python3
import 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 res
class 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 res
class 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, None
class 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)