分组查询
select classid,count(*) from stu group by classid,sex;
格式:
select [字段名] * form 表名
[where 搜索条件]
[group by 分组字段 [having 子条件]]
[order by 排序 asc|desc]
[limit 分页参数]
数据库授权、备份和恢复
格式 ：grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码';
---实例：创建张三的账号，密码123，授权lamp61库下所有表的增删改查数据，来源地不限
grant select,insert,update,delete on lamp61.* to zhangsan@'%' identified by '123'
grant all on *.* to zhangsan@'%' identified by '123';
刷新
flush privileges
删除用户
drop user 'zhangsan'@'%'
查看当前用户信息
select user,host,password from mysql.user;
导入导出
mysqldump -u root -p mydb > mydb.sql 导出
mysql -u rooe -p mydb < mydb.sql 导入
多表联查
- 嵌套关系
select * from stu where age=(select max(age) from stu)- 关联查询
select s.id,s.name,s.classid,t.subject,t.grade from stu s,test t where s.id = t.sid;- 链接查询
select * s.id,s.name,s.classid,t.subject,t.grade from stu s left join test t on s.id = t.sid order by s.id asc;
