TA的每日心情 | 无聊 7 小时前 |
---|
签到天数: 523 天 连续签到: 5 天 [LV.9]测试副司令
|
约束
- 约束直接在属性类型后面加上便可
- 主键约束 PRIMARY KEY (自主编号AUTO_INCREMENT)
- 唯一约束 UNIQUE KEY
- 默认约束 DEFAULT
- 外键约束 FOREIGN KEY
···························
1.主键约束 PRIMARYKEY
每张数据表只能 存在一个主键
主键保证记录的唯一性
主键自动为 NOT NULL
添加主键约束
如:ALTER TABLE persion(数据表名称) ADD CONSTRAINTPK_persion_id(系统自动生成的主键id名字、格式) PRIMARY KEY (id);
删除:persionDROP PRIMARY KEY;
···························
保证数据完整性,一致性
表极约束:两个字段以上, 列极约束:某个字段
···························
2.外键约束 FOREIGNKEY
- 保持数据的完整性,一致性
- 实现一对一或是一对多的关系
- 要求:父表、子表用相同的存储引擎,唯一的数据表引擎,INNODB
- 外键列和参照列有相似的数据类型,若不存在则自动创建
这个是干嘛的呢??????
FOREIGN KEY (pid子表id) REFERENCESpersion父表(id);
可以:pid SMALLINT UNSIGNED
添加约束外键
如:ALTER TABLE persion(数据表名称) ADD FOREIGNKEY(子表id) REFERENCES persion父表(id)
删除:··················
3.添加唯一约束
如:ALTER TABLE persion(数据表名称) ADDUNIQUE(username1,username2,...);
删除:ALTER TABLE persion DROP INDEXusername;
4.添加/删除默认约束
如:ALTER TABLE persion(数据表名称) SET/DROPDEFAULT
··································
子查询
- 可以返回标量,一行一列,或是子查询
- 求平均值
- 平均值=SELECT AVG(goods_price) FROMtdb_goods;
- 保留多少位
SELECT ROUND(ANG(goods_price),2) FROM tdb_goods;
子查询:
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHEREgoods_price>=平均值
当使用自查询返回多个结果是要使用:AND,SOME,ALL连接语句
把查询到的记录插入到别的表中
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goodsGROUP BY tdb_cates;
···············································
连接
tbl_name [[AS] alias] table_subquery [AS]alias
数据表中赋予别名tbl_name AS name_alias
在子查询中子表就一定要赋予别名
连接类型
内连接 INNER 仅符合表连接的条件
左外连接 LEFT [OUTER]JOIN
右外连接 RIGHT [OUTER]JOIN
使用ON关键来设定连接条件,也可以使用WHERE来代替,过滤
1.内连接
SELECT goods_id,goods_name,cate_name FROMtdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id;
2.外连接
SELECT goods_id,goods_name,cate_name FROMtdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id;
SELECT goods_id,goods_name,cate_name FROMtdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id;
多表连接
SELECT goods_id,goods_name,goods_price,cate_name,brand_name FROMtdb_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.cates_id=b.cates_id
INNER JOIN tdb_goods_brands AS b ONg.brand_id=b.brands_id
无限分类表设计
自身连接
父类--子类
SELECT s.type_id,s.type_name,p.type_name FROM tdb_good INNER JOINtdb_goods_type AS p ON s.parent_id=p.type_id;
父类--子类
多表删除
DELETE t1FROM tdb_goods AS t1 LEFTJOIN
(SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_nameHAVING count(goods_name)>=2) AS t2 ON t1.goods_name=t2.goods_name
WHERE t1.goods_id>t2.goods_id;
SELECT goods_id,goods_name FROM tdb_goods GROUP BY tdb_goods_nameHAVING count(goods_name)>=2;
|
|