–查询员工编号、名字、薪资、按照工资升序排序
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY ASC;
–按照姓名进行降序排序
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY FIRST_NAME DESC;
8.2.2 依据多列进行排序
–查询员工编号,名字,薪资;按照工资进行升序排序,如果工资相等,按照编号降序
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
ORDER BY SALARY ASC , EMPLOYEE_ID DESC;
语法: SELECT 列名 FROM 表名 WHERE 条件
- WHERE : 在查询结果中,筛选符合条件的查询结果,条件为布尔表达式;
8.3.1 等值判断(=)
–查询姓为James的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees WHERE FIRST_NAME = ‘James’;
- MySQL中等值判断用 =
8.3.2 不等值判断(>、<、>=、<=、!=、<>)
–查询员工工资不等于2500的员工的信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY <> 2500;
8.3.3 逻辑判断(and、or、not)
–查询员工工资在6000~10000的员工的信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY >= 6000 AND SALARY <= 10000;
–查询员工工资是13256或者9000的员工的信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY = 13256 OR SALARY = 9000;
–查询员工工资除了5000的员工的信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE NOT SALARY = 5000;
8.3.4 区间判断(between and)
–区间判断 包含区间边界的两个值
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE NOT SALARY BETWEEN 6000 AND 10000;
- between and要遵循 between 小值 and 大值;
8.3.5 NULL值判断(IS NULL,IS NOT NULL)
IS NULL(是空)
- 列名 IS NULL
IS NOT NULL(是非空的)
- 列名 IS NOT NULL
–查询出没有经理编号的员工 IS NULL
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees
WHERE MANAGER_ID IS NULL;
–查询出没有经理编号以外的员工
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees
WHERE MANAGER_ID IS NOT NULL;
–查询出没有经理编号的员工(此处NOT为取反,两个结果)
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees
WHERE NOT MANAGER_ID IS NULL;
8.3.6 枚举查询(IN(value1,value2,value3…))
–查询部门编号为 70、80、90的员工信息
–方式一:
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID FROM t_employees
WHERE DEPARTMENT_ID = 70 OR DEPARTMENT_ID = 80 OR DEPARTMENT_ID = 90;
–方式二:
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID FROM t_employees
WHERE DEPARTMENT_ID IN(70,80,90);
8.3.7 模糊查询(_、%)
LIKE
- LIKE (单个任意字符)
* 列名 LIKE ‘S\_’
- LIKE %(任意长度的任意字符 0~n个)
* 列名 LIKE ‘S%’
–模糊查询 查询名以K开头长度为5个字符数的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees
WHERE LAST_NAME LIKE ‘K____’;
–查询名以K开头所有的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees
WHERE LAST_NAME LIKE ‘K%’;
–查询名以包含K所有的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees
WHERE LAST_NAME LIKE ‘%K%’;
–查询名以包含第三个是K所有的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees
WHERE LAST_NAME LIKE ‘__K%’;
8.3.8 分支结构查询
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
WHEN 条件3 THEN 结果3
WHEN 条件4 THEN 结果4
ELSE 结果
END
–查询员工信息(编号、名字、薪资、薪资级别<条件表达式>)
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,
CASE
WHEN SALARY >= 10000 THEN ‘A’
WHEN SALARY >= 8000 AND SALARY < 10000 THEN ‘B’
WHEN SALARY >= 6000 AND SALARY < 8000 THEN ‘C’
WHEN SALARY >= 4000 AND SALARY < 6000 THEN ‘D’
ELSE ‘E’
END AS ‘薪资级别’
FROM t_employees;
–管理部门
SELECT MANAGER_ID,
CASE
WHEN MANAGER_ID = 100 THEN ‘第一’
WHEN MANAGER_ID = 102 THEN ‘第2’
WHEN MANAGER_ID = 103 THEN ‘第3’
END AS ‘部门’
FROM t_employees;
-
通过使用CASE END进行条件判断,每条数据对应生成一个值()
-
case分支结构产生一个新的列
语法: SELECT 时间函数(参数列表);
–1.当前系统时间
SELECT SYSDATE();
–2.获得当前日期
SELECT CURDATE();
–3.获得当前时间
SELECT CURTIME();
–4.获得指定日期在一年内为第几周
SELECT WEEK(CURDATE());
–5.获取指定日期的年份
SELECT YEAR(CURDATE());
–6.获取指定日期的月份
SELECT MONTH(CURDATE());
–7.获取指定日期的日
SELECT DAY(CURDATE());
–8.获取指定日期的时
SELECT HOUR(CURDATE());
–9.获取指定日期的分
SELECT MINUTE(CURDATE());
–10.获取指定日期的秒
SELECT SECOND(CURDATE());
–11.获取date1和date2之间相隔的天数
SELECT DATEDIFF(SYSDATE(),2019-3-26);
–12.获取指定日期之上加上N天后的日期
SELECT ADDDATE(SYSDATE(),6);
- 执行时间函数查询,会生成一张虚拟表(一行一列)
语法:SELECT 字符串函数(参数列表);
–1.连接 多个字符串连接在一起
SELECT COUNT(‘My’,‘S’,‘QL’);
–2.插入替换(下标是从1开始)
–将MYSQL数据库中的M开始的五个字符替换为Oracle
SELECT INSERT(‘MySQL数据库’,1,5,‘Oracle’);
–3.转小写
SELECT LOWER(‘MYSQL’);
–4.转大写
SELECT UPPER(‘mysql’);
–5.截取 (从3开始截取4个内容)
SELECT SUBSTRING(‘发生的范围规范化’,3,4);
- 执行字符串函数,产生一张虚拟表,(一行一列)
语法:SELECT 聚合函数(列名) FROM 表名;
–1.查询员工一共多少人 总行
SELECT COUNT(EMPLOYEE_ID) AS ‘员工总数’ FROM t_employees;
SELECT COUNT(MANAGER_ID) AS ‘经理总数’ FROM t_employees;
SELECT COUNT(*) FROM t_employees;
–2.查询工资总和总和
SELECT SUM(SALARY) FROM t_employees;
–3.查询每个员工的每月平均工资
SELECT AVG(salary ) FROM t_employees;
–4.查询月薪最高的
SELECT MAX(SALARY) FROM t_employees;
–5.查询月薪最低的
SELECT MIN(SALARY) FROM t_employees;
-
聚合函数会自动null值,不进行统计
-
聚合函数式对多条数据的单列进行统计,返回统计后的一行结果
语法: SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据(列名)
- GROUP BY : 分组依据;如果有WHERE,在WHERE之后生效
–查询各部门的总人数
SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID)
FROM t_employees
GROUP BY DEPARTMENT_ID;#先执行
–查询各部门的平均工资
SELECT DEPARTMENT_ID,AVG(SALARY) AS ‘平均工资’,COUNT(EMPLOYEE_ID) AS ‘人数’
FROM t_employees
GROUP BY DEPARTMENT_ID;
–查询各个部门、岗位的人数
SELECT DEPARTMENT_ID AS ‘部门’,JOB_ID AS ‘岗位’, COUNT(EMPLOYEE_ID) AS ‘人数’
FROM t_employees
GROUP BY DEPARTMENT_ID,JOB_ID;
–查询各个部门的ID,总人数、first_name
SELECT DEPARTMENT_ID, COUNT(EMPLOYEE_ID) ,FIRST_NAME
FROM t_employees
GROUP BY DEPARTMENT_ID,JOB_ID;
- 分组查询中,select显示的列只能是分组依据的列或者是聚合函数列,不能出现其他列;
语法: SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据(列名) HAVING 过滤规则
- HAVING : 过滤规则是对分组后的数据进行过滤
–统计部门中编号为60、70、80的部门最高的工资
SELECT DEPARTMENT_ID, MAX(SALARY)
FROM t_employees
GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID IN (60,70,80);
–统计部门中最低的工资
SELECT DEPARTMENT_ID, MIN(SALARY)
FROM t_employees
GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID IN (60,70,80);
-
where和having的区别:
-
where是在group by之前进行条件判断,满足条件,进行选择
-
having是在group by之后进行条件判断,满足条件,进行筛选
-
where语句后面不能使用聚合函数的,having语句后面可以使用聚合函数
语法:SELECT 列名 FROM 表名 LIMIT 起始行,查询行
- LIMIT offset_start,row_count : 限定查询结果的起始行和总行数
–查询前5条
SELECT * FROM t_employees LIMIT 0,5;
–查询第二页数据
SELECT * FROM t_employees LIMIT 5,5;
–查询第三页数据
SELECT * FROM t_employees LIMIT 10,5;
-
起始行是从0开始,代表了第一行。第二个参数代表的是从指定行开始查询几行
-
在分页的应用场景中,起始行是跟随页数变化的,但是一页显示的条数是不变得
SQL语句编写顺序:
- ELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列 LIMIT 起始行,总条数
SQL语句执行顺序:
- 执行 FROM : 指定数据来源表
- 执行WHERE : 对查询的数据做第一次过滤
- 执行GROUP BY :分组
- 执行HAVING : 对分组后的数据做第二次过滤
- 执行SELECT : 查询各个字段的值
- 执行ORDER BY : 排序
- 执行LIMIT : 限定查询结果
8.11.1 作为条件判断
语法:SELECT 列名 FROM 表名 WHERE 条件(子查询结果)
–查询工资大于Diana的员工信息(只能一个值比较)
SELECT salary FROM t_employees WHERE FIRST_NAME = ‘Diana’;
SELECT * FROM t_employees WHERE SALARY > 6000;
–整合
SELECT * FROM t_employees WHERE SALARY > SELECT salary FROM t_employees WHERE FIRST_NAME = ‘Diana’;
-
将子查询"一行一列"的结果作为外部查询的条件。做第二次查询
-
子查询得到的是一行一列的结果才能作为外部条件的等值或不等值判断条件
8.11.2 作为枚举查询的条件
语法:SELECT 列名 FROM 表名 WHERE 列名 IN(子查询结果)
–查询与King同一部门员工信息
–1.查询King所在的部门编号(多行单列)
SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME = ‘King’;
–2.将1查询出的作为枚举查询的条件
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees
WHERE DEPARTMENT_ID IN (80,90);
–3.整合
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME = ‘King’);
–工资高于60的部门的所有人的信息
–1.工资为60部门的工资
SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60;
–2.查询高于60部门所有人的工资的员工信息(高于所有人)
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY > ALL
(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);
–3.整合 查询高于60部门所有人的工资的员工信息(高与部分人)
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY > ANY
(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);
-
将子查询得到的"多行一列"的结果作为外部查询的枚举查询条件,做第二次查询
-
当子查询结果集为多行单列时,也可以使用ALL匹配所有或者ANY匹配部分
8.11.3 作为一张表
语法:SELECT 列名 FROM (子查询结果集) WHERE 条件;
–查询员工表中部分列的信息(工资大于8000)
–1.先查询部分列的信息作为临时表
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC ;
–2.将子查询得到临时表作为外部查询表
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM
(SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC ) AS temp
WHERE SALARY > 8000;
- 将子查询得到的"多行多列"的结果作为外部查询的一张临时表,做第二次查询
语法:
- SELECT 列名 FROM 表名 1 UNION SELECT 列名 FROM 表名2
- SELECT 列名 FROM 表名 1 UNION ALL SELECT 列名 FROM 表名2
–合并两张表的结果
–去重
SELECT *FROM t1
UNION
SELECT *FROM t2;
–不去重
SELECT *FROM t1
UNION ALL
SELECT *FROM t2;
-
合并的两个结果集,列数必须相同,列类型、列名可以不同
-
纵向合并
语法:SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件;
8.13.1 内连接查询(INNER JOIN ON)
–如果只做连接,产生的表叫笛卡尔积
–sql标准(通用)
SELECT * 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,t_departments
WHERE t_employees.DEPARTMENT_ID
= t_departments.DEPARTMENT_ID
;
–1.两张表连接查询要有关联条件;若列重复,需要明确查询的是那个表的列
–2.表名较长,可以起别名
SELECT EMPLOYEE_ID,FIRST_NAME,d.DEPARTMENT_ID,DEPARTMENT_NAME FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.DEPARTMENT_ID
= d.DEPARTMENT_ID
;
–查询所有岗位的员工信息,显示岗位名称
SELECT EMPLOYEE_ID,FIRST_NAME,JOB_TITLE
FROM t_employees AS e
INNER JOIN t_jobs AS j
ON e.JOB_ID
= j.JOB_ID
;
8.13.2 三表连接查询
–查询所有员工号、名字、部门名称、所在城市的名称
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
;
8.13.3 多表连接查询
–查询所有员工号、名字、部门名称、部门所在城市名称、所在国家的名称
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME,CITY,COUNTRY_NAME
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
;
- 多表查询时,要明确哪一张表和连接的表有关系
8.13.4 左外连接查询(LEFT JOIN ON)
#查询所有员工信息,以及对应的部门名称
#没有部门的员工也在查询结果中,但是部门名称以NULL填充
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
;
-
左外连接,是以左表为主表,依次向右表匹配,匹配到,则返回正确结果
-
匹配不到,则返回NULL值,填充显示
8.13.5 右外连接查询(RIGHT JOIN ON)
#查询所有部门信息,以及对应的员工信息
#没有员工的部门也在查询结果中,但是员工信息以NULL填充
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
;
-
右外连接,是以右表为主表,依次向左匹配,匹配到,返回正确结果
-
匹配不到,则返回NULL填充
==============================================================================
约束就是保证当前表中的数据的正确性、有效性和完整性是合法数据;
表中一行数据代表一个实体,实体完整性约束是标识每一行数据不重复使得实体具有唯一性;
9.1.1 主键约束
PRIMARY KEY 唯一、标识表中的一行数据,此列的值不可重复,且不能为NULL;
–创建表中,选择适合做主键的列,添加主键约束
–学生表
CREATE TABLE Student(
stuid INT PRIMARY KEY,–主键约束,每一个编号是唯一的,不能为null
stuName VARCHAR(20),
phone VARCHAR(11)
)CHARSET=utf8;
INSERT INTO Student(stuid,stuName,phone)
VALUE (001,‘kaka’,100015);
9.1.1.1 删除主键约束
– 删除主键约束:alter table 表名 drop primary key ;
ALTER TABLE Student DROP PRIMARY KEY ;
9.1.1.2创建表后加入主键约束
– 创建表后,加入主键约束
ALTER TABLE Student MODIFY id INT PRIMARY KEY ;
9.1.2 唯一约束
UNIQUE 唯一、标识表中的一行数据,不可重复,可以为NULL;
–表中的手机号列,添加唯一约束!不能重复,但是可以为NULL
CREATE TABLE Student(
stuid INT PRIMARY KEY,
stuName VARCHAR(20),
phone VARCHAR(11) UNIQUE–唯一的约束,可以为空;
)CHARSET=utf8;
INSERT INTO Student(stuid,stuName,phone)
VALUE (002,‘kaka’,1000154);
INSERT INTO Student(stuid,stuName,phone)
VALUE (003,‘kaka’,NULL);
SELECT * FROM Student;
9.1.3 自动增长列
AUTO_INCREMENT 自动增长,给主键数值列添加自动增长。从1开始,每次++;不能单独使用,和主键搭配;
#自动增长,避免ID重复
CREATE TABLE Student(
stuid INT PRIMARY KEY AUTO_INCREMENT,#必须搭配主键
stuName VARCHAR(20),
phone VARCHAR(11)
)CHARSET = utf8;
INSERT INTO Student(stuName,phone)
VALUE (001,‘kaka’,100015);
INSERT INTO Student(stuName,phone)
VALUE (002,‘kaka’,1000154);
INSERT INTO Student(stuName,phone)
VALUE (003,‘kaka’,NULL);
– 删除自增长约束 alter table 表名 modify id int ;
– 添加自增长 alter table 表名 modify id int AUTO_INCREMENT ;
限制列的每一个单元格的数据正确性
9.2.1 非空约束
NOT NULL 非空,约束此列的每一个单元格不允许有NULL值;
#非空约束,必须有值
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
empName VARCHAR(20) NOT NULL,#约束名字一列必须有值
address VARCHAR(50) NOT NULL
)CHARSET = utf8;
INSERT INTO emp (empName,address) VALUE (‘kaka’,‘xi`an’);
9.2.2 默认值约束
DEFAULT 为列赋予默认值,当新增的数据不指定值时,写DEFAULT,以定义好的默认值进行填充;
#默认值约束
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;
INSERT INTO emp (empName,address,sex) VALUE (‘kaka’,‘xi`an’,DEFAULT);
9.2.3 引用完整性约束(外键约束)
- 语法:CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)
FOREIGN KEY 引用外部表的某5个列的值,新增数据时,约束此列的值必须是被引用表中存在的值
–引用完整性约束
–专业表
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,
subjecname VARCHAR(20) UNIQUE NOT NULL,
subjecthours INT DEFAULT 20,#默认值20
specialid INT NOT NULL,
CONSTRAINT fk_subject_specialid#外键
FOREIGN KEY(specialid)
REFERENCES Speciality(id)
)CHARSET=utf8;
–存在引用关系的表。要先添加被引用的表数据(主键表).再添加引用表的数据(外键表)
INSERT INTO Speciality (SpecialName) VALUES(‘Java’);
INSERT INTO Speciality (SpecialName) VALUES(‘HTML5’);
INSERT INTO subject
(subjecname,subjecthours,specialid)
VALUES(‘JavaSE’,10,1);
INSERT INTO subject
(subjecname,subjecthours,specialid)
VALUES(‘JavaScript’,20,2);
- 两张表存在引用关系时,执行删除操作注意,先删除从表(引用表、外键表),再删除主表(被引用表、主键表;
–删除Speciality
DROP TABLE Speciality;–先删除主表,容易造成外键表数据孤立,不可先删除
DROP TABLE subject
;–先删除引用表后删除主表
9.2.3.1 案例
– 创建部门表department:id部门 编号 以及 部门名称dept_name
– 主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(30)
);
– 创建员工表:单独描述员工信息(id:员工编号,name:员工姓名,部门id)
– 从表:外键需要在这个里面设置
–
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dept_id INT, – 部门编号 需要关联主表的主键id字段(部门表的id)
– CONSTRAINT(声明) 外键名称(从表名称_主表名称_fk) foreign key (从表的列名称) references(关联) 主表名称(主键字段)
CONSTRAINT emp_dep_fk FOREIGN KEY (dept_id) REFERENCES department(id)
);
– 给部门表department 插入数据
INSERT INTO department (dept_name) VALUES(‘研发部’);
INSERT INTO department (dept_name) VALUES(‘测试部’);
– 给员工表employee 插入数据
INSERT INTO employee (NAME,age,dept_id) VALUES(‘张三’,20,1) ;
INSERT INTO employee (NAME,age,dept_id) VALUES(‘李四’,22,1) ;
INSERT INTO employee (NAME,age,dept_id) VALUES(‘赵六’,20,1) ;
– 设置外键了 ,加入非法数据
INSERT INTO employee (NAME,age,dept_id) VALUES(‘王五’,23,5) ;
– 外键的作用:就是将两张表关联起来,保证数据的完整性!(合法性)
– 如果要去员工表插入一条数据,并且部门不存在(3号部门)
– 步骤:首先在部门表中插入3号部门
INSERT INTO department(dept_name) VALUES(‘销售部’) ;
– 在给员工表中插入数据3号部门的人
INSERT INTO employee (NAME,age,dept_id) VALUES(‘田七’,25,3) ;
– 一旦从表和主表有关系(外键关系) ,删除,修改 都需要建立先操作主表基础上!
– 删除外键名称(emp_dep_fk)
ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;
INSERT INTO employee (NAME,age,dept_id) VALUES(‘马七’,27,4) ;
DELETE FROM employee WHERE id = 9;
– 创建表后,增加外键
– atler table 表名 add CONSTRAINT emp_dep_fk FOREIGN KEY (dept_id) REFERENCES department(id)
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dept_id) REFERENCES department(id) ;
创建带有约束的表
9.3.1 创建Grade表
–Grade
CREATE TABLE Grade(
GradeId INT PRIMARY KEY AUTO_INCREMENT,#主键、自增
GradeName VARCHAR(20) UNIQUE NOT NULL#唯一、非空
)CHARSET = utf8;
SELECT * FROM Grade;
INSERT INTO Grade(GradeName) VALUES(‘JAVA’);
INSERT INTO Grade(GradeName) VALUES(‘HTML’);
INSERT INTO Grade(GradeName) VALUES(‘Spring’);
9.3.2 创建Student表
–Student表
CREATE TABLE Student(
student_id VARCHAR(50) PRIMARY KEY,#主键
student_name VARCHAR(50) NOT NULL,#非空
sex CHAR(2) DEFAULT ‘男’,#默认男
borndate DATE NOT NULL,#非空
phone VARCHAR(11),
GradeId INT NOT NULL,#非空
CONSTRAINT fk_student_gradeId #外键约束
FOREIGN KEY(GradeId)#列名
REFERENCES Grade(GradeId)#被引用表名
)CHARSET=utf8;
SELECT * FROM student;
INSERT INTO student(student_id,student_name,sex,borndate,phone,GradeId)
VALUES(‘1001’,‘卡卡’,DEFAULT,‘200200101’,NULL,2);
INSERT INTO student(student_id,student_name,sex,borndate,phone,GradeId)
VALUES(‘1002’,‘糖糖’,‘女’,‘20200101’,NULL,3);
- 在创建有关系关联表时,要先创建主表(主键),再创建从表(外键表)
================================================================================
-
一对一的关系: 一个人对应一张身份证,一张身份证中对应一个人 (使用较少)
-
一对多或者对多一的关系:一个人对应可以多个订单信息,一个订对应一个人 (1)
部门表 员工表 (外键 dept_id)
-
多对多的关系:一个学生可以选择多门课程,一个课程被多个学生进行选择(2)
-
多对多关系建表原则: 需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键
============================================================================
事务是一个原子操作、是一个最小执行单元;可以由一个或多个SQL语句组成,在同一个事务中,所有的SQL语句都成功执行时,整个事务成功!有一个SQL语句执行失败,整个事务都执行失败!
开始:
- 连接到数据库,执行一条DML语句。 上一个事务结束后,又输入了一条DML语句,即事务的开始;
结束:
- 提交:
显示提交:COMMIT;
隐式提交:一条DML语句。正常退出(客户端退出链接);
- 回滚:
显示回滚:ROLLBACK;
隐式回滚:非正常退出,执行了创建、删除的语句,但是失败,会为这个无效的SQL语句执行回滚;
数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段),一个事务中所有的增删改语句的执行结果都会缓存在回滚段中,只有当事务中所有的SQL语句均正常结束(COMMIT),才会将回滚段中的数据同步到数据库。否则整个事务回滚(ROLLBACK);
Atomicity(原子性)
- 表示的是一个事务内的所有操作是一个整体,要么全部成功,要么全部失败;
Consistency(一致性)
- 表示一个事务内有一个操作失败时,所有的更改过得数据都必须回滚到修改前状态,数据本身的总数保持不变;
Isolation(隔离性)
- 事务查看数据操作时数据所处的状态,要么是另一个并发事务修改数据之前的状态,要么是另一个并发事务修改它之后的状态,事务和事务之间保存隔离,不会查看中间状态的数据;
Durability(持久性)
- 事务提交之后,对于数据库的影响是永久性的;
CREATE TABLE account(
id INT,
money INT
)CHARSET = utf8;
INSERT INTO account(id,money) VALUES(1,10000);
INSERT INTO account(id,money) VALUES(2,1000);
SELECT * FROM account;
#1账号转钱给2账户1000元
#开启事务
START TRANSACTION; #方式1 开启一个事务
SET autoCommit = 0;#方式2 设置自动提交为0:关闭自动提交; 1:开启自动提交
#原子操作
#1账户扣钱
UPDATE account SET money = money - 1000 WHERE id = 1;
#2账户加钱
UPDATE account SET money = money + 1000 WHERE id = 2;
#执行提交 —成功
COMMIT;
#执行回滚 —失败
ROLLBACK;
- 开启事务后,在当前事务内执行的语句均属于当前事务,成功再执行COMMIT,失败要进行ROLLBACK;
============================================================================
- 语法:CREATE USER 用户名 IDENTIFIED BY 密码
#创建用户
CREATE USER ‘zhangsan’ IDENTIFIED BY ‘123’;
- 语法:GRANT ALL ON 数据库.表名 TO 用户名;
#将companydb数据里的grade表授权给zhangsan
GRANT ALL ON companydb.grade
TO ‘zhangsan’;
#将companydb数据库里的所有表授权给zhangsan
GRANT ALL ON companydb.* TO ‘zhangsan’;
- 语法:REVOKE ALL ON 数据库.表名 FROM 用户名;
REVOKE ALL ON companydb.grade FROM ‘zhangsan’;
- 语法:DROP USER 用户名;
DROP USER ‘zhangsan’;
#创建用户名为kaka密码为123的用户
CREATE USER ‘kaka’ IDENTIFIED BY ‘123’;
#授权
#对kaka开放stu表的授权
GRANT ALL ON companydb.stu
TO ‘kaka’;
#对kaka开放所有表的授权
GRANT ALL ON companydb.* TO ‘kaka’;
#撤销
#撤销kaka对stu表的权限
REVOKE ALL ON companydb.stu FROM ‘kaka’;
#删除kaka用户
DROP USER ‘kaka’;
==========================================================================
视图是一个虚拟表,只操作部分数据;从一个表中或多个表中查询出来的结果表,作用和真实表一样,包含一系列的带有行和列的数据。视图中,可以使用SELECT语句查询数据,也可以使用INSERT、UPDATE、DELETE修改记录,视图可以使用户操作方便,并保障了数据库系统安全;
- 优点
* 简单化,数据所见即所得;
* 安全性,只能查询或修改视图中锁能见到的数据;
* 逻辑独立性,可以屏蔽真实表结构变化带来的影响;
- 缺点
* 性能相对较差,简单的查询会稍微复杂;
* 修改不方便,当视图的数据时复杂的聚合视图时,无法修改;
14.3.1 创建视图
- 语法:CREATE VIEW 视图名 AS 查询数据源表的语句;
#创建一个t_empinfo视图,该视图的数据是员工姓名,邮箱,手机号码
CREATE VIEW t_empinfo
AS
SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;
14.3.2 使用视图
#使用视图
#查询
SELECT * FROM t_empinfo WHERE FIRST_NAME=‘Steven’ AND LAST_NAME=‘King’;
#修改 只能修改得到的
UPDATE t_empinfo SET email = ‘Kings’ WHERE FIRST_NAME=‘Steven’ AND LAST_NAME=‘King’;
14.3.3 视图的修改
- 方式一:CREATE OR REPLACE VIEW 视图名 AS 查询源表的语句;
- 方式二:ALTER VIEW 视图名 AS查询源表的语句;
#视图的修改
#方式1:存在就替换数据,不存在就新建
CREATE OR REPLACE VIEW t_empinfo
AS
SELECT employee_id,FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;
#方式2
ALTER VIEW t_empinfo
AS
SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注Java获取)
最后
本人也收藏了一份Java面试核心知识点来应付面试,借着这次机会可以送给我的读者朋友们:
目录:
Java面试核心知识点
一共有30个专题,足够读者朋友们应付面试啦,也节省朋友们去到处搜刮资料自己整理的时间!
Java面试核心知识点
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!
点
* 性能相对较差,简单的查询会稍微复杂;
* 修改不方便,当视图的数据时复杂的聚合视图时,无法修改;
14.3.1 创建视图
- 语法:CREATE VIEW 视图名 AS 查询数据源表的语句;
#创建一个t_empinfo视图,该视图的数据是员工姓名,邮箱,手机号码
CREATE VIEW t_empinfo
AS
SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;
14.3.2 使用视图
#使用视图
#查询
SELECT * FROM t_empinfo WHERE FIRST_NAME=‘Steven’ AND LAST_NAME=‘King’;
#修改 只能修改得到的
UPDATE t_empinfo SET email = ‘Kings’ WHERE FIRST_NAME=‘Steven’ AND LAST_NAME=‘King’;
14.3.3 视图的修改
- 方式一:CREATE OR REPLACE VIEW 视图名 AS 查询源表的语句;
- 方式二:ALTER VIEW 视图名 AS查询源表的语句;
#视图的修改
#方式1:存在就替换数据,不存在就新建
CREATE OR REPLACE VIEW t_empinfo
AS
SELECT employee_id,FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;
#方式2
ALTER VIEW t_empinfo
AS
SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。[外链图片转存中…(img-ZAWXAur7-1712655313561)]
[外链图片转存中…(img-InxbnV1k-1712655313561)]
[外链图片转存中…(img-xDgOsx8J-1712655313562)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注Java获取)
最后
本人也收藏了一份Java面试核心知识点来应付面试,借着这次机会可以送给我的读者朋友们:
目录:
[外链图片转存中…(img-np5Mx5VA-1712655313562)]
Java面试核心知识点
一共有30个专题,足够读者朋友们应付面试啦,也节省朋友们去到处搜刮资料自己整理的时间!
[外链图片转存中…(img-C8xwBRca-1712655313562)]
Java面试核心知识点
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!