SQL語言 · Python / Linux / Mysql / 軟件測試等等

[TOC] # **SQL語言** * 數據表操作：創建、刪除 * 數據操作：增加、刪除、修改、簡單查詢 * 數據操作：查詢 > 此部分中查詢為重點，需要熟練掌握 * SQL語言編寫和運行 * 鼠標左鍵點擊某個數據庫下面的查詢按鈕，然后點擊新建查詢 ![](https://i.niupic.com/images/2020/08/03/8udi.png) * 在打開的查詢編輯器中，編寫SQL語言，再點擊運行 ![](https://i.niupic.com/images/2020/08/03/8udk.png) ## **數據表操作** **創建表** ``` create table 表名( 字段名類型約束, 字段名類型約束 ... ); ``` 例：創建學生表，字段要求如下：姓名(長度為10) ``` create table students( name varchar(10) ); ``` 例：創建學生表，字段要求如下：姓名(長度為10)，年齡 ``` create table students( name varchar(10), age int unsigned ); ``` 例：創建學生表，字段要求如下：姓名(長度為10)，年齡，身高(保留小數點2位) ``` create table students( id int unsigned primary key auto_increment, name varchar(20), age int unsigned, height decimal(5,2) ); ``` 刪除表 ``` 格式一：drop table 表名; 格式二：drop table if exists 表名; ``` 例：刪除學生表 ``` drop table students; 或 drop table if exists students; ``` ## **數據操作\-增刪改查** **簡單查詢** ``` select * from 表名; 例：查詢所有學生數據 select * from students; ``` **添加數據** 添加一行數據格式一：所有字段設置值，值的順序與表中字段的順序對應 * 說明：主鍵列是自動增長，插入時需要占位，通常使用0或者 default 或者 null 來占位，插入成功后以實際數據為準 ``` insert into 表名 values(...); ``` 例：插入一個學生，設置所有字段的信息 ``` insert into students values(0,'亞瑟',22,177.56); ``` 格式二：部分字段設置值，值的順序與給出的字段順序對應 ``` insert into 表名(字段1,...) values(值1,...); ``` 例：插入一個學生，只設置姓名 ``` insert into students(name) values('老夫子'); ``` 添加多行數據方式一：寫多條insert語句，語句之間用英文分號隔開 ``` insert into students(name) values('老夫子2'); insert into students(name) values('老夫子3'); insert into students values(0,'亞瑟2',23,167.56); ``` 方式二：寫一條insert語句，設置多條數據，數據之間用英文逗號隔開 ``` 格式一：insert into 表名 values(...),(...)... 例：插入多個學生，設置所有字段的信息 insert into students values(0,'亞瑟3',23,167.56),(0,'亞瑟4',23,167.56); ``` ``` 格式二：insert into 表名(列1,...) values(值1,...),(值1,...)... 例：插入多個學生，只設置姓名 insert into students(name) values('老夫子5'),('老夫子6'); ``` 修改 ``` 格式：update 表名 set 列1=值1,列2=值2... where 條件; ``` 例：修改id為5的學生數據，姓名改為狄仁杰，年齡改為 20 ``` update students set name='狄仁杰',age=20 where id=5; ``` 刪除 ``` 格式：delete from 表名 where 條件; ``` 例：刪除id為6的學生數據 ``` delete from students where id=6; ``` 邏輯刪除：對于重要的數據，不能輕易執行delete語句進行刪除，一旦刪除，數據無法恢復，這時可以進行邏輯刪除。 1、給表添加字段，代表數據是否刪除，一般起名isdelete，0代表未刪除，1代表刪除，默認值為0 2、當要刪除某條數據時，只需要設置這條數據的isdelete字段為1 3、以后在查詢數據時，只查詢出isdelete為0的數據 ``` 例： 1、給學生表添加字段（isdelete），默認值為0，如果表中已經有數據，需要把所有數據的isdelete字段更新為0 update students set isdelete=0 2、刪除id為1的學生 update students set isdelete=1 where id=1; 3、查詢未刪除的數據 select * from students where iddelete=0; ``` ## **數據操作\-查詢** **創建數據表** ``` drop table if exists students; create table students ( studentNo varchar(10) primary key, name varchar(10), sex varchar(1), hometown varchar(20), age tinyint(4), class varchar(10), card varchar(20) ); ``` **準備數據** ``` insert into students values ('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'), ('002', '諸葛亮', '男', '上海', '18', '2班', '340322199002242354'), ('003', '張飛', '男', '南京', '24', '3班', '340322199003247654'), ('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'), ('005', '大喬', '女', '天津', '19', '3班', '340322199004247654'), ('006', '孫尚香', '女', '河北', '18', '1班', '340322199006247654'), ('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'), ('008', '小喬', '女', '河南', '15', '3班', null), ('009', '百里守約', '男', '湖南', '21', '1班', ''), ('010', '妲己', '女', '廣東', '26', '2班', '340322199607247654'), ('011', '李白', '男', '北京', '30', '4班', '340322199005267754'), ('012', '孫臏', '男', '新疆', '26', '3班', '340322199000297655'); ``` 查詢所有字段 ``` select * from 表名; 例： select * from students; ``` 查詢指定字段在select后面的列名部分，可以使用as為列起別名，這個別名出現在結果集中 ``` select 列1,列2,... from 表名; -- 表名.字段名 select students.name,students.age from students; -- 可以通過 as 給表起別名 select s.name,s.age from students as s; -- 如果是單表查詢可以省略表明 select name,age from students; - 使用as給字段起別名 select studentNo as 學號,name as 名字,sex as 性別 from students; ``` 消除重復行在select后面列前使用distinct可以消除重復的行 ``` select distinct 列1,... from 表名; 例： select distinct sex from students; ``` ### **條件** * 使用where子句對表中的數據篩選，符號條件的數據會出現在結果集中 * 語法如下： ``` select 字段1,字段2... from 表名 where 條件; 例： select * from students where id=1; ``` * where后面支持多種運算符，進行條件的處理 * * 比較運算 * * 邏輯運算 * * 模糊查詢 * * 范圍查詢 * * 空判斷 **比較運算符** * * 等于: = * * 大于: > * * 大于等于: >= * * 小于: < * * 小于等于: <= * * 不等于: != 或 <> 例1：查詢小喬的年齡 ``` select age from students where name='小喬' ``` 例2：查詢20歲以下的學生 ``` select * from students where age<20 ``` 例3：查詢家鄉不在北京的學生 ``` select * from students where hometown!='北京' ``` 練習： ``` 1、查詢學號是'007'的學生的身份證號 2、查詢'1班'以外的學生信息 3、查詢年齡大于20的學生的姓名和性別 ``` **邏輯運算符** * and * or * not 例1：查詢年齡小于20的女同學 ``` select * from students where age<20 and sex='女' ``` 例2：查詢女學生或'1班'的學生 ``` select * from students where sex='女' or class='1班' ``` 例3：查詢非天津的學生 ``` select * from students where not hometown='天津' ``` 練習： ``` 1、查詢河南或河北的學生 2、查詢'1班'的'上海'的學生 3、查詢非20歲的學生 ``` **模糊查詢** * like * %表示任意多個任意字符 * _表示一個任意字符例1：查詢姓孫的學生 ``` select * from students where name like '孫%' ``` 例2：查詢姓孫且名字是一個字的學生 ``` select * from students where name like '孫_' ``` 例3：查詢叫喬的學生 ``` select * from students where name like '%喬' ``` 例4：查詢姓名含白的學生 ``` select * from students where name like '%白%' ``` 練習： ``` 1、查詢姓名為兩個字的學生 2、查詢姓百且年齡大于20的學生 3、查詢學號以1結尾的學生 ``` 范圍查詢 * in表示在一個非連續的范圍內例1：查詢家鄉是北京或上海或廣東的學生 ``` select * from students where hometown in('北京','上海','廣東') ``` * between ... and ...表示在一個連續的范圍內例2：查詢年齡為18至20的學生 ``` select * from students where age between 18 and 20 ``` 練習： ``` 1、查詢年齡在18或19或22的女生 2、查詢年齡在20到25以外的學生 ``` **空判斷** * 注意：null與''是不同的 * 判空is null 例1：查詢沒有填寫身份證的學生 ``` select * from students where card is null ``` 判非空is not null 例2：查詢填寫了身份證的學生 ``` select * from students where card is not null ``` ### **排序** * 為了方便查看數據，可以對數據進行排序 * 語法： ``` select * from 表名 order by 列1 asc|desc,列2 asc|desc,... ``` * 將行數據按照列1進行排序，如果某些行列1的值相同時，則按照列2排序，以此類推 * 默認按照列值從小到大排列 * asc從小到大排列，即升序 * desc從大到小排序，即降序例1：查詢所有學生信息，按年齡從小到大排序 ``` select * from students order by age ``` 例2：查詢所有學生信息，按年齡從大到小排序，年齡相同時，再按學號從小到大排序 ``` select * from students order by age desc,studentNo ``` 練習： ``` 1、查詢所有學生信息，按班級從小到大排序，班級相同時，再按學號再按學號從小到大排序 ``` ### **聚合函數** * 為了快速得到統計數據，經常會用到如下5個聚合函數 * count(*)表示計算總行數，括號中寫星與列名，結果是相同的 * 聚合函數不能在 where 中使用例1：查詢學生總數 ``` select count(*) from students; ``` * max(列)表示求此列的最大值例2：查詢女生的最大年齡 ``` select max(age) from students where sex='女'; ``` * min(列)表示求此列的最小值例3：查詢1班的最小年齡 ``` select min(age) from students; ``` * sum(列)表示求此列的和例4：查詢北京學生的年齡總和 ``` select sum(age) from students where hometown='北京'; ``` * avg(列)表示求此列的平均值例5：查詢女生的平均年齡 ``` select avg(age) from students where sex='女' ``` 練習： ``` 1、查詢所有學生的最大年齡、最小年齡、平均年齡 2、一班共有多少個學生 3、查詢3班年齡小于18歲的同學有幾個 ``` ### **分組** * 按照字段分組，表示此字段相同的數據會被放到一個組中 * 分組后，分組的依據列會顯示在結果集中，其他列不會顯示在結果集中 * 可以對分組后的數據進行統計，做聚合運算語法： ``` select 列1,列2,聚合... from 表名 group by 列1,列2... ``` 例1：查詢各種性別的人數 ``` select sex,count(*) from students group by sex ``` 例2：查詢各種年齡的人數 ``` select age,count(*) from students group by age ``` 練習 ``` 查詢各個班級學生的平均年齡、最大年齡、最小年齡 ``` 分組后的數據篩選 * 語法： ``` select 列1,列2,聚合... from 表名 group by 列1,列2,列3... having 列1,...聚合... ``` * having后面的條件運算符與where的相同例1：查詢男生總人數 ``` 方案一 select count(*) from students where sex='男' ----------------------------------- 方案二： select sex,count(*) from students group by sex having sex='男' ``` 練習 ``` 查詢1班除外其他班級學生的平均年齡、最大年齡、最小年齡 ``` **對比where與having** * where是對from后面指定的表進行數據篩選，屬于對原始數據的篩選 * having是對group by的結果進行篩選 ### **分頁** **獲取部分行** * 當數據量過大時，在一頁中查看數據是一件非常麻煩的事情 * 語法 ``` select * from 表名 limit start,count ``` * 從start開始，獲取count條數據 * start索引從0開始例1：查詢前3行學生信息 ``` select * from students limit 0,3 ``` 練習 ``` 查詢第4到第6行學生信息 ``` 分頁 * 已知：每頁顯示m條數據，求：顯示第n頁的數據 ``` select * from students limit (n-1)*m,m ``` * 求總頁數 * 查詢總條數p1 * 使用p1除以m得到p2 * 如果整除則p2為總數頁 * 如果不整除則p2+1為總頁數練習： ``` 每頁顯示5條數據，顯示每一頁的數據 ``` ### **連接查詢** * 當查詢結果的列來源于多張表時，需要將多張表連接成一個大的數據集，再選擇合適的列返回 * 等值連接查詢：查詢的結果為兩個表匹配到的數據 ![](https://i.niupic.com/images/2020/08/04/8uoN.png) * 左連接查詢：查詢的結果為兩個表匹配到的數據加左表特有的數據，對于右表中不存在的數據使用null填充 ![](https://i.niupic.com/images/2020/08/04/8uoP.png) * 右連接查詢：查詢的結果為兩個表匹配到的數據加右表特有的數據，對于左表中不存在的數據使用null填充 ![](https://i.niupic.com/images/2020/08/04/8uoR.png) 準備數據 ``` drop table if exists courses; create table courses ( courseNo int(10) unsigned primary key auto_increment, name varchar(10) ); insert into courses values ('1', '數據庫'), ('2', 'qtp'), ('3', 'linux'), ('4', '系統測試'), ('5', '單元測試'), ('6', '測試過程'); ``` ``` drop table if exists scores; create table scores ( id int(10) unsigned primary key auto_increment, courseNo int(10), studentno varchar(10), score tinyint(4) ); insert into scores values ('1', '1', '001', '90'), ('2', '1', '002', '75'), ('3', '2', '002', '98'), ('4', '3', '001', '86'), ('5', '3', '003', '80'), ('6', '4', '004', '79'), ('7', '5', '005', '96'), ('8', '6', '006', '80'); ``` **等值連接** 方式一 ``` select * from 表1,表2 where 表1.列\=表2.列 ``` 方式二(又稱內連接) ``` select * from 表1 inner join 表2 on 表1.列=表2.列 ``` 例1：查詢學生信息及學生的成績 ``` select * from students stu, scores sc where stu.studentNo = sc.studentNo --------------------------------------- select * from students stu inner join scores sc on stu.studentNo = sc.studentNo ``` 例2：查詢課程信息及課程的成績 ``` select * from courses cs, scores sc where cs.courseNo = sc.courseNo --------------------------------------- select * from courses cs inner join scores sc on cs.courseNo = sc.courseNo ``` 例3：查詢學生信息及學生的課程對應的成績 ``` select * from students stu, courses cs, scores sc where stu.studentNo = sc.studentno and cs.courseNo = sc.courseNo --------------------------------------- select * from students stu inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on cs.courseNo = sc.courseNo ``` 例4：查詢王昭君的成績，要求顯示姓名、課程號、成績 ``` select stu.name, sc.courseNo, sc.score from students stu, scores sc where stu.studentNo = sc.studentNo and stu.name = '王昭君' --------------------------------------- select stu.name, sc.courseNo, sc.score from students stu inner join scores sc on stu.studentNo = sc.studentNo where stu.name = '王昭君' ``` 例5：查詢王昭君的數據庫成績，要求顯示姓名、課程名、成績 ``` select stu.name, cs.name, sc.score from students stu, scores sc, courses cs where stu.studentNo = sc.studentNo and sc.courseNo = cs.courseNo and stu.name = '王昭君' and cs.name = '數據庫' --------------------------------------- select stu.name, cs.name, sc.score from students stu inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on sc.courseNo = cs.courseNo where stu.name = '王昭君' and cs.name = '數據庫' ``` 例6：查詢所有學生的數據庫成績，要求顯示姓名、課程名、成績 ``` select stu.name, cs.name, sc.score from students stu, scores sc, courses cs where stu.studentNo = sc.studentNo and sc.courseNo = cs.courseNo and cs.name = '數據庫' --------------------------------------- select stu.name, cs.name, sc.score from students stu inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on sc.courseNo = cs.courseNo where cs.name = '數據庫' ``` 例7：查詢男生中最高成績，要求顯示姓名、課程名、成績 ``` select stu.name, cs.name, sc.score from students stu, scores sc, courses cs where stu.studentNo = sc.studentNo and sc.courseNo = cs.courseNo and stu.sex = '男' order by sc.score desc limit 1 -------------------------------------- select stu.name, cs.name, sc.score from students stu inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on sc.courseNo = cs.courseNo where stu.sex = '男' order by sc.score desc limit 1 ``` **左連接** ``` select * from 表1 left join 表2 on 表1.列\=表2.列 ``` 例1：查詢所有學生的成績，包括沒有成績的學生 ``` select * from students stu left join scores sc on stu.studentNo = sc.studentNo ``` 例2：查詢所有學生的成績，包括沒有成績的學生，需要顯示課程名 ``` select * from students stu left join scores sc on stu.studentNo = sc.studentNo left join courses cs on cs.courseNo = sc.courseNo ``` 右連接 ``` select * from 表1 right join 表2 on 表1.列=表2.列 ``` ``` 添加兩門課程 insert into courses values (0, '語文'), (0, '數學'); ``` 例1：查詢所有課程的成績，包括沒有成績的課程 ``` select * from scores sc right join courses cs on cs.courseNo = sc.courseNo ``` 例2：查詢所有課程的成績，包括沒有成績的課程，包括學生信息 ``` select * from scores sc right join courses cs on cs.courseNo = sc.courseNo left join students stu on stu.stud ``` ### **自關聯** * 設計省信息的表結構provinces * id * ptitle * 設計市信息的表結構citys * id * ctitle * proid * citys表的proid表示城市所屬的省，對應著provinces表的id值 * 問題：能不能將兩個表合成一張表呢？ * 思考：觀察兩張表發現，citys表比provinces表多一個列proid，其它列的類型都是一樣的 * 意義：存儲的都是地區信息，而且每種信息的數據量有限，沒必要增加一個新表，或者將來還要存儲區、鄉鎮信息，都增加新表的開銷太大 * 答案：定義表areas，結構如下 * * id * * atitle * * pid * 因為省沒有所屬的省份，所以可以填寫為null * 城市所屬的省份pid，填寫省所對應的編號id * 這就是自關聯，表中的某一列，關聯了這個表中的另外一列，但是它們的業務邏輯含義是不一樣的，城市信息的pid引用的是省信息的id * 在這個表中，結構不變，可以添加區縣、鄉鎮街道、村社區等信息準備數據： ``` create table areas( aid int primary key, atitle varchar(20), pid int ); insert into areas values ('130000', '河北省', NULL), ('130100', '石家莊市', '130000'), ('130400', '邯鄲市', '130000'), ('130600', '保定市', '130000'), ('130700', '張家口市', '130000'), ('130800', '承德市', '130000'), ('410000', '河南省', NULL), ('410100', '鄭州市', '410000'), ('410300', '洛陽市', '410000'), ('410500', '安陽市', '410000'), ('410700', '新鄉市', '410000'), ('410800', '焦作市', '410000'); ``` * 例1：查詢一共有多少個省 ``` select count(*) from areas where pid is null; ``` 例1：查詢河南省的所有城市 ``` select * from areas as p inner join areas as c on c.pid=p.aid where p.atitle='河北省'; ``` ``` 添加區縣數據 insert into areas values ('410101', '中原區', '410100'), ('410102', '二七區', '410100'), ('410103', '金水區', '410100'); ``` 例2：查詢鄭州市的所有區縣 ``` select * from areas as c inner join areas as a on a.pid=c.aid where c.atitle='鄭州市'; ``` 例3：查詢河南省的所有區縣 ``` select * from areas as p left join areas as c on c.pid=p.aid left join areas as a on a.pid=c.aid where p.atitle='河南省' ``` ### **子查詢** * 在一個 select 語句中,嵌入了另外一個 select 語句, 那么被嵌入的 select 語句稱之為子查詢語句 **主查詢** * 主要查詢的對象,第一條 select 語句 * **主查詢和子查詢的關系** * 子查詢是嵌入到主查詢中 * 子查詢是輔助主查詢的,要么充當條件,要么充當數據源 * 子查詢是可以獨立存在的語句,是一條完整的 select 語句 **子查詢分類** * 標量子查詢: 子查詢返回的結果是一個數據(一行一列) * 列子查詢: 返回的結果是一列(一列多行) * 行子查詢: 返回的結果是一行(一行多列) * 表級子查詢: 返回的結果是多行多列 **標量子查詢** 例1：查詢班級學生的平均年齡 ``` 查詢班級學生平均年齡 select avg(age) from students 查詢大于平均年齡的學生 select * from students where age > 21.4167 select * from students where age > (select avg(age) from students); ``` 例2：查詢王昭君的成績，要求顯示成績 ``` 學生表中查詢王昭君的學號 select studentNo from students where name = '王昭君' 成績表中根據學號查詢成績 select * from scores where studentNo = '001' select * from scores where studentNo = (select studentNo from students where name = '王昭君') ``` **列級子查詢** 例3：查詢18歲的學生的成績，要求顯示成績 ``` 學生表中查詢18歲的學生的學號 select studentNo from students where age=18 成績表中根據學號查詢成績 select * from scores where studentNo in ('002','006') select * from scores where studentNo in (select studentNo from students where age=18) ``` **行級子查詢** 例4：查詢男生中年齡最大的學生信息 ``` select * from students where sex='男' and age=(select max(age) from students) ``` ### **表級子查詢** 例5：查詢數據庫和系統測試的課程成績 ``` select * from scores s inner join (select * from courses where name in ('數據庫','系統測試')) c on s.courseNo = c.courseNo ``` **子查詢中特定關鍵字使用** * in 范圍 * 格式: 主查詢 where 條件 in (列子查詢) * any | some 任意一個 * 格式: 主查詢 where 列 = any (列子查詢) * 在條件查詢的結果中匹配任意一個即可,等價于 in * all * 格式: 主查詢 where 列 = all(列子查詢) : 等于里面所有 * 格式: 主查詢 where 列 <>all(列子查詢) : 不等一其中所有 ``` select * from students where age in (select age from students where age between 18 and 20) ``` ### **查詢演練** **準備數據** ``` create table goods( id int unsigned primary key auto_increment, name varchar(150), cate varchar(40), brand_name varchar(40), price decimal(10,3) default 0, is_show bit default 1, is_saleoff bit default 0 ); insert into goods values(0,'r510vc 15.6英寸筆記本','筆記本','華碩','3399',default,default); insert into goods values(0,'y400n 14.0英寸筆記本電腦','筆記本','聯想','4999',default,default); insert into goods values(0,'g150th 15.6英寸游戲本','游戲本','雷神','8499',default,default); insert into goods values(0,'x550cc 15.6英寸筆記本','筆記本','華碩','2799',default,default); insert into goods values(0,'x240 超極本','超級本','聯想','4999',default,default); insert into goods values(0,'u330p 13.3英寸超極本','超級本','聯想','4299',default,default) ; insert into goods values(0,'svp13226scb 觸控超極本','超級本','索尼','7999',default,defaul t); insert into goods values(0,'ipad mini 7.9英寸平板電腦','平板電腦','蘋果','1998',default,default); insert into goods values(0,'ipad air 9.7英寸平板電腦','平板電腦','蘋果','3388',default,default); insert into goods values(0,'ipad mini 配備 retina 顯示屏','平板電腦','蘋果','2788',default,default); insert into goods values(0,'ideacentre c340 20英寸一體電腦 ','臺式機','聯想','3499',default,default); insert into goods values(0,'vostro 3800-r1206 臺式電腦','臺式機','戴爾','2899',default,default); insert into goods values(0,'imac me086ch/a 21.5英寸一體電腦','臺式機','蘋果','9188',default,default); insert into goods values(0,'at7-7414lp 臺式電腦 linux ）','臺式機','宏碁','3699',default,default); insert into goods values(0,'z220sff f4f06pa工作站','服務器/工作站','惠普','4288',default,default); insert into goods values(0,'poweredge ii服務器','服務器/工作站','戴爾','5388',default,default); insert into goods values(0,'mac pro專業級臺式電腦','服務器/工作站','蘋果','28888',default,default); insert into goods values(0,'hmz-t3w 頭戴顯示設備','筆記本配件','索尼','6999',default,default); insert into goods values(0,'商務雙肩背包','筆記本配件','索尼','99',default,default); insert into goods values(0,'x3250 m4機架式服務器','服務器/工作站','ibm','6888',default,de fault); insert into goods values(0,'hmz-t3w 頭戴顯示設備','筆記本配件','索尼','6999',default,default); insert into goods values(0,'商務雙肩背包','筆記本配件','索尼','99',default,default); ``` **查詢演練** > 求所有電腦產品的平均價格,并且保留兩位小數 ``` select round(avg(price),2) as avg_price from goods; ``` > 查詢所有價格大于平均價格的商品，并且按價格降序排序 ``` select id,name,price from goods where price > (select round(avg(price),2) as avg_price from goods) order by price desc; ``` > 查詢類型為'超極本'的商品價格 ``` select price from goods where cate = '超級本'; ``` > 查詢價格大于或等于"超級本"價格的商品，并且按價格降序排列 ``` select id,name,price from goods where price >= any(select price from goods where cate = '超級本') order by price desc; ``` > = any 或者 =some 等價 in ``` select id,name,price from goods where price in (select price from goods where cate = '超級本') order by price desc; ``` > !=all 等價于 not in ``` select id,name,price from goods where price not in (select price from goods where cate = '超級本') order by price desc; ``` **數據分表** > 創建“商品分類”表 ``` create table if not exists goods_cates( cate_id int unsigned primary key auto_increment, cate_name varchar(40) ); ``` > 查詢goods表的所有記錄，并且按"類別"分組 ``` select cate from goods group by cate; ``` > 將分組結果寫入到goods_cates數據表 ``` insert into goods_cates (cate_name) select cate from goods group by cate; ``` > 通過goods_cates數據表來更新goods表 ``` update goods as g inner join goods_cates as c on g.cate = c.cate_name set cate = cate_id; ``` > 通過create...select來創建數據表并且同時寫入記錄,一步到位 ``` create table goods_brands ( brand_id int unsigned primary key auto_increment, brand_name varchar(40)) select brand_name from goods group by brand_name; ``` > 通過goods_brands數據表來更新goods數據表 ``` update goods as g inner join goods_brands as b on g.brand_name = b.brand_name set g.brand_name = b.brand_id; ``` > 查看 goods 的數據表結構,會發現 cate 和 brand_name對應的類型為 varchar 但是存儲的都 > 是字符串 > 修改數據表結構，把cate字段改為cate_id且類型為int unsigned，把brand_name字段改為 > brand_id且類型為int unsigned > 分別在 good_scates 和 goods_brands表中插入記錄 ``` insert into goods_cates(cate_name) values ('路由器'),('交換機'),('網卡'); insert into goods_brands(brand_name) values ('海爾'),('清華同方'),('神舟'); ``` > 在 goods 數據表中寫入任意記錄 ``` insert into goods (name,cate_id,brand_id,price) values('LaserJet Pro P1606dn 黑白激光打印機','12','4','1849'); ``` > 查詢所有商品的詳細信息 (通過左右鏈接來做) ``` select * from goods left join goods_cates on goods.cate_id=goods_cates.id inner join goods_brands on goods.brand_id=goods_brands.id ``` > 顯示沒有商品的品牌(通過右鏈接+子查詢來做) -- 右鏈接 ``` select * from goods right join goods_brands on goods.brand_id =goods_brands.id ``` > -- 子查詢 ``` select * from goods_brands where id not in (select DISTINCT brand_id from goods) ```