数据库脚本样例

1、登陆数据库

mysql -uroot -p'xxxxxx' --default-character-set=utf8;

2、导出数据库

mysqldump -uroot -proot fanfou > fanfou0816.sql

导出数据库中表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
　　mysqldump -u wcnc -p test_db users> test_users.sql

3、数据库表中插入新用户
insert into user (Host, User, Password) VALUES('%', 'skysip', PASSWORD('xxxxxx'));
GRANT ALL PRIVILEGES ON *.* TO 'skysip'@'%' IDENTIFIED BY '换成密码' WITH GRANT OPTION;
FLUSH PRIVILEGES 
4、丢掉外键
a、show create table ff_menu;
`stop_sale` int(1) NOT NULL DEFAULT '0',
`remove_flag` int(1) NOT NULL DEFAULT '0',
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ff_menu_fk_store_idx` (`store_id`),
  CONSTRAINT `ff_menu_fk_store` FOREIGN KEY (`store_id`) REFERENCES `ff_store` (                                                                                        `id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1  
b、ALTER TABLE ff_menu DROP FOREIGN KEY ff_menu_fk_store;
c、ALTER TABLE ff_menu DROP  KEY ff_menu_fk_store_idx;

5、修改字段类型

alter table ff_order change user_id user_id varchar(255) not null;

6、新增字段

ALTER TABLE `ff_menu` ADD `sold_out` INT(1) NOT NULL DEFAULT '0' AFTER `stop_sale`;

7、统计
select ip, ctime from tv_snlicense where ctime between '2016-07-29' and '2016-08-11' order by ctime  limit 1;
select  count(*) from snlicense where ctime between '2016-07-29' and '2016-08-11';
select ip, ctime from tv_snlicense where ctime between '2016-07-29' and '2016-08-11' order by ctime desc limit 1;
select left(ctime,10), count(ip) from snlicense where ctime between '2016-07-29' and '2016-08-11' group by left(ctime,'10');
select count(*) from qyq_stat where call_dur > 0 and (created_time  between '2016-08-21' and '2016-09-21');
select sum(call_dur) from qyq_stat where call_dur > 0 and (created_time  between '2016-08-21' and '2016-09-21');
select count(*) as 次数, left(created_time,'10') as 日期  from qyq_stat where call_dur > 0 and (created_time  between '2016-08-21' and '2016-09-21') group by left(created_time, '10');
select sum(call_dur) from qyq_stat where call_dur > 0 and camera_type=1 and (created_time  between '2016-08-21' and '2016-09-21');