數(shù)據(jù)庫的基本sql語句操作流程及步驟是怎么樣的

下文給大家?guī)頂?shù)據(jù)庫的基本sql語句操作流程及步驟是怎么樣的，希望能夠給大家在實(shí)際運(yùn)用中帶來一定的幫助，數(shù)據(jù)庫涉及的東西比較多，理論也不多，網(wǎng)上有很多書籍，今天我們就用億速云在行業(yè)內(nèi)累計的經(jīng)驗(yàn)來做一個解答。

##############數(shù)據(jù)庫的基本sql語句操作#############

yum install -y mariadb-server
systectl stop firewalld
1.登陸
mysql -uroot -p123        ####-u表示登陸用戶，-p表示用戶的密碼
2.查詢
show databases；          ####現(xiàn)實(shí)數(shù)據(jù)庫
use mysql；               ####使用mysql數(shù)據(jù)庫
show tables；             ####顯示當(dāng)前的庫的表名稱
select * from user；      ####查詢user表的信息
desc user；               ####查詢user表的結(jié)構(gòu)
3.數(shù)據(jù)庫及表的建立
create database westos       ###創(chuàng)建westos庫
create table linux(          ###創(chuàng)建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經(jīng)過加密 密碼是一串密文：*23AE809DDACAF96AF0FD78ED04B6A265E05AA257
4更新數(shù)據(jù)庫的信息
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.刪除數(shù)據(jù)庫
delete from linux where username='user1';    ##從linux表中刪除user1的數(shù)據(jù)
drop table linux;                            ##刪除linux表
drop database westos;                        ##刪除westos數(shù)據(jù)庫
6.數(shù)據(jù)庫的備份
mysqldump -u root -p123 --all-database    ##備份所有數(shù)據(jù)
mysqldump -u root -p123 --all-database --no-data  ##備份表但不備份數(shù)據(jù)
mysqldump -u root -p123 westos     ##備份數(shù)據(jù)庫
mysql -u root -p123 westos > /mnt/westos.sql   ##導(dǎo)出庫中的數(shù)據(jù)到/mnt/westos.sql
mysql -uroot -p123 westos -e "drop database westos;"  ##刪除westos庫
mysql -uroot -p123 -e "create database westos;"   ##創(chuàng)建westos庫
mysql -u root -p123 westos < /mnt/westos.sql     ##從/mnt/westos.sql導(dǎo)入數(shù)據(jù)
7.用戶授權(quán)
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;####給用戶授權(quán)
show grants for lee@localhost;               ####查看用戶的權(quán)力       
    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@'%';    ####給用戶授權(quán)
show grants for lee@'%';                       ####查看用戶的權(quán)力
      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;      ####去除用戶的授權(quán)權(quán)力
drop user lee@'%';            ####刪除用戶


8.密碼修改
 mysqladmin -uroot -p123 password lee   ##修改密碼
####忘記密碼的做法###
 mysqld_safe --skip-grants-table &    開啟mysql登陸接口并忽略授權(quán)表
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                  ##結(jié)束影響實(shí)驗(yàn)的進(jìn)程
systemctl restart mariadb         ##重啟
mysql -uroot -p123                ##登陸測試

看了以上關(guān)于數(shù)據(jù)庫的基本sql語句操作流程及步驟是怎么樣的，如果大家還有什么地方需要了解的可以在億速云行業(yè)資訊里查找自己感興趣的或者找我們的專業(yè)技術(shù)工程師解答的，億速云技術(shù)工程師在行業(yè)內(nèi)擁有十幾年的經(jīng)驗(yàn)了。億速云官網(wǎng)鏈接www.kemok4.com