MySql常用命令

標籤：eve   _id   thread   hang   file   sql   creat   添加   read   

建立資料庫：create database database_name;刪除資料庫：drop database database_name;使用資料庫：use database_name;查看系統所支援的儲存引擎類型：show engines;修改表名：alter table offices rename offices_new;修改欄位的資料類型：alter table offices modify name varchar(30);修改欄位名：alter table offices change city city_new varchar(30);添加欄位：alter table offices add manager int(10);
刪除欄位：alter table offices drop manager;刪除表的外鍵約束：alter table offices drop foreign key office_fk;刪除表：（若表被其他表關聯，先刪除外鍵約束，再刪除表）drop table offices;刪除表中資料：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‘;

 大量刪除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;use zcy_develop;source ~/Documents/zcy_develop.sql;  mysqladmin -u root  password  "newpass" mysqladmin -u root  password  oldpass  "newpass"    show VARIABLES like ‘%max_allowed_packet%‘

所有大於16M的SQL檔案都會報ERROR 2006 (HY000) at line 17128: MySQL server has gone away，我們可以登入MySQL用戶端，修改系統變數：

set GLOBAL max_allowed_packet=500*1024*1024;

我們也可以通過修改MySQL配置my.cnf檔案，在最後一行增加max_allowed_packet=500M就可以了

MySQL設定檔的位置：

• Windows下 C:\ProgamData\MySQL\MySQL Server5.6
• Linux下 /etc/mysql
• Mac下通過brew安裝 /usr/local/Cellar/mysql/5.6.23

1、在mysql 資料庫中，“2009-09-15 00：00：00”轉化為列為長整型的函數：select * from tb where createAt < unix_timestamp("2013-03-15 00:00:00")*1000,2、在mysql資料庫中，“1252999488000”（java中的long型資料）轉化為日期：select  * from tb where createAt <  from_unixtime(1252999488);【注】：要將最後三位去掉。      

 

 

MySql常用命令