mySQL数据库 约束/联合查询

mySQL数据库

约束

1. 在实际应用中，表格之间会有关联，每张表也会有一些字段是有格式要求（比如：非空且唯一，不能为负数等等）。

2. 所以每张表都会有一些约束

   • 主键：特点，非空且唯一，一张表只能有一个主键
   • 外键：通过外键关联两一个表的主键，两张表产生关系
   • 非空约束：not null,字段值不能为空
   • 自增长约束：increment，自增字段的数据类型一般是整数型的，通常配合主键来使用，自动增加。
   • 唯一约束:nuique ,字段值不能重复，但是这个约束对于字段值为null,没有约束，也就是说，被约束的列可以有一个以上的null值
   • 非负约束：nusigned,被修饰的字段不能是负值（例如是字段是int类型，值的范围是包含负数，但是被unsigned修饰后，没有负值，但是正值的范围扩大二倍）

3. 创建主键的方式

   • 一般情况下我们在创建表的时候都会添加主键。

   • 在创建表的时候就创建主键

     • CREATE TABLE demo2(sid INT PRIMARY KEY,sname VARCHAR(10));
     • CREATE TABLE demo(id INT,pid INT,uname VARCHAR(10),PRIMARY KEY(id));

   • 表创建完成之后添加主键

     • ALTER TABLE project ADD PRIMARY KEY (project_no);

   • 联合主键

     • 是将多个字段联合起来作为作为主键，此时主键的唯一判断，是将这两个字段的内容组合成整体来判断。

     • 联合主键的添加方式：联合主键是通过修改的方式来添加

       CREATE TABLE test5(
       username VARCHAR(20),
       PASSWORD VARCHAR(30)

       )

       ALTER TABLE test5 ADD PRIMARY KEY(username,PASSWORD);

4. 创建或者添加外键

   • 创建表的时候创建外键
     • CREATE TABLE demo(id INT,pid INT,uname VARCHAR(10),PRIMARY KEY(id),FOREIGN KEY (pid)REFERENCES demo2(sid));
   • 创建完成之后添加外键
     • ALTER TABLE employee ADD FOREIGN KEY (e_dept_no)REFERENCES department(dept_no);

5. 枚举类型

   • 和我们在Java中使用的枚举类型一样，我们在创建表格的时候，就希望这个字段的值是几个固定的数值，

   • 定义方法：在创建的时候给固定的数值。

     CREATE TABLE demo3(id INT PRIMARY KEY ,sex ENUM(‘男’,‘女’));
     INSERT INTO demo3 VALUES(1,‘男’)
     INSERT INTO demo3 VALUES(2,‘女’)

表格操作

1. 删除表中的所有数据

   • 方法一：DELETE FROM demo3，逐行删除，如果数据比较多的时候，是比较慢的
   • 方法二：TRUNCATE demo3,是删除整张表，在创建一张空表。这个对于行数比较多的数据，速度是比较快的。

2. 为了保证数据的有效性和完整性，我们会添加一些外键约束，让两个表形成关联。

   • 添加了外键约束之后的特点：

     • 主表不能删除从表还在引用的数据
     • 从表不能添加主表未描述的数据

   • 有时候我们两张表之间添加了外键约束，在数据删除或者更新时，我们可以添加级联更新或者级联删除

     • CREATE TABLE demo(id INT,pid INT,uname VARCHAR(10),PRIMARY KEY(id),FOREIGN KEY (pid)REFERENCES demo2(sid) ON DELETE CASCADE ON UPDATE CASCADE);

     • 主表删除数据时，删除从表（多表）。

     • 主表更新数据时，更新从表关联的数据。

多表联合查询

1. 在几张表相互关联的时候，我们需要依据关联条件查询。多表联合没有条件查询结果是不满足要求的

2. 方式一：内连接 —>不符合条件的数据不显示

   • 隐式内连接：SELECT user.* ,orders.* FROM USER,orders WHERE user.id=orders.user_id
   • 显示内连接：SELECT users.*,orders.*FROM users JOIN orders ON users.id=orders.oid;

3. 方式二：外连接 —>没有的信息使用null显示

   • 左外连接：

     -- 左外连接 LEFT OUTER JOIN 表 ON 关联条件  outer 可以省略不写
     -- 以LEFT这个单词分左右，left 左边的表他的数据是全部展示出来的。
     SELECT user.*,orders.* FROM USER LEFT OUTER JOIN orders ON user.`id`=`orders`.`user_id`
     

   • 右外连接：SELECT user.* ,orders.* FROM USER RIGHT OUTER JOIN orders ON user.id=orders.user_id

   • 左连接和右连接，其实没什么太大区别，以左连接为例，是以left左边的USER数据为基准查找 left右边的orders，如果orders里面的数据是null,也展示出来。

4. 子查询—>根据条件一步步找出所需数据

   • 多表查询的时候，子查询嵌套就可以根据条件找出数据

     • -- 查询商品是电脑的所有用户信息,电脑id是2
       SELECT*FROM miditem WHERE miditem.ppid=2;
       SELECT*FROM orders WHERE orders.oid=2 OR orders.oid=4 OR orders.oid=5
       SELECT users.*FROM users WHERE users.id=3 OR users.id=4 OR users.id=5
       
       -- 合并子查询
       SELECT miditem.ooid FROM miditem WHERE miditem.ppid=2;  --根据电脑编号查找订单编号
       SELECT orders.uid FROM orders WHERE orders.oid IN(SELECT miditem.ooid FROM miditem WHERE miditem.ppid=2)-- 根据订单编号查找有用户编号
       SELECT users.* FROM users WHERE users.id IN(SELECT orders.uid FROM orders WHERE orders.oid IN(SELECT miditem.ooid FROM miditem WHERE miditem.ppid=2))
       

5. 替换子查询，使用内连接查询

   -- 查询所有人的所有订单的所有物品
   SELECT users.*,orders.*FROM users JOIN orders ON users.id=orders.oid;
   -- 显示所需信息,内联查询
   SELECT users.id,users.username,orders.oid,orders.total,miditem.*,products.*FROM users JOIN orders ON users.id=orders.oid JOIN miditem ON orders.oid=miditem.ooid
   JOIN products ON miditem.ppid=products.pid ;
   
   -- 去除冗余的信息，最终版查询语句
   SELECT users.id,users.username,orders.oid,orders.total,products.*FROM users JOIN orders ON users.id=orders.uid JOIN miditem ON orders.oid=miditem.ooid
   JOIN products ON miditem.ppid=products.pid ;
   

复制表

1. CREATE TABLE demo4 SELECT*FROM demo  -- 复制表
   CREATE TABLE demo5 SELECT*FROM demo WHERE 1=2   --当条件（ 1=2）为假的时候只复制表头
   CREATE TABLE demo6 SELECT demo.id FROM demo   -- 只复制选定的字段
   CREATE TABLE demo7 SELECT demo.id FROM demo WHERE 1=2   -- 只复制原定的字段表头