MySQL数据库-2
MySQL数据库-2
第09章子查询
#第09章子查询
SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#2.单行子查询
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id)=(
SELECT manager_id,department_id
FROM employees
WHERE employee_id=141
)
AND employee_id<>141;
#3.多行子查询
#题目:返回其它job_id中比job_id为'IT_PROG'部门所有工资低的员工的员工号、
#姓名、job id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary< ALL(
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
);
#题目:查询平均工资最低的部门id
#MySQL中聚合函数是不能嵌套使用的。
#方式1
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(avg_sal)
FROM(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) t_dept
);
#方式2
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)
6．相关子查询
#题目:查询员工中工资大于本部门平均工资的员工的last name,salary和其department id
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary>(
SELECT AVG(salary)
FROM employees e2
WHERE department_id=e1.department_id
);
#from型的子查询:子查询是作为from的一部分，子查询要用(引起来，并且要给这个子查询取别名,把它当成一张“临时的虚拟的表”来使用。
#结论:在sELECT中，除了GROUP BY 和LIMIT之外，其他位置都可以声明子查询!
子查询的编写技巧(或步骤）:
从里往外写o从外往里写
如何选择?
- 如果子查询相对较简单，建议从外往里写。一旦子查询结构较复杂，则建议从里 往外写
- 如果是相关子查询的话，通常都是从外往里写。
第10章_创建和管理表
#第10章_创建和管理表
#l.创建和管理数据库
CREATE DATABASE IF NOT EXISTS mytest1 CHARACTER SET 'utf8';
SHOW DATABASES;
#切换数据库
USE atguigudb;
#查看当前数据库中保存的数据表
SHOW TABLES
#查看当前使用的数据库
SELECT DATABASE()
#查看指定数据库下保存的数据表
SHOW TABLES FROM mysql;
#1.4册删除数据库
DROP DATABASE IF EXISTS mytest1;
#2.如何创建数据表
USE mytest1
CREATE TABLE IF NOT EXISTS myempl(#需要用户具备创建表的权限。
id INT,
emp_name VARCHAR(15),#使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。
hire_date DATE
);
SELECT *
FROM myempl
#查看表结构
DESC myempl
#方式2:基于现有的表,同时导入数据
CREATE TABLE myemp2
AS
SELECT employee_id xxid, last_name xxName, salary
FROM employees;
SELECT *
FROM myemp2
#3.修改表
#3.1 添加一个字段
ALTER TABLE myempl
ADD salary DOUBLE(10,2);#默认添加到表中的最后一个字段的位置
DESC myempl
#3.2修改一个字段:数据类型、长度、默认值(略)
ALTER TABLE myempl
MODIFY emp_name VARCHAR(15) DEFAULT 'aaa';
#3.3重命名一个字段
ALTER TABLE myempl
CHANGE salary monthly_salary DOUBLE ( 10,2);
#3.4册除一个字段
ALTER TABLE myempl
DROP COLUMN monthly_salary;
#4.重命名表
RENAME TABLE myempl
TO myempX
#5.册除表
#不光将表结构删除掉，同时表中的数据也删除掉，释放表空间
DROP TABLE IF EXISTS myempl2;
#6．清空表
#清空表，表示清空表中的所有数据，但是表结构保留。
TRUNCATE TABLE myempl2
#7.DCL中COMMIT 和ROLLBACK
# CONMIT:提交数据。一旦执行cONMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
# ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次coNMIT之后。
#8．对比TRUNCATE TABLE 和DELETEFROM
#相同点:都可以实现对表中所有数据的删除，同时保留表结构。#不同点:
#TRUNCATE TABLE:一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的。
#DELETE FROM:一旦执行此操作，表数据可以全部清除（不带wHBRE)。同时，数据是可以实现回滚
/*9. DDL和DML的说明
0DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。自动提交，不受操作影响
DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DM之前，执行了
SET autocommit = FALSE，则执行的DML操作就可以实现回滚。
*/
#演示:DELETE FROM
COMMIT;
SELECT *
FROM myemp2;
SET autocommit=FALSE;
DELETE FROM myemp2;
ROLLBACK;
#9.测试MysQL8.0的新特性:DDL的原子化
第11章_数据处理之增删改
#第11章_数据处理之增删改
#0.创建表
CREATE TABLE IF NOT EXISTS emp1(
id INT,
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);
DESC emp1;
SELECT *
FROM emp1;
#l.添加数据
#方式l:一条一条的添加数据
#o没有指明添加的字段
INSERT INTO emp1
VALUES (1,'tom','2000-12-21',3400);#注意:一定要按照声明的字段的先后顺序添加
#o指明要添加的字段(推荐)
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'1999-09-9',4000,'Jerry');
#说明:没有进行赋值的hire_date 的值为null
#o同时插入多条记录
INSERT INTO emp1 (id,`name`,salary)
VALUES
(3,'张俊杰',5500) ,
(4, 'Jim' ,5000) ;
#方式2:将查询结果插入到表中
INSERT INTO emp1(id,hire_date,salary,`name`)
#查询语句
SELECT employee_id,hire_date,salary,last_name#查询的字段一定要与添加到的表的字段一一对应
FROM employees
WHERE department_id IN(60,70);
#说明: empl表中要添加数据的字段的长度不能低于employees表中查询的字段的长度。
#如果empi表中要添加数据的字段的长度低于employees表中查询的字段的长度的话，就有添加不成功的风险
SELECT *
FROM emp1;
#2．更新数据(或修改数据)
#UPDATE .... SET .... WHERE...
#可以实现批量修改数据的。
UPDATE emp1
SET hire_date=CURDATE()
WHERE id=3;
#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date=CURDATE(),salary=9000
WHERE id=4;
#在删除数据时，也有可能因为约束的影响，导致删除失败
#小结:DML操作默认情况下，执行完以后都会自动提交数据。
#如果希望执行完以后不自动提交数据，则需要使用SET autocommit = FALSE.
#4。MySQL8的新特性:计算列
CREATE TABLE test2(
a INT,
b INT,
c INT generated always AS(a+b) VIRTUAL #字段c即为计算列
);
INSERT INTO test2(a,b)
VALUE(10,20);
SELECT * FROM test2;
UPDATE test2
SET a=100;
60-整型数据类型讲解
f3 INT(5) ZEROFILL
0 显示宽度为5。当insert的值不足5位时，使用o填充。o当使用zEROFILL时，自动会添加UNSIGNED
fl INT UNSIGNED
无符号，不包括负数，正数变多
存在四舍五入
此时+0以后，可以以十进制的方式显示数据
第13章_约束
#第13章_约束
/*1.2什么叫约束? 对表中i字段的限制。
1.3 约束的分类:
角度l:约束的字段的个数
单列约束vs多列约束角度
2:约束的作用范围
列级约束:将此约束声明在对应字段的后面
表级约束:在表中所有字段都声明完，在所有字段的后面声明的约束
角度3:约束的作用(或功能)
not null (非空约束)
unique(唯一性约束)
primary key (主键约束)
foreign key (外键约束)
check(检查约束)
default （黑犬认值约束)
1.4如何添加/册除约束?
CREATE TABLE时添加约束
ALTER TABLE时增加约束、删除约束
*/
#3. not null (非空约束)
USE mytest1;
CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);
DROP TABLE test1
INSERT INTO test1
VALUES(1,'Toom','tom@66com' ,3455);
#错误
INSERT INTO test1
VALUES(2,NULL,'tom@66com' ,3455);
#3.3 在ALTERTABLE时册除约束
ALTER TABLE test1
MODIFY id INT NULL;
DESC test3
#4. unique(准一性约束)
CREATE TABLE test3(
id INT NOT NULL,
last_name VARCHAR(15) ,
email VARCHAR(25) UNIQUE,
salary DECIMAL(10,2),
CONSTRAINT uk_test3_id UNIQUE(id)
);
#可以向声明为unique的字段上添加null值。而且可以多次添加null
INSERT INTO test3
VALUES(1,'Toom','tom@66com' ,3455);
SELECT *
FROM test3
INSERT INTO test3
VALUES(1,'Toom1','tom@661com' ,13455);
#4.3复合的唯一性约束
CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(15),
#表级约束
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)
);
#如何删除唯一性索引
ALTER TABLE USER
DROP INDEX uk_user_name_pwd
#5. primary key (主键约束)
#—一个表中只能有一个主键约束。
#主键约束特征:非空且唯一，用于唯一的标识表中的一条记录。
#6．自增长列:AUTO INCREMENT
CREATE TABLE test4(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15) ,
email VARCHAR(25) UNIQUE,
salary DECIMAL(10,2)
);
#7.foreign key (外健约束)
#主表和从表;父表和子表
#o先创建主表
CREATE TABLE dept1(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(15)
);
#o再创建从表
CREATE TABLE emp1(
emp1_id INT PRIMARY KEY AUTO_INCREMENT,
emp1_name VARCHAR(15),
department_id INT,
#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1 (dept_id)
);
INSERT INTO dept1
VALUE (NULL,'IT')
SELECT *
FROM dept1
INSERT INTO emp1
VALUE (NULL,'tom',1)
SELECT *
FROM emp1
#删除外键约束
ALTER TABLE empl
DROP FOREIGN KEY fk_empl_dept_id;
#8. check约束
#9.DEFAULT约束
CREATE TABLE test6(
id INT DEFAULT 000,
last_name VARCHAR(15) ,
email VARCHAR(25) UNIQUE,
salary DECIMAL(10,2) CHECK(salary>2000)
);
DESC test6
浙公网安备 33010602011771号