MySQL学习笔记

数据库应用场景

咨询类型网站:存新闻,存评论,存用户信息
电商类:商品,订单,用户信息
社交类:微博,用户信息,用户微博信息,评论信息

课程介绍:

IO流文件存储的弊端

1. 效率低(开发效率低,执行效率也低)
2. 数据的增删改查非常麻烦
3. 只能保存小量数据
4. 只能存储文本数据

什么是DB

DataBase:数据库,数据库实际上就是一个文件集合,本质就是一个文件系统,数据按照特定的格式存储到文件中,使用sql语言对数据进行增删改查操作.

什么是DBMS

DataBaseManagementSystem:数据库管理系统,管理数据库文件的软件

• 指一种操作和管理数据库的大型软件,用于建立,使用和维护数据库,对数据进行统一的管理和控制,用户通过DBMS访问数据库中的数据
  常见:mysql oracle db2 sqlserver sqlite …
  ##数据库的分类

1. 关系型数据库:经过数学理论验证,可以将现实生活中存在的各种关系,保存到数据库中,这种数据库称为关系型数据库,在此数据库中,以表的形式保存数据之间的关系
2. 非关系型数据库: 主要为了解决特定的应用场景,如:缓存,高并发访问等,存储数据的方式有多种,redis是常见的非关系型数据库,redis是以键值对的形式保存数据.

常见数据库介绍

1. mysql: oracle公司,08年被sun收购 09 sun被oracle收购 ,开源,市场占有最高
   被oracle收购后出了5.5版本由于使用了oracle核心技术,性能有了大幅提高,导致oracle用户流失,公司决定将mysql闭源,导致原mysql程序员离开并创建了mariaDB,maria创始人的女儿名字
2. oracle: oracle公司,性能最强大的数据库,而且收费最贵,通常不缺钱却技术的公司会选用,市场排名第二
3. DB2: IBM公司,闭源收费,通常一些银行项目会使用
4. sqlserver: 微软公司,排名第三,闭源收费,提供整套解决方案(web服务器,操作系统,数据库服务器等)
5. sqlite:轻量级数据库,应用在嵌入式或移动设备中,大小只有几十k,功能和性能较大型数据库要少很多

开源和闭源

• 开源: 公开源代码,免费, 大牛程序员会维护和升级
  弊端:通过卖服务赚钱,来钱比较慢
• 闭源: 不公开源代码,收费(卖产品)
  弊端:大牛程序员会攻击找漏洞

mysql安装

• 参见mysql安装文档,注意两点:

1. 端口号选择默认的3306
2. 字符集选择utf-8

什么是数据库服务器

服务器:一台高配置电脑
ftp服务器:安装了ftp服务软件提供了ftp服务功能的高配置电脑
邮件服务器:安装了邮件服务的软件 供了邮件服务功能的高配置电脑
数据库服务器:安装了数据库服务的软件 供了数据服务功能的高配置电脑
web服务器:安装了web服务软件的高配置电脑

连接数据库

• 打开终端或命令行 在终端中输入以下命令:
  mysql -uroot -p然后敲回车,然后再敲回车
• 退出指令: exit;

什么sql

Stuctured Query Language: 结构化查询语言,使用sql语言和数据库服务器进行交互,通过sql告诉数据库服务器对数据进行什么操作.

sql规范

1. 以;(分号)结尾
2. 关键字之间有空格,通常只有一个,但多个也可以
3. 可以存在换行
4. 数据库名称和表名称区分大小写

数据库相关的SQL

• 每一个工程对应一个数据库,存储数据需要先创建一个数据库,然后再数据库中创建表

1. 查看所有数据库
   show databases;
2. 创建数据库
   -格式:create database 数据库名称;
   create database db1;
   -指定字符集: create database 数据库名称 character set gbk;
   create database db2 character set gbk;
3. 查看指定数据库详情
   -格式:show create database 数据库名称;
   show create database db1;
4. 删除数据库
   -格式:drop database 数据库名称;
   drop database db2;
5. 使用数据库
   -格式: use 数据库名称;
   use db1;

练习: 创建 mydb1-5(第三个字符集为gbk) 然后查看第三个,删除2-5,使用1

和表相关的sql

• 什么是表: 表是关系型数据库存储数据的单位,数据库中存储数据需要先创建表,往表中存储数据.
• 执行表相关的sql时一定要先选择数据库

1. 创建表
   -格式: create table 表名(字段1名 字段1类型, 字段2名 字段2类型,…);
   create table stu(id int,name varchar(10),age int, chinese int, math int,english int);

创建表sql语句的执行过程: 在终端中写完sql语句后敲回车终端会把sql通过网络传输到DBMS(mysql),DBMS对sql语句进行解析,然后对数据库中的数据进行操作

练习:创建英雄表hero 字段(id name type sal)
create table hero(id int,name varchar(10),type varchar(10),sal int);
2. 查看所有表
show tables;
3. 查看指定表详情 和 表的字段信息
-格式:show create table 表名;
-格式:desc 表名;
4. 创建表指定引擎和字符集
create table t1(id int,name varchar(10)) engine=myisam charset=gbk;
5. 删除表
drop table t1;

练习:

1. 创建员工表(emp) 字段有员工编号(empno),员工姓名(ename),员工工资(sal)
   create table emp(empno int, ename varchar(10),sal int);
2. 创建数据库newdb 在newdb中 创建 商品表(item) 字段有商品标题(title),价格(price),库存(num)
   create database newdb;
   use newdb;
   create table item(title varchar(10),price int,num int);
3. 查看员工表详情和字段信息
   use db1;
   show create table emp;
   desc emp;
4. 删除商品表
   use newdb;
   drop table item;

表的引擎

1. innodb:支持数据库的高级操作,包括:事务 外键等
2. myisam:仅支持数据的增删改查操作

表的修改

use db1;
create table person(id int,name varchar(10));

1. 修改表的名称
   -格式: rename table 原名 to 新名;
   rename table person to t_person;
2. 修改表的引擎和字符集
   -格式: alter table 表名 engine=myisam charset=gbk;
   alter table t_person engine=myisam charset=gbk;
   show create table t_person;
3. 添加表的字段
   -最后面格式: alter table 表名 add 字段名 字段类型;
   alter table t_person add age int;
   desc t_person;
   -最前面格式: alter table 表名 add 字段名 字段类型 first;
   alter table t_person add chinese int first;
   -某个字段的后面格式: alter table 表名 add 字段名 字段类型 after 字段名;
   alter table t_person add math int after id;
4. 删除字段
   -格式:alter table 表名 drop 字段名;
   alter table t_person drop chinese;
5. 修改字段名称和类型
   -格式:alter table 表名 change 原字段名 新字段名 字段类型
   alter table t_person change age myage int;
6. 修改字段的类型和位置
   -格式:alter table 表名 modify 字段名 字段类型 first/after xxx;
   alter table t_person modify myage int after id;

create table t1(id int,name varchar(10));
show tables;
show create table t1;
desc t1;
drop table t1;
rename table t1 to t2;
alter table t1 engine=myisam charset=gbk;
alter table t1 add age int first/after xxx;
alter table t1 drop age;
alter table t1 change age myage int;
alter table t1 modify age int first/after xxx;

练习:

1. 创建t_hero表 字段 id name 引擎myisam
   create table t_hero(id int,name varchar(10)) engine=myisam;
2. 修改表名为t_h
   rename table t_hero to t_h;
3. 修改表的引擎为innodb
   alter table t_h engine=innodb;
4. 添加age字段在id的后面
   alter table t_h add age int after id;
5. 删除id字段
   alter table t_h drop id;

数据相关的SQL

create table t_stu(id int,name varchar(10),age int);

1. 插入数据
   -全表插入:要求插入的数据的数量和顺序要和表字段的数量顺序一致,格式:insert into 表名 values(值1,值2,值3…);
   insert into t_stu values(1,‘zhangsan’,23);
   -指定字段插入格式:insert into 表名 (字段1,字段2…) values(值1,值2…);
   insert into t_stu (id,name) values(2,‘lisi’);
   -批量插入:
   insert into t_stu values(3,‘悟空’,23),(4,‘八戒’,20),(5,‘沙僧’,18);
   insert into t_stu (id,name) values(6,‘刘备’),(7,‘关羽’),(8,‘貂蝉’);

2. 查询数据
   select * from t_stu;
   select name,age from t_stu;

3. 删除数据
   delete from t_stu where name=‘八戒’;
   delete from t_stu where age is null;

4. 修改数据
   update t_stu set name=‘张三’ where id=1;
   update t_stu set name=‘卷帘大将’,age=200 where id=5;

windows电脑出现命令行中无法插入中文数据的解决方案

在命令行中先登录mysql 然后执行 set names gbk; 通知mysql数据库服务器,客户端(命令行)的编码格式为gbk

create table t1(id int,age int);

作业:

1. 创建 homeworkdb 数据库 在数据库中创建hero表,字段id name age
2. 修改表名为t_hero
3. 表中添加money字段
4. 添加以下几条数据:
   1,诸葛亮,28,18888
   2,孙悟空,500,2888
   3,刘备,35,6888
   4,关羽,32,18888
5. 修改所有18888价格为28888
6. 修改年龄在33岁以下的价格为6666
7. 删除价格为6666
8. 修改孙悟空为猪八戒
9. 删除所有数据
10. 删除表
11. 删除数据库

数据库相关

