数据库的基本sql语句操作流程及步骤是怎么样的

下文给大家带来数据库的基本sql语句操作流程及步骤是怎么样的，希望能够给大家在实际运用中带来一定的帮助，数据库涉及的东西比较多，理论也不多，网上有很多书籍，今天我们就用亿速云在行业内累计的经验来做一个解答。

##############数据库的基本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                ##登陆测试

看了以上关于数据库的基本sql语句操作流程及步骤是怎么样的，如果大家还有什么地方需要了解的可以在亿速云行业资讯里查找自己感兴趣的或者找我们的专业技术工程师解答的，亿速云技术工程师在行业内拥有十几年的经验了。亿速云官网链接www.yisu.com