Python之路【目錄】
Python開發(fā)【第一篇】：目錄
MySQL練習(xí)題參考答案
Day57
約法三章：
1. 課下不看視頻
2. 筆記梗概
3. 課下整理上課內(nèi)容
課程安排：
MySQL
Web框架：
- Python
- 前端
- MySQL
項(xiàng)目實(shí)戰(zhàn)：
- 簡單CURD
- 保障系統(tǒng)（博客+BBS+后臺管理）
- CMDB資產(chǎn)管理
- CRM
- 堡壘機(jī)
爬蟲:
- Scrapy
其他：
...
MySQL
單機(jī)程序（自己DB）
單機(jī)程序（公用DB）
MySQL：是用于管理文件的一個軟件
- 服務(wù)端軟件
- socket服務(wù)端
- 本地文件操作
- 解析指令【SQL語句】
- 客戶端軟件（各種各樣）
- socket客戶端
- 發(fā)送指令
- 解析指令【SQL語句】
PS:
- DBMS數(shù)據(jù)庫管理系統(tǒng)
- SQL語句
技能：
- 安裝 服務(wù)端和客戶端
- 連接
- 學(xué)習(xí)SQL語句規(guī)則；指示服務(wù)端做任意操作
其他類似軟件：
關(guān)系型數(shù)據(jù)庫：sqllite,db2,oracle,access,sql server MySQL
非關(guān)系型數(shù)據(jù)庫：MongoDB,redis
-
MySQL安裝
Windows:
可執(zhí)行文件
點(diǎn)點(diǎn)點(diǎn)
壓縮包
放置任意目錄
初始化
服務(wù)端：E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --initialize-insecure
# 用戶名 root 密碼：空
啟動服務(wù)端：
E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysqld客戶端連接： E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysql -u root -p 發(fā)送指令： show databases; create database db1; 環(huán)境變量的配置： E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin mysqld windows服務(wù)： E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --install E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --remove net start MySQL net stop MySQL -
關(guān)于連接
文件夾【數(shù)據(jù)庫】
文件【表】
數(shù)據(jù)行【行】
數(shù)據(jù)行
數(shù)據(jù)行連接：
默認(rèn)：用戶root show databases; use 數(shù)據(jù)庫名稱; show tables; select * from 表名; select name,age,id from 表名; mysql數(shù)據(jù)庫user表 use mysql; select user,host from user; 創(chuàng)建用戶： create user 'alex'@'192.168.1.1' identified by '123123'; create user 'alex'@'192.168.1.%' identified by '123123'; create user 'alex'@'%' identified by '123123'; 授權(quán)： 權(quán)限 人 grant select,insert,update on db1.t1 to 'alex'@'%'; grant all privileges on db1.* to 'alex'@'%'; revoke all privileges on db1.t1 from 'alex'@'%'; DBA: 用戶名密碼 -
學(xué)習(xí)SQL語句規(guī)則
操作文件夾
create database db2;
create database db2 default charset utf8; *****
show databases;
drop database db2;操作文件
show tables;
create table t1(id int,name char(10)) default charset=utf8;
create table t1(id int,name char(10))engine=innodb default charset=utf8;
create table t3(id int auto_increment,name char(10))engine=innodb default charset=utf8; *****create table t1( 列名 類型 null, 列名 類型 not null, 列名 類型 not null auto_increment primary key, id int, name char(10) )engine=innodb default charset=utf8; # innodb 支持事務(wù)，原子性操作 # myisam myisam auto_increment 表示：自增 primary key： 表示 約束(不能重復(fù)且不能為空); 加速查找 auto_increment和primary key是綁定關(guān)系，一個表只能設(shè)置一個 not null: 是否為空 數(shù)據(jù)類型： 數(shù)字： tinyint int bigint FLOAT 0.00000100000123000123001230123 DOUBLE 0.00000000000000000000100000123000123001230123 0.00000100000123000000000000000 decimal 0.1 字符串： char(10) 速度快() root root varchar(10) 節(jié)省空間 root PS: 創(chuàng)建數(shù)據(jù)表把定長列往前放 text 上傳文件： 文件存硬盤 db存路徑 時間類型 DATETIME enum set create table t1( id int signed not null auto_increment primary key, num decimal(10,5), name char(10) )engine=innodb default charset=utf8; 清空表： delete from t1; truncate table t1; 刪除表： drop table t1;操作文件中內(nèi)容
插入數(shù)據(jù)：
insert into t1(id,name) values(1,'alex');
刪除：
delete from t1 where id<6
修改：
update t1 set age=18;
update t1 set age=18 where age=17;
查看數(shù)據(jù)：
select * from t1;外鍵：
create table userinfo( uid int auto_increment primary key, name varchar(32), department_id int, xx_id int, constraint fk_user_depar foreign key (department_id) references department(id) )engine=innodb default charset=utf8; create table department( id bigint auto_increment primary key, title char(15) )engine=innodb default charset=utf8;
innodb原子操作
今日內(nèi)容參考博客：
http://www.cnblogs.com/wupeiqi/articles/5713315.html
作業(yè)：
http://images2015.cnblogs.com/blog/425762/201608/425762-20160803224643778-2071849037.png
http://www.cnblogs.com/wupeiqi/articles/5729934.html
Day58
上節(jié)回顧：
1. 以ATM引出DBMS
2. MySQL
- 服務(wù)端
- 客戶端
3. 通信交流
- 授權(quán)
- SQL語句
- 數(shù)據(jù)庫
create database db1 default charset=utf8;
drop database db1;
- 數(shù)據(jù)表
先創(chuàng)建tb2部門表
create table tb1用戶表(
id int not null auto_increment primary key,
name char(10), --> 10為字符
department_id int,
p_id int,
constraint fk_1 foreign key (department_id,p_id) references tb2(tid,xid)
)engine=innodb default charset=utf8;
補(bǔ)充：主鍵
一個表只能有一個主鍵
主鍵可以由多列組成
補(bǔ)充：外鍵 ?
CREATE TABLE t5 (
nid int(11) NOT NULL AUTO_INCREMENT,
pid int(11) not NULL,
num int(11),
primary key(nid,pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t6(
id int auto_increment primary key,
name char(10),
id1 int,
id2 int,
CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid)
)engine=innodb default charset=utf8;
- 數(shù)據(jù)行
insert into tb1(name,age) values('alex',18);
insert into tb1(name,age) values('alex',18),('egon',19),('yuan',20);
delete from tb1;
truncate table tb1;
delete from tb1 where id > 10
update tb1 set name='root' where id > 10
select * from tb;
select id,name from tb;
4 對于自增補(bǔ)充：
desc t10;
show create table t10;
show create table t10 \G;
alter table t10 AUTO_INCREMENT=20;
MySQL: 自增步長
基于會話級別：
show session variables like 'auto_inc%'; 查看全局變量
set session auto_increment_increment=2; 設(shè)置會話步長
# set session auto_increment_offset=10;
基于全局級別：
show global variables like 'auto_inc%'; 查看全局變量
set global auto_increment_increment=2; 設(shè)置會話步長
# set global auto_increment_offset=10;
SqlServer：自增步長：
基礎(chǔ)表級別：
CREATE TABLE `t5` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步長=2 DEFAULT CHARSET=utf8
CREATE TABLE `t6` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步長=20 DEFAULT CHARSET=utf8
今日內(nèi)容：
0. 唯一索引
create table t1(
id int ....,
num int,
xx int,
unique 唯一索引名稱 (列名,列名), #聯(lián)合唯一
constraint ....
)
#
1 1 1
2 1 2
PS:
唯一：
約束不能重復(fù)（可以為空）
PS: 主鍵不能重復(fù)（不能為空）
加速查找
1. 外鍵的變種
a. 用戶表和部門表
用戶：
1 alex 1
2 root 1
3 egon 2
4 laoyao 3
部門:
1 服務(wù)
2 保安
3 公關(guān)
===》 一對多
b. 用戶表和博客表
用戶表：
1 alex
2 root
3 egon
4 laoyao
博客表：
FK() + 唯一
1 /yuanchenqi/ 4
2 /alex3714/ 1
3 /asdfasdf/ 3
4 /ffffffff/ 2
===> 一對一
create table userinfo1(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;
create table admin(
id int not null auto_increment primary key,
username varchar(64) not null,
password VARCHAR(64) not null,
user_id int not null,
unique uq_u1 (user_id),
CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8;
c. 用戶表（百合網(wǎng)） 相親記錄表
示例1：
用戶表
相親表
示例2：
用戶表
主機(jī)表
用戶主機(jī)關(guān)系表
===》多對多
create table userinfo2(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;
create table host(
id int auto_increment primary key,
hostname char(64)
)engine=innodb default charset=utf8;
create table user2host(
id int auto_increment primary key,
userid int not null,
hostid int not null,
unique uq_user_host (userid,hostid),
CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;
2. SQL語句數(shù)據(jù)行操作補(bǔ)充
create table tb12(
id int auto_increment primary key,
name varchar(32),
age int
)engine=innodb default charset=utf8;
增
insert into tb11(name,age) values('alex',12);
insert into tb11(name,age) values('alex',12),('root',18);
insert into tb12(name,age) select name,age from tb11;
刪
delete from tb12;
delete from tb12 where id !=2
delete from tb12 where id =2
delete from tb12 where id > 2
delete from tb12 where id >=2
delete from tb12 where id >=2 or name='alex'
改
update tb12 set name='alex' where id>12 and name='xx'
update tb12 set name='alex',age=19 where id>12 and name='xx'
查
select * from tb12;
select id,name from tb12;
select id,name from tb12 where id > 10 or name ='xxx';
select id,name as cname from tb12 where id > 10 or name ='xxx';
select name,age,11 from tb12;
其他：
select * from tb12 where id != 1
select * from tb12 where id in (1,5,12);
select * from tb12 where id not in (1,5,12);
select * from tb12 where id in (select id from tb11)
select * from tb12 where id between 5 and 12; -- 閉區(qū)間
通配符：
select * from tb12 where name like "a%"
select * from tb12 where name like "a_"
分頁：
select * from tb12 limit 10;
select * from tb12 limit 0,10;
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;
select * from tb12 limit 10 offset 20;
從第20行開始讀取，讀取10行；
結(jié)合Python分頁：
# page = input('請輸入要查看的頁碼')
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1
# select * from tb12 limit 10,10;2
排序：
select * from tb12 order by id desc; 大到小
select * from tb12 order by id asc; 小到大
select * from tb12 order by age desc,id desc;
取后10條數(shù)據(jù)
select * from tb12 order by id desc limit 10;
分組：
select count(id),max(id),part_id from userinfo5 group by part_id;
count
max
min
sum
avg
**** 如果對于聚合函數(shù)結(jié)果進(jìn)行二次篩選時？必須使用having ****
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
連表操作：
select * from userinfo5,department5
select * from userinfo5,department5 where userinfo5.part_id = department5.id
select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
# userinfo5左邊全部顯示
# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
# department5右邊全部顯示
select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
將出現(xiàn)null時一行隱藏
select * from
department5
left join userinfo5 on userinfo5.part_id = department5.id
left join userinfo6 on userinfo5.part_id = department5.id
select
score.sid,
student.sid
from
score
left join student on score.student_id = student.sid
left join course on score.course_id = course.cid
left join class on student.class_id = class.cid
left join teacher on course.teacher_id=teacher.tid
select count(id) from userinfo5;
作業(yè)練習(xí)：
http://www.cnblogs.com/wupeiqi/articles/5729934.html
10-15個完成
day59
臨時表
select sid from (select * from score where num > 60) as B;
(select * from score where num > 60) as B;
2、查詢“生物”課程比“物理”課程成績高的所有學(xué)生的學(xué)號；
select A.student_id from (select score.sid,score.student_id,course.cname,score.num from score left join course on score.course_id=course.cid where course.cname="生物") as A inner join (select score.sid,score.student_id,course.cname,score.num from score left join course on score.course_id=course.cid where course.cname="物理") as B on A.student_id = B.student_id where A.num > B.num;
3、查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績；
select student_id,avg(num) from score group by student_id having avg(num)>60;
在此基礎(chǔ)上顯示學(xué)生的姓名
select B.student_id,student.sname,B.cccc from (select student_id,avg(num) as cccc from score group by student_id having avg(num)>60) as B left join student on B.student_id = student.sid;
4、查詢所有同學(xué)的學(xué)號、姓名、選課數(shù)、總成績；
select score.student_id,student.sname,count(1),sum(num) from score left join student on score.student_id=student.sid group by score.student_id;
5、查詢姓“李”的老師的個數(shù)；
select count(1) from teacher where tname like "李%";
select count(tid) from teacher where tname like '李%';
6、查詢沒學(xué)過“葉平”老師課的同學(xué)的學(xué)號、姓名；
select student.sid,student.sname from student where sid not in (select student_id from score where course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老師") group by student_id);
pymysql
# SQL注入
import pymysql
user = input("username:")
pwd = input("password:")
conn = pymysql.connect(host="localhost",user="root",password="",database="db666")
cursor = conn.cursor()
# sql = "select * from userinfo where username='%s' and password='%s'" %(user,pwd,) #不要使用這種方式
# sql = "select * from userinfo where username='uu'or 1=1 -- ' and password='%s'" %(user,pwd,) --空格 會注釋掉后面的東西
# print(sql)
#3種方式，字典和列表形式可以用，中間的那種會報錯
sql = "select * from userinfo where username=%(u)s and password=%(p)s"
cursor.execute(sql,{'u':user,'p':pwd})
# sql = "select * from userinfo where username=%s and password=%s" #這種會報錯
# cursor.execute(sql,user,pwd) #TypeError: execute() takes from 2 to 3 positional arguments but 4 were given
# sql = "select * from userinfo where username=%s and password=%s"
# cursor.execute(sql,[user,pwd])
result = cursor.fetchone() 返回單個的元組，也就是一條記錄(row)，如果沒有結(jié)果 則返回 None
cursor.close()
conn.close()
print(result)
if result:
print('登陸成功')
else:
print('登陸失敗')
增 刪 改
import pymysql
#增加
conn = pymysql.connect(host="localhost",user="root",password="",database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('root','123123')" # root和123123那里只能是單引號
r = cursor.execute(sql) # r表示受影響的行數(shù)
conn.commit() # 涉及到增刪改的時候需要commit
cursor.close()
conn.close()
import pymysql
#增加
# user = "eric"
# pwd = "123123"
conn = pymysql.connect(host="localhost",user="root",password="",database="db666")
cursor = conn.cursor()
# sql = "insert into userinfo(username,password) values(%s,%s)"
# cursor.execute(sql,[user,pwd]) # 列表元組都可以
sql = "insert into userinfo(username,password) values(%s,%s)" # 增加多條
r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')]) # r表示受影響的行數(shù)
conn.commit() # 涉及到增刪改的時候需要commit
cursor.close()
conn.close()
查
import pymysql
# 查
# cursor.scroll(1,mode='relative') # 相對當(dāng)前位置移動
# cursor.scroll(2,mode='absolute') # 相對絕對位置移動
conn = pymysql.connect(host="localhost",user="root",password="",database="db666")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #括號里面的參數(shù)不加返回元組里面套元組，加了返回列表里面套字典
sql = "select * from userinfo"
cursor.execute(sql)
# result = cursor.fetchone() # 獲取一條
# print(result)
# result = cursor.fetchmany(3) # 獲取多條
# print(result)
result = cursor.fetchall() # 獲取全部
print(result)
cursor.close()
conn.close()
