一內容回顧
insert
python MySQL， insert into 表名 (字段名)? values (值)
insert into 表名 values (有多少個字段寫多少個值)
insert into 表名 values (有多少個字段寫多少個值),
msq數據庫。 (有多少個字段寫多少個值),
(有多少個字段寫多少個值),
update
python訪問數據庫、 update 表名 set? 字段名 = 新的值 where 條件
update?表名 set 字段名 = 新的值, 字段名 = 新的值 where 條件;
delete
delete from 表名 where 條件;
單表查詢
select distinct 字段 from 表 where 條件
group by 分組
having 過濾組
order by 排序
limit m,n
?? select? distinct 字段 from? 表
concat('name? :??',name,' ,age : ',age)
concat_ws('-',name,age,salary)
egon-18-10000
四則運算
select?age+1? from? tablename
select salary*12 from tablename
重命名 as
? select name as n from 表
select? name? n? from? 表
case 語句
case
when? 條件
then? 顯示的內容
when? 條件
then? 顯示的內容
else
顯示的內容
end
distinct 關鍵字 去重功能
where 條件
比較運算? > < = != <> >= <=
范圍 between and/in
like
'%'
'_'
邏輯運算符 and or not
select * from 表 where a=1 and b=2 and c=3
and來說? 有任意一個不成立就不需要繼續判斷了
select * from 表 where a=1? or? b =2?or c =3
or來說,有任意一個不成立并不影響繼續判斷其他條件
select * from 表 where a not between and
group by
group_concat(字段)? 可以顯示一個分組內的所有字段內容
having
對分組之后的結果進行過濾
應該和group? by連用
聚合函數
count
sum
avg
max
min
order by
默認從小到大? 升序 asc
select * from? 表 order by score
select * from? 表? order by score asc
降序排列? 從大到小
select * from? 表? order by score desc
limit
根據前面的條件篩選出多行,取前n行
取前n名
order by 和 limit連用
limit? n
分頁
我要取所有的符合條件的項目
但是一次性顯示到頁面中顯示所以先返回一部分,然后再返回剩余的
limit 0,25
limit 25,25
limit 50,25
怎么用python操作mysql
sql注入
今日內容
多表查詢
連表查詢
先把表連起來,再查詢
子查詢
先把一個結果從一張表中查不出來,再根據結果查詢另一個表
能用子查詢做的? 就不用? 連表
多表查詢
# 一 \ 內連接 # select * from department # inner join employee # on department.id = employee.dep_id; # 只會把左表和右表對應的行顯示出來 # 如果左表中的department.id在employee.dep_id中沒有出現,那么會拋棄這個行 # 如果右表中的employee.dep_id在department.id中沒有出現,那么也會拋棄這個行# 內連接的另一種方式 用where # select * from department,employee # where department.id = employee.dep_id;# 從不同的表中取字段 # select employee.name,employee.sex,department.name # from department,employee # where department.id = employee.dep_id;# 給表起別名,讓sql更簡單 # select emp.name,emp.sex,dep.name # from department as dep,employee as emp # where dep.id = emp.dep_id;# 給字段起別名,讓顯示的效果更明確 # select emp.name,emp.sex,dep.name as dep_name # from department as dep,employee as emp # where dep.id = emp.dep_id;# 外連接 # 二 \左外連接 ： 顯示左表中的所有項，和右表中所有滿足拼接條件的項 # select * from department # left join employee # on department.id = employee.dep_id;# 三 \右外連接：顯示右表中的所有項，和左表中所有滿足拼接條件的項 # select * from department # right join employee # on department.id = employee.dep_id;# 四 \全外連接：左表和右表都完全顯示出來了 # select * from department left join employee on department.id = employee.dep_id # union all # select * from department right join employee on department.id = employee.dep_id# 練習 # 示例一 : 找到年齡> 25的員工的姓名和部門 # select * from employee # inner join department # on employee.dep_id = department.id # where age>25;# 示例一變式 : 找到alex員工的年齡和部門 # select age,department.name from employee # inner join department # on employee.dep_id = department.id # where employee.name = 'alex';# 給表重命名 # select age,dep.name from employee as emp # inner join department as dep # on emp.dep_id = dep.id # where emp.name = 'alex';# 給字段重命名引起的錯誤 # select employee.name as emp_name,age,department.name from employee # inner join department # on employee.dep_id = department.id # where emp_name = 'alex'; # 報錯,因為在select處重名名不能在where/group by/having中使用,由于mysql的詞法分析順序導致該問題# 示例2：以內連接的方式查詢employee和department表，并且以age字段的升序方式顯 # select * from employee # inner join department # on employee.dep_id = department.id # order by age;
多表查詢_子查詢
# 示例一 : 查詢平均年齡在25歲以上的部門名 # 涉及到年齡 員工表 # 部門名字 部門表 # 你的結果在哪個表,那個表一定不是子查詢的表# 1. 連表查詢的結果 # 先內連接得到一張大表 # select * from department # inner join employee # on department.id = employee.dep_id# 再根據部門分組 # select department.name from department # inner join employee # on department.id = employee.dep_id # group by department.id # having avg(age) > 25;# 2. 子查詢的結果 # 先完成一部分需求,求每一個部門的人的平均年齡 # select dep_id,avg(age) from employee group by dep_id # 再篩選出平均年齡大于25的部門 # select dep_id,avg(age) from employee group by dep_id having avg(age)>25 # 由于我們只需要部門名稱,而和部門名稱相關的項就只有部門id,所以我們只留下dep_id字段 # select dep_id from employee group by dep_id having avg(age)>25 # 查詢部門表,找到id在上面這個查詢結果內的內容 # select name from department where id in ( # select dep_id from employee group by dep_id having avg(age)>25 # ) pass # 示例2 : 查看"技術"部員工姓名 # 結果是 : 姓名 - 員工表 # 怎么知道技術部是誰? 怎么和員工表關聯? # 如果我能知道技術部的id是多少,就可以查詢了 # 1.查詢技術部的id # select id from department where name = '技術' # 2.取id=200的所有人 # select * from employee where dep_id = ( # select id from department where name = '技術'); # 3.只取名字 # select name from employee where dep_id = ( # select id from department where name = '技術'); pass # 示例3 :查看不足1人的部門名 # 結果是 部門名 - 部門表 # 先操作員工表 # 1.找到員工表中所有人的部門id # select dep_id from employee group by dep_id; # select distinct dep_id from employee; # 2.操作部門表查看id not in 上面范圍中的項目 # select name from department where id not in ( # select dep_id from employee group by dep_id);# select name from department where id not in ( # select distinct dep_id from employee);
?
?
?
?
?
??????????????????????????????????????????????
