一起学习数据库

###数据库
####数据库介绍
之前通过io流操作文件的方式保存数据弊端：

1. 效率低
2. 一般只能保存小量数据
3. 只能保存文本数据
   ####什么是DB

• DataBase数据库，数据库实际上就是一个文件集合
  ####什么是DBMS
• DataBaseManagementSystem 数据库管理系统，是用来管理数据库文件的软件 常见的DBMS：MySql Oracle DB2 Sqlserver…
  ####数据库分类

1. 关系型数据库
   以表为单位保存数据，经过数学理论验证，关系型数据库可以保存现实生活中的任何关系
2. 非关系型数据库
   通常用于解决特定的问题如：数据缓存，redis数据库（通过键值对）
   ####主流关系型数据库介绍
3. MySQL： 08年被Sun公司收购，09年Sun被Oracle收购，Oracle公司产品，市场占有率排名第一，开源数据库， 原MySQL程序员创办了MariaDB
4. Oracle： 市场占有率第二，拉里.埃里森 性能最高价格最贵的数据库
5. DB2：IBM公司产品 闭源收费 经常和IBM公司的其它产品结合使用
6. SQLserver：微软产品 市场占有率第三，.net（C#）开发的网站一般使用SQLserver
7. sqlite： 轻量级数据库，应用在嵌入式设备和手机上

###开源和闭源

1. 开源：公开源代码，盈利方式为卖服务，技术大拿无偿维护升级
2. 闭源：不公开源代码，盈利方式为卖产品和服务，技术大拿各种攻击

###SQL

• Structured结构化 Query查询 Language语言，用户程序员和数据库进行交互，通过sql对数据进行增删改查

###数据库服务器

• 服务器指一台高配置的电脑，
• 数据库服务器就是在一台高配置的电脑上安装了提供数据库服务的软件(DBMS)，则这台电脑就称为数据库服务器，数据库软件本身支持网络访问功能

###连接并登陆MySQL数据库

• 连接数据库有以下几种方式：

1. 在命令行/终端中连接
2. 通过三方客户端
3. 通过Eclipse

• 如何连接数据库

  mysql -uroot -p 回车 学生机没有密码 直接回车

• 退出
  exit;
  ####数据库相关的SQL

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

###表相关SQL(前提一定选中数据库)

1. 创建表
   -格式：create table 表名(字段1名 字段1类型, 字段2名 字段2类型…);
   create table person(name varchar(5), age int);
2. 查询所有表
   show tables;
3. 查询表详情
   show create table person;
4. 创建表指定引擎和字符集
   -格式：create table 表名(字段1名 字段1类型, 字段2名 字段2类型…) engine=innodb/myisam charset=gbk/utf8;
   create table t1(name varchar(10),age int) engine=myisam charset=gbk;

• 数据库表的引擎
  1. innodb(默认)：支持数据库的高级操作如：事务，外键等
  2. myisam：不支持高级操作，只支持基础的增删改查操作

5. 查看表结构(表字段)
   desc person;
6. 删除表
   drop table person;
   ###创建三个数据库 mydb1 mydb2 mydb3 每一个数据库里面创建一个表 表名随意 表字段name字符串 age整数 创建完表后 再把表删除 最后把三个数据库删除

####修改表相关
create table student(name varchar(10),age int);

1. 修改表名
   -格式： rename table 原名 to 新名;
   rename table student to stu;

2. 修改表引擎和字符集
   -格式： alter table 表名 engine=myisam/innodb charset=gbk/utf8;

   alter table stu engine=myisam charset=gbk;

3. 添加表字段

• 最后添加 alter table 表名 add 字段名 类型;
  alter table stu add chinese int;
• 最前面添加 alter table 表名 add 字段名 类型 first;
  alter table stu add math int first;
• 某个字段后面添加 alter table 表名 add 字段名 类型 after xxx;
  alter table stu add english int after name;

4. 删除表字段
   -格式：alter table 表名 drop 字段名;
   alter table stu drop english;
5. 修改表字段名称和类型
   -格式：alter table 表名 change 原字段名 新字段名 新类型;
   alter table stu change math english int;
6. 修改字段类型和位置
   -格式：
   alter table 表名 modify 字段名 类型 first/(after xxx);
   alter table stu modify english int after chinese;

• 练习：

1. 创建一个hero表 有 id 整数 name 字符串 type 字符串 三个字段
   create table hero(id int,name varchar(10),type varchar(10));
2. 修改hero表的属性引擎为myisam 字符集为gbk
   alter table hero engine=myisam charset=gbk;
3. 给hero表添加money字段 整数类型，添加在name的后面
   alter table hero add money int after name;
4. 修改type字段 名称改为 hero_type varchar(20)
   alter table hero change type hero_type varchar(20);
5. 修改表名hero为 heros
   rename table hero to heros;
6. 修改name字段到最后面
   alter table heros modify name varchar(10) after hero_type;
7. 删除money字段
   alter table heros drop money;
8. 删除hero表
   drop table heros;

###数据相关SQL
create table hero(name varchar(10),age int);

1. 插入数据
   -全表插入格式：insert into 表名 values(值1,值2…);
   insert into hero values(‘李白’,30);
   -指定字段格式：insert into 表名 (字段1名,字段2名) values(值1,值2);
   insert into hero (name) values(‘关羽’);

   • 批量插入数据：
     insert into hero values(‘刘备’,20),(‘关羽’,19),(‘张飞’,30);
     insert into hero (name) values(‘悟空’),(‘八戒’),(‘沙僧’);

2. 查询数据 *代表所有字段
   select * from hero;
   select name from hero;
   select name,age from hero;
   select * from hero where age<20;
   select * from hero where name=‘关羽’;

###中文问题

• 在命令行中执行 set names gbk; (需要在数据库里面执行)

• 显示？的是因为数据库或表的字符编码为gbk 修改为utf8

• 数据库相关

1. create database db1 character set gbk/utf8;
2. show databases;
3. drop database db1;
4. show create database db1;
5. use db1;

• 表相关

