MySql常用命令

创建数据库：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';

 

批量删除table

Select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_name LIKE 'hotel_%';

 

mysql -P 3306 -h 127.0.0.1 -uroot -p123456

create 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);

【注】：要将最后三位去掉。