create database db1 character set utf8;
show databases;
show create database db1;
drop database db1;
use db1;
##表相关
create table t1 (id int,name varchar(10));
show tables;
show create table t1;
desc t1;
drop table t1;
rename table t1 to t2;
alter table t1 engine=myisam charset=gbk;
alter table t1 add age int first/after xxx;
alter table t1 drop age;
alter table t1 change age myage int;
alter table t1 modify myage int first/after xxx;

数据相关

insert into t1 (id,name,age) values(a,b,c),(a2,b2,c2);
select id,name from t1;
update t1 set a=1,b=2 where id=1;
delete from t1 where id=1;

====================================================
(二).

课程回顾

数据库相关

1. 创建数据库
   create database db1 character set utf8;
2. 查询所有数据库
   show databases;
3. 查看指定数据库详情
   show create database db1;
4. 删除数据库
   drop database db1;
5. 使用数据库
   use db1;

表相关

1. 创建表
   create table t1(id int,name varchar(10));
2. 查询所有表
   show tables;
3. 查看单个表
   show create table t1;
   desc t1;
4. 删除表
   drop table t1;
5. 修改表名称
   rename table t1 to t2;
6. 修改表引擎和字符集
   alter table t1 engine=myisam/innodb charset=gbk/utf8
7. 添加字段
   alter table t1 add age int;
8. 删除字段
   alter table t1 drop age;
9. 修改字段名和类型
   alter table t1 change age myAge int;
10. 修改类型和位置
    alter table t1 modify age int first/after xxx;

数据相关

1. 插入数据
   insert into t1 (a,b,c) values(1,2,3),(1,2,3);
2. 查询数据
   select * from t1;
   select name,age from t1;
3. 修改数据
   update t1 set age=20 where id=1;
4. 删除数据
   delete from t1 where id=1;

练习:

1. 创建db2数据库 如果之前存在先删除
   create database db2;
2. 在db2中创建员工表(emp) 字段:员工编号(empno),员工姓名(ename)
   use db2;
   create table emp(empno int, ename varchar(10));
3. 修改表名为t_emp
   rename table emp to t_emp;
4. 在表中添加工资字段(sal)
   alter table t_emp add sal int;
5. 在员工姓名的前面添加年龄字段(age)
   alter table t_emp add age int after empno;
6. 在员工姓名后面添加部门字段(dept varchar(10))
   alter table t_emp add dept varchar(10) after ename;
7. 添加刘关张和取经的四个人 共7个人,年龄随意,工资5000以内随意,刘关张为三国部 取经师徒四个人为取经部
   insert into t_emp values
   (1,20,‘刘备’,‘三国部’,2000),
   (2,21,‘关羽’,‘三国部’,3000),
   (3,22,‘张飞’,‘三国部’,4000),
   (4,23,‘唐僧’,‘取经部’,1000),
   (5,24,‘悟空’,‘取经部’,2000),
   (6,25,‘八戒’,‘取经部’,5000),
   (7,26,‘沙僧’,‘取经部’,4000);
8. 把取经部的年龄改成统一的1000岁
   update t_emp set age=1000 where dept=‘取经部’;
9. 删除工资低于2000的
   delete from t_emp where sal<2000;
10. 查询三国部的所有人的名字
    select ename from t_emp where dept=‘三国部’;

在eclipse中执行sql步骤:

1. 下载mysql-connector-java-5.1.6.jar 文件 记住下载的位置
2. 在eclipse->window->show View->Other ->Data Management里面选择 第一个和第三个
3. 在DataManagementExplorer里面第一个文件夹上面右键new 在弹出窗口中选择 mysql,点击下拉选旁边的添加按钮
4. 在弹出窗口中选择5.1 第二个选项卡先clearall 然后添加下载的文件 最后ok
5. 修改url 把最后的database改成db2 有密码的添加密码没有直接 点击test connection 显示ping successed 点击finish
6. 在新建的文件上面右键 open sql scrapbook

使用eclipse执行sql乱码问题

在建立连接的时候修改url,在数据库名称的后面添加如下代码: ?useUnicode=true&characterEncoding=UTF-8
例如:
jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=UTF-8

主键约束

• 什么是约束:就是创建表的时候给表字段添加的限制条件
• 主键约束的特点: 唯一并且非空,一个表中只有一个主键
• 如何使用:
  create table t1(id int primary key,
  name varchar(10));
  -以下两行会报错,因为id重复了
  insert into t1 values(1,‘小明’);
  insert into t1 values(1,‘小红’);
  -以下会报错 因为主键不能为null
  insert into t1 (name) values(‘小花’);

主键+自增 primary key auto_increment

-如何使用:
	create table t2(id int primary key auto_increment,age int);

1. 当自增字段的值为null时会自动赋值并自增
2. 以表中曾出现的最大值+1
3. 删除数据自增数值不减
4. delete删除所有数据 自增值在原来基础上继续+1

注释 comment

• 在创建表的时候可以通过comment对字段进行描述
  create table t3(id int primary key auto_increment comment ‘这是主键id’,comm int comment ‘这是奖金’);
• 如何查看注释
  show create table t3;

`和’的区别

• 是在创建表时 修饰表名和字段的名的 可以省略 create tablet4(id int,age` int);
• '是用来表示字符串的

数据冗余

• 如果数据库设计不合理,保存大量数据后会出现大量的重复数据,这种现象称为数据的冗余 ,通过拆分表格的形式,把可能大量重复的数据,用单独一张表保存,在原表中只需要通过id建立关系即可.

练习:

1. 创建商品表(item) 商品id 商品名称 商品价格 分类id 库存
   create table item(id int primary key auto_increment,name varchar(10),price int,categoryid int,num int);
2. 创建分类表(category) 分类id 分类的名称 上级分类
   create table category(id int primary key auto_increment,name varchar(10),parentid int);
3. 表中插入 电器分类下电视机分类下的康佳电视价格3580,库存25.
   • 分类表插入以下数据
     insert into category values(null,‘电器’,null);
     insert into category values(null,‘电视机’,1);
   • 商品表插入以下数据
     insert into item values(null,‘康佳电视’,3580,2,25);

练习:设计表保存以下数据: 保存教学部下java教研部的老师信息苍老师 工资200 年龄18岁, 然后再保存集团总部下销售部,销售A部的员工李然老师,工资50,年龄28

1. 创建部门表
   create table dept(id int primary key auto_increment,name varchar(10),parentid int);
   insert into dept values(null,‘集团总部’,null);
   insert into dept values(null,‘教学部’,1);
   insert into dept values(null,‘java教研部’,2);
   insert into dept values(null,‘销售部’,1);
   insert into dept values(null,‘销售A部’,4);
2. 创建员工表
   create table emp(id int primary key auto_increment,name varchar(10),sal int,age int,deptid int);
   insert into emp values(null,‘苍老师’,200,18,3),(null,‘李然老师’,50,28,5);

事务

	create table person(id int,name varchar(10),money int);
	insert into person values(1,'超人',500),(2,'钢铁侠',1000);
-超人和钢铁侠借300块钱  
1. 超人+300
	update person set money=800 where id=1;
2. 钢铁侠-300
	update person set money=700 where id=2;

• 什么是事务: 事务是数据库中执行sql语句的最小工作单元,在同一个事务中的sql语句要么同时成功,要么同时失败.
• mysql数据库默认sql语句是自动提交的
• 关闭数据库的自动提交
  -查看自动提交的状态:
  show variables like ‘%autocommit%’;
  -关闭自动提交 如果需要打开自动提交把0换成1
  set autocommit=0;
  -验证转账流程:
  update person set money=800 where id=1;
  -此时打开一个新的终端,在新终端中查看是否修改(没改)
  -回到原窗口执行下面sql commit之后再去新窗口验证(改了)
  update person set money=700 where id=2;
  commit;
  • 回滚 rollback
    执行rollback会回滚到上次提交的点或者关闭自动提交时的点
  • 保存回滚点 savepoint s1(标识);
    update person set money=100 where id=1;
    savepoint s1;
    update person set money=200 where id=1;
    rollback to s1;
• 回顾:执行sql的最小工作单元 同时成功,同时失败
  show variables like ‘%autocommit%’;
  关闭或打开
  set autocommit=0/1;
  提交
  commit
  回滚 rollback
  保存回滚点 savepoint s1
  回滚到某个回滚点 rollback to s1

SQL分类

DDL Data Definition Language 数据定义语言

• 包括: create drop alter truncate
• 不支持事务

DML Data Manipulation Language 数据操作语言

• 包括: insert update delete select()
• 支持事务

DQL Data Query Language 数据查询语言

• 只有select
• 也属于DML

TCL Transaction Control Language 事务控制语言

• 包括:commit rollback savepoint rollback to

DCL Data Control Language 数据控制语言

• 分配用户权限的相关sql

DDL 数据定义语言: create drop alter truncate 不支持事务
DML 数据操作语言:insert update delete select 支持事务
DQL 数据查询语言:Select
TCL 事务控制语言: commit rollback 等
DCL 数据控制语言:分配用户权限

truncate

-格式:truncate table 表名;

• 作用:删除表并创建一张空表,auto_increment数值清零

数据库数据类型

整型

• 常用:int(m) bigint(m) m代表显示长度,如果字段数值长度不到m时 会在数值的前面补零,但是一定要和 zerofill结合使用
  例如: create table t_int(num int(10) zerofill);
  insert into t_int values(15);
  select * from t_int;

浮点数

