万字mysql基础入门文档

【ima笔记】数据库Mysql https://ima.qq.com/note/share/_AwePbuI-QCfLZMbaFFgNA?channel=4

数据库Mysql

1.视频网址

2.数据库的基础知识

3.Mysql概述

数据：数据库储存的基本对象。

数据库：Database(DB)   存储数据的仓库

数据库管理系统：Database Management System (DBMS)   操作管理数据的软件

SQL: 操作管理数据库的编程语言，定义了操作关系型数据库的标准

数据库系统（DBS）：集大成，运行模式分为两种：客户机C/S（app式）,浏览器B/S（网站）

4.下载安装Mysql

5.数据模型

数据模型

关系型数据库：

解读二维表：

每一行和列个数有限，可以交换次序，各不相同，值域确定

数据库设计

1.需求分析

2.概念结构设计（关键）：er图

ER图的三种冲突：

1.属性冲突：值域冲突：学号：int 和varchar

2.命名冲突：语文问题

3.结构冲突

ER图的优化：消除有基本属性结算或推导可得的属性

3.逻辑结构设计

将概念模型转变为数据库系统支持的逻辑模型

实体集的转换规则：

转换成一个独立的关系模式，也可以和n端合并

在独立模式下，最大的区别就是，拥有那里只能选择n端作为主键

范式

为了提示性能，保证数据的准确性和可靠性，有5种规范化形式，叫做范式

第一范式（1NF）：每个属性都是不可以分割的

第二范式（2NF）：必须满足1NF.

1.数据唯一   2.每个属性完全依赖于主键

第三范式（3NF）

要求不存在其他属性对主属性的值传递依赖

也就是说不能存在完全依赖主属性的值同时决定着另一个完全依赖主属性的值

同时要求不可以存在通过简单计算得出来的数据

SQL篇

（一）SQL的通用语法

（二）SQL语句的分类

（三）DDL:数据定义语言

1.查询显示所有数据库

SHOW DATABASES;

2.查询当前数据库

SELECT DATABASE();

2.创建数据库

if not exists:有S结尾啊

CREATE DATABASE 数据库名字;CREATE DATABASE [IF NIT EXISTS] 数据库名字;

3.修改数据库

把创建修改为alter即可

alter 数据库名 修改的东西

4.删除

DROP DATABASE [IF EXISTS] 数据库名字;

存在即删除

5.使用/切换数据库

6.查询数据库

表格

1.查看数据库的所有表格

SHOW TABLES;

2.查询表的结构

DESC 表名;show columns from 表格;

3.查看建表语句

SHOW CREATE TABLES 表名;

7.表的创建语句

7.1操作过程

查询

加入这个句子，表示不存在则创建，存在则不创建

查看数据库的所有表格

7.2数据类型案例

char(定长)

varchar(变长)

括号里面指的是字符长度！不是字节

括号里面的n实际表示的是显示位数

表的不同参数：

题目1：设计一张表，要求如下：

1.编号：（数字）

2.工号：（字符串，小于10个字符）

3.姓名：（字符串，小于10个字符）

4.性别：（一个汉字）

5.年龄：（正整数）

6.身份证号：（18位数）

7.入职时间

create table emo(–编号  id int comment ‘编号’,–工号  worknum varchar(10) comment ‘工号’,–姓名  name varchar(10) comment ‘姓名’,  –性别  gender char(1) comment ‘性别’,–年龄  age tinyint unsigned comment ‘年龄’,–身份证  idcard char(18) comment ‘身份证’,–时间  entrytime date comment ‘入职时间’     )comment ‘员工表’create table emo(id int comment ‘编号’,worknum varchar(10) comment ‘工号’,name varchar(10) comment ‘姓名’,  gender char(1) comment ‘性别’,age tinyint unsigned comment ‘年龄’,idcard char(18) comment ‘身份证’,entrytime date comment ‘入职时间’     )comment ‘员工表’;

