數據庫的基本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.neiyidaogou.com