打开数据库：
mysql -uroot -p
密码为空(回车)
exit
SQL规范：
1.以“；”结尾
2.关键字之间要有空格，可以有多个空格
3.SQL语句中可以有1个或多个换行
4.关键字不区分大小写
MySQL常用数据类型：
字符串型：varchar，char
大数据类型：blob，text
数值型：tinyint，smallint，int，bigint，float，double
逻辑型：bit
日期型：date，time，datetime，timestamp
数据库相关的SQL：
1.查询所有数据库：
show databases;
-创建数据库：
create database db1;
-删除数据库：
drop database db1;
-删除数据库时添加”如果存在”判断语句：
drop database if exists db1;
-查看数据库详情：
show create database db1;
-创建数据库时指定字符集：
create database db2 character set gbk;
-使用数据库：
use db1;
-创建数据库时增加校对规则：
create database mydb3 character set utf8 collate utf8_bin;
-查看mysql数据库存储位置：
show global variables like "%datadir%";
-查看当前使用的数据库：
select database();
-修改数据库：
alter database mydb3 character set gbk;
表相关SQL：执行表相关SQL需要先使用数据库（use db1）。
-什么是表：在关系型数据库中保存数据库的单元，类似于Excel中表，创建表的时候需要指定表的字段信息。
1.创建表：
-格式：create table 表名(字段名1 字段1类型，字段名2 字段2类型........);
create table person(name varchar(10),age int);
2.查看所有表：
show tables;
3.删除表：
drop table emp;
4.查看单个表信息：
show create table person;
5.创建表指定字符集和引擎：
create table student(name varchar(10),age int) engine=myisam charset=gbk;
6.查看表字段信息：
desc student;
修改表的相关SQL：
1.修改表名：
rename table 原名 to 新名;
2.修改表的引擎和字符集：
alter table t_stu engine=innodb charset=utf8;
3.添加表字段：
-在表的最后位置添加：
alter table t_stu add chinese int;
-在表的最前面添加：
-格式：alter table 表名 add 新字段名 类型 first;
alter table t_stu add id int first;
-在某个字段的后面添加：
alter table 表名 add 新字段名 新字段类型 after 老字段名;
4.删除表字段：
-格式：alter table 表名 drop 字段名;
5.修改表字段的名称和类型：
-格式：alter table 表名 change 老字段名 新字段名 新字段类型;
alter table t_stu change math english int;
6.修改字段类型和字段的位置：
-格式：alter table 表名 modify 字段名 新类型 first/after xxx;
alter table t_stu modify english int after name;
数据相关SQL：
create table emp(id int,name varchar(10),age int);
-插入数据：
-格式：insert into 表名values(值1，值2，值3......);
insert into emp values(1,'李白',18);
-指定字段插入格式：insert into 表名 (字段名1,字段名2) values (值1,值2);
insert into emp(id,name) values(2,'刘备');
insert into emp(name) values('张飞');
-批量插入数据格式：insert into 表名 values(值1,值2,值3...),(值1,值2,值3...),(值1,值2,值 3...);
insert into emp values(4,'貂蝉',88),(5,'吕布',18);
-指定字段批量：insert into 表名 (字段名1,字段名2) values (值1,值2),(值1,值2),(值1,值2);
insert into emp (id,name) values(10,'悟空'),(11,'八戒'),(12,'沙僧');
-查询数据：
-格式：select * from 表名;
select * from emp;
-指定字段查询：select id,name from emp;
-条件查询格式：select * from 表名where 条件;
select * from emp where id
-修改数据：
-格式：update 表名 set age=20 where id=1;
update emp set age=18 where id=4;
-删除数据：
-格式：delete from 表名 where 条件;
delete from emp where id=2;
-删除age为null的数据：
delete from emp where age is null;
MySQL表的引擎：
1.InnoDB：支持数据库的高级操作，如：事务，外键等。
2.MyIsam：只支持数据基础的增删改查
编码问题：
-在连接上数据库后执行 set names gbk; 把数据库的解码字符集修改成gbk。
约束：
-什么是约束：给表的字段添加限制条件。
-非空约束：not null
-添加非空约束后，字段值不能为null
create table t_null(id int,age int not null);
测试：
insert into t_null(1,null);(报错失败)
-唯一约束：unique
-添加唯一约束后，字段值不能重复
create table t_unique(id int,age int unique);
测试：
insert into t_unique values(1,18);(成功)
insert into t_unique values(2,18);(失败)
主键约束：
-什么是主键：表示表中数据唯一性的字段称之为主键。(主键只有一个)
-什么是约束：约束就是创建表的时候给表中字段添加的限制条件。
-主键约束：添加了主键约束的字段，保存的数值必须唯一而且非空。
-添加了主键约束，主键值不能为null，并且不能重复。
-格式：create table t1(id int primary key,name varchar(10));
-测试唯一：以下代码会报错，主键值不能重复。
insert into t1 values(1,”刘备”);
insert into t1 values(1,”张飞”);
-测试非空：
insert into t1 values(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;
主键+自增约束：
-当字段值赋值为null时，字段值会自动增长。
-删除数据数值不会减
-数值会从曾经的最大值基础上+1
-可以使用truncate使自增数值清零。
-格式：create table t2(id int primary key auto_increment,name varchar(10));
-测试非空：
insert into t2 values(null,”关羽”);
insert into t2 values(null,”悟空”);
insert into t2 values(10,”沙僧”);
insert into t2 values(null,”八戒”);
delete from t2 where id>5;
insert into t2 values(null,”二郎神”);
-自增的特点：
1.值为null时会自动赋值
2.以表中会出现的最大值为基础+1
3.删除数据自增值不减
4.delete清空表，自增数值不变
-如何让自增数值清零：使用truncate关键字，相当于drop+create。
truncate table t2;
默认约束：default
-给字段添加默认约束，当不对此字段赋值时，默认值生效
create table t_def (id int,age int default 10);
insert into t_def (id) values (1);
检查约束：check
-在mysql中check约束语法支持，但是不生效
create table t_check(id int,age int,check (age>10));
insert into t_check values(1,5);(成功)
外键约束：
-外键指用来建立两张表之间关系的字段，值指向另外一张表的主键，一张表主键只能有一个，但 是外键可以有多个。
-添加外键约束后，外键的值可以为null，可以重复，但是不能是关联表中不存在的数据。被关联 的表和数据不能直接删除，需要去掉约束或先删除关联数据。
-格式：
create table emp (id int,age int,deptid int,constraint 约束名 foreign key(deptid) references 表名(字段名));
-测试：
create database db6;
use db6;
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));
insert into dept values(null,'神仙部'),(null,'妖怪部');
insert into emp values(null,'孙悟空',1);(成功)
insert into emp values(null,'白骨精',2);(成功)
insert into emp values(null,'刘德华',3);(失败)
insert into emp values(null,'刘德华',null);(成功)
delete from dept where id=1;(失败)
drop table dept;(失败)
-可以通过 show create table 表名;查看约束信息
-删除外键约束
alter table emp drop foreign key fk_dept;
-myisam引擎不支持外键约束，要求两张表都是innodb引擎才能使用外键约束
-在java开发中，除非特定情况，一般不使用外键约束，因为会给测试带来不便，但是通常会通 过java代码所写的逻辑对插入的数据进行控制。
注释 comment：
-创建表的时候可以通过comment关键字对字段进行描述。
-格式：create table t3(id int primary key auto_increment comment'这个字段是主键',ename varchar(10),sal int comment '这是工资');
-通过 show create table t3; 查看字段的注释。
`和'的区别：
-`的作用：是用来修饰表名和字段名的，可以省略。
create table `t4`(id int,`age` int);
-'的作用：是用来修饰字符串的。
数据冗余：
-由于表设计不够合理，出现大量的重复数据，称为数据冗余。可以通过拆分表的形式，把可能重 复的数据保存到一张新的表中，在原表中只需要通过一个id建立关系即可。这种建立关系的字段 称之为外键。
-练习：保存商品的信息（包括商品id，商品名称，商品价格，库存，分类名称，上级分类）。
1.创建商品表(item)商品id，商品名称(name)，商品价格(price)，库存(num)，分类 id(category_id)。
create table item(id int primary key auto_increment,name varchar(10),price int,num int,category_id int);
2.创建分类表(category)：分类id，分类名称(name)，上级分类
create table category(id int primary key auto_increment,name varchar(10),parent_id int);
3.以上表中插入数据：电器分类下的电视机分类下的康佳电视价格4480，库存38。第二 条数据：鞋分类下的男鞋分类下的男运动鞋分类下的耐克运动鞋价格380，库存99。
insert into category values(null,'电器',null);
insert into category values(null,'电视机',1);
insert into item values(null,'康佳电视',4480,38,2);
事务：
-事务：事务是数据库中执行sql语句的最小工作单元，把多条sql语句放在事务中可以保证多条 sql，要么全部成功，要么全部失败。
-测试事务：
1.创建person表：create table person(id int primary key auto_increment,name varchar(10),money int);
2.插入数据：insert into person values(null,'超人',200),(null,'钢铁侠',1000);
-查看数据库的自动提交状态
show variables like '%autocommit%';
-关闭自动提交 0 打开自动提交 1：
set autocommit=0;
事务的ACID特性：**面试常考**
-Atomicity 原子性：最小，不可拆分，保证全部成功或全部失败。
-Consistency 一致性：从一个一致状态到另一个一致状态。
-Isolation 隔离性：多个事务之间互不影响。
-Durability 持久性：事务提交后数据持久保持到数据库中。
事务相关指令：
1.查看自动提交状态：
show variables like '%autocommit%';
2.修改自动提交状态：set autocommit=0/1;
3.提交：commit;
4.回滚：rollback;
5.设置回滚点：savepoint s1;
6.回滚到指定回滚点：rollback to s1;
7.set [session/global] transaction isolation level read uncommitted;
a.session：表示只修改当前mysql客户端的隔离级别。
b.global(默认)：表示只修改mysql服务器的隔离级别，当前窗口不生效。
数据库事务级别：
| Read uncommitted | 可能会出现脏读，不可重复读，幻读/虚读(安全级别最低，但是效率最高) |
| Read committed | 不可能出现脏读，可能出现不可重复读，幻读/虚读 |
| Repeatable read | 不可能出现脏读，不可重复读，有概率出现虚读/幻读(默认的) |
| serializable | 不可能出现脏读，不可重复读，幻读/虚读，它是一个串行化的隔离级别，在这个级别下所有线程都是单线程操作，不会出现多线程并发安全问题，但是会影响数据库效率。 |
8.查询数据库的隔离级别：
Select @@tx_isolation;
转账验证过程：
1.把自动提交关掉：set autocommit=0;
2.让超人+300：update person set money=500 where id=1;
3.此时打开另外一个终端use db2; 查询数据select * from person;(数据库中的数据此时并没有修 改)。
4.回到第一个终端窗口，让钢铁侠-300 update person set money=700 where id=2;（当前窗口 查询数据是因为查询的是内存中的数据，所以数据变了，但是此时去第二个窗口查看数据库中的 数据并没有发生改变）。
5.在第一个窗口中执行手动提交 commit; 此时会把内存中的多次SQL在内存中的执行结果，同 时提交到数据库中（此时两个终端全部发生改变）。
事务回滚：
-rollback：此关键字会把数据库内存中的数据回滚到上次commit的点。
-savepoint：设置回滚点。
-格式：savepoint 标识;
-rollback to s1; 指定回滚到某个回滚点。
-测试步骤：1.先把超人money=100; 2.把超人money=200; 3.此时savepoint s1; 4.把超人 money=300; 5.此时rollback to s1;(此时数据会回滚到money=200)
事务的应用场景：
-当执行某一件事需要多行sql语句时，并且要求多行sql要么全部成功，要么全部失败。此时必 须使用事务，不然就会出现多行sql部分成功部分失败的情况。
事务总结：
1.查看自动提交状态：
show variables like '%autocommit%';
2.修改自动提交状态：
set autocommit=0/1;
3.手动提交：
commit;
4.回滚：
rollback;
5.设置回滚点：
savepoint s1;
6.回滚到某个回滚点：
rollback to sl;
SQL分类：
-DDL：Data Definition Language,数据定义语言，包括：create,drop,alter,truncate;不支持事 务。
-DML：Data Manipulation Language:数据操作语言，包括： insert,delete,update,select（DQL); 支持事务。
-DQL：Data Query Language：数据查询语言，只包括select。
-TCL：Transaction Control Language：事务控制语言，包括： commit,rollback,savepoint,rollback to等;
-DCL：Data Control Language：数据控制语言，用来分配用户权限相关的sql。
数据库的数据类型：
-整数：
-int(m)：4个字节，和java中的int一致。
-bigint(m)：8个字节，和java中的long一致。
-m代表显示长度，但是必须和zerofill关键字结合使用。
create table t_int(num int(10) zerofill);
insert into t_int values(18);
select * from t_int;
-浮点数类型：
-double(m,d)：m代表总长度，d代表小数长度。如：56.237 m=5 d=3
-decimal(m,d)：超高精度小数，需要涉及高精度运算时使用decimal
-字符串类型：
-char(m)：长度不可变。m=20 abc 此时所占长度为20 执行效率高，最大长度255.
-varchar：长度可变。m=20 abc 此时所占长度为3 节省空间，最大长度65535.
-text：长度可变，通常保存长度大于255的文本数据，最大值65535.
-日期类型：
-date：只保存年月日。
-time：只保存时分秒。
-datetime：年月日时分秒，默认值为null，最大值为9999-12-31.
-timestamp(时间戳)：年月日时分秒，默认值为当前时间，最大值为2038-01-19.
-练习：创建时间的表
create table t_date(d1 date,d2 time,d3 datetime,d4 timestamp);
insert into t_date values('2018-07-18',null,null,null);
insert into t_date values(null,null,'2008-08-08 18:08:12',null);
判断值是否为null：
1.查询奖金为null的所有员工信息：
select *from emp where comm is null;
2.查询没有上级领导(mgr)的员工信息：
select *from emp where mgr is null;
is not null：
1.查询emp表中有奖金的员工信息：
select * from emp where comm is not null;
别名：
1.查询emp表中所有姓名，把ename改成姓名
-以下三种写法，等效
select ename as '姓名' from emp;
select ename '姓名' from emp;
select ename 姓名 from emp;
去重 distinct：
1.查询所有员工从事的工作：
select distinct job from emp;
比较运算符：,>=,
-emp：comm奖金，job工作，sal工资，mgr领导编号，deptno部门编号。
-t_item：price单价，num库存。
1.查询工资小于等于1600的所有员工姓名和工资。
2.查询部门编号是20的所有员工姓名，职位job，部门编号。
3.查询职位是manager的所有员工的姓名和职位。
4.查询部门不是10号部门的所有员工的姓名和部门编号，使用两种方法。
5.查询t_item表中单价等于23的商品信息。
6.查询单价不等于8443的商品信息。
and和or：
-and和java中的&&效果一样
-or和java中的||效果一样
1.查询不是10号部门并且工资小于3000的员工信息。
select * from emp where deptno!=10 and sal
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之间的数值(包含x和y).
1.查询工资在2000和4000之间的员工信息：
select * from emp where sal>=2000 and sal
select * from emp where sal between 2000 and 4000;
like：
-用于模糊查询。
-_：代表单个未知字符
-%：代表0个或多个未知字符
-举例：
1.包含字符a：%a%
2.以a开头：a%
3.以a结尾：%a
4.第二个字符是a：_a%
5.倒数第三个字符是a：%a__
6.第二个字符是a，最后一个字符是b：_a%b
-案例：
1.查询标题中出现笔记本的商品标题
select title from t_item where title like '%笔记本%';
2.查询单价在50到200之间的得力商品(title中出现得力)
select * from t_item where price between 50 and 200 and title like '%得力%';
3.查询有图片的得力商品(image字段不为null)
select * from t_item where image is not null and title like '%得力%';
4.查询标题中不包含得力的商品信息
select * from t_item where title not like '%得力%';
5.查询有赠品的商品信息(在sell_point字段中包含赠字)
select * from t_item where sell_point like '%赠%';
排序：
-order by 写在where的后面，没有where写在最后。
-order by 后面写排序的字段名，可以写一个或多个。
-默认排序规则为升序，也可以指定规则：升序(asc),降序(desc)
-举例：select name,money from person where money>5000 order by money asc/desc;
1.查询所有员工的姓名和工资，按照工资降序排列
select ename,sal from emp order by sal desc;
2.查询所有的dell商品，按照单价降序排列
select title,price from t_item where title like '%dell%' order by price desc;
3.查询所有员工信息，按照部门编号降序，工资升序排列
select * from emp order by deptno desc,sal;
分页查询 limit：
-limit x,y：x代表跳过的条数，y代表查询的条数。
-举例：
查询第一页的10条数据：limit 0,10
查询第五页的8条数据：limit 32，8
查询第三页每页3条数据：limit (3-1)*3,3
1.查询所有商品的价格，价格升序，显示第二页，每页7条数据
select price from t_item order by price limit 7,7;
2.查询工资前三名的员工信息：
select * from emp order by sal desc limit 0,3;
3.查询20部门中工资最高的员工信息：
select * from emp order by sal desc limit 0,1;
4.查询得力商品中价格最便宜的商品名称和价格：
select title,price from t_item where title like '%得力%' order by price asc limit 0,1;
数值运算：+,-,*,/,7%2等效(mod(7,2))
1.查询员工姓名，工资，年终奖(工资*5)
select ename,sal,sal*5 年终奖 from emp;
2.查询商品单价，库存和总金额(单价*库存)
select price,num,price*num 总金额 from t_item;
日期相关函数：
-select 'helloworld';
-获取当前日期+时间：now()
select now();
-获取当前的日期：
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());
-练习：查询每位员工入职的年份：
select ename,extract(year from hiredate) from emp;
-日期的格式化：
-格式：date_format(日期,格式)
-%Y：代表四位年
-%y：两位年
-%m：两位月
-%c：一位月
-%d：日
-%H：24小时
-%h：12小时
-%i：分
-%s：秒
-测试：把now()转成 2018年07月17日 16点27分30秒：
select date_format(now(),'%Y年%m月%d日 %H点%i分%s秒');
-把非标准格式的时间转成标准格式：
str_to_date(非标准格式的时间，格式);
-测试：把15.08.2016 08:00:00 转成标准格式
select str_to_date('15.08.2016 08:00:00','%d.%m.%Y %H:%i:%s');
ifnull 函数：
-age=ifnull(x,y)：如果x的值为null，则age=y；x不为null，则age=x.
-练习：将emp表中奖金为null的改成0，使用ifnull
update emp set comm=ifnull(comm,0);
聚合函数：
-对多条数据进行统计查询
-求平均值：avg(字段名)
select avg (sal) from emp;
-最大值：max(字段名)
select max (sal) from emp;
-最小值：min(字段名)
select min (sal) from emp;
-总和：sum(字段名)
select sum (sal) from emp;
-练习：查询emp表中所有员工的平均工资，最大工资，最小工资，工资总和。要求：对查询内 容起别名。
select avg(sal) 平均工资,max(sal) 最大工资,min(sal) 最小工资,sum(sal) 工资总和 from emp;
-查询符合条件的条数：count(*)
select count(*) from emp where deptno=10;
字符串相关函数：
1.字符串拼接：concat(s1,s2) 结果等于s1s2
-案例：查询emp表中员工姓名，工资，在工资后面添加“元”
select ename,concat (sal,'元') from emp;
2.获取字符串的长度：char_length(str)
-案例：查询员工姓名和姓名的字符长度
select ename,char_length (ename) from emp;
3.获取字符串在另一个字符串中出现的位置
-格式一：instr(str,substr);
-格式二：locate(substr,str);
select instr('abcdefg','d'),locate('d','abcdefg');
4.转大写和转小写：
-格式：select upper('abc'),lower('Nba');
5.去两端空白：
-格式：select trim(' ab c ');
6.截取字符串：
-从左边截取：select left('abcdefg',2);
-从右边截取：select right('abcdefg',2);
-从某个位置截取：select substring('abcdefg',2,3); //bcd
7.重复：
-格式：select repeat('ab',3); //ababab
8.反转：
-格式：select reverse('abc'); //cba
9.替换：
-格式：select replace('abcde','c','m'); //abmde
10.插入：
-格式：select insert('abcdefg',3,2,'m'); //abmefg
分组查询：
-分组查询通常和聚合函数结合使用
-查询条件中每个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
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(*),sum(sal),avg(sal),min(sal) from emp where mgr is not null group by job order by count(*) desc,avg(sal);
-根据多个字段分组：
1.每个主管手下的人数：
select mgr,count(*) from emp where mgr is not null group by mgr;
2.查询每个部门每个主管手下的人数：
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后面写聚合函数的条件
1.查询每个部门的平均工资，只查询平均工资大于2000的：
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
3.查询每个部门名字里包含a的员工的平均工资，只显示平均工资高于2000的：
select deptno,avg(sal) c from emp where ename like '%a%' group by deptno having c>2000;
-sql中各个关键字的顺序：
select ... from 表名 where ... group by ... having ... order by ... limit ...;
-六个关键字的执行顺序：
from…where…group by…having…select…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 sum(sal),avg(sal) a,min(sal) n from emp where ename not like '%k%' group by deptno having n>1000 order by a;
3.查询部门编号是10，30的部门员工中每个职业中最高工资低于5000的职业名称，人 数平均工资，最高工资，根据人数排序如果人数一致根据最高工资降序排序：
select deptno,job,count(*),avg(sal),max(sal) a from emp where deptno in (10,30) group by job having a
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;
子查询：
-什么是子查询：嵌套在sql语句中的查询sql语句称之为子查询
-子查询可以有多层嵌套
-子查询可写的位置：
1.写在where和having后面作为查询条件的值
2.写在创建表的时候：
create table newemp as(select ename,sal from emp);
3.写在from后面，当成一张新表使用(必须有别名)：
select ename from (select * from emp) t1;
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.查询工资高于20号部门最高工资的所有员工的信息：
select * from emp where sal>(select max(sal) from emp where deptno=20);
5.查询和jones相同职位的员工信息：
select * from emp where job=(select job from emp where ename='jones') and ename! ='jones';
6.查询有员工的部门信息：
select * from dept where deptno in (select distinct deptno from emp);
7.查询有商品的分类信息(有商品指在商品表中出现的分类)：
select * from t_item_category where id in (select distinct category_id from t_item);
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.通过部门编号得到部门信息：
select * from emp 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.查询每个员工的姓名和对应的部门名称：
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
2.查询每个商品的标题，商品单价和对应的分类名称：
select item.title,item.price,c.name from t_item item,t_item_category c where item.category_id=c.id;
3.查询在new york工作的所有员工信息：
select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='new york';
4.查询所有的员工信息和对应的部门地点：
select e.*,d.loc from emp e,dept d where e.deptno=d.deptno;
5.查询员工姓名，员工工资，对应的部门名称，要求工资大于2000：
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>2000;
笛卡尔积：
-关联查询：如果不写关联关系，则查询结果为两张表的乘积，这个乘积称为笛卡尔积(提出这个 的哥们叫笛卡尔)。
-笛卡尔积是一种错误的查询结果，在工作中不能出现。
等值连接和内连接：
-这两种连接方式都是关联查询的查询方式，效果相同。
-等值连接：select * from A,B where A.x=B.x and A.y=xxx;
-内连接：select * from A join B on A.x=B.x where A.y=xxx;
-如果是自关联，在查询的时候可以把一张表当成两张表查询
1.查询在new york工作的所有员工信息：
select e.* from emp e join dept d on e.deptno=d.deptno where d.loc='new york';
外连接：
-格式：select * from A left/right join B on A.x=B.x where A.y=xxx;
1.左外连接：以join左侧表为主表，查询其所有数据和右侧表的交集数据。
2.右外连接：以join右侧表为主表，查询其所有数据和左侧表的交集数据。
-练习：
1.查询所有员工的名字和对应的部门名：
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;
表设计之 关联关系：
-一对一：有两张表A和B，A表中有一条数据对应B表中的一条数据称为一对一关系。
-应用场景：用户表和用户信息扩展表，商品表和商品信息扩展表。
-如何建立关系：在从表中添加一个外键字段，指向主表的主键
-练习：
1.创建用户和用户信息扩展表：
-创建用户表 user (id,username,password)
create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
-创建用户信息扩展表 userinfo (uid,nick,qq,phone)
create table userinfo(uid int,nick varchar(10),qq varchar(10),phone varchar(11));
2.在两个表中各插入三条数据(两个表之间的数据要有关系)：
-插入以下三条数据：
wukong admin 悟空 123456 13838383838
bajie admin 八戒 332211 13533555533
wujing admin 悟净 334422 13212212122
3.写SQL查询每个用户的所有信息(用户名 密码 昵称 qq 电话)：
select * from user u join userinfo ui on u.id=ui.uid;
4.查询名字叫悟空的用户名和密码：
select u.username,u.password from user u join userinfo ui on u.id=ui.uid where ui.nick='悟空';
5.查询用户名bajie的昵称：
select ui.nick from user u join userinfo ui on u.id=ui.uid where u.username='bajie';
-一对多：AB两张表，A表中一条数据对应B表中的多条数据，同时B表中的一条数据对应A 表中的一条数据，称为一对多。
-应用场景：员工表和部门表，分类表和商品表。
-如何建立关系：一对多两张表，在多的表里添加外键指向另外一个表主键。
-练习：
1.创建t_emp表 id,name,deptid t_dept表 id,name,loc
2.插入数据：
-部门表数据：神仙部 地址：天上 妖怪部 地址：盘丝洞
-员工表数据：孙悟空，猪八戒，蜘蛛精，白骨精
3.查询每个部门对应的员工信息：
select d.*,e.* from t_emp e right join t_dept d on e.deptid=d.id;
4.查询在盘丝洞工作的所有员工姓名：
select d.loc,e.name from t_emp e right join t_dept d on e.deptid=d.id where d.loc='盘丝洞';
5.查询孙悟空的部门名称和工作地点：
select e.name,d.name,d.loc from t_emp e right join t_dept d on e.deptid=d.id where e.name='孙悟空';
-多对多：AB两张表，A表中一条数据对应B表中的多条数据，同时B表中的一条数据对应A 表中的多条数据，称为多对多。
-应用场景：老师表和学生表。
-如何建立关系：需要创建单独的关系表，表中两个字段分别指向两个表的主键。
-练习：
1.创建三个表：
-学生表：student(id,name) 刘德华 宋祖英 高圆圆
-老师表：teacher(id,name) 苍老师 传奇老师
-关系表：t_s(tid,sid)
2.插入以下数据：苍老师对应的学生：刘德华，高圆圆 传奇老师对应的学生：宋祖英， 高圆圆：
insert into t_s values(1,1),(1,3),(2,2),(2,3);
3.查询每个老师对应的学生：
select t.name,s.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid;
4.查询高圆圆的老师都有谁：
select s.name,t.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where s.name='高圆圆';
5.查询苍老师的所有学生： select t.name,s.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where t.name='苍老师';
自关联：
-什么是自关联：在表中有一个外键字段指向当前表的主键值，称为自关联。
-应用场景：需要保存层级关系，但又不确定有多少层的时候使用自关联，如：员工的上级领导， 部门的上级部门，分类的上级分类。
-练习：查询每个员工的上级领导名称：
select e.ename,f.ename from emp e join emp f on e.mgr=f.empno;
连接方式和关联关系：
-连接方式：包括等值连接，内连接和外连接，是关联查询的查询方式。
-关联关系：包括一对一，一对多，多对多，是指设计表的时候两个表之间存在的逻辑关系。
-当查询存在关联关系的表时，使用某一种连接方式进行查询。
表设计案例：权限管理案例
- 包含5张表：用户表，角色表，权限表，用户角色关系表，角色权限关系表。
1.创建：
用户表：
create table t_user(id int primary key auto_increment,name varchar(10));
角色表：
create table t_role(id int primary key auto_increment,name varchar(10));
权限表：
create table t_module(id int primary key auto_increment,name varchar(10));
2.创建两张关系表：
create table u_r(uid int,rid int);
create table r_m(rid int,mid int);
3.插入数据：
用户：刘德华，张学友，凤姐
角色：男游客，男会员，女游客，女管理员
权限：男浏览，男发帖，女浏览，女发帖，女删帖
4.关系表中建立关系：
男游客：男浏览
男会员：男浏览，男发帖
女游客：女浏览
女管理员：女浏览，女发帖，女删帖
刘德华：男游客
张学友：男会员
凤姐：女管理员，男游客
insert into r_m values (1,1),(2,1),(2,2),(3,3),(4,3),(4,4),(4,5);
insert into u_r values (1,1),(2,2),(3,4),(3,1);
5.查询每个用户对应的角色：
select u.name,r.name from t_user u join u_r ur on u.id=ur.uid join t_role r on r.id=ur.rid;
6.查询每个用户对应的所有权限：
select u.name,m.name from t_user u join u_r ur on u.id=ur.uid join r_m rm on rm.rid=ur.rid join t_module m on m.id=rm.mid;
7.查询具有男发帖权限的用户有谁：
select u.name,m.name from t_user u join u_r ur on u.id=ur.uid join r_m rm on rm.rid=ur.rid join t_module m on m.id=rm.mid where m.name='男发帖';
8.查询张学友对应的权限：
select m.name from t_user u join u_r ur on u.id=ur.uid join r_m rm on rm.rid=ur.rid join t_module m on m.id=rm.mid where u.name='张学友';
视图：
-什么是视图：数据库中存在多种对象，表和视图都是数据库中的对象，视图本质上就是一段sql 查询语句，可以理解成是一张虚拟的表，视图没有自己独立的数据，数据会随着原表的改变而改 变。
-为什么使用视图：因为有些数据的查询需要书写大量的sql语句，每次书写比较麻烦，使用视图 可以起到SQL重用的作用，可以隐藏敏感信息。
-格式：create view 视图名 as(子查询);
-例：
create view v_emp_10 as(select * from emp where deptno=10);
select * from v_emp_10;
create view v_emp_nosal as(select ename,mgr,deptno from emp);
select * from v_emp_nosal;
-练习：
1.创建emp表部门是20并且工资小于3000的视图：
create view v_emp_deptno as(select * from emp where deptno=20 and sal
select * from v_emp_deptno;
2.创建emp表每个部门的工资总和，平均工资，最大工资，最小工资的视图：
create view v_emp_sum 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 (1001,'猪八 戒',10,8000);
-数据污染：往视图中插入一条视图中不可见，但是在原表中存在的数据，称为数据污染。如果需 要避免数据污染的出现，可以使用 with check option 关键字。
-例：create view v_emp_30 as(select * from emp where deptno=30) with check option;
-测试：
insert into v_emp_30 (empno,ename,deptno,sal) values (1003,'蝙蝠侠',30,8000);
(成功)
insert into v_emp_30 (empno,ename,deptno,sal) values (1004,'钢铁侠',20,3000);
(报错失败)
2.在视图中修改和删除数据：
-修改和删除不会出现类似数据污染的问题，修改和删除只能操作视图中存在的数 据
update v_emp_10 set sal=sal+5;(成功)
update v_emp_10 set sal=sal+1 where deptno=20;(失败)
delete from v_emp_10 where empno=1001;(成功)
delete from v_emp_10 where empno=1002;(失败)
修改视图：
-格式：
create or replace view 视图名 as 子查询;
create or replace view v_emp_10 as (select * from emp where deptno=10 and sal
删除视图：
-格式：drop view v_emp_10;
-检验：shwo tables;
视图别名：
-如果创建视图的子查询中使用了别名，则对视图进行的各种操作都必须使用别名。
create view v_emp_name as(select ename name,sal from emp);
update v_emp_name set sal=10 where name='猪八戒';(成功)
update v_emp_name set sal=10 where ename='猪八戒';(失败)
索引：
-什么是索引：索引是数据库中用来提高查询效率的技术，类似于字典中的目录
-为什么使用索引：如果不使用索引，数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一 个磁盘块，直到找到数据为止，使用索引后，磁盘块会以树状结构对数据进行保存，查询数据时 会大大降低磁盘块的访问数量，从而提高查询效率。
-索引原理图：
-索引是越多越好吗？
不是，因为索引会占用磁盘空间。对常用的，写在查询条件后面的字段创建索引。
-有索引一定好吗？
不是，如果数据量小，添加索引有可能会降低查询效率。
索引的分类(了解)：
1.聚集索引(聚簇索引，系统自动创建的)：一个表只有一个聚集索引，通过主键创建的索引称为 聚集索引，数据库会自动为添加了主键约束的表增加聚集索引。在聚集索引中保存着查询依据和 数据。
2.非聚集索引：通过非主键字段创建的索引称为非聚集索引，在非聚集索引中保存着查询依据和 地址(没有数据)。
导入数据：
-在db6数据库下，执行source命令
-学生机(linux)：
source /home/soft01/桌面/item_backup.sql
-windows：
source d:/item_backup.sql
-测试：
1.show tables; 看是否有item2表
2.select count(*) from item2; 看是否有172万条数据
3.select * from item2 where title='100'; 看查询时间是多少？1.05秒
如何创建索引：
-格式：create index 索引名 on 表名(字段名(长度));
create index index_item2_title on item2(title);
-验证：select * from item2 where title='100'; 看查询时间是多少？0.03秒
如何查看索引：
show index from item2;
删除索引：
drop index index_item2_title on item2;
select * from item2 where title='100'; 耗时0.01秒
复合索引：
-通过多个字段创建的索引称为复合索引
-应用场景：频繁使用多个字段作为查询条件的时候，可以为这几个字段创建复合索引。
-创建格式：
create index index_item2_title on item2(title,price);
索引总结：
1.索引是用来提高查询效率的节航速，类似于目录
2.因为索引会占用磁盘空间，所以不是越多越好
3.因为数据量小的表创建索引会降低查询效率，所以不是有索引就一定好
group_concat()：
select deptno,group_concat(ename,”:”,sal)from emp group by deptno;
在创建表的时候增加字符集：
create table t_user(id int primary key auto_increment,username varchar(20),password varchar(30),phone varchar(20),email varchar(20),birthday datetime)default charset=utf8;
备份、恢复数据库：
1. 备份数据库表中的数据：
cmd>mysqldump –u 用户名 –p 数据库名>路径:/文件名.sql
例：
Mysqldump –uroot –p mydb1>d:/1.sql
2. 恢复数据库：（注意：如果数据库已经删除，先创建数据库再恢复数据。）
方式一：在cmd中：
mysql –u 用户名 –p 数据库名
mysql –uroot –p mydb1
mysql –u root –p mydb1
方式二：在mysql客户端中
source 路径:/文件名.sql
source d:/1.sql
1491
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