题目2：在数据库scoredb创建表格department;

use scoredb;create table department(deptno char(3) not null primary KEY comment ‘院系代码’,depname varchar(50) unique comment ‘院系名称’,director varchar(50) default ‘院长’ comment ‘负责人’)COMMENT ‘学院表’

题目3：创建家庭表familyinfo，改名字为sfamily

8.表修改

9添加字段

ALTER TABLE 表名字 ADD 字段名 类型（长度） [comment 注释] [约束];

最后是写位置，要么填写first ，要么填写after什么什么

在emo表里面添加nickname 要求varchar(10)

修改字段

1.修改数据类型

ALTER table 表名 MODIFY 字段名 新的数据类型（长度）;

2.修改字段名和字段类型

ALTER table 表名 CHANGE 旧字段名 新字段名 数据类型（长度）[comment 注释] [约束];

3.删除字段

ALTER TABLE 表名 DROP 字段名;

4.修改表的名字

ALTER TABLE 表名 RENAME TO 新的表名;

5.删除表

DROP TABLE [ID EXISTS] 表名;

删除指定表，并且重新创建此表

TRUNCATE TABLE 表名;

复制数据表格

这里是包括了数据和结构

create table 新表格名字 select * from 原表名

仅有结构

create table 新表名 Like 原表名

（四）DML语句-数据操作

增加（insert）

修改（update）

删除（delete）

1.添加数据

给指定字段添加数据

INSERT INTO 表名 (字段名1，2，3) VALUES (值1，2，3);

给全部字段添加数据

删除了上述语句字段名字

INSERT INTO 表名 VALUES (值1，2，3);

批量添加语句

INSERT INTO 表名 VALUES (值1，2，3)(值1，2，3)(值1，2，3);

插入查询结果的数据(将表2的数据插入表1)

insert into 表格1 select from department;

插入替换存在的数据

replace into

案例：添加数据并且查询

2.修改数据

UPDATE 表名 SET 字段名字1=？, 字段2=？, 字段3=？…[WHERE 条件];

案例：当前有4条数据，按要求修改

（1）将id为1的数据的name修改为itheima

UPDATE emp set name = ‘itheima’ WHERE id = 1;SELECT * FROM emp;

3.删除数据

(五)DQL语句-数据查询

1.基础查询

SELECT 字段1，字段2… from 表名：

查询全部字段

SELECT * from 表名：

通过给字段设置别名可以增加字段的可读性

SELECR 字段 [AS 别名] … FROM 表名;

通过去除重复的记录增加可读性

SELECT DISTINCT 字段列表 FROM 表名;

案例

1.要求查询 name ,worknum,age

SELECT name ,worknum ,age from emp;

2.查询所有字段

SELECT * from emp;

3.要求查询 id，name，age查询中文显示

SELECT id as ‘编号’,name as ‘姓名’, age as ‘年龄’from emp;

2.条件查询

1.语法

SELECT 字段1，字段2… from 表名 WHERE 条件；

where后面的条件可以是1个，也可以是多个

案例：

1.查询年龄>=88的员工

select * from emp where age>=88;

不等于88

有两种写法

select * from emp where age != 88;select * from emp where age <> 88;

在20到88岁之间

select * from emp where age >= 20 && age <=88;select * from emp where age >= 20 and age <=88;select * from emp where age between 20 and 88;

找到2个名字的员工

_ 表示一个字符

% 表示任意字符

select * from emp where name like ‘__’

身份证后面是X

select * from emp where idcard like ‘%X’;

2.查询没有身份证的员工,或者在里面替换为17个下划线

select * from emp where idcard is null;

有身份证

(插入not)

select * from emp where idcard is not null;

3.聚合函数

将一列数据进行纵向的计算

count :统计数量 计算时不包括NULL的值

max min 最值

avg 平均

sum 和

语法：

select 函数[字段列表] from 表名;

例子：

1.统计全部数据的数量

SELECT count(*) FROM emp;

