常见约束、事务及其他查询语句

随时随地阅读更多技术实战干货，获取项目源码、学习资料，请关注源代码社区公众号(ydmsq666)

一、实体完整性：保证每行所代表的实体能相互区别，不能存在两条一模一样的记录。
实现方法：
主键约束：主键列不能为空，也不能重复。一个表中只能有一个主键。
唯一约束：是指给定列的所有值必须唯一，该列在表中每一行的值必须唯一。
          它和主键约束的区别在于该列可以为空，并且可以在一张表中给多个列设置唯一约束。

二、域完整性：保证指定列的数据的有效性，是指列的输入有效性
实现方法
    非空约束：NotNull
    默认约束：Default
    检查约束：Check（MySQL不支持）

三、外键和外键约束：
外键：是指从表的某列与主表的某列存在依附关系
外键约束：是指在主键关联的外键上强制加上一个约束，如果违反该约束，则不允许该条数据的修改。
注意：没有建立外键约束不等于没有外键

CREATE TABLE person(
-- 设置id列为主键列:不能为null,同时不能重复,AUTO_INCREMENT表示设置主键列为自动增长列
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(40) UNIQUE, -- 姓名列不能重复
sex VARCHAR(40) NOT NULL -- 增加非空约束
)AUTO_INCREMENT=1000;-- 列号起始为1000
-- 一旦设置了自动增长列，该列的数据就可以由DBMS来维护

INSERT INTO person(NAME,sex) VALUES('张三','男');
INSERT INTO person(NAME,sex) VALUES('李四','男');
INSERT INTO person(NAME,sex) VALUES('王五','男');

-- 增加默认约束，增加默认约束的列，在不插入数据时，默认为指定值,一般在建表时就加上
ALTER TABLE person ADD contry VARCHAR(40)DEFAULT '中国';

CREATE TABLE computer(
id INT PRIMARY KEY AUTO_INCREMENT,
 computerType VARCHAR(30),
personid INT ,
CONSTRAINT fk_com FOREIGN KEY(personid) REFERENCES person(id)
)ENGINE=INNODB;

-- 增加外键约束,用从表的外键列去关联主表的主键列。
-- 也就是说从表的外键列出现的内容必须是主表主键列出现的内容。
ALTER TABLE computer ADD CONSTRAINT fk_com FOREIGN KEY(personid) REFERENCES person(id);

-- 开启事务
START TRANSACTION;
DELETE FROM person;

-- 提交事务,若执行了提交事务，则将现在所有的操作更新数据库，数据不能回滚
COMMIT;

-- 回滚事务
ROLLBACK;


-- DELETE 会记录日志，意味着删除后的数据还可以恢复，但是效率低。
-- TRUNCATE不会记录日志，删除后的数据不能恢复，但是效率高。

-- 删除所有记录，记录不能回滚
TRUNCATE TABLE dog;

-- concat 连接字符串
UPDATE person SET NAME=CONCAT(NAME,'你好');

-- 给表和列取别名
SELECT p.NAME 姓名 FROM person p;

-- 手动建立product表，并添加如下数据
INSERT INTO product(productName,price,number,factory)VALUES('电视机',1500,2,'绵阳');
INSERT INTO product(productName,price,number,factory)VALUES('电冰箱',2500,1,NULL);
INSERT INTO product(productName,price,number,factory)VALUES('毛毯',150,5,'内蒙古');
INSERT INTO product(productName,price,number,factory)VALUES('苹果',5,6,NULL);
INSERT INTO product(productName,price,number,factory)VALUES('板凳',150,10,'成都');
INSERT INTO product(productName,price,number,factory)VALUES('芒果',10,8,'海南');

-- 查询时可以加入计算
SELECT p.*,price*number totalprice FROM product p

-- 去除重复的记录
SELECT DISTINCT price FROM product;

-- 返回指定行数的记录，第一个参数表示从第几行开始（第一行为0），第二个参数表示显示几条记录
SELECT * FROM product LIMIT 1,3

-- 查询条件为复合条件时，and 连接表示并且；or连接表示或者，between...and连接表示在...之间
SELECT * FROM product WHERE price>1000 AND factory='绵阳'
SELECT * FROM product WHERE price>1000 OR factory='绵阳'
SELECT * FROM product WHERE price BETWEEN 100 AND 2000

-- 查询id为3和5的商品（IN的用法）
SELECT * FROM product WHERE id IN(3,5);

-- 查询id不为3和5的商品（NOT IN的用法）
SELECT * FROM product WHERE id NOT IN(3,5);

-- 模糊查询  
-- “-”通配符 _表示任何单个字符，“%”表示包含零个或多个任意字符
SELECT * FROM product WHERE productName LIKE '%电%';

-- 查询厂家为null 的商品
SELECT * FROM product WHERE factory IS NULL

-- 按商品单价排序显示 .ORDER BY price,id表示先按price排序，price相同时再按id排序
SELECT * FROM product ORDER BY price,id;-- 升序
SELECT * FROM product ORDER BY price DESC,id DESC;-- 降序