mysql基础

表连接
建表时使用的是第三范式,每个表的每个列尽可能的单一,每个只体现与本表的直接内容,如果牵扯其它表的数据,一般会使用外键关联,员工表是直接看不到部门的名称,只能看到部门编号,又比如部门表中不能直接体现地区名称,只能看到地区编号,这样做的好处数据不冗余,

查询43号部门所在地区的名称
查询过程牵扯到两张表:s_dept s_region
查询的结果:s_dept id region_id ,s_region id,name
怎么样区分两个表中间额相同名称的列,加前缀来区分,我们采取的方法是给表起别名,别名.列名来区分,给表起别名和给列起别名的方法相同.

#查询43号部门所在地区的名称
# s_dept                 s_region
#       部门是属于地区
#       region_id 外键        id  主键

#查询出部门表和地区表的所有数据
SELECT d.* ,r.*
FROM s_dept d ,s_region r;
#查询出一个比较庞大的结果,只是把两个表中的
#数据组合再一起,总共有12*5=60条记录,这个
#结果被称为笛卡尔积,这个时候需要对结果进行
#过滤,拿到想要的结果,通过主外键关系来进行
#筛选,连接的条件是s_dept 表的region_id列
#和s_region表的id列的值相等.
SELECT d.* ,r.*
FROM s_dept d ,s_region r
WHERE d.region_id = r.id;

SELECT d.* ,r.*
FROM s_dept d ,s_region r
WHERE d.region_id = r.id
AND d.id= 43;
#上面的这种查询方式就是表连接,而且查询的条件

# 查询Mark在哪个工作地区
SELECT e.first_name,e.dept_id,d.id ,
d.region_id,r.id ,r.name
FROM s_emp e,s_dept d , s_region r
WHERE  e.first_name = "Mark" AND
e.dept_id = d.id AND d.region_id = r.id;

#  分多步解决问题
#通过姓名得到部门编号
SELECT dept_id
FROM s_emp
WHERE  first_name = 'Mark';
#通过部门编号10得到地区编号
SELECT region_id
FROM s_dept
WHERE  id = 10 ;
#通过地区编号1得到地区名称
SELECT  name
FROM  s_region
WHERE  id = 1;

分页查询

# 分页查询
#查询出员工表的第6条到第10条记录
SELECT id ,first_name
FROM s_emp
WHERE  id BETWEEN 6 AND 10;
#使用分页查询实现
# 第二页:
SELECT id ,first_name
FROM s_emp
limit 5,5;
# 后面的5表示每页显示5条记录
#前面的5表示从5开始,不包含5
#limit 分页 limit n,m 
# n 从几开始 n的取值是(页数-1)*m
# m 表示每页有几条记录
#每页显示5条 第一页
SELECT id ,first_name
FROM s_emp
limit 0,5;
# 第三页
SELECT id ,first_name
FROM s_emp
limit 10,5;

#分页查询,每页显示4条记录,
#显示第4页的记录
# n = (4-1)*4=12
SELECT id ,first_name
FROM s_emp
limit 12,4;
#分页查询,每页显示3条记录,
#显示第5页的记录
# n = (5-1)*3=12
SELECT id ,first_name
FROM s_emp
limit 12,3;

非等值连接

#列出员工的姓名 工资 和工资级别
# s_emp        s_salgrade 
#       salary       grade  losal   hisal
SELECT e.first_name,e.salary,
g.grade,g.losal,g.hisal
FROM  s_emp e, s_salgrade g
WHERE e.salary BETWEEN 
g.losal AND g.hisal;
#标准SQL
SELECT e.first_name,e.salary,
g.grade,g.losal,g.hisal
FROM  s_emp e INNER JOIN  s_salgrade g
ON  e.salary BETWEEN 
g.losal AND g.hisal;
#上面连接条件不是等值连接成为非等值连接

自连接

#查询员工的姓名和领导的id
SELECT id,first_name,manager_id
FROM s_emp;
# 1,2,3,6,7,8,9,10
#通过领导id查询到领导的姓名
SELECT id ,first_name
FROM s_emp 
WHERE  id IN(1,2,3,6,7,8,9,10);

SELECT e.first_name '员工姓名',
e.manager_id '领导编号1',
m.id '领导编号2',m.first_name '领导姓名'
FROM s_emp e ,s_emp m 
WHERE  e.manager_id = m.id;

#查询的结果是24条记录,而员工表有25条记录
#这是因为1号员工Carmen的manager_id的值
#是null,而现在的查询的方式是内连接里面的自连接
#内连接是一种严格匹配的方式,NULL
#不能和任何值值匹配成功,所以导致一条记录
#丢失,内连接已经无法解决这个问题,这个时候
#需要使用外连接来把第一条记录找回来
# 外连接分为三种:
#左外连接,可以保证左表的记录一条都不少
 A表 LEFT OUTER JOIN B表  ON 条件