• 常用:double(m,d) m代表总长度,d代表小数长度 如:76.232 m=5 d=3;
• decimal(m,d) 超高精度小数,需要涉及高精度运算时使用decimal

字符串

• char(m): 长度不可变 m=20 abc 20 执行效率高 最大值255
• varchar(m):长度可变 m=20 abc 3 节省资源 最大值:65535但是超过255建议使用text
  text: 可变长度,最大65535

日期类型

• date: 只能保存年月日
• time: 只能保存时分秒
• datatime:年月日时分秒 9999-12-31 默认值为null
• timestamp:年月日时分秒 2038-01-19 默认值为当前时间
• 练习:创建时间的表
  create table t_date(d1 date,d2 time,d3 datetime,d4 timestamp);
  insert into t_date values(‘2018-03-22’,null,null,null);
  insert into t_date values(‘2018-03-22’,‘12:22:38’,‘2008-08-08 18:08:18’,null);

回顾:

1. 主键 自增
   primary key auto_increment
2. 注释
   comment
3. `和’
4. 数据冗余
5. 事务:执行sql语句的最小单元
   show variables like ‘%autocommit%’;
   set autocommit=0/1;
   rollback
   commit
   savepoint s1;
   rollback to s1;
6. 数据库sql分类
   • DDL 数据定义语言 不支持事务 create drop alter truncate
   • DML 数据操作语言 支持事务 insert update delete select
   • DQL 数据查询语言 select
   • TCL 事务控制语言
   • DCL 数据控制语言 分配用户权限
7. 数据类型
   -整型 int bigint(m) zerofill
   -浮点型 double(m,d) decimal(m,d)
   -字符串 char(m)不可变 255 varchar可变 65535 text 可变 65535 longtext
   -日期
   date 年月日
   time 时分秒
   datetime null 9999-12-31
   timestamp 当前值 2038-01-19

==============================================================
(三).

课程回顾

day01

数据相关

1. 创建数据库
   create database db1 character set utf8;
2. 查看所有
   show databases;
3. 查看指定详情
   show create database db1;
4. 删除
   drop database db1;
5. 使用
   use db1;

表相关

1. 创建表
   create table t1(id int,name varchar(10));
2. 查看所有
   show tables;
3. 查看指定详情
   show create table t1;
   desc t1;
4. 删除表
   drop table t1;
5. 改表名
   rename table t1 to t2;
6. 修改表引擎字符集
   alter table t1 engine=myisam/innodb charset=gbk;
7. 添加字段
   alter table t1 add age int;
8. 删除字段
   alter table t1 drop age;
9. 修改字段名和类型
   alter table t1 change age myAge int;
10. 修改类型和位置
    alter table t1 modify age int first/after xxx;

数据相关

1. 插入数据
   insert into t1 (a,b,c) values(1,2,3);
2. 查询数据
   select * from t1;
3. 修改数据
   update t1 set name=‘aaa’ where id=1;
4. 删除数据
   delete from t1 where id=5;

day02

1. 主键+自增
   primary key auto_increment
2. 注释
   comment ‘这是注释’
3. `和’
4. 冗余
5. 事务:执行sql语句的最小工作单元 同时成功或同时失败
   show variables like ‘%autocommit%’;
   set autocommit=0/1;
   commit;
   rollback;
   savepoint s1;
   rollback to s1;
6. sql分类
   -DDL:数据定义语言 create alter drop truncate,不支持事务
   -DML:数据操作语言 select insert update delete,支持事务
   -DQL:数据查询语言 select
   -TCL:事务控制语言 commit;rollback;savepoint s1; rollback to s1;
   -DCL:数据控制语言 分配用户权限
7. 数据类型
   -整数: int(m) bigint(m) 结合zerofill使用
   -浮点数: double(m,d) m总长度 d是小数长度 decimal(m,d)
   -字符串: char(m) 固定长度 最大255 varchar可变长度 最大65535 text 可变长度 最大65535
   -日期:date time datetime:9999-12-31 null timestamp:2038-01-19 当前时间

Day03

1. 创建四张表 导入一堆数据

   验证四张表是否都有数据
   select * from emp;
   select * from dept;
   select * from t_item;
   select * from t_item_category;

没有条件的查询

select * from 表名;
select 字段1,字段2... 表名;

带条件查询

列值为null 和 不为null

1. 查询没有上级领导的员工编号,姓名,工资
   select empno,ename,sal from emp where mgr is null;
2. 查询有上级领导的员工编号,姓名,领导编号
   select empno,ename,mgr from emp where mgr is not null;
   -练习: 查询没有奖金(comm)的员工信息
   select * from emp where comm is null;

别名

select empno as '员工编号',ename as '姓名' from emp;
select empno '员工编号',ename '姓名' from emp;
select empno 员工编号,ename 姓名 from emp;

去重 distinct

-查询emp表中所有员工的职位
	select distinct job from emp;

比较运算符 > < = >= <= !=(<>)

1. 查询工资高于2000的所有员工编号,姓名,职位,工资
   select empno,ename,job,sal from emp
   where sal>2000;
2. 查询工资小于等于1600的所有员工编号,姓名,工资
   select empno,ename,sal from emp
   where sal<=1600;
3. 查询部门编号是20的所有员工的姓名,职位,部门编号
   select ename,job,deptno from emp
   where deptno=20;
4. 查询职位是manager的所有员工姓名,职位
   select ename,job from emp
   where job=‘manager’;
5. 查询不是10号部门的所有员工姓名,工资,部门编号(两种写法)
   select ename,sal,deptno from emp
   where deptno!=10(deptno<>10);
6. 查询单价等于23的商品信息
   select * from t_item where price=23;
7. 查询单价不等于8443的商品信息
   select * from t_item where price!=8443;

and和or

• and等效java的 &&
• or等效java的 ||

1. 查询不是10号部门,工资小于3000的员工编号,姓名,工资,部门编号
   select empno,ename,sal,deptno from emp
   where deptno!=10 and sal<3000;
2. 查询部门是30或者上级领导为7698的所有员工的姓名,部门编号,上级领导编号
   select ename,deptno,mgr from emp
   where deptno=30 or mgr=7698;

in

• 如果查询字段的值为多个的时候可以使用in关键字

1. 查询person表中 年龄(age)是25,28,30,22岁的所有信息
   select * from person where age in (25,28,30,22);
2. 查询员工工资是800,950,1600的员工名字和工资
   select ename,sal from emp
   where sal in(800,950,1600);

between x and y

-在某两个数值之间, 包含and两边的数值

1. 查询员工工资在500-1000的所有员工名字和工资
   select ename,sal from emp
   where sal between 500 and 1000;

like

• _:代表单个未知字符
• %:代表多个未知字符
• 举例:
  -以a开头 a%
  -以a结尾 %a
  -第二个字符是a a%
  -包含a %a%
  -倒数第三个字符是a %a_
  -第二个和最后一个是a _a%a

1. 查询名字以k开头的所有员工名字
   select ename from emp where ename like ‘k%’;

2. 查询商品标题包含记事本的商品
   select title from t_item where title like ‘%记事本%’;

3. 查询单价低于100的记事本
   select title,price from t_item where title like ‘%记事本%’ and price<100;

4. 查询有图片的得力商品
   select title,image from t_item where title like ‘%得力%’ and image is not null;

5. 查询单价介于50到200之间的得力商品
   select title,price from t_item
   where price between 50 and 200
   and title like ‘%得力%’;

6. 查询有赠品的dell商品(卖点中带赠字)
   select title,sell_point from t_item
   where title like ‘%dell%’
   and sell_point like ‘%赠%’;

7. 查询标题不包含得力的商品
   select title from t_item
   where title not like ‘%得力%’;

8. 查询价格介于50和200之外的所有商品
   select * from t_item
   where price not between 50 and 200;

查询结果排序 order by

• 格式: order by 字段名
• 默认升序, 指定升序是:asc 降序:desc

1. 查询员工的名称和工资 按照工资降序排序
   select ename,sal from emp order by sal desc;
2. 查询单价在100以下的商品名称和价格,按照价格降序排序
   select title,price from t_item
   where price<100
   order by price desc;

• 多字段排序,当第一个字段有相同值时,第二个字段排序开始

1. 查询所有员工名称,部门编号,工资, 部门编号降序排序,工资升序排序
   select ename,deptno,sal from emp
   order by deptno desc,sal;
2. 查询所有dell商品,按分类id升序,单价降序排序
   select title,category_id,price from t_item
   where title like ‘%dell%’
   order by category_id,price desc;

limit 分页查询

• limit 跳过条数,查询条数

1. 查询商品表中商品名称和价格,第2页数据每页5条
   select title,price from t_item
   limit 5,5;
2. 查询所有商品,单价升序,显示第三页,每页3条数据
   select title,price from t_item
   order by price
   limit 6,3;
3. 查询拿最高工资的员工信息
   select * from emp
   order by sal desc
   limit 0,1;

数值计算 + - * / % (mod())

1. 查询所有商品的单价,库存,总价
   select price,num,price*num from t_item;

• %和mod都是取余的作用
  7%2 等效 mod(7,2)

日期相关函数

1. 获取当前日期+时间 now()
   select now();
2. 获取当前日期
   select curdate();
3. 获取当前时间
   select curtime();
4. 从日期和时间中提取日期
   select date(now());
5. 从日期和时间中提取时间
   select time(now());

• 案例: 查询商品创建的年月日
  select date(created_time) from t_item

