本帖最后由 武汉18期-杨铭 于 2022-8-9 11:50 编辑
- 备份数据,表,数据库,还原数据库
- 备份表
- create table +表1 like 表2
- create table ff like ym_user; 创建一个ff表,表结构跟ym_user一样
- insert into +表1名称 值 (表2数据)
- insert into ff select * from ym_user; 往ff表里面插入ym_user表中所有的数据
- insert into +表1(字段,字段)select 字段1,字段2 from表2
- insert into ff2(id,name,class)select id,name,class from ff; 往ff2表里面插入ff表中的id,name,class字段对应的数据
- 注意:插入的表是必须存在;插入的表是新表,没有数据
- 备份库
- mysqldump -uroot -p 数据库名 >脚本名
- mysqldump -uroot -p123456 ym>/yangming/ym01.sql
- 创建ymm这个库; create database ymm;
- 还原库
- mysqld -uroot -p +数据库 <脚本名
- mysql -uroot -p123456 ymm</yangming/ym01.sql
- 注意: ymm这个库要先存在,不然会找不到对应的库
- 用户权限
- select host,user,password from user; 查询有哪些用户
- 1.往user表新增一个dcs18的用户
- insert into user(host,user)values('localhost','dcs18');
- 2.show grants for 'dcs18@localhost'; ==》查看是否有权限,
- 3.flush privileges; ==》加载权限
- 4.grant select,update on *.* to 'dcs18'@'localhost' identified by 'null'; ==》增加权限
- *.* 表示所有数据库里面的表的表数据
- 5.show grants for 'dcs18'@'localhost'; ==》再次查看权限,具有查询,更改的权限
- 6.revoke all on *.* from 'dcs18'@'localhost'; ==》移除本地用户dcs18的所有权限
- 新建远程用户并赋予所有权限
- grant all privileges on *.* to 'dcs18'@'%' identified by '';
- 删除本地用户
- delete from user where user='dcs18'and host='localhost';
- update user set password=password('')where user='root' ;==》修改root的所有密码
- 两个表中字段的值相同时则可以进行拼接
- 基本链接
- select * from aa,cc where aa.id=cc.s_id;
- 内链接
- select * from aa,cc inner join cc on aa.id=cc.s_id;
- 左连接(左边表中的数据全部显示,右边表中符合条件的显示,不符合条件的则填充null,已左表有基准
- select * from aa left join cc on aa.id=cc.s_id;
- 右链接 (右边表中的数据全部显示,左边表中符合条件的显示,不符合条件的则填充null,已右表有基准)
- select * from aa right join cc on aa.id=cc.s_id;
- 求张三的成绩
- 临时表
- select * from aa,cc where aa.id=cc.s_id;
- select t.score from (select * from aa,cc where aa.id=cc.s_id) as t where t.name="zhangsan";
- 基本链接
- select * from aa,cc where aa.id=cc.s_id and aa.name="zhangsan";
- 嵌套方法(=和in的区别,=只能是一个值,in后面可以是多个值)
- select * from aa where name="zhangsan";
- select * from cc where s_id=(select id from aa where name="zhangsan");
- 嵌套"in"
- select * from cc where s_id in(select id from aa where name="zhangsan");
- 求谁没参加考试?
- select * from aa LEFT JOIN cc on aa.id=cc.s_id where cc.score is null;
- select * from cc RIGHT JOIN aa on aa.id=cc.s_id where cc.score is null;
- select id from aa,cc where aa.id=cc.s_id;
- select name from aa where aa.id not in (select id from aa,cc where aa.id=cc.s_id);
- select name from aa where id not in (SELECT s_id FROM cc);
|
-
|