CREATE TABLE IF NOT EXISTS book(
bookid INT(10) UNSIGNED NOT NULL auto_increment,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(bookid)
)
CREATE TABLE IF NOT EXISTS class(
id INT(10) UNSIGNED NOT NULL auto_increment,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(id)
)
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO class(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO book(card) VALUES(FLOOR(1 + RAND()*20));
SELECT * from class;
SELECT * from book;
#----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS phone(
phoneid INT(10) UNSIGNED NOT NULL auto_increment,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(phoneid)
)ENGINE = INNODB;
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
INSERT INTO phone(card) VALUES(FLOOR(1 + RAND()*20));
#----------------------------------------------------------------
create table staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)CHARSET utf8 COMMENT '员工记录表';
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('Z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
select * from staffs;
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name,age,pos);
#----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tbl_user(
id INT(11) NOT NULL auto_increment,
name VARCHAR(20) DEFAULT NULL,
age INT(11) DEFAULT NULL,
email VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE = INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;
INSERT INTO tbl_user(name,age,email) VALUES('1aa1', 21, 'b@163.com');
INSERT INTO tbl_user(name,age,email) VALUES('2aa2', 222, 'a@163.com');
INSERT INTO tbl_user(name,age,email) VALUES('3aa3', 265, 'c@163.com');
INSERT INTO tbl_user(name,age,email) VALUES('4aa4', 21, 'd@163.com');
#----------------------------------------------------------------
CREATE TABLE test03(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
INSERT INTO test03(c1, c2, c3, c4, c5) VALUES('a1','a2','a3','a4','a5');
INSERT INTO test03(c1, c2, c3, c4, c5) VALUES('b1','b2','b3','b4','b5');
INSERT INTO test03(c1, c2, c3, c4, c5) VALUES('c1','c2','c3','c4','c5');
INSERT INTO test03(c1, c2, c3, c4, c5) VALUES('d1','d2','d3','d4','d5');
INSERT INTO test03(c1, c2, c3, c4, c5) VALUES('e1','e2','e3','e4','e5');
#----------------------------------------------------------------
CREATE TABLE tblA(
age INT,
birth TIMESTAMP NOT NULL
);
INSERT INTO tblA(age, birth) VALUES(22, NOW());
INSERT INTO tblA(age, birth) VALUES(23, NOW());
INSERT INTO tblA(age, birth) VALUES(24, NOW());
#----------------------------------------------------------------
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT '',
loc VARCHAR(13) NOT NULL DEFAULT ''
)ENGINE=INNODB DEFAULT CHARSET=GBK;
CREATE TABLE emp(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #编号
enname VARCHAR(20) NOT NULL DEFAULT '', #名字
job VARCHAR(9) NOT NULL DEFAULT '', #工作
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #上级编号
hiredate DATE NOT NULL, #入职时间
sal DECIMAL(7,2) NOT NULL, #薪水
comm DECIMAL(7,2) NOT NULL, #红利
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 #部门编号
)ENGINE=INNODB DEFAULT CHARSET=GBK;
//定义函数1
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_set VARCHAR(100) DEFAULT 'abcdefghigklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_set,FLOOR(1 + RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
//定义函数2
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100 + RAND()*10);
RETURN i;
END $$
//定义存储过程1
DELIMITER $$
CREATE PROCEDURE insert_emp(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp(empno, enname, job, mgr, hiredate, sal, comm, deptno) VALUES((start + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
//定义存储过程2
DELIMITER $$
CREATE PROCEDURE insert_dept(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept(deptno,dname,loc) VALUES((start + i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
//开始插入数据
DELIMITER ;
call insert_dept(100,10);
call insert_emp(100001,500000);
drop PROCEDURE insert_emp
drop PROCEDURE insert_dept