Practical MySQL SQL statements for programmers

MySQL SQL statements that programmers use are only practical. MySQL SQL statements that programmers often use (excluding basic select, update, and delete statements ). 1. add a user build and grant all permissions to the command

[html] grant all privileges on *.* to 'build'@'%' identified by 'build' ;  

 

2. Command Line window Logon

[Html] mysql-uusername-ppassword [db_name] [db_name] If specified, enter the specific database. Example: [html] mysql-ubuild-pbuild mysql

 

3. Use gbk character encoding to display Chinese characters in the command line

[html] set names gbk;  

 

Similarly, I set other codes, such as: set names utf8 4. Switch the database

[html] use db_name;  

 

5. Run the script

[Html] notes for the source SQL _file source command:

 

1 ). in windows, use/to replace the default path \, for example, source F:/project/SQL/init. ddl 2 ). if SQL _file contains Chinese characters, ensure that the character encoding of SQL _file is the same as that of the database, and run the set names command before running the source command. for example, if the database is encoded as utf8, 1), make sure that the script file (. SQL ,. ddl) the character encoding is utf8. Before running the source command, run the command: set names utf8 (you can also put this command in SQL _file) 6. run the following command in the command line window:

[Html] mysqldump-u <username>-p <password> db_name> outfile_path: mysqldump-ubuild-pbuild mysql> e:/mysql. the default SQL statement is the dump table structure and data. If only the dump table structure is used, the command is as follows: [html] mysqldump -- opt-d <db_name>-u <username>-p <password> outfile_path: mysqldump -- opt-d mysql-ubuild-pbuild> e: /mysql. SQL

 

7. the query limits the use of the limit keyword in the returned result set (paging is possible), for example.

[html] select * from user_ order by user_name limit 10,10  [html] select * from user_ order by user_name limit 10  

 

Limit can be followed by two parameters or one parameter. Two parameters: the first parameter specifies the start position, and the second parameter specifies the number of items to be crawled. One parameter indicates that the number of specified items is 8 from the first parameter. view the SQL statement used to create a table

[html] show create table table_name  

 

9. Create a database (Create a database only if it does not exist and specify the database character encoding as utf8)

[html] create database if not exists db_name default character set utf8;  

 

10. Delete table data (retain table structure)

[html] truncate table_name  

 

11. Create a table (check whether the table exists before creation, and delete it if it exists)

[Html] Drop table if exists cooking_user_group; create table 'cooking _ user_group '('id' int (11) not null auto_increment, 'guid' varchar (255) not null unique, 'create _ time' datetime, 'archived' tinyint (1) default '0', 'name _ 'varchar (255), primary key ('id ')) ENGINE = InnoDB AUTO_INCREMENT = 20 default charset = utf8; cooking_user_group indicates the table name.

 

Id is the primary key and auto-increment. It is specified to start from 20 (Reserved id before 20). engine is InnonDB, and the default character encoding of the transaction table is utf8.