python學(xué)習(xí)day42筆記
約束條件
unsigned
作用：設(shè)置無符號(hào)
tinyint是-128~127，如果設(shè)置了unsigned，范圍就變成了0~255
例子：
create table t1(id int unsigned,name varchar(4));
insert into t1 values(-10,'egon');
# ERROR 1264 (22003): Out of range value for column 'id' at row 1
# 報(bào)錯(cuò)，意思就是必須是正數(shù)
用處：如果用不到負(fù)數(shù)，可以擴(kuò)大正數(shù)方面存儲(chǔ)值的范圍
zerofill
作用：未填充滿時(shí)用0填充至滿
int默認(rèn)是十位，如果設(shè)置了zerofill，填入沒達(dá)到十位的數(shù)時(shí)會(huì)在其前方填入0直到填滿
例子：
create table t3(id int zerofill);
insert into t3 values(5);
select * from t3;
# +------------+
# | id ? ? ? ? |
# +------------+
# | 0000000005 |
# +------------+
很少會(huì)用到這個(gè)方法
not null
作用：填寫值不能為空
默認(rèn)設(shè)置如果填入為空時(shí)自動(dòng)填入NULL，設(shè)置not null后，填入的值不能為空，可以從desc看到表結(jié)構(gòu)中的Default查看到輸入為空時(shí)自動(dòng)填入的值
insert into t1 values();
# 存入值null
create table t4(name varchar(4) not null);
insert into t4 values();
# ERROR 1364 (HY000): Field 'name' doesn't have a default value
# 報(bào)錯(cuò)，name格式?jīng)]存入值
空字符串和null的區(qū)別
空字符串和null并不是同一個(gè)東西
create table t4(name varchar(4));
insert into t4 values();
# 存入值NULL
insert into t4 values('');
# 無存入值，是一片空
會(huì)出現(xiàn)的問題：
存入值時(shí)是會(huì)顯示null和空白的區(qū)別的，但是用其他軟件查看的時(shí)候都顯示的是一樣的，如果用not null取值的話會(huì)有一部分取不出來
default
作用：設(shè)置默認(rèn)值
在某個(gè)格式后設(shè)置默認(rèn)值，如果未輸入值則直接使用默認(rèn)值
create table t5(id int,name varchar(16),gender enum('male','female','other') default 'male');
insert into t5 (id,name) values(1,'egon');
# 存入值1,'egon','male'
unique
作用：限制單列里數(shù)據(jù)的唯一性
設(shè)置數(shù)據(jù)時(shí)加上unique后，這一列的每個(gè)數(shù)據(jù)都是獨(dú)特的，這一列內(nèi)不能有一樣的數(shù)據(jù)存在
create table t6 (id int,name varchar(16) unique);
insert into t6 values(1,'egon');
insert into t6 values(2,'egon');
# ERROR 1062 (23000): Duplicate entry 'egon' for key 'name'
# 報(bào)錯(cuò)，你name里已經(jīng)有egon了
作用：限制多列里數(shù)據(jù)的唯一性
如下設(shè)置unique(格式1,格式2)，如果這兩個(gè)一起重復(fù)了就不能存入，如果只是單個(gè)重復(fù)了則可以存入
create table t7(id int,host varchar(32),port int,unique(host,port));
insert into t7 values(1,'127.0.0.1',3306);
insert into t7 values(1,'127.0.0.1',3307);
insert into t7 values(1,'127.0.0.1',3306);
# ERROR 1062 (23000): Duplicate entry '127.0.0.2-3306' for key 'host'
# 報(bào)錯(cuò)，'127.0.0.2-3306'這兩個(gè)值共同存在了
primary key
主鍵
1.從限制角度來說，主鍵相當(dāng)于非空且唯一
id int primary key == id int not null unique
? ?
? ?create table t8(id int primary key);
? ?insert into t8 values();
? ?# ERROR 1364 (HY000): Field 'id' doesn't have a default value
? ?# 沒有默認(rèn)值就直接報(bào)錯(cuò)，因?yàn)閜rimary有非空的特性
? ?insert into t8 values(1);
? ?insert into t8 values(1);
? ?# ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
? ?# primary條目的1重復(fù)，有唯一性
2.InnoDB存儲(chǔ)引擎規(guī)定一張表中必須有一個(gè)主鍵
# 為什么之前的創(chuàng)建表沒有指定主鍵也能創(chuàng)建成功？
InnoDB引擎自動(dòng)用一個(gè)隱藏的字段創(chuàng)建了一個(gè)主鍵，隱藏意味著看不到摸不著，也不能用。
3.主鍵的功能
查詢速度快：主鍵本質(zhì)也是一種索引
auto_increment
給主鍵使用的，自增，每次增加1
create table t9(id int primary key auto_increment,name varchar(16));
desc t9;
# +-------+-------------+------+-----+---------+----------------+
# | Field | Type ? ? ? ?| Null | Key | Default | Extra ? ? ? ? ?|
# +-------+-------------+------+-----+---------+----------------+
# | id ? ?| int(11) ? ? | NO ? | PRI | NULL ? ?| auto_increment |
# | name ?| varchar(16) | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|
# +-------+-------------+------+-----+---------+----------------+
insert into t9(name) values('egon');
# 存入id默認(rèn)自增，初始為0，寫一個(gè)增加1
select * from t9;
# +----+------+
# | id | name |
# +----+------+
# | ?1 | egon |
# +----+------+
結(jié)論：id類字段的標(biāo)準(zhǔn)寫法↓
id int primary key auto_crement
insert into t9(name) values('tom');
delete from t9 where id = 2;
insert into t9(name) values('tom');
select * from t9;
# +----+------+
# | id | name |
# +----+------+
# | ?1 | egon |
# | ?3 | tom ?|
# +----+------+
中間自增的主鍵不會(huì)因?yàn)樯倭艘粋€(gè)id就重新使用這個(gè)id，會(huì)一直往下自增
清空表數(shù)據(jù)
1.delete from t9; ?# 沒有指定則全部清空
select * from t9;
# Empty set (0.00 sec)
# 里面沒有值
2.truncate table t9; ?# 也是全部清空，只不過更加徹底
select * from t9;
# Empty set (0.00 sec)
# 里面沒有值
insert into t9(name) values('egon');
select * from t9;
# +----+------+
# | id | name |
# +----+------+
# | ?1 | egon |
# +----+------+
# 會(huì)連同自增一起清空
# 以后清空表數(shù)據(jù)，更推薦使用truncate
'''binlog 恢復(fù)數(shù)據(jù)(運(yùn)維掌握，我們可會(huì)可不會(huì))'''
外鍵
外鍵前期準(zhǔn)備
例子：
公司里人員的表格，字段分別有：員工id，姓名，所處部門，負(fù)責(zé)的工作
如果公司里有1000個(gè)人在同一個(gè)部門內(nèi)，而這個(gè)部門改名了且換了工作，此時(shí)公司人員的大表格則需要每個(gè)人的所處部門和負(fù)責(zé)的工作都進(jìn)行修改
此時(shí)就可以使用外鍵，將部門和負(fù)責(zé)的工作從公司人員的大表格中分離開來，單獨(dú)組成一個(gè)表格，然后公司人員的大表格中就可以直接輸入與部門工作相對(duì)應(yīng)的id進(jìn)行對(duì)接，如果有部門的改動(dòng)就只需要改動(dòng)部門工作相關(guān)的表格就可以全部改動(dòng)了
需要了解的是：
人員與部門之間的關(guān)系是，人員只能有一個(gè)部門，部門可以有多個(gè)人員，所以這是一對(duì)多的關(guān)系
在數(shù)據(jù)表中一般不存中文
表關(guān)系判斷
一對(duì)一
一對(duì)多
多對(duì)多
'''一對(duì)多'''
站在不同的角度去判斷
1.一個(gè)部門可以有多個(gè)用戶 ?# 可以
2.一個(gè)用戶可以有多個(gè)部門 ?# 不可以
結(jié)論：只要是一個(gè)可以，一個(gè)不可以，那他們就是一對(duì)多的關(guān)系
為什么不是多對(duì)一，因?yàn)镸ySQL關(guān)系表中沒有多對(duì)一的說法
'''多對(duì)多'''
站在不同的角度去判斷
1.一本書可以有多個(gè)作者 ?# 可以
2.一個(gè)作者可以寫多本書 ?# 可以
結(jié)論：只要兩個(gè)都可以，那他們就是多對(duì)多的關(guān)系
'''一對(duì)一'''
站在不同的角度去判斷
1.一個(gè)人可以有多個(gè)個(gè)人信息 ?# 不可以
2.一個(gè)個(gè)人信息可以是多個(gè)人 ?# 不可以
結(jié)論：只要兩個(gè)都不可以，那他們就是一對(duì)一的關(guān)系
SQL語句實(shí)現(xiàn)表關(guān)系
關(guān)鍵字：
foreign key(關(guān)聯(lián)的z名) references 被關(guān)聯(lián)的表(id)
'''
創(chuàng)建帶有表關(guān)系的表
1.先創(chuàng)建基表
2.再添加外鍵關(guān)系
'''
1.實(shí)現(xiàn)一對(duì)多，一對(duì)多的外鍵要建在多的一方
create table dep(id int primary key auto_increment,name varchar(32),descript varchar(64));
# 要先創(chuàng)建不帶外鍵的表
create table userinfo(id int primary key auto_increment,username varchar(16),salary decimal(8,2),dep_id int,foreign key(dep_id) references dep(id));
insert into dep values('技術(shù)部','搞技術(shù)');
insert into dep values('講師部','講課');
insert into dep values('外交部','公關(guān)');
# 輸入也要先輸入不帶外鍵的表
insert into userinfo(username,salary,dep_id) values('egon',10000,1);
# 使用的外鍵id必須是關(guān)聯(lián)的表內(nèi)有的id，不然報(bào)錯(cuò)
2.實(shí)現(xiàn)多對(duì)多
# 多對(duì)多一定要?jiǎng)?chuàng)建第三張表
# 先創(chuàng)建基表
create table book(id int primary key auto_increment,title varchar(16),price int);
create table author(id int primary key auto_increment,name varchar(16),phone int);
# 再創(chuàng)建第三張表進(jìn)行關(guān)聯(lián)
create table book2author(id int primary key auto_increment,book_id int,author_id int,foreign key (book_id) references author(id)on update cascade on delete cascade, foreign key (author_id) references book(id) on update cascade on delete cascade);
insert into author(name,phone) values('egon',110);
insert into book(title,price) values('egon歷險(xiǎn)記',100);
insert into book2author(book_id,author_id) values(1,1);
# 將編號(hào)1的書與編號(hào)1的作者進(jìn)行綁定
3.實(shí)現(xiàn)一對(duì)一
# 用于將用戶不常用的信息擇出來，放入另一個(gè)表中，一一對(duì)應(yīng)
# 外鍵建在任何一個(gè)表中都可以，但是推薦建在查詢頻率高的表中
# 表的id由于只是經(jīng)過主鍵限制，所以還是可以重復(fù)使用同一個(gè)id，需要使用unique使其保證唯一
create table author_detail(id int primary key auto_increment,phone varchar(16));
create table author(id int primary key auto_increment,name varchar(16),author_detail_id int unique,foreign key(author_detail_id) references author_detail(id));
級(jí)聯(lián)更新級(jí)聯(lián)刪除
關(guān)鍵字：
on update cascade ?# 級(jí)聯(lián)更新
on delete cascade ?# 級(jí)聯(lián)刪除
不帶外鍵的表的id不能刪除、修改，如果非要修改，只能一榮共榮一損俱損，一起消失或一起修改
create table dep_1(id int primary key auto_increment,name varchar(32),descript varchar(64));
create table userinfo_1(id int primary key auto_increment,username varchar(16),salary decimal(8,2),dep_id int,foreign key(dep_id) references dep_1(id) on update cascade on delete cascade);
insert into dep_1(name,descript) values('技術(shù)部','搞技術(shù)');
insert into dep_1(name,descript) values('外交部','搞外交');
insert into dep_1(name,descript) values('講師部','搞講課');
insert into userinfo_1(username,salary,dep_id) values('egon',10000,3);
update dep_1 set id=200 where id=3;
select * from userinfo_1;
# +----+----------+----------+--------+
# | id | username | salary ? | dep_id |
# +----+----------+----------+--------+
# | ?1 | egon ? ? | 10000.00 | ? ?200 |
# +----+----------+----------+--------+
查詢關(guān)鍵字之where
1.查找id大于等于3小于等于6的數(shù)據(jù)
select * from emp where id>=3 and id<=6;
select * from emp where id between 3 and 6;
# 關(guān)鍵字between，顧頭也顧尾
2.查詢薪資是20000或者是18000或者是17000的
select * from emp where salary = 20000 or salary = 17000 or salary = 18000;
select * from emp where salary in (20000,17000,18000);
# 關(guān)鍵詞in，查找的值滿足in后面括號(hào)的條件
# 題外話，not可以取反，所以not in就是查找不滿足后面括號(hào)的條件
# not in不走索引
'''
模糊查詢
關(guān)鍵字
like
關(guān)鍵符號(hào)
%:匹配任意個(gè)數(shù)的任意字符
_:匹配單個(gè)個(gè)數(shù)的任意字符
'''
3.查詢姓名中帶有字母o的員工姓名和薪資
select name,salary from emp where name like '%o%'；
# 兩個(gè)%的解釋：如果前面帶%表示是o結(jié)尾的，在后面帶%表示是o開頭的，兩頭都加表示任意一個(gè)地方帶有o都可以查詢
# like后開頭帶%，不走索引
4.查詢姓名由四個(gè)字符組成的員工姓名和薪資
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;
# 內(nèi)置函數(shù)char_length，相當(dāng)于python中給字符串專用的len
5.查詢id小于3或者大于6的數(shù)據(jù)
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;
6.查詢薪資不是20000或者是18000或者是17000的數(shù)據(jù)
select * from emp where salary not in (20000,17000,18000);
7.查詢崗位描述為空的員工名與崗位名
# null不能用等號(hào)，只能用is
select * from emp where post_comment is null;
# 需要注意，null和空字符串并不是一回事，兩者不一樣，查null查不到空字符串，反之亦如此
查詢關(guān)鍵字之group by分組
分組：將單個(gè)的個(gè)體按照指定的條件分成一個(gè)個(gè)整體
'''
分組之后默認(rèn)只能直接獲取到分組的依據(jù)
其他字段無法再直接獲取(可以間接獲取)
就是說只能直接獲取到你按照什么分組的，不能直接獲取到其他字段
'''
1.查詢每個(gè)部門的最高薪資
select name,post,max(salary) from emp group by post;
2.查詢每個(gè)部門的最低薪資
select name,post,min(salary) from emp group by post;
3.查詢每個(gè)部門的平均薪資
select name,post,avg(salary) from emp group by post;
4.查詢每個(gè)部門的人數(shù)
select post,count(post) from emp group by post;
5.每個(gè)部門的月工資總和
select post,sum(salary) from emp group by post;
'''
可以給字段起別名(as還可以給表起別名)
1.在字段后+as+空格+'別名'
2.在字段后+空格+'別名' ?# 不建議使用
select post,sum(salary) as '月工資總和' from emp group by post;
'''
聚合函數(shù)
分組之后頻繁需要使用的
max() ?# 最大值
min() ?# 最小值
sum() ?# 求和
count() ?# 計(jì)數(shù)
avg() ?# 平均值
concat() ?# 拼接
group_concat() ?# 分組之后拼接
concat_ws() ?# 將除第一個(gè)字符以外的拼接，第一個(gè)字符卡在每個(gè)字符中間
學(xué)習(xí)用數(shù)據(jù)
create table emp(
?id int not null unique auto_increment,
?name varchar(20) not null,
?sex enum('male','female') not null default 'male', #大部分是男的
?age int(3) unsigned not null default 28,
?hire_date date not null,
?post varchar(50),
?post_comment varchar(100),
?salary double(15,2),
?office int, #一個(gè)部門一個(gè)屋子
?depart_id int
);
#插入記錄
#三個(gè)部門：教學(xué)，銷售，運(yùn)營
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','張江第一帥形象代言',7300.33,401,1), #以下是教學(xué)部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是銷售部門
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('樂樂','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龍','male',28,'20160311','operation',10000.13,403,3), #以下是運(yùn)營部門
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬銀','female',18,'20130311','operation',19000,403,3),
('程咬銅','male',18,'20150411','operation',18000,403,3),
('程咬鐵','female',18,'20140512','operation',17000,403,3);
https://www.wanxiangsucai.com/read/CV9902
