4.DQL

最新推荐文章于 2024-07-11 21:41:59 发布

「已注销」

最新推荐文章于 2024-07-11 21:41:59 发布

阅读量97

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_67192586/article/details/123998222

版权

MySQL 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

DQL和DML的补充

CREATE TABLE t_student(
	sno INT(6) PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(5) NOT NULL,
	sex CHAR(1) DEFAULT '男' CHECK (sex='男' || sex='女'),
	age INT(3) CHECK(age>=18 AND age<=50),
	enterdate DATE,
	classname VARCHAR(10),
	email VARCHAR(15) UNIQUE
);
INSERT INTO t_student VALUES (NULL,'张三','男',21,'2023-9-1','javaee','zs@126.com');
INSERT INTO t_student VALUES (NULL,'李四','男',21,'2023-9-1','javaee','ls@126.com');
INSERT INTO t_student VALUES (NULL,'露露','男',21,'2023-9-1','javaee','ll@126.com');

--  快速添加一张表1: 结构和数据跟t_student一致的
CREATE TABLE t_student2 AS SELECT * FROM t_student;
SELECT * FROM t_student2;


-- 快速添加,结构跟t_student一致,数据没有:
CREATE TABLE t_student3 AS SELECT * FROM t_student WHERE 1=2;
SELECT * FROM t_student3;

-- 快速添加,只要部分列,部分数据
CREATE TABLE t_student4 AS SELECT sno,sname,age FROM t_student WHERE sno =2;
SELECT * FROM t_student4;

-- 删除数据
DELETE FROM t_student;
TRUNCATE TABLE t_student; -- 清空数据,保留表结构,重新创建了这个表,所有的状态都相当于新表

DQL表的准备

-- 准备四张表:dept(部门表),emp(员工表),salgrade(薪资等级表),bonus(资金表)
-- 部门表 :dept :department loc : location 位置
CREATE TABLE dept(
	deptno INT(2) NOT NULL,
	dname VARCHAR(14),
	loc VARCHAR(13)
);
ALTER TABLE dept ADD CONSTRAINT pk_dept PRIMARY KEY (deptno);

-- 员工表 emp: employee员工 , mgr:manager上级领导 hiredate:入职日期 firedate:解雇日期, common 补助
-- deptno 外键 参考 dept - deptno字段
 -- mgr 外键 参考 自身表emp - empno 产生了自关联salgrade
CREATE TABLE emp(
	empno INT (4) PRIMARY KEY,
	ename VARCHAR(10),
	job INT (4),
	hiredate DATE,
	sal DOUBLE(7,2),
	comm DOUBLE(7,2),
	deptno INT (2)
);
ALTER TABLE emp ADD mgr INT(4) AFTER job;

ALTER TABLE emp ADD CONSTRAINT fk_deptno 
	FOREIGN KEY (deptno) REFERENCES dept(deptno);


-- losal - lowsal
-- hisal - highsal
CREATE TABLE salgrade(
	grade INT PRIMARY KEY,
	losal DOUBLE(7,2),
	hisal DOUBLE(7,7)
);

CREATE TABLE bonus(
	ename VARCHAR(10),
	job VARCHAR(9),
	sal DOUBLE(7,2),
	comm DOUBLE(7,2)
);

INSERT INTO dept(deptno,dname,loc) VALUES
(10,'ACCOUNTTING','NEW YORK'),
(20,'RESEARCH','DALLASK'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES
(7369,'SMITH','CLERK',7902,'2001-01-10',800,NULL,20),
(7499,'ALLEN','ALLEN',7968,'2001-01-10',800,NULL,20),
(7521,'WARD','WARD',7112,'2001-01-10',800,NULL,20),
(7566,'JONES','JONES',7902,'2001-01-10',800,NULL,20),
(7654,'MARTIN','MARTIN',7202,'2001-01-10',800,NULL,20),
(7698,'BLAKE','BLANK',7902,'2001-01-10',800,NULL,20),
(7782,'KING','CLARK',7902,'2001-01-10',800,NULL,20),
(7934,'JAMES','CLERK',7902,'2001-01-10',800,NULL,20),
(7900,'FORD','CLERK',7902,'2001-01-10',800,NULL,20),
(7160,'MILLER','CLERK',7902,'2001-01-10',800,NULL,20),
(7869,'SCOTT','CLERK',7902,'2001-01-10',800,NULL,20);

INSERT INTO salgrade (grade,losal,hisal) VALUES
(1,700,6666),
(2,1201,8880),
(3,1401,2222),
(4,2010,3689),
(5,3001,9999);