黑馬程序員 MySQL數(shù)據(jù)庫入門到精通，從mysql安裝到mysql高級(jí)、mys
外鍵約束
CREATE TABLE dept(
id int auto_increment COMMENT 'ID' PRIMARY KEY,
name VARCHAR(50) not null COMMENT '部門名稱'
)COMMENT '部門表';
INSERT INTO dept (id,name) VALUES (1,'研發(fā)部'),(2,'市場部'),(3,'財(cái)務(wù)部'),(4,'銷售部'),(5,'總經(jīng)辦');
CREATE TABLE eml (
??id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
??NAME VARCHAR(50) NOT NULL COMMENT '姓名',
??age INT COMMENT '年齡',
??job VARCHAR(20) COMMENT '職位',
??salary INT COMMENT '薪資',
entrydate date COMMENT '入職時(shí)間',
??managerid INT COMMENT '直屬領(lǐng)導(dǎo)ID',
??dept_id INT COMMENT '部門'
) COMMENT '員工表';
INSERT INTO eml (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES
(1, '柳巖', 66, '總裁', 20000, '2000-01-01', NULL, 5),
(2, '張無忌', 20, '項(xiàng)目經(jīng)理', 125000, '2005-09-01', 1, 1),
(3, '韋一笑', 33, '開發(fā)', 8400, '2005-08-01', 2, 1),
(4, '趙敏', 48, '開發(fā)', 11000, '2009-12-01', 2, 1),
(5, '小昭', 43, '開發(fā)', 6600, '2007-07-01', 3, 1),
(6, '奧特瑪', 43, '程序員鼓勵(lì)是', 6600, '2007-07-01', 2, 1);
# - 多對(duì)多
# **創(chuàng)建學(xué)生表**
CREATE TABLE student (
??id INT AUTO_INCREMENT PRIMARY KEY,
??name VARCHAR(10) COMMENT '姓名',
??no VARCHAR(10) COMMENT '學(xué)號(hào)'
) COMMENT '學(xué)生表';
INSERT INTO student VALUES?
(null, '維練', '2088100101'),
(null, '謝遜', '2080100102'),
(null, '傲天正', '200100103'),
(null, '偉一肖', '200100104');
# **創(chuàng)建課程表**
CREATE TABLE course (
??id INT AUTO_INCREMENT PRIMARY KEY,
??name VARCHAR(100) COMMENT '課程名稱'
) COMMENT '課程表';
INSERT INTO course VALUES?
(null, 'Java'),
(null, 'PHP'),
(null, 'MySQL'),
(null, 'Hadoop');
# **創(chuàng)建中間表**
CREATE TABLE student_course (
??id INT AUTO_INCREMENT COMMENT '主鍵',
??studentid INT NOT NULL COMMENT '學(xué)生ID',
??courseid INT NOT NULL COMMENT '課程ID',
??PRIMARY KEY (id),
??CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES course (id),
??CONSTRAINT fk_studentid FOREIGN KEY (studentid) REFERENCES student (id)
) COMMENT '學(xué)生課程中間表';
INSERT INTO student_course VALUES?
(null, 1, 1),
(null, 1, 2),
(null, 1, 3),
(null, 2, 2),
(null, 2, 3),
(null, 3, 4);
# - 一對(duì)一
# **創(chuàng)建用戶基本信息表**
CREATE TABLE tb_user (
??id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主鍵ID',
??name VARCHAR(10) COMMENT '姓名',
??age INT COMMENT '年齡',
??gender CHAR(1) COMMENT '1: 男，2: 女',
??phone CHAR(11) COMMENT '手機(jī)號(hào)'
) COMMENT '用戶基本信息表';
CREATE TABLE tb_user_edu(
??id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主鍵ID',
??degree VARCHAR(20) COMMENT '學(xué)歷',
??major VARCHAR(50) COMMENT '專業(yè)',
??primaryschool VARCHAR(50) COMMENT '小學(xué)',
??middleschool VARCHAR(50) COMMENT '中學(xué)',
??university VARCHAR(50) COMMENT '大學(xué)',
??userid INT UNIQUE COMMENT '用戶ID',
??CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES tb_user(id)
) COMMENT '用戶教育信息表';
# **插入數(shù)據(jù)**
INSERT INTO tb_user(id, name, age, gender, phone) VALUES
(null, '黃渤', 45, '1', '18800001111'),
(null, '冰冰', 352, '1', '18800002222'),
(null, '碼云', 55, '1', '18800008888'),
(null, '李彥宏', 50, '1', '18800009999');
INSERT INTO tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) VALUES
(null, '本科', '舞蹈', '靜安區(qū)第一小學(xué)', '靜安區(qū)第一中學(xué)', '北京舞蹈學(xué)院', 1),
(null, '碩士', '表演', '朝陽區(qū)第一小學(xué)', '朝陽區(qū)第一中學(xué)', '北京電影學(xué)院', 2),
(null, '本科', '英語', '杭州市第一小學(xué)', '杭州市第一中學(xué)', '杭州師范大學(xué)', 3),
(null, '本科', '應(yīng)用數(shù)學(xué)', '陽泉第一小學(xué)', '陽泉區(qū)第一中學(xué)', '清華大學(xué)', 4);
