一.mysql基本命令

?

1.基礎(chǔ)常用命令

mysql -uroot -p密碼;(也可以不帶密碼，之后輸入) 本地登錄

?

mysql -h 登錄ip -p 端口(通常3306） -uroot -p密碼; 遠(yuǎn)程登錄

?

desc 表名;查看表的各個(gè)字段的屬性，以及自增鍵

?

mysqldump -u用戶 -p 數(shù)據(jù)庫(kù)名 > xx.sql; 導(dǎo)出數(shù)據(jù)庫(kù)文件，保存

?

mysql -u用戶 -p密碼 數(shù)據(jù)庫(kù)名< xx.sql;導(dǎo)入數(shù)據(jù)庫(kù)文件(也可以選擇登錄進(jìn)去，在選擇數(shù)據(jù)庫(kù)后，使用source命令導(dǎo)入數(shù)據(jù)）

?

2.創(chuàng)建命令

create user '用戶名' @'ip' identified by '密碼'; 創(chuàng)建用戶

?

ip是指用戶登錄mysql的電腦ip，可以寫%，本地寫localhost

?

grant 權(quán)限(select/insert/updata/all priveleges) on 表/數(shù)據(jù)庫(kù)名 to '用戶'@'ip' identified by '密碼'; 用戶授權(quán)

?

drop user 用戶名@ip 刪除用戶

?

show databases; 查數(shù)據(jù)庫(kù)

?

show tables; 看表

?

create database 數(shù)據(jù)庫(kù)名 default charset utf8; 創(chuàng)建數(shù)據(jù)庫(kù)

?

create table 表名(列名 數(shù)據(jù)類型 約束···，列名 數(shù)據(jù)類型 約束···）engine=innodb default charset=utf8 創(chuàng)建表

?

其中數(shù)據(jù)類型種類 數(shù)字(int，tinyint,smallint,float,double),字符串(char(個(gè)數(shù))varchar(個(gè)數(shù)))時(shí)間(DATE,TIME,DATETIME),枚舉enum(值只能是枚舉中的元素），集合set(值只能是結(jié)合元素的組合)

?

create table 表名(列名 數(shù)據(jù)類型 primary key auto_increment,列名 數(shù)據(jù)類型 約束···）engine=innodb default charset=utf8 創(chuàng)建含主鍵的表

?

create table 表名(列名 數(shù)據(jù)類型 primary key,列名 數(shù)據(jù)類型 constraint fk_外鍵名 foreign key (列名[,]) references 表名2（列名[,]）···）engine=innodb default charset=utf8 創(chuàng)建含外鍵的表

?

2.查表命令

條件查詢

select * from 表名;

?

select 列名···from 表名;

?

select 列名 from 表名 where 列名(id等) >/</!= value;

?

select 列名，常量 from 表名; 增加一個(gè)常量列

?

select 列名 from 表名 where 列名 in/not in/between and value;

?

select 列名 from 表名 where 條件1 and 條件2;

?

特殊的：select 列名 from 表名 where 列名 in (select 列名(只能一列) from 表名);

?

select 列名 from 表名 where 列名 like 'xx%'/'%xx'/"xx_"; 查詢以xx開頭/xx結(jié)尾 %代表任意位，_代表一位

?

select xx,(select xx from ...) from ..... 查詢條件做常量值

?

分頁

select 列名 from 表名 limit num; 顯示num個(gè)

?

select 列名 from 表名 limit num1,num2;從num1后取num2行數(shù)據(jù)，num1是起始位置，num2是個(gè)數(shù)

?

select 列名 from 表名 limit num1 offset num2;從num2后取num1行數(shù)據(jù)，num2是起始位置，num1是個(gè)數(shù)

?

排序

select * from 表名 order by 列名 desc;從大到小排序

?

select * from 表名 order by 列名 asc;從小到大排序

?

select * from 表名 order by 列名1 desc 列名2 asc; 首先遵循列1從大到小排序，遇到相同數(shù)據(jù)時(shí)，按列2從小到大排序

?

分組操作

select count/sum/max/min/avg(列名1），列名2 from 表名 group by 列名(通常是列名2）；分組操作?

?

select count/sum/max/min/avg(列名1），列名2 from 表名 group by 列名(通常是列名2）having 條件；分組操作后篩選

?

連表操作

select * from 表1 left join 表2 on 表1.列名=表2.列名; 左連接

?

select * from 表1 right join 表2 on 表1.列名=表2.列名; 右連接

?

select * from 表1 inner join 表2 on 表1.列名=表2.列名; 內(nèi)連接

?

注意:如果超過3個(gè)表聯(lián)合操作，如果其中兩個(gè)表操作時(shí)已經(jīng)改變了表結(jié)構(gòu)，應(yīng)該將這兩個(gè)表操作的結(jié)果作為一個(gè)臨時(shí)表再與第三個(gè)表聯(lián)合操作。

?

臨時(shí)表

?

(select * from 表名)as e

?

3.刪除，修改，插入命令

插入

insert into 表名(列名1，列名2···) values(值1，值2···),(值1，值2···),(值1，值2···); 插入值

?

insert into 表名1(列名) select 列名 from 表2; 在一個(gè)表中插入另一個(gè)表中數(shù)據(jù)

?

修改

update 表名 set 列名1=value1，列名2=value2 where 條件1 [and/or 條件2]；

?

刪除

delete from 表名; 清除表(如果有自增id，id 不會(huì)重新開始)

?

delete from 表名 where 條件； 清除特定數(shù)據(jù)

?

truncate table 表名;清除表(如果有自增id，id 會(huì)重新開始)

?

4.修改表結(jié)構(gòu)

alter table 表名 auto_increment=value;設(shè)置自增鍵起始值；

?

alter table 表名 drop 列名;刪除列

?

alter table 表名 add 列名 數(shù)據(jù)類型 約束; 增加列

?

alter table 表名 change 舊列名 新列名 數(shù)據(jù)類型; 修改字段類型

?

alter table 表名 modify 列名 數(shù)據(jù)類型; 修改數(shù)據(jù)類型

?

alter table 舊表名 rename 新表名; 修改表名

?

alter table 表名 drop primary key; 刪除表中主鍵

?

alter table 表名 add 列名 數(shù)據(jù)類型 primary key;添加主鍵

?

alter table 表名 add primary key(列名);設(shè)置主鍵

?

alter table 表名 add column 列名 數(shù)據(jù)類型 after 列名;在某一列后添加主鍵

?

二.mysql進(jìn)階

1.外鍵

外鍵適用于一對(duì)多，一對(duì)一，多對(duì)多三種情況

?

一對(duì)多

?

典型案例員工與部門，一個(gè)部門對(duì)應(yīng)于多個(gè)員工，一個(gè)員工對(duì)應(yīng)于一個(gè)部分，所以要在員工表中設(shè)置部門id列，并設(shè)置為外鍵，與部門表id關(guān)聯(lián)。

?

一對(duì)一

?

案例博客園用戶與博客，不是每個(gè)用戶都寫博客，寫博客的用戶與擁有的博客地址一一對(duì)應(yīng)，所以在博客用戶表user中設(shè)置blog_id，設(shè)置成外鍵和唯一索引，與博客表blog中id關(guān)聯(lián)

?

create table user(id int not null auto_increment primary key,name char(10), blog_id int,unique uq1(blog_id),constraint fk_user_blog foreign key(blog_id) references blog(id))engine=innodb default charset=utf8;

?

多對(duì)多

?

典型案例電腦與用戶，一個(gè)用戶可以使用多臺(tái)電腦，一臺(tái)電腦對(duì)應(yīng)多個(gè)用戶，多對(duì)多，此時(shí)一般選擇新建一個(gè)表contact，在其中設(shè)置兩個(gè)外鍵，同時(shí)關(guān)聯(lián)用戶表id與電腦表id

?

create table contact(id int not null auto_increment primary key,user_id int,computer_id int,unique uq2(user_id,computer_id),constraint fk_user foreign key(user_id) references user(id),constraint fk_user foreign key(computer_id) references computer(id))engine=innodb default charset=utf8;

?

可以看情況決定要不要把兩個(gè)外鍵弄成聯(lián)合唯一索引。

?

2.自增

show create table 表名 [\G];查看表的創(chuàng)建信息

?

對(duì)于自增鍵，我們可以設(shè)置它的初始值以及與自增步長(zhǎng)

?

alter table auto_increment=value;設(shè)置自增初始值

?

對(duì)于自增步長(zhǎng)有兩種類型：

?

第一種：基于會(huì)話級(jí)別(只對(duì)本次登錄有效)

?

show session variables like "auto_incre%"; 查看步長(zhǎng)

?

set session auto_increment_increment=value; 改變步長(zhǎng)

?

set session auto_increment_offest=value; 設(shè)置起始值(不常用，因?yàn)橛兄暗腶lter)

?

第二種：基于全局級(jí)別(對(duì)所有會(huì)話都有用)

?

set global auto_increment_increment=value; 設(shè)置全局步長(zhǎng)

?

3.pymysql

pymysql 是python第三方模塊，主要用于python與mysql交互。

?

pymysql 方法:

?

connect(host=,user=,passwd=,database=) 連接數(shù)據(jù)庫(kù)

?

cursor() 建立游標(biāo)

?

execute(sql)執(zhí)行sql語句

?

fetchone()從查詢結(jié)果中取出一條數(shù)據(jù)

?

fetchmany(num)從查詢結(jié)果中取出num條數(shù)據(jù)

?

fetchall()從查詢結(jié)果中取出所有數(shù)據(jù)

?

commit() 改變(updata，insert，drop)數(shù)據(jù)庫(kù)內(nèi)容專用,execute sql語句后必須commit才能真正改變數(shù)據(jù)庫(kù)

?

close()注意最后有兩個(gè)close,游標(biāo)要關(guān)閉，連接也要關(guān)閉

?

sql注入

sql注入就是通過把SQL命令插入到Web表單提交或輸入域名或頁面請(qǐng)求的查詢字符串，最終達(dá)到欺騙服務(wù)器執(zhí)行惡意的SQL命令。

?

實(shí)例如下：

?

假設(shè)我們輸入用戶名和密碼，然后登錄數(shù)據(jù)庫(kù)，在數(shù)據(jù)庫(kù)中查找用戶和密碼是否存在，存在即登錄成功，否則登錄失敗

?

?

?

程序如上，一開始我們輸入數(shù)據(jù)庫(kù)存在的用戶和密碼，自然能夠登錄成功

?

?

?

輸入錯(cuò)誤用戶和密碼，也能成功

?

?

?

追究其原有，原因出在sql語句字符串拼接上，拼接的字符串為 ‘’ or 1=1 -- ‘‘ and passwd='123',有1=1，當(dāng)然成立

?

解決辦法：將要拼接的內(nèi)容放入execute括號(hào)中，由其自己拼接

?

視圖

?

視圖是一種虛擬存在的表，本身不包含數(shù)據(jù)，作為一個(gè)select語句保存在數(shù)據(jù)庫(kù)中。通俗的講視圖代表的是一條select語句產(chǎn)生的結(jié)果集。

?

create view 視圖名稱 as select語句; 創(chuàng)建視圖?

?

視圖單獨(dú)不能插入，修改數(shù)據(jù)，因?yàn)橐晥D是虛擬的。但可以修改select語句，改變視圖結(jié)果集。

?

alter view 視圖名稱 as sql語句; 修