-- 基本查詢
-- 查詢所有字段
????-- select * from 表名;
????select * from students;
????select * from classes;
-- 查詢指定字段
????-- select 列1,列2,... from 表名;
????select name, gender from students;
-- 使用 as 給字段起別名
????-- select 字段 as 名字.... from 表名;
????select name as '名字', gender as '性別' from students;
-- sql語句完全的形式
????select students.* from students;
????select students.name, students.gender from students;
????select python_test_1.students.* from students;
省略數(shù)據(jù)庫名字: 只有當(dāng)前正在使用該數(shù)據(jù)庫時才能夠省略數(shù)據(jù)庫名
省略數(shù)據(jù)表名: 只有當(dāng)前正在某個數(shù)據(jù)表中查詢數(shù)據(jù)時才能夠省略數(shù)據(jù)表名
不能夠省略數(shù)據(jù)表名: 當(dāng)一個sql語句中有多個表, 并且表中的字段有重復(fù)的時候, 需要添加表名
????ex: select name,name from students,classes;
????-- select 表名.字段 .... from 表名;
????select students.name, students.age, students.gender from students;
????-- 可以通過 as 給表起別名
????-- select 別名.字段 .... from 表名 as 別名;
????select s.name, s.age, s.gender from students as s; -- 在當(dāng)前sql語句中臨時給students表取別名叫做s
-- 在當(dāng)前的sql 語句中 臨時的給students 起了一個別名叫做s
-- 消除重復(fù)行
-- distinct 字段, 修飾所有需要查詢的字段
-- 查詢班級學(xué)生的性別
????select gender from students;
-- 查詢班級有多少種性別
????select distinct gender from students;
????select distinct gender,id from students;
-- 條件查詢 where
-- 比較運算符
-- 查詢大于18歲的信息
????select * from students where age > 18;
????select * from students where 1 > 0;
-- 查詢小于18歲的信息
-- 查詢小于或者等于18歲的信息
-- = 而不是 '=='
????select * from students where age = 18;
-- != 或者 <>? 實際開發(fā)中 最好使用 ! 表示不等于
-- <> 不夠通用
-- 邏輯運算符
-- and
-- 18歲以上的女性
????select * from students where age > 18 and gender = '女'; # 原始值 男 女 中性 保密
????select * from students where age > 18 and gender = 2;? # 枚舉值 1 2 3 4
-- or
-- 18以上或者身高超過180(包含)以上
????select * from students where age > 18 or height >= 180.00;
-- not? 非
-- 年齡不是18歲的學(xué)生
????select * from students where age != 18;
????select * from students where not age = 18;
-- 年齡是小于或者等于18 并且是女性
????select * from students where age <= 18 and gender = 2;
????# select * from students where (age < 18 or age = 18) and gender = 2;
-- 模糊查詢
-- like
-- % 表示任意字符可有可無
-- 查詢姓名中 以 "小" 開始的名字
????select * from students where name like "小%";
????select * from students where name like "%杰%";
-- _ 表示任意一個字符
-- 查詢有2個字的名字
????select * from students where name like "__";
-- 查詢有3個字的名字
-- rlike 正則
-- 查詢以 周開始的姓名
????-- re.match()? r"dfgdfgkdfjg"
????select * from students where name rlike "^周.*";
-- 范圍查詢
-- in 表示在一個非連續(xù)的范圍內(nèi)
-- 查詢 年齡為18芭商、34歲的學(xué)生
????select * from students where age = 18 or age = 34;
????select * from students where age = 18 or age = 34 or age = 59;
????select * from students where age in (18,18,34,49,45);
-- not in 不在非連續(xù)的范圍之內(nèi)
-- 年齡不是 18、34歲的學(xué)生的信息
????select * from students where age not in (18,34);
-- 年齡不是 18搀缠、34歲之間的信息
????select * from students where age < 18 or age > 34;
-- 18 ~ 34
????select * from students where age >= 18 and age <= 34;
????select * from students where not (age < 18 or age > 34);
-- between ... and ...表示在一個連續(xù)的范圍內(nèi)? 兩邊都會包含
-- 查詢 年齡在18到34之間的的信息
????select * from students where age between 18 and 34;
-- not between ... and ...表示不在一個連續(xù)的范圍內(nèi)
-- 查詢 年齡不在在18到34之間的的信息
????select * from students where age not between 18 and 34;
-- 空判斷 null? 不能夠使用比較運算符
-- 查詢身高為空的信息
????select * from students where height is null;
-- 查詢身高不為空的學(xué)生
????select * from students where height is not null;
-- 排序 從大到小--> 降序排序 從小到大-->升序排序
-- order by 字段 排序規(guī)則? 默認(rèn)就是升序排序 asc 可以省略
-- asc從小到大排列铛楣，即升序
-- 查詢年齡在18到34歲之間的男性，按照年齡從小到大排序
????select * from students where age between 18 and 34 and gender = 1 order by age asc;
-- 降序 desc
-- desc從大到小排序艺普，即降序
-- 查詢年齡在18到34歲之間的女性簸州，身高從高到矮排序
????select * from students where age between 18 and 34 and gender = 2 order by height desc;
-- order by 多個字段, order by age asc, height desc
-- 查詢年齡在18到34歲之間的女性，身高從高到矮排序, 如果身高相同的情況下按照年齡從小到大排序
????select * from students where age between 18 and 34 and gender = 2 order by height desc, age asc;
-- 查詢年齡在18到34歲之間的女性歧譬，身高從高到矮排序, 如果身高相同的情況下按照年齡從小到大排序, 如果年齡也相同那么按照id從大到小排序
????select * from students where age between 18 and 34 and gender = 2 order by height desc, age asc,id desc;
-- 按照年齡從小到大岸浑、身高從高到矮的排序
????select * from students order by age asc,height desc;
-- 聚合函數(shù) 做統(tǒng)計
-- 總數(shù)
-- count()
-- count(*) 以行單位來進(jìn)行統(tǒng)計個數(shù)
-- count(*) 效率更高,count(height): 效率略差:獲取對應(yīng)的行--> 獲取該行對應(yīng)字段是否為NULL
-- 查詢班級有多少人
????select * from students;
????select count(*) from students;
-- 查詢男性有多少人，女性有多少人
????select count(*) from students where gender = 1;
????select count(*) from students where gender = 2;
-- 最大值
-- max()
-- 查詢最大的年齡
????select max(age) from students;
-- 查詢女性的最高 身高
????select max(height) from students where gender = 2;
-- 查詢最高身高的學(xué)生的名字
????select name, max(height) from students;
# 錯誤:select name, max(height) from students;
????select name from students where height = (select max(height) from students);? -- 子查詢語句
????select max(height) from students;
-- 最小值
-- min()
-- 求和
-- sum()
-- 計算所有人的年齡總和
????select sum(age) from students;
# 錯誤select sum(age) / count(*) from students;
????select sum(age) / count(age) from students;
-- 計算平均身高
# select sum(height) / count(*) from students;
select sum(height) / count(height) from students;
-- 平均值
-- avg()
-- 計算平均年齡
????select avg(age) from students;
-- 計算平均身高
-- 四舍五入 round(123.23 , 1) 保留1位小數(shù), 四舍五入
-- 計算所有人的平均年齡瑰步，保留2位小數(shù)
????select round(avg(age),2) from students;
-- 計算男性的平均身高 保留2位小數(shù)
????database(), now(), version()
select now(); sql: 結(jié)構(gòu)化的查詢語言
? 幫助文檔
? functions;
-- 分組
-- 分組的目的就是為了進(jìn)行聚合統(tǒng)計
-- group by 字段
-- 查詢班級學(xué)生的性別
????select gender from students;
-- 查看有哪幾種性別
????select distinct gender from students;
-- 按照性別分組
????select gender from students group by gender;
# 每種性別中的最大年齡
????select gender, max(age) from students group by gender;
-- 計算每種性別中的人數(shù)
????select gender,count(*) from students group by gender;
-- 查詢每種性別中的平均年齡avg(age), 最大年齡,平均身高,最高身高
????select gender,avg(age),max(age), avg(height), max(height) from students group by gender;
-- group_concat(...)
# 查詢分組數(shù)據(jù)中的人的姓名
錯誤:select gender, name from students group by gender;
男 周杰倫
男 彭于晏
男 張學(xué)友
...
女 靜香
女 周杰
????select gender,group_concat(name) from students group by gender;
男: 周杰倫, 彭于晏, 張學(xué)友
????select gender,group_concat(name,age) from students group by gender;
-- 查詢同種性別中的姓名和身高
-- 計算男性的人數(shù)
????select count(*) from students where gender = 1;
-- 通過分組來實現(xiàn)
????select gender,count(*) from students where gender = 1 group by gender;
-- 使用having 條件篩選 表示對于已經(jīng)分組的數(shù)據(jù)做進(jìn)一步的篩選
????select gender,count(*) from students group by gender having gender = 1;
-- 除了男生以外的分組的人數(shù)
????select gender,count(*) from students group by gender having gender != 1;
-- having? 對于分組之后的數(shù)據(jù) 做進(jìn)一步的篩選
-- 查詢每種性別中的平均年齡avg(age)
????select gender, avg(age) from students group by gender;
-- 查詢每種性別中的平均年齡avg(age), 最大年齡,平均身高,最高身高, 分組是為了更好的統(tǒng)計
????select gender,avg(age),max(age), avg(height), max(height) from students group by gender;
-- 查詢平均年齡超過30歲的性別矢洲，以及姓名
????select gender, group_concat(name) from students group by gender having avg(age) > 30;
-- having 和 where 的區(qū)別
having:? 對于分組之后的數(shù)據(jù) 做進(jìn)一步的篩選, 有having 就一定group by, 有g(shù)roup by 不一定having
where : 對于源數(shù)據(jù)做條件篩選
-- 分頁
-- limit start, count
-- start: 表示從哪里開始查詢, start 默認(rèn)值為0, 可以省略, 跳過多少條數(shù)據(jù)
-- count: 查詢多少條
獲取第一頁, 每頁顯示4條數(shù)據(jù)
select * from students limit 4;
第1頁
select * from students limit 4;
select * from students limit 0,4;
第2頁
select * from students limit 4,4;
第3頁
select * from students limit 8,4;
第4頁
select * from students limit 12,4;
-- 每頁顯示5個，顯示第3頁的信息, 按照年齡從小到大排序 (pagenum - 1) * count
select * from students limit 10,5 order by age asc;
select * from students order by age asc limit 10,5;
-- 連接查詢? 將兩個表按照某種條件合并到一起
-- 學(xué)生名: students;
-- 班級名: classes
-- 查詢學(xué)生的名字和學(xué)生對應(yīng)的班級名字
????select students.name,classes.name from students,classes;
????select * from students,classes;
-- 笛卡爾積查詢, 會產(chǎn)生很多無用的信息
????select * from students,classes;
????select * from students,classes where students.cls_id = classes.id;
-- 連接查詢 將兩個表中的數(shù)據(jù)按照設(shè)置的連接條件進(jìn)行篩選, 符合連接條件的數(shù)據(jù)才能夠被篩選出來
-- table1 inner join table2 on 設(shè)置內(nèi)連接條件? --> 內(nèi)連接查詢
select * from students inner join classes on students.cls_id = classes.id;
select students.name,classes.name from students inner join classes on students.cls_id = classes.id;
-- 按照要求顯示姓名缩焦、和學(xué)生對應(yīng)的班級的名字(學(xué)生所在的班級)
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
-- 在以上的查詢中读虏，將班級名字顯示在第1列
select c.name,s.name from students as s inner join classes as c on s.cls_id = c.id;
-- 查詢 學(xué)生所在的班級, 按照班級進(jìn)行排序
select c.name,s.name from students as s inner join classes as c on s.cls_id = c.id order by c.name;
-- 外連接查詢: left join + right join
-- left join 左外連接查詢
-- 查詢每位學(xué)生對應(yīng)的班級信息
-- 不滿足連接條件的數(shù)據(jù)會以NULL填充
select * from students left join classes on students.cls_id = classes.id;
-- right join 右外連接查詢? 使用的比較少
-- 將數(shù)據(jù)表名字互換位置责静，用left join完成
-- 擴充了解
-- 內(nèi)連接和外連接的其他寫法
--? 內(nèi)連接的其他寫法
select s.name,c.name from students as s join classes as c on s.cls_id = c.id;
select s.name,c.name from students as s cross join classes as c on s.cls_id = c.id;
-- 外連接的其他寫法
select * from students left outer join classes on students.cls_id = classes.id;
-- 自關(guān)聯(lián)? 自己關(guān)聯(lián)自己 a inner join a
create table areas(
? ? aid int primary key,
? ? atitle varchar(20),
? ? pid int
);
-- 通過 source 指令導(dǎo)入一個sql文件
-- 省級聯(lián)動 url:http://demo.lanrenzhijia.com/2014/city0605/
-- 查詢所有省份
select * from areas where pid is null;
-- 查詢出廣東省有哪些市
廣東省 廣州市
廣東省 深圳市
...
-- 需要有想象力 想象成省表 和 市表
select p.atitle,c.atitle from areas as p inner join areas as c on c.pid = p.aid where p.atitle = "廣東省";
-- 查詢出廣州市有哪些區(qū)縣
select p.atitle,s.atitle from areas as p inner join areas as s on s.pid = p.aid where p.atitle = "廣州市" ;
