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