MySQL建表约束
约束名称
描述
not null
非空约束
uniq
唯一约束
default
默认约束
primary key
主键约束
auto_increment
自增约束
foreign key
外键约束
MySQL约束类型
1.[not] null 类型(不为空)
要填都必须填上
drop table if EXISTS author; -- 判断有没有author这个表,有的话删除原来的新创一个author表,没有的话直接创一个author表
create table author(
aut_id varchar(8) not null,
aut_name varchar(50) not null,
country varchar(25) not null,
home_city char(20) not null
);
2.unique约束(唯一约束)
重复的值不能插入
方法一:
drop table if EXISTS author;
create table if not EXISTS author(
aut_id varchar(8) not null,
aut_name varchar(50) not null,
country varchar(25) not null,
unique (aut_id)
);
方法二:
drop table if EXISTS author;
create table if not EXISTS author(
aut_id varchar(8) not null unique,
aut_name varchar(50) not null,
country varchar(25) not null
);
3.defaule约束(默认约束)
defaule;
drop table if EXISTS author;
create table if not EXISTS author(
id INT,
name VARCHAR(20),
age INT DEFAULT 10
);
-- 移除非空约束
alter table author(表名) modtfy age(列名) int;
4.primary key 约束(主键约束)
唯一确定一张表中的一条记录.也就是我们通过给某个字段添加约束,就使得该字段不重复且不为空;
drop table if EXISTS author;
create table if not EXISTS author(
id int primary key,
name varchar(20)
);
联合主键
联合主键中的每个字段都不能为空,并且加起来不能和已设置的联合主键重复
drop table if EXISTS author;
create table if not EXISTS author(
id INT,
name VARCHAR(20),
password VARCHAR(20),
PRIMARY KEY(id, name)
);
5.auto_increment约束(自动增长)
需要配合主键一起用
drop table if EXISTS author;
create table if not EXISTS author(
id int primary key auto_increment,
name varchar(20)
);
如果忘记设置主键,还可以通过sql语句设置(两种方式):
1、 alter table author add primary key(id);
2、 alter table author modify id int primary key;
删除主键
alter table author drop primary key;
6.foreign key约束
涉及两个表:父表和子表
外键可以建立多个,多个外键接着写就可以
外键会产生的效果:
1.删除表时,如果不删除引用外键的表,被引用的表不能直接删除
2.外键的值必须来源于引用的表的主键字段
create table classes(id int primary key,name VARCHAR(20));-- 班级表
create table students(id int primary key,name VARCHAR(20),class_id int,foreign key(class_id) references classes(id));//学生表
//有三个班
insert into classes value(1,'class1');
insert into classes value(2,'class2');
insert into classes value(3,'class3');
//插入学生
insert into students value(1001,'Lily',1);
insert into students value(1002,'Ony',2);
insert into students value(1003,'Poy',3);
-- 1. 主表(父表)classes 中没有的数据值,在副表(子表)中是不可以使用的
-- 主表的记录被副表引用,是不能被删除的
MySQL数据查询语言
该语言用来查询记录,不会修改数据库和表结构
一、单表查询
1.带条件的查询
以slect开头:
select * from + 表名称 + 条件
查询字段
select 字段 from 表名;
模糊查询
where 字段 like '%不确定%'
distinct :用distinct来返回不重复字段的条数(count(distinct id)),其原因是distinct只能返回他的目标字段,而无法返回其他字段
2.排序
基本语法:order by 字段
备注:默认是升序,ASC升序,DESC降序
3.聚合函数
count():统计记录数
avg():平均数
max():最大值
min():最小值
sum():求和
4.分组查询
基本语法:select 字段1,字段2,聚合函数 from + 表名称 + group by 字段1,字段2
备注:group by和having 一起用,主要是对分组结果进行过滤
5.LIMT子句(MySQL中独有的语言)
可以被用于强制 SELECT 语句返回指定的记录数
接受一个或两个数字参数。参数必须是一个整数常量
如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。
SELECT * from t LIMIT 1;-- 返回第一条记录
select * from t LIMIT 3,6;-- 返回六条记录,从第四条开始计算
Limit的执行效率高,是对于一种特定条件下来说的:即数据库的数量很大,但是只需要查询一部分数据的情况。 高效率的原理是:避免全表扫描,提高查询效率
在sql语句中,limt关键字是最后才用到的。以下条件的出现顺序一般是:where->group by->having->order by->limit
二、多表查询
union 、 union all
union:用于连接两个以上的select语句的结果,讲结果组合到一个结果集中,并删除重复数据
union all: 用于连接两个以上的select语句的结果,讲结果组合到一个结果集中,并显示全部的数据
基本语法:
select 字段 from 表1 union[all|distinct] select 字段 from 表2
备注:union即为union distinct;若为union all,即返回全部结果集
表结构一致
笛卡尔积:简单来说就是两个集合相乘的结果,集合A和集合B中任意两个元素结合在一起
1.内连接(inner join)
两个表重复的部分
内连接即等值连接,获取两个表中字段匹配关系的记录,可以省略成join,可理解成集合概念中的交集,关键字段同时存在与两表的记录。
2.左连接(left join)
左连接,获取左边主表的全部记录,即使右表没有对应的数据
3.右连接(right join)
右连接,获取右边主表的全部记录,即使左表没有对应的数据
三、子查询
1.where 型子查询
将查询结果当条件
where型子查询,如果时where列=(内层sql)则内层sql返回的必须单行单例单个值。
where型子查询,如果where列in(内层sql)则内层sq返回的必须是单例,可以是多行
2.from 型子查询
#如何用子查询查出挂科两门及以上同学的平均分,where,from型都可以
select *from stu;
先把挂科2门及以上的同学找出来
1)不及格的同学:
select name,count(*) from stu where score <60 group by name;
2)然后将挂科两门以上的同学找出来:
select name,count(*) as gk from stu where score <60 group by name having gk>=2;
3)将人员抽离出来:
select name from (select name,count(*) as gk from stu where score <60 group by name having gk>=2) as tmp;
4)求平均分:
select name,acg(score) from score stu where name in(select name from (select name,count(*) as gk from stu where score <60 group by name having gk>=2) as tmp) group by name;
判断是否为空值用
-- 不为空值
is not null
-- 为空值
is null