约束
内容
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'employees';
CREATE DATABASE dbtest13;
USE dbtest13;
CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);
DESC test1;
INSERT INTO test1(id,last_name,email,salary)
VALUES (1,'Tom','tom@126.com',3400);
INSERT INTO test1(id,last_name,email,salary)
VALUES (2,NULL,'tom1@126.com',3400);
INSERT INTO test1(id,last_name,email,salary)
VALUES (NULL,'Jerry','jerry@126.com',3400);
INSERT INTO test1(id,email)
VALUES(2,'abc@126.com');
UPDATE test1
SET last_name = NULL
WHERE id = 1;
SELECT * FROM test1;
DESC test1;
ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;
ALTER TABLE test1
MODIFY email VARCHAR(25) NULL;
CREATE TABLE test2(
id INT UNIQUE,
last_name VARCHAR(15),
email VARCHAR(25) ,
salary DECIMAL(10,2),
CONSTRAINT uk_test2_email UNIQUE (email)
);
DESC test2;
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'test2';
INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',4500);
INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom1','tom1@126.com',4600);
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1','tom@126.com',4600);
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1',NULL,4600);
INSERT INTO test2(id,last_name,email,salary)
VALUES(3,'Tom2',NULL,4600);
SELECT * FROM test2;
DESC test2;
UPDATE test2
SET salary = 5000
WHERE id = 3;
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;
CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)
);
INSERT INTO USER
VALUES(1,'Tom','abc');
INSERT INTO USER
VALUES(1,'Tom1','abc');
SELECT * FROM USER;
CREATE TABLE student(
sid INT,
sname VARCHAR(20),
tel CHAR(11) UNIQUE KEY,
cardid CHAR(18) UNIQUE KEY
);
CREATE TABLE course(
cid INT,
cname VARCHAR(20)
);
CREATE TABLE student_course(
id INT,
sid INT,
cid INT,
score INT,
UNIQUE KEY(sid,cid)
);
INSERT INTO student VALUES(1,'张三','13710011002','101223199012015623');
INSERT INTO student VALUES(2,'李四','13710011003','101223199012015624');
INSERT INTO course VALUES(1001,'Java'),(1002,'MySQL');
SELECT * FROM student;
SELECT * FROM course;
INSERT INTO student_course VALUES
(1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);
SELECT * FROM student_course;
INSERT INTO student_course
VALUES (5,2,1002,67);
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'student_course';
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'test2';
DESC test2;
ALTER TABLE test2
DROP INDEX last_name;
ALTER TABLE test2
DROP INDEX uk_test2_sal;
CREATE TABLE test3(
id INT PRIMARY KEY,
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10,2),
email VARCHAR(25)
);
CREATE TABLE test4(
id INT PRIMARY KEY,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
CREATE TABLE test5(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
CONSTRAINT pk_test5_id PRIMARY KEY(id)
);
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'test5';
INSERT INTO test4(id,last_name,salary,email)
VALUES (1,'Tom',4500,'tom@126.com');
INSERT INTO test4(id,last_name,salary,email)
VALUES (1,'Tom',4500,'tom@126.com');
INSERT INTO test4(id,last_name,salary,email)
VALUES (NULL,'Tom',4500,'tom@126.com');
SELECT *
FROM test4;
CREATE TABLE user1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),
PRIMARY KEY(NAME,PASSWORD)
);
INSERT INTO user1
VALUES (1,'Tom','abc');
INSERT INTO user1
VALUES (1,'Tom1','abc');
INSERT INTO user1
VALUES (1,NULL,'abc');
SELECT * FROM user1;
CREATE TABLE test6(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
DESC test6;
ALTER TABLE test6
ADD PRIMARY KEY (id);
ALTER TABLE test6
DROP PRIMARY KEY;
CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
INSERT INTO test7(last_name)
VALUES ('Tom');
SELECT * FROM test7;
INSERT INTO test7(id,last_name)
VALUES (0,'Tom');
INSERT INTO test7(id,last_name)
VALUES (NULL,'Tom');
INSERT INTO test7(id,last_name)
VALUES (-10,'Tom');
INSERT INTO test7(id,last_name)
VALUES (10,'Tom');
CREATE TABLE test8(
id INT PRIMARY KEY ,
last_name VARCHAR(15)
);
DESC test8;
ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;
ALTER TABLE test8
MODIFY id INT;
CREATE TABLE test9(
id INT PRIMARY KEY AUTO_INCREMENT
);
INSERT INTO test9
VALUES (0),(0),(0),(0);
SELECT * FROM test9;
DELETE FROM test9
WHERE id = 4;
INSERT INTO test9
VALUES (0);
DELETE FROM test9
WHERE id = 5;
SELECT * FROM test9;
INSERT INTO test9
VALUES (0);
CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCE dept1(dept_id)
);
ALTER TABLE dept1
ADD PRIMARY KEY(dept_id);
DESC dept1;
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
);
DESC emp1;
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'emp1';
INSERT INTO emp1
VALUES (1001,'Tom',10);
INSERT INTO dept1
VALUES (10,'IT');
INSERT INTO emp1
VALUES (1001,'Tom',10);
DELETE FROM dept1
WHERE dept_id = 10;
UPDATE dept1
SET dept_id = 20
WHERE dept_id = 10;
CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);
CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT
);
ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY (department_id) REFERENCES dept2(dept_id);
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'emp2';
USE atguigudb;
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'employees';
USE dbtest13;
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'emp1';
ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;
SHOW INDEX FROM emp1;
ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;
CREATE TABLE test10(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK (salary > 2000)
);
INSERT INTO test10
VALUES (1,'Tom',2500);
INSERT INTO test10
VALUES (2,'Tom1',1500);
CREATE TABLE test11(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) DEFAULT 2000
);
DESC test11;
INSERT INTO test11(id,last_name,salary)
VALUES (1,'Tom',3000);
INSERT INTO test11(id,last_name)
VALUES (2,'Tom1')
SELECT * FROM test11;
CREATE TABLE test12(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2)
);
DESC test12;
ALTER TABLE test12
MODIFY salary DECIMAL(8,2) DEFAULT 2500;
ALTER TABLE test12
MODIFY salary DECIMAL(8,2);
SHOW CREATE TABLE test12;
练习
CREATE DATABASE test04_emp;
USE test04_emp;
CREATE TABLE emp2(
id INT,
emp_name VARCHAR(15)
);
CREATE TABLE dept2(
id INT,
dept_name VARCHAR(15)
);
ALTER TABLE emp2
MODIFY id INT PRIMARY KEY;
ALTER TABLE dept2
ADD PRIMARY KEY(id);
ALTER TABLE emp2
ADD dept_id INT ;
DESC emp2;
ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_deptid FOREIGN KEY (dept_id) REFERENCES dept2(id);
USE test01_library;
DESC books;
CREATE TABLE books(
id INT,
`name` VARCHAR(50),
`authors` VARCHAR(100),
price FLOAT,
pubdate YEAR,
note, VARCHAR(100),
num INT
);
ALTER TABLE books
ADD PRIMARY KEY(id);
ALTER TABLE books
MODIFY id INT AUTO_INCREMENT;
ALTER TABLE books
MODIFY id INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE books MODIFY `name` VARCHAR(50) NOT NULL;
ALTER TABLE books MODIFY `authors` VARCHAR(100) NOT NULL;
ALTER TABLE books MODIFY price FLOAT NOT NULL;
ALTER TABLE books MODIFY pubdate YEAR NOT NULL;
ALTER TABLE books MODIFY num INT(11) NOT NULL;
DESC books;
CREATE DATABASE test04_company;
USE test04_company;
CREATE TABLE offices(
officeCode INT(10) PRIMARY KEY,
city VARCHAR(50) NOT NULL,
address VARCHAR(50),
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15),
CONSTRAINT uk_off_postcode UNIQUE(postalCode)
);
DESC offices;
CREATE TABLE employees(
employeeNumber INT(11) PRIMARY KEY AUTO_INCREMENT,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
mobile VARCHAR(25) UNIQUE,
officeCode INT(10) NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth DATETIME NOT NULL,
note VARCHAR(255),
sex VARCHAR(5),
CONSTRAINT fk_emp_offCode FOREIGN KEY (officeCode) REFERENCES offices(officeCode)
);
DESC employees;
ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER officeCode;
ALTER TABLE employees
CHANGE birth employee_birth DATETIME;
ALTER TABLE employees
MODIFY sex CHAR(1) NOT NULL;
ALTER TABLE employees
DROP COLUMN note;
DROP COLUMN note
FROM TABLE employees;
ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);
RENAME TABLE employees
TO employees_info;
DESC employees_info;
视图
内容
CREATE DATABASE dbtest14;
USE dbtest14;
CREATE TABLE emps
AS
SELECT *
FROM atguigudb.employees;
CREATE TABLE depts
AS
SELECT *
FROM atguigudb.departments;
SELECT * FROM emps;
SELECT * FROM depts;
DESC emps;
DESC atguigudb.employees;
CREATE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary
FROM emps;
SELECT * FROM vu_emp1;
CREATE VIEW vu_emp2
AS
SELECT employee_id emp_id,last_name lname,salary
FROM emps
WHERE salary > 8000;
SELECT * FROM vu_emp2;
CREATE VIEW vu_emp3(emp_id,NAME,monthly_sal)
AS
SELECT employee_id,last_name ,salary
FROM emps
WHERE salary > 8000;
SELECT * FROM vu_emp3;
CREATE VIEW vu_emp_sal
AS
SELECT department_id,AVG(salary) avg_sal
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;
SELECT * FROM vu_emp_sal;
CREATE VIEW vu_emp_dept
AS
SELECT e.employee_id,e.department_id,d.department_name
FROM emps e JOIN depts d
ON e.department_id = d.department_id
SELECT * FROM vu_emp_dept;
CREATE VIEW vu_emp_dept1
AS
SELECT CONCAT(e.last_name,'(',d.department_name,')') emp_info
FROM emps e JOIN depts d
ON e.department_id = d.department_id
SELECT * FROM vu_emp_dept1;
CREATE VIEW ev_emp4
AS
SELECT employee_id,last_name
FROM vu_emp1;
SELECT * FROM ev_emp4;
SHOW TABLES;
DESC vu_emp1;
SHOW TABLE STATUS LIKE 'vu_emp1';
SHOW CREATE VIEW vu_emp1;
SELECT * FROM vu_emp1;
SELECT employee_id,last_name,salary
FROM emps;
UPDATE vu_emp1
SET salary = 20000
WHERE employee_id = 101;
UPDATE emps
SET salary = 10000
WHERE employee_id = 101;
DELETE FROM vu_emp1
WHERE employee_id = 101;
SELECT * FROM vu_emp_sal;
UPDATE vu_emp_sal
SET avg_sal = 5000
WHERE department_id = 30;
DELETE FROM vu_emp_sal
WHERE department_id = 30;
DESC vu_emp1;
CREATE OR REPLACE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,email
FROM emps
WHERE salary > 7000;
ALTER VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,email,hire_date
FROM emps;
SHOW TABLES;
DROP VIEW ev_emp4;
DROP VIEW IF EXISTS vu_emp2,vu_emp3;
练习
USE dbtest14;
CREATE OR REPLACE VIEW employee_vu(lname,emp_id,dept_id)
AS
SELECT last_name,employee_id,department_id
FROM emps;
DESC employee_vu;
SELECT * FROM employee_vu;
ALTER VIEW employee_vu(lname,emp_id,dept_id)
AS
SELECT last_name,employee_id,department_id
FROM emps
WHERE department_id = 80;
CREATE OR REPLACE VIEW employee_vu
AS
SELECT last_name,employee_id,department_id
FROM emps
WHERE department_id = 80;
SELECT * FROM emps;
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM emps
WHERE phone_number LIKE '011%';
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,email,phone_number,salary
FROM emps
WHERE phone_number LIKE '011%'
AND email LIKE '%e%';
SELECT * FROM emp_v1;
DESC emps;
INSERT INTO emp_v1
VALUES ('Tom','tom@126.com','01012345')
SELECT * FROM emp_v1;
UPDATE emp_v1
SET salary = salary + 1000;
DELETE FROM emp_v1
WHERE last_name = 'Olsen';
CREATE OR REPLACE VIEW emp_v2(dept_id,max_sal)
AS
SELECT department_id,MAX(salary)
FROM emps
GROUP BY department_id
HAVING MAX(salary) > 12000;
SELECT * FROM emp_v2;
INSERT INTO emp_v2(dept_id,max_sal)
VALUES (4000,20000)
DROP VIEW IF EXISTS emp_v2,emp_v1;
SHOW TABLES;