1. create table t1(name varchar(10),age int);
2. show tables;
3. show create table t1;
4. create table t1(name varchar(10),age int) engine=myisam/innodb charset=utf8/gbk;
5. drop table t1;
6. desc t1;
7. rename table t1 to t2;
8. alter table t1 engine=myisam/innodb charset=utf8/gbk;
9. alter table t1 add age int first/after xxx;
10. alter table t1 drop age;
11. alter table t1 change age abc int;
12. alter table t1 modify age int first/after xxx;

• 数据相关

1. insert into t1 values(值1,值2),(值1,值2);
   insert into t1 (字段1,字段2) values(值1,值2),(值1,值2);
2. select * from t1;
   select name,age from t1;

###主键约束

• 什么是主键：表示表中数据唯一性的字段称为主键

• 添加主键约束的字段，值唯一且非空。
  -格式： create table emp(id int primary key,name varchar(10));
  insert into emp values(1,‘Tom’);//成功
  insert into emp values(2,‘Jerry’);//成功
  insert into emp values(1,‘abc’);//失败 id不能重复
  insert into emp values(null,‘abc’);//失败 主键不能为null
  ###主键+自增
  -格式：create table t1(id int primary key auto_increment,name varchar(10));
  insert into t1 values(null,‘刘备’);
  insert into t1 values(null,‘刘备’);

  insert into t1 values(10,‘张飞’);

1. 当自增字段的值为null时会自动赋值并且数值+1
2. 可以指定赋值
3. 自增数值只增不减 不会因为删除数据而减少
4. 在表中曾出现过的最大值的基础上+1
   ###注释 comment
   -格式：create table t2(id int primary key auto_increment comment ‘这是id表示唯一性’, name varchar(10) comment ‘这是名字’, sal int comment ‘这是工资’);
   -查看方式： show create table t2;

`和’ 的区别

• `是用来修饰表名和字段名的 可以省略

• ’ 是用来修饰字符串的
  ###数据冗余

• 什么是冗余：如果表设计不够合理，随着数据量的增多，可能会出现大量的重复数据，这种重复数据成为数据冗余，可以通过拆分多个表的形式解决此问题

• 练习1：请设计表保存以下数据

  1. 电视机分类下的康佳电视价格3000库存100个
  2. 电视机分类下的惠普电视价格8000库存800个
  3. 手机分类下的iphone x 价格6000 库存5个
  4. 手机分类下的华为 价格3000 库存10个

• 分类表category（id,name）
  create table category(id int primary key auto_increment,name varchar(10));

• 商品表item(id,name,price,num,cid)
  create table item(id int primary key auto_increment,name varchar(10),price int,num int,cid int);

• 插入数据
  insert into category values(null,‘电视机’),(null,‘手机’);
  insert into item values(null,‘康佳’,3000,100,1),(null,‘惠普’,8000,800,1),(null,‘iPhone X’,6000,5,2),(null,‘华为’,3000,10,2);

• 练习2：请设计表保存以下数据

1. java教学部的苍老师工资200年龄18
2. java教学部的传奇老师工资300年龄20
3. 销售部的刘德华工资100年龄50
4. 人事部的张学友工资80年龄30

• 创建部门表dept id name
  create table dept(id int primary key auto_increment,name varchar(10));
• 员工表emp id name sal age deptid
  create table emp(id int primary key auto_increment,name varchar(10),sal int,age int,deptid int);
• 插入数据
  insert into dept values(null,‘java教学部’),(null,‘销售部’),(null,‘人事部’);
  insert into emp values(null,‘苍老师’,200,18,1),(null,‘传奇老师’,300,20,1),(null,‘刘德华’,100,50,2),(null,‘张学友’,80,30,3);

###事务

• 创建人物表
  create table person(id int primary key auto_increment,name varchar(10),money int);

• 插入两条数据
  insert into person values(null,‘超人’,500),(null,‘钢铁侠’,50000);

• 超人和钢铁侠借10000块钱

  1. 让钢铁侠-10000
     update person set money=money-10000 where id=2;
  2. 让超人+10000
     update person set money=money+10000 where id=1;

• 什么是事务：事务是数据库中执行SQL语句的工作单元，可以保证事务内的SQL语句要么全部成功，要么全部失败。

• 如何使用事务：

1. 把数据库的自动提交改成手动提交，
2. 执行多条sql语句，此时SQL会在内存中执行
3. 当所有SQL在内存中执行完后 手动提交，把多次改动一次性提交到数据库文件中

• 查看数据库自动提交的状态
  show variables like ‘%autocommit%’;
• 关闭和打开自动提交 0:关闭 1：开启
  set autocommit=0;
• 验证事务的步骤：

1. 在A窗口中执行让钢铁侠-10000
   update person set money=money-10000 where id=2;
2. 此时在A窗口中执行select * from person 验证数据时被改掉的，但是此时显示的内容是内存中的数据
3. 打开新的B窗口执行select * from person 因为此时数据查询的是数据库文件中的数据，此时并没有发生改变
4. 在A窗口中执行让超人+10000
   update person set money=money+10000 where id=1;
5. 此时A窗口查询数据改变（内存中数据），B窗口数据没变（数据库文件中数据）
6. 回到A窗口执行commit; 手动提交，此时B窗口查询数据也发生了改变，因为A窗口中已经把两次内存中的改动提交到了数据库文件中。

####为什么使用事务？
当做某一件事需要执行多条SQL语句的时候（类似转账），如果不使用事务，则可能出现多条SQL部分成功部分失败，这样的结果会导致数据错乱，使用事务后可以保证多条SQL语句要么全部成功，要么全部失败。可以解决以上问题
####事务的执行流程？
所谓开启事务实际上就是把数据库的自动提交关闭改成手动提交，在手动提交之前多次SQL语句的执行只会对内存中的数据进行更改，当提交的时候会把多次SQL的执行结果一次性提交到数据库文件中

####事务回滚

• 事务回滚会把内存中的数据回滚到上次提交的点
• 设置回滚点： savepoint 标识;
• 回滚到某个回滚点： rollback to 标识;

###事务相关指令总结：

1. show variables like ‘%autocommit%’;
2. set autocommit=0/1;
3. commit;
4. rollback;
5. savepoint s1;
6. rollback to s1;

###SQL分类