6. 提取年 月 日 时 分 秒
   -select extract(year from now());
   -select extract(month from now());
   -select extract(day from now());
   -select extract(hour from now());
   -select extract(minute from now());
   -select extract(second from now());

• 案例:查询入职的年份
  select extract(year from hiredate) from emp;

7. 日期格式化 date_format()
   -格式: date_format(时间,格式)
   -%Y 4位年 2018
   -%y 2位年 18
   -%m 2位月 05
   -%c 1位月 5
   -%d 日
   -%H 24小时
   -%h 12小时
   -%i 分
   -%s 秒

• 案例:
  select date_format(now(),’%Y年%m月%d日 %H时%i分%s秒’);
• 练习:查询商品的创建日期(created_time) 年月日
  select date_format(created_time,’%Y年%m月%d日’) from t_item;

8. 把不规则日期格式转成标准格式
   -格式: str_to_date(日期字符串,格式)
   select str_to_date(‘25号12月2015年’,’%d号%m月%Y’);

ifnull()

• 格式: age=ifnull(x,y) 判断x是否为null如果是 age=y如果不是age=x;
• 案例:把员工表 没有奖金的 奖金修改为0;
  update emp set comm=ifnull(comm,0);

聚合函数

-对多行数据进行合并统计

• 求和 sum(字段名) 比如:求工资总和 sum(sal)
• 平均值 avg(字段名) 比如:求工资平均值 avg(sal)
• 最大值 max(字段名) 比如:求奖金最大值 max(comm)
• 最小值 min(字段名) 比如:求年龄最小值 min(age)
• 统计数量 count(*)

1. 查询员工表的平均工资
   select avg(sal) from emp;
2. 查询10号部门的最高工资
   select max(sal) from emp where deptno=10;
3. 查询dell商品的库存总和
   select sum(num) from t_item
   where title like ‘%dell%’;
4. 查询得力商品的条数
   select count(*) from t_item
   where title like ‘%得力%’;
5. 查询员工姓名中包含a的 最低工资
   select min(sal) from emp
   where ename like ‘%a%’;

字符串相关函数

1. concat(a,b) 字符串连接函数
   select concat(‘a’,‘b’);

• 案例:查询每个员工的工资 工资后面显示元
  select ename,concat(sal,‘元’) from emp;

2. char_length(str) 获取字符串的长度

• 案例: 获取每个员工姓名的长度
  select ename,char_length(ename) from emp;

3. instr(str,subStr)获取subStr在Str中的位置
   select instr(‘nba’,‘a’);
4. locate(subStr,str)获取subStr在Str中的位置
   select locate(‘a’,‘nba’);
5. insert(str,start,length,newstr)插入字符串
   select insert(‘abcdefg’,3,2,‘m’);
6. lower(str) 转小写
   select lower(‘NBa’);
7. upper(str) 转大写
   select upper(‘nBa’);
