#??外鍵
????????#??當我們進行表關(guān)聯(lián)的時候径荔，我們可以通過外鍵約束一下
????#??1.??創(chuàng)建表的時候直接添加
????????Create??table??表名(字段??類型垂寥，字段??類型颠黎，...,??constraint??[外鍵名稱]??foreign??key(外鍵字段)??references??父表(主鍵字段))
????????create??table??stu(id??int??primary??key??auto_increment,??name??varchar(20),??card_id??varchar(10),clazz_id??int,constraint??fk_clazz_id_id??foreign??key(clazz_id)??references??clazz(id));
????#??2.??通過alert進行修改??(外鍵起名要有一個命名規(guī)則)
????????Alter??table??表名??add??[constraint??外鍵名字]??foreign??key(外鍵字段)??references??父表(主鍵字段);
????????alter??table??stu??add??constraint??fk_clazz_id_id??foreign??key(clazz_id)??references??clazz(id);
????#??3.??刪除外鍵
????????alter??table??[表名]??drop??foreign??key??[外鍵名稱];
????????alter??table??stu??drop??foreign??key??fk_clazz_id_id;
????#??4.??查詢外鍵
????????show??create??table??[表名]
????#? 5.??級聯(lián)操作??(級聯(lián)更新，級聯(lián)刪除)
# 表連接查詢
????查詢出表中所有的記錄? ? ? ? select * from stu, clazz (笛卡爾積 3*3=9行記錄)?
????添加關(guān)聯(lián)條件(不使用)? ? ? select * from stu, clazz where stu.clazz_id = clazz.id
????連接查詢
? ? ????內(nèi)連接? inner join?? (on 是關(guān)聯(lián)條件)?? 兩張表中符合條件的交集
?????????select * from stu inner join clazz on stu.clazz_id = clazz.id
????????左連接? left join? 把左表中內(nèi)容都列出來滞项，右表有匹配的就匹配狭归，沒有匹配的直接為空
? ? ? ? 哪張表在前面，哪張表就是左表
? ? ? ? ???????? select * from stu left join clazz on stu.clazz_id = clazz.id
? ? ?????? 右連接? right join 把右表內(nèi)容都列出來文判，左表中有匹配的就匹配过椎，沒有匹配的直接為空
? ? ? ?? select * from stu right join clazz on stu.clazz_id = clazz.id
? ? ????# 或者三張表，四張表
? ?? select * from stu inner join clazz on stu.clazz_id = clazz.id inner join teacher on stu.stu_id = teacher.id left jon ...
# 子查詢
????一個查詢的結(jié)果是另外一個查詢的條件
????select name from clazz where id in (select clazz_id from stu where name = 'zhangsan');
????# 別名(as)
????# 通過as 可以給表起別名戏仓， 也可以給字段起別名
????select s.name as sname, c.name as cname, c.number cnumber from stu as s inner join clazz as c on s.clazz_id = c.id;
????# as 可以省略掉
????select s.name sname, c.name cname, c.number cnumber from stu s inner join clazz c on s.clazz_id = c.id;
# 學生表(姓名,年齡,性別)
? ????create table stu2 (id int primary key auto_increment,name varchar(20),age tinyint, gender char(1));
????? insert into stu2(name,age,gender) value ('wang',20,'m'),('li',19,'m'),('h',16,'m'),('h',20,'g'), ('j',19,'g'),('a',19,'g'),('q',19,'g'),('z',16,'g');
? ????# 1.查詢出男生有多少人,女生有多少個人
? select gender, count(gender) gcount from stu2? group by gender;
????? # 2. 查詢出18歲以上的男生有多少人疚宇，18歲以上的女生有多少人 (年齡大于18歲) （where 分組之前執(zhí)行過濾）
? ????select gender ,count(gender) gcount from stu2 where age > 18 group by gender;
? ????# 3. 查詢出18歲以上的男生亡鼠，人數(shù)大于3個的性別才加入統(tǒng)計(having 分組出結(jié)果之后，過濾結(jié)果)
? ????select gender ,count(gender) gcount from stu2 where age > 18 group by gender having gcount > 3;
mysql的函數(shù)
? ? 1. max 最大值
? ? ? 求男生的最大年齡
? ? ? select gender, max(age) mage from stu2 group by gender having gender = 'm';
? ? 2. min? 最小值
? ? ? 求女生最小年齡
? ? ? select gender, min(age) mage from stu2 group by gender having gender = 'g';
? ? 3. count? 數(shù)量和
? ? ? 分別求男生和女生的數(shù)量
? ? ? select gender, count(gender) gcount from stu2? group by gender;
? ? 4. avg 平均數(shù)
? ? 男生和女生的年齡的平均數(shù)
? ? select gender, avg(age) gcount from stu2? group by gender;
? ? 5. concat 字段拼接
? ? ? 全表查詢敷待，把姓名字段和年齡字段拼接輸出
? ? ? select name,age,concat(name,'-','age') cc from stu2;
? ? 6. substring 字段裁剪 substring(字段,begin,count)? begin 是從1開始的
? ? ? 截取姓名的字段的首字母輸出
? ? ? select name ,substring(name,1,1) sub from stu2;
? ? 7. md5
? ? ? insert into stu2 (name,age,gender) value (substring(md5('aa'),1,10),12,'m');
? ? ? select * from stu2 where name = substring(md5('aa'),1,10);