SELECT count(id) FROM emp;–统计id的数量

2.统计员工的平均年龄

select avg(age) from emp;

3.统计男员工的年龄之和

select * from emp where gender = ‘男’;

4.分组查询

group by

语法

select * from 表 [where 条件] group by 字段分组 [having 分组后条件];

1.数据先在where进行筛选第一次

2.筛选完之后由having进行二次筛选

3.where是不能使用聚合函数的，having是可以的

例子：

1.根据性别分组，统计男性和女性的数量

select gender,count(*) from emp group by gender ;

2.根据性别分组，统计平均年龄

select gender,avg(age) from emp group by gender ;

3.查询年龄小于19的员工数量 ，根据性别分组

select gender,count(*)  from emp where age < 19 group by gender

where>聚合函数>having

5.排序查询

order by

select 字段 from 表 order by 字段1 排序，字段2 排序；

ASC:升序

DESC：降序

1.按年龄排序

select * from emp order by age asc;select * from emp order by age desc;

2.先根据年龄升序，给相同年龄的按照入职时间降序排序

select * from emp order by age asc,entrytime desc;

6.分页查询

limit

语法：

select * from emp limit 起始索引，查询记录数；

查询第一页的员工数，每一页有10个记录

select * from emp limit 0,10;

查询第二页的员工数据，每一页查询10条记录

第二页的第一个数据是10

select * from emp limit 10,10;

DQL的语句练习

1，查询员工为20  21 22 23的女性员工信息

select * from emp where gender = ‘女’ and age in(20,,21,22,23);

2.查询性别为男，年龄在20-40之间的姓名为三位数的员工

select * from emp where gender = ‘男’ and age between 20 and 40 and name like ‘___’;

3.统计年龄小于60岁的，男性员工和女性员工的数量

select gender,count(*) from emp where age < 60 group by gender;

4.查询所有年龄小于35的姓名和年龄，并且按照年龄升序，入职时间降序

select name,age from emp where age <= 35 order by age asc,entrytime desc;

(六)DCL语句

1.管理用户

2.创建用户

3.修改密码

4.删除用户

5.权限控制

1.查询权限

show grants for ‘用户名’@’主机’;

默认usage,即登录并且链接的权限

2.授权

grant all on emp.* to ‘用户名’@’主机’;

3.撤销授权

revoke all on emp.* from ‘用户名’@’主机’;

函数

字符串函数

1.拼接字符串

#拼接字符串select CONCAT(‘hello’,’Mysql’);

2.大小写

#全部转为小写select lower(‘SFGIOF’);#全部转为大写select upper(‘faifyu’);

3.填充

#lpad 左填 给什么填，填到几位数，用什么填select lpad(‘我’,5,’￥’);#rpad 右填select rpad(‘我’,5,’￥’);

4，去除空格

#trim去除空格select trim(‘ hello mysql ‘);

5.截取字符

select substring(‘ hello mysql’,1,5);

从索引1开始截取5个字符

例子

数值函数

（1）对1.5向上取整

select ceil(1.5);

(2)对1.5向下取整

select floor(1.9);

(3)求模运算

3 / 4 = 0……3

select mod(3,4);

例子：生成随机数

select lpad(  round(   rand()*10000000 ,0  ) 6,0 )

日期函数（略）

流程函数

实现函数筛选

IF(value,t,f)

相当于

判断value ？ 结果t ：结果f

结果为真返回t,结果为假返回f

IFNULL(value1,value2)

如果value1里面有东西，就把1给返回

里面是空白的，就把2返回

SELECT #里面为空IFNULL( ”, ‘2’) AS result1,#里面不为空IFNULL(‘1′,’2’) as result2,#里面为NULLIFNULL(NULL,’2′) as result3

发现里面不是空的时候，跳转‘1’

里面是空的字符串，返回空的字符串

里面是是null时，返回第二个

case when [val1]then [res1]else [default]END

如果val1是真，则返回res1