8. trim(str) 去两端空白
   select trim(’ abc ');
9. left(str,length) 从左边截取多少个字符
   select left(‘abcdefg’,3);
10. right(str,length)从右边截取多少个字符
    select right(‘abcdefg’,3);
11. substring(str,index,length)截取字符串
    select substring(‘abcdefg’,2,3);
12. replace(str,old,new) 替换字符串
    select replace(‘我爱苍老师’,‘我’,‘你们’);
13. repeat(str,count)重复
    select repeat(‘加油’,2);
14. reverse(str) 反转
    select reverse(‘我爱苍老师’);

数学相关函数

1. 向下取整 floor(num)
   select floor(3.23);
2. 四舍五入 round(num)
   select round(2.7);
   -round(num,m);
   select round(3.283749,2);
3. truncate(num,m); 非四舍五入
   select truncate(3.283749,1);
4. 随机数 rand() 0-1
   select floor(rand()*6);

回顾:

1. is null is not null

2. < >= <= != <>

3. select age as ‘年龄’
4. 去重 distinct select distinct age
5. and && or ||
6. in
7. between x and y
8. like _ %
9. order by age asc/desc, sal
10. limit 12,3
11. • • • / % mod()
12. now() curdate() curtime() date(now()) time(now) extract(year from now()) month day hour minute second date_format(now(),‘格式’) %Y y m c d H h i s, str_to_date(‘日期字符串’,‘格式’)
13. ifnull(x,y)
14. sum() avg() max() min() count(*)
15. 字符串

• concat(a,b)

• char_length(str)

• instr(str,substr)

• locat(substr,str)

• insert(str,start,length,newstr)

• upper()

• lower()

• trim()

• left(str,length)

• right()

• substring(str,index,length)

• replace(str,old,new)

• repeat(str,count);

• reverse(abc);
  数学:

• floor()

• round(num,m) m可以写负数

• truncate(num,m)

• rand()

  1.案例：查询没有上级领导的员工的编号，姓名，工资
  2.案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
  3.案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
  4.案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
  5.案例：查询emp表中名字以‘S’开头的所有员工的姓名
  6.案例：查询emp表中名字的最后一个字符是’S’的员工的姓名
  7.案例：查询倒数的第2个字符是‘E’的员工的姓名
  8.案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名
  9.案例：查询emp表中员工的名字中包含‘A’的员工的姓名
  10.案例：查询emp表中名字不是以’K’开头的员工的所有信息
  11.案例：查询emp表中名字中不包含‘A’的所有员工的信息
  12.案例：做文员的员工人数（job_id 中 含有 CLERK 的）
  13.案例：销售人员 job: SALESMAN 的最高薪水
  14.案例：最早和最晚入职时间
  15.案例：查询类别 163的商品总库存量
  16.案例：查询 类别 163 的商品
  17.案例：查询商品价格不大于100的商品名称列表
  18.案例：查询品牌是联想,且价格在40000以上的商品名称和价格
  19.案例：查询品牌是三木,或价格在50以下的商品名称和价格
  20.案例：查询品牌是三木、广博、齐心的商品名称和价格
  21.案例：查询品牌不是联想、戴尔的商品名称和价格
  22.案例：查找品牌是联想且价格大于10000的电脑名称
  23.案例：查询联想或戴尔的电脑名称列表
  24.案例：查询联想、戴尔、三木的商品名称列表
  25.案例：查询不是戴尔的电脑名称列表
  26.案例：查询所有是记事本的商品品牌、名称和价格
  27.案例：查询品牌是末尾字符是’力’的商品的品牌、名称和价格
  28.案例：名称中有联想字样的商品名称
  29.案例：查询卖点含有’赠’产品名称
  30.案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
  31.案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
  32.案例：查询emp表中名字中包含’E’，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
  33.案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
  34.案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
  35.案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号
  36.案例：查询不是30号部门的员工的所有信息
  37.案例：查询奖金不为空的员工的所有信息
  38.案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
  39.案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
  40.案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
  41.案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
  42.案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
  43.案例：统计emp表中员工的总数量
  44.案例：统计emp表中获得奖金的员工的数量
  45.案例：求出emp表中所有的工资累加之和
  46.案例：求出emp表中所有的奖金累加之和
  47.案例：求出emp表中员工的平均工资
  48.案例：求出emp表中员工的平均奖金
  49.案例：求出emp表中员工的最高工资
  50.案例：求出emp表中员工编号的最大值
  51.案例：查询emp表中员工的最低工资。
  52.案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
  53.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
  54.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
  55.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
  56.案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资
  57.案例：查询emp表中奖金在500~2000之间所有员工的编号，姓名，工资以及奖金
  58.案例：查询员工的编号是7369，7521，
  59.案例：查询emp表中，职位是ANALYST，
  60.案例：查询emp表中职位不是ANALYST,

========================================================
(四).

课程回顾

1. is null is not null

2. 别名

3. distinct

4. < >= <= = != <>

5. and or

6. in

7. between x and y

8. like _ %

9. order by 字段1 desc/asc,字段2;

10. limit 15,5

11. • • • / % mod()

12. now() curdate() curtime() date(now()) time(now()) extract(year from now()) month day hour minute second date_format(now(),格式) %Y y m c d H h i s str_to_date(‘时间字符串’,格式)

13. ifnull(x,y)

14. sum() avg() max() min() count(*)

15. concat(x,y) insert(str,start,length,newStr)
    char_length(str) instr(str, subStr), locate(substr,str), upper() lower() trim() left(str,length) right(),substring(str,index,length)
    replace(str,old,new),repeat(str,3),reverse(nba)

16. floor() round(num,2) truncate(num,2) rand()
    3-8 0-5 +3 floor(rand()*6)+3

17. 案例：查询品牌是三木、广博、齐心的商品名称和价格
    select title,price from t_item
    where title like ‘%三木%’ or title like ‘%广博%’ or title like ‘%齐心%’;

18. 案例：查询品牌不是联想、戴尔的商品名称和价格
    select title,price from t_item
    where title not like ‘%联想%’ and title not like ‘%戴尔%’;

19. 案例：查找品牌是联想且价格大于10000的电脑名称
    select title from t_item where price>10000
    and title like ‘%联想%’;

20. 案例：查询联想或戴尔的电脑名称列表
    select title from t_item where title like ‘%联想%’ or title like ‘%联想%’;

21. 案例：查询品牌是末尾字符是’力’的商品的名称和价格
    select title,price from t_item where title like ‘%_力%’;

22. 案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
    select empno,ename,job,sal from emp
    where sal between 1000 and 2000;

23. 案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
    select ename,job,mgr,deptno from emp
    where deptno=10 and mgr is not null;

24. 案例：查询emp表中名字中包含’E’，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
    select empno,ename,job,sal from emp
    where ename like ‘%E%’ and job!=‘manager’;

25. 案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
    select * from emp where deptno in(10,20);

26. 案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
    select * from emp where comm=0 or ename not like ‘%T_’;

27. 案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号
    select * from emp where sal>3000 or deptno=30;

28. 案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
    select * from emp where deptno in(10,30)
    order by sal;

29. 案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
    select * from emp order by deptno,empno desc;

30. 案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
    select * from emp where sal>1000 or mgr is null order by deptno desc,sal;

31. 案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
    select * from emp where ename not like ‘%s%’ order by sal,empno desc;

32. 案例：统计emp表中员工的总数量
    select count(*) from emp;

33. 案例：统计emp表中获得奖金的员工的数量
    select count(*) from emp where comm!=0;

34. 案例：求出emp表中所有的工资累加之和
    select sum(sal) from emp;

35. 案例：求出emp表中所有的奖金累加之和
    select sum(comm) from emp;

36. 案例：求出emp表中员工的平均工资
    select avg(sal) from emp;

37. 案例：求出emp表中员工的平均奖金
    select avg(comm) from emp;

38. 案例：求出emp表中员工的最高工资
    select max(sal) from emp;

39. 案例：求出emp表中员工编号的最大值
    select max(empno) from emp;

40. 案例：查询emp表中员工的最低工资。
    select min(sal) from emp;

41. 案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
    select count(*) 人数,sum(sal) 工资总和,avg(sal) 平均工资, max(comm) 奖金最大,min(comm) 奖金最小 from emp;

分组查询 group by

• 分组查询通常和聚合函数结合使用
• 查询条件中每个xxx 就以xxx作为分组的条件
• 格式: select deptno,avg(sal) from emp group by deptno;

1. 查询每个部门的最高工资
   select deptno,max(sal) from emp
   group by deptno;
2. 查询每个分类下商品的库存总量
   select category_id,sum(num) from t_item
   group by category_id;
3. 查询每个部门有多少人
   select deptno,count(*) from emp
   group by deptno;
4. 查询每个部门工资大于2000的有多少人
   select deptno,count(*) from emp
   where sal>2000 group by deptno;
5. 查询每个分类下低于100元的商品数量
   select category_id,count(*) from t_item
   where price<100 group by category_id;
6. 案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
   select deptno,count(*) c,sum(sal) s from emp
   group by deptno
   order by c,s desc;
7. 案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
   select deptno,avg(sal) a,min(sal),max(sal) from emp
   where sal between 1000 and 3000
   group by deptno
   order by a;
8. 案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
   select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp
   where mgr is not null
   group by job
   order by c desc,a;

• 查询每个部门,每个主管的手下人数
  select deptno,mgr,count(*) from emp
  where mgr is not null
  group by deptno,mgr;
• 提高题: 每年入职的人数
  select extract(year from hiredate) year,count(*) from emp group by year;

Having 有条件的分组统计

• where后面只能对普通字段进行筛选
• having写在 group by 后面,通常是和group by结合使用
• 普通字段的条件写在where后面,聚合函数条件写在having后面,having写在group by后面
• 查询每个部门的平均工资要求平均工资大于2000;
  select deptno,avg(sal) a from emp
  group by deptno
  having a>2000;

1. 查询所有分类的所对应的库存总量,要求库存总量高于1000
   select category_id,sum(num) s from t_item
   group by category_id
   having s>100000;
2. 查询所有分类对应的平均单价,平均单价低于100
   select category_id,avg(price) a from t_item
   group by category_id
   having a<100;
3. 查询每个部门中名字里面包含a的员工的平均工资,只显示平均工资高于2000的
   select deptno,avg(sal) a from emp
   where ename like ‘%a%’
   group by deptno having a>2000;

• sql中各个关键字的顺序:
  select … from 表名 where …group by…
  having…order by…limit

课堂练习

1. 查询emp表中,每个部门的平均工资高于2000的部门编号,部门人数,平均工资,根据平均工资进行降序排序.
   select deptno,count(*),avg(sal) a from emp
   group by deptno
   having a>2000 order by a desc;
2. 查询emp表,名字不是以k开头的信息,每个部门的最低工资高于1000的部门编号,工资总和,平均工资以及最低工资,根据平均工资进行排序
   select deptno,sum(sal),avg(sal) a,min(sal) m from emp where ename not like ‘k%’
   group by deptno having m>1000 order by a;
3. 查询emp表中部门编号是10,30的部门员工,每个职业中的最高工资低于5000的职业名称,人数,平均工资,最高工资,根据人数排序,如果人数一致,根据最高工资进行降序排序
   select job,count(*) c,avg(sal),max(sal) m from emp where deptno in (10,30)
   group by job having m<5000
   order by c,m desc;
4. 查询emp表,工资在1000-3000的员工信息,每个部门的编号,工资总和,平均工资,过滤掉平均工资低于2000的部门,平均工资升序排序,如果一致则工资总和降序
   select deptno,sum(sal) s,avg(sal) a from emp
   where sal between 1000 and 3000
   group by deptno
   having a>=2000
   order by a,s desc;

子查询

1. 查询拿最高工资的员工姓名
   select max(sal) from emp;
   select ename from emp where sal=5000;
   -把上面两条sql合并成一条
   select ename from emp where sal=(select max(sal) from emp);
2. 查询工资 高于平均工资的员工姓名和工资
   select ename,sal from emp where sal>(select avg(sal) from emp);
3. 查询最后入职的员工信息
   select * from emp where hiredate=(select max(hiredate) from emp);
4. 查询出有商品的分类信息(有商品 指在商品表中出现过的分类id)
   -1. 查询商品表中出现的分类id
   select distinct category_id from t_item;
   -2. 查询id值为 238 241 236 163 917的分类信息
   select * from t_item_category where id in(select distinct category_id from t_item);
5. 查询出有员工的部门信息
   select * from dept where deptno in(select distinct deptno from emp);
6. 查询工资高于 20号部门里面最高工资的 所有员工信息
   select * from emp where sal>(select max(sal) from emp where deptno=20);
7. 查询和jones一样工作的员工信息
   select * from emp where job=(select job from emp where ename=‘jones’) and ename!=‘jones’;
8. 查询部门平均工资最高的部门信息
   -1.得到最高的平均工资
   select avg(sal) a from emp group by deptno order by a desc limit 0,1;
   -2.得到最高平均工资的部门编号
   select deptno from emp
   group by deptno
   having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
   -3.查询所有部门详情 条件是2步的结果
   select * from dept where deptno in (select deptno from emp
   group by deptno
   having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1));

• 什么是子查询:嵌套在sql语句里面的查询sql语句 称为子查询
• 子查询可以有多层嵌套
• 子查询可以写在的位置有:

1. 写在where/having后面做查询条件的值
2. 写在from后面当成一张新的表 必须起别名
   select * from (select * from xxx) t1;
3. 可以写在创建表的时候
   create table newemp as (select ename,sal from emp);

关联查询

• 同时查询多张表的数据称为关联查询

1. 查询每一个员工姓名和所对应的部门名称
   select emp.ename,dept.dname
   from emp,dept
   where emp.deptno=dept.deptno;
2. 查询每个商品的标题,商品单价,商品分类名称
   select i.title,i.price,c.name
   from t_item i,t_item_category c
   where i.category_id=c.id;
3. 查询在new york工作的所有员工信息
   select e.*
   from emp e,dept d
   where e.deptno=d.deptno and d.loc=‘new york’;

笛卡尔积

• 关联查询如果不写关联关系,则查询结果为两张表的乘积,这个乘积称为笛卡尔积.
• 笛卡尔积是一种错误的查询结果,工作中不能出现.

等值连接和内连接

• 等值连接和内连接都是关联查询的查询方式,效果相同.
• 等值连接格式: select * from A,B where A.x=B.x and A.y=abc;
• 内连接格式: select * from A [inner] join B on A.x=B.x where A.y=abc;
• 内连接可读性更高,所以以后尽量使用内连接

1. 查询在new york工作的所有员工信息
   select e.*
   from emp e join dept d
   on e.deptno=d.deptno
   where d.loc=‘new york’;

外连接

• 关联查询时有时 只查询两张表有关系的数据,不能满足需求,如果需要查询某一张表所有数据(包含没有关系的),则使用外连接查询方式
• 左外连接:以join 左边表为主表查询所有数据,右边表只查询有关系的数据
• 右外连接:以join 右边表为主表查询所有数据,左边表只查询有关系的数据

1. 查询所有员工的名字和对应的部门名(使用左外)
   select e.ename,d.dname
   from emp e left join dept d
   on e.deptno=d.deptno;
2. 查询所有部门和对应的员工名(使用右外)
   select e.ename,d.dname
   from emp e right join dept d
   on e.deptno=d.deptno;

回顾:

1. 分组查询 group by having
2. 子查询 where/having后面 from后面当成新表 创建表的时候
3. 关联查询
4. 查询方式: 等值连接和内连接 外连接:左外 右外

作业:

1. 每个部门的人数,根据人数排序

2. 每个部门中，每个主管的手下人数

3. 每种工作的平均工资

4. 每年的入职人数

5. 少于等于3个人的部门

6. 拿最低工资的员工信息

7. 只有一个下属的主管信息

8. 平均工资最高的部门编号

9. 下属人数最多的人，查询其个人信息

