22、数据库权限
1、use mysql; ==》使用MySQL数据库
2、select host,user from user; ==》查看mysql库用户表有哪些用户
localhost 和127.0.0.1表示本地用户
% 表示具有远程访问的权限用户
3、insert into user(host,user,password)values('localhost','dcs11',password('123456'));
==》往user表插入一个dcs11用户,没有赋权
flush privileges; ==》刷新权限
4、show grants for 'dcs11'@'localhost'; ==》查看dcs11这个用户是否具有权限
GRANT USAGE ON *.* TO 'dcs11'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
==》没有权限
5、grant select,update,delete,alter,drop on *.* to 'dcs11'@'localhost' identified by '123456';
==》给dcs11这个用户进行赋权
6、flush privileges; ==》刷新权限
7、revoke all on *.* from 'dcs11'@'localhost'; ==》移除本地用户dcs11的所欲权限
flush privileges; ==》刷新权限
show grants for 'dcs11'@'localhost'; ==》查看dcs11这个用户的权限
8、update user set password=password('654321') where user = 'dcs11'; ==》更改dcs11这个用户的密码
flush privileges; ==》刷新权限
9、delete from user where user = 'dcs11'; ==》删除dcs11这个用户
【多表连接】
navicat ==》是连接数据库的客户端
怎么用navicat去连接数据库
1)自定义一个连接名
2)输入ip地址
3)端口3306 ==》mysql数据库的默认端口 oracle 默认端口1521 redies 默认端口 6379
4)账号
5)密码
如果连接不上
1)service mysqld restart 重启数据库
2)service iptables stop 关闭防火墙
3)检查登录用户是否有 %权限 (grant all privileges on *.* to 'root'@'%' identified by '123456')
4)检查是否填写有误
快捷键
ctrl+q ==》新建一个窗口
ctrl+w ==》关闭窗口
CTRL+r ==》运行语句
ctrl+鼠标 放大缩小
运行三种
1)左上角绿色三角形
2)ctrl+r
3)鼠标右键 选中
注释
# ==》单行注释
/* */ ==》多行注释
段注释/*
1、基本连接 ==》2张表中有字段值相同则可以进行连接,结果显示相同字段值的数据
select * from aa,cc where aa.id=cc.s_id;
2、内连接 ==》2张表中有字段值相同则可以进行连接,结果显示相同字段值的数据
select* from aa inner join cc on aa.id = cc.s_id;
3、左连接 ==》以左表为主,显示左表的全部数据,右表没有的数据以null填充
aa是左表 cc是右表
select * from aa left join cc on aa.id = cc.s_id;
4、右连接 ==》以右表为主,显示右表的全部数据,左表没有的数据以null填充
aa是右表 cc左表
select * from cc right join aa on aa.id = cc.s_id;
5、硬链接 (条件:2张表的字段必须相同)
select * from aa union select * from cc;
求张三的成绩?
select score from aa,cc where aa.id=cc.s_id and name = 'zhangsan';
select score from aa inner join cc on aa.id = cc.s_id where name = 'zhangsan';
select score from aa left join cc on aa.id = cc.s_id where name = 'zhangsan';
6、临时表法 先连表 select * from aa,cc where aa.id=cc.s_id;
select score from (select * from aa,cc where aa.id=cc.s_id)t where name = 'zhangsan';
求张三的成绩?
7、嵌套法 (in )
select id from aa where name = 'zhangsan'; ==>1001
select score from cc where s_id in (select id from aa where name = 'zhangsan');
8、嵌套 法 =
select id from aa where name = 'zhangsan'; ==>1001
select score from cc where s_id =(select id from aa where name = 'zhangsan');
求出谁没参加考试?
select name from aa LEFT JOIN cc on aa.id = cc.s_id where score is null;
select s_id from cc; ==》参加的学生 1001
select name from aa where id not in (select s_id from cc);
【处理mysql数据库中文乱码的问题】
1、vim /etc/my.cnf ==》数据库的配置文件
2、加入这行character_set_server=utf8
3、重启数据库
4、dcs这个库的数据库属性把Latin改为utf8编码格式 (1.字符集:utf8 -- UTF-8 Unicode 2.排序规则:utf8_general_ci)
==》重新创建表就可以看到中文了
|
|