如果是假，则返回default的默认值，记得加上结尾符号

case [表达式] when [val1]then [res1]else [default]END

如果表达式的结果等于val1，则返回res1，反之返回default的值

例子：

1.建表语句

— 创建学生表CREATE TABLE `student` (  `id` int NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’,  `name` varchar(50) NOT NULL COMMENT ‘姓名’,  `gender` char(1) NOT NULL COMMENT ‘性别：男/女’,  `age` int NOT NULL COMMENT ‘年龄’,  `class` varchar(20) NOT NULL COMMENT ‘班级’,  `score` decimal(5,2) NOT NULL COMMENT ‘成绩（0-100）’,  `address` varchar(100) NOT NULL COMMENT ‘地址’,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’学生信息表’;– 插入50条学生信息INSERT INTO `student` (`name`, `gender`, `age`, `class`, `score`, `address`) VALUES(‘张明宇’, ‘男’, 15, ‘高一(1)班’, 87.50, ‘北京市海淀区学院路1号’),(‘李雨桐’, ‘女’, 15, ‘高一(2)班’, 92.00, ‘上海市浦东新区世纪大道2号’),(‘王浩然’, ‘男’, 16, ‘高二(3)班’, 76.25, ‘广东省深圳市南山区科技园3号’),(‘陈思琪’, ‘女’, 14, ‘初三(1)班’, 88.75, ‘浙江省杭州市西湖区文三路4号’),(‘刘子轩’, ‘男’, 13, ‘初二(2)班’, 68.50, ‘江苏省南京市玄武区长江路5号’),(‘赵雨萱’, ‘女’, 17, ‘高三(1)班’, 95.00, ‘四川省成都市武侯区人民南路6号’),(‘周子涵’, ‘男’, 15, ‘高一(3)班’, 73.00, ‘湖北省武汉市洪山区珞喻路7号’),(‘吴可欣’, ‘女’, 14, ‘初三(2)班’, 82.50, ‘陕西省西安市雁塔区长安路8号’),(‘郑乐怡’, ‘女’, 16, ‘高二(1)班’, 91.25, ‘重庆市渝中区解放碑9号’),(‘林俊辉’, ‘男’, 12, ‘初一(1)班’, 79.00, ‘天津市和平区南京路10号’),(‘郭思敏’, ‘女’, 15, ‘高一(4)班’, 85.00, ‘湖南省长沙市岳麓区麓山路11号’),(‘唐子豪’, ‘男’, 17, ‘高三(2)班’, 66.50, ‘福建省厦门市思明区中山路12号’),(‘孙雅静’, ‘女’, 13, ‘初二(1)班’, 93.00, ‘山东省青岛市市南区香港路13号’),(‘宋佳怡’, ‘女’, 16, ‘高二(2)班’, 78.75, ‘辽宁省大连市中山区人民路14号’),(‘徐天乐’, ‘男’, 14, ‘初三(3)班’, 69.00, ‘河南省郑州市金水区花园路15号’),(‘黄子琪’, ‘女’, 15, ‘高一(5)班’, 84.50, ‘安徽省合肥市蜀山区黄山路16号’),(‘胡宇轩’, ‘男’, 17, ‘高三(3)班’, 72.00, ‘云南省昆明市五华区东风路17号’),(‘林晨曦’, ‘女’, 12, ‘初一(2)班’, 98.00, ‘黑龙江省哈尔滨市南岗区西大直街18号’),(‘罗一鸣’, ‘男’, 16, ‘高二(4)班’, 81.25, ‘吉林省长春市朝阳区人民大街19号’),(‘梁心怡’, ‘女’, 15, ‘高一(6)班’, 77.50, ‘江西省南昌市东湖区八一大道20号’),(‘方宇航’, ‘男’, 14, ‘初三(4)班’, 90.00, ‘广西南宁市青秀区民族大道21号’),(‘崔梦瑶’, ‘女’, 13, ‘初二(3)班’, 86.75, ‘贵州省贵阳市南明区中华路22号’),(‘石宇泽’, ‘男’, 17, ‘高三(4)班’, 64.00, ‘甘肃省兰州市城关区庆阳路23号’),(‘戴雨欣’, ‘女’, 16, ‘高二(5)班’, 94.50, ‘内蒙古呼和浩特市新城区新华大街24号’),(‘魏子涵’, ‘男’, 15, ‘高一(7)班’, 70.25, ‘新疆乌鲁木齐市天山区解放路25号’),(‘沈静怡’, ‘女’, 12, ‘初一(3)班’, 89.00, ‘宁夏银川市兴庆区北京路26号’),(‘范子豪’, ‘男’, 14, ‘初三(5)班’, 62.50, ‘青海省西宁市城西区五四大街27号’),(‘钟欣怡’, ‘女’, 16, ‘高二(6)班’, 83.00, ‘海南省海口市龙华区滨海大道28号’),(‘谭子轩’, ‘男’, 15, ‘高一(8)班’, 97.00, ‘河北省石家庄市长安区中山东路29号’),(‘苏雨桐’, ‘女’, 13, ‘初二(4)班’, 74.50, ‘山西省太原市迎泽区迎泽大街30号’),(‘高天宇’, ‘男’, 17, ‘高三(5)班’, 80.00, ‘北京市朝阳区三里屯路31号’),(‘卢思琪’, ‘女’, 14, ‘初三(6)班’, 96.25, ‘上海市徐汇区淮海中路32号’),(‘夏子涵’, ‘男’, 16, ‘高二(7)班’, 67.75, ‘广州市天河区体育西路33号’),(‘廖雨萱’, ‘女’, 15, ‘高一(9)班’, 88.00, ‘深圳市福田区福华路34号’),(‘肖宇航’, ‘男’, 12, ‘初一(4)班’, 75.00, ‘杭州市余杭区文一西路35号’),(‘白梦瑶’, ‘女’, 17, ‘高三(6)班’, 91.00, ‘南京市鼓楼区中山北路36号’),(‘贺宇泽’, ‘男’, 14, ‘初三(7)班’, 63.50, ‘成都市青羊区蜀都大道37号’),(‘欧阳光’, ‘男’, 16, ‘高二(8)班’, 85.75, ‘武汉市江汉区建设大道38号’),(‘慕容晴’, ‘女’, 15, ‘高一(10)班’, 92.50, ‘西安市碑林区友谊西路39号’),(‘南宫逸’, ‘男’, 13, ‘初二(5)班’, 78.00, ‘重庆市江北区观音桥40号’),(‘上官婉儿’, ‘女’, 17, ‘高三(7)班’, 99.00, ‘天津市南开区卫津路41号’),(‘诸葛明’, ‘男’, 14, ‘初三(8)班’, 70.00, ‘长沙市天心区芙蓉路42号’),(‘司徒静’, ‘女’, 16, ‘高二(9)班’, 87.25, ‘厦门市湖里区五缘湾43号’),(‘尉迟兰’, ‘女’, 15, ‘高一(11)班’, 82.00, ‘青岛市崂山区海尔路44号’),(‘宇文乐’, ‘男’, 12, ‘初一(6)班’, 95.50, ‘大连市甘井子区华北路45号’),(‘端木颖’, ‘女’, 14, ‘初三(9)班’, 73.75, ‘郑州市二七区大学路46号’),(‘东方旭’, ‘男’, 16, ‘高二(10)班’, 89.00, ‘合肥市包河区芜湖路47号’),(‘令狐冲’, ‘男’, 17, ‘高三(8)班’, 77.00, ‘昆明市官渡区日新路48号’),(‘独孤雪’, ‘女’, 13, ‘初二(7)班’, 94.00, ‘哈尔滨市道里区中央大街49号’),(‘慕容复’, ‘男’, 15, ‘高一(12)班’, 68.00, ‘长春市南关区人民大街50号’);

要求在学生表中完成

>=85 显示优秀

>=60显示及格

反之，不及格

SELECT    id,    name,        CASE        WHEN score >= 80 THEN ‘优秀’        WHEN score >= 60 THEN ‘及格’        ELSE ‘不及格’    END AS ‘成绩等级’FROM student;

约束

数据完整性约束：域完整性，实体完整性，参照完整性，用户定义完整性

限制数据字段的规则

保证正确，有效，完整

约束

描述

关键字

非空约束

不可以为NULL

not null

唯一约束

保证唯一不重复

unique

主键约束

一行数据的唯一标识，非空且唯一

primary key

默认

默认为某值，后面加‘ ？’

default

外键约束

建立不同表的联系

foreign key

常见约束

主键约束：primary key

唯一约束：unique

CREATE table yueshu1 ( id int primary key auto_increment comment ‘主键’, name varchar(10) not null unique comment ‘姓名’, age int CHECK (age>0 and age<=120) comment ‘年龄’, status char(1) default ‘1’ comment ‘状态’)comment ‘约束案例’;

外键约束

建立部门表：

CREATE table dept(id int auto_increment PRIMARY KEY COMMENT ‘ID’,name varchar(50) not NULL COMMENT ‘部门名字’ )COMMENT ‘部门表’;

插入数据：

INSERT INTO dept (id,name) VALUES (1,’研发部’),(2,’市场部’),(3,’财务部’),(4,’销售部’),(5,’总经办’);

建立员工表：

注意：最后一个字段无需‘，’

CREATE table emp(id int auto_increment COMMENT ‘ID’ PRIMARY KEY,name varchar(20) not null COMMENT ‘姓名’,age int comment ‘年龄’,job VARCHAR(20) COMMENT ‘职位’,salary int comment ‘薪资’,entrydate date COMMENT ‘入职时间’,managerid int COMMENT ‘领导id’,dept_id int COMMENT ‘部门id’)COMMENT ‘员工表’;INSERT INto emp VALUES(1,’金庸’,66,’总裁’,20000,’2000-01-01′,null,5),(2,’张无忌’,20,’项目经理’,10000,’2005-03-21′,1,1),(3, ‘杨道’,33, ‘开发’,8400,’2000-11-03′,2,1),(4,’韦一笑’,48, ‘开发’,11000, ‘2002-02-05′,2,1),(5,’常遇春’,43,’开发’,10500,’2004-09-07′,3,1),(6,’小昭’,19,’程序员鼓励师’,6600,’2004-10-12′,2,1);

添加外键的语法：

在建立表结构时候添加外键

CREATE TABLE 表名（[CONSTRAINT] [外键名字] FOREIGN KEY(外键字段名) REfERENCES 主表(主表列名)）;

创立完之后，添加表的外键

ALter table 表名 add constraint 外键名字 foreign key (外键段名) references 主表（主表列名）;

alter:修改表

add constraint ：添加外键约束

指定外键名 references关联 主表

ALTER TABLE emp add CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);