10. 拿最低工资的人的信息

11. 最后入职的员工信息

12. 工资多于平均工资的员工信息

13. 查询员工信息，部门名称

14. 员工信息，部门名称，所在城市

15. DALLAS 市所有的员工信息

16. 按城市分组，计算每个城市的员工数量

17. 查询员工信息和他的主管姓名

18. 员工信息，员工主管名字，部门名

19. 员工信息，部门名，和部门经理

20. 员工和他所在部门名

21. 案例：查询emp表中所有员工的编号，姓名，职位，工资以及工资的等级，根据工资的等级进行升序排列

22. 案例：查询emp表中所有员工的编号，姓名，职位，工资以及该员工上级领导的编号，姓名，职位，工资

23. 案例：查询emp表中名字中没有字母’K’的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址

24. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资

25. 案例：查询emp表中所有员工的编号，姓名，职位，工资以及工资的等级，该等级的最低工资，按照员工的编号进行升序排列。

=====================================================
(五).

回顾

1. 分组查询 group by having

• 查询每个部门的平均工资 要求平均工资大于2000;
  select deptno,avg(sal) a from emp
  group by deptno
  having a>2000;

2. 子查询

• 查询工资高于平均工资的员工信息
  select * from emp where sal>(select avg(sal) from emp);

3. 关联查询

• 等值连接
  select e.ename,d.loc
  from emp e,dept d
  where e.deptno=d.deptno

• 内连接
  select e.ename,d.loc
  from emp e join dept d
  on e.deptno=d.deptno;

• 外连接
  select *
  from emp e left/right join dept d
  on e.deptno=d.deptno;

  1. 每个部门的人数,根据人数排序
     select deptno,count(*) c from emp
     group by deptno
     order by c;
  2. 每个部门中，每个主管的手下人数
     select deptno,mgr,count(*) from emp
     where mgr is not null
     group by deptno,mgr
  3. 每种工作的平均工资
     select job,avg(sal) from emp
     group by job;
  4. 每年的入职人数
     select extract(year from hiredate) year,count(*) from emp
     group by year;
  5. 少于等于3个人的部门
     select deptno,count(*) c from emp
     group by deptno
     having c<=3;
  6. 少于等于3个人的部门信息
     select * from dept where deptno in (select deptno from emp
     group by deptno
     having count(*)<=3);
  7. 拿最低工资的员工信息
     select * from emp where sal=(select min(sal) from emp);
  8. 只有一个下属的主管信息

• 先得到主管的员工编号
  select mgr from emp
  where mgr is not null
  group by mgr
  having count(*)=1

• 然后通过员工编号查询员工详情
  select * from emp where empno in (select mgr from emp
  where mgr is not null
  group by mgr
  having count()=1);
  7. 只有一个下属的主管信息
  select mgr,count() c from emp
  group by mgr
  having c=1;

• 通过内连接的方式把查询结果当成一张新表
  select e.*
  from emp e join (select mgr,count(*) c from emp
  group by mgr
  having c=1) n
  on e.empno=n.mgr;

  7. 只有一个下属的主管信息
     select m.*
     from emp e join emp m
     on e.mgr=m.empno
     group by e.mgr
     having count(*)=1;

  8. 平均工资最高的部门编号
     select deptno from emp
     group by deptno
     order by avg(sal) desc
     limit 0,1;

  9. 下属人数最多的人，查询其个人信息

• 查询最多的人数
  select count(*) c from emp
  group by mgr
  order by c desc
  limit 0,1;

• 查询最多人数的上级编号
  select mgr from emp
  group by mgr
  having count()=(select count() c from emp
  group by mgr
  order by c desc
  limit 0,1);

• 查询上面得到编号的详情
  select * from emp where empno in (select mgr from emp
  group by mgr
  having count()=(select count() c from emp
  group by mgr
  order by c desc
  limit 0,1));

  10. 拿最低工资的人的信息
      select * from emp where sal=(select min(sal) from emp);

  11. 最后入职的员工信息
      select * from emp where hiredate=(select max(hiredate) from emp);

  12. 工资多于平均工资的员工信息
      select * from emp where sal>(select avg(sal) from emp);

  13. 查询员工信息，部门名称
      select e.*,d.dname
      from emp e join dept d
      on e.deptno=d.deptno

  14. 员工信息，部门名称，所在城市
      select e.*,d.dname,d.loc
      from emp e join dept d
      on e.deptno=d.deptno

  15. DALLAS 市所有的员工信息
      select e.*
      from emp e join dept d
      on e.deptno=d.deptno
      where d.loc=‘dallas’;

  16. 按城市分组，计算每个城市的员工数量
      select d.loc,count(*)
      from emp e join dept d
      on e.deptno = d.deptno
      group by d.loc;

  17. 查询员工信息和他的主管姓名
      select e.*,m.ename
      from emp e join emp m
      on e.mgr=m.empno;

  18. 员工信息，员工主管名字，部门名
      select e.*,m.ename,d.dname
      from emp e join emp m
      on e.mgr=m.empno
      join dept d
      on e.deptno=d.deptno;

  19. 员工和他所在部门名
      select e.*,d.dname
      from emp e join dept d
      on e.deptno = d.deptno

  20. 案例：查询emp表中所有员工的姓名以及该员工上级领导的编号，姓名，职位，工资
      select e.ename,m.empno 领导编号,m.ename 领导姓名,m.job 职位,m.sal 工资
      from emp e left join emp m
      on e.mgr=m.empno;

  21. 案例：查询emp表中名字中没有字母’K’的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址
      select e.empno,e.ename,e.job,
      d.deptno,d.dname,d.loc
      from emp e left join dept d
      on e.deptno=d.deptno
      where e.ename not like ‘%k%’;

  22. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资
      select d.*,e.empno,e.ename,e.job,e.sal
      from dept d left join emp e
      on d.deptno=e.deptno;

表设计之关联关系

一对一:

• 什么是一对一:有两张表A和B,A表中有一条数据对应B表中的一条数据称为一对一关系

• 应用场景: 用户表和用户信息扩展表,商品表和商品信息扩展

• 如何建立关系:在从表中添加一个字段记录主表的id,用户表和用户信息扩展表中,用户表为主表,信息扩展表为从表,用户表中的id 称为主键, 从表中记录主表id的字段称为外键,主键用来表示数据的唯一性,外键用来和其它表建立关系.

• 练习:创建user(id,username,password)和userinfo(id,nick,age,phone)两张表然后往两个表里面各插入3条有关系的数据
  create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
  create table userinfo(id int,nick varchar(10),age int,phone varchar(15));

• 插入数据
  insert into user values(null,‘libai’,‘admin’),(null,‘lisi’,‘admin’),(null,‘liubei’,‘123’);
  insert into userinfo values(1,‘李白’,25,‘13838383388’),(2,‘李四’,15,‘131223232’),(3,‘刘备’,40,‘2342342’);

1. 查询每一个用户的用户名,昵称,电话
   select u.username,ui.nick,ui.phone
   from user u join userinfo ui
   on u.id=ui.id;
2. 查询名字叫李白的所有信息
   select *
   from user u join userinfo ui
   on u.id=ui.id
   where ui.nick=‘李白’;

一对多

• 什么是一对多:AB两张表中A表中一条数据对应B表中多条数据,并且B表中一条数据对应A表中一条数据,两张表的关系称为一对多.
• 应用场景:部门和员工,商品和分类等
• 如何建立关系: 一对多的两张表,在多的表中添加一个字段记录另外一张表的id.
• 练习:创建t_emp(id,name,sal,deptid),t_dept(id,name,loc), 两张表中插入数据:部门表插入神仙部loc为西天, 妖怪部 loc为盘丝洞, 员工表插入,猪八戒,孙悟空,蜘蛛精和白骨精.
  create table t_emp(id int primary key auto_increment,name varchar(10),sal double,deptid int);
  create table t_dept(id int primary key auto_increment,name varchar(10),loc varchar(20));
  insert into t_dept values(null,‘神仙’,‘西天’),(null,‘妖怪’,‘盘丝洞’);
  insert into t_emp values(null,‘猪八戒’,2000,1),(null,‘孙悟空’,3000,1),(null,‘蜘蛛精’,1000,2),(null,‘白骨精’,4000,2);

1. 查询每个部门对应的每个员工
   select *
   from t_emp e join t_dept d
   on e.deptid=d.id
2. 查询每个员工对应的每个部门只显示工资大于2000的信息,
   select *
   from t_emp e join t_dept d
   on e.deptid=d.id
   where e.sal>2000;

多对多

• 什么是多对多:AB两张表,A表中的一条数据对应B表的多条数据,同时B表的一条数据对应A表的多条数据,这种关系称为多对多

• 应用场景: 老师表和学生表,用户表和角色表

• 如何建立关系:需要创建一个关系表,在关系表中记录两个表的id.

• 练习: 创建老师(id,name)和学生表(id,name),创建老师和学生关系表t_s(tid,sid)
  create table teacher(id int primary key auto_increment,name varchar(10));
  create table student(id int primary key auto_increment,name varchar(10));
  create table t_s(tid int,sid int);

    insert into teacher values(null,'唐僧'),(null,'刘备'),(null,'苍老师');
    insert into student values(null,'悟空'),(null,'关羽'),(null,'小明');
  

1. 关系表中插入数据唐僧是悟空和小明的老师,刘备是关羽的老师,苍老师是所有人的老师
   insert into t_s values(1,1),(1,3),(2,2),(3,1),(3,2),(3,3);
