Python全棧之路系列之MySQL表內操作

標籤：values   ble   data   更改   進入   upd   database   Nid   sele   

先創建立一個表用於測試

-- 建立資料庫CREATE DATABASE dbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;-- 建立表CREATE TABLE `tb` (  `id` int(5) NOT NULL AUTO_INCREMENT,  `name` char(15) NOT NULL,  `alias` varchar(10) DEFAULT NULL,  `email` varchar(30) DEFAULT NULL,  `password` varchar(20) NOT NULL,  `phone` char(11) DEFAULT ‘13800138000‘,  PRIMARY KEY (`id`,`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

增加表內資料

# 進入dbname資料庫mysql> use dbnameDatabase changed# 查看當前庫所有的表mysql> show tables;+------------------+| Tables_in_dbname |+------------------+| tb               |+------------------+1 row in set (0.00 sec)# 查看tb表內的內容mysql> select * from tb;Empty set (0.00 sec)

-- 插入單條資料insert into tb(name,email,password) values("xxx","[email protected]","xx");-- 同時插入多條資料insert into tb(name,email,password) values("as","xxxx.com","pwd"),("info","xxx.com","i");

查看插入的資料

mysql> select * from tb;把別的表的資料插入當前表

查看tb_copy表內的內容

mysql> select * from tb_copy;+----+--------+-------+-------+----------+-------------+| id | name   | alias | email | password | phone       |+----+--------+-------+-------+----------+-------------+|  5 | hello  | NULL  | NULL  | 1        | 13800138000 ||  6 | word   | NULL  | NULL  | 2        | 13800138000 ||  7 | python | NULL  | NULL  | 3        | 13800138000 |+----+--------+-------+-------+----------+-------------+3 rows in set (0.00 sec)

把tb_copy表內的name,email,password列插入到tb表中

insert into tb (name, email, password) select name,email,password from tb_copy;

查詢tb內的內容

mysql> select * from tb;6 rows in set (0.00 sec)

刪除表內資料

-- 刪除表內的所有內容delete from tb_copy;

-- 刪除表內某一條資料delete from tb where id=2 and name="xxx";

更改表內資料

update tb set name="as" where id="3";

查

-- 查詢表內所有內容select * from tb;-- 帶條件的查詢表內的內容select * from tb where id > 4;

查詢的時候指定最後一列的名稱

mysql> select id,name as username from tb where id > 4;+----+----------+| id | username |+----+----------+|  5 | hello    ||  6 | word     ||  7 | python   |+----+----------+3 rows in set (0.00 sec)

其他動作

條件

-- 多條件查詢select * from tb where id>3 and name="hello" and password="1";-- 查詢指定範圍select * from tb where id between 4 and 6;-- 查詢括弧記憶體在的資料select * from tb where id in (4,6);-- 查詢括弧內不存在的資料select * from tb where id not in (4,6);-- 以別的表的內容為查詢條件select * from tb where id in (select id from tb_copy);

萬用字元

-- 以p開頭的所有（多個字串）select * from tb where name like "p%";-- 以p開頭的所有（一個字元）select * from tb where name like "p%";

限制

-- 前三行資料select * from tb limit 3;-- 從第2行開始的3行select * from tb limit 2,3;-- 從第4行開始的5行select * from tb limit 5 offset 4;

排序

-- 根據"name"列從小到大排列select * from tb order by name asc;-- 根據"name"列從大到小排列select * from tb order by name desc;-- 根據 “列1” 從大到小排列，如果相同則按列2從小到大排序select * from 表 order by 列1 desc,列2 asc;

分組

select id from tb group by id;select id,name from tb group by id,name;select num,nid from 表  where nid > 10 group by num,nid order nid desc;select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid;select num from 表 group by num having max(id) > 10;

特別的：group by 必須在where之後，order by之前

連表

無對應關係則不顯示

select A.num, A.name, B.name from A,B where A.nid = B.nid;

無對應關係則不顯示

select A.num, A.name, B.name from A inner join B on A.nid = B.nid;

A表所有顯示，如果B中無對應關係，則值為null

select A.num, A.name, B.name from A left join B on A.nid = B.nid;

B表所有顯示，如果B中無對應關係，則值為null

select A.num, A.name, B.name from A right join B on A.nid = B.nid;

組合

組合，自動處理重合

select nickname from A union select name from B;

組合，不處理重合

select nickname from A union all select name from B;

Python全棧之路系列之MySQL表內操作