Shulou(Shulou.com)06/01 Report--

The following brings you the basic sql statement operation process and steps of the database, hoping to give you some help in practical application, the database involves more things, theory is not much, there are many books on the Internet, today we will use the accumulated experience in the industry to do a solution.

################################

yum install -y mariadb-server

systectl stop firewalld

1. landing

mysql -uroot -p123 ####-u indicates login user, -p indicates user password

2. query

show databases; ####Reality databases

use mysql; ####Use mysql database

show tables; ####Displays the table names of the current library

select * from user; ####Query user table information

desc user; ####Query user table structure

3. Establishment of database and tables

create database westos ###Create database westos

create linux table ( ###Create linux table with usernam field no more than 15 characters

-> username varchar(15) not null, password field does not exceed 50 characters and none

-> password varchar(50) not null); can be null

insert into linux values ('user 1','123'); user1 has no encryption password for 123

insert into linux values ('user 3', password ('123 ')); user3 encrypted password is a string of ciphertext: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257

4 Update database information

update linux set password=password ('123 ') where password='123'; ##Update all 123 to ciphertext

+----------+-------------------------------------------+

| username | password |

+----------+-------------------------------------------+

| user | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| user1 | 0 |

| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

+----------+-------------------------------------------+

alter table linux add class varchar(20) not null; ##Add class field to last column of table

+----------+-------------------------------------------+-------+

| username | password | class |

+----------+-------------------------------------------+-------+

| user | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |

| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |

| user1 | 0 | |

| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |

| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |

+----------+-------------------------------------------+-------+

lter table linux add date varchar(20) not null; ####Add date field to last column of table

+----------+-------------------------------------------+-------+------+

| 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; ####Add age field after password of table

+----------+-------------------------------------------+-----+-------+------+

| username | password | age | class | date |

+----------+-------------------------------------------+-----+-------+------+

| user | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | | |

| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | | |

| user1 | 0 | | | |

| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | | |

| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | | |

+----------+-------------------------------------------+-----+-------+------+

alter table linux drop class; ##Delete class column of table

5. delete database

delete from linux where username='user1'; ##Delete user1's data from linux tables

drop table linux; ##drop table linux

drop database westos; ##Delete database westos

6. database backup

mysqldump -u root -p123 --all-database ##Backup all data

mysqldump -u root -p123 --all-database --no-data ##Backup tables but not data

mysqldump -u root -p123 westos ##Backup database

mysql -u root -p123 westos >/mnt/westos. sql ##Export data from the library to/mnt/westos.sql

mysql -uroot -p123 westos -e "drop database westos;"##Delete westos library

mysql -uroot -p123 -e "create database westos;"##Create database 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@'%';####Authorize users

show grants for lee@'%';####View user's rights

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; ####Remove user authorization

drop user lee@'%';####Delete user

8. password modification

mysqladmin -uroot -p123 password lee ##Change password

####Forgetting password ###

mysqld_safe --skip-grants-table & open mysql login interface and ignore authorization table

update mysql.user set Passwoed=passwd ('123 ') where User='root'##Update password

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 ##End the process affecting the experiment

systemctl restart mariadb ##restart

mysql -uroot -p123 ##login test

Read the above basic sql statements about the database operation process and steps is what kind of, if you still have anything to know can find their interest in the industry information or find our professional technical engineers to answer, technical engineers in the industry has more than ten years of experience. Official website link www.yisu.com

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.