3, MySQL additions and deletions to change the user rights to grant system functions

<--Directory--

1) Database classification

2) System functions

3) Create a database

4) Select Database

5) Build a table statement

6) Inserting data

7) Delete the data from the user delete the database list

8) Modify table data

9) Query data

10) User Authorization

One) Show view all current library tables, build table statements, user permissions

DESC View Table Structure

"Database Classification"

In MySQL software, the database can be divided into two categories, the system database and the user database.

1. System Database

The system database refers to some data that comes with the MySQL server, as shown in

mysql> show databases;

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

| Database |

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

| Information_schema |

| MySQL |

| Ryzc_data |

| Test |

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

5 rows in Set (0.00 sec)

INFORMATION_SCHEMA: Some database-to-image information such as user information, column information, permission information, character set information and partition information in the primary storage system

Performance_schema: Primary Storage Database Server performance parameters

MySQL: User rights information for primary storage systems

Test: The database is automatically created for the MySQL database management system, and can be used by any user

2. User Database

A user database is a database that users create based on actual requirements, such as where the Userdatabase database belongs to the user database

"System Functions"

Version () #返回系统库的版本号

Databasee () #返回当前系统库名

User () #返回当前用户

#查询多个

Mysql> Select User (), database (), version ();

#查询单个

Mysql> Select User ();

"CREATE DATABASE"

CREATE DATABASE database_name

The database_name parameter in the preceding statement indicates the name of the database to be created

mysql> CREATE DATABASE Databasetest;

Query OK, 1 row Affected (0.00 sec)

Query OK: Indicates success of SQL statement execution

1 row affected: Indicates that the operation affects only one row of records in the data

0.00 SEC: Indicates when the operation was executed

Create DATABASE WSYHT_GBK default Character set GBK collate gbk_chinese_ci; #创建库wsyht_gbk并设置默认字符集gbk

Create DATABASE Wsyht_utf8 default character set UTF8 collate utf8_general_ci; #同理指定utf8字符集

"Select Database"

mysql> use MySQL

"Build Table Statement"

#创建表stdent

The ID field must be filled with a minimum of four-bit integers and cannot be bit empty

The Name field is a string with a minimum of 20 bits and cannot be empty

Use Wsyht

CREATE TABLE Student (

ID Int (4) is not NULL,

Name Char (a) is not NULL,

Age tinyint (2) is not NULL default ' 0 ',

Dept varchar (+) Default NULL

);

"Insert Data"

INSERT into student (Id,name) VALUES (1, ' wsyht ');

INSERT into student values (4, ' Jack ', +, ' abc '); #按照顺序插值

INSERT into student values (5, ' dock ', +, ' abc '), (6, ' Tom ', +, ' CAD '); #插入两个值

"Deletion of data from the user deleted from the database"

#删库

Drop database wsyht; #删除wsyht数据库

Drop user ' root ' @ ' localhost ' #删除系统多余账号, remove user table root host name localhost

#删用户

Delete from Mysql.user where user= ' root ' and host= ' localhost '; #删除root用户

Flush privileges; #删除用户需要刷新权限

#删表

drop table student;

#删数据

Delete from test where id=1; #删除test表id号等于1的行

Delete from Test where id>3; #删除test表id号等于3的行

TRUNCATE TABLE test; #直接清空整个表, test is the name of the table

Truncate emptying the physical file delete logic clears by row delete

"Modify table Data"

Update T1 set name= ' Jack ' where id=1; #更新表t1的name字段为jack, the condition is id=1

Rename table test to T1; #把test表改名为t1;

ALTER TABLE T1 rename to test; Rename the T1 table to test

"Query Data"

Select *from Student; #查询表所有数据的值

Select ID, name from student; #查询id the value of the Name field

Select Id,name from student limit 2; #只查询id, the value of the Name field 2 rows of data

Select Id,name from student where id=1; #条件查询, query the value of id=5

Select Id,name from student where name= ' Tom '; #字符患查询要加引号

Select Id,name from student where Name= ' Tom ' and id= ' 6 '; #两边都要成立

Select Id,name from student where name= ' Tom ' or id= ' 8 '; #一边成立即可

Select Id,name from student where id>3 and id<6; #范围内查询

Select Id,name from student order by ID; #排序id列, do not add default forward sort

Select Id,name from student order by ID ASC; #排序id列, plus ASC forward sort

Select Id,name from student order by id DESC; #排序id列, add desc reverse sort, need to specify column

Select User,host,password from Mysql.user order by user ASC; #user列升序排序

Select User,host,password from Mysql.user order by user desc; #user列倒序排序

"User Authorization"

#用户授权之后需要flush Privileges Refresh Permissions

Grant all on db1.* to [email protected] identified by ' WSYHT '; #授权用户, the host refers to the client to log on the host, here to write localhost allowed to log on native

Grant all privileges on * * to [email protected] identified by ' Peter '; #或这样授权用户, create users and authorize actions together

Create user [email protected] identified by ' WSYHT '; #先创建用户 (statement details meaning can see MySQL profile document)

Grant all on * * to [email protected]; #再授权用户

Create user [email protected] ' 10.0.0.% ' identified by ' wsyht '; #创建远程登陆账户

Grant all on * * to [e-mail protected] ' 10.0.0.% ' identified by ' wsyht '; #创建并授权远程登陆账户法1

Grant all on * * to [e-mail protected] ' 10.0.0.0/255.255.255.0 ' identified by ' test123 '; #创建并授权远程登陆账户法2

Revoke Insert on * * FROM [email protected]; #去除他所有库和所有表的insert权限, first look at which library and which table he has permissions for, and then remove permissions from the libraries and tables he authorized

Revoke all privileges,grant option from ' [email protected] '; #回收用户的所有权限

"Show View Info"

#查看当前所有库

show databases; #显示所有数据库

Show databases like '%wsyht% '; #显示所有含有wsyht名字的库

#查看库中的所有表

Show tables from D1; #显示d1库有多少表

Show tables; #显示当前库中所有的表

#查看建库建表语句

Show CREATE Database Wsyht_utf8; #显示创建的数据库的字符集

Show CREATE Database wsyht_gbk\g; #加 \g display without a semicolon, view the statement of the built library

Show CREATE TABLE student\g; #查看已建表的语句

#查看权限

Show grants for [email protected]; #查看权限

"Desc Use"

View the structure of the build table

Use Wsyht #进入wsyht库

DESC student; #查看student表结构

This article is from the "Wsyht blog" blog, make sure to keep this source http://wsyht2015.blog.51cto.com/9014030/1787594

3, MySQL additions and deletions to change the user rights to grant system functions