删除外键：

alter table 表名 drop foreign key 外键名字;

当为子表建立外外键联系之后，删除父表的时候会报错

cascade:父改子也改

ALTER TABLE emp add CONSTRAINT fk FOREIGN KEY (dept_id) REFERENCES dept(id) on update cascade on delete cascade;

修改时设为null

ALTER TABLE emp add CONSTRAINT fk FOREIGN KEY (dept_id) REFERENCES dept(id) on update sey null on delete set null;

检查约束

CHECK约束

对多个字段进行设置检查约束，指定选择范围

删除检查约束

`desc 表`先显示 表约束

alter table 表格 drop 约束 字段;

多表查询

多表关系

一对一，一对多（多对一），多对多

一对多：

部门（1）→员工（多）

在多那里建立外键约束

多对多

学生（多）→课程 （多）

建立中间表，至少包含两个外键，分别关联两张表的主键

eg:

1.先建立学生表：

CREATE TABLE student (id int auto_increment PRIMARY KEY COMMENT ‘主键id’,name VARCHAR(10) COMMENT ‘姓名’,no varchar(10) COMMENT ‘学号’)COMMENT ‘学生表’;

插入数据：

INSERT into student values (null,’李小萌’,’20001′),(null,’加德纳’,’20002′),(null,’能耗低暗卫’,’20003′),(null,’废话’,’20004′);

