MySQL 库 和 样例表 创建脚本

1.创建库

CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

1).CHARACTER SET 指定字符集

2).COLLATE 排序方式，可以不指定，默认会根据字符集变化而变化

3).创建数据库时不能指定默认存储引擎

2.创建小表

CREATE TABLE emp
(empno smallint(4) not null primary key,
 ename varchar(10),
 job varchar(9),
 mgr smallint(4),
 hiredate date,
 sal float(7, 2),
 comm float(7, 2),
 deptno tinyint(2)
) engine=innodb charset=utf8;

INSERT INTO emp VALUES (7369, '员工SMITH',  'CLERK',     7902, '1980-12-17',  800, NULL, 20);
INSERT INTO emp VALUES (7499, '员工ALLEN',  'SALESMAN',  7698, '1981-02-20', 1600,  300, 30);
INSERT INTO emp VALUES (7521, '员工WARD',   'SALESMAN',  7698, '1981-02-22', 1250,  500, 30);
INSERT INTO emp VALUES (7566, '员工JONES',  'MANAGER',   7839, '1981-04-02',  2975, NULL, 20);
INSERT INTO emp VALUES (7654, '员工MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES (7698, '员工BLAKE',  'MANAGER',   7839, '1981-05-01',  2850, NULL, 30);
INSERT INTO emp VALUES (7782, '员工CLARK',  'MANAGER',   7839, '1981-06-09',  2450, NULL, 10);
INSERT INTO emp VALUES (7788, '员工SCOTT',  'ANALYST',   7566, '1982-10-09', 3000, NULL, 20);
INSERT INTO emp VALUES (7839, '员工KING',   'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp VALUES (7844, '员工TURNER', 'SALESMAN',  7698, '1981-09-08',  1500,    0, 30);
INSERT INTO emp VALUES (7876, '员工ADAMS',  'CLERK',     7788, '1983-01-12', 1100, NULL, 20);
INSERT INTO emp VALUES (7900, '员工JAMES',  'CLERK',     7698, '1981-12-03',   950, NULL, 30);
INSERT INTO emp VALUES (7902, '员工FORD',   'ANALYST',   7566, '1981-12-03',  3000, NULL, 20);
INSERT INTO emp VALUES (7934, '员工MILLER', 'CLERK',     7782, '1982-01-23', 1300, NULL, 10);

create table dept
(deptno tinyint(2) primary key,
 dname varchar(14),
 loc varchar(13)
) engine=innodb charset=utf8;

INSERT INTO dept VALUES (10, '部门ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, '部门RESEARCH',   'DALLAS');
INSERT INTO dept VALUES (30, '部门SALES',      'CHICAGO');
INSERT INTO dept VALUES (40, '部门OPERATIONS', 'BOSTON ');

commit;

3.创建大表

create table bt as select * from emp where 1=0;


mysqlslap --concurrency=50 --iterations=10 --create-schema='test' --query="insert into bt select * from emp" --number-of-queries=100000 --debug-info -uroot -proot


上面mysqlslap命令在shell执行。上面是mysql的压力命令，这里用来生产大表数据，并发产生50个线程，每个循环执行10次，每次执行100000次--query内容。

4.创建分区表

分区表有三个类型：范围 hash 列表

CREATE TABLE p_range (
id int(10) NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (100) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (200) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = MyISAM
);
mysqlslap --concurrency=1 --iterations=1 --create-schema='test' --query="insert into p_range select null, ename from emp" --number-of-queries=50 --debug-info -uroot -proot


CREATE TABLE p_list (
id int(10) NOT NULL AUTO_INCREMENT,
name char(20) DEFAULT NULL,
type mediumint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (id,type)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
PARTITION BY LIST (type)
(PARTITION p0 VALUES IN (1,2,3,4) ENGINE = InnoDB ,
PARTITION p1 VALUES IN (5,6,7,8) ENGINE = InnoDB ) ;

mysqlslap --concurrency=1 --iterations=1 --create-schema='test' --query="insert into p_list select null, ename, floor(1+rand()*8) from emp" --number-of-queries=50 --debug-info -uroot -proot

CREATE TABLE p_hash (
id int(10) NOT NULL AUTO_INCREMENT,
name char(255) DEFAULT NULL,
type mediumint(10) NOT NULL DEFAULT '0',
PRIMARY KEY (id,type)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY HASH (type)
PARTITIONS 4;

mysqlslap --concurrency=1 --iterations=1 --create-schema='test' --query="insert into p_hash select null, ename, floor(1+rand()*8) from emp" --number-of-queries=50 --debug-info -uroot -proot