1. DDL：Data Definition Language 数据定义语言，包括：create,alter,drop,truncate。不支持事务
2. DML：Data Manipulation Language 数据操作语言，包括：insert delete update 和 select(DQL)。支持事务
3. DQL：Data Query Language 数据查询语言，只有select
4. TCL：Transaction Control Language 事务控制语言，包括：commit，rollback，savepoint，rollback to xxx;
5. DCL：Data Control Language 数据控制语言，分配用户权限相关的SQL
   ####truncate

• 删除表并创建一个新表(空表)
• 格式： truncate table 表名;
• truncate、drop、delete区别： 执行效率drop>truncate>delete, drop只是删除表，truncate是删除表并创建一个空表，delete只是删除数据自增数值不会清零

###数据库的数据类型

1. 整数： int(m) bigint(m) m代表显示长度 zerofill（零填充）
   create table t_int(num int(10) zerofill);
   insert into t_int values(123);
2. 浮点数： double(m,d) m代表总长度，d代表小数长度
   如：76.234 m=5 d=3;

• decimal(m,d) 超高精度浮点数，涉及超高精度运算时使用。

3. 字符串

• char(n)： 固定长度 n=10 ‘abc’ 占10 执行效率高，最大长度255
• varchar(n):可变长度 n=10 ‘abc’ 占3 更节省空间，最大长度65535，超过255建议使用text。
• text: 可变长度 最大65535。

4. 日期类型

• date: 保存年月日

• time: 保存时分秒

• datetime： 保存年月日时分秒 默认值为null，最大值9999-12-31

• timestamp(时间戳)：保存年月日时分秒 默认值为当前时间，最大值2038-1-19

• 练习：
  create table t_date(d1 date,d2 time,d3 datetime,d4 timestamp);
  insert into t_date values(‘2018-03-18’,null,null,null);
  insert into t_date values(null,‘17:23:18’,‘2018-05-15 12:18:33’,null);

• 数据库相关

1. 创建 create database db1;
2. 查询所有 show databases;
3. 查询详情 show create database db1;
4. 指定字符集 create database db1 character set gbk/utf8;
5. 使用 use db1;
6. 删除 drop database db1;

• 表相关

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

• 数据相关

1. 插入数据
   insert into t1 (name,age) values (值1,值2),(值1,值2),(值1,值2);
2. 查询 select * from t1; select name,age from t1;
3. 删除数据 delete from t1 where id=10;
4. 修改数据 update t1 set name=‘abc’,age=10 where id=5;
5. 表示数据唯一性的字段称为主键 主键约束：唯一且非空
   primary key
6. 自增 auto_increment null自动赋值 只增不减
7. 注释 comment
8. `修饰表名和字段名 可以省略 和’ 修饰字符串
9. 冗余 通过拆分表解决
10. 事务：数据库中执行SQL语句的工作单元 可以保证全部成功或全部失败
    show variables like ‘%autocommit%’;
    set autocommit=0/1;
    commmit;
    rollback;
    savepoint s1;
    rollback to s1;
11. SQL分类

• DDL：数据定义语言 包括 create drop alter truncate 不支持事务
• DML：数据操作语言 包括 insert delete update select(DQL) 支持事务
• DQL：数据查询语言 只有select
• TCL： 事务控制语言 包括 commmit;
  rollback;
  savepoint s1;
  rollback to s1;
• DCL： 数据控制语言 分配用户权限相关SQL

8. 数据类型

• 整数： int(m) bigint(m) m代表显示长度 需要结合 zerofill

• 浮点数： double(m,d) m带表总长度 d代表小数长度 decimal(m,d)

• 字符串： char(n)固定长度 最大255 执行效率高 varchar(n)可变长度最大65535超过255建议使用text 节省空间 text 可变长度 最大65535

• 日期： date年月日 time时分秒 datetime 默认null 最大值9999-12-31 timestamp 默认当前时间 最大值 2038-1-19

• 练习题：

1. 创建db3并使用 如果之前存在的话先删除
   create database db3;
   use db3;
2. 创建emp员工表 有id（主键+自增）姓名，入职日期（hiredate 类型为date）
   create table emp(id int primary key auto_increment,name varchar(10),hiredate date);
3. 在姓名的后面添加工资sal字段和年龄age字段
   alter table emp add sal int after name;
   alter table emp add age int after name;
4. 表中添加以下五位员工的信息： 姚明 35 2000 2018-12-22，周杰伦 36 8000 2018-10-14，范冰冰 40 5000 2018-9-30，貂蝉 24 6000 2018-11-15，武则天 50 9000 2016-10-22
   insert into emp values(null,‘姚明’,35,2000,‘2018-12-22’),(null,‘周杰伦’,36,8000,‘2018-10-14’),(null,‘范冰冰’,40,5000,‘2018-9-30’),(null,‘貂蝉’,24,6000,‘2018-11-15’),(null,‘武则天’,50,9000,‘2016-10-14’);
5. 修改表中第二位员工的姓名为周星驰
   update emp set name=‘周星驰’ where id=2;
6. 创建员工类型表，表名type 字段有id，name，loc（工作地点），并且在表中插入数据 体育明星 大陆，歌星 港台，影星 大陆，法师 古代
   create table type(id int primary key auto_increment,name varchar(10),loc varchar(10));
   insert into type values(null,‘体育明星’,‘大陆’),(null,‘歌星’,‘港台’),(null,‘影星’,‘大陆’),(null,‘法师’,‘古代’);
7. 给员工表添加 typeid字段 并且修改表中的数据 姚明-体育明星，周星驰-歌星，范冰冰-影星，貂蝉和武则天-法师
   alter table emp add typeid int;
   update emp set typeid=1 where id=1;
   update emp set typeid=2 where id=2;
   update emp set typeid=3 where id=3;
   update emp set typeid=4 where id=4;
   update emp set typeid=4 where id=5;
8. 修改法师的工资为8888
   update emp set sal=8888 where typeid=4;
9. 查询年龄在30岁以上的员工姓名和工资
   select name,sal from emp where age>30;
10. 删除影星
    delete from emp where typeid=3;
11. 删除表 删除db3
    drop table emp;
    drop table type;
    drop database db3;

is null

1. 查询没有上级领导的员工编号姓名和工资
   select empno,ename,sal from emp where mgr is null;
2. 查询emp表中没有奖金comm的员工姓名ename，工资sal，奖金comm
   select ename,sal,comm from emp where comm is null;

is not null

1. 查询emp表中有奖金的员工信息
   select * from emp where comm is not null;
   ###比较运算符
2. 查询工资小于等于1600 姓名 工资
   select ename,sal from emp where sal<=1600;
3. 查询部门编号是20的员工姓名职位工资
   select ename,job,sal from emp where deptno=20;
4. 查询职位是manager的所有员工姓名职位部门编号
   select ename,job,deptno from emp where job=‘manager’
5. 查询不是10号部门的员工姓名部门编号
   select ename,deptno from emp where deptno!=10;
   select ename,deptno from emp where deptno<>10;
6. 查询商品表t_item 单价等于23的商品信息
   select * from t_item where price=23;
7. 查询单价不等于8443的商品信息
   select * from t_item where price!=8443;

####别名

1. select ename as ‘姓名’,sal as ‘工资’ from emp;
2. select ename ‘姓名’,sal ‘工资’ from emp;
3. select ename 姓名,sal 工资 from emp;
   ####去重 distinct
4. 查询员工从事的所有职业
   select distinct job from emp;
5. 查询有员工的部门编号
   select distinct deptno from emp;
   ####and 和 or

• and和java中的 &&效果一样
• or 和java中的|| 效果一样

1. 查询10号部门工资高于3000块钱的员工信息
   select * from emp where deptno=10 and sal>3000;
2. 查询部门编号为30或者上级领导为7698的员工姓名，职位，上级领导和部门编号
   select ename,job,mgr,deptno from emp where deptno=30 or mgr=7698;

in

1. 查询工资为5000,1500,3000的员工信息

   select * from emp where sal=5000 or sal=1500 or sal=3000;
   select * from emp where sal in (5000,1500,3000);

between x and y 在x和y之间 包含xy

1. 查询工资在2000到4000之间的员工信息
   select * from emp where sal between 2000 and 4000;

2. 查询工资在2100到2800之外的员工姓名和工资
   select ename,sal from emp where sal not between 2100 and 2800;

3. 查询10号部门工资在2000到3000之间的员工信息

   select * from emp where deptno=10 and sal between 2000 and 3000;
   ###模糊查询 like

• _: 代表单个未知字符
• %：代表0或多个未知字符
• 举例:

1. 名字以a开头 ename like ‘a%’
2. 以a结尾 %a
3. 包含a %a%
4. 第二个字母是a _a%
5. 倒数第三个字符是a %a__
6. 第二个字母是a最后字母是b _a%b

• 案例：

1. 查询标题title中包含记事本的商品标题
   select title from t_item where title like ‘%记事本%’;
2. 查询单价低于100的记事本 标题和单价price
   select title,price from t_item where price<100 and title like ‘%记事本%’;
3. 查询单价在50到200之间的得力（title包含得力）商品标题和单价
   select title,price from t_item where price between 50 and 200 and title like ‘%得力%’;
4. 查询有图片(image字段不等于null)的得力商品信息
   select * from t_item where image is not null and title like ‘%得力%’;
5. 查询有赠品的商品信息（sell_point字段包含赠字）
   select * from t_item where sell_point like ‘%赠%’;
6. 商品标题中不包含得力的商品
   select * from t_item where title not like ‘%得力%’;
   ###排序 order by

• 如果有条件写在条件的后面 没条件写在 表名的后面
• 默认是升序 desc降序 asc升序

1. 查询员工姓名和工资按照工资的降序排序
   select ename,sal from emp order by sal desc;
2. 查询所有的dell商品(title包含dell) 按照单价降序排序
   select title,price from t_item where title like ‘%dell%’ order by price desc;

• 多字段排序

1. 查询员工的姓名工资部门编号 按照部门编号降序如果编号相同则按照工资升序排序
   select ename,sal,deptno from emp
   order by deptno desc,sal;
   ###分页查询 limit x,y

• 第一个参数代表跳过的条数
• 第二个参数代表每页的数量
• limit 关键字通常写在sql语句的最后面

1. 查询所有商品按照单价升序排序 显示第二页 每页7条数据
   select * from t_item order by price limit 7,7;
2. 查询工资前三名的员工姓名和工资
   select ename,sal from emp order by sal desc limit 0,3;

###数值计算 + - * / 5%3等效mod(5,3)

• 查询所有员工的姓名，工资及年终奖(工资5)
  select ename,sal,sal5 年终奖 from emp;
• 查询商品表中商品单价，库存，及总金额(单价库存)
  select price,num,pricenum from t_item;
  ###ifnull(x,y)函数
• age=ifnull(x,y) 如果x的值为null则赋值y 如果不为null则赋值x

1. 将emp表中奖金为null的全部改成0
   update emp set comm=ifnull(comm,0);
   ###聚合函数

• 对多行数据进行统计

1. 求和 sum(求和的字段名)
   • 查询所有员工的工资总和
     select sum(sal) from emp;
   • 查询20号部门的工资总和
     select sum(sal) from emp where deptno=20;
2. 平均值 avg(字段名)
   • 查询10号部门的平均工资
     select avg(sal) from emp where deptno=10;
3. 最大值 max(字段名)
   • 查询30号部门的最高工资
     select max(sal) from emp where deptno=30;
4. 最小值 min(字段名)
   • 查询dell商品中最便宜的商品价格
     select min(price) from t_item where title like ‘%dell%’;
5. 统计数量 count(字段名/*)
   • 查询工资大于等于3000的员工数量
     select count(*) from emp where sal>=3000;

###日期相关函数

select ‘helloworld!’;

• 获取当前的年月日时分秒 now()
  select now();
• 获取当前年月日 current
  select curdate();
• 获取当前时分秒
  select curtime();
• 从年月日时分秒中 提取年月日 提取时分秒
  select date(now());
  select time(now());
• 提取时间分量 年 月 日 时 分 秒
  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());
• 日期格式化函数
  -格式： date_format(日期,format)；
• format：

1. %Y 四位年 %y 两位年
2. %m 两位月 %c 一位月
3. %d 号
4. %H 24小时 %h 12小时
5. %i 分
6. %s 秒
   select now();
7. 把now()格式改成 年月日时分秒
   select date_format(now(),’%Y年%m月%d日%H时%i分%s秒’);

• 把非标准格式转回标准格式
  str_to_date(非标准时间,format)

1. 14.08.2018 08:00:00 转回标准时间
   select str_to_date(‘14.08.2018 08:00:00’,’%d.%m.%Y %H:%i:%s’);

###回顾知识

1. is null 和 is not null
2. 比较运算符 > < >= <= = !=和<>
3. and 和 or
4. in
5. between x and y
6. like _单个未知 %0或多个未知
7. 去重 distinct
8. 别名
9. order by 字段名 desc/asc ,字段名
10. limit 跳过条数,每页条数
11. • • • / % mod
12. age = ifnull(x,y)
13. 聚合函数 求和sum 平均值avg 最大值max 最小值 min 统计数量count
14. 日期相关 now() curdate() curtime() date(now()) time(now()) extract(year/month/day/hour/minute/second from now()) date_format(now(),format) %YymcdHhis str_to_date(非标准格式时间,format);

练习

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= CLERK 的）

13. 案例：销售人员 job: SALESMAN 的最高薪水

14. 案例：最早和最晚入职时间

15. 案例：查询类别 163的商品总库存量

16. 案例：查询 类别 163 的商品

17. 案例：查询商品价格不大于100的商品名称列表

18. 案例：查询品牌是联想,且价格在40000以上的商品名称和价格

19. 案例：查询品牌是三木,或价格在50以下的商品名称和价格

20. 案例：查询品牌是三木、广博、齐心的商品名称和价格

21. 案例：查询品牌不是联想、戴尔的商品名称和价格

22. 案例：查找品牌是联想且价格大于10000的电脑名称

23. 案例：查询联想或戴尔的电脑名称列表

24. 案例：查询联想、戴尔、三木的商品名称列表

25. 案例：查询不是戴尔的电脑名称列表

26. 案例：查询所有是记事本的名称和价格

27. 案例：查询品牌是末尾字符是’力’的商品的名称和价格

    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表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。

    56.案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资

    57.案例：查询emp表中奖金在500~2000之间所有员工的编号，姓名，工资以及奖金

    58.案例：查询员工的编号是7369，7521，

    59.案例：查询emp表中，职位是ANALYST，

    60.案例：查询emp表中职位不是ANALYST,

###字符串相关函数

1. 字符串的拼接 concat(s1,s2) s1s2
   select concat(‘aaa’,‘bbb’);
   • 查询员工姓名和工资 要求工资以元为单位
     select ename,concat(sal,‘元’) from emp;
2. 获取字符串的长度 char_length(str)
   select char_length(‘abc’);
   • 查询员工姓名和名字的长度
     select ename,char_length(ename) from emp;
3. 获取字符串在另一个字符串出现的位置 从1开始
   -格式： instr(str,substr)
   select instr(‘abcdefg’,‘d’); 4
   -格式2： locate(substr,str);
   select locate(‘d’,‘abcdefg’); 4
4. 插入字符串 insert(str,start,length,newstr);
   select insert(‘abcdefg’,3,2,‘m’); abmefg
5. 转大写转小写 upper(str) lower(str)
   select upper(‘abc’),lower(‘NBA’); ABC nba
6. 去空白 trim(str)
   select trim(’ a b '); a b
7. 截取字符串 left(str,start) right(str,start)
   select left(‘abcdefg’,2);
   select right(‘abcdefg’,2);
   -substring(str,start,[length]);
   select substring(‘abcdefg’,2); //截取到最后
   select substring(‘abcdefg’,2,3); //从2个开始截取 截取3个
8. 重复repeat(str,count)
   select repeat(‘ab’,2);
9. 替换replace(str,old,new)
   select replace(‘abcdefg’,‘de’,‘mm’);
10. 反转reverse(str)
    select reverse(‘abc’);

###数学相关

• 向下取整 floor(num)
  select floor(3.84); 3
• 四舍五入 round(num)
  select round(23.8); 24
• 四舍五入 round(num,m) m代表小数位数
  select round(23.879,2); 23.88
• 非四舍五入 truncate(num,m) m代表小数位数
  select truncate(23.879,2); 23.87
• rand() 随机数 0-1随机数
  select floor(rand()*9);
  select floor(rand()*3)+3; 3-5 随机数

###分组查询

1. 查询每个部门的最高工资

   select deptno,max(sal) from emp group by deptno;

2. 查询每个职位的平均工资
   select job,avg(sal) from emp group by job;

3. 查询每个部门的人数
   select deptno,count(*) from emp group by deptno;

4. 查询工资大于1000的员工 每个部门的最大工资
   select deptno,max(sal) from emp
   where sal>1000
   group by deptno;

5. 查询每个领导(主管)的手下人数
   select mgr,count(*) from emp where mgr is not null group by mgr;

• 多字段分组查询

1. 查询每个部门下每个主管的手下人数
   select deptno,mgr,count(*) from emp
   where mgr is not null group by deptno,mgr;

• 分组查询练习

1. 案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
   select deptno,count(*) c,sum(sal) s from emp
   group by deptno order by c,s desc;
2. 案例：查询工资在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;
3. 案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
   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;

• 各种关键字顺序 where order by limit group by
  select * from 表名 where … group by … order by … limit…

###having

• having一般要结合分组查询和聚合函数使用，用于给聚合函数的内容添加条件
• 聚合函数的条件不能写在where后面
• 普通字段的条件写在where后面，聚合函数的条件写在having后面

1. 查询每个部门的平均工资 要求平均工资大于2000
   -以下是错误写法：
   select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;

   • 正确：
     select deptno,avg(sal) a from emp
     group by deptno having a>2000;

2. 查询每个分类的平均单价 要求平均单价低于100
   select category_id,avg(price) a from t_item
   group by category_id having a<100;

3. 查询category_id为238和917的两个分类的平均单价
   select category_id,avg(price) from t_item
   where category_id in(238,917)
   group by category_id;

4. 查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序
   select deptno,count(*),avg(sal) a from emp
   group by deptno having a>2000 order by a desc;

5. 查询emp表中工资在1000-3000之间的员工，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，按照平均工资进行升序排序。
   select deptno,sum(sal),avg(sal) a from emp
   where sal between 1000 and 3000
   group by deptno
   having a>=2000
   order by a;

6. 查询emp表中不是以s开头，每个职位的名字，人数，工资总和，最高工资，过滤掉平均工资是3000的职位，根据人数升序排序如果一直根据工资总和降序排序
   select job,count(*) c,sum(sal) s,max(sal) from emp
   where ename not like ‘s%’
   group by job
   having avg(sal) !=3000
   order by c,s desc;

7. 查询emp表中每年入职的人数(提高题)
   select extract(year from hiredate) year,count(*) from emp group by year;

8. 查询每个部门的最高平均工资(提高题)
   select avg(sal) a from emp group by deptno
   order by a desc
   limit 0,1;

   • 查询每个部门的最高平均工资的部门编号
     select deptno from emp group by deptno
     order by avg(sal) desc
     limit 0,1;
     ###子查询(嵌套查询)

9. 查询emp表中工资最高的员工信息
   select max(sal) from emp;
   select * from emp where sal=5000;
   -把上面两条sql 和并到一起
   select * from emp where sal=(select max(sal) from emp);

10. 查询emp表中工资大于平均工资的所有员工的信息

    select * from emp where sal>(select avg(sal) from emp);

11. 查询工资高于20号部门最大工资的员工信息
    select * from emp where sal>(select max(sal) from emp where deptno=20)

12. 查询和Jones相同工作的其他员工信息
    select * from emp where job=(select job from emp where ename=‘jones’) and ename!=‘jones’;

13. 查询工资最低的员工的同事们的信息 (同事=相同job)
    -1. 得到最低工资
    select min(sal) from emp
    -2.通过最低工资得到工作名称
    select job from emp where sal=(select min(sal) from emp);
    -3.通过工作名称找到对应的员工信息，需要把最低工资的人过滤掉
    select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

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

15. 查询名字为king的部门编号和部门名称（需要用到dept表）
    select deptno,dname from dept where deptno=(select deptno from emp where ename=‘king’);

16. 查询有员工的部门信息(编号和部门名称)
    select deptno,dname from dept where deptno in(select distinct deptno from emp)

17. 查询平均工资最高的部门信息(最大难度)
    -最大平均工资
    select avg(sal) a from emp group by deptno
    order by a desc
    limit 0,1;
    -通过最大平均工资找到部门编号
    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);
    -通过部门编号查询部门详情
    select * from dept where deptno in(上面一坨);

• 子查询总结：

1. 嵌套在SQL语句中的查询语句称为子查询
2. 子查询可以嵌套n层
3. 子查询可以写在哪些位置？

• 写在where或having后面当做查询条件的值

• 写在from后面 当一张新表 必须有别名

  select * from emp where sal>1000;

  select ename from (select * from emp where sal>1000) newtable;

• 写在创建表的时候
  create table emp_20 as (select * from emp where deptno=20);
  ###关联查询

• 同时查询多张表的查询方式称为关联查询

1. 查询每一个员工的名称和其对应的部门名称
   select e.ename,d.dname
   from emp e,dept d
   where e.deptno=d.deptno;
2. 查询在new york工作的所有员工的信息
   select e.*
   from emp e,dept d
   where e.deptno=d.deptno and d.loc=‘new york’;
   ###笛卡尔积

• 如果关联查询不写关联关系则查询到的数据是两张表的乘积，这个乘积称为笛卡尔积
• 笛卡尔积是一种错误查询方式的结果，工作中切记不要出现
  ###等值连接和内连接

1. 等值连接：select * from A,B where A.x=B.x and A.age=18;
2. 内连接： select * from A join B on A.x=B.x where A.age=18;
   -1. 查询每一个员工的名称和其对应的部门名称
   select e.ename,d.dname
   from emp e join dept d
   on e.deptno=d.deptno;
   ###外连接

• 使用外连接查询得到的数据是除了两张表的交集数据以外和另外一张主表的全部数据，哪个表为主键通过 left/right控制 left是以join左边表为主表 right以join右边的表为主表

1. 查询所有员工的名称和其对应的部门名称
   select e.ename,d.dname
   from emp e left/right join dept d
   on e.deptno=d.deptno;

###关联查询总结：

• 关联查询的查询方式： 等值连接、内连接、外连接
• 如果查询两张表的交集数据使用 等值连接或内连接（推荐）
• 如果要查询某一张表的全部数据和另外一张表的交集数据此时使用外连接

###练习

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

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

3. 每种工作的平均工资

4. 每年的入职人数

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

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

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

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

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

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

11. 最后入职的员工信息

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

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

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

15. DALLAS 市所有的员工信息

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

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

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

19. 员工和他所在部门名

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

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

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

###关联关系之表设计

• 外键： 用来建立关系的字段称为外键
• 主键： 用来表示数据唯一性的字段称为主键
  ####一对一
• 有AB两张表，A表中的一条数据对应B表中的一条数据同时B表一条对应A表一条，这种关系称为一对一
• 应用场景：商品表和商品详情表，
• 如何建立关系： 在从表中添加外键，外键的值指向主表的主键
• 练习：请设计表保存以下数据

1. 用户名：wukong 密码：123456 昵称：齐天大圣 电话：13733666633 地址：花果山
2. 用户名：bajie 密码：abcd 昵称：二师兄 电话：13833446622 地址：高老庄
3. 用户名：libai 密码：aabbcc 昵称：李白 电话：13355668877 地址：语文书里
   create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
   create table userinfo(userid int,nick varchar(10),tel varchar(15),address varchar(20));
   insert into user values(null,‘wukong’,‘123456’),(null,‘bajie’,‘bacd’),(null,‘libai’,‘aabbcc’);
   insert into userinfo values(1,‘齐天大圣’,‘13833446622’,‘花果山’),(2,‘二师兄’,‘13833446622’,‘高老庄’),(3,‘李白’,‘13833446622’,‘语文书里’);

• 完成以下查询：

1. 查询李白的用户名和密码是什么
   select u.username,u.password
   from user u join userinfo ui
   on u.id=ui.userid
   where ui.nick=‘李白’;
2. 查询每一个用户的所有信息
   select *
   from user u join userinfo ui
   on u.id=ui.userid;
3. 查询用户名bajie 的昵称是什么
   select ui.nick
   from user u join userinfo ui
   on u.id=ui.userid
   where u.username=‘bajie’;
   ####一对多

• AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条数据，称为一对多
• 应用场景： 员工-部门， 商品-分类
• 如何建立关系： 在多的一端添加外键指向另外一张表的主键
• 练习：创建表保存以下数据 t_emp t_dept

1. 悟空 28岁 3000月薪 神仙部 花果山
2. 刘备 34岁 8000月薪 三国部 蜀国
3. 路飞 18岁 1000月薪 海贼部 日本
4. 八戒 30岁 4000月薪 神仙部 花果山
   create table t_emp(empno int primary key auto_increment,ename varchar(10),age int,sal int,deptno int);
   create table t_dept(deptno int primary key auto_increment,dname varchar(10),loc varchar(10));
   insert into t_dept values(null,‘神仙部’,‘花果山’),(null,‘三国部’,‘蜀国’),(null,‘海贼部’,‘日本’);
   insert into t_emp values(null,‘悟空’,28,3000,1),(null,‘刘备’,34,8000,2),(null,‘路飞’,18,1000,3),(null,‘八戒’,30,4000,1);

• 做题：

1. 查询每个员工的姓名和部门名
   select e.ename,d.dname
   from t_emp e join t_dept d
   on e.deptno=d.deptno;
2. 查询工作在花果山的员工姓名及工资
   select e.ename,e.sal
   from t_emp e join t_dept d
   on e.deptno=d.deptno
   where d.loc=‘花果山’;

####多对多

• AB两张表，A表中一条数据对应B表中多条数据同时B表中一条数据对应A表中多条，称为多对多
• 应用场景： 老师-学生 用户-角色
• 如何建立关系：需要创建新的关系表，表中添加两个外键，指向两个主表的主键
• 练习：创建表保存以下数据

1. 唐僧的学生有：悟空，传奇哥
2. 苍老师的学生有： 传奇哥，克晶姐
   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,‘苍老师’);
   insert into student values(null,‘悟空’),(null,‘传奇哥’),(null,‘克晶姐’);
   insert into t_s values(1,1),(1,2),(2,2),(2,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=‘苍老师’;

• 查询传奇哥的老师姓名
  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=‘传奇哥’
  ###表设计之权限管理案例

• 创建三张主表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);

• 保存以下数据：
  用户表：刘德华，貂蝉
  insert into user values(null,‘刘德华’),(null,‘貂蝉’);
  角色表：男游客，男管理员，女游客，女会员
  insert into role values(null,‘男游客’),(null,‘男管理员’),(null,‘女游客’),(null,‘女会员’);
  权限表：男浏览，男发帖，男删帖，女浏览，女发帖
  insert into module values(null,‘男浏览’),(null,‘男发帖’),(null,‘男删帖’),(null,‘女浏览’),(null,‘女发帖’);
  关系：男游客->男浏览；男管理员->男浏览，男发帖，男删帖；女游客-》女浏览；女会员-》女浏览，女发帖
  刘德华-》男管理员和女游客
  貂蝉-》女会员和男游客
  insert into r_m values(1,1),(2,1),(2,2),(2,3),(3,4),(4,4),(4,5);
  insert into u_r values(1,2),(1,3),(2,4),(2,1);

• 练习：

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 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
   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=‘男浏览’;

#######视图

• 什么是视图： 数据库中表和视图都是其内部的对象，视图可以理解成一张虚拟的表，视图本质上就是取代了一段SQL查询语句
• 为什么使用视图： 因为有些数据的查询需要书写大量SQL语句 每次书写浪费时间，使用视图可以起到SQL语句重用的作用,可以隐藏敏感信息
• 创建视图格式：
  create view 视图名 as 子查询; 创建出来一个虚拟的表
  create table 表名 as 子查询; 创建出来一张新表

1. 创建一个10号部门员工的视图
   create view v_emp_10 as (select * from emp where deptno=10);
   select * from v_emp_10;
2. 创建每个部门的工资总和，平均工资，最大工资，最小工资的视图
   create view v_emp_info as (select deptno,sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno);
3. 创建一个没有工资的员工表视图
   create view v_emp_nosal as (select empno,ename,deptno,hiredate,comm,job from emp);
   ###视图的分类
4. 简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询 的视图称为简单视图，简单视图可以对表中数据进行增删改查操作。
5. 复杂视图: 和简单视图相反，复杂视图只能进行查询。
   ####对简单视图进行增删改查 操作方式和table一样
6. 插入数据
   insert into v_emp_10 (empno,ename) values(10010,‘Tom’);

• 数据污染：往视图中插入一条视图中不可见，但是原表中存在的数据称为数据污染
  insert into v_emp_10 (empno,ename,deptno) values(10011,‘Jerry’,10);

• 通过 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) values(10012,‘Lucy’);报错
  insert into v_emp_20 (empno,ename,deptno) values(10012,‘Lucy’,20);//成功

• 修改和删除只能操作视图中存在的数据
  update v_emp_10 set sal=10000 where deptno=20;
  delete from v_emp_20 where deptno=10;
  delete from v_emp_20 where empno=10012;

• 修改视图

  create or replace view v_emp_20 as (select * from emp where deptno=20 and sal>2000);

• 删除视图
  drop view 视图名;
  drop view v_emp_10;

• 视图别名：如果创建视图的子查询中使用别名，则对视图进行增删改查时只能使用别名
  create view v_emp as (select ename name from emp);
  此时在视图中只能使用name 使用ename则报错
  ####视图总结：

1. 视图是数据库中的对象，代表一段sql语句，可以理解成是一个虚拟的表
2. 作用：重用sql，隐藏敏感信息
3. 分类：简单视图(不包含去重，函数，分组，关联查询)和复杂视图(只能进行查询)
4. 插入数据时的数据污染，通过with check option 解决
5. 删除和修改只能操作视图中存在的数据
6. 起了别名后只能用别名
   ###约束

• 什么是约束： 约束就是给表字段添加限制条件
  ####非空约束 not null

• 字段的值不能为null

  create table t1(id int,age int not null);
  -测试：
  insert into t1 values(1,20);//成功
  insert into t1 values(2,null);//失败
  ####唯一约束 unique

• 字段的值不能重复

  create table t2(id int,age int unique);
  -测试：
  insert into t2 values(1,20);//成功
  insert into t2 values(1,20);//失败
  ####主键约束 primary key

• 字段的值唯一且非空

####默认约束 default

• 给字段设置默认值

  create table t3(id int,age int default 20);
  -测试：
  insert into t3 (id) values (1);//默认值生效
  insert into t3 values (2,30);

###外键约束

• 外键：用来建立关系的字段称为外键
• 外键约束：添加外键约束的字段，值可以为null，可以重复，但是不能是关联表中不存在的数据，外键指向的数据不能先删除，外键指向的表不能先删除
• 如何使用外键约束

1. 创建部门表
   create table dept(id int primary key auto_increment,name varchar(10));
2. 创建员工表
   create table emp(id int primary key auto_increment,name varchar(10),deptid int,constraint fk_dept foreign key(deptid) references dept(id));
   -格式介绍：constraint 约束名称 foreign key(外键字段名) references 表名(字段名)
   -插入数据：
   insert into dept values(null,‘神仙’),(null,‘妖怪’);
   -测试：
   insert into emp values(null,‘悟空’,1);//成功
   insert into emp values(null,‘赛亚人’,3);//失败
   delete from dept where id=1;//失败
   drop table dept;//失败
   drop table emp;//成功

• 工作中除非特定场景一般不是用外键约束，因为添加约束后会影响测试效率，一般通过代码建立逻辑外键。

###索引

1805->mysql->day06->day06.zip 解压得到一个 item_backup.sql的文件 千万不要打开

• 什么是索引：索引是数据库中用来提高查询效率的技术，类似于目录

• 为什么使用索引：如果不使用索引，数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一个磁盘块，直到找到数据为止，使用索引后会将磁盘块以树状结构保存，查询数据时会大大降低访问的磁盘块数量，从而提高查询效率

####索引是越多越好吗？

• 索引会占用磁盘空间，只对常用的查询字段创建索引
  ####有索引就一定好吗？
• 如果表中数据量很少，添加索引反而会降低查询效率

####导入数据

1. 学生机 Linux系统 把item_backup.sql文件放到桌面
   在终端中执行以下指令：

    source /home/soft01/桌面/item_backup.sql;  
   

2. window系统 把item_backup.sql文件放到d盘根目录

    source d:/item_backup.sql;
   

   select * from item2 where title=‘100’;//耗时1.15秒

####如何创建索引 name varchar(10)

• 格式： create index 索引名 on 表名(字段(字符长度));

  create index index_item_title on item2(title);
  select * from item2 where title=‘100’; //耗时0.02秒
  ####如何查看索引

• show index from item2;

• 只要是给表添加主键约束，则数据库会为此表自动创建主键字段的索引。

####删除索引

• drop index 索引名 on 表名;
  drop index index_item_title on item2;
  ####复合索引
• 通过多个字段创建的索引称为复合索引
• 格式： create index 索引名 on 表名(字段1,字段2);
  create index index_item_title_price on item2(title,price);

####索引总结：

1. 索引是用来提高查询效率的技术，类似于目录
2. 因为索引会占用磁盘空间所以不是越多越好
3. 因为数据量小的表创建索引会降低查询效率所以不是有索引就一定好

###事务

• 数据库中执行SQL语句的工作单元，保证全部成功或全部失败
  ####事务的ACID特性
• Atomicity： 原子性， 最小不可拆分 全部成功全部失败
• Consistency: 一致性， 从一个一致状态到另外一个一致状态
• Isolation： 隔离性， 多个事务之间互不影响
• Durability：持久性，事务完成后数据提交到数据库持久保存
  ####事务相关SQL

1. 查看自动提交状态
   show variables like ‘%autocommit%’;
2. 修改状态
   set autocommit=0/1;
3. 提交
   commit;
4. 回滚
   rollback;
5. 保存回滚点
   savepoint s1;
6. 回滚到某个回滚点
   rollback to s1;
   ####group_concat()
   select deptno,group_concat(ename,’-’,sal) from emp group by deptno;
   ###面试题
   有个学生成绩表student， id 主键 name 姓名 subject学科 socre成绩
   保存以下数据：
   张三 语文 66，张三 数学 77，张三 英语 55，张三 体育 77
   李四 语文 59 ，李四 数学 88，李四 英语 78，李四 体育 95
   王五 语文 75 ，王五 数学 54，王五 英语 98，王五 体育 88
   create table student(id int primary key auto_increment,name varchar(10),subject varchar(5),score int);
   insert into student values(null,‘张三’,‘语文’,66),(null,‘张三’,‘数学’,77),(null,‘张三’,‘英语’,55),(null,‘张三’,‘体育’,77),(null,‘李四’,‘语文’,59),(null,‘李四’,‘数学’,88),(null,‘李四’,‘英语’,78),(null,‘李四’,‘体育’,95),(null,‘王五’,‘语文’,75),(null,‘王五’,‘数学’,54),(null,‘王五’,‘英语’,98),(null,‘王五’,‘体育’,88);
7. 查询每个人的平均分 从大到小排序
   select name,avg(score) a from student group by name order by a desc;
8. 每个人的名称 科目 成绩 一行显示出来
   select name,group_concat(subject,’-’,score) from student group by name;
9. 查询每个人的最高分和最低分
   select name,max(score),min(score) from student group by name;
10. 查询每个人不及格的科目以及分数 不及格的科目数量
    select name,group_concat(subject,’-’,score),count(*) from student where score<60 group by name;

优化数据库的方法
1、创建索引
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
2、复合索引
比如有一条语句是这样的：select * from users where area=‘beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
3、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
4、使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
5、排序的索引问题
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
6、like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
7、不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;
8、不使用NOT IN和<>操作
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。