建立课程表

CREATE table course (id int auto_increment PRIMARY key COMMENT ‘主键id’,name varchar(10) COMMENT ‘课程名字’)COMMENT ‘课程表’;

插入课程数据：

insert into course VALUES (NULL,’数学’),(NULL,’语文’),(NULL,’英语’),(NULL,’物理’);

创立中间表

CREATE table student_course (id int auto_increment PRIMARY key COMMENT ‘主键id’,studentid int not null comment ‘学生id’,courseid int not null comment ‘课程id’,CONSTRAINT fk_courseid FOREIGN key (courseid) REFERENCES course (id),CONSTRAINT fk_studentid FOREIGN KEY (studentid) REFERENCES student (id))COMMENT ‘学生课程中间表’;

插入中间表数据

INSERT INTO student_course VALUES (NULL,1,1),(NULL,1,2),(NULL,1,3),(NULL,2,2),(NULL,2,3),(NULL,3,4);

一对一

多表查询

从多张表查询

select * from emp , dept;

默认查出来是笛卡尔积，详细参考离散数学

集合A和集合B的数据乘积即为笛卡尔积的个数，查询时要消除无效的笛卡尔积。

两个表之间

只筛选相同数据的,使用where

select * from emp , dept where emp.dept_id=dept.id;

内连接