2. 查询老师和学生表中 每个学生名子和对应的老师名字
   select s.name,t.name
   from teacher t join t_s ts
   on t.id=ts.tid
   join student s
   on ts.sid=s.id;
3. 查询苍老师的所有学生姓名
   select s.name
   from teacher t join t_s ts
   on t.id=ts.tid
   join student s
   on ts.sid=s.id
   where t.name=‘苍老师’;
4. 查询小明的所有老师
   select t.name
   from teacher t join t_s ts
   on t.id=ts.tid
   join student s
   on ts.sid=s.id
   where s.name=‘小明’;

• 子查询方式:
  select name from teacher where id in(select tid from t_s where sid=(select id from student where name=‘小明’));

自关联

• 什么是自关联:在一张表中有一个字段指向当前表的id,这种称为自关联
• 是什么时候使用自关联:当保存的数据有层级关系并且不确定有多少层的时候使用自关联.

1. 查询员工姓名和对应的上级姓名

    select e.ename,m.ename
    from emp e join emp m
    on e.mgr=m.empno
   

连接方式和关联关系

• 连接方式:包括内连接和外连接(左外和右外),是指通过sql查询两张表数据时使用的查询方式.
• 关联关系:包括一对一,一对多,多对多,是指设计表时两张表之间存在的逻辑关系.

案例:权限管理的表设计

1. 创建db5数据库并use,创建5张表: user(id,name),role(id,name),module(id,name),
   用户和角色关系表u_r(uid,rid),
   角色和权限关系表r_m(rid,mid)
   create table user(id int primary key auto_increment,name varchar(10));
   create table role(id int primary key auto_increment,name varchar(10));
   create table module(id int primary key auto_increment,name varchar(10));

    create table u_r(uid int,rid int);
    create table r_m(rid int,mid int);
   

2. 插入数据user:刘德华,张学友,凤姐 role:男游客,男会员,女游客,女管理员 module:男浏览,男发帖,女浏览,女发帖,女删帖
   insert into user values(null,‘刘德华’),(null,‘张学友’),(null,‘凤姐’);
   insert into role values(null,‘男游客’),(null,‘男会员’),(null,‘女游客’),(null,‘女管理员’);
   insert into module values(null,‘男浏览’),(null,‘男发帖’),(null,‘女浏览’),(null,‘女发帖’),(null,‘女删帖’);

3. 建立关系 刘德华是男游客 张学友是男会员,凤姐是女游客和男会员, 男游客对应男浏览 男会员对应男浏览和男发帖 女游客对应女浏览,女管理员对应女浏览女发帖和女删帖
   insert into u_r values(1,1),(2,2),(3,3),(3,2);
   insert into r_m values(1,1),(2,1),(2,2),(3,3),(4,3),(4,4),(4,5);

• 做题:

1. 查询每个用户对应的所有权限

    select u.name,m.name
    from user u join u_r ur
    on u.id=ur.uid
    join r_m rm
    on ur.rid=rm.rid
    join module m
    on rm.mid=m.id;
   

2. 查询刘德华对应的所有权限

    select u.name,m.name
    from user u join u_r ur
    on u.id=ur.uid
    join r_m rm
    on ur.rid=rm.rid
    join module m
    on rm.mid=m.id
    where u.name='刘德华';
   

3. 有男发帖的用户都有哪些

    select u.name,m.name
    from user u join u_r ur
    on u.id=ur.uid
    join r_m rm
    on ur.rid=rm.rid
    join module m
    on rm.mid=m.id
    where m.name='男发帖';
   

课程回顾

1. 关联关系:

• 一对一: 从表中添加字段记录主表的id
• 一对多: 从多的表中添加字段记录另一个表的id
• 多对多: 创建中间关系表保存关系
• 自关联: 自己关联自己 把一张表当两张表查询

2. 连接方式: 内连接和外连接(左右)
3. 权限管理:5张表 用户表 角色表 权限表 用户-角色 角色-权限

==============================================================
(六).

面试题

1. 创建表

    create table trade(id int primary key auto_increment,time date,money double,type varchar(5),pid int);
    create table person(id int primary key auto_increment,name varchar(10),gender varchar(5),rel varchar(5));
   

• 插入数据
  insert into person values(null,‘刘德华’,‘男’,‘亲戚’),(null,‘刘若英’,‘女’,‘亲戚’),(null,‘马云’,‘男’,‘同事’),(null,‘特朗普’,‘男’,‘朋友’),(null,‘貂蝉’,‘女’,‘同事’);
  insert into trade values(null,‘2018-02-20’,-20,‘微信’,1),
  (null,‘2018-02-21’,500,‘现金’,2),
  (null,‘2018-02-21’,-50,‘现金’,2),
  (null,‘2018-02-23’,50000,‘支付宝’,3),
  (null,‘2018-02-23’,-100,‘支付宝’,3),
  (null,‘2018-02-20’,1000,‘微信’,4),
  (null,‘2018-02-20’,-20000,‘现金’,5);

3. 统计2月15号到现在的所有红包收益 2018年2月15号
   select sum(money) from trade where time>str_to_date(‘2018年2月15号’,’%Y年%m月%d号’);
4. 查询2月15号到现在 金额大于100 所有女性亲戚的名字和红包金额
   select p.name,t.money
   from trade t join person p
   on t.pid=p.id
   where t.money not between -100 and 100
   and p.gender=‘女’
   and p.rel=‘亲戚’
   and t.time>str_to_date(‘2018年2月15号’,’%Y年%m月%d号’);
5. 查询三个平台分别收入的红包金额
   select type,sum(money) from trade
   where money>0
   group by type;

回顾

关联关系

1. 一对一: 从表中添加外键记录主表id
2. 一对多: 在多的一端添加外键记录另外一张表id
3. 多对多: 创建关系表 关系表中记录两个表的id
4. 自关联: 在当前表中添加外键记录当前表的id

连接方式

• 包括 内连接和外连接

权限管理

• 3张主表: 用户表,角色表,权限表
• 2个关系表: 用户角色关系表, 角色权限关系表

视图

• 什么是视图:数据库中存在多种对象,表和视图都是数据库中的对象,创建视图时名称不能和表名重名,视图实际上是代表了一段sql查询语句,可以理解成视图是一张虚拟的表,表中的数据会随着原表的改变而改变.

• 为什么使用视图:因为有些数据的查询需要书写大量的sql语句,每次书写比较麻烦,使用视图可以起到sql重用的作用,可以隐藏敏感信息

• 创建视图的格式:

    create view 视图名 as 子查询;
  

  -例如: (切换回db3数据库)
  create view v_emp_10 as (select * from emp where deptno=10);

• 隐藏员工表的工资字段
  create view v_emp_nosal as (select empno,ename,job,mgr,deptno from emp);

1. 创建emp表部门是20并且工资小于3000的视图

    create view v_emp_20 as (select * from emp where deptno=20 and sal<3000);
   

2. 创建emp表每个部门工资的总和,平均工资,最大工资,最小工资的视图
   create view v_emp as (select deptno,sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno);

视图的分类

1. 简单视图:创建视图的子查询中不包含:去重,函数,分组,关联查询的视图称为简单视图. 可以进行增删改操作
2. 复杂视图: 和简单视图相反.

在简单视图中进行增删改操作

1. 视图中插入数据
   insert into v_emp_10 (empno,ename,deptno,sal) values (10001,‘张三’,10,300);

• 数据污染: 往视图中插入一条视图中不显示,但是原表会显示的数据称为数据污染
  insert into v_emp_10 (empno,ename,deptno,sal) values (10002,‘李四’,20,400);
• 如果需要避免数据污染的出现,创建视图时需要使用 with check option的关键字
  create view v_emp_20 as (select * from emp where deptno=20) with check option;
  insert into v_emp_20 (empno,ename,deptno,sal) values (10003,‘王五’,20,400);(成功)
  insert into v_emp_20 (empno,ename,deptno,sal) values (10004,‘赵六’,10,400);(失败)
• 测试在视图中修改和删除数据 修改和删除操作方式和table一样

1. 修改v_emp_10视图中任意一条数据的工资为500;
   update v_emp_10 set sal=500 where empno=10001;
   update v_emp_10 set sal=500 where empno=10002;
2. 删除v_emp_10中工资为500的员工
   delete from v_emp_10 where sal=500;
   delete from v_emp_10 where empno=10002;

• 修改和删除只能操作视图中存在的数据

修改视图

• 格式:
  create or replace view 视图名 as 子查询

  create or replace view v_emp_10 as (select * from emp where deptno=10 and sal<2000);

删除视图

	drop view v_emp_20;
	drop view if exists v_emp_20;(如果存在删除 不存在也不会报错)

视图别名

• 如果创建视图的时候使用了别名,则对视图操作的时候只能使用别名
  create view v_emp_name as (select ename name from emp);
  update v_emp_name set name=‘abc’ where name=‘李四’;

视图总结:

1. 视图是数据库中的对象,代表一段sql语句
2. 作用:重用sql,隐藏敏感信息
3. 分类:简单(不包含函数,去重,分组,关联查询)(可以进行增删改查操作)和复杂(和简单相反)(只能查)
4. 工作中一般使用视图时只进行查询操作 如果需要增删改则直接对原表进行操作

约束

• 什么是约束:约束是给表字段添加的限制条件.

非空 not null

• 添加非空约束的字段 值不能为null
  create table t_null(id int,age int not null);

