数据库取经（二）

目录

一、数据库基础(二)

二、表与表之间的关系

三、修改表及复制表

四、常用约束条件

五、查询表操作

六、多表操作

---

一、数据库基础(二)

1、unique 唯一

• 单列唯一

  """
  create table t1(id int unique,name char(11));
  insert into t1 values(1101,'teddy'),(1102,'bob');
  """

• 联合唯一

  例如 IP和port，单个都可以重复，但是联合在一起是唯一的

  """
  create table t4(
      id int,
      ip char(16),
      port int,
      unique(ip,port)
  );
  insert into t4(1101,'192.168.1.1',8080);
  insert into t4(1102,'192.168.1.1',8081);
  insert into t4(1103,'192.168.1.1',8080);报错
  """

2、primary key 主键

1、从约束效果看，等价于not null + unique 非空且唯一

2、也是inndb存储引擎组织数据的依据(innodb在创建表的时候必须要有主键，类似于索引，可以加快查询的速度)

注：1、一个表有且只有一个主键，如果没有设置主键，会自动设置一个最上方的非空且唯一的字段

2、如果表中没有设置主键，也没有非空且唯一的字段，会使用内部的隐藏字段作为主键，因此不能提升查询速度。

3、与unique类似，主键也有单个字段主键和联合主键

3、auto_increment 自增

自增只能设置在主键的后面，作为主键字段的约束条件；

create table t1 (int primary key auto_increment,name char(11));

4、外键(foreign key)

"""
外键就是用来帮助建立表与表之间关系的
"""

"""
在使用外键时会消耗系统的资源，在实际使用时，如果表比较多，可以不使用外键，在操作表时，可以直接通过sql语句来操作，建立逻辑关系。
"""

二、表与表之间的关系

如何判断？

-->双向判断表与表之间的关系，一对多和多对一都是一对多的关系。

1. 一对多(常用)

   """
   1 一对多表关系  外键字段建在多的一方
   2 在创建表的时候 一定要先创建被关联表 
   3 在录入数据的时候 也必须先录入被关联表
   """

   # SQL语句建立表关系
   create table dep(
       id int primary key auto_increment,
       dep_name char(16),
       dep_desc char(32)
   );
   ​
   create table emp(
       id int primary key auto_increment,
       name char(16),
       gender enum('male','female','others') default 'male',
       dep_id int,
       foreign key(dep_id) references dep(id)
   );
   insert into dep(dep_name,dep_desc) values('教学部','教学'),('外交部','外交'),('技术部','技术');
   insert into emp(name,dep_id) values('a',2),('b',1),('c',1),('d',3);

   """
   级联更新   >>>   同步更新   >>>on update cascade
   级联删除   >>>   同步删除   >>>on delete cascade
   """

   create table dep(
       id int primary key auto_increment,
       dep_name char(16),
       dep_desc char(32)
   );
   ​
   create table emp(
       id int primary key auto_increment,
       name char(16),
       gender enum('male','female','others') default 'male',
       dep_id int,
       foreign key(dep_id) references dep(id) 
       on update cascade  # 同步更新
       on delete cascade  # 同步删除
   );
   insert into dep(dep_name,dep_desc) values('教学部','教学'),('外交部','外交'),('技术部','技术');
   insert into emp(name,dep_id) values('a',2),('b',1),('c',1),('d',3);

2. 多对多

   """
   创建多对多关系时，需要再创建一张表来存储两张表的关系
   """

   """
   create table book(
       id int primary key auto_increment,
       title varchar(32),
       price int
   );
   create table author(
       id int primary key auto_increment,
       name varchar(32),
       age int
   );
   create table book2author(
       id int primary key auto_increment,
       author_id int,
       book_id int,
       foreign key(author_id) references author(id) 
       on update cascade  # 同步更新
       on delete cascade,  # 同步删除
       foreign key(book_id) references book(id) 
       on update cascade  # 同步更新
       on delete cascade  # 同步删除
   );
   """

3. 一对一

   # 一对一 外键字段建在任意一方都可以 但是推荐建在查询频率比较高的表中
   create table authordetail(
       id int primary key auto_increment,
       phone int,
       addr varchar(64)
   );
   create table author(
       id int primary key auto_increment,
       name varchar(32),
       age int,
       authordetail_id int unique,
       foreign key(authordetail_id) references authordetail(id) 
       on update cascade  # 同步更新
       on delete cascade  # 同步删除
   )

三、修改表及复制表

1、修改表

"""
1 修改表名
    alter table 表名 rename 新表名;
​
2 增加字段
    alter table 表名 add 字段名 字段类型(宽度)  约束条件;
    alter table 表名 add 字段名 字段类型(宽度)  约束条件 first;
    alter table 表名 add 字段名 字段类型(宽度)  约束条件 after 字段名;
​
3 删除字段
    alter table 表名 drop 字段名;
​
4 修改字段
    alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
    alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
    
"""

