Common mysql operations

Common mysql operations
1. mysql insert update on duplicate

If the update key conflict occurs, update the data according to the specified rules. Sometimes this is more convenient. Another method is to update data in a table. In this way, the status of table_ B can be synchronized to table_a, which is similar to that of Multi-table queries.

insert into myblog (id,title,ctime) values(123,'hello',now())on duplicate key update title=values(title),ctime=values(ctime);update table_a a , table_b b set a.shop_status = b.group_status where a.shop_id = b.shop_id;

2. Insert: select content from another table and insert it to the new table.

Import all data in the blog_bak table to myblog. The table blog and blog_bak should have the same table structure.

insert into myblog( blog,ctime) select * from blog_bak;

3. mysql Index

To add an index to a table, you can add an index to multiple fields in the following two ways. The corresponding method for deleting an index is also listed below.

create index index_name on table_name (column_list);alter table table_name add index index_name (column_list);drop index index_name on table;alter table table_name drop index index_name;

4. mysql show

If you encounter warnnings, how can you view it? You can use the following command to view the information of the entire table. You can find the comment information of your table. You can use the last command "full fields from.

show warnings;show errors;show tables like '%talname%';show full fields from your_table;

5. mysql Data Import and Export

Truncate table clears the data in the table, and fields including auto_increment are reset. You can use mysqldump to export the table, export the database, export the table structure, and export the data in the table. Or the overall export, the corresponding shell commands are available below.

Mysqldump-h localhost-ppasswd-uroot-d database> dump. SQL; # export only the database structure mysqldump-h localhost-ppasswd-uroot database> dump. SQL; # export the database structure and all data mysqldump-h localhost-ppasswd-uroot-d database tablename> dump. SQL; # export only the table structure mysqldump-h localhost-ppasswd-uroot database tablename> dump. SQL; # export the table structure and data mysql-u root-p yourpasswd-h localhost yourdb <dump. SQL # Dumping dump. SQL export to your database

6. Several simple time processing functions of mysql

Do not write day as days, month, and hour. Similarly, group by has multiple fields with lower priority from col_a-> col_ B-> col_c.

select date_format(now(),"%Y-%m-%d %H:%i:%s") now;select date_sub(now(), interval 10 day) as yesterday; select * from test_table where status = 1 order by col_a desc, col_b desc, col_c asc limit 100;

7. mysql Variables

You can assign values to the variables by using select. If yes, you are not mistaken. You can assign values to @ a in either of the two ways. Remember to use @ when using it. Just like $ in PHP, this variable is only valid in this link period.

set @a = 100;set @a:=100;select @a:=300;  

8. mysql Stored Procedure

The following is an example of a simple stored procedure. Because the default terminator of mysql is, and this is the syntax of the stored procedure, change delimiter to $ before writing the stored procedure, after the stored procedure ends, change the delimiter to the default one. This is in line with our habits and finally calls the stored procedure call p (). The Code is as follows:

delimiter $ create procedure p()beginselect * from ttt;end;$delimiter ;     call p();       

9. mysql Chinese garbled characters, set to utf8.

The following is the mysql configuration file on my centos 6.5. mysql is installed through yum. It seems that all versions are 5.1 .... The content I added is in italic text to set the default character set to utf8. If some parts do not exist, add them by yourself. For example, if [client] does not exist, in my configuration file, I just added it myself. I don't know why the mysql configuration of that version is so amazing that I didn't believe my eyes when I opened it.

[Client]
Default_character_set = utf8
[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links = 0
Character_set_server = utf8
Init_connect = 'set NAMES utf8'
[Mysqld_safe]
Log-error =/var/log/mysqld. log
Pid-file =/var/run/mysqld. pid
Default-character-set = utf8

10. mysql permission management

All information about permission management is stored in the mysql database. Operations can be performed only by entering the root user. The first one is to create a user, and the host indicates where the user can connect to the database, which is safer to set the cost. Pay attention to the password () function. Flush privileges is used to make the modification take effect. The final grant is to authorize a database t_sweet to this user.

use mysql;insert into mysql.user(Host,User,Password) values("localhost","phplamp",password("1234"));flush privilegesgrant all privileges on t_sweet.* to sweet_b@localhost identified by 'yourpassword'

After setting it, you can take a look.

show variables like 'character%'