Java课程学习第40天--mysql,DML操作，库表操作，约束

千锋逆战班：孙华建
在千锋学习第40天
“未来的你会感谢今天奋斗的自己”
今天我学习了java课程，mysql,DML操作，库表操作，约束
#中国加油！武汉加油！千锋加油！也为自己加油！！！#…

USE companydb;
SELECT * FROM t_departments;

#表连接查询
#查询所有有部门的员工信息显示部门名称
#sql标准
SELECT employee_id,first_name,department_name FROM t_employees
INNER JOIN t_departments
ON t_employees.`department_id`= t_departments.`department_id`;
#mysql标准
SELECT employee_id,first_name,department_name 
FROM t_employees AS e,t_departments AS d
WHERE e.`DEPARTMENT_ID`= d.`DEPARTMENT_ID`; 

#多表连接查询
#查询所有员工工号，名字，部门名称，部门所在城市名称
SELECT employee_id,first_name,department_name,city
FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`
INNER JOIN t_locations AS l
ON d.`LOCATION_ID` = l.`LOCATION_ID`;

#查询所有员工工号，名字，部门名称，部门所在城市名称，城市的国家
SELECT employee_id,first_name,department_name,city
FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`
INNER JOIN t_locations AS l
ON d.`LOCATION_ID` = l.`LOCATION_ID`
INNER JOIN t_countries AS c
ON l.`COUNTRY_ID`= c.`COUNTRY_ID`;

#左外连接查询
#查询所有员工信息，对应的部门
SELECT employee_id,first_name,department_name
FROM t_employees AS e
LEFT JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`

#右外连接查询
#查询所有部门信息，以及部门中员工信息
SELECT employee_id,first_name,department_name
FROM t_employees AS e
RIGHT JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`

#DML,新增
SELECT * FROM t_countries
INSERT INTO t_countries(country_id,country_name)VALUES('AL','阿尔巴尼亚');
SELECT * FROM t_departments;
INSERT INTO t_departments(department_id,department_name,MANAGER_ID,LOCATION_ID)
VALUES('270','Teach','111','1500'),('291','study','121','1600');

#修改
UPDATE t1 SET id = '3',NAME = 'bb' WHERE id = 3;
SELECT * FROM t1;

UPDATE t_employees
SET first_name = 'TOM',last_name='JACKSON'
WHERE employee_id = '201';
SELECT * FROM t_employees;

#删除
DELETE FROM t1 ;

DELETE FROM t_employees
WHERE employee_id = '111'

#清空整张表
TRUNCATE TABLE t1;

#创建科目表
#科目编号，名称，学时
CREATE TABLE `Subject`(
	subjectId INT,
	subjectName VARCHAR(20),
	subjectHours INT
)CHARSET=utf8;
SELECT * FROM `subject`;
INSERT INTO `subject`(subjectId,subjectName,subjectHours)
VALUES(1,'java',10);
INSERT INTO `subject`(subjectId,subjectName,subjectHours)
VALUES(2,'HTML5',20);
INSERT INTO `subject`(subjectId,subjectName,subjectHours)
VALUES(3,'BIGDATA',5);

#考试表
#编号，科目ID，时间，分数
CREATE TABLE exam(
		id INT,
		subjectId INT,
		examDate DATE,
		examresult DOUBLE(4,1)
)CHARSET=utf8;
SELECT*FROM exam;
INSERT INTO exam(id,subjectid,examDate,examResult)
VALUES(1,1,'2020-3-17',99.5);

INSERT INTO exam(id,subjectid,examDate,examResult)
VALUES(2,1,'2020-3-17 13:35:26',77);

INSERT INTO exam(id,subjectid,examDate,examResult)
VALUES(3,1,CURDATE(),80);

INSERT INTO exam(id,subjectid,examDate,examResult)
VALUES(4,1,CURDATE(),100.13);

#实体完整性约束，主键约束  unique,自动增长
CREATE  TABLE Student(
	stuid INT PRIMARY KEY AUTO_INCREMENT,
	stuName VARCHAR(20),
	phone VARCHAR(11) UNIQUE
)CHARSET= utf8;
DROP TABLE student;
SELECT*FROM Student;
INSERT INTO Student (stuName,phone)VALUES('小明','11111111111');
INSERT INTO Student (stuName,phone)VALUES('小红','11111111112');
INSERT INTO Student (stuName,phone)VALUES('小华',NULL);
INSERT INTO Student (stuName,phone)VALUES('小网',NULL);

#域完整性约束
#加了NOT NULL的约束列，必须有值
#默认值约束
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	empName VARCHAR(20)NOT NULL,#约束名字这一列必须有值
	address VARCHAR(50) NOT NULL,
	sex CHAR(1) DEFAULT '女'
)CHARSET=utf8;
DROP TABLE emp;
SELECT*FROM emp;
INSERT INTO emp(empName,address) VALUES('小明','北京市海淀区');#error,课程名称必须有值
INSERT INTO emp(empName,address,sex) VALUES('小明','北京市海淀区',DEFAULT);

DROP TABLE SUBJECT;

#引用完整性约束
#专业表
CREATE TABLE speciality(
	id INT PRIMARY KEY AUTO_INCREMENT,
	SpecialName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;
#课程表
CREATE TABLE `subject`(
	subjectid INT PRIMARY KEY AUTO_INCREMENT,
	subjectname VARCHAR(20) UNIQUE NOT NULL,
	subjecthours INT DEFAULT 20,
	specialid INT NOT NULL,
	CONSTRAINT fk_subject_specialid
	FOREIGN KEY(specialid)
	REFERENCES Speciality(id)
)CHARSET=utf8;

SELECT*FROM `subject`;

INSERT INTO speciality (SpecialName) VALUES('Java');
INSERT INTO speciality (SpecialName) VALUES('HTML5');

INSERT INTO `subject`(subjectname,subjecthours,specialid) 
VALUES('JavaSE',10,1);
INSERT INTO `subject`(subjectname,subjecthours,specialid)
VALUES('JavaScript',20,2);

#先删除外键表，再删除主键表
DROP TABLE `subject`;
DROP TABLE speciality;