MySQL表的操作

外键的变种

创建外键以及删除外键的补充

如果文件已经存在,想要和别的表建立外键关联关系

alter table 表名 add constraint 外键名 foreign key 列 references 表(主键);

删除外键关联关系

alter table 表名 drop foreign key 外键名;

# 外键关联关系删除了,字段还在,不会被删除

唯一索引unique

在创建表的时候,想让哪个字段的值不唯一,就可以使用unique把那个列括起来

例子:

create table t1(
id int auto_increment primary key,
course_id int,
unique(course_id)
)engine=innodb charset=utf8;

唯一索引的作用:

  1、使这个列的值唯一

  2、加快索引

联合唯一索引

  如果你想让多个列联合起来唯一(两个组合在一起唯一),可以用联合唯一索引,也就是把多个

  字段通过逗号隔开放在unique的括号中,联合多少个没有限制

  例子

create table t1(
id int auto_increment primary key,
course_id int,grade_id int,
unique(course_id,grade_id)
)engine=innodb charset=utf8;

联合唯一索引的作用:

  1、让联合起来的列的值组合起来唯一

  2、加快索引

一对多

  指的是当两张表建立外键关系时,记录的对应关系

  一对多指的就是a表的一条记录可以对应b表的多条记录

  但是b表的一条记录不能对应a表的多条记录(只能对应一条)

  例子:

    一条狗只能对应一个品种,但是一个品种可以对应多条狗

一对一

  指的是a表的一条记录只能对应b表的一条记录

  并且b表的一条记录也只能对应a表的一条记录

  例子:

    一个人只能对应一张身份证,并且一张身份证也只能对应一个人

多对多

  指的是a表的一条记录可以对应b表的多条记录

  并且b表的一条记录也可以对应a表的多条记录

  在这种状况下,我们要想在某一张表上建立外键关系就显得不太现实了,建外键的条件

  就是你关联的表必须存在

  解决办法:

    所以这种情况只能造出一个第三张表,c表,分别和a表、b表建立外键关系

    再让两个建立外键关系的列联合唯一索引

数据行的操作

增操作

一次增一行

insert into 表名(列1,列2...) values (值1,值2....);

一次增多行

insert into 表名(列1,列2...) values (值1,值2....),(值1,值2...),...;

复制某张表里的值到新的表里

insert into 表名(列1,列2...) select 列1,列2... from 表名;

删除操作

删除整张表数据

delete from 表名;

带条件删除某条记录

  通过where 条件 实现

delete from 表名 where id = 10;  # 删除id=10 的这条记录

where 后面可以跟的运算符如下

修改

也可以跟上条件判断

update 表名 set 列名 = 值,...where 列名2 = 值2,...;

查询

基本的查询

select * from 表名;   # 查询整张表的数据
select 列名,... from 表名;    # 查询表中某一列的全部数据

高级查询

1、where条件查询

select * from 表名 where 列名 = 值;  # 查询列名为这个值的所有行的数据
select * from 表名 where 列名 > 值;  # 查询列名值大于这个值的所有行的数据

2、between and 查找(闭区间)

select * from t1(表名) id(列名) between 1(值1) and 4(值2);
# 查询的是id值在1和4中间的所有行的数据,并且这个1到4是闭区间

3、in查找(集合里查找)

select * from t1(表名) id in (1,2,5);
# 查询的是id值是1,2,5的行的所有数据

4、通配符(%,_)查找,又称模糊查找

例如我要找一个increment ,但我只知道他是in开头想要查找就需要用到通配符查找

select * from t1(表名) where name(列) like('in%')
# 可以查找到,%可以代表任意长度字符
select * from t1(表名) where name(列) like('in_')
# 不可以找到,_只能代表一个字符

总结:上面4中都需要用到where,后面再跟上一些不一样的条件

5、限制取几条  limit

select * from 表名 索引偏移量,取出数据量(offset);
# 表示的是一次显示多少条数据,这也是网页分页的核心SQL

  例子

select * from t1(表名) limit 10,20;
# 表示的是从10的索引开始显示,一共显示20条记录

6、排序  order by

  通过某个队列的值进行排序,如果是字符串,通过首字母进行排序

# 降序
select * from t1 order by id(列名) desc;
# 升序
select * from t1 order by id(列名) asc;

  多列排序

select * from t1 order by id(列名) desc,num(列名) asc;  # 多列用逗号隔开
# 先按前一列进行排序,如果第一列排完有值相同的,再根据第二个规定的列进行排序
# 也就是如果第一个列排完了,并且没有重复,那就没有第二个排序的事了

7、分组  group by

select name(列), 聚合函数(count(),sum(),max(),min(),avg())from t1(表名) group by age(列名);

  聚合函数可以根据聚合函数可以对分组里的内容进行一些操作(求个数,求和...) 需要传如一个

  列名,他根据这个列名操作。

  二次筛选

    针对的是一次查询之后的结果,还需要进一步删选出符合条件的结果

select name,count(name) as c from t1 group by age having c>3;
# 先是根据年龄进行了分组,并且第一次选出结果  as 是起别名
# having是对第一次筛选的结果做出个数大于3的二次筛选

where和having的区别:

  --1、having与where类似,都是用来筛选数据的

  --2、where针对表中的列发挥作用,查询数据

  --3、having针对查询结果中的列发挥作用,二次筛选出结果,一般与group by 一起使用

8、连表操作

select * from 表1,表2;    # 会由于笛卡尔积的关系,显示出的不是我们想要的结果
# 要想得到想要的结果,必须确定外键的对应关系
select * from t1,t2 where t1.t2_id = t2.id; # 把t1的外键 和t2的主键条件判断一下

  左连接

    左边的表全部显示出来,右边没用到的不显示

select * from t1 left join t2 on t1.t2_id = t2.id;

  右连接

    右边的表全部显示出来,左边没用到的不显示

select * from t1 right join t2 on t1.t2_id = t2.id;

  内连接

    左右两边的数据都会显示出来

ps:

  1、只需要记住左连接即可,右连接可以直接把左连接中的表的顺序颠倒

  2、可以连接多张表,只要有外键对应关系即可

查询表的顺序

select * from 表 (where 条件) (group by 列名 [having 二次筛选]) (order by 列名) (limit 索引偏移,offset);
# 括号只是表明查找的方法,[]代表可有可无,实战中是没有括号的
posted @ 2019-06-14 12:08  hesujian  阅读(303)  评论(0编辑  收藏  举报