Mysql SQL statement _ MySQL

Mysql operation SQL statement 2. database operation SQL statement
1. display the current database on the server
Show databases;

2. create a database named rewin
Create database rewin;

3. delete a database named rewin
Drop database rewin;

4. select rewin database
USE rewin;

3. operate SQL statements in tables (after logon, you must USE the preceding USE command to select a database before performing table operations)
1. display the tables in the current database
Show tables;

2. create a database table zhangyan: paste the following SQL statement after mysql>. the storage engine is MYISAM, and the Field id is the primary key and unique index.
Create table 'hangday '(
'Id' INT (5) unsigned not null AUTO_INCREMENT,
'Username' VARCHAR (20) not null,
'Password' CHAR (32) not null,
'Time' datetime not null,
'Number' FLOAT (10) not null,
'Content' text not null,
Primary key ('id ')
) ENGINE = MYISAM;

3. view the structure of the zhangyan table
DESCRIBE zhangyan;

4. retrieve information from the table
4.1 retrieve all records from the zhangyan table
SELECT * FROM zhangyan;

4.2 retrieve specific rows from the zhangyan table: The field username is equal to abc, and the Field number is equal to 1, which are sorted in descending order by Field id
SELECT * FROM zhangyan WHERE username = 'abc' AND number = '1' order by id DESC;

4.3 retrieve the specified fields from the zhangyan table: username and password
SELECT username, password FROM zhangyan;

4.4 retrieve unique non-repeated records from the zhangyan table:
Select distinct username FROM zhangyan;

5. Insert information to the zhangyan table
Insert into zhangyan (id, username, password, time, number, content) VALUES ('', 'ABC', '123', '2017-08-06 14:32:12 ', '23. 41 ', 'Hello World ');

6. update the specified information in the zhangyan table.
UPDATE zhangyan SET content = 'Hello China' WHERE username = 'abc ';

7. delete the specified information in the zhangyan table
Delete from zhangyan WHERE id = 1;

8. clear the zhangyan table
Delete from zhangyan;

9. delete the zhangyan table
Drop table zhangyan;

10. change the table structure and change the field type of the username field in the zhangyan table to CHAR (25)
Alter table zhangyan CHANGE username CHAR (25 );

11. import mysql. SQL in the current directory to the database
SOURCE./mysql. SQL;

IV. database permission operation SQL statements
1. create a user sina with root permission and can log on from any IP address. the password is zhangyan.
Grant all privileges on *. * TO 'sina '@' % 'identified by 'zhangyan' with grant option;
Flush privileges;

2. create a user with the "data operation" and "structure operation" permissions. the password is zhangyan.
Grant select, INSERT, UPDATE, DELETE, FILE, CREATE, DROP, INDEX, ALTER, create temporary tables, create view, show view, create routine, alter routine, execute on *. * TO 'sina '@ '2017. 168.1.% 'identified BY 'zhangyan ';

3. create a user that only has the "data operation" permission and can only log on from 192.168.1.24. the user sina can only operate on the zhangyan table of rewin database, and the password is zhangyan.
Grant select, INSERT, UPDATE, delete on rewin. zhangyan TO 'sina '@ '192. 168.1.24' identified by 'zhangyan ';

4. create a user with the "data operation" and "structure operation" permissions, which can log on from any IP address and can only operate on the rewin database user sina with the password zhangyan
Grant select, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, create temporary tables, create view, show view, create routine, alter routine, execute on rewin. * TO 'sina '@' % 'identified by 'zhangyan ';

5. delete a user
Drop user 'sina '@' % ';