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

The following content mainly brings you a detailed explanation of mysql professional sentences, the knowledge mentioned is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, and have a certain value of experience sharing, hoping to bring help to the majority of readers.

I. character set

1. List available character sets

Show character set

two。 List the proofreading rules for a character set

Show collation like 'latin1%'

3. Database character set and proofreading rules

Create database li default character set latin1 collate latin1_swedish_ci

4. CVM character set and proofreading rules

(1) specify parameter settings at compile time

. / configure-with-charset=latin1-with-collation=latin1_german1_ci

(2) initial option setting when starting mysql

Mysqld-default-character-set=latin1-default-collation=latin1_swedish_ci

5. Table character set and proofreading rules

Create table test1 (id int,name char (20)) default character set latin1 collate latin1_danish_ci

6. Column character set and proofreading rules

Create table test2 (coll1 varchar (5) character set latin1 collate latin1_german1_ci)

7 display all available character sets

Show character set like 'latin1%'

8. Show create database statement

Show create database li

9. Displays the number of connections for the user

Show processlist (100 items are listed by default)

Show full processlist (list all)

2. Detailed explanation of sql sentence

1.alter statement

(1) change the column name

Alter table test change id number integer; renamed the id of an int type column to number

(2) change the column type

Alter table test change number a bigint

2.drop statement

(1) drop database

Drop database li

(2) drop index

Drop index test3

(3) drop table

Drop table test

3.rename statement

(1) rename table

Rename table test1 to test

4.delete statement

Delete from test

5.insert statement

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

6.load data file statement

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table

III. User management

1. Create a user

Create user wang@localhost identified by '123456'

two。 Delete user

Drop user wang@localhost

3. Create an authorized user

Grant create,delete on test.* to wang@'192.168.10.%' identified by '123456'

4. View users

Select user,host from mysql.user

5. Refresh authorization table

Flush privileges

6. View user permissions

Show grants for wang

7. Change password

If it is the initial login, it is an empty password and needs to be set.

Mysqladmin-uroot-password '123456'

Change the password if you know the original password

Mysqladmin-uroot-p123456 password '1234567'

If you have changed but forgotten, and have logged out of Mysql, you need to skip the authorization table to start mysql

First stop the running mysql

Then mysqld_safe-- skip-grant-tables &

Mysql-uroot

Update mysql.user set password=password ('123456') where user='root'

Flush privileges

View users

Select user ()

IV. Mysql monitoring

1. View the disk usage space of a database

Select concat (truncate (sum (data_length) / 1024 sum 1024), 'MB') as data_size

Concat (truncate (sum (max_data_length) / 1024 sum 1024), 'MB') as max_data_size

Concat (truncate (sum (data_free) / 1024 sum 1024), 'MB') as data_free

Concat (truncate (sum (index_length) / 1024 sum 1024), 'MB') as index_size

From information_schema.tables where TABLE_SCHEMA = 'databasename'

two。 View the disk space of all databases

Select TABLE_SCHEMA,concat (truncate (sum (data_length) / 1024 MB' 1024 as max_data_size), 'MB') as data_size, concat (truncate (sum (max_data_length) / 1024 Ligue 1024),' MB') as max_data_size

Concat (truncate (sum (data_free) / 1024 sum 1024), 'MB') as data_free

Concat (truncate (sum (index_length) / 1024 sum 1024), 'MB') as index_size

From information_schema.tables group by TABLE_SCHEMA

3. Query the space of a table

Select concat (truncate (sum (data_length) / 1024 sum 1024), 'MB') as data_size

Concat (truncate (sum (max_data_length) / 1024 sum 1024), 'MB') as max_data_size

Concat (truncate (sum (data_free) / 1024 sum 1024), 'MB') as data_free

Concat (truncate (sum (index_length) / 1024 sum 1024), 'MB') as index_size

From information_schema.tables

Where TABLE_NAME = 'tablename'

For the detailed explanation of the above professional sentences about mysql, if you have more to know, you can continue to pay attention to the innovation of our industry. If you need to get professional answers, you can contact the pre-sale and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.