您好,登錄后才能下訂單哦!
先創(chuàng)創(chuàng)建一個(gè)表用于測(cè)試
-- 創(chuàng)建數(shù)據(jù)庫(kù) CREATE DATABASE dbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci; -- 創(chuàng)建表 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;
進(jìn)入dbname數(shù)據(jù)庫(kù) mysql> use dbname Database changed # 查看當(dāng)前庫(kù)所有的表 mysql> show tables; +------------------+ | Tables_in_dbname | +------------------+ | tb | +------------------+ 1 row in set (0.00 sec) # 查看tb表內(nèi)的內(nèi)容 mysql> select * from tb; Empty set (0.00 sec)
-- 插入單條數(shù)據(jù) insert into tb(name,email,password) values("ansheng","anshengme.com@gmail.com","as"); -- 同時(shí)插入多條數(shù)據(jù) insert into tb(name,email,password) values("as","i@anshengme.com","pwd"),("info","info@anshengme.com","i");
查看插入的數(shù)據(jù)
mysql> select * from tb; +----+---------+-------+-------------------------+----------+-------------+ | id | name | alias | email | password | phone | +----+---------+-------+-------------------------+----------+-------------+ | 2 | ansheng | NULL | anshengme.com@gmail.com | as | 13800138000 | | 3 | as | NULL | i@anshengme.com | pwd | 13800138000 | | 4 | info | NULL | info@anshengme.com | i | 13800138000 | +----+---------+-------+-------------------------+----------+-------------+ 3 rows in set (0.00 sec)
把別的表的數(shù)據(jù)插入當(dāng)前表
查看tb_copy表內(nèi)的內(nèi)容
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表內(nèi)的name,email,password列插入到tb表中
insert into tb (name, email, password) select name,email,password from tb_copy;
查詢tb內(nèi)的內(nèi)容
mysql> select * from tb; +----+---------+-------+-------------------------+----------+-------------+ | id | name | alias | email | password | phone | +----+---------+-------+-------------------------+----------+-------------+ | 2 | ansheng | NULL | anshengme.com@gmail.com | as | 13800138000 | | 3 | as | NULL | i@anshengme.com | pwd | 13800138000 | | 4 | info | NULL | info@anshengme.com | i | 13800138000 | | 5 | hello | NULL | NULL | 1 | 13800138000 | | 6 | word | NULL | NULL | 2 | 13800138000 | | 7 | python | NULL | NULL | 3 | 13800138000 | +----+---------+-------+-------------------------+----------+-------------+ 6 rows in set (0.00 sec)
-- 刪除表內(nèi)的所有內(nèi)容 delete from tb_copy;
-- 刪除表內(nèi)某一條數(shù)據(jù) delete from tb where id=2 and name="ansheng";
update tb set name="as" where id="3";
-- 查詢表內(nèi)所有內(nèi)容 select * from tb; -- 帶條件的查詢表內(nèi)的內(nèi)容 select * from tb where id > 4;
查詢的時(shí)候指定最后一列的名稱
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; -- 查詢括號(hào)內(nèi)存在的數(shù)據(jù) select * from tb where id in (4,6); -- 查詢括號(hào)內(nèi)不存在的數(shù)據(jù) select * from tb where id not in (4,6); -- 以別的表的內(nèi)容為查詢條件 select * from tb where id in (select id from tb_copy);
通配符
-- 以p開(kāi)頭的所有(多個(gè)字符串) select * from tb where name like "p%"; -- 以p開(kāi)頭的所有(一個(gè)字符) select * from tb where name like "p%";
限制
-- 前三行數(shù)據(jù) select * from tb limit 3; -- 從第2行開(kāi)始的3行 select * from tb limit 2,3; -- 從第4行開(kāi)始的5行 select * from tb limit 5 offset 4;
排序
-- 根據(jù)"name"列從小到大排列 select * from tb order by name asc; -- 根據(jù)"name"列從大到小排列 select * from tb order by name desc; -- 根據(jù) “列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之前
連表
無(wú)對(duì)應(yīng)關(guān)系則不顯示
select A.num, A.name, B.name from A,B where A.nid = B.nid;
無(wú)對(duì)應(yīng)關(guān)系則不顯示
select A.num, A.name, B.name from A inner join B on A.nid = B.nid;
A表所有顯示,如果B中無(wú)對(duì)應(yīng)關(guān)系,則值為null
select A.num, A.name, B.name from A left join B on A.nid = B.nid;
B表所有顯示,如果B中無(wú)對(duì)應(yīng)關(guān)系,則值為null
select A.num, A.name, B.name from A right join B on A.nid = B.nid;
組合
組合,自動(dòng)處理重合
select nickname from A union select name from B;
組合,不處理重合
select nickname from A union all select name from B;
#Python全棧之路
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。