mysql数据库的sql语句的入门与提高-完整篇

最新推荐文章于 2024-02-04 17:53:39 发布

汐汐同学呀

最新推荐文章于 2024-02-04 17:53:39 发布

阅读量746

点赞数 2

文章标签： SQL语句 SQL指令 MySql数据库 SQL入门与实战

本文链接：https://blog.csdn.net/yk997575119/article/details/102306761

版权

进入10月,学校里每年一度的秋招又要开始了，正在准备找实习单位的我又把之前学过的sql回顾了一下，收获颇丰，这次我把之前跟着数据库老师学习的笔记和习题一同整理了一下，供大家一起学习，这里面有很多知识点的总结，也有配合的练习题，所以不管是小白还是有一定sql基础的朋友来说都可以看一看，当然，如果是大神的话肯定会觉得这些很简单，哈哈哈哈。废话不多说了，整理的东西比较多，如果大家发现错误欢迎大家指正，一起进步，/握手。
以下是我当时学习的总的摘要，在这我就直接把他当作一个小目录了,大家如果想要详细的了解个别的知识点去对应的天数里找就OK了。

day01

数据库相关SQL

查询所有 show databases;
创建数据库 create database db1 character set utf8/gbk
查看数据库详情 show create database db1;
删除数据库 drop database db1;
使用 use db1;

表相关SQL

