MySQL database system basic additions and deletions to check

mysql> status; # # #查看数据库基本信息
·
mysql> show databases; # # # # # #查看数据库列表信息
·

mysql> use MySQL; # # # #切到mysql This library
·
Mysql> show Tables; # # # #查看着个库中有哪些表
·
mysql> use MySQL # # # #切到mysql This library
·
mysql> describe user; # # #查看着个user表的结构
·

# # # #创建新的数据库 # #
mysql> CREATE DATABASE auth; # # #新建一个auth数据库
Query OK, 1 row Affected (0.00 sec)
·
# # # #创建新的数据表 # # # CREATE table name (field definition ...)
mysql> use auth;
Mysql> CREATE TABLE users (user_name char (+) not NULL, user_passwd char ("DEFAULT", PRIMARY KEY (user_name));
·
# # # # #删除指定的数据表 # # DROP TABLE [database name.] Table name
Mysql> Use Auth
mysql> drop table auth.users; # # #删除auth中这个users表
Query OK, 0 rows affected (0.01 sec)
·
# # # #删除指定的数据库 # # DROP database name
mysql> drop Database auth; # # # #删除auth数据库
Query OK, 0 rows affected (0.01 sec)
·
# # # #向数据表中插入新的数据记录 # #
INSERT into table name (Field 1, Field 2, ...) Values (the Value of field 1, the value of field 2, ...)
Insert into users (USER_NAME,USER_PASSWD) VALUES (' Zhangsan ', password (' 123456 '));
# # #如果这条记录包含表中所有字段的值, the established field in the INSERT statement can be omitted
Insert into users values (' Lisi ', password (' 123456 '));
·
# # # #从数据表中查找符合条件的数据记录 # #
SELECT Field name 1, field Name 2 ... From table name WHERE conditional expression
SelectFrom Auth.users; # # # #authe中users表中所有的数据, too much data for caution
·
Select user_name,user_passwd from auth.users where user_name= ' Zhangsan '; # # # #查看数据库auth的users表中zhangsan Data
·
# # # #修改, update data records in Datasheet # # # # #
UPDATE table name SET field name 1= value 1[, field name 2= value 2] WHERE condition expression
Update auth.users set User_passwd=password (' 123456 ') where user_name= ' Lisi '; # # #修改lisi的密码
Update Mysql.user set Host=password (' 123456 ') where user= ' root ';
·
# # # #在数据表中删除指定的数据记录 # #
DELETE from table name WHERE conditional expression
Delete from auth.users where user_name= ' Lisi '; # # #删除lisi的数据记录
·
# # # #设置用户权限 (new user when no user exists) # # # # # # #
GRANT permission list on database name. Table name to User name @ source address [identified by ' Password ']
Grant SELECT on auth.To ' xiaoqi ' @ ' localhost ' identified by ' 123456 ';
Flush privileges; # # #刷新权限
·
##### #退出mysql用xiaoqi账户登录 # # # #
[Email protected] ~]# mysql-u xiaoqi-p # #登录
Enter Password: # #输入密码
·
###### #验证xiaoqi #####
Mysql> SelectFrom auth.users; # # #验证授权的访问操作
+-----------+-------------------------------------------+
| User_name | user_passwd |
+-----------+-------------------------------------------+
| Zhangsan | 6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
+-----------+-------------------------------------------+
1 row in Set (0.00 sec)
·
Mysql> SelectFrom Auth.user; # # #验证非授权的访问操作
ERROR 1146 (42S02): Table ' auth.user ' doesn ' t exist
·
####### #创建bdqn数据库, authorized 192.168.32.2 host connection, user name dbuser password for [email protected] Allow BDQN Library Operation # # #
Create Database bdqn; # # #创建bdqn数据库
Grant all on Auth.To ' dbuser ' @ ' 192.168.32.' identified by ' [email protected] '; # # #做授权 # # #
Grant create on BDQN.To ' dbuser ' @ ' 192.168.32.2 ' identified by ' [email protected] ';
Flush privileges; # # #权限刷新
GRANT all on BDQN.To ' dbuser ' @ ' 192.168.32.2 ' identified by ' [email protected] ';
·
·
# # # # #查看用户的权限 #####
SHOW GRANTS for User name @ Source Address
·
Mysql> Show grants for ' Dbuser ' @ ' 192.168.32.2 '; # # #查看用户的权限
+-------------------------------------------------------------+
| Grants for [email protected] |
+-------------------------------------------------------------+
| GRANT USAGE on.To ' dbuser ' @ ' 192.168.32.2 ' |
| GRANT all privileges on "BDQN".To ' dbuser ' @ ' 192.168.32.2 ' |
+-------------------------------------------------------------+
2 rows in Set (0.00 sec)
·
·
# # # #撤销用户的权限 #####
REVOKE permissions list on database name. Table name from user name @ Source Address
Revoke all on BDQN.From ' dbuser ' @ ' 192.168.32.2 ';
Revoke all on BDQN.From ' Xiaoqi ' @ ' localhost ';

MySQL database system basic additions and deletions to check