隐式内连接：

select 字段 from 表1，2 where 条件；

显式内连接：

select 字段 from 表1 【inner】join 表2 on条件；

SELECT * from emp ,dept where emp.dept_id = dept.id;

在前面添加要查询的数据

SELECT emp.name ,dept.`name` from emp ,dept where emp.dept_id = dept.id

显示内连接：inner join

内核一样，只是语法的形式不太一样了

外连接

select * from 表1 left outer join 表2 on 条件；select * from 表1 right outer join 表2 on 条件；

左外连接：查询emp的全部数据，并且加上部门表的注释

SELECT e.`name` ,d.`Name` from emp e left outer join dept d on e.dept_id= d.id;

自连接

select * from 表A 别名a join 表A 别名B on 条件；

自连接可以是内连接查询，也可以是外连接查询，可以左外，也可以右外

第二行的领导id所属的为第一行，都在一张表里面

一张表自己连接自己

题目：查询员工 及其 领导的名字

把emp表看作两张表，取做名字a，b再查询a的领导和b的id相等的值

–查询领导名字SELECT a.name,b.name from emp a ,emp b where a.managerid = b.id;

题目：查询所有员工emp及其所有员工的emp,把没有领导的员工也要查询出来

使用外连接，左外连接

–表结构 emp a,emp b

联合查询

union,union all

将多个查询的结果合并起来，形成一个新的查询结果

合并

语法：

SELECT 字段列表 FROM 表A…UNION[ALL]SELECT 字段列表FROM 表B…

all 表示去重

题目：查询年龄大于50，工资少于5000

基础表

代码：

SELECT * from emp WHERE salary <5000SELECT * from emp WHERE age >50

3.进行union all查询

类似于把上面的两张表拼接在一起

SELECT * from emp WHERE salary <5000UNION allSELECT * from emp WHERE age >50

4.使用 umion  查询 没有all

去重效果

子查询

SELECT * from t1 WHERE columnl=(SELECT FROM columnl FROM t2);

外部的select可以是insert,delect ,update等等

根据子查询结果不同，分为:

标量子查询

题目：查询销售部全部员工信息

1.查询dept表 –销售部的id–查询出id为4

