MySQL Database common syntax

1) Log in to the MySQL database.

Mysql–uroot–poldboy123

Mysql

2) View the currently logged on user.

Selectuser ();

3) Create the database Oldboy and view the complete statement of the built library .

Create Database Oldboy;

show databases;

Show CREATE Database Oldboy;

4) Create user Oldboy so that it can manage database Oldboy.

Create user [email protected] ' localhost ' identified by ' oldboy123 ';

Grant all on oldboy.* to [email protected] ' localhost ';

Grant all on oldboy.* [email protected] ' localhost ' indetified by oldboy123;

5) See what permissions the user Oldboy has created.

Show grants for [email protected] ' localhost ';

6) See which users are in the current database.

Select User,host from Mysql.user;

7) Enter the Oldboy database.

Use Oldboy;

8) View the database that is currently located.

Selectdatabase ();

9) Create a table test, field ID and name varchar (16).

CREATE TABLE test (ID int (4) NOT NULL, Namevarchar (+) not NULL);

10) View the SQL statement that constructs the table structure and table structure.

DESC test;

Show full columns from test;

One ) Insert a data "1,oldboy"

Insertinto Test (Id,name) VALUES (1, ' Oldboy ');

SELECT * from Test;

) then BULK INSERT 2 rows of data "2, Old boy", "3,oldboyedu".

INSERT into Test (Id,name) VALUES (2, ' old boy '), (3, ' oldboyedu ');

SELECT * from Test;

search for records with the name Oldboy.

SELECT * FROM test where name= ' Oldboy ';

14) Change the name of the data ID equal to 1 oldboy to Oldgirl.

Update test set name= ' Oldgirl ' where id=1;

SELECT * from Test;

15) Insert the Age field before the field name, type tinyint (2).

ALTER TABLE test add age tinyint (2) after ID;

DESC test;

16) Do not exit the database backup Oldboy database.

System Mysqldump-uroot-poldboy123-b Oldboy >/opt/oldboy1.sql;

17) Delete all data from the test table and view.

Delete fromtest;

Truncate test;

18) Delete the table test and Oldboy database and view

Table:

Show tables;

drop table test;

Library:

Drop database Oldboy;

show databases;

19) Do not exit database recovery above deleted data.

Source/opt/oldboy1.sql

20) After setting the ID column as the primary key, create a normal index on the name field.

Primary key:

CREATE TABLE Test (

ID int (4) NOT NULL,--self-increment ID

Name Char (+) is not NULL,

Primary key (ID));

Common key:

ALTER TABLE test add index Intex_name (name);

21) Insert the Phone number field (Shouji) after the field name, type char (11).

ALTER TABLE test add Shouji char (one) after name;

DESC test;

22) Insert 2 records on all fields (self-setting data)

INSERT INTO Test (Id,name,shouji) VALUES (1, ' aige ', ' 13555555 '), (2, ' Oldboy ', ' 1388888888 ');

INSERT into Test (Id,name,shouji) VALUES (3, ' Oldboy ', ' 135555555 ');

SELECT * from Test;

23) Delete the index of the name column .

Drop index intex_name on test;

24) Check the mobile phone number starts with 135, the name is Oldboy record (early insertion).

SELECT * FROM test where Shouji like ' 135% ' and the name like ' Oldboy ';

recover The Oldboy user's select permission.

Revoke select on oldboy.* from [email protected] ' localhost ';

Shell terminal execution uses the-e parameter to invoke the MySQL internal command

MYSQL-UROOT-POLDBOY123-E "show grants [email protected] ' localhost ' | Grep-i Select

26) Delete the Oldboy user.

Select User,host from Mysql.user;

drop user [email protected] ' localhost ';

Select User,host from Mysql.user;

27) Delete the Oldboy database.

Drop database Oldboy;

use Mysqladmin to close the database.

mysqladmin-uroot-poldboy123 shutdown

Ps-ef | grep MySQL

This article is from the "Serve the" blog, please be sure to keep this source http://junhun.blog.51cto.com/12852949/1926334

MySQL Database common syntax