实验案例:
创建库,增、删、改、查
优化路径,echo "PATH=
P
A
T
H
:
/
u
s
r
/
l
o
c
a
l
/
m
y
s
q
l
/
b
i
n
"
>
>
/
r
o
o
t
/
.
b
a
s
h
p
r
o
f
i
l
e
,
将
m
y
s
q
l
添
加
到
PATH:/usr/local/mysql/bin" >> /root/.bash_profile,将mysql添加到
PATH:/usr/local/mysql/bin">>/root/.bashprofile,将mysql添加到PATH变量中,能够在mysql如何地方键入!
source /root/.bash_profile
这样就可以使用以下两条命令来关闭或者开启Mysql数据库系统了
mysqld.sh start
mysqld.sh stop
创建库:
创建一个名为benet1的数据库,create database benet1;
使用show databases;命令查看刚创建的库,可以看到刚创建的库已经创建成功!
删除库:
删除刚创建的benet1的库,在查看是否已经删除成功!
可以看到下图,benet1的库 已经删除成功!
切换到目标库:
use benet1;
进入benet1这个库里面创建表:
查看当前所在的库中包含的表:
show tables;
查看表的结构:
describe Theclass;
假如,没有进入到目标库,怎么创建在目标库里创建表:
切换到test库;原本到目标库位benet1:
Use test;
在test库里面往benet1里面创建表:
create table benet1.Theclass_new(Thename char(100),Theclass char(100),idcard char(18),Thephone char(11),address char(100));
往表里面插入数据:
insert into Theclass(Thename,Theclass,idcard,Thephone,address)values(‘laoda’,‘37’,‘123456789987765432’,‘9999999999’,‘guangzhou’);
insert into Theclass(Thename,Theclass,idcard,Thephone,address)values(‘zhangsan’,‘38’,‘12345678998776549987765431’,‘9999999998’,‘shanghai’);
insert into Theclass(Thename,Theclass,idcard,Thephone,address)values(‘lisi’,‘39’,'123456789987765476
insert into Theclass(Thename,Theclass,idcard,Thephone,address)values(‘wangwu’,‘40’,'12345678998776548776
insert into Theclass(Thename,Theclass,idcard,Thephone,address)values(‘xiaoliu’,‘41’,'123456789987765498
查看表里面的数据:
select * from Theclass;
只查看某一个人到数据:
select * from Theclass where Thename=‘zhangsan’;
或者只查看某一电话号码到人到数据:
select * from Theclass where Thephone=‘9999999998’;
只查看shanghai地区的人数据:
把表里面的“namjing”改成“hangzhou”:
update Theclass set address=‘hangzhou’ where address=‘namjing’;
然后再查看:select * from Theclass;
修改wangwu的电话号码:
update Theclass set Thephone=‘1594563258’ where Thename=‘wangwu’; 在查看数据表!
把laoda的address内容配置为密文:
update Theclass set address=PASSWORD(‘guangzhou’) where Thename=‘laoda’;在查看数据表!
删除”lisi”这一行:
delete from Theclass where Thename=‘lisi’;在查看数据表!
删除表:
drop table Theclass;
查看的时候,那个表已经被删除了
删除库:
drop database benet;
在查看:
show databases;
benet这个库已经不存在了!
授权:
新建用户并授权:
create user zihang identified by ‘P@ssw0rd’;
create database zihangdb;
grant select,insert,update on zihangdb.* to ‘zihang’@‘192.168.100.%’;
show grants for ‘zihang’@‘192.168.100.%’;
撤销权限:
revoke select,insert,update on zihangdb.* from ‘zihang’@‘192.168.10.%’;
show grants for ‘zihang’@‘192.168.100.%’;
drop user ziahng;
用户都不存在如何直接给用户授权:
rant select,insert,update on zihangdb.* to ‘python’@‘192.168.100.%’ identified by ‘P@ssw0rd’;
show grants for ‘python’@‘192.168.100.%’;
grant select,insert,update,delete on zihangdb.* to ‘python’@‘192.168.100.%’ identified by ‘P@ssw0rd’;
要求只有select权限怎么做:
revoke insert,update,delete on zihangdb.*from ‘python’@‘192.168.100.%’;
show grants for 'python'@'192.168.100.%';