内连接、外连接

内连接

隐示连接
select * from user join goods;连接两个表格得到的是笛卡尔乘积
select *from user join goods user.gid=goods.gid;连接两个表，查询两个表格
“gid”相等的数据。
select user.name,goods.name from user,goods where user.gid=goods.gid;查询对我们有意义的数据
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 表名;  清空表中所有数据，但是自增的id依然从清空前开始计
truncate table 表名;
        清空表中所有数据，但是id会从1开始计

set autocommit = 0;禁止自动提交，默认下是自动提交
set autocommit = 0;updata 更新数据设置本地(磁盘)和phpMyAdmin手动提交
commit；更新完数据后手动提交
set autocommit = 1;设置自动提交

begin 开启一个事务
在事务里面写了语句之后，都不会修改到磁盘中需要commit一下
commit:提交语句，在这中间如果有错就回滚rollback
rollback：回滚，所有指令全部失效

创建用户
create user '用户名'@‘地址’ identified by '密码';如果地址写%代表所有ip
mysql -h地址 -u用户名 -p密码
删除用户
drop user '用户名'@'地址'
授予权限：创建用户名以后并不能查看他人的库文件，只有授予权限以后才能查看
grant 权限 on . '用户'@‘地址’
.代表所有库的所有表
kongkong.user是查看空空库的里面的user表
收回权限
revoke 权限 on . ‘用户名’@‘地址’
修改当前用户的密码：
set password = password('123456')