数据库脚本样例

最新推荐文章于 2024-05-15 16:51:40 发布

原创最新推荐文章于 2024-05-15 16:51:40 发布 · 423 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

3 篇文章

订阅专栏

本文介绍MySQL数据库的基本操作，包括登录数据库、导出数据库、插入新用户、丢弃外键、修改字段类型、新增字段及统计数据等实用技巧。

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