#右外连接,可以保证右表的记录一条都不少
#全外连接,保证两个表的记录一条都不少
#现在的情况是员工表丢失了一条记录,也就是
#左表丢失了一条记录,需要使用左外连接
#找回
# LEFT OUTER JOIN  的outer 可以省略
SELECT e.first_name '员工姓名',
e.manager_id '领导编号1',
m.id '领导编号2',m.first_name '领导姓名'
FROM s_emp e LEFT  JOIN s_emp m 
ON  e.manager_id = m.id;
# 把老板的领导编号设置为0,领导姓名设置为
# 'BOSS'
# 空值替换函数 ifnull(列名,如果为空替换的值)

SELECT e.first_name '员工姓名',
ifnull(e.manager_id,0) '领导编号1',
ifnull(m.id,0)  '领导编号2',
ifnull(m.first_name,'BOSS') '领导姓名'
FROM s_emp e LEFT  JOIN s_emp m 
ON  e.manager_id = m.id;

##右外连接,可以保证右表的记录一条都不少
# A表 RIGHT OUTER JOIN B 表 ON  条件
# 保证领导表的记录一条都不少
SELECT e.first_name '员工姓名',
e.manager_id '领导编号1',
m.id '领导编号2' , m.first_name '领导姓名'
FROM s_emp e RIGHT OUTER JOIN s_emp m 
ON   e.manager_id = m.id;
#全外连接,保证两个表的记录一条都不少
# A表 FULL OUTER JOIN B表 ON 条件
#这种语法mysql不支持,mysql提供了替代的方法
# 把左外连接的语句和右外连接的语句使用
#UNION 连接起来,,最终的结果会自动过滤掉
#重复值,也就是全外连接的结果
SELECT e.first_name '员工姓名',
ifnull(e.manager_id,0) '领导编号1',
ifnull(m.id,0)  '领导编号2',
ifnull(m.first_name,'BOSS') '领导姓名'
FROM s_emp e LEFT  JOIN s_emp m 
ON  e.manager_id = m.id
UNION 
SELECT e.first_name '员工姓名',
e.manager_id '领导编号1',
m.id '领导编号2' , m.first_name '领导姓名'
FROM s_emp e RIGHT OUTER JOIN s_emp m 
ON   e.manager_id = m.id;

#创建两张表 t_emp  t_dept
drop table if exists t_emp;
drop table if exists t_dept;
create table t_dept(
    id int primary key,
    name varchar(20) not null
) charset=utf8;
insert into t_dept values(11,'Java'),
(12,'C++'),(13,'Js'),(14,'python');
select * from t_dept;

create table t_emp(
    id  int primary key,
    name varchar(20) not null,
    dept_id int
) charset=utf8;
insert into t_emp values(1,'Tom',11),
 (2,'James',12),(3,'Marry',null),
(4,'Tony',13),(5,'Ajax',11),
(6,'Nill',12);
select * from t_emp;
commit;
# 6个员工 4个部门  Marry没有部门
# 14号部门没有员工
# 查询出所有的员工和部门信息.
# 查询员工和所在的部门信息
select t.id ,t.name,d.name
from t_emp t,t_dept d
where t.dept_id = d.id;
#改为标准SQL
select t.id ,t.name,d.name
from t_emp t inner join t_dept d
on t.dept_id = d.id;
# Marry 没有查到 ,要添加Marry,使用
#左外连接 t_emp数据一条都不少
select t.id ,t.name,d.name
from t_emp t left outer join t_dept d
on t.dept_id = d.id;
#右外连接 t_dept数据一条都不少
select t.id ,t.name,d.name
from t_emp t right outer join t_dept d
on t.dept_id = d.id;
#全外连接   union  把左外连接和右外
#连接的结果去除掉重复值显示出来
select t.id ,t.name,d.name
from t_emp t left outer join t_dept d
on t.dept_id = d.id 
union
select t.id ,t.name,d.name
from t_emp t right outer join t_dept d
on t.dept_id = d.id;

总结:
增删改查
增:
建立数据库
create database 数据库名 charset=utf8;
建立数据表:
create table 表名(
列名1 数据类型 约束 (主键),
列名2 数据类型 约束(非空),
列名3 数据类型 约束(唯一),
…
) charset = utf8;
写入数据:
insert into 表名 values(值1,值2,值3,…);
insert into 表名(列名1,列名2,…)
values(值1,值2,…);
insert into 表名 values(值1,值2,值3,…),
(值1,值2,值3,…), (值1,值2,值3,…),….;
改:
update 表名 set 列名1=值1,列名2=值2,…
where 条件;
rename table 表名 to 表名1;
删:
删除数据:
delete from 表名 where 条件;
删除表:
drop table if exists 表名;
删除数据库:
drop database if exists 数据库名;
查询:
select 字段名称…
from 表名1 别名1,表名2,别名2 …
where 条件
order by 排序字段1 排序规则,…
group by 分组字段
having 分组后过滤
limit n,m 分页