Shulou(Shulou.com)06/01 Report--

This article mainly introduces some frequently used mysql database syntax, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand some frequently used mysql database syntax bar.

1) Log in to mysql database.

Mysql-uroot-poldboy123

Mysql

2) View the currently logged-in users.

Selectuser ()

3) create the database oldboy and view the complete statement of the built database.

Create database oldboy

Show databases

Show create database oldboy

4) create a user oldboy so that it can manage database oldboy.

Create user oldboy@'localhost' identified by'oldboy123'

Grant all on oldboy.* to oldboy@'localhost'

Grant all on oldboy.* tooldboy@'localhost' indetified by oldboy123

5) check what permissions the created user oldboy has.

Show grants for oldboy@'localhost'

6) check which users are in the current database.

Select user,host from mysql.user

7) enter the oldboy database.

Use oldboy

8) View the current database.

Selectdatabase ()

9) create a table test with fields id and name varchar (16).

Create table test (id int 4) not null, namevarchar 16 not null)

10) View the table structure and the SQL statement of the table structure.

Desc test

Show full columns from test

11) insert a piece of data "1J oldboy"

Insertinto test (id,name) values (1)

Select * from test

12) insert another 2 rows of data "2, old boy" and "3 # oldboyedu" in batches.

Insert into test (id,name) values (2) 'old boy'), (3)

Select * from test

13) query the record named oldboy.

Select * from test where name='oldboy'

14) change the name oldboy where the data id equals 1 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 to backup the oldboy database.

System mysqldump-uroot-poldboy123-B oldboy > / opt/oldboy1.sql

17) Delete all data in the test table and view it.

Delete fromtest

Truncate test

18) Delete tables test and oldboy databases and view

Table:

Show tables

Drop table test

Library:

Drop database oldboy

Show databases

19) do not exit the database to recover the data deleted above.

Source / opt/oldboy1.sql

20) create a normal index on the Name field by setting the id column as the primary key.

Primary key:

Create table test (

Id int (4) not null,-- self-increasing ID

Name char (16) not null

Primary key (id))

Common key:

Alter table test add index intex_name (name)

21) insert the mobile number field (shouji), type char (11), after the field name.

Alter table test add shouji char (11) after name

Desc test

22) insert 2 records on all fields (set data by yourself)

Insert into test (id,name,shouji) values (1355555555'), (2)

Insert into test (id,name,shouji) values (135555555')

Select * from test

23) delete the index of the Name column.

Drop index intex_name on test

24) query the records whose mobile phone number starts with 135and the name is oldboy (insert in advance).

Select * from test where shouji like' 135% 'and name like'oldboy'

25) revoke the select permission of the oldboy user.

Revoke select on oldboy.* from oldboy@'localhost'

Shell terminal executes calling mysql internal commands using the-e parameter

Mysql-uroot-poldboy123-e "show grants forroot@'localhost'" | grep-I select

26) delete the oldboy user.

Select user,host from mysql.user

Drop user oldboy@'localhost'

Select user,host from mysql.user

27) delete the oldboy database.

Drop database oldboy

28) use mysqladmin to close the database.

Mysqladmin-uroot-poldboy123 shutdown

Ps-ef | grep mysql

After reading the above about some frequently used mysql database syntax, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.