基本查詢語法:
select * from 表名;
例：
select * from classes;
查詢指定列:
注:可以使用as為列或表指定別名
select 列1,列2,... from 表名;
例：
select id,name from classes;
給字段(列)起別名
select id as 序號, name as 名字, gender as 性別 from students;
給表起別名
select s.id,s.name,s.gender from students as s;
查詢的時候去重
select distinct 列1,... from 表名; # 會將重復(fù)的數(shù)據(jù)刪除 不返回
例：
select distinct gender from students;
全列插入和部分列插入:
全列單行插入:主鍵列是自動增長，但是在全列插入時需要占位蛔垢，通常使用0或者 default 或者 null 來占位求冷，插入成功后以實際數(shù)據(jù)為準(zhǔn)
insert into 表名 values(...)
例：
insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2');
全列多行插入
insert into 表名 values(...),(...)...;
例：
insert into classes values(0,'python1'),(0,'python2');
部分列插入
insert into 表名(列1,...) values(值1,...)
例：
insert into students(name,hometown,birthday) values('黃蓉','桃花島','2016-3-2');
部分列多行插入
insert into 表名(列1,...) values(值1,...),(值1,...)...;
例：
insert into students(name) values('楊康'),('楊過'),('小龍女');
修改
update 表名 set 列1=值1,列2=值2... where 條件
例：
update students set gender=0,hometown='北京' where id=5;
刪除
物理刪除:
delete from 表名 where 條件
例：
delete from students where id=5;
邏輯刪除: 添加一個說明字段表示是否刪除
update students set isdelete=1 where id=1;
mysql條件where
語法:
select * from 表名 where 條件;
例：
select * from students where id=1;
where 后面支持的運(yùn)算符:
- 比較運(yùn)算符 = > < != >= <=
select * from students where id > 3;
- 邏輯運(yùn)算符 and or not
select * from students where id > 3 and gender=0;
select * from students where id not in (1,3,4,5);
- 模糊查詢 like , %表示任意多個任意字符, _表示一個任意字符
查詢姓黃或叫靖的學(xué)生
select * from students where name like '黃%' or name like '%靖';
查詢姓黃并且“名”是一個字的學(xué)生
select * from students where name like '黃_';
- 范圍查詢 in | between start and end
in:不連續(xù)的范圍
between :連續(xù)的范圍
查詢編號是1或3或8的學(xué)生
select * from students where id in(1,3,8);
查詢編號為3至8的學(xué)生
select * from students where id between 3 and 8;
- 空判斷,空判斷要用is null , is not null
查詢沒有填寫身高的學(xué)生
select * from students where height is null;
查詢填寫了身高的學(xué)生
select * from students where height is not null;
where運(yùn)算的優(yōu)先級
小括號>not>比較運(yùn)算符>邏輯運(yùn)算符
邏輯運(yùn)算符中and>or ,同時出現(xiàn)and 和 or ,and先運(yùn)算,想改變規(guī)則就使用小括號
mysql 排序 order
語法:
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
將行數(shù)據(jù)按照列1進(jìn)行排序叉讥，如果某些行列1的值相同時红氯，則按照列2排序穆端，以此類推
默認(rèn)升序排序 asc
asc:升序
desc:降序
顯示所有的學(xué)生信息绘盟，先按照年齡從大-->小排序概而，當(dāng)年齡相同時 按照身高從高-->矮排序
select * from students order by age desc,height desc;
mysql 聚合函數(shù)
計算總數(shù) count
查詢學(xué)生總數(shù)
select count(*) from students;
最大值max
查詢女生的編號最大值
select max(id) from students where gender=2;
最小值min
查詢未刪除的學(xué)生最小編號
select min(id) from students where is_delete=0;
求和sum
查詢男生的總年齡
select sum(age) from students where gender=1;
查詢男生的平均年齡
select sum(age)/count(id) from students where gender=1;
平均值avg
查詢未刪除女生的年齡平均值
elect avg(age) from students where is_delete=0 and gender=2;
mysql分組
group by的含義:將查詢結(jié)果按照1個或多個字段進(jìn)行分組倍权，字段值相同的為一組
group by可用于單個字段分組掷豺，也可用于多個字段分組
注意使用過分組之后,查詢的字段必須是分組的字段,寫其他字段會錯誤,不過可以寫聚合函數(shù)動態(tài)添加字段
注:如果你想要查看一個分組下所有數(shù)據(jù)的非分組字段總數(shù)據(jù),可以使用group_concat
select gender from students group by gender;
select gender,count(id) as num from students group by gender;
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男 | 32.6000 |
| 女 | 23.2857 |
| 中性 | 33.0000 |
| 保密 | 28.0000 |
+--------+----------+
使用group_concat
select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name) |
+--------+-----------------------------------------------------------+
| 男 | 彭于晏,劉德華,周杰倫,程坤,郭靖 |
| 女 | 小明,小月月,黃蓉,王祖賢,劉亦菲,靜香,周杰 |
| 中性 | 金星 |
| 保密 | 鳳姐 |
+--------+-----------------------------------------------------------+
select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男 | 3,4,8,9,14 |
| 女 | 1,2,5,7,10,12,13 |
| 中性 | 11 |
| 保密 | 6 |
+--------+------------------+
為分組加條件having
having 條件表達(dá)式：用來分組查詢后指定一些條件來輸出查詢結(jié)果
having作用和where一樣帅霜，但having只能用于group by
where是分組之前的篩選,having是分組之后的篩選
select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
+--------+----------+
分組自動計算總和with rollup
with rollup的作用是：在最后新增一行纪吮，來記錄當(dāng)前列里所有記錄的總和
select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
| NULL | 14 |
+--------+----------+
select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age) |
+--------+-------------------------------------------+
| 男 | 29,59,36,27,12 |
| 女 | 18,18,38,18,25,12,34 |
| 中性 | 33 |
| 保密 | 28 |
| NULL | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+
mysql 分頁之limit
語法:
select * from 表名 limit [start,]count
從start開始，獲取count條數(shù)據(jù)
默認(rèn)start 為0,即不跳過
查詢前3行男生信息
select * from students where gender=1 limit 3;
跳過5個男生查取3個男生信息
select * from students where gender=1 limit 3;
mysql連接查詢
mysql支持三種連接查詢:
內(nèi)連接:inner join 查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)
左連接:left join 查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)加左表特有的數(shù)據(jù)餐曼，對于右表中不存在的數(shù)據(jù)使用null填充
右連接:right join查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)默辨，右表特有的數(shù)據(jù)德频，對于左表中不存在的數(shù)據(jù)使用null填充
語法:
select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
示例:
使用內(nèi)連接查詢班級表與學(xué)生表
select * from students inner join classes on students.cls_id = classes.id;
使用左連接查詢班級表與學(xué)生表 并使用as
select * from students as s left join classes as c on s.cls_id = c.id;
查詢學(xué)生姓名及班級名稱
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
使用右連接查詢班級表與學(xué)生表
select * from students as s right join classes as c on s.cls_id = c.id;
使用join的自關(guān)聯(lián),不使用外鍵
create table areas(
aid int primary key,
atitle varchar(20),
pid int # 父省編號
);
查詢一共有多少個省
select count(*) from areas where pid is null;
查詢省的名稱為“山西省”的所有城市
select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';
查詢市的名稱為“廣州市”的所有區(qū)縣
select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='廣州市';
子查詢
標(biāo)量子查詢
select * from students where age > (select avg(age) from students); #查詢年齡大于班級平均年齡的學(xué)生信息
列級子查詢
select name from classes where id in (select cls_id from students group by cls_id); #查詢在students表中出現(xiàn)的班級名
行級子查詢
select name from students where height = (select max(height) from students); # 查詢身高最高的學(xué)生姓名
查詢
-- 普通查詢
select * from students;
select id,name from students \G
-- 分頁查詢
select TOP 10 * from students \G -- 取前10個數(shù)據(jù),不建議使用,建議使用limit
select * from students limit 10 \G
select * from students limit 5,10 \G -- 跳過5個匹配的數(shù)據(jù),然后取10個數(shù)據(jù)
-- 起別名
select id,name as n from students limit 10;
-- 條件查詢
select * from students where age = 23 limit 10;
-- 范圍查詢
select * from students where id in (1,3,4,5);
select * from students where id in (1,3,4) and status = 0 \G
-- 模糊查詢
select * from students where name like "小_" \G -- _匹配單個任意的字符
select * from students where name like "小%" \G -- * 匹配任意個任意字符
select * from students where name like "小[東紅金]" -- 匹配小東或小紅或小金
select * from students where age like "2[^56]" -- 匹配不是25 或 26 歲的所有人
select * from students where instr(name,"小")>0 -- 注意 instr方法是在字符串中搜尋
-- 當(dāng)前語法是在name中搜尋存在小字符的數(shù)據(jù), instr的下表索引是以1 開始的,
-- 即找到就返回字符所在的下標(biāo),找不到返回0,不存在返回負(fù)數(shù)情況,所以這里>0相當(dāng)于like
-- 不過不能向like那樣控制匹配的位數(shù)
-- 排序查詢
select * from students order by id desc limit 1 ; -- 降序
select * from students order by id asc limit 1; -- 升序
-- 分組聚合查詢
select name,count(*) as num from students group by name \G
select name,SUM(grade) as total_num from grade where name = "xiaodong" group by name;
select name,SUM(grade) from grade where id > 20 group by name having SUM(grade)>300;
--注意 where是過濾行,having是過濾組,即where過濾后的結(jié)果交給后面group分組,然后having過濾分組后結(jié)果
# group 還可以同時分多個組
select name,class,SUM(grade) from grade group by name,class;
-- 注意分組之后,select 查詢的字段不能包含分組字段之外的字段
#如果同時分多個組,select就會根據(jù)每個分組生成一條數(shù)據(jù),可以理解為這里進(jìn)行了2次分組查詢,把查詢的結(jié)果合并了
-- 返回xiaodong的總成績
select name,SUM(grade) as num from grade group by name \G --返回所有人的總成績
select name,AVG(grade) as avg_num from grade where name = "xiaodong" group by name; --求平均值
select id,name,MAX(grade) from grade group by name \G -- id和name都是最大值對應(yīng)的那行的id和name
select id,name,MIN(grade) from grade group by name \G -- 同理最大值
select a.name as name,b.age as age from A as a inner join B as b on a.name=b.name;
-- 內(nèi)聯(lián)查詢, 注意不加on 會觸發(fā)笛卡爾乘積 ,加on 之后只會連接匹配的項,也就是說會篩選,為了性能必須要用
-- left join 和 right join 必須要加on,不加on 出錯
-- left join 以左表為準(zhǔn),左表中不存在的不會和右表去連接,就算右表存在也會被舍棄
select a.name ,b.id from A left join B on A.id = B.id;
插入
insert into students value(1,"haha",23); -- 全部字段插入
insert into students(name,age) value("heihei",22) -- 指定字段插入
insert into students(name) value("haha"),("heihei"),("hehe") -- 插入多行
insert into students(name) values("haha"),("heihei"),("hehe") --插入多行
-- 注意 value 在插入多行時速度較快, values在插入單行時較快,不過sqlserver只認(rèn)values
-- 所以盡量使用values
insert into students set name = 'haha', age = 23 ; --插入的另外一種方式
insert into students(name) select name from students1 ; -- 從另外一個表查詢
更改
-- 單表更新
update students set name = "xiaodong" where id = 1;
update students set name = REPLACE(name,"dong","yu") where id = 1 ;
--將id=1行的name字段的dong字符改為yu 即 xiaodong 改為 xiaoyu 部分更新操作
update students set name = "xiaodong" ; -- 將全部更新為xiaodong
update students set name = "xiaodong" limit 1 ; -- 更新匹配到的第一條結(jié)果
update students set name = "xiaodong" order by id desc limit 1;
-- 多表更新
update student1,student2 set student1.id = student2.oid where student1.name = student2.name;
update student1 set student1.age = student2.age + 1 from student1,student2 where student1.id = student2.id
update student1 set student1.age = B.age from student2 as B where student1.id = B.id;
-- ignore 盡量不要使用
update students set id = 1 where id = 2 ; --會報錯
update ignore students set id = 1 where id =2 ; -- 不會報錯,但是有一個警告
-- 表達(dá)式
update students set age = age + 1 where age =23; -- 設(shè)置完后 age 會變?yōu)?24
刪除
delete 在不加where的時候,是刪除所有的匹配行,如果想清空整個表,使用 TRUNCATE table_name
注意MyISAM引擎和InnoDB引擎,在刪除行數(shù)據(jù)后,AUTO_INCREMENT字段不會被重新使用,比如id字段,數(shù)據(jù)刪除后
這個id并不會重新被使用,但有一種表除外BDB引擎
-- 單表刪除
delete from students where id = 2;
delete from students limit 1;--刪除匹配的第一條數(shù)據(jù),在數(shù)據(jù)庫更新和刪除的時候最好都寫limit防止誤刪
delete from students order by id desc limit 1 ;
-- 多表刪除
delete from somelog where user='jcole' order by timestamp_column limit 1;
# 表名放在from前面不用使用using ,放在from 后面就需要使用using了
delete t1,t2 from t1 inner join t2 inner join t3 where t1.id=t2.id and t2.id=t3.id;
delete from t1,t2 using t1 inner join t2 inner join t3 where t1.id=t2.id and t2.id=t3.id;
# 使用別名
delete a1,a2 from t1 as a1 inner join t2 as a2 where a1.id=a2.id;
delete from a1,a2 using t1 as a1 inner join t2 as a2 where a1.id=a2.id;
維護(hù)
對于mysql來說刪除數(shù)據(jù)之后,并不會刪除索引,所以導(dǎo)致id在刪除數(shù)據(jù)后依然不能自動使用,可以手動使用.
解決刪除索引的方法 optimize table table_name重新編排一下索引
對于存在經(jīng)常增刪的數(shù)據(jù)庫可以定期清理一下索引