3，一条语句完成

把3替换成1语句

因为里面只查询出来一个数据，也就是3，所以也就成为子查询

题目：查询在韦一笑入职之后的人的信息

1.查询入职时间

列子查询

子查询返回的一列（可以是多行），称之为列子查询

常用的操作符:IN ， NOT IN、ANY ， SOME、 ALL

操作符

描述

IN

在指定的集合范围之内，多选-

NOT IN

不在指定的集合范围之内

ANY

子查询返回列表中，有任意一个满足即可

SOME

与ANY等同，使用SOME的地方都可以

ALL

子查询返回列表的所有值都必须满足

题目：查询销售部和市场部的全部信息

a,查询销售部和市场部门的id

行子查询

和列子查询类似

查询和张无忌的薪资和领导都是一样的人的员工信息

emp：

b.查询相同的人

1.and语句

发现效果是一样的

我们就可以实现行子查询

表子查询

子查询返回的是多行多列，主要使用in关键字

题目1：查询和张无忌，韦一笑的薪资和领导都一样的员工信息

注意，等于一般在行子查询使用

这里要换成in

SELECT * from emp where (salary,managerid)in (SELECT salary ,managerid FROM emp where name = ‘张无忌’ or name = ‘韦一笑’)

练习

1，查询员工（emp）的姓名、年龄、职位、部门信息。(隐式内连接)

表：emp,dept

连接条件：emp.dept_id = dept .id

2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息。(显式内连接)  inner join

表：emp,dept

连接条件：emp.dept_id = dept .id

dept,emp

内连接+去重 distinct

内连接查询的是两张表交集部分的

4.查询所有年龄大于40的员工，及其所属的部门名称，如果员工没有部门，也需要显示出来

使用左外连接

5.查询所有员工的工资等级。

隐式内连接

emp  ,salgrade

使得emp的工资在salgrade的最值之间,大于最小值，小于最大值

条件：emp.salary >=salgrade.losal and emp.salary<= salgrade.hisal

或者将>=换成between and

6.查询‘研发部’所有的员工信息和工资等级

表：emp,salgrade,dept

查询研发部id

select id from dept where name=”研发部”

查询所有员工

select * from emp where depy_id=（select id fromdept where name=”研发部”）

union all

查询所有的工资等级

select e.* , s.grade from emp e ,salgrade s where emp.salary >=salgrade.losal and emp.salary<= salgrade.hisal .

7.拆查询研发部平均工资

其他题目答案：

视频连接

事务

事务的介绍

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作的请求，即这些操作要么同时成功，要么同时失败。

银行转账

事务操作

1.创建账户表account

主键id,自增

姓名，余额

CREATE TABLE account(id int auto_increment PRIMARY KEY COMMENT ‘主键id’,name varchar(10) COMMENT ‘姓名’,money int COMMENT ‘余额’)COMMENT ‘账户表’;insert into account (id,name,money) VALUES (null,’张三’,2000),(null,’李四’,2000)

2.转账操作

/*–查询余额*/SELECT * from account where name = ‘张三’;/*–将张三的余额-1k*/UPDATE account set money = money-1000 where name = ‘张三’;/*–将李四的余额+1k*/UPDATE account set money = money+1000 where name = ‘李四’;

3.恢复数据

UPDATE account set money =2000 where name=”张三” or name = “李四”

4.

在第二三步之间抛出一个异常

！！！张三的钱没有了

每一条sql语句都是一条事务，默认是自动提交的

控制事务

1.查看/设置事务的提交方式

select @@autocommit;set @@autocommit = 0;

默认是1，自动提交

2.提交事务

commit

3.回滚事务

rollback

这是一个报错

回滚事务

事务操作成功→commit

失败→ rollback

方式2：

1.开启事务

START TRANSACTION 或 BEGIN;

前提：autocommit的值是默认值1

事务的四大特性

原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。

隔离性(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独 立环境下运行。

持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。