MySQL Operation SQL statement

II. database Operation SQL statement
1. Show what database is currently on the server
SHOW DATABASES;

2. Create a database named Rewin
CREATE DATABASE Rewin;

3. Delete the database named Rewin
DROP DATABASE Rewin;

4. Select Rewin Database
Use Rewin;

Third, table operation SQL statement (after login must use the above command to select a database, and then table operation)
1. Show what table exists in the current database
SHOW TABLES;

2. Create database table Zhangyan: Paste the following SQL statement after mysql>, the storage engine is MyISAM, the field ID is the primary key, and the unique index.
CREATE TABLE ' Zhangyan ' (
' ID ' INT (5) UNSIGNED not NULL auto_increment,
' username ' VARCHAR (not NULL),
' Password ' CHAR (+) is not NULL,
' Time ' DATETIME is not NULL,
' Number ' FLOAT (Ten) is not NULL,
' Content ' TEXT is not NULL,
PRIMARY KEY (' id ')
) ENGINE = MYISAM;

3. View Zhangyan Table structure
DESCRIBE Zhangyan;

4. Retrieving information from the table
4.1. Retrieving all records from the Zhangyan table
SELECT * from Zhangyan;

4.2. Retrieving specific rows from the Zhangyan table: Field username equals ABC, field number equals 1, sorted by field ID Descending
SELECT * from Zhangyan WHERE username = ' abc ' and number= ' 1 ' ORDER by ID DESC;

4.3. Retrieving the specified fields from the Zhangyan table: username and password
SELECT username, password from Zhangyan;

4.4. Retrieve unique non-duplicate records from the Zhangyan table:
SELECT DISTINCT username from Zhangyan;

5. Insert information into the Zhangyan table
INSERT into Zhangyan (ID, username, password, time, number, content) VALUES (' ', ' abc ', ' 123456 ', ' 2007-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 from the Zhangyan table
DELETE from Zhangyan WHERE id = 1;

8. Empty the Zhangyan table
DELETE from Zhangyan;

9. Delete Zhangyan table
DROP TABLE Zhangyan;

10, change the table structure, the Zhangyan Table username field field type to char (25)
ALTER TABLE Zhangyan change username username CHAR (25);

11. Import the Mysql.sql in the current directory into the database
SOURCE./mysql.sql;

IV. Database Permissions Operations SQL statements
1, create a user with root privileges, can login from any IP Sina, password is Zhangyan
GRANT all privileges on * * to ' sina ' @ ' percent ' identified by ' Zhangyan ' with GRANT OPTION;
FLUSH privileges;

2, create a "data operation", "Structure operation" permission, only from 192.168.1.*** login user sina, password is Zhangyan
GRANT SELECT, INSERT, UPDATE, DELETE, FILE, create, DROP, INDEX, ALTER, create temporary TABLES, create VIEW, S How VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE on *. * to ' sina ' @ ' 192.168.1.% ' identified by ' Zhangyan ';

3, create a only "data operation" permission, only from 192.168.1.24 login, only the user Sina the Zhangyan table of Rewin database, the password is Zhangyan
GRANT SELECT, INSERT, UPDATE, DELETE on Rewin.zhangyan to ' Sina ' @ ' 192.168.1.24 ' identified by ' Zhangyan ';

4, create a "data operation", "Structure operation" permission, can log in from any IP, only operate Rewin database user Sina, password is Zhangyan
GRANT SELECT, INSERT, UPDATE, DELETE, create, DROP, INDEX, ALTER, create temporary TABLES, create VIEW, SHOW VIE W, CREATE ROUTINE, ALTER ROUTINE, EXECUTE on rewin.* to ' sina ' @ '% ' identified by ' Zhangyan ';

5. Delete users
DROP USER ' sina ' @ '% ';