尚硅谷mysql高级——周阳 _建表sql(book&class)

最新推荐文章于 2024-08-02 21:18:55 发布
Maynard-xu
最新推荐文章于 2024-08-02 21:18:55 发布
阅读量1.2k
点赞数 5
文章标签： sql 数据库
本文链接：https://blog.csdn.net/Pinkxu/article/details/105548507
版权
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
Maynard-xu
关注
5
点赞
踩
5

收藏

觉得还不错? 一键收藏
1
评论
尚硅谷mysql高级——周阳 _建表sql(book&class)

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 N...
复制链接

扫一扫