1.mysql高級
1.關(guān)系
創(chuàng)建學(xué)生成績關(guān)系表
drop table if exists scores;
drop table if exists students;
drop table if exists subjects;
create table students(
id int auto_increment primary key,
sname varchar(20)
);
create table subjects(
id int auto_increment primary key,
stitle varchar(20)
);
/*
create table scores(
id int auto_increment primary key,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id), #引用的第一種寫法
foreign key(subid) references subjects(id)
);
*/
create table scores(
id int auto_increment primary key,
stuid int,
subid int,
score decimal(5,2)
);
#引用的第二種寫法
alter table scores
add constraint fk_scores_stuid foreign key(stuid)
references students(id) on delete cascade; #外鍵的級聯(lián)操作
alter table scores
add constraint fk_scores_subid foreign key(subid)
references subjects(id) on delete cascade;
insert into students(sname) values('小茗');
insert into students(sname) values('可樂');
insert into students(sname) values('小伊');
insert into subjects(stitle) values('語文');
insert into subjects(stitle) values('數(shù)學(xué)');
insert into subjects(stitle) values('英語');
select * from students;
select * from subjects;
select * from scores;
insert into scores(stuid,subid,score) values(1,1,88);
insert into scores(stuid,subid,score) values(1,2,90);
insert into scores(stuid,subid,score) values(2,3,99);
insert into scores(stuid,subid,score) values(2,1,70);
-- 查詢一個學(xué)生各科的成績
select t1.id,t1.sname,t2.score
from students t1,scores t2
where t1.id = t2.stuid;
三張表如圖
查詢一個學(xué)生各科成績的結(jié)果
級聯(lián)操作的類型包括：
- restrict（限制）：默認(rèn)值捕传，拋異常
- cascade（級聯(lián)）：如果主表的記錄刪掉凝危，則從表中相關(guān)聯(lián)的記錄都將被刪除
- set null：將外鍵設(shè)置為空
- no action：什么都不做
2.連接查詢
- 當(dāng)需要對有關(guān)系的多張表進(jìn)行查詢時逻炊，需要使用連接 join
連接查詢分類如下：
-
表A inner join 表B：表A與表B匹配的行會出現(xiàn)在結(jié)果中 -
表A left join 表B：表A與表B匹配的行會出現(xiàn)在結(jié)果中伐脖，外加表A中獨有的數(shù)據(jù)氢卡，未對應(yīng)的數(shù)據(jù)使用null填充 -
表A right join 表B：表A與表B匹配的行會出現(xiàn)在結(jié)果中嫂冻，外加表B中獨有的數(shù)據(jù)，未對應(yīng)的數(shù)據(jù)使用null填充
- 在查詢或條件中推薦使用“表名.列名”的語法
- 如果多個表中列名不重復(fù)可以省略“表名.”部分
- 如果表的名稱太長导犹，可以在表名后面使用' as 簡寫名'或' 簡寫名'唱凯，為表起個臨時的簡寫名稱
實例一
-- 先建立關(guān)系表
drop table if exists x_emp;
drop table if exists x_dept;
create table x_dept(
id int auto_increment primary key,
dname varchar(20)
);
create table x_emp(
id int auto_increment primary key,
ename varchar(20),
did int not null,
mgr int,
foreign key(did) references x_dept(id),
foreign key(mgr) references x_emp(id)
);
insert into x_dept(dname) values('研發(fā)部');
insert into x_dept(dname) values('人事部');
insert into x_dept(dname) values('財務(wù)部');
insert into x_emp(ename,did,mgr) values('老王',1,null);
insert into x_emp(ename,did,mgr) values('老張',1,1);
insert into x_emp(ename,did,mgr) values('老趙',1,1);
insert into x_emp(ename,did,mgr) values('小茗',2,3);
insert into x_emp(ename,did,mgr) values('小伊',2,3);
select * from x_dept;
select * from x_emp;
創(chuàng)建的兩個表
/*查詢員工的編號，姓名谎痢，上級的姓名*/
select * from x_emp 員工表,x_emp 上級表
where 員工表.mgr = 上級表.id;
-- 第一種寫法
select t1.id,t1.ename,t2.ename
from x_emp t1,x_emp t2
where t1.mgr = t2.id;
-- 第二種寫法
select t1.id,t1.ename,t2.ename
from x_emp t1
inner join x_emp t2
on t1.mgr = t2.id;
查詢員工額編號磕昼，姓名，上級的姓名
-- 'left',表x_emp與表x_dept匹配的行會出現(xiàn)在結(jié)果中节猿，外加表x_emp中獨有的數(shù)據(jù)票从，未對應(yīng)的數(shù)據(jù)使用null填充
select *
from x_emp left outer join x_dept
on x_emp.did = x_dept.id;
left
-- 'right',表x_emp與表x_dept匹配的行會出現(xiàn)在結(jié)果中，外加表x_dept中獨有的數(shù)據(jù)滨嘱，未對應(yīng)的數(shù)據(jù)使用null填充
select *
from x_emp right outer join x_dept
on x_emp.did = x_dept.id;
right
/*x_emp中峰鄙，查詢是經(jīng)理的人的名字
1、哪些是經(jīng)理疤辍吟榴？
2、再找名字
*/
select ename
from x_emp
where id in(
select mgr from x_emp
);
查詢哪些是經(jīng)理
實例二
drop table if exists scores;
drop table if exists students;
drop table if exists subjects;
create table students(
id int auto_increment primary key,
sname varchar(20)
);
create table subjects(
id int auto_increment primary key,
stitle varchar(20)
);
create table scores(
id int auto_increment primary key,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);
insert into students(sname) values('小茗');
insert into students(sname) values('可樂');
insert into students(sname) values('小伊');
insert into subjects(stitle) values('語文');
insert into subjects(stitle) values('數(shù)學(xué)');
insert into subjects(stitle) values('英語');
insert into scores(stuid,subid,score) values(1,1,88);
insert into scores(stuid,subid,score) values(1,2,90);
insert into scores(stuid,subid,score) values(2,3,99);
insert into scores(stuid,subid,score) values(3,1,70);
select * from students;
select * from subjects;
select * from scores;
創(chuàng)建的三個表
/*查詢學(xué)生的編號囊扳，姓名吩翻，科目的名稱，成績*/
-- 三種方法都可以
select t1.id,t1.sname,t2.stitle,t3.score
from students t1,subjects t2,scores t3
where t3.stuid = t1.id and t3.subid = t2.id;
select t1.id,t1.sname,t2.stitle,t3.score
from scores t3
inner join students t1
on t3.stuid = t1.id
inner join subjects t2
on t3.subid = t2.id;
select t1.id,t1.sname,t2.stitle,t3.score
from students t1
inner join scores t3
on t3.stuid = t1.id
inner join subjects t2
on t3.subid = t2.id;
查詢學(xué)生的編號锥咸，姓名狭瞎，科目的名稱，成績
實例三
drop table if exists emp1;
drop table if exists salgrade;
create table emp1(
id int auto_increment primary key,
name VARCHAR(20),
sal int
);
create table salgrade(
id int auto_increment primary key,
low int,
high int
);
insert into emp1(name,sal) values('小茗',4000);
insert into emp1(name,sal) values('可樂',5000);
insert into emp1(name,sal) values('小伊',8800);
insert into salgrade(low,high) values(1000,3999);
insert into salgrade(low,high) values(4000,7999);
insert into salgrade(low,high) values(8000,10000);
select * from emp1;
select * from salgrade;
創(chuàng)建的兩個表
-- 查詢員工的編號搏予，工資和等級
select t1.id,t1.name,t1.sal,t2.id
from emp1 t1 inner join salgrade t2
on t1.sal between t2.low and t2.high;
查詢員工的編號熊锭，工資和等級
create view myView2
as
select * from emp1;
delete from emp1;
select * from myView2;
delete from emp1后，表emp1數(shù)據(jù)為空
實例四
/*查詢各學(xué)生的語文、數(shù)學(xué)碗殷、英語的成績*/
select sname,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='語文' and stuid=stu.id) as 語文,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='數(shù)學(xué)' and stuid=stu.id) as 數(shù)學(xué),
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='英語' and stuid=stu.id) as 英語
from students stu;
-- 這里我們可以用視圖來查詢劣针，視圖的用途就是查詢
/*視圖*/
create view myView
as
select sname,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='語文' and stuid=stu.id) as 語文,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='數(shù)學(xué)' and stuid=stu.id) as 數(shù)學(xué),
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='英語' and stuid=stu.id) as 英語
from students stu;
select * from myView;
select sname from myView;
查詢各學(xué)生的語文、數(shù)學(xué)亿扁、英語的成績
用視圖以后查詢的結(jié)果如圖
事務(wù)
- 當(dāng)一個業(yè)務(wù)邏輯需要多個sql完成時捺典，如果其中某條sql語句出錯，則希望整個操作都退回从祝，保證數(shù)據(jù)的正確性襟己。
- 使用事務(wù)可以完成退回的功能，保證業(yè)務(wù)邏輯的正確性
- 兩個功能：有一個出問題牍陌，回滾擎浴。都沒有問題，提交毒涧。(同生共死)
事務(wù)四大特性(簡稱ACID)
- 原子性(Atomicity)：事務(wù)中的全部操作在數(shù)據(jù)庫中是不可分割的贮预，要么全部完成，要么均不執(zhí)行
- 一致性(Consistency)：幾個并行執(zhí)行的事務(wù)契讲，其執(zhí)行結(jié)果必須與按某一順序串行執(zhí)行的結(jié)果相一致
- 隔離性(Isolation)：事務(wù)的執(zhí)行不受其他事務(wù)的干擾仿吞，事務(wù)執(zhí)行的中間結(jié)果對其他事務(wù)必須是透明的
- 持久性(Durability)：對于任意已提交事務(wù)，系統(tǒng)必須保證該事務(wù)對數(shù)據(jù)庫的改變不被丟失捡偏，即使數(shù)據(jù)庫出現(xiàn)故障
要求：表的類型必須是innodb或bdb類型(表的默認(rèn)類型就是innodb)唤冈，才可以對此表使用事務(wù)
查看表的創(chuàng)建語句
show create table students;
修改表的類型
alter table '表名' engine=innodb;
事務(wù)語句
- 開啟
begin; - 提交
commit; - 回滾
rollback;
示例
drop table if exists bank;
create table bank(
id int primary key,
name varchar(20),
money int
);
insert into bank values(1,'王明哲',100);
insert into bank values(2,'張妙涵',1);
select * from bank;
update bank set money = money-10 where id=1;
update bank set money = money+10 where id=2;
select * from bank;
-- 查看建表語句
show create table students;
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
-- 從建表語句中我們可以看出默認(rèn)的是InnoDB類型
/*事務(wù)，一般用來做測試*/
begin;
delete from bank;
rollback;
commit;
用事務(wù)來進(jìn)行測試
3.內(nèi)置函數(shù)
1.字符串函數(shù)
/*字符串函數(shù),sql里函數(shù)都是有返回值的*/
-- 查看字符的ascii碼值ascii(str)银伟，str是空串時返回0
select ascii('a');
97
-- 查看ascii碼值對應(yīng)的字符char(數(shù)字)
select char(97);
a
-- 拼接字符串concat(str1,str2...)
select concat(12,34,'ab','小茗');
1234ab小茗
-- 包含字符個數(shù)length(str)
select length('中a')
4
select length(ename),ename from x_emp;
/*截取字符串
left(str,len)返回字符串str的左端len個字符
right(str,len)返回字符串str的右端len個字符
substring(str,pos,len)返回字符串str的位置pos起len個字符*/
select substring('abc123',2,3);
bc1
/*去除空格
ltrim(str)返回刪除了左空格的字符串str
rtrim(str)返回刪除了右空格的字符串str
trim([方向 remstr from str)返回從某側(cè)刪除remstr后的字符串str你虹，方向詞包括both、leading彤避、trailing傅物，表示兩側(cè)、左琉预、右*/
select trim(' bar ');
bar
select trim(leading 'x' FROM 'xxxbarxxx');
barxxx
select trim(both 'x' FROM 'xxxbarxxx');
bar
select trim(trailing 'x' FROM 'xxxbarxxx');
xxxbar
--返回由n個空格字符組成的一個字符串space(n)
select space(10);
-- 替換字符串replace(str,from_str,to_str)
select replace('abc123','123','def');
-- 大小寫轉(zhuǎn)換董饰，函數(shù)如下
lower(str)
upper(str)
select lower('aBcD');
2.數(shù)學(xué)函數(shù)
-- 求絕對值abs(n)
select abs(-32);
32
-- 求m除以n的余數(shù)mod(m,n)，同運算符%
select mod(10,3);
1
select 10%3;
1
-- 地板floor(n)模孩，表示不大于n的最大整數(shù)
select floor(2.3);
2
-- 天花板ceiling(n)尖阔，表示不小于n的最大整數(shù)
select ceiling(2.3);
3
-- 求四舍五入值round(n,d)，n表示原數(shù)榨咐，d表示小數(shù)位置，默認(rèn)為0
select round(1.6);
2
-- 求x的y次冪pow(x,y)
select pow(2,3);
8
-- 獲取圓周率PI()
select PI();
3.141593
-- 隨機數(shù)rand()谴供，值為0-1.0的浮點數(shù)
select rand();
0.7000933091157053
select floor(2.3),ceil(2.3),round(2.3456,2);
2 3 2.35
3.日期函數(shù)
獲取子值块茁，語法如下:
-
year(date)返回date的年份(范圍在1000到9999) -
month(date)返回date中的月份數(shù)值 -
day(date)返回date中的日期數(shù)值 -
hour(time)返回time的小時數(shù)(范圍是0到23) -
minute(time)返回time的分鐘數(shù)(范圍是0到59) -
second(time)返回time的秒數(shù)(范圍是0到59) - 日期計算，使用+-運算符，數(shù)字后面的關(guān)鍵字為year数焊、month永淌、day、
hour佩耳、minute遂蛀、second - 日期格式化date_format(date,format)，format參數(shù)可用的值如下
- 獲取年%Y干厚，返回4位的整數(shù)
- 獲取年%y李滴，返回2位的整數(shù)
- 獲取月%m，值為1-12的整數(shù)
- 獲取日%d蛮瞄，返回整數(shù)
- 獲取時%H所坯，值為0-23的整數(shù)
- 獲取時%h，值為1-12的整數(shù)
- 獲取分%i挂捅，值為0-59的整數(shù)
- 獲取秒%s芹助，值為0-59的整數(shù)
/*日期-->字符串*/
select date_format('2017-10-20','%Y年%m月%d日')
2017年10月20日
/*字符串-->日期*/
select str_to_date('2017年10月20日','%Y年%m月%d日')
2017-10-20
-- 當(dāng)前日期current_date()
select current_date();
2017-06-23
-- 當(dāng)前時間current_time()
select current_time();
21:21:55
-- 當(dāng)前日期時間now()
select now();
2017-06-23 21:21:41
create table t1(
id int,
birthday date
);
insert into t1 values(1,'2017-10-20');
insert into t1 values(2,'2017/10/20');
insert into t1 values(3,str_to_date('2017年10月21日','%Y年%m月%d日'));
insert into t1 values('4','2017/10/20');
select * from t1;
/*日期-->字符串*/
select date_format('2017-10-20','%Y年%m月%d日')
/*字符串-->日期*/
select str_to_date('2017年10月20日','%Y年%m月%d日')
select current_date(),now();
4.時間與字符串的相互轉(zhuǎn)換
import time
import datetime
stime1 = '2015年10月20日'
#字符串--->時間time.struct_time
time1 = time.strptime(stime1,'%Y年%m月%d日')
print(time1)
結(jié)果：
time.struct_time(tm_year=2015, tm_mon=10, tm_mday=20, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=1, tm_yday=293, tm_isdst=-1)
#時間time.struct_time--->字符串
stime1 = time.strftime('%Y-%m-%d',time1)
print(stime1)
結(jié)果：
2015-10-20
dt = datetime.datetime.now()
print(dt)
結(jié)果：
2017-06-24 14:59:09.948008
dt = datetime.date(2017,6,24)
print(str(dt))
print(type(dt))
結(jié)果：
2017-06-24
<class 'datetime.date'>
dt = datetime.datetime(2017,6,24,14,23,45)
print(dt)
結(jié)果：
2017-06-24 14:23:45
dt = datetime.date(2017,6,24)
print(dt.timetuple())
結(jié)果：
time.struct_time(tm_year=2017, tm_mon=6, tm_mday=24, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=5, tm_yday=175, tm_isdst=-1)
dt = datetime.date(2017,6,24)
s = time.strftime('%Y/%m/%d',dt.timetuple())
print(s)
結(jié)果：
2017/06/24
