首页 > 代码库 > mysql
mysql
##############数据库的基本sql语句操作#############
yum install -y mariadb-server
systectl stop firewalld
1.登陆
mysql -uroot -p123 ####-u表示登陆用户，-p表示用户的密码
2.查询
show databases； ####现实数据库
use mysql； ####使用mysql数据库
show tables； ####显示当前的库的表名称
select * from user； ####查询user表的信息
desc user； ####查询user表的结构
3.数据库及表的建立
create database westos ###创建westos库
create table linux( ###创建linux表，usernam字段不超过15个字符
-> username varchar(15) not null, password字段不超过50个字符并且全部不
-> password varchar(50) not null); 能为空
insert into linux values (‘user1‘,‘123‘); user1没有加密 密码为123
insert into linux values (‘user3‘,password(‘123‘)); user3经过加密 密码是一串密文：*23AE809DDACAF96AF0FD78ED04B6A265E05AA257
4更新数据库的信息
update linux set password=password(‘123‘) where password=‘123‘; ##将123全部更新为密文
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user1 | 0 |
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
alter table linux add class varchar(20) not null; ##添加class字段到表的最后一列
+----------+-------------------------------------------+-------+
| username | password | class |
+----------+-------------------------------------------+-------+
| user | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |
| user1 | 0 | |
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |
+----------+-------------------------------------------+-------+
lter table linux add date varchar(20) not null; ####添加date字段到表的最后一列
+----------+-------------------------------------------+-------+------+
| username | password | class | date |
+----------+-------------------------------------------+-------+------+
| user | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | |
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | |
| user1 | 0 | | |
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | |
+----------+-------------------------------------------+-------+------+
alter table linux add age varchar(20) not null after password; ####添加age字段到表的password之后
+----------+-------------------------------------------+-----+-------+------+
| username | password | age | class | date |
+----------+-------------------------------------------+-----+-------+------+
| user | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | | |
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | | |
| user1 | 0 | | | |
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | | |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | | |
+----------+-------------------------------------------+-----+-------+------+
alter table linux drop class; ##删除表的class列
5.删除数据库
delete from linux where username=‘user1‘; ##从linux表中删除user1的数据
drop table linux; ##删除linux表
drop database westos; ##删除westos数据库
6.数据库的备份
mysqldump -u root -p123 --all-database ##备份所有数据
mysqldump -u root -p123 --all-database --no-data ##备份表但不备份数据
mysqldump -u root -p123 westos ##备份数据库
mysql -u root -p123 westos > /mnt/westos.sql ##导出库中的数据到/mnt/westos.sql
mysql -uroot -p123 westos -e "drop database westos;" ##删除westos库
mysql -uroot -p123 -e "create database westos;" ##创建westos库
mysql -u root -p123 westos < /mnt/westos.sql ##从/mnt/westos.sql导入数据
7.用户授权
create user lee@‘%‘ identified by ‘lee‘;
+------+-----------+
| User | Host |
+------+-----------+
| lee | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
create user lee@localhost identified by ‘lee‘;
+------+-----------+
| User | Host |
+------+-----------+
| lee | % |
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
vim /etc/my.cnf
10 skip-networking=0
systemctl restart mariadb
grant insert,update,delete,select on westos.* to lee@localhost;####给用户授权
show grants for lee@localhost; ####查看用户的权力
MariaDB [(none)]> show grants for lee@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘lee‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*9BB439A3A652A9DAD3718215F77A7AA06108A267‘ |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `westos`.* TO ‘lee‘@‘localhost‘ |
grant insert,update on westos.* to lee@‘%‘; ####给用户授权
show grants for lee@‘%‘; ####查看用户的权力
MariaDB [(none)]> show grants for lee@‘%‘;
+----------------------------------------------------------------------------------------------------+
| Grants for lee@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘lee‘@‘%‘ IDENTIFIED BY PASSWORD ‘*9BB439A3A652A9DAD3718215F77A7AA06108A267‘ |
| GRANT INSERT, UPDATE ON `westos`.* TO ‘lee‘@‘%‘
revoke delete on westos.* from lee@localhost; ####去除用户的授权权力
drop user lee@‘%‘; ####删除用户
8.密码修改
mysqladmin -uroot -p123 password lee ##修改密码
####忘记密码的做法###
mysqld_safe --skip-grants-table & 开启mysql登陆接口并忽略授权表
update mysql.user set Passwoed=passwd(‘123‘) where User=‘root‘##更新密码
select User,Host,Password from mysql.user
------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | ::1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| lee | localhost | *9BB439A3A652A9DAD3718215F77A7AA06108A267 |
kill -9 mysqlpid ##结束影响实验的进程
systemctl restart mariadb ##重启
mysql -uroot -p123 ##登陆测试
mysql