mysql-数据库

1.5数据库

• 数据库：DataBase(DB)，是数据储存和管理的堆栈。
  
• 数据管理系统：DataBase Management System（DBMS）,利用和管理数据库的大型软件。
  
• SQL：Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。
  

sql简介
单行注释：–或#
多行注释：/* 注释内容 */
分类全称阐明DDLData Definition Language数据定义语音，用来定义数据库对象（数据库，表，字段）DMLData Manipulation Language数据操作语言，用来对数据库表中数据进行增删改查DQLData Query Language数据库查询语言，用来查询数据DCLData Control Language数据控制语言，用来创建数据库用户，控制访问权限 1.5.1DDL

1. #查看数据库
2. show databases;
3. #创建数据库db01
4. create database if not exists db01;
5. #使用db01
6. use db01;
7. #删除db01
8. drop database if exists db01;
9. #创建表结构
10. create table user(
11.     id int comment 'id 唯一标识',
12.     username varchar(20) comment '用户名',
13.     age int comment '年龄',
14.     gender char(1) comment '性别'
15. )comment "用户表";

复制代码

1. create table user(
2.     #auto_increment自动增长
3.     id int primary key comment 'id 唯一标识',
4.     username varchar(20) not null unique comment '用户名',
5.     name varchar(10) not null comment '姓名',
6.     age int not null comment '年龄',
7.     gender char(1) default '男' comment '性别'
8. )comment "用户表";

复制代码

1. #查询当前数据库索引的表
2. show tables;
3. #查询表结构
4. desc 表名;
5. #查询建表语句
6. show create table 表明;
7. #添加字段qq
8. alter table emp add qq varchar(11) comment 'QQ';
9. #modify关键字修改类型
10. alter table emp modify qq varchar(13) comment 'QQ';
11. #change关键字修改字段名称
12. alter table emp change qq qq_id varchar(13) comment 'QQ';
13. #drop column关键字删除qq_id字段
14. alter table emp drop column qq_id;

复制代码

1.5.2DML

