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

1. Log in to the MySQL database.

Mysql-uroot-poldboy123

two。 View the currently logged in user.

Select user ()

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 to manage the database oldboy.

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

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

Grant all on oldboy.* to oldboy@'localhost' identified by oldboy123

5. See what permissions the created user oldboy has.

Show grants for oldboy@'localhost'

5. See which users are in the current database.

Select user,host from mysql.user

6. Access the oldboy database.

Use oldboy

7. View the current database.

Select database ()

8. Create a table test with fields id and name varchar (16).

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

9. View the table structure and the SQL statement of the table structure.

Desc test

Show columns from test

Show full columns from test

10. Insert a piece of data "1 ~ (nd) oldboy"

Insert into test (id,name) values (1)

Select * from test

11. Then insert 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

twelve。 Query the record named oldboy.

Select * from test where name='oldboy'

Select * from test where name like'% old%'; (fuzzy lookup)

13. Change the name oldboy where the data id equals 1 to oldgirl.

Update test set name='oldgirl' where id=1

Select * from test

14. Insert the age field, type tinyint (2), before the field name.

Alter table test add age tinyint (2) after id

Desc test

15. Do not exit the database to backup the oldboy database.

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

16. Delete all data in the test table and view it.

Delete from test; method 1

Truncate test; method II

Select * from test; View

17. Delete tables test and oldboy databases and view

Table:

Show tables

Drop table test

Library:

Drop database oldboy

Show databases

18. Do not exit the database to recover the above deleted data.

Source / opt/oldboy1.sql

19. After setting the id column as the primary key, create a normal index on the Name field (to improve search efficiency).

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)

20. Insert the mobile number field (shouji), type char (11), after the field name.

Alter table test add shouji char (11) after name

Desc test

21. 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

twenty-two。 Delete the index of the Name column.

Drop index intex_name on test

23. 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'

24. Revoke the select rights of the oldboy user.

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

Show grants for oldboy@'localhost'; View

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

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

25. Delete the oldboy user.

Select user,host from mysql.user

Drop user oldboy@'localhost'

Select user,host from mysql.user

twenty-six。 Delete the oldboy database.

Drop database oldboy

twenty-seven。 Use mysqladmin to shut down the database.

Mysqladmin-uroot-poldboy123 shutdown

Ps-ef | grep mysql

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.