2、复制表

# 我们sql语句查询的结果其实也是一张虚拟表
"""
create table 表名 select * from 旧表;  不能复制主键 外键 ...
create table new_dep2 select * from dep where id>3;
"""

四、常用约束条件

"""
unique
not null
unsigned
default
zerofill
primary key
auto_increment
"""

# 补充：在删除MySQL表中的数据时，即使删掉了表的某个数据，表的主键的自增仍会从下一个数字开始，除非使用truncate 清空数据并重置主键。

五、查询表操作

1、tips

# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;

2、select

使用select时，先使用*进行占位，最后对需要查询的内容进行补位

3、where

select id,name,age from emp where id>=3 and id<=6;
select id,name from emp where id between 3 and 6;  #两者等价

4、group by 分组

MySQL 5.5之后的版本默认开启了严格模式，在取用数据时，只能拿到分组的依据的数据；

select * from emp group by post;  # 报错
select post from emp group by post;  # 不报错
# 除此之外，也可以使用聚合函数来拿到分组的数据

"""
聚合函数：
max
min
sum
count
avg
...
group_concat
"""
# 在使用聚合函数时，可以将聚合函数用‘as’重新命名；

# group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
# concat不分组的时候用 
select concat('NAME:',name),concat('SAL:',salary) from emp;
​
# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;  
select emp.id,emp.name from emp as t1;   报错
select t1.id,t1.name from emp as t1;
​
# 查询每个人的年薪  12薪
select name,salary*12 from emp;

# 关键字where和group by同时出现的时候group by必须在where的后面

5、having

"""
having的语法根where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
"""

6、distinct

# 注：必须是一模一样的数据才可以去重！（包括主键）
select distinct id,age from emp;

7、order by

select * from emp order by salary asc;
select * from emp order by salary desc;
"""
order by默认是升序  asc 该asc可以省略不写
也可以修改为降序     desc
"""
select * from emp order by age desc,salary asc;
# 先按照age降序排  如果碰到age相同 则再按照salary升序排

8、limit

select * from emp;
"""针对数据过多的情况 我们通常都是做分页处理"""
select * from emp limit 3;  # 只展示三条数据
​
select * from emp limit 0,5;
select * from emp limit 5,5;
第一个参数是起始位置
第二个参数是展示条数

9、regexp(正则)

select * from emp where name regexp '^j.*(n|y)$';

10、like

"""
模糊查询
    like
        %  匹配任意多个字符
        _  匹配任意单个字符
"""
select name,salary from emp where name like '%o%';

六、多表操作

前期表准备

#建表
create table dep(
id int,
name varchar(20) 
);
​
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
​
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
​
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

6.1表查询

select * from dep,emp;  # 结果   笛卡尔积
"""
了解即可 不知道也没关系
"""
​
select * from emp,dep where emp.dep_id = dep.id;
​
"""
MySQL也知道 你在后面查询数据过程中 肯定会经常用到拼表操作 
所以特地给你开设了对应的方法
    inner join  内连接(常用)
    left join   左连接(不常用)
    right join  右连接(不常用)
    union       全连接(不常用)
"""
# inner join  内连接
select * from emp inner join dep on emp.dep_id = dep.id;
# 只拼接两张表中公有的数据部分
​
# left join   左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左表所有的数据都展示出来 没有对应的项就用NULL
​
# right join  右连接
select * from emp right join dep on emp.dep_id = dep.id;
# 右表所有的数据都展示出来 没有对应的项就用NULL
​
# union     全连接  左右两表所有的数据都展示出来
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

6.2子查询

"""
子查询就是我们平时解决问题的思路
    分步骤解决问题
        第一步
        第二步
        ...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
    1 先获取部门的id号
    2 再去员工表里面筛选出对应的员工
    select id from dep where name='技术' or name = '人力资源';
    
    select name from emp where dep_id in (200,201);
    
    
    select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');

6.3总结

"""
1.多表操作的两种方法就是联表和子查询两种
2.通常情况下，这两种方法是一起用的
3.表的查询结果可以作为其他表的查询条件
4.也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
5.在书写sql语句查询时，应当慢一点写，边写边查
"""

"""
为了避免在执行sql语句时报错，可以使用exist关键字
#   关键字exists(了解)
    只返回布尔值 True False
    返回True的时候外层查询语句执行
    返回False的时候外层查询语句不再执行
    select * from emp where exists 
        (select id from dep where id>3);
           
   select * from emp where exists 
        (select id from dep where id>300);
"""

部分知识点来源于网络，如有侵权请联系删除！