MySQL Common commands

CREATE database database_name; Delete databases: DROP database database_name; using database: use database_name; View the types of storage engines supported by the system: Show Engines: ALTER TABLE offices rename Offices_new; Modify the data type of the field: ALTER TABLE offices modify name varchar (30); Modify field name: Alter Table Offices Change City city_new varchar (30); Add field: ALTER TABLE offices add manager int (10);
Delete field: ALTER TABLE offices drop manager; Delete table's FOREIGN KEY constraint: ALTER TABLE offices drop foreign key office_fk; Delete Table: (If the table is associated with another table, first delete the foreign KEY constraint, Delete tables) drop table offices; Delete data in table: delete from offices;

Select h.id,h.hotel_id,hp.id,hp.hotel_id from Hotel_supporting_service as H left join Hotel_supporting_ Service_protocol as hp on hp.src_id = h.id where h.id>6;

Show CREATE PROCEDURE sys.ps_setup_enable_background_threads;
Show procedure status like ' Ps_setup_enable_background_threads ';
SELECT * from INFORMATION_SCHEMA. ROUTINES WHERE routine_name= ' ps_setup_enable_background_threads ';

Show triggers;

# #select_priv, Create_view_priv
SELECT * from Mysql.user WHERE user= ' root ';
SELECT * FROM INFORMATION_SCHEMA. views;
SELECT * FROM INFORMATION_SCHEMA. TRIGGERS;
SELECT @ @AUTOCOMMIT;
SELECT @ @tx_isolation;

SELECT * from zcy_develop.vanyar_users where account= ' 339900111111 ';
SELECT * from zcy_develop.vanyar_orgins_category where org_id = 100013315;
SELECT * from zcy_develop.vanyar_employee where user_id = 100018406;
SELECT * from Zcy_develop.vanyar_user_extra where user_id = 100018406;

Delete from Oauth_code;
Delete from Oauth_access_token;
Select Password ("**jjhh");
Analyze table Zcy_develop.vanyar_user_extra;
SELECT * from Mysql.user;
SHOW VARIABLES like ' slow_query_log_file ';
SHOW STATUS like ' slow_queries ';

Bulk Delete tableselect CONCAT (' drop table ', table_name, '; ') From Information_schema.tables Where table_name like ' hotel_% '; Mysql-p 3306-h 127.0.0.1-uroot-p123456create Database zcy_develop default character set UTF8 collate utf8_general_ci;u Se zcy_develop;source ~/documents/zcy_develop.sql; mysqladmin -u root  password "newpass" mysqladmin -u root  password oldpass  "newpass"    Show VARIABLES like '%max_allowed_packet% '

All SQL files larger than 16M will be reported as Error 2006 (HY000) at line 17128:mysql server have gone away, we can log in to the MySQL client and modify the system variables:

set GLOBAL max_allowed_packet=500*1024*1024;

We can also modify the MySQL configuration my.cnf file by adding it in the last line max_allowed_packet=500M

Location of MySQL configuration file:

• Windows underC:\ProgamData\MySQL\MySQL Server5.6
• Linux under/etc/mysql
• Brew installation under Mac/usr/local/Cellar/mysql/5.6.23

1. In MySQL database, "2009-09-15 00:00:00" is converted to a function listed as Long integer: SELECT * from TB where Createat < Unix_timestamp ("2013-03-15 00:00:00" *1000,2, in MySQL database, "1252999488000" (Long data in Java) is converted to date: SELECT * from TB where Createat < From_unixtime (1252999488 ); "Note": To remove the last three bits.

MySQL Common commands