內(nèi)連接
隱示連接
select * from user join goods;連接兩個表格得到的是笛卡爾乘積
select *from user join goods user.gid=goods.gid;連接兩個表，查詢兩個表格
“gid”相等的數(shù)據(jù)。
select user.name,goods.name from user,goods where user.gid=goods.gid;查詢對我們有意義的數(shù)據(jù)
select user.name,goods.name from user,goods on user.gid=goods.gid;
顯示連接：
select user.name,goods.name from user join goods on user.gid=goods.gid;
select u.name,g.name from user as u join goods as g on u.gid=g.gid;給表格取別名；
外連接
select *from user left join goods on user.gid=goods.gid;左連接
select *from user right join goods on user.gid =good.gid;右連接
select goods.name from user right join goods on user.name = goods.gid where id is null;id 是null代表商品沒有被購買過
select user.name from user left join goods on user.name = goods.gid where goods.gid is null;gid 是null代表商品沒有被購買過
///本月商品銷售冠軍嵌溢、商品價格
select goods.name, goods.price count(goods.name) as count from user join goods on user.gid=goods.gid where group by goods.name order by count desc limit 1;
select *from user where gid in (select gid from goods);去除goods的gid，然后把user中
子查詢（鑲嵌）
gid和goods的gid相等的取出來蹋岩。那些人購買過的商品
select *from user where gid not in (select gid from goods);那些人沒購買過的商品
全外連接uion
select *from user left join goods on user.gid=goods.gid
union
select *from user right join goods on user.gid=goods.gid;
兩個表同時更新
update user as u,goods as g set u.name = '馬蓉',g.price='8000' where u.id = 1 and u.gid = g.gid;
清空表記錄
delete from 表名; 清空表中所有數(shù)據(jù)赖草，但是自增的id依然從清空前開始計
truncate table 表名;
清空表中所有數(shù)據(jù)，但是id會從1開始計
set autocommit = 0;禁止自動提交剪个，默認下是自動提交
set autocommit = 0;updata 更新數(shù)據(jù)設(shè)置本地(磁盤)和phpMyAdmin手動提交
commit秧骑；更新完數(shù)據(jù)后手動提交
set autocommit = 1;設(shè)置自動提交
begin 開啟一個事務(wù)
在事務(wù)里面寫了語句之后，都不會修改到磁盤中需要commit一下
commit:提交語句，在這中間如果有錯就回滾rollback
rollback：回滾乎折，所有指令全部失效
創(chuàng)建用戶
create user '用戶名'@‘地址’ identified by '密碼';如果地址寫%代表所有ip
mysql -h地址 -u用戶名 -p密碼
刪除用戶
drop user '用戶名'@'地址'
授予權(quán)限：創(chuàng)建用戶名以后并不能查看他人的庫文件绒疗，只有授予權(quán)限以后才能查看
grant 權(quán)限 on . '用戶'@‘地址’
.代表所有庫的所有表
kongkong.user是查看空空庫的里面的user表
收回權(quán)限
revoke 權(quán)限 on . ‘用戶名’@‘地址’
修改當前用戶的密碼：
set password = password('123456')