创建 create table t1(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk;
查询所有表 show tables;
查看表详情 show create table t1;
查看表字段 desc t1;
修改表名 rename table t1 to t2;
修改引擎字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
添加表字段 alter table t1 add age int first/after xxx;
删除表字段 alter table t1 drop 字段名;
修改字段名和类型 alter table t1 change 原名新名新类型
修改类型和位置 alter table t1 modify 字段名新类型 first/after xxx;
删除表 drop table t1
删除并创建 truncate table t1

数据相关SQL

插入数据 insert into t1 (字段1，字段2，字段3) values(1,2,3),(1,2,3);
删除 delete from t1 where id=10;
修改 update t1 set age=18 where id=10;
查询 select * from t1;

day02

主键+自增 primary key auto_increment
注释 comment
`用于修饰表名和字段名 '修饰字符串
冗余：表设计不合理导致大量重复数据通过拆分表解决
SQL分类：DDL数据定义语言包括 create alter drop truncate 不支持事务，DML数据操作语言包括 insert update delete select(DQL) 支持事务，DQL数据查询语言只包括select ，TCL事务控制语言 DCL数据控制语言用于分配用户权限
数据类型

整数：int bigint(m) m代表显示长度结合 zerofill使用
浮点数：double(m,d) m总长度 d小数长度 decimal
字符串：char固定长度最大255 varchar可变长度最大65535 text可变长度最大65535
日期： date time datetime 默认null最大9999-12-31 ， timestamp 默认当前时间最大 2038-1-19
其它

day03

is null 和 is not null
别名
去重 distinct
比较运算符 > < >= <= = !=和<>
and 和 or
in 和 not in
not between x and y
模糊查询 like _单个未知 %0或多个未知
排序 order by 字段名 asc/desc,字段名 asc/desc;
分页查询 limit 跳过的条数,每页条数
数值计算 + - * / % mod
日期相关 now() date() time() date(now()) time(now()) extract(year/month/day/hour/minute/second from now()) %YymcdHhis date_format() str_to_date()
ifnull(x,y)
聚合函数 avg max min sum count
字符串相关 concat char_length instr locate insert upper lower left right substring trim replace repeat reverse

day04

数学相关 floor() round() truncate() rand()
分组 group by
having
子查询

写在where或having后面
from后面
创建表和创建视图的时候

关联查询：

等值连接 select * from A,B where A.x=B.x and A.age=18
内连接 select * from A join B on A.x=B.x where A.age=18
外链接 select * from A left/right join B on A.x=B.x where A.age=18

笛卡尔积

day05

关联关系

1对1 在从表中添加外键指向主表中的主键
1对多在多的表中添加外键指向另一张表的主键
多对多创建一个关系表添加两个外键指向另外两张表的主键
自关联：当前表外键指向当前表的主键查询时把一张表当成两张表

权限管理案例：用户表角色表权限表用户角色关系表角色权限关系表

day06

1.视图
2.视图的分类
3.对简单视图进行增删改查
4.各种约束及使用方法
5.索引的相关知识
6.事务的相关知识

----------------------------------------------- 正文开始 -----------------------------------------------

### day01

MySQL

数据库简介

之前通过流操作文件方式保存数据的弊端：

效率低
实现过程比较复杂,开发效率低
一般只能保存小量数据
只能保存文本

什么是DB

DataBase：数据库，数据库是一个文件集合

什么是DBMS

DataBaseManagementSystem:数据库管理系统(软件)，用于管理数据库文件，常见的DBMS有哪些？ Oracle MySQL DB2 SQLserver Sqlite

数据库的分类

关系型数据库
以表为数据库存储数据的单位，关系型数据库是经过数学理论验证可以保存现实生活中任何关系型的数据
非关系型数据库
主要应用在一些特定场景，解决某些具体问题比如解决高并发访问时的数据缓存，（redis数据库属于非关系型数据库，redis内部以键值对的形式保存数据） name=xxx age=xxx

主流关系型数据库的介绍

MySQL： Oracle公司产品， 08年被Sun公司收购，09年Sun公司被Oracle收购，开源产品，MySQL被收购后性能大幅提高，面临闭源风险，原MySQL程序员离开Oracle创建了MariaDB，MariaDB和MySQL区别不大市场占有率第一
Oracle：闭源产品价格最贵性能最高市场排名第二，拉里埃里森 32
DB2： IBM公司产品，闭源主要应用在IBM公司整套解决方案中
SqlServer：微软公司产品，闭源，主要应用在微软公司整套解决方案中
Sqlite：轻量级数据库安装包只有几十k，主要应用在嵌入式设备中或移动设备中

开源和闭源

开源：公开源代码，靠卖服务盈利，有大牛程序员无偿维护升级
闭源：不公开源代码，靠卖产品和服务盈利，有大牛攻击找漏洞

什么是SQL

Structured Query Language：结构化查询语言，用于程序员和DBMS（数据库管理软件）进行交互

连接数据库

Linux系统： mysql -uroot -p
Windows系统：开始菜单-》所有程序-》MySQL/MariaDB-》MySQL Client

数据库相关的SQL

查看所有数据库
show databases;
创建数据库
格式：create database 数据库名称;
create database db1;
删除
格式：drop database 数据库名称;

练习：创建数据库 db1 db2 db3 db4 db5 然后再一个个删除

查看数据库详情
格式：show create database 数据库名称;
show create database db1;
创建数据库指定字符集
格式：create database 数据库名称 character set utf8/gbk;
create database db2 character set gbk;
使用数据库
格式: use 数据库名称
use db1;

练习：创建mydb1 字符集为gbk ，并使用，再创建mydb2 字符集为utf8，查看详情，然后使用，然后删除mydb1和mydb2
create database mydb1 character set gbk;
use mydb1;
create database mydb2 character set utf8;
show create database mydb2;
use mydb2;
drop database mydb1;
drop database mydb2;

表相关SQL

什么是表：表是数据库中保存数据的单位

创建表
格式： create table 表名 (字段1名字段1类型, 字段2名字段2类型,…);
创建一个person表保存名字、性别、年龄、工资
create table person(name varchar(10),gender varchar(5),age int,sal int);
查看所有表
show tables;

练习1：创建数据库mydb1 并使用在mydb1里面创建学生(student)表,保存姓名（name），年龄（age），语文成绩（chinese），数学成绩（math），英语成绩（english）
练习2：创建数据库mydb2 字符集为gbk 并使用，在里面创建book表，字段有书名（book_name）作者（author）页数（page）价格(price)

查看表详情格式： show create table 表名; show create table person;
创建表指定引擎和字符集格式： create table 表名 (字段1名字段1类型, 字段2名字段2类型,…) engine=innodb/myisam charset=gbk/utf8;
create table t1(nama varchar(10),age int) engine=myisam charset=gbk; show create table t1;
查看表字段信息格式： desc 表名; desc person;
修改表名格式： rename table 原名 to 新名; rename table person to t_person;
修改表引擎和字符集格式： alter table 表名 engine=myisam/innodb charset=gbk/utf8; alter table t_person engine=myisam charset=gbk;
添加表字段格式1： alter table 表名 add 字段名字段类型; 最后格式2： alter table 表名 add 字段名字段类型 first; 最前面
格式3： alter table 表名 add 字段名字段类型 after xxx; alter table t1 add gender varchar(5); alter table t1 add id int first; alter table t1 add sal int after age;
删除表字段格式： alter table 表名 drop 字段名 alter table t1 drop sal;
修改字段名称和类型格式： alter table 表名 change 原字段名新字段名新类型; alter table t1 change nama name varchar(5);
修改字段类型和位置格式： alter table 表名 modify 字段名新类型 first/after xxx alter table t1 modify name varchar(10) after age;
删除表 drop table 表名;

练习：

1.创建一个thero的英雄表，有id，name两个字段 create table thero (id int,name varchar(10));
2.修改表名为hero rename table t_hero to hero;
3.修改表引擎为myisam 字符编码为gbk alter table hero engine=myisam charset=gbk;
4.添加类型type字段在最后面 alter table hero add type varchar(10);
5.添加money字段在name的后面 alter table hero add money int after name;
6.删除id字段 alter table hero drop id;
7.修改type字段名称为herotype alter table hero change type herotype varchar(10);
8.修改money字段到最后面 alter table hero modify money int after hero_type;

SQL回顾

数据库相关SQL

查询所有 show databases;
创建数据库 create database db1 character set utf8/gbk;
查看详情 show create database db1;
删除 drop database db1;
使用 use db1;

表相关SQL

创建表 create table t1(name varchar(10),age int) engine=myisam/innodb charset=gbk/utf8;
查询所有表 show tables;
查询表详情 show create table t1;
查看表字段 desc t1;
修改表名 rename table t1 to t2;
修改表引擎字符集 alter table t1 engine=myisam/innodb charset=gbk/utf8;
添加表字段 alter table t1 add age int first/after xxx;
删除表字段 alter table t1 drop age;
修改表字段名称和类型 alter table t1 change age xxx 类型;
修改表字段的类型和位置 alter table t1 modify age 新类型 first/after xxx;
删除表 drop table t1;

数据相关SQL

create table hero(id int,name varchar(10),gender varchar(5));

插入数据
全表插入： insert into hero values(1,‘Tom’,‘Man’);
指定字段插入： insert into hero (name,gender) values(‘张飞’,‘男’);
批量插入数据： insert into hero values(5,‘唐僧’,‘男’),(6,‘悟空’,‘男’),(7,‘八戒’,‘男’); insert into hero (name) values(‘李雷’),(‘韩梅梅’),(‘Lucy’);
查询数据格式：select 字段信息 from 表名 where id<5; select * from hero;//查询所有数据的所有字段信息 select name,gender from hero; //查询所有的名字和性别 select * from hero where name=‘悟空’;//查询悟空的所有信息查询性别为Man的所有名字？ select name from hero where gender=‘Man’;
修改数据格式： update 表名 set gender=‘女’ where id=1; update hero set gender=‘女’ where id=1;
修改id小于5的性别为女 update hero set gender=‘女’ where id<5; 修改八戒的名字为悟能 update hero set name=‘悟能’ where id=7;
删除数据格式: delete from 表名 where 条件; delete from hero where name=‘张飞’;
删除id小于3的信息 delete from hero where id<3; 删除表中所有数据 delete from hero;
练习：创建员工emp表字段有id，name，sal，dept(部门) create table emp (id int,name varchar(10),sal int, dept varchar(10));
插入以下数据
insert into emp values(1,‘李白’,3000,‘打野部门’),(2,‘刘备’,4000,‘打野部门’),(3,‘诸葛亮’,6000,‘法师部门’),(4,‘周瑜’,5500,‘法师部门’),(5,‘程咬金’,2000,‘战士部门’);
查询打野部门的员工姓名 select name from emp where dept=‘打野部门’;
修改诸葛亮的名字为卧龙 update emp set name=‘卧龙’ where id=3;
修改程咬金的工资为3500 update emp set sal=3500 where id=5;
删除工资低于4000的员工 delete from emp where sal<4000;
删除打野部门的所有员工 delete from emp where dept=‘打野部门’;

数据库表的引擎

innodb:支持数据库的复杂操作，包括外键、事务等
myisam:只支持数据基础的增删改查操作
####字符编码问题
如果使用Windows电脑SQL语句中写中文报错的话通过以下指令解决 set names gbk;

作业：

创建数据库newdb1 并使用
create database newdb1;
use newdb1;
创建商品表t_item，字段有id和商品标题title 指定引擎为myisam字符集为gbk
create table t_item(id int,title varchar(10)) engine=myisam charset=gbk;
修改表名为item
rename table t_item to item;
添加价格price字段在最后
alter table item add price int;
添加分类category字段在title的后面
alter table item add category varchar(10) after title;
插入以下数据 1 苹果x 手机分类 5000，2 海尔滚筒洗衣机家电 3000，3 小米电视家电 2500， 4 双飞燕鼠标电脑配件 50，5 机械键盘电脑配件 150
insert into item values(1,‘苹果x’,‘手机’,5000),(2,‘海尔滚筒洗衣机’,‘家电’,3000),(3,‘小米电视’,‘家电’,2500),(4,‘双飞燕鼠标’,‘电脑配件’,50),(5,‘机械键盘’,‘电脑配件’,150);
查询单价低于2000的商品信息
select * from item where price<2000;
修改家电为家用电器
update item set category=‘家用电器’ where category=‘家电’;
删除价格低于100的商品
delete from item where price<100;
给表格添加库存num字段
alter table item add num int;
修改id小于3的商品库存为100
update item set num=100 where id<3;
删除表所有数据
delete from item;
删除表
drop table item;
删除newdb1
drop database newdb1;

练习：

创建数据库newdb2 指定字符集为utf8,并使用
create database newdb2 character set utf8;
use newdb2;
在数据库中创建员工emp表，有员工编号empno，姓名name，工资sal，部门dept，奖金comm
create table emp(empno int,name varchar(10),sal int,dept varchar(10),comm int);
插入刘关张和取经四人组共7个人刘关张为三国部取经四个人为取经部，刘关张工资为 8000，7000，5000，取经四个人按级别工资为800，700，600，500，奖金刘备和唐僧为500其它人员为null
insert into emp values(1,‘刘备’,8000,‘三国部’,500),
(2,‘关羽’,7000,‘三国部’,null),(3,‘张飞’,6000,‘三国部’,null),(4,‘唐僧’,800,‘取经部’,500),(5,‘悟空’,700,‘取经部’,null),
(6,‘八戒’,600,‘取经部’,null),(7,‘沙僧’,500,‘取经部’,null);
修改三国部的奖金为400
update emp set comm=400 where dept=‘三国部’;
修改悟空为齐天大圣
update emp set name=‘齐天大圣’ where name=‘悟空’;
修改唐僧为玉帝哥哥，并且工资改为1000
update emp set name=‘玉帝哥哥’,sal=1000 where name=‘唐僧’;
查询取经部的所有人的名字和工资
select name,sal from emp where dept=‘取经部’;
查询所有员工的名字、工资和奖金
select name,sal,comm from emp;
删除工资为5000块钱的员工信息
delete from emp where sal=5000;
查询奖金为null的员工有哪些
select * from emp where comm is null;

### day02

主键约束

主键：用于表示数据唯一性的字段称为主键
约束：就是对表字段的值添加限制条件
主键约束：保证主键的值唯一并且非空
格式： create table t1(id int primary key,name varchar(10));
测试： insert into t1 values(1,‘aaa’);
insert into t1 values(1,‘bbb’); //报错，不能重复
insert into t1 values(null,‘ccc’);//报错，不能为null
insert into t1 values(2,‘ddd’);

主键约束+自增

格式： create table t2(id int primary key auto_increment,name varchar(10));
测试： insert into t2 values(null,‘aaa’); 1
insert into t2 (name) values(‘bbb’); 2
insert into t2 values(10,‘ccc’); 10
insert into t2 values(null,‘ddd’); 11
delete from t2 where id>=10;
insert into t2 values(null,‘eee’); 12
delete from t2;
insert into t2 values(null,‘fff’); 13
自增数值只增不减，从历史最大值的基础上+1
truncate table t2; //删除表并创建新表自增数值清零

注释 comment

格式：
create table t3(id int primary key auto_increment comment ‘这是个主键’,name varchar(10) comment ‘这是名字’);
查看注释内容： show create table t3;

`和单引号的区别

:用于修饰表名和字段名 create tablet4(idint,name` varchar(10));
':用于修饰文本

数据冗余

如果表设计不合理，可能会出现大量的重复数据，这种现象称为数据冗余，通过拆分表的形式解决此问题
练习：创建表保存以下信息
保存集团总部下财务部里面的财务A部的张三工资8000 年龄18
保存集团总部下研发部的李四工资800 年龄75
需要保存的信息有：部门名员工姓名工资年龄
-考虑数据冗余问题所以创建两个表员工表和部门表
create table t_emp(id int primary key auto_increment,name varchar(10),sal int,age int,dept int);
create table t_dept(id int primary key auto_increment,name varchar(10),parent_id int);
-插入数据先插入部门表数据然后再插入员工表数据
insert into t_dept values(null,‘集团总部’,null),(null,‘财务部’,1),(null,‘财务A部’,2),(null,‘研发部’,1);
insert into t_emp values(null,‘张三’,8000,18,3),(null,‘李四’,800,75,4);
练习：分类category 价格price 库存num

保存家电分类下洗衣机分类下的海尔洗衣机价格2300，库存38
保存办公用品下，笔分类下的晨光圆珠笔价格5，库存100

创建表：
create table category(id int primary key auto_increment,name varchar(10),parent_id int);
create table item(id int primary key auto_increment,name varchar(10),price int,num int,category_id int);
插入数据：
insert into category values(null,‘家电’,null),(null,‘洗衣机’,1),(null,‘办公用品’,null),(null,‘笔’,3);
insert into item values(null,‘海尔洗衣机’,2300,38,2),(null,‘晨光圆珠笔’,5,100,4);

事务

事务(transaction)是数据库中执行同一业务多条SQL语句的工作单元，事务可以保证多条SQL语句全部执行成功或全部执行失败
验证事务：

创建表
create table user(id int primary key auto_increment,name varchar(10),money int,state varchar(5));
插入数据
insert into user values(null,‘李雷’,5000,‘正常’),(null,‘韩梅梅’,50,‘正常’),(null,‘Lucy’,10,‘冻结’);
李雷给Lucy转账SQL：
update user set money=4000 where id=1 and state=‘正常’;
update user set money=1010 where id=3 and state=‘正常’;
select * from user;

以上代码无事务保护，则导致数据库内部的数据李雷丢了1000块钱
以下操作有事务保护的步骤：
-开启事务：
begin;
-让李雷-1000
update user set money=3000 where id=1 and state=‘正常’;
-让Lucy+1000
update user set money=1010 where id=3 and state=‘正常’;
-因为一条成功一条失败所以不能把内存中的改变提交到磁盘中，所以需要执行回滚指令，执行之前可以打开另外一个窗口查看数据库中的数据是否发生改变（变的是内存中的数据，数据库里面的数据并没发生改变）
rollback;
转账成功执行流程：
1. 开启事务
  begin;
2. 让李雷-1000
  update user set money=3000 where id=1 and state=‘正常’;
3. 让韩梅梅+1000
  update user set money=1050 where id=2 and state=‘正常’;
4. 由于转账业务的多条SQL全部执行成功所以可以执行提交指令把内存中的改动提交到磁盘中
  commit;
和事务相关的SQL语句：

begin: 开启事务
rollback：回滚事务把内存中的改动清除
commit：提交事务把内存中的改动一次性提交到磁盘中

第二种开启事务的方式
数据库中事务默认是自动提交的

查看数据库自动提交的状态
show variables like ‘%autocommit%’;
修改自动提交的状态 0关闭 1 开启
set autocommit=0;
修改李雷钱为50000
update user set money=50000 where id=1;

savepoint：保存回滚点
set autocommit=1;
李雷开始是50000
begin;
update user set money=10000 where id=1;
savepoint s1
update user set money=20000 where id=1;
savepoint s2
update user set money=30000 where id=1;
rollback to s1;
总结事务相关指令：

begin开启事务
rollback 回滚事务
commit 提交事务
查看自动提交状态 show variables like ‘%autocommit%’
修改自动提交状态 set autocommit=0/1;
保存回滚点 savepoint s1;
回滚到回滚点 rollback to s1;

SQL分类

DDL Data Definition Language

数据定义语言
包括： create，alter，drop，truncate
不支持事务

DML Data Manipulation Language

数据操作语言
包括： insert delete update select（DQL）
支持事务

DQL Data Query Language

数据查询语言
只包括select

TCL Transaction Control Language

事务控制语言
包括：begin,rollback commit,savepoint xxx,rollback to xxx

DCL Data Control Language

数据控制语言
用于分配用户权限相关的SQL

数据类型

整数类型： int(m)对应java中的int， bigint(m)对应java中的long，m代表显示长度，需要结合 zerofill使用
create table t_int(id int,age int(8) zerofill);
insert into t_int values(1,28);
select * from t_int;
浮点数： double(m,d) m代表总长度，d代表小数长度 76.232(m=5 d=3),decimal超高精度小数，当涉及超高精度运算时使用。
字符串： char(m) 固定长度最大255 char(10) “abc” 所占长度10，varchar(m) 可变长度最大65535 varchar(10) “abc” 所占长度3，可变长度更节省空间，固定长度执行效率略高

varchar最大65535 但是建议保存255以内的长度，超过255使用text
text可变长度最大65535，

日期：

data：只能保存年月日
time：只能保存时分秒
datetime：保存年月日时分秒，最大值9999-12-31，默认值null
timestamp：保存年月日时分秒，最大值2038-1-19，默认值为当前时间
create table t_date(d1 date,d2 time,d3 datetime,d4 timestamp);
insert into t_date values(‘2018-11-15’,‘16:58:33’,null,null);
insert into t_date values (‘2018-11-15’,‘16:58:33’,‘2018-10-18 12:30:18’,null);

练习：

设计表保存以下数据
小明男 18岁工资3000 销售部北京无领导
小丽女 20岁工资4000 市场部上海无领导
小黄女 25岁工资1500 销售部北京上级领导小明
李白男 35岁工资800 市场部上海小丽
典韦男 33岁工资1500 市场部上海小丽
员工表：姓名性别年龄工资上级领导部门表：部门地点
create table emp(id int primary key auto_increment,name varchar(10),gender varchar(5),age int, sal int,parent_id int,dept_id int);
create table dept(id int primary key auto_increment,name varchar(10),loc varchar(10));
insert into dept values(null,‘销售部’,‘北京’),(null,‘市场部’,‘上海’);
insert into emp values(null,‘小明’,‘男’,18,3000,null,1),
(null,‘小丽’,‘女’,20,4000,null,2),
(null,‘小黄’,‘女’,25,1500,1,1),
(null,‘李白’,‘男’,35,800,2,2),
(null,‘典韦’,‘男’,33,1500,2,2);
查询市场部的所有员工姓名、性别和工资
select name,gender,sal from emp where dept_id=2;
查询工作地点在上海的部门名称
select name from dept where loc=‘上海’;
修改小丽的名字为丽丽
update emp set name=‘丽丽’ where name=‘小丽’;
查询25岁以下的女员工有谁
select * from emp where age<25 and gender=‘女’;
查询市场部的男员工姓名和工资
select name,sal from emp where dept_id=2 and gender=‘男’;
删除没有领导的男员工
delete from emp where parent_id is null and gender=‘男’;
指定字段插入三个员工的姓名和工资，刘备2000、关羽1500、张飞100
insert into emp (name,sal) values(‘刘备’,2000),(‘关羽’,1500),(‘张飞’,100);
删除工资低于1000的员工
delete from emp where sal<1000;
删除表里所有数据、删除表、删除库。
delete from emp;
drop table emp;
drop table dept;
drop database newdb3;

### day03

导入*.sql数据到数据库

window系统
source d:/tables.sql;
linux系统
source /home/soft01/桌面/tables.sql;
查询所有表看是否有四张表

is null 和 is not null

查询没有上级领导的员工编号，姓名，工资
select empno,ename,sal from emp where mgr is null;
查询emp表中没有奖金comm的员工姓名、工资、奖金
select ename,sal,comm from emp where comm is null;
查询emp表中有奖金的员工信息
select * from emp where comm is not null and comm>0;

别名

把查询到的员工姓名ename 改成名字
select ename as ‘名字’ from emp;
select ename ‘名字’ from emp;
select ename 名字 from emp;

去重 distinct

查询emp表中出现的所有职位job
select distinct job from emp;

比较运算符 >,<,>=,<=,=,!=和<>

查询工资小于等于1600的员工姓名和工资
select ename,sal from emp where sal<=1600;
查询部门编号是20的所有员工的姓名、职位job和部门编号deptno
select ename,job,deptno from emp where deptno=20;
查询职位是manager的所有员工姓名和职位
select ename,job from emp where job=‘manager’;
查询部门不是10号部门的所有员工姓名和部门编号（两种写法）
select ename,deptno from emp where deptno!=10;
select ename,deptno from emp where deptno<>10;
查询t_item表中单价price 等于23的商品信息
select * from t_item where price=23;
查询t_item表中单价不等于8443的商品标题title和单价
select title,price from t_item where price!=8443;

and 和 or

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

查询不是10号部门并且工资小于3000的员工信息
select * from emp where deptno!=10 and sal<3000;
查询部门编号为30号或者上级领导为7698的员工姓名，职位，上级领导和部门编号
select ename,job,mgr,deptno from emp where deptno=30 or mgr=7698;

in

当查询某个字段的值为多个的时候使用in关键字

查询emp表中工资为5000，1500，3000的员工信息
select * from emp where sal=5000 or sal=1500 or sal=3000;
-使用in:
select * from emp where sal in(5000,1500,3000);
查询emp表中工资不等于5000，1500，3000的员工信息
select * from emp where sal not in(5000,1500,3000);

between x and y 包括x和y

查询emp表中工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;
- between and
  select * from emp where sal between 2000 and 3000;
查询emp表中工资在1000到3000之外的员工信息
select * from emp where sal not between 1000 and 3000;

模糊查询 like

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

查询以a开头 like ‘a%’
以m结尾 like ‘%m’
第二个字符是a like ‘_a%’
第三个字符是x 倒数第二个字符是y like ‘_x%y’
倒数第三个字符是x like ‘%x__’
查询包含a like ‘%a%’

like案例

查询t_item表中标题中包含记事本的商品信息
select title from t_item where title like ‘%记事本%’;
查询单价低于100的记事本(title包含记事本)
select * from t_item where price<100 and title like ‘%记事本%’;
查询单价在50到200之间的得力商品(title包含得力)
select * from t_item where price between 50 and 200 and title like ‘%得力%’;
查询有图片的得力商品（有图片image字段不为null）
select * from t_item where image is not null and title like ‘%得力%’;
查询分类(category_id)为238，917的商品信息
select * from t_item where category_id in(238,917);
查询有赠品的商品信息（卖点sellpoint包含赠字）
select * from t_item where sell_point like ‘%赠%’;
查询标题中不包含得力的商品标题
select title from t_item where title not like ‘%得力%’;
查询价格在50到200以外的商品信息
select * from t_item where price not between 50 and 200;

排序 order by

格式 order by 字段名 desc降序/asc升序;

查询所有员工的姓名和工资，按照工资升序排序
select ename,sal from emp order by sal;
查询所有员工的姓名，工资和部门编号，按照部门编号降序排序
select ename,sal,deptno from emp order by deptno desc;

多字段排序： order by 字段名1 asc/desc,字段名2 asc/desc;

查询所有员工的姓名，工资和部门编号，按照部门编号降序排序,如果部门一致则按照工资升序排序
select ename,sal,deptno from emp order by deptno desc,sal;

分页查询 limit

格式： limit 跳过的条数,请求的数量
举例：

请求第一页20条数据 limit 0,20
请求第三页10条数据 limit 20,10
请求第5页 8条数据 limit 32,8
请求第四页 7条数据 limit 21,7

公式：limit((页数-1)*每页数量,每页数量)

练习：

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

数值计算 + - * / % 7%2 等效 mod(7,2)

查询员工姓名，工资，及年终奖(年终奖=工资5)
select ename,sal,sal5 年终奖 from emp;
查询t_item表中商品单价，库存和总金额(单价库存)
select price,num,pricenum 总金额 from t_item;
修每个员工的工资，每个人涨10块钱
update emp set sal=sal+10;
- 改回去
  update emp set sal=sal-10;

日期相关的函数

select 'helloworld'; (这句话就是简单的输出)

获取当前日期+时间 now()
select now();
获取当前的日期 curdate() cur(current当前)
select curdate();
获取当前时间 curtime()
select curtime();
从年月日时分秒中提取年月日和提取时分秒
select date(now());
select time(now());
- 查询商品上传的日期
  select date(created_time) from t_item;
  select time(created_time) from t_item;
从年月日时分秒中提取时间分量 extract(year from 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 extract(year from hiredate) from emp;
日期格式化

格式：
%Y 四位年 2018 %y两位年 18
%m 两位月 05 %c一位月 5
%d 日
%H 24小时 %h 12小时
%i 分
%s 秒
date_format(时间,格式)
举例：把时间默认格式转成年月日时分秒
select date_format(now(),’%Y年%m月%d日 %H时%i分%s秒’);
把非标准格式的时间转回默认格式 str_to_date(‘非标准格式的时间’,格式)
举例：把 14.08.2018 08:00:00 转成标准格式
select str_to_date(‘14.08.2018 08:00:00’,’%d.%m.%Y %H:%i:%s’);

ifnull(x,y)函数

age = ifnull(x,y) 如果x的值为null则age=y 如果x值不为null则age=x

把员工表中奖金为null的改成0 其它的不变
update emp set comm=ifnull(comm,0);

聚合函数

聚合函数用于对多行数据进行统计，平均值，最大值，最小值，求和，统计数量
平均值： avg(字段名称)
1. 查询所有员工的平均工资
  select avg(sal) from emp;
2. 查询10号部门的平均工资
  select avg(sal) from emp where deptno=10;
3. 查询戴尔商品的平均单价
  select avg(price) from t_item where title like ‘%戴尔%’;
最大值: max(字段名)
1. 查询所有员工中的最高工资
  select max(sal) from emp;
2. 查询30号部门中的最高奖金
  select max(comm) from emp where deptno=30;
最小值：min(字段名)
1. 查询20号部门的最低工资
  select min(sal) from emp where deptno=20;
2. 查询商品表中所有商品中最便宜的价格是多少
  select min(price) from t_item;
求和: sum(字段名)
1. 查询30号部门每个月需要发多少工资
  select sum(sal) from emp where deptno=30;
2. 查询戴尔商品的库存总量
  select sum(num) from t_item where title like ‘%戴尔%’;
统计数量： count(字段名)
1. 查询员工表中的员工数量
  select count(*) from emp;
2. 查询员工表中30号部门工资大于2000块钱的人数
  select count(*) from emp where deptno=30 and sal>2000;
  回顾：平均值avg() 最大值max() 最小值min() 求和sum() 统计数量count()

字符串相关

字符串拼接 concat(‘aa’,‘bb’) ->aabb

案例：查询emp表中员工姓名和工资工资后面显示单位元
select ename,concat(sal,‘元’) from emp;

获取字符串的长度 char_length(‘abc’) 3

案例：查询员工姓名和姓名的长度
select ename,char_length(ename) from emp;

获取字符串在另一个字符串出现的位置
-格式1：instr(str,substr)
select instr(‘abcdefg’,‘c’);
-格式2：locate(substr,str)
select locate(‘c’,‘abcdefg’);
插入字符串 insert(str,start,length,newstr)
select insert(‘abcdefg’,3,2,‘m’);
转大小写
- 格式： upper(str) lower(str)
  select upper(‘nba’),lower(‘NBa’);
去两端空白
select trim(’ a b ');
截取字符串
- left(str,num)
  select left(‘abcdefg’,2);
- right(str,num)
  select right(‘abcdefg’,2);
- substring(str,start,?length)
  select substring(‘abcdefg’,2,3);
重复 repeat
select repeat(‘ab’,2);
替换 replace
select replace(‘abcdefg’,‘c’,‘x’);
反转 reverse
select reverse(‘abc’);

### day04

数学相关函数

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

分组查询

将某个字段的相同值分为一组，对其它字段的数据进行聚合函数的统计，称为分组查询

查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
查询每个职位的最高工资
select job,max(sal) from emp group by job;
查询每个部门的人数
select deptno,count(*) from emp group by deptno;
查询每个职位中工资大于1000的人数
select job,count(*) from emp where sal>1000 group by job;
查询每个领导的手下人数
select mgr,count(*) from emp
where mgr is not null group by mgr;

多字段分组查询

查询每个部门每个主管的手下人数
select deptno,mgr,count(*) from emp
where mgr is not null group by deptno,mgr;
案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
select deptno,count(),sum(sal) from emp group by deptno
order by count(),sum(sal) desc;

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

having

having后面可以写普通字段的条件也可以写聚合函数的条件，但是不推荐在having后面写普通字段的条件
where后面不能写聚合函数的条件
having要结合分组查询使用

查询每个部门的平均工资，要求平均工资大于2000.
-错误写法（where后面不能写聚合函数）
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;
查询t_item表中每个分类category_id的平均单价要求平均单价低于100
select category_id,avg(price) a from t_item
group by category_id having a<100;
查询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 desc;
查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序
select deptno,count(*),avg(sal) a from emp group by deptno having a>2000 order by a desc;
查询emp表中不是以s开头，每个职位的名字，人数，工资总和，最高工资，高滤掉平均工资是3000的职位，根据人数升序排序如果人数一致则根据工资总和降序排序
select job,count(*) c,sum(sal) s,max(sal) from emp where job not like ‘s%’ group by job having avg(sal)!=3000 order by c,s desc;
查询emp表中每年入职的人数。
select extract(year from hiredate) year,count(*) from emp
group by year;
查询每个部门的最高平均工资（提高题）
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;
###子查询（嵌套查询）

查询emp表中工资最高的员工信息
select max(sal) from emp;
select * from emp where sal=5000;
-通过子查询把上面两条合并成一条
select * from emp where sal=(select max(sal) from emp);
查询emp表中工资大于平均工资的所有员工的信息
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);
查询工资高于20号部门最高工资的所有员工信息
select max(sal) from emp where deptno=20
select * from emp where sal>(select max(sal) from emp where deptno=20);
查询和jones相同工作的其他员工信息
select job from emp where ename=‘jones’
select * from emp where job=(select job from emp where ename=‘jones’) and ename!=‘jones’;
查询工资最低的员工的同事们的信息(同事=相同job)
-得到最低工资
select min(sal) from emp;
-得到最低工资哥们的工作
select job from emp where sal=(select min(sal) from emp);
-通过工作找到同事们还要排除最低工资那哥们儿
select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
查询最后入职的员工信息
select max(hiredate) from emp;
select * from emp where hiredate=(select max(hiredate) from emp);
查询员工king的部门编号和部门名称(需要用到dept表)
select deptno from emp where ename=‘king’;
select deptno,dname from dept where deptno=(select deptno from emp where ename=‘king’);
查询有员工的部门信息
-查询员工表中出现的部门编号
select distinct deptno from emp;
select * from dept where deptno in (select distinct deptno from emp);
查找平均工资最高的部门信息 (最大难度，需要考虑并列第一问题)
-得到最高平均工资
select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1;
- 通过平均工资找到部门编号
  select deptno from emp group by deptno having avg(sal)=(select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1);
- 通过部门编号查询部门信息
  select * from dept where deptno in(上面一坨);
select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)=(select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1));

子查询总结：

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

写在where或having后面作为查询条件的值
写在from后面当成一张表使用必须有别名
select * from emp where deptno=20;
select ename from (select * from emp where deptno=20) t1;
写在创建表的时候
create table newemp as (select ename,sal,deptno from emp where deptno=20);
###关联查询
同时查询多张表的查询方式称为关联查询

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

笛卡尔积

如果关联查询不写关联关系，则得到两张表结果的乘积，这个乘积称为笛卡尔积
笛卡尔积是错误的查询方式导致的结果，工作中切记不要出现

等值连接和内连接

这两种查询方式得到的结果是一样的

等值连接： select * from A,B where A.x=B.x and A.age=18;
内连接：select * from A join B on A.x=B.x where A.age=18;

查询每一个员工姓名和其对应的部门名称
select e.ename,d.dname
from emp e join dept d
on e.deptno=d.deptno;

外链接

查询A，B两张表的数据，如果查询两张表的交集数据使用内连接或等值连接，如果查询某一张表的全部数据另外一张表的交集数据则用外链接

左外链接： select * from A left join B on A.x=B.x where A.age=18;
右外链接： select * from A right join B on A.x=B.x where A.age=18;

查询所有员工和对应的部门名称
插入新数据
insert into emp (empno,ename,sal) values(10010,‘Tom’,500);
select e.ename,d.dname
from emp e left join dept d
on e.deptno=d.deptno;
查询所有部门名称和对应的员工姓名
select e.ename,d.dname
from emp e right join dept d
on e.deptno=d.deptno;

关联查询总结：

关联查询的查询方式：1. 等值连接 2.内连接 3.外链接（左外和右外）
查询两张表的交集用等值或内连接，推荐使用内连接
查询一张表的全部数据和另外一张表的交集数据使用外链接

练习：

select ..... from 表名 where ... group by .... having .... order by .... limit ....     (关键词的大概顺序)

每个部门的人数，根据人数降序排序
select deptno,count(*) c from emp group by deptno order by c desc;
每个部门中，每个主管的手下人数
select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;
每种工作的平均工资
select job,avg(sal) from emp group by job;
每年的入职人数
select extract(year from hiredate) year,count(*) from emp
group by year;
少于等于3个人的部门信息
select * from dept where deptno in (select deptno from emp group by deptno
having count(*)<=3);
拿最低工资的员工信息
select * from emp where sal=(select min(sal) from emp);
只有一个下属的主管信息
-得到每一个领导的下属人数
select mgr from emp where mgr is not null group by mgr having count()=1;
select * from emp where empno in(select mgr from emp where mgr is not null group by mgr having count()=1);
每月发工资最多的部门信息
-得到最多的钱数
select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1;
-通过钱数找到对应的部门编号
select deptno from emp group by deptno having sum(sal)=(select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1);
- 通过编号得到部门信息
  select * from dept where deptno in (上面一坨);
下属最多的人，查询其个人信息
- 得到最多的人数
  select count() from emp where mgr is not null group by mgr order by count() desc limit 0,1;
- 通过人数找到对应的领导编号
  select mgr from emp group by mgr having count()=(select count() from emp where mgr is not null group by mgr order by count(*) desc limit 0,1);
- 通过得到的领导编号查询其个人信息
  select * from emp where empno in(上面一坨);
拿最高工资员工的同事信息
- 最高工资
  select max(sal) from emp;
- 找到最高工资员工的工作
  select job from emp where sal=(select max(sal) from emp);
- 找到相同工作的人
  select * from emp where job=(select job from emp where sal=(select max(sal) from emp)) and sal!=(select max(sal) from emp);
和最后入职的员工在同一部门的员工信息
张三李四王五赵六
- 最后入职的时间
  select max(hiredate) from emp;
- 通过时间找到部门编号
  select deptno from emp where hiredate=(select max(hiredate) from emp);
- 通过部门编号找到部门内部的其它员工
  select * from emp where deptno in(select deptno from emp where hiredate=(select max(hiredate) from emp)) and hiredate!=(select max(hiredate) from emp);
查询平均工资高于20号平均工资的部门信息
-得到20号部门的平均工资
select avg(sal) from emp where deptno=20;
-查询每个部门的平均工资要求高于20号部门的
select deptno from emp group by deptno
having avg(sal)>(select avg(sal) from emp where deptno=20);
-通过部门编号查询部门详情
select * from dept where deptno in(select deptno from emp group by deptno
having avg(sal)>(select avg(sal) from emp where deptno=20));
查询员工信息和员工对应的部门名称
select e.*,d.dname
from emp e join dept d
on e.deptno=d.deptno;
查询员工信息，部门名称，所在城市
select e.*,d.dname,d.loc
from emp e join dept d
on e.deptno=d.deptno;
查询Dallas市所有的员工信息
select e.*
from emp e join dept d
on e.deptno=d.deptno
where d.loc=‘dallas’;
查询每个城市的员工人数（需要查询出波士顿0人）
select d.loc,count(e.ename)
from emp e right join dept d
on e.deptno=d.deptno
group by d.loc;
查询每个员工的名字和对应的主管名字
-如果表中建立关系的字段指向当前表的主键，则称为自关联，关联查询时把一张表当成两张表
select e.ename,m.ename
from emp e left join emp m
on e.mgr=m.empno;
查询每个员工的名字、对应的主管名字和对应的部门名字
select e.ename,m.ename,d.dname
from emp e left join emp m
on e.mgr=m.empno
join dept d
on e.deptno=d.deptno;
查询emp表中所有员工的姓名以及该员工的领导编号，领导姓名，领导职位
select e.ename,m.empno,m.ename,m.job
from emp e left join emp m
on e.mgr=m.empno;
查询emp表名字中没有字母k的所有员工的编号，姓名，职位，工资，部门名
select e.empno,e.ename,e.job,e.sal,d.dname
from emp e left join dept d
on e.deptno=d.deptno
where e.ename not like ‘%k%’;
查询dept表中所有部门的信息和与之关联的员工名字和工资，排除掉10号部门，根据部门编号降序排序，如果部门编号一致则按照工资升序排序
select d.*,e.ename,e.sal
from emp e right join dept d
on e.deptno=d.deptno
where e.deptno!=10
order by e.deptno desc,e.sal;

### day05

表设计之关联关系

什么是外键：主键是用于表示数据唯一性的字段，外键是用于建立关系的字段，值通常指向另外一张表的主键

一对一

什么是一对一关系：有A、B两张表，A表中一条数据对应B表中的一条数据，称为一对一关系
应用场景：用户表和用户信息扩展表，商品表和商品信息扩展表
如何建立关系：在从表中添加外键指向主表的主键
练习：创建user（id,username,password）和userinfo（uid,nick,age,phone）然后插入以下数据

create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
create table userinfo(uid int,nick varchar(10),age int,phone varchar(15));

1 libai admin 李白 28 13838383388
2 liubei admin 刘备 33 13222333322
3 guanyu 123456 关羽 30 13555333355

insert into user values(null,‘libai’,‘admin’),(null,‘liubei’,‘admin’),(null,‘guanyu’,‘123456’);
insert into userinfo values(1,‘李白’,28,‘13838383388’),
(2,‘刘备’,33,‘13222333322’),(3,‘关羽’,30,‘13555333355’);

查询每一个用户的用户名、昵称和电话
select u.username,ui.nick,ui.phone
from user u join userinfo ui
on u.id=ui.uid;
查询李白的用户名和密码
select u.username,u.password
from user u join userinfo ui
on u.id=ui.uid
where ui.nick=‘李白’;
查询guanyu的昵称
select ui.nick
from user u join userinfo ui
on u.id=ui.uid
where u.username=‘guanyu’;

一对多

什么是一对多：AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条对应A表中的一条数据
应用场景：员工表和部门表商品表和商品分类表
如何建立关系：在多的表中添加外键指向另外一张表的主键

练习：

创建 t_emp(id,name,sal,dept_id)和t_dept(id,name,loc)
create table t_emp(id int primary key auto_increment,name varchar(10),sal int,dept_id int);
create table t_dept(id int primary key auto_increment,name varchar(10),loc varchar(10));

保存以下数据：
1 诸葛亮 3000 法师部 A国
2 周瑜 2000 法师部 A国
3 关羽 1500 战士部 B国
4 张飞 1000 战士部 B国
5 孙尚香 5000 射手部 C国

insert into t_dept values(null,‘法师部’,‘A国’),(null,‘战士部’,‘B国’),(null,‘射手部’,‘C国’);
insert into t_emp values(null,‘诸葛亮’,3000,1),(null,‘周瑜’,2000,1),(null,‘关羽’,1500,2),(null,‘张飞’,1000,2),(null,‘孙尚香’,5000,3);

查询每个员工的名字和所在部门的名称
select e.name,d.name
from t_emp e join t_dept d
on e.dept_id=d.id;
查询A国的员工姓名
select e.name
from t_emp e join t_dept d
on e.dept_id=d.id where d.loc=‘A国’;
查询张飞的部门名称
select d.name
from t_emp e join t_dept d
on e.dept_id=d.id where e.name=‘张飞’;
查询工资在2000以下员工姓名，所在地
select e.name,d.loc
from t_emp e join t_dept d
on e.dept_id=d.id where e.sal<2000;

多对多

什么是多对多：AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条对应A表中的多条
应用场景：老师表和学生表
如何建立关系：创建一个关系表，两个外键字段，分别指向另外两张表的主键
练习：创建student(id,name) teacher(id,name) 和关系表 t_s(tid,sid)
create table student(id int primary key auto_increment,name varchar(10));
create table teacher(id int primary key auto_increment,name varchar(10));
create table t_s(tid int,sid int);
保存以下数据：
苍老师：小明小红小绿小蓝
范老师：小明小红
insert into student values(null,‘小明’),(null,‘小红’),(null,‘小绿’),(null,‘小蓝’);
insert into teacher values(null,‘苍老师’),(null,‘范老师’);
insert into t_s values(1,1),(1,2),(1,3),(1,4),(2,1),(2,2);

查询每个学生对应的老师
select s.name,t.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on ts.tid=t.id;
查询苍老师的学生有哪些
select s.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on ts.tid=t.id where t.name=‘苍老师’;
查询小红的老师是谁
select t.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on ts.tid=t.id where s.name=‘小红’;

自关联

当前表的外键指向当前表的主键，这种关联方式叫做自关联
应用场景：需要保存上下级关系时

查询员工姓名和对应的主管姓名
select e.ename,m.ename
from emp e join emp m
on e.mgr=m.empno;

连接方式和关联关系

连接方式：包括等值连接，内连接，外连接是指查询多张表数据时使用的查询方式
关联关系：包括一对一一对多多对多，是指设计表时，两张表之间存在的逻辑关系

表设计案例：权限管理

需要创建5张表，3张主表 2张关系表
create database db5 character set utf8;
use db5;
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 module (name) values(‘男浏览’),(‘男发帖’),(‘男删帖’),(‘女浏览’),(‘女发帖’),(‘女删帖’);
insert into role (name) values(‘男会员’),(‘男管理员’),(‘女游客’),(‘女会员’);
insert into user (name) values(‘刘德华’),(‘张学友’),(‘凤姐’);
insert into u_r values(1,1),(1,3),(2,2),(3,4),(3,1);
insert into r_m values(1,1),(1,2),(2,1),(2,2),(2,3),(3,4),(4,4),(4,5);

查询每个用户对应的权限有哪些
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;
查询凤姐的权限有哪些
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=‘凤姐’;
查询拥有男发帖权限的用户有谁
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=‘男发帖’;

面试题：

2019年小微收到了很多红包，有亲戚的红包，朋友的红包，同事的红包。红包有的是现金，有的是微信红包，支付宝红包。为了礼尚往来小微还发了一些红包给弟弟，妹妹，朋友，同事。请设计表帮小微统计:
提示：至少包含2张表(其中包含一张红包交易流水表，交易流水表中不能包含交易双方的姓名)
1.创建表
create table trade(id int primary key auto_increment,time date,money int,type varchar(5),pid int);
create table person(id int primary key auto_increment,name varchar(10),gender varchar(5),rel varchar(5));
插入人物数据：(这里插入任务数据和下面的流水数据都是测试用的，不是题目关键，不用写)
刘德华男亲戚，杨幂女亲戚，马云男同事，特朗普男朋友，
貂蝉女朋友
insert into person (name,gender,rel) values(‘刘德华’,‘男’,‘亲戚’),(‘杨幂’,‘女’,‘亲戚’),(‘马云’,‘男’,‘同事’),(‘特朗普’,‘男’,‘朋友’),(‘貂蝉’,‘女’,‘朋友’);
插入流水数据：
刘德华微信收1000 2018-03-20
杨幂现金收500 发50 2018-04-14
马云支付宝收20000 发5 2018-3-11
特朗普微信收2000 2018-05-18
貂蝉微信发20000 2018-07-22
insert into trade values(null,‘2018-03-20’,1000,‘微信’,1),
(null,‘2018-04-14’,500,‘现金’,2),
(null,‘2018-04-14’,-50,‘现金’,2),
(null,‘2018-3-11’,20000,‘支付宝’,3),
(null,‘2018-3-11’,-5,‘支付宝’,3),
(null,‘2018-05-18’,2000,‘微信’,4),
(null,‘2018-07-22’,-20000,‘微信’,5);
2. 查询2018年2月15号到现在的红包收益
select sum(money) from trade where time>str_to_date(‘2018年2月15号’,’%Y年%c月%d号’);
3. 查询2018年2月15号到现在金额大于100 所有女性亲戚的名字和金额
select p.name,t.money
from trade t join person p
on t.pid=p.id
where time>str_to_date(‘2018年2月15号’,’%Y年%c月%d号’)
and t.money not between -100 and 100
and p.gender=‘女’
and p.rel=‘亲戚’;加粗样式
4. 查询三个平台分别收入的红包金额
select type,sum(money) from trade
where money>0
group by type;

### day06

视图

什么是视图：数据库中的表和视图都是其内部的对象，视图可以理解成一张虚拟的表，视图本质就是取代了一条SQL查询语句。
为什么使用视图：因为有些数据的查询需要书写大量的SQL语句，每次书写效率太低，使用视图可以起到SQL重用的作用，视图可以隐藏敏感信息
创建视图的格式：
create view 视图名 as 子查询;
create table 表名 as 子查询
创建一个10号部门员工的视图
create view v_emp_10 as (select * from emp where deptno=10);
-从视图中查询数据
select * from v_emp_10;
创建一个没有工资的员工表视图
create view v_emp_nosal as (select empno,ename,comm,deptno from emp);
-查询 select * from v_emp_nosal

视图的分类

简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询。可以对视图中的数据进行增删改查操作
复杂视图：和简单视图相反，只能对视图中的数据进行查询操作

创建一个复杂视图
create view v_emp_info as (select avg(sal),max(sal),min(sal) from emp);
-查询 select * from v_emp_info;

对简单视图进行增删改查，操作方式和table一样

插入数据
insert into v_emp_10 (empno,ename,deptno) values(10011,‘刘备’,10);
select * from v_emp_10;
select * from emp;

如果插入一条在视图中不可见，但是原表中却可见的数据称为数据污染。
insert into v_emp_10 (empno,ename,deptno) values(10012,‘关羽’,20);
select * from v_emp_10;
select * from emp;
通过 with check option 解决数据污染问题
create view v_emp_20 as (select * from emp where deptno=20) with check option;
insert into v_emp_20 (empno,ename,deptno) values(10013,‘赵云’,20); //成功
insert into v_emp_20 (empno,ename,deptno) values(10014,‘黄忠’,30); //失败
修改和删除视图中的数据（只能修改删除视图中有的数据）
update v_emp_20 set ename=‘赵云2’ where ename=‘赵云’;
update v_emp_20 set ename=‘刘备2’ where ename=‘刘备’;//修改失败
delete from v_emp_20 where deptno=10;//没有数据被删除
创建或替换视图
create or replace view v_emp_10 as (select * from emp where deptno=10 and sal>2000);
删除视图
drop view 视图名;
drop view v_emp_10;
show tables;
如果创建视图的子查询中使用了别名则对视图操作时只能使用别名
create view v_emp_10 as (select ename name from emp where deptno=10);
select name from v_emp_10;//成功
select ename from v_emp_10;//失败

视图总结：

视图是数据库中的对象，代表一段SQL语句，可以理解成一张虚拟的表
作用：重用SQL，隐藏敏感信息
分类：简单视图（创建视图时不使用去重、函数、分组、关联查询，可以对数据进行增删改查）和复杂视图（和简单视图相反，只能对数据进行查询操作）
插入数据时有可能出现数据污染，可以通过with check option解决
删除和修改只能操作视图中存在的数据
起了别名后只能用别名

约束

什么是约束：约束就是给表字段添加的限制条件

主键约束+自增 primary key auto_increment

作用：唯一且非空

非空约束 not null

作用：该字段的值不能为null
create table t_null(id int,age int not null);
insert into t_null values(1,18); //成功
insert into t_null values(2,null);//失败

唯一约束 unique

作用：该字段的值不能重复
create table t_unique(id int,age int unique);
insert into t_unique values(1,28);//成功
insert into t_unique values(2,28);//失败不能重复

默认约束 default

作用：给字段设置默认值
create table t_default(id int,age int default 20);
insert into t_default (id) values(1); //默认值会生效
insert into t_default values(2,null);//默认值不会生效
insert into t_default values(3,30);//可以赋值其它值

外键约束

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

创建部门表
create table dept(id int primary key auto_increment,name varchar(10));
创建员工表
create table emp(id int primary key auto_increment,name varchar(10),dept_id int,constraint fk_dept foreign key(dept_id) 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; //失败

由于添加外键约束后会影响测试效率，所以工作中很少使用，一般都是通过java代码实现逻辑外键。

索引

什么是索引：索引是数据库中用来提高查询效率的技术，类似于目录
为什么使用索引：如果不使用索引，数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一个磁盘块，直到找到数据为止，使用索引后会将磁盘块以树桩结构保存，查询数据时会大大降低磁盘块的访问数量，从而提高查询效率。
有索引就一定好吗？
如果表中的数据很少，使用索引反而会降低查询效率
索引是越多越好吗？
不是，索引会占用磁盘空间，只针对查询时常用的字段创建索引
导入数据
windows系统： source d:/item_backup.sql
linux系统： source /home/soft01/桌面/item_backup.sql
导入完后：
show tables; 查看是否有item2 这张表
select count(*) from item2; 172万
- 测试查询耗时
  select * from item2 where title=‘100’; //耗时1.13

如何创建索引 title varchar(10)

格式： create index 索引名 on 表名(字段名(字符长度))
create index index_item_title on item2(title);
- 测试查询耗时
  select * from item2 where title=‘100’; //耗时0.04

索引分类

聚集索引：通过主键创建的索引称为聚集索引，聚集索引中保存数据，只要给表添加主键约束，则会自动创建聚集索引
非聚集索引：通过非主键字段创建的索引称为非聚集索引，非聚集索引中没有数据

如何查看索引

格式：show index from 表名;
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);

索引总结

索引是用来提高查询效率的技术，类似目录
因为索引会占用磁盘空间，所以不是越多越好
因为数据量小的时候使用索引会降低查询效率所以不是有索引就一定好
分类：聚集索引和非聚集索引
通过多个字段创建的索引称为复合索引

事务

数据库中执行同一业务多条SQL语句的工作单元，可以保证全部执行成功或全部执行失败
####事务的ACID特性
ACID是保证数据库事务正确执行的四大基本要素

Atomicty：原子性，最小不可拆分，保证全部成功或全部失败
Consistency：一致性，保证事务从一个一致状态到另外一个一致状态
Isolation：隔离性，多个事务之间互不影响
Durablity: 持久性，事务提交之后数据保存到数据库文件中持久生效
####事务相关的SQL
开启事务
begin;
回滚
rollback;
提交
commit;
保存回滚点
savepoint s1;
回滚到某个回滚点
rollback to s1;
查看自动提交状态
show variables like ‘%autocommit%’;
修改自动提交状态
set autocommit=0/1;

group_concat()

查询每一个部门所有员工的姓名和工资
select deptno,group_concat(ename,’-’,sal) from emp group by deptno;
查询每个部门的员工姓名，要求每个部门只显示一行
select deptno,group_concat(ename) from emp group by deptno;

面试题

创建学生成绩表 student(id主键,name姓名,subject学科,score成绩)
create table student(id int primary key auto_increment,name varchar(10),subject varchar(10),score int);
保存以下数据：
insert into student (name,subject,score) values
(‘张三’, ‘语文’, 66),(‘张三’, ‘数学’, 77),(‘张三’,‘英语’, 55),(‘张三’, ‘体育’, 77),(‘李四’ ,‘语文’, 59),(‘李四’, ‘数学’, 88),(‘李四’, ‘英语’, 78),(‘李四’, ‘体育’, 95),(‘王五’, ‘语文’, 75),(‘王五’, ‘数学’, 54),(‘王五’, ‘英语’, 98),(‘王五’, ‘体育’, 88);

查询每个人的平均分从大到小排序
select name,avg(score) a from student
group by name order by a desc;
查询每个人的名字科目和成绩一行显示出来
select name,group_concat(subject,’:’,score) from student
group by name;
查询每个人的最高分和最低分
select name,max(score),min(score) from student group by name;
查询每个人的名字不及格的科目以及分数，不及格的科目数量一行显示
select name,group_concat(subject,’:’,score),count(*) from student
where score<60
group by name;

东西差不多就这些，sql这东西还是多敲一敲，给我的感觉sql上最常出错的地方就是单词的拼写，唉英语不太好啊。如果大家有疑问的或者有想交流的欢迎留言哈。唉怎么办啊,要实习了还啥也不会呢 2333…

汐汐同学呀

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库的sql语句的入门与提高-完整篇

进入10月,学校里每年一度的秋招又要开始了，正在准备找实习单位的我又把之前学过的sql回顾了一下，收获颇丰，这次我把之前跟着数据库老师学习的笔记和习题一同整理了一下，供大家一起学习，这里面有很多知识点的总结，也有配合的练习题，所以不管是小白还是有一定sql基础的朋友来说都可以看一看，当然，如果是大神的话肯定会觉得这些很简单，哈哈哈哈。废话不多说了，整理的东西比较多，如果大家发现错误欢迎大家指正，一...
复制链接

扫一扫