首页 > 代码库 > MySql常用命令

MySql常用命令

2024-08-23 09:04:49   220人阅读

创建数据库：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);
【注】：要将最后三位去掉。
 
 
 
 
 
 

 

 

MySql常用命令


联系
我们