第二章课堂笔记

数据库

– 建立一个数据库
CREATE TABLE classes
(
class_id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20)
)ENGINE=InnoDB;

– 建立一个student表
CREATE TABLE student
(
id INT PRIMARY KEY,
name VARCHAR(20),
sex VARCHAR(5),
class_id VARCHAR(20),
CONSTRAINT fk_student_class FOREIGN KEY(class_id) REFERENCES classes(class_id)
)ENGINE=InnoDB;

– 修改表名
ALTER TABLE student RENAME TO tb_student;

– 修改数据表的字段名
ALTER TABLE tb_student CHANGE name st_name VARCHAR(25);

– 查看表结构
DESC tb_student;

– 单字段主键约束
CREATE TABLE emp
(
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT,
salary FLOAT
);

– 多字段联合主键约束
CREATE TABLE emp_2
(
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT,
salary FLOAT,
PRIMARY KEY(id,name)
);

– 外键约束
CREATE TABLE dept
(
id INT PRIMARY KEY,
name VARCHAR(20),
location VARCHAR(50)
)ENGINE=InnoDB;

CREATE TABLE emp_3
(
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT,
salary FLOAT,
CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
)ENGINE=InnoDB;

– 非空约束
CREATE TABLE emp_4
(
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
dept_id INT,
salary FLOAT
);

– 唯一性约束
CREATE TABLE emp_5
(
id INT PRIMARY KEY,
name VARCHAR(20) UNIQUE,
dept_id INT,
salary FLOAT
);

– 默认约束
CREATE TABLE emp_6
(
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT,
salary FLOAT DEFAULT 2000
);

– 属性值自动增长约束
CREATE TABLE emp_7
(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
dept_id INT,
salary FLOAT
);