唯一 unique

• 添加唯一约束的字段 值不能重复
  create table t_unique(id int,age int unique);
  insert into t_unique values (1,20);(成功)
  insert into t_unique values (2,20);(失败)

主键约束 primary key

• 添加了主键约束的字段,值不能为null也不能重复
• 创建表时添加主键约束
  create table t_pri (id int primary key);
• 创建表后添加主键约束
  create table t_pri2 (id int);
  alter table t_pri2 add primary key(id);
• 一个表只能有一个主键
• 删除主键约束
  alter table t_pri2 drop primary key;

自增

1. 当字段赋值为null,字段会自动增长
2. 如果删除数据,自增数值不会减
3. 如果指定插入比较大的值,下次插入数据时会从最大值的基础上+1
4. 如果使用delete删除全表数据,自增值不变
5. 使用truncate 关键字 自增数值清零

默认约束 default

• 给字段设置默认值,当字段不赋值的时候,默认值生效

    create table t_def(id int,age int default 10);
  

检查约束 check

• mysql不支持,但语法通过不会报错

    create table t_check(id int,age int,check(age>10));
  

外键约束

• 什么是外键约束: 用来保证两张表之间数据一致性和完整性的约束
• 添加约束后:外键的值可以为null,可以重复,但不能是另外一张表不存在的数据
• 添加约束后,外键指向的表(dept)不能先删除,如果需要删除,要么删除外键约束,要么先删除存在外键的表(emp)
• 添加约束后,外键指向的数据不能先删除
• 外键的值通常指向另外一张表的主键
• 使用外键必须两张表使用相同的引擎(innodb),myisam不支持外键
• 工作中除非特殊情况,一般不使用外键约束,使用java代码通过逻辑对插入和删除的数据进行限制,因为加了外键约束后不方便测试

添加外键约束的格式:

create table emp(id int,age int,deptid int,constraint 约束名 foreign key(deptid) 
references 关联表名(关联的字段名));

• 创建部门和员工的两张表,创建一个db6数据库并使用
  create table dept(id int primary key auto_increment,name varchar(10));

    create table emp(id int primary key auto_increment,name varchar(10),deptid int, constraint fk_dept foreign key(deptid) references dept(id));
  
    insert into dept values(null,'神仙'),(null,'妖怪');
    insert into emp values(null,'观音',3);(失败)
  

索引

导入数据

• 在终端中先登录mysql 在db6下面执行 source命令
  source 文件的绝对路径

• 学生机路径:
  source /home/soft01/桌面/item_backup.sql

• windows电脑路径:把文件放在d盘下
  source d:/item_backup.sql

• 导入完之后 测试执行: select count(*) from item2;

什么是索引

索引是用来提高查询速度的技术,类似一个目录

• 为什么使用索引: 如果不使用索引,数据会零散的保存在磁盘块中,磁盘块大小(4-8kb),查询数据时需要挨个遍历每一个磁盘块,直到找到数据为止,使用索引之后会在磁盘中将数据以树状结构进行保存,查询数据时从树状结构中进行查询,可以大大降低磁盘块的访问数量,从而提高查询速度
• 索引内部原理图(了解)
  

索引是越多越好吗?

• 索引会占用磁盘空间,所以创建时需谨慎,根据查询需求来决定创建什么索引.

有索引就一定好吗?

• 索引需要建立在大量数据的表中,如果数据量不够大,有可能会降低查询效率

索引的分类(了解)

1. 聚集索引(聚簇索引):数据保存在树状结构中,一张表只有一个聚集索引,数据库会自动为添加了主键的表创建聚集索引.
2. 非聚集索引: 树状结构中没有数据,保存的是磁盘块的地址

如何创建索引(非聚集索引)

• 格式: create index 索引名 on 表名(字段名[(长度)]);
• 创建索引之前先查询看时间 1.06秒
  select * from item2 where title=‘100’;
• 创建title索引
  create index index_title on item2(title);
• 再次查询 看时间 0.03 提高30多倍
  select * from item2 where title=‘100’;

查看索引

show index from item2;

删除索引

drop index index_title on item2;

复合索引

创建索引的时候添加多个字段,这种索引称为复合索引

• 什么时候使用:当频繁使用多个字段作为查询条件的时候使用复合索引
• 创建格式: create index index_title_price on item2(title,price);

创建表时添加索引

create table t_index(id int,age int,index index_age(age));

索引总结

1. 索引会占磁盘空间,不是越多越好
2. 数据量小的表不要创建索引
3. 主键会自动创建聚集索引
4. 对经常出现在 where/order by/distinct后面的字段创建索引可以提高效率,效果更好
5. 不要在修改太频繁的表中创建索引

事务

什么是事务

数据库执行sql语句的最小工作单元,不可拆分,同时成功或同时失败

事务的ACID特性 面试常考

• Atomicity:原子性,最小 不可再次拆分
• Consistency:一致性,同时成功,同时失败
• Isolation:隔离性, 多个事务之间互不影响
• Durability:持久性, 事务完成后数据持久保存到数据库

mysql中事务的指令

1. 查看自动提交的状态
   show variables like ‘%autocommit%’;
2. 设置自动提交的状态
   set autocommit=0/1;
3. 提交
   commit
4. 回滚
   rollback
5. 保存回滚点
   savepoint s1
6. 回滚到某个点
   rollback to s1

课程回顾

day01

1. 数据库相关
   create database db1 character set utf8;
   show databases;
   show create database db1;
   drop database db1;
   use db1;
2. 表相关
   create table t1(id int,age int);
   show tables;
   show create table t1;
   desc t1;
   drop table t1;
   rename table t1 to t2;
   alter table t1 engine=myisam/innodb charset=gbk;
   alter table t1 add age int first/after xxx;
   alter table t1 drop age;
   alter table t1 change age myage int;
   alter table t1 modify age int first/after xxx;
3. 数据相关
   insert into t1 (id,age) values(1,2),(3,4);
   delete from t1 where id=1;
   update t1 set age=20 where id=1;
   select * from t1;

day02

1. 注释 comment
2. '和`
3. 冗余
4. sql分类
   ddl数据定义语言 alter create drop truncate 不支持事务
   dml数据操作语言 insert update delete select 支持事务
   dql数据查询语言 select
   tcl事务控制语言
   dcl数据控制语言 分配权限
5. 数据类型
   整数:int bigint(m) 显示长度 zerofill
   浮点数: double(m,d) decimal(m,d)
   字符串: char 不可变 255 varchar 可变 65535 text longtext
   日期: date time datetime timestamp
   其他类型

day03

1. < >= <= = != <>

2. and or
3. in
4. between x and y
5. like _ %
6. distinct
7. not
8. sum avg max min count
9. limit count,size
10. order by 字段名 desc/asc,字段名
11. now() curdate() curtime()
    date(now()) time(now())
    extract(year from now()) month day hour minute second
    date_format(now(),‘格式’)
    Y y m c d H h i s
    str_to_date(‘时间字符串’,‘格式’)
12. ifnull(x,y)
13. 字符串
    concat(a,b) char_length(str) instr(str,substr) locate(substr,str)
    upper() lower() trim() left right
    substring(str,index,length)
    replace(str,old,new)
    repeat(str,2);
    reverse(abc)
    insert(str,start,length,new)
14. 数学
    floor()
    round(num,m)
    truncate(num,m)
    rand()

day04

1. 分组查询 group by having
2. 子查询
3. 关联查询
   等值连接 select * from A,B where A.x=B.x and …
   内连接 select * from A join B on A.x=B.x where …
   外连接 select * from A left/right join B on A.x=B.x where …

day05

1. 表设计:
   1对1: 在从表中添加外键 记录主表的id
   1对多: 在多的一端添加外键 记录主表id
   多对多: 关系表 记录两个表的主键id
   自关联:当前表中添加外键指向自己表的主键id
2. 连接方式 内连接和外连接
3. 权限管理案例 三张主表 用户表 角色表,权限表 2张关系表 用户-角色 角色-权限

day06

1. 视图:数据库中的对象,一段sql,简单视图(不包含函数,去重,分组,关联查询,可以进行增删改操作)和复杂视图(简单相反)
   数据污染:插入一条视图中不可见但是原表中存在的数据 通过添加 with check option 解决
   格式: create view 名字 as 子查询
   修改格式: create or replace view 名字 as 子查询
   删除视图: drop view if exists 视图名;
2. 约束
   非空 not null
   唯一 unique
   主键 primary key
   自增 auto_increment
   默认 default
   检查 check
   外键约束: 可以为null 可以重复但不能不存在, 被依赖的数据不能删除 被依赖表不能随便删除
3. 索引: 提高查询效率 目录 ,索引占磁盘空间,数据量小的表添加索引会降低查询效率, 主键会自动创建聚集索引, 自己创建的索引都是非聚集索引
   格式: create index 名字 on 表名(字段名(长度),字段名)
   多字段的索引称为复合索引
   查看索引: show index from 表名
   删除索引: drop index 索引名 on 表名
4. 事务
   ACID:
   原子性:最小 不能拆分
   一致性: 同时成功或失败
   隔离性: 多事务互不影响
   持久性:事务完成后 数据持久保存在数据库中
   查看自动提交: show variables like ‘%autocommit%’
   设置自动提交: set autocommit=0/1;
   提交: commit
   回滚: rollback
   设置回滚点: savepoint s1;
   回滚到某个点: rollback to s1;