首页 > 代码库 > 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