1. #创建一个复杂表
3. CREATE TABLE `emp` (
4.                        `username` varchar(20) NOT NULL COMMENT '用户名',
5.                        `password` varchar(30) DEFAULT '123456' COMMENT '密码',
6.                        `name` varchar(10) NOT NULL COMMENT '姓名',
7.                        `gender` char(1) DEFAULT '男' COMMENT '性别',
8.                        `img` varchar(300) DEFAULT NULL COMMENT '图像',
9.                        `job` tinyint unsigned DEFAULT NULL COMMENT '1,班主任。2，讲师。3,学工主管。4,教研主管,5学生',
10.                        `age` tinyint NOT NULL COMMENT '年龄',
11.                        `create_time` datetime  NULL not null COMMENT '开始时间',
12.                        `modify_time` datetime  NULL not null COMMENT '结束时间',
13.                        `qq` varchar(20) NOT NULL COMMENT 'QQ',
14.                        UNIQUE KEY `username` (`username`)
15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

复制代码

1. #给必填字段赋值，其中now传递目前时间
2. insert into emp (username, name, age, create_time, modify_time, qq) VALUE ('lumingfei','路明非',20,now(),now(),'123456789');
3. #给必填字段，批量添加
4. insert into emp (username, name, age, create_time, modify_time, qq) VALUE ('lumingfei','路明非',20,now(),now(),'123456789'),(.....),(......);

复制代码

1. #当想给全部字段赋值时可以省略主键字段
2. insert into emp value ('huiliyi','1234567','上杉绘梨衣','女','1.jpj',5,'20',now(),now(),'1234567654');
3. #批量添加
4. insert into emp value ('huiliyi','1234567','上杉绘梨衣','女','1.jpj',5,'20',now(),now(),'1234567654'),(.....),(.....);

复制代码

• 注意插入数据时注意规范
  

更新数据

1. #将唯一标识 username='lumingfei'中的name=路明非改为陈平安
2. update emp set name='陈平安',modify_time=now() where username='lumingfei';

复制代码

1. #将所有初始时间，和结束时间修改
2. update emp set create_time='2024-01-01 00:00:00',modify_time=now();

复制代码

删除数据

1. delete from emp where 条件;
2. #删除username='lumingfei'的数据，不写条件则会删除整张表
3. delete from emp  where username='lumingfei';

复制代码

1.5.3DQL

查询操作
创建表写入数据

1. -- 员工管理(带约束)
2. create table tb_emp (
3.                         id int unsigned primary key auto_increment comment 'ID',
4.                         username varchar(20) not null unique comment '用户名',
5.                         password varchar(32) default '123456' comment '密码',
6.                         name varchar(10) not null comment '姓名',
7.                         gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
8.                         image varchar(300) comment '图像',
9.                         job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
10.                         entrydate date comment '入职时间',
11.                         create_time datetime not null comment '创建时间',
12.                         update_time datetime not null comment '修改时间'
13. ) comment '员工表';
15. -- 准备测试数据
16. INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time) VALUES
17.                                                                                                                (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
18.                                                                                                                (2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
19.                                                                                                                (3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),
20.                                                                                                                (4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),
21.                                                                                                                (5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),
22.                                                                                                                (6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),
23.                                                                                                                (7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
24.                                                                                                                (8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),
25.                                                                                                                (9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),
26.                                                                                                                (10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),
27.                                                                                                                (11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),
28.                                                                                                                (12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),
29.                                                                                                                (13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),
30.                                                                                                                (14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33', '2022-10-27 16:36:01'),
31.                                                                                                                (15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),
32.                                                                                                                (16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:05'),
33.                                                                                                                (17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33', '2022-10-27 16:36:07'),
34.                                                                                                                (18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
35.                                                                                                                (19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
36.                                                                                                                (20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
37.                                                                                                                (21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),
38.                                                                                                                (22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),
39.                                                                                                                (23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
40.                                                                                                                (24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
41.                                                                                                                (25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
42.                                                                                                                (26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
43.                                                                                                                (27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
44.                                                                                                                (28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
45.                                                                                                                (29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');

复制代码

1.5.3.1根本查询

1. #查询多个字段
2. select 字段一,字段二,字段三 from 表明;
3. #查询所有字段
4. select * from 表名
5. #查询并设置别名显示
6. select 字段一 as 别名一 , 字段二 as 别名二 from 表明;
7. #去重
8. select distinct 字段列表 from 表明;

复制代码

1. select name,gender from tb_emp;

复制代码

1. select name as '姓名',gender as '性别' from tb_emp;
2. #其中as关键字可以省略中间用空格隔开就行

复制代码

1. #简化通配符写法
2. select * from tb_emp;
3. #原始写法
4. select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp;
5. #在实际生产环境中不建议使用*

复制代码

1. select distinct job from tb_emp;

复制代码

1.5.3.2条件查询

1. select 字段列表 from where 条件列表;

复制代码

常用条件

实例

1. #查询姓名为杨逍的员工
2. select * from tb_emp where name='杨逍';
4. #查询id小于5的员工信息
5. select * from tb_emp where id<5;
7. #查询没有职位的员工
8. select * from tb_emp where job is null;
10. #查询密码不为'123456'的员工信息
11. select * from tb_emp where password!='123456';
12. select * from tb_emp where password <> '123456';
14. #查询入职日期在'2001-01-01'到'2010-01-01'之间的员工信息
15. select * from tb_emp where entrydate >= '2001-01-01' and entrydate <='2010-01-01';
16. select * from tb_emp where entrydate between '2001-01-01' and '2010-01-01';
18. #查询入职日期在'2001-01-01'到'2010-01-01'之间的员工信息并且性别为女的信息
19. select * from tb_emp where entrydate between '2001-01-01' and '2010-01-01' and gender=2;
20. select * from tb_emp where entrydate between '2001-01-01' and '2010-01-01' && gender=2;
22. #查询职位为2,3,4的员工信息
23. select * from tb_emp where job=2 or job=3 or job=4;
24. select * from tb_emp where job=2 || job=3 or job=4;
25. select * from tb_emp where job in (2,3,4);
27. #查询姓名字段为两字的员工,下划线的个数表示字的个数
28. select * from tb_emp where name like '__';
30. # 查询姓张的员工信息
31. select * from tb_emp where name like '张%';

复制代码

1.5.3.3分组查询

• 聚合函数：将一列数据作为一个整体，进行纵向盘算
  

函数功能count统计数目max最大值min最小值avg均匀值sum求和

• 1. select 聚合函数 from 表明;
  3. #统计员工数量
  4. select count(id) from tb_emp;
  5. select count(*) from tb_emp;
  6. #统计最早入职的员工
  7. select min(create_time) from tb_emp;
  8. select max(create_time) from tb_emp;
  9. #统计id平均值
  10. select avg(id) from tb_emp;
  11. #求id和
  12. select sum(id) from tb_emp;
  13. #null值不参与计算

  复制代码
  分组查询
  
  1. select 字段 from 表名 where 条件 group by 字段名 having 分组后过滤提条件;

  复制代码
  实例
  
  1. #根据性别分组统计男性和女性的数量，注意条件要写俩一是分组字段和聚和函数
  2. select gender,count(*) from tb_emp group by gender;
  3. #查询入职时间在'2015-01-01' 包含（以前的员工），并对结果进行分组，获取员工数大于2的职位
  4. select job,count(*) from tb_emp where (entrydate<='2013-01-01') group by job having count(*)>=2;

  复制代码

排序查询

1. select 字段列表 from 表明 where 条件列表 group by 分组字段 order by 字段一 排序方式一,字段二 排序方式二....;
2. #其中 ASC:升序，DESC:降序

复制代码

实例

1. #根据入职时间，对员工进行生序排序-ASC,默认升序，不写asc也行
2. select * from tb_emp order by entrydate asc ;
3. select * from tb_emp order by entrydate desc ;
5. #根据入职时间对公司员工进行升序，入职时间相同根据更新时间进行降序
6. select * from tb_emp order by entrydate ,update_time desc ;

复制代码

• 假如第一个字段雷同时才会进行第二个字段排序
  

分页查询

1. #起始索引从几开始，查询记录数这一页记录多少数据
2. select 字段列表 from 表明 limit 起始索引，查询记录数;

复制代码

实例

1. #从起始索引0开始查询，每页展示5条数据
2. select * from tb_emp limit 0,5;
3. #查询第一页员工数据，每页展示5条记录
4. select * from tb_emp limit 0,5;
5. #查询第二页员工数据，每页展示5条记录
6. select * from tb_emp limit 5,5;
7. #查询第三页员工数据，每页展示5条记录
8. select * from tb_emp limit 10,5;
9. #姓张性别男入职日期在'2000-01-01'到'2015-12-31'之间按最后修改时间降序，分页进行查询
10. select * from tb_emp where name like '张%' and gender=1 and entrydate between '2000-01-01' and '2015-12-31' order by update_time desc limit 0,10;

复制代码

1.5.3.4数据统计图形报表

• if流程控制（条件，符合条件输出，否则输出）
  

1. #员工性别统计
2. select if(gender=1,'男','女') 性别,count(*) 个数 from tb_emp group by gender;

复制代码

• case流程控制函数
  

1. #通过工作将1返回班主任，2返回讲师。。。
2. select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主任' else '未分配' end ) 职位,count(*)
3. from tb_emp group by job;

复制代码

1.5.4多表设计

• 一对多（多对一）
  

一个部门表可以对应多个员工表，

1. #创建部门表
2. create table tb_dept(
3.     id int unsigned primary key auto_increment comment 'ID',
4.     name varchar(10) not null unique comment '部门名称',
5.     create_time datetime not null comment '创建时间',
6.     update_time datetime not null comment '修改时间'
7. ) comment '部门表';
8. #创建员工表
9. create table tb_emp (
10.                         id int unsigned primary key auto_increment comment 'ID',
11.                         username varchar(20) not null unique comment '用户名',
12.                         password varchar(32) default '123456' comment '密码',
13.                         name varchar(10) not null comment '姓名',
14.                         gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
15.                         image varchar(300) comment '图像',
16.                         job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
17.                         entrydate date comment '入职时间',
18.                         dept_id int unsigned comment '创建时间',
19.                         create_time datetime not null comment '创建时间',
20.                         update_time datetime not null comment '修改时间'
21. ) comment '员工表';

复制代码

1. insert into tb_dept (id, name, create_time, update_time) values
2.         (1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()),
3.         (4,'就业部',now(),now()),(5,'人事部',now(),now());
5. INSERT INTO tb_emp
6.         (id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
7.         (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
8.         (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
9.         (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
10.         (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
11.         (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
12.         (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
13.         (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
14.         (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
15.         (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
16.         (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
17.         (11,'luzhangke','123456','鹿杖客',1,'11.jpg',1,'2007-02-01',1,now(),now()),
18.         (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',1,'2008-08-18',1,now(),now()),
19.         (13,'fangdongbai','123456','方东白',1,'13.jpg',2,'2012-11-01',2,now(),now()),
20.         (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
21.         (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
22.         (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',2,now(),now()),
23.         (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());

复制代码

外键约束
创建表时

1. #物理外键
2. create table 表明(
3.        字段名 数据类型,
4.        ...
5.        constraint 外键名 foreign key(外键字段名) references 主表(字段名)
6. );
8. #添加外键
9. alter table 表明 add constraint 外键名 foreign key (外键字段名) references 主表(字段名);

复制代码

• 物理外键
  
  
  
  • 概念：利用foreign key定义关联另一张表
    
  • 缺点：
    
    
    
    • 影响增删改服从
      
    • 仅实用单节点数据库，不实用分布式、集群场景。
      
    
    
  
  
• 逻辑外键
  
  
  
  • 概念：在业务层逻辑中，解决外键关联
    
  • 通过逻辑外键，就可以很方便解决上述问题
    
  
  
• 一对一
  用户和身份证就是一对一关系
  
  1. #数据准备
  2. create table tb_user(
  3.     id int unsigned  primary key auto_increment comment 'ID',
  4.     name varchar(10) not null comment '姓名',
  5.     gender tinyint unsigned not null comment '性别, 1 男  2 女',
  6.     phone char(11) comment '手机号',
  7.     degree varchar(10) comment '学历'
  8. ) comment '用户信息表';
  10. insert into tb_user values (1,'白眉鹰王',1,'18812340001','初中'),
  11.                         (2,'青翼蝠王',1,'18812340002','大专'),
  12.                         (3,'金毛狮王',1,'18812340003','初中'),
  13.                         (4,'紫衫龙王',2,'18812340004','硕士');
  15. #创建外键user_id关联主键id
  16. create table tb_user_card(
  17.     id int unsigned  primary key auto_increment comment 'ID',
  18.     nationality varchar(10) not null comment '民族',
  19.     birthday date not null comment '生日',
  20.     idcard char(18) not null comment '身份证号',
  21.     issued varchar(20) not null comment '签发机关',
  22.     expire_begin date not null comment '有效期限-开始',
  23.     expire_end date comment '有效期限-结束',
  24.     user_id int unsigned not null unique comment '用户ID',
  25.     constraint fk_user_id foreign key (user_id) references tb_user(id)
  26. ) comment '用户信息表';
  28. insert into tb_user_card values (1,'汉','1960-11-06','100000100000100001','朝阳区公安局','2000-06-10',null,1),
  29.         (2,'汉','1971-11-06','100000100000100002','静安区公安局','2005-06-10','2025-06-10',2),
  30.         (3,'汉','1963-11-06','100000100000100003','昌平区公安局','2006-06-10',null,3),
  31.         (4,'回','1980-11-06','100000100000100004','海淀区公安局','2008-06-10','2028-06-10',4);

  复制代码
• 多对多
  
  
  
  • 学生与课程之间关系
    
  
  

创建一张中间表，中间表包含两个逐渐分别关联两方主键

1. #创建学生信息表并插入数据
2. create table tb_student(
3.                            id int auto_increment primary key comment '主键ID',
4.                            name varchar(10) comment '姓名',
5.                            no varchar(10) comment '学号'
6. ) comment '学生表';
7. insert into tb_student(name, no) values ('黛绮丝', '2000100101'),('谢逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104');
9. #创建课程表并插入数据
10. create table tb_course(
11.                           id int auto_increment primary key comment '主键ID',
12.                           name varchar(10) comment '课程名称'
13. ) comment '课程表';
14. insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') , ('Hadoop');
16. #创建中间表并插入数据
17. create table tb_student_course(
18.                                   id int auto_increment comment '主键' primary key,
19.                                   student_id int not null comment '学生ID',
20.                                   course_id  int not null comment '课程ID',
21.                                   constraint fk_courseid foreign key (course_id) references tb_course (id),
22.                                   constraint fk_studentid foreign key (student_id) references tb_student (id)
23. )comment '学生课程中间表';
25. insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);

复制代码

设计分类管理、菜品管理、套餐管理

创建以上表此中category为分类表，dish为菜品表，

1.5.4.1多表查询

1. select * from 表A,表B;
2. #当对A和B进行多表查询的时候，二者会进行一一匹配查询到的结果条数是二者条数乘积，这一结果被称为笛卡尔积

复制代码

1. #可以通过条件筛选来匹配我们需要的笛卡尔积
2. select * from tb_dept,tb_emp where tb_emp.job=tb_dept.id;

复制代码

• 多表查询
  
  
  
  • 内毗连：相称于查询A,B交集的部分
    
  • 外毗连
    
    
    
    • 左外毗连：查询左表所有数据（包括两张表交集部分数据）
      
    • 右外毗连：查询右表所有数据（包括两张表交集部分数据）
      
    
    
  • 子查询
    
  
  

​
内毗连

• 隐式内毗连：
  
  
  
  • 1. select 字段列表 from 表一，表二 where 条件...;
    2. #要想指定需要返回的字段可以用【表明.字段】的方式指定

    复制代码
  
  
• 显式内毗连：
  
  
  
  • 1. select 字段列表 from 表1 inner join 表二 on 连接条件...;

    复制代码
  
  

1. select tb_dept.name,tb_emp.name from tb_dept,tb_emp where tb_emp.job=tb_dept.id;

复制代码

1. select tb_dept.name,tb_emp.name from tb_dept inner join tb_emp on tb_emp.job=tb_dept.id;

复制代码

外毗连

• 左外毗连：
  
  
  
  • 1. select 字段 from 表1 left outer join 表2 on 连接条件;

    复制代码
  
  
• 右外毗连
  
  
  
  • 1. select 字段 from 表1 right outer join 表2 on 连接条件;

    复制代码
  
  

1. #tb_dept a相当于将tb_dept重命名为a,之后用到的tb_dept都将替换成a
2. select a.name,b.name from tb_dept a left join tb_emp b on b.job=a.id;

复制代码

1. select tb_dept.name,tb_emp.name from tb_dept right join tb_emp on tb_emp.job=tb_dept.id;

复制代码

有一个小细节 rows一个是16一个是17，是因为二者毗连的方式有所不同所导致，那毗连就完全包含谁的数据
子查询

• sql语句中嵌套select语句，称为嵌套查询，又称子查询
  
• 形式：
  
  
  
  • 1. select * from t1 where column1=(select column1 from t2 ...);

    复制代码
    查询到所有entrydate大于姓名是金庸的entrydate所有的数据
    
  
  

1. select * from tb_emp where entrydate>(select entrydate from tb_emp where name='金庸');

复制代码

子查询

• 标量子查询：返回的效果是单个值（数字，字符串，日期），最简朴的形式，常用操作符：= > <等
  
• 列子查询：子查询效果是一列，常用操作符in 、not in
  

1. select id from tb_dept where id>2;

复制代码

1. select * from tb_emp where job in (select id from tb_dept where id>2);

复制代码

1. #查询所有的entrydate和job等于'2013-09-05'和1的数据
2. select * from tb_emp where (entrydate,job)=('2013-09-05',1);
3. #用于子查询
4. select * from tb_emp where (entrydate,job)=(select entrydate,job from tb_emp where name='小昭');

复制代码

1. #查询价格低于10元的菜品名称、价格、极其菜品分类的名称
2. select dish.name from dish,category where dish.id=category.id and price<10;
4. #查询价格在十到五十之间且状态为起售的菜品展示菜名分类名价格，即使菜品没有分类也要显示类名
5. select d.name,d.price,c.name from dish d left join category c on c.id=d.category_id where d.price between 10 and 50 and d.status=1;
7. #查询每个分类下最贵的菜名，展示出分类的名称和最贵的价格
8. select c.name,max(d.price) from dish d,category c where d.category_id=c.id group by c.name;
10. #查询各个分类下菜品状态为起售，并且该分类下菜品总数大于等于3的菜品分类
11. select c.name,count(*)<=3 from dish d,category c where d.category_id=c.id and c.status=1 group by c.name;
13. #查询出商务套餐A中包含哪些菜品（展示出套餐名称、价格、包含的菜品名称、价格、分数）
14. select s.name,s.price,d.name from setmeal s,dish d,setmeal_dish sd where s.id=sd.setmeal_id and d.id=sd.dish_id  and s.name='商务套餐A';

复制代码

1.5.4.2事务

• 概述：事务是一组操作的集合，是不可分割的工作单位。会把操作作为一个整体向系统提交或撤销操作哀求，使这些操做要么同时乐成要么同时失败。
  
• 操作
  
  
  
  • 开启事务：start transaction；/ begin；
    
  • 提交事务：commit；
    
  • 回滚事务：rollback；（操作失误把所有数据恢复到操作之前）
    
  
  

1. #开启事务
2. start transaction;
3. #执行操作
4. delete from tb_emp where id=2;
5. delete from tb_dept where id=2;
6. #提交任务
7. commit ;
8. #回滚事务
9. rollback ;

复制代码

事务的四大特性

• 原子性：事物是不可分割的最小单元
  
• 同等性：数据完成时，必须所有数据都保持同等状态
  
• 隔离性：数据库系统提供隔离机制，包管事务在不受外部并发操作影响的独立环境运行
  
• 长期性：事务一旦提交或回滚，他对数据库中的改变是永世的
  

1.5.4.3索引

简介：索引是帮助数据库高效获取数据的数据布局。
优点：提高数据库查询服从，低落数据库IO本钱，通过索引列对数据进行排序，低落数据排序本钱，低落cpu斲丧。
缺点：索引会占用储存空间。索引大大提高了查询服从，同时却也低落了insert,update,delete的服从。
布局
mysql数据库支持的索引布局有许多，如Hash索引、B+Tree索引，Full-Text索引等。我们平常所说的索引，假如没有特别指明，都是默认的B+TRee布局组织索引。

• B+Tree(多路平衡搜索树)
  
  
  
  • 每个非叶子节点都有n个k,和n个指针，但不报存数据，数据只在叶子结点保存
    
    
    
    
  
  
• 语法
  
  
  
  • 创建索引
    
    1. create unique index 索引名 on 表明 (字段名...);

    复制代码
  • 查看索引
    
    1. show index from 表明;

    复制代码
  • 删除索引
    
    1. drop index 索引名 on 表明;

    复制代码
  
  

1. #创建索引
2. create unique index  n on tb_emp(name);

复制代码

1. #查看索引
2. show index from tb_emp;

复制代码

1. #删除索引
2. drop index n on tb_emp;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。