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

How to choose the version of 1.mysql

Because the performance of mysql is greatly optimized, mysql 5.5or 5.6is generally chosen.

The particularity of mysql 5.5.xx-5.6.xx products, so the compilation method is also different from the early product installation, using cmake or gmake to compile and install. That is,. / cmake; make; make install. For the specific commands and parameters of the production scenario, please see mysql one-click installation script for details.

One-click installation script download link http://down.51cto.com/data/2228998

two。 Set the initial password for mysql

There is no initial username and password after mysql installation, so we need to set an initial password for mysql

Mysqladmin-uroot password 1qaz@WSX? -- (remarks, mysqladmin, not mysql)

3. View the database

Show databases

4. Use a library

Use library name

5. View the current database

Select database ()

6. Delete a library

Drop database test (library name is test)

7. Use a library

Use library name

8. Check the table after entering the library

Show tables

9. View table structure

Desc table name

Or select * from table name

10. View all users

Select host,user from mysql.user

11. Delete user

Drop is usually used to delete users, but if there are uppercase or special characters in the name, drop may have a problem and will use delete

Drop user'@ 'localhost'

Drop user "@" localhost.localdomain "

Or

Delete from mysql.user where host= "127.0.0.1"

Flush privileges

twelve。 Create a new user and empower the user / new administrator

Grant all on *. * to 'dailiang'@'%' identified by' 1qazure WSX

Flush privileges

In this way, the difference between the user and the administrator is that he does not have grant privileges.

Grant all on *. * to 'dailiang'@'%' identified by' 1qazure WSX With grant option

13. Only set query permissions

Grant select on *. * to 'dailiang'@'%' identified by' 1qazure WSX

Flush privileges

Grant select on *. * to 'dailiang'@'10.0.0.%' identified by' 1qazure WSX

Indicates authorization of the 10.0.0.x network segment

13. View user permissions

Show grants for 'dailiang'@'%'

Or

Select * from mysql.user where user='dailiang'\ G

14. Take back the revoke of permission

Help revoke

REVOKE INSERT ON *. * FROM 'jeffrey'@'localhost'

15. Execute sql interactively without logging in to the database

Mysql-uroot-p1qaz@WSX?-e "show grants for 'dailiang'@'%';"

16.mysql users have a total of 18 permissions

Select

Insert

Update

Delete

Create

Drop

Grant

References

Index

Alter

17. Modify a user's password

UPDATE mysql.user SET password=PASSWORD ('new password') WHERE user=' username

Update mysql.user set password=PASSWORD ('111') where user='dailiang' and host='%'

Flush privileges

Note:

1. If the WHERE condition is not added, the passwords of all users will be changed to 'new passwords'

After the password modification is completed, the permission refresh operation is required to take effect. FLUSH PRIVILEGES

ROOT users can change their own passwords or other users' passwords

Other users can only change their own passwords

2.PASSWORD function

Mysql > SELECT PASSWORD ('111')

Used to encrypt the plaintext of the password, the resulting password is the hash value of the original password.

Mysql > SELECT PASSWORD ('111')

+-- +

| | PASSWORD ('111') | |

+-- +

| | * 832EB84CB764129D05D498ED9CA7E5CE9B8F83EB |

+-- +

1 row in set (0.00 sec)

18. Construction table sentence

The format of the table is:

Create table table name (

Field name 1 Type 1

Field name 2 Type 2

Lot name 3 Type 3

)

Note: pay attention to the position of the comma

Example of creating a table:

CREATE table student (

Id int (4) NOT NULL

Name CHAR (20) NOT NULL

Age TINYINT (2) NOT NULL DEFAULT'0'

Dept VARCHAR (16) DEFAULT NULL

);

Int integer type (4) refers to the length not null means that it is not allowed to be empty

The structure of the table is as follows:

Mysql > desc student

Look at the original table statement:

Show create table student\ G

Mysql > show create table student\ G

* * 1. Row *

Table: student

