MySQL common commands and records

MySQL version: Ver 14.14 distrib 5.7.17, for macos10.12 (x86_64) using Editline Wrapper
Documents: http://www.cnblogs.com/mr-wid/archive/2013/05/09/3068229.html, http://www.runoob.com/mysql/mysql-index.html

Under Mac:
Login: Mysql-u root-p
See which databases are available: show databases;
Select database: Use database name, such as "Use zml_db;"
Create a database mysqldata:mysql> create databases Mysqldata;
Delete a database: Drop databases < database name >
What tables are queried: show tables;
Creating a table: Create TABLE MYTABLE (name VARCHAR), sex CHAR (1));
Delete a table: drop table MYTABLE;
Empty a table: Delete from < table name >;truncate tables < table name >; The difference, please see http://blog.csdn.net/longshenlmj/article/details/18309529
Display the structure of a table:mysql> describe < table name >;
Added field in table: ALTER TABLE name Add (COLUMN) field name type other; For example "ALTER TABLE mytable add age int default ' 0 ';"
Delete field in table: ALTER TABLE Name drop (column) field name; ALTER TABLE mytable drop COLUMN age;
Modify fields (name, type, and so on) in the table:
ALTER TABLE user MODIFY New1 VARCHAR (10); Modify the type of a field
ALTER TABLE user change new1 new4 int; To modify the name of a field, be sure to re-specify the field's type
Change table name: Rename table name to new table name;
Basic table data additions and deletions to check the statement:
Insert: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)]
Delete: Delete from table name where expression
Query: SELECT * FROM < table name > where ....
Updated: Update < table name > set sex= "F" where name= ' HyQ ';

Paged query: Query the first few rows of data. For example: View the first 2 rows of data in table MyClass mysql> select * FROM MyClass ORDER by ID limit 0, 2;
Creating index: CREATE INDEX INDEX_NAME on table_name (COLUMN_NAME);//There are other ways to create the focus, see http://wiki.jikexueyuan.com/project/sql/ Indexes.html
--Note: There is no need to add the index when the data volume is small, but it will reduce the efficiency of the modified deletion.
Three common MySQL build statements: http://database.51cto.com/art/201011/234350.htm

Convenient operation:
Load data into a database table in text mode (for example, d:/mysql.txt):mysql> LOAD data LOCAL INFILE "d:/mysql.txt" into table MYTABLE;

Loop through the data, using stored procedures, such as:

DROP PROCEDURE if existsMyProc;d Elimiter//Create procedureMyProc ()begin DeclareNumint; SetNum=1;  whileNum< 100000 DoInsert  intoMyTable (Name,sex,age)Values(CONCAT ("Zhang", num), "male", num);SetNum=Num+1;End  while;End//Call MyProc ();

Randomly query one of the data in a table: http://blog.csdn.net/mengxiangone/article/details/8093347, the final choice is "select * from the Users WHERE userId >= ( (select MAX (userid) from the Users)-(select min (userid) from users) * RAND () + (select min (userid) from users) LIMIT 1 "

Several principles of index building: https://tech.meituan.com/mysql-index.html

Experience Cases:
Optimization of fuzzy query three ways: http://blog.csdn.net/createment/article/details/50602529

MySQL common commands and records