太简单的就不说了
**
嵌套查询
**
select c_name, grade from score where stu_id in (select id from student where name='哄哄';
**
连接查询
**
select sc.stu_id, st.name, sum(grade) sum_grade from score sc join student st on st.id=sc.id group by sc.stu_id;
**
连接与where子句联合
**
select st.name, st.department, sc.c_name,sc.grade from student st join score sc on st.id=sc.stu_id where st.name like '李%';
**
多表更新
**
update student st, score sc set st.name='tom', sc.grade=0 where st.id = sc.stu_id && st.name = 'jerry' && sc.c_name='running';
update student st inner join score sc on st.id = sc.stu_id set st.name='tom', sc.grade=0 where st.name = 'jerry' && sc.c_name='running';
**
创建用户和授予用户权限
**
create user ttt@'%' identified by '123456’; //ttt是用户名,123456是密码;
grant all on homew.* to ttt@'%'; //homew是数据库的名字,ttt是用户名;
revoke select on homew.* from ttt@'%';
show processlist
查询哪些线程正在执行,返回是结果是实时变化的。
id:ID标识;
user:当前连接用户;
host:显示这个连接从哪个ip的哪个端口上发出;
db:数据库名;
command:连接状态,一般是休眠(sleep)查询(query)连接(connect)
time:连接持续时间,单位是秒
state:显示当前sql语句的状态
info:显示这个sql语句