Create Table: CREATE TABLE `student` (

`id`int (4) NOT NULL

`name` char (20) NOT NULL

`age`tinyint (2) NOT NULL DEFAULT'0'

`dept` varchar (16) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Explanation: innoDB engine is used after mysql 5.5. the default engine for mysql5.1 and before is MyISAM.

Field types of the MYSQL table:

Int integer type

Char fixed-length string type, when storing, always fill the right side with spaces to the specified length

Varchar variable length string type

19. Knowledge about index (primary key)

An index is like a book's catalogue. if an index is established on a field, it can speed up the query when the index is used as a query condition, which is one of the important contents of mysql optimization.

Primary key index:

To query the database, pressing the primary key is the fastest. Each table can only have one primary key column, but it can have multiple ordinary index columns. The primary key column requires that all the contents must be unique, while the index column does not require that the content be unique.

The primary key is like our student number or × × number at school, it is unique, and the index class is not unique.

It can be said that the primary key is a special index

Index:

First of all, whether you set up a primary key index or a general index, you need to create an index on the corresponding column of the table, either on a single column or on multiple columns.

CREATE table student02 (

Id int (4) NOT NULL AUTO_INCREMENT

Name CHAR (20) NOT NULL

Age TINYINT (2) NOT NULL DEFAULT'0'

Dept VARCHAR (16) DEFAULT NULL

PRIMARY KEY (id)

KEY INDEX_NAME (NAME)

);

Parsing:

AUTO_INCREMENT → self-increment

PRIMARY KEY (id) → PRI primary key, with id as the primary key

KEY INDEX_NAME (NAME) → General Index

Mysql > desc student02

PRI is the primary key index

MUL is a general index

After building a table, you can increase the primary key index through the alter command, but this is generally not recommended.

Alter table student change id id int primary key auto_increment

Before adding the primary key:

After adding the primary key:

View the indexes and primary keys of a table:

Show index from table name\ G

Delete the normal index after creating the table:

Alter table student02 drop index index_name

Index_name is the name of the index

Add a general index after table creation:

Alter table student02 add index index_name (name)

Index_name refers to the name of the index

Name is the name of the line, which is added to the line name.

Create a federated index:

Create index id_name_dept on student (name,dept)

Note: red can be used as the index name, but it is better to change it according to the standard in order to change it in the future.

Create a unique index:

Create unique index index_ind_name on student (name)

When it's built, it will be UNI.

OK, finally, let's talk about the specific application of the index.

Question 1: since indexes can speed up queries, why not index all columns?

Answer: because the index not only takes up the system space, but also needs to maintain the index data when updating the database, so the index is a double-edged sword, not the more the better. For example, smaller tables do not need to be indexed. Businesses that write frequently and read less need to build indexes less, because writing once requires updating the index.

Because just like the word directory, you need to update the directory after you write something, otherwise it will be messy. This is why it is troublesome to add indexes to frequently written databases. When the table is updated, you need to update the index.

Question 2: on which columns is better to create an index?

Select host,user from mysql.user where user='xxx'

The index is usually built on the condition column after where

Insert inserted by 20.mysql

First build a simple test table

Create table test (

Id int (4) NOT NULL AUTO_INCREMENT

Name varchar (20) NOT NULL

PRIMARY KEY (id)

);

Ok, the preparations are done. Start inserting the first entry.

Insert a single piece of data:

Insert into test (id,name) value (1dailiang`)

Insert multiple pieces of data:

Insert into test (id,name) value (2 recorder dailiang02'), (3 recorder dailiang03')

Note: things like number 2 do not need single quotation, but string series such as dailiang need single quotation.

To insert data, it is best to insert data in batch, not one by one.

Select statement of 21.mysql

Limit usage:

Select id,namefrom test limit 2 only checks two lines.

Select id,namefrom test limit 0pr 2; check only 0 to 2 lines

Conditional query:

Mysql > selectid,name from test where id=1

Mysql > selectid,name from test where name='oldgirl'

Attention: the string should be quoted.

Followed by a where conditional statement: and or

Mysql > selectid,name from test where name='oldgirl'and id=3

Mysql > selectid,name from test where name='oldgirl' or id=1

Range query:

Select id,name from test where id > 2 and id2 or id1 and student.Sno explain select Sage from student where Sage=18\ G

You can see that the Sage after where is not an index.

Add a normal index to this column:

Mysql > Alter table student add index index_Sage (Sage)

Now the index is used.

24. Modify the data in the table

Change the table data:

Update student set Sname=' Zhang Lai 'where Sno=2

Strong remarks: do not forget where, if you forget where, then the entire table Sname will be changed, easy to cause production accidents

25. Delete the entire table

Drop tablename

twenty-six。 Delete the table contents, but keep the table structure

Delete table name

Or

Truncat table name

Both are to empty the data in the table, but retain the contents of the table

* * all rows in the specified table are deleted, but the structure of the table and its columns, constraints, indexes, etc., remain unchanged * *

27.mysql stops the operation of slave library

Show processlist

Show slave status\ G

Slave stop

Service mysqld stop

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.