Linux 连接数据库
终端： mysql -uroot -p

#######Databases:

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 数据库名;

#######Table(前提选中数据库):
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;

5.查看表结构（表字段）
desc person;

6.删除表
drop table person;

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

#######修改表
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 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;(after xxx是一个整体）

######部分windows中文问题(将数据库中utf8改为gbk)
- 在命令行中执行 set names gbk;（需要在数据库里执行）
- 显示？的是因为数据库或表的字符编码为gbk 修改为utf8

######数据相关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),('张飞',18);
   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='关羽';
3.删除数据
   格式：delete from 表名 where age =10;
   删除表中所有数据： delete from 表名
   delete from hero where age=20;
   delete from hero where name='悟空';
4.修改数据
   格式：update 表名 set name = 'Tom' where age =20;
   update hero set age=100 where name='八戒';
   update hero set age=50;

######主键约束
  什么是主键 表示表中数据唯一性的字段  不能重复/不能为空
  添加主键约束的字段，值唯一且非空
  格式： create table emp(id int primary key,name varchar(10));
  insert into emp values(1,'Tom');
  Query OK, 1 row affected (0.02 sec)

  insert into emp values(2,'Jerry');
  Query OK, 1 row affected (0.00 sec)

  insert into emp values(2,'hsdjk');
  ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
  insert into emp values(null,'abc');
  ERROR 1048 (23000): Column 'id' cannot be null

######主键+自增
  格式：create table t1(id int primary key auto_increment,name varchar(10));
  1.当自增字段的值为null时会自动赋值并且数值+1
  2.可以指定赋值，接下来的数据的自增字段会自动添加1
  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;

######`和'的区别
  `是用来修饰表名和字段名的 可以省略
  '是用来修饰字符串的

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

######事务
  创建人物表
  create table person(id int primary key auto_increment,name varchar(11),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(零填充)
    5  123   00123
    create table t_int(num int(10) zerofill);
    insert into t_int values(123);//0000000123 
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:25:38','2018-05-15 17:25:30',null);
 

-练习：
1.创建db3并使用 如果之前存在的话先删除
mysql> create database db3;
Query OK, 1 row affected (0.00 sec)

mysql> use db3;
Database changed

2.创建emp员工表 有id(主键+自增) 姓名，入职日期(hiredate 类型为date)
mysql> create table emp(id int primary key auto_increment ,name varchar(10),hiredate date);
Query OK, 0 rows affected (0.02 sec)

3.在姓名的后面添加工资sal字段和年龄age字段
mysql> alter table emp add sal int after name;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table emp add age int after name;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

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
mysql> 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-22');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

5.修改表中第二位员工的姓名为周星驰
mysql> update emp set name='周星驰' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

6.创建员工信息表，表名type 字段有 id,name,loc(工作地点),并且在表中插入数据： 体育明星 大陆，歌星 港台 ，影星 大陆，法师 古代
mysql> insert into type values(1,'体育明星','大陆'),(2,'歌星','港台'),(3,'影星','大陆'),(4,'法师','古代');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

7.给员工表添加 typeid字段 并且修改表中数据 
       姚明-体育明星，周星驰-歌星，
       范冰冰-影星，貂蝉和武则天-法师
mysql> alter table emp add typeid int ;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update emp set typeid=1 where name='姚明';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update emp set typeid=2 where name='周星驰';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update emp set typeid=3 where name='范冰冰';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update emp set typeid=4 where name='貂蝉';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update emp set typeid=4 where name='武则天';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

8.修改法师的工资为8888
mysql> update emp set sal=8888 where typeid=4;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

9.查询年龄在30岁以上的员工姓名和工资
mysql> select (name,sal) from emp where age>30;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select name,sal from emp where age>30;
+-----------+------+
| name      | sal  |
+-----------+------+
| 姚明      | 2000 |
| 周星驰    | 8000 |
| 范冰冰    | 5000 |
| 武则天    | 8888 |
+-----------+------+
4 rows in set (0.00 sec)

mysql> delete from emp where typeid=3;
Query OK, 1 row affected (0.00 sec)

10.删除影星
mysql> delete from emp where typeid=3;
Query OK, 1 row affected (0.00 sec)

11.删除表 删除db3
mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)

mysql> drop database db3;
Query OK, 1 row affected (0.02 sec)

########eclipse数据库配置
Database Connections
new Mysql

选择jar包配置路径
jdbc:mysql://localhost:3306/db3?useUnicode=true&characterEncoding=UTF-8

Test Connection测试是否成功

new mysql ->open sql scrapbook
->选择对应jar包版本与数据库名称

###### is null
update person set age=50 where age is null;
1.查询没有上级领导的员工编号姓名和工资
select empno,ename,sal from emp where mgr is null;
2.查询emp表中没有奖金的员工姓名，工资，奖金
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.查询部门编号deptno是20的所有员工姓名ename，职位job和工资sal
select ename,job,sal from emp where deptno=20;
4.查询职位job是manager的所有员工姓名，职位，部门编号deptno
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;
7.查询t_item表中单价price等于23的商品信息
select * from t_item where price=23;
8.查询商品表中单价不等于8443的商品信息
select * from t_item where price!=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
1.查询员工从事的所有职业
select distinct job from emp;
2.查询有员工的部门编号
select distinct deptno from emp;

#######and 和 or
-and 和java中的&&效果一样
-or  和java中的||效果一样
1.查询20号部门工资高于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到28000之外的员工姓名和工资
  select * from emp where sal not between 2100 and 2800;
3.查询10号部门工资在2000到3000之间的员工信息
  select * from emp where sal between 2000 and 3000 and  deptno=10;

#######模糊查询 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 title like '%得力%' and image is not null;
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;//默认升序
    select ename,sal from emp order by sal desc;//降序
    select ename,sal from emp order by sal asc;//升序
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语句的最后
  每页10条 第1页
    limit 0,10
  每页10条 第3页
    limit 20,10
  每页5条 第8页
    limit 5*7,5
  每页12条 第3页
    limit 12*2,12
1.查询所有商品按单价升序排序 显示第二页 每页七条数据
   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,sal*5 from emp;
- 查询商品表中商品单价，库存，及总金额（单价*库存）
    select price,num,price*num 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(字段名/*)   null不统计进去
 - 查询工资大于等于3000的员工数量
    select count(ename) from emp where sal>=3000;//2
    select count(*) from emp where sal>=3000;//2
    select count(sal) from emp where sal>=3000;//2
    select count(mgr) from emp where sal>=3000;//1

######日期相关函数
   select 'helloworld!';//测试方式

  - 获取当前的年月日时分秒  now()
   select now();
   update emp set hiredate=now() where empno=xxx;
  - 获取当前年月日   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 秒
1.把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.字符串的拼接 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');
   - 格式2：locate(substr,str);
   select locat('d','abcdeg');

4.插入字符串 insert(str,start,length,newstr);
   select insert('abcdefg',3,2,'m');

5.转大写转小写 upper(str) lower(str)
   select upper('abc'),lower('NBA');

6.去空白   trim(str)
   select trim('  a b c   ');

7.截取字符串   left(str,start) right(str,start)
   select left('abahdkaj',2);
   select right('absjsh',2);
   - substring(str,start,[length]);  //length可以省略
   select substring('shajkda',2);  //截取到最后
   select substring('shadjkh',2,3); //从第二个开始截取 截取三个

8.重复repeat(str,count)
   select repeat('ab',2);

9.替换replace(str,old,new);
   select replace('abcderf','de','m');

10.反转reverse(str)
   select reverse('abc');

########数字相关
 - 向下取整 floor(num)
    select floor(3.84);
 - 四舍五入 round(num)
    select round(4.5);
 
 - 四舍五入 round(num,m) m代表小数位数
    select round(23.879,2);
 - 非四舍五入 truncate(num,m) m代表小数位数
    select truncate(23.879,2);

 - rand()随机数 0-1随机数
    select floor(rand()*9);
    0-8随机数
    select floor(rand()*6)+5;
    5-10

########分组查询
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 asc;
  
-各种关键字顺序  where order by limit group by
select * from 表名 where ...... group by ......having....... order by ...... limit ......

###### having
- having 一般要结合分组查询和聚合函数的使用，用于给聚合函数的内容添加条件
- 聚合函数的条件不能写在where后面
- 普通字段的条件写在where后面，聚合函数的条件写在having后面
1.查询每个部门的平均工资 要求平均工资大于 2000
  - 以下是错误的：
  select deptno,avg(sal) a from emp where a>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 asc;

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 asc,s desc;

7.查询emp表中每年入职的人数（提高题）
  select extract(year from hiredate) year,count(*) from emp group by year;
8.查询每个部门的最高平均工资（提高题）
  select deptno,avg(sal) a from emp  group by deptno order by a desc limit 0,1;
  - 查询每个部门最高平均工资的部门编号
  select deptno from emp group by deptno order bt avg(sal) desc limit 0,1;

########子查询(嵌套查询)
1.查询emp表中工资最高的员工信息
  select max(sql) from emp; 
  select * from emp where sal=5000;
  - 把上面两条sql合并在一起
  select*from emp where sal=(select max (sal) from emp);
  //select * from emp order by sal desc limit 0,1;
2.查询emp表中工资大于平均工资的所有员工的信息
  select * from emp where sal>(select avg(sal) from emp);
3.查询工资高于20号部门最大工资的员工信息
  select * from emp where sal>(select max(sal) from emp where deptno=20);
4.查询和Jones相同工作的其他员工信息 
  select * from emp where job=(select job from emp where ename='Jones') and ename !='Jones';
5.查询工资最低的员工的同事们的信息（同事=相同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);  
//select * from emp where job=(select job from emp having min(sal));

6.查询最后入职的员工信息
  select * from emp where hiredate=(select max(hiredate) from emp);
7.查询名字为King的部门编号和部门名称（需要用到dept表）
  select deptno,dname from dept where deptno = (select deptno from emp where ename='king');
8.查询有员工的部门信息（编号和部门名称）(distinct去重，in(a,b,c))
  select deptno,dname from dept where deptno in(select distinct deptno from emp );
9.查询平均工资最高的部门信息 (最大难度)
  - 最大平均工资
  - 通过最大平均工资找到部门编号
  - 通过部门编号查询部门详情
  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));

--子查询总结：
1.嵌套在SQL语句中的查询语句称为子查询
2.子查询可以嵌套n层
3.子查询可以写在哪些位置？
-写在where或having后面当作查询条件的值
-写在from后面当作一张新表   **必须起别名**
    select * from emp where sal>1000;
    select ename from (select * from emp where sal>1000) newtable;

-写在创建表的时候
    select * from emp where deptno=20;
    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 join dept d 
    on e.deptno=d.deptno; 

######关联查询总结：
 - 关联查询的查询方式：等值连接/内连接/外连接
 - 如果查询两张表的交集数据使用 等值连接或内连接（推荐）
 - 如果要查询某一张表的全部数据和另外一张表的交集数据则使用外连接

1.查询商品表和商品分类表 中每个分类名称对应的商品数量(有多少种商品)
  select c.name,count(*)  from t_item i join t_item_category c on i.category_id=c.id group by c.name;

2.查找每种分类库存总量最大的分类名称
  select sum(num) s from t_item group by category_id order by s desc limit 0,1;

  select category_id from t_item group by category_id having sum(num)=(select sum(num) s from t_item group by category_id order by s desc limit 0,1);
  
  select name from t_item_category where id in (select category_id from t_item group by category_id having sum(num)=(select sum(num) s from t_item group by category_id order by s desc limit 0,1));
3.查询每个城市员工的工资总和
  select d.loc l,sum(sal) s from dept d left join emp e on d.deptno = e.deptno group by d.loc;

######关联关系之表设计
 - 外键：用来建立关系的字段称为外键
 - 主键：用来表示数据唯一性的字段称为主键
####一对一
 - 有AB两张表，A表中的一条数据对应B表中的一条数据 同时B表一条对应A表一条，这种关系称为一对一
 - 应用场景：商品表和商品详情表
 - 如何建立关系：在从表中添加外键，外键的值指向主表的主键
 - 练习：请设计表保存以下数据
1.用户名：wukong  密码：12345678 昵称：齐天大圣 电话：13733666633
地址：花果山
2.用户名：bajie  密码：abcd 昵称：二师兄 电话：13833446622
地址：高老庄
3.用户名：libai  密码：aabbcc 昵称：李白 电话：13355668877
地址：语文书里
create database db4;
       use db4;
       delete from hero;
       drop table hero;
       create table hero (id int primary key auto_increment,username varchar(10),password varchar(10));
       insert into hero values(null,'wukong',12345678),(null,'bajie','abcd'),(null,'libai','aabbcc');
       use db4;
       create table h_item (userid int,nickname varchar(10),tel varchar(11),address varchar(10));
       insert into h_item values(1,'齐天大圣',13733666633,'花果山'),(2,'二师兄',13833446622,'高老庄'),(3,'李白',13355668877,'语文书里');
 - 完成以下查询：
1.查询李白的用户名和密码是什么
   select h.username,h.password from hero h join h_item  hi on h.id=hi.userid where hi.nickname='李白';
2.查询每一个用户的所有信息
   select (h.*,hi.省略)* from hero h join h_item hi on h.id=hi.userid;
3.查询用户名bajie的昵称是什么
   select hi.nickname from hero h join h_item hi on h.id=hi.userid where h.username='bajie';

####一对多
- AB 两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条数据，称为一对多
- 应用场景：员工-部门，商品-分类
- 如何建立关系：在多的一端添加外键指向另一张表的主键
- 练习：创建表保存以下数据   t_emp t_dept
1.悟空 28岁 3000月薪 神仙部 花果山
2.刘备 34岁 8000月薪 三国部 蜀国
3.路飞 18岁 1000月薪 海贼部 日本
4.八戒 30岁 4000月薪 神仙部 花果山
       use db4;
       create table t_emp (deptid int primary key auto_increment,username varchar(10),age varchar(10),sal int);
       create table t_dept(deptid int primary key auto_increment,dname varchar(10),loc varchar(10));
       desc t_emp;
       insert into t_emp values (null,'悟空','28岁',3000),(null,'刘备','34岁',8000),(null,'路飞','18岁',1000),(null,'八戒','30岁',4000);
       insert into t_dept values(null,'神仙部','花果山'),(null,'三国部','蜀国'),(null,'海贼部','日本'),(null,'神仙部','花果山');
- 做题：
1.查询每个员工的姓名和部门名
   select e.username,d.dname from t_emp e join t_dept d on e.deptid=d.deptid;
2.查询工作在花果山的员工姓名及工资
    select d.loc,e.username,e.sal from t_emp e join t_dept d on e.deptid=d.deptid where d.loc='花果山'; 

####多对多
 - AB两张表，A表中一条数据对应B表中多条数据同时B表中一条数据对应A表中多条，称为多对多
 - 应用场景：老师-学生   用户-角色
 - 如何建立关系：需要创建新的关系表，表中添加两个外键，指向两个主表的主键
 - 练习：创建表保存以下数据
1.唐僧的学生有：悟空，传奇哥
2.苍老师的学生有：传奇哥，克晶姐
       create table teacher (tchid int primary key auto_increment,tname varchar(10));
       create table student (stuid int primary key auto_increment,sname varchar(10));
       create table t_s(yid 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.sname from teacher t join t_s ts on t.tchid = ts.yid join student s on s.stuid=ts.sid where t.tname = '苍老师';
- 查询传奇哥的老师姓名
       select t.tname from teacher t join t_s ts on t.tchid=ts.yid join student s on ts.sid=s.stuid where s.sname='传奇哥';

#######表设计之权限管理案例
 - 创建三张主表user(id,name) role(id,name) module(id,name) 和两张关系表 u_r(uid,rid) (用户和角色) r_m(rid,mid) (角色和权限)
 - 保存以下数据：
用户表:刘德华,貂蝉
角色表:男游客,男管理员,女游客,女会员
权限表:男浏览,男发帖,男删帖,女浏览,女发帖
关系:男游客->男浏览,男管理员->男浏览,男发帖,男删帖;
     女游客->女浏览;女会员->女浏览,女发帖
刘德华->男管理员和女游客
貂蝉->女会员和男游客
       use db4;
       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,'女发帖');
       select * from role;
       select * from module;
       insert into u_r values(1,2),(1,3),(2,4),(2,1);
       insert into r_m values(1,1),(2,1),(2,2),(2,3),(3,4),(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 rm.rid=ur.rid join module m on m.id=rm.mid  ; 
2.查询刘德华的所有权限
       select u.name,m.name from user u join u_r ur on u.id=ur.uid  join r_m rm on rm.rid=ur.rid join module m on m.id=rm.mid 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 rm.rid=ur.rid join module m on m.id=rm.mid 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);
   select * from v_emp_info;
 3.创建一个没有工资的员工表视图
   create view v_emp_nosal as (select empno,ename,deptno,hiredate,comm,job from emp);      
 
#######视图分类
1.简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询的视图称为简单视图，简单视图可以对表中数据进行增删改查操作。
2.复杂视图：和简单视图相反，复杂视图只能进行查询。

#######对简单视图进行增删改查  操作方式和table一样
1.插入数据
   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,20);

######外键约束
- 外键：用来建立关系的字段称为外键
- 外键约束：添加外键约束的字段，值可以为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;//成功
  - 工作中除非特定场景一般不是用外键约束，因为添加约束后会影响测试效率，一半通过代码建立逻辑外键

面试题：
  var User={
     count:1,
      getCount:function(){
           return this.count;
      }
  };
  console.log(User.getCount());//1
  var func = User.getCount;
  console.log(func());//undefined

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

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

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特性  *****笔试****
- A：Atomicity:原子性，最小不可拆分 全部成功 全部失败
- C：Consistency:一致性，从一个一致状态到另外一个一致状态
- I：Isolation:隔离性，多个事务之间互不影响
- D：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,'-',floor(sal)) from emp group by deptno;

######面试题：
 有个学生成绩表student, id 主键 name姓名 subject学科 score成绩
保存以下数据：
张三 语文 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(10),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,'王五','体育',66);
1.查询每个人的平均分 从大到小排序
   select name,avg(score) from student group by name order by avg(score) desc; 
2.每个人的名称 科目 成绩 一行显示出来
   select name,group_concat('科目：',subject,'成绩：',score) from student group by name;
3.查询每个人的最高分和最低分
   select name,max(score),min(score) from student group by name;
4.查询每个人不及格的科目以及分数 不及格的科目数量
    select name,group_concat('科目：',subject,'成绩：',score),count(*) from student where score<60 group by name;