MYSQL——MYSQL的基本使用-约束

Geek Li

已于 2022-02-20 22:09:44 修改

阅读量221

点赞数 1

分类专栏： MYSQL 文章标签： mysql 数据库 database

于 2022-02-20 22:06:10 首次发布

本文链接：https://blog.csdn.net/weixin_43496491/article/details/123036629

版权

MYSQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1.约束

作用:为了保证数据的有效性和完整性

mysql中常用的约束:
主键约束(primary key)  
			自增长约束 auto_incrment  加在整数型的字段配和主键约束来使用
			唯一约束(unique) 
			非空约束(not null) 
			外键约束(foreign key)  
			sex ENUM('男','女')  -- 把一个字段的数据类型设置为枚举类型 也可以起到一种约束的效果
			非负约束 UNSIGNED
			例如：这个数据类型 TINYINT 能表示的范围 -128---127   
                        我添加了这个约束  TINYINT  UNSIGNED    范围就是 0----255 也就是没有了负数值

2.主键约束

主键约束  PRIMARY KEY 注解约束的特点：非空且唯一 ，一张表中，只能有一个主键

-- 添加主键约束的语法
-- 方式1 建表的时候，给某个字段添加主键约束
CREATE TABLE test(
	id INT, 
	username VARCHAR(20) PRIMARY KEY  -- 给这个字段添加主键约束
);

INSERT INTO test VALUES(1,'tom');-- 成功

INSERT INTO test VALUES(2,'tom');-- 失败

INSERT INTO test VALUES(2,'jery');-- 成功

INSERT INTO test VALUES(2,NULL); -- 失败

-- 方式2 建表的时候，给某个字段添加主键约束
CREATE TABLE test2(
	id INT,
	username VARCHAR(20),
	PRIMARY KEY(id) -- 在这里指定主键字段
);

INSERT INTO test2 VALUES(1,'tom');-- 成功

INSERT INTO test2 VALUES(1,'tom2');-- 失败

-- 方式3 表建好之后，再添加主键约束
CREATE TABLE test3(
	id INT,
	username VARCHAR(20)
);

-- 采用修改表的方式，来添加主键

ALTER TABLE test3 ADD PRIMARY KEY(username);

INSERT INTO test3 VALUES(1,'tom');-- 成功

INSERT INTO test3 VALUES(2,'tom');-- 失败

-- 联合主键：把多个字段作为一个整体，来添加主键约束
CREATE TABLE test4(
	id INT,
	username VARCHAR(20)
);
-- 把 id 和  username 看做一个整体，添加联合主键
ALTER TABLE test4 ADD PRIMARY KEY(id,username);

INSERT INTO test4 VALUES(1,'tom');-- 成功

INSERT INTO test4 VALUES(1,'jery');-- 成功

INSERT INTO test4 VALUES(1,NULL);-- 失败

INSERT INTO test4 VALUES(NULL,'tom');-- 失败

-- 我们也强烈的建议，建表的时候，要有一个主键，一般建议建表的时候，给个 id 整数型字段，把这个id字段作为主键，

3.唯一约束和非空约束

唯一约束特点：值不能重复
注意：唯一约束，对null值不起作用。

CREATE TABLE test5(
	id INT,
	username VARCHAR(20) UNIQUE -- 添加唯一约束
	
);

INSERT INTO test5 VALUES(1,'tom');-- 成功

CREATE TABLE test6(
	id INT,
	username VARCHAR(20),
	UNIQUE(id) -- 唯一约束
);

INSERT INTO test6 VALUES(1,'tom');-- 成功

INSERT INTO test6 VALUES(1,'jery');

CREATE TABLE test7(
	id INT,
	username VARCHAR(20)
);

ALTER TABLE test7 ADD UNIQUE(username);

INSERT INTO test7 VALUES(1,'tom');-- 成功

INSERT INTO test7 VALUES(1,NULL);-- 成功

INSERT INTO test7 VALUES(1,NULL);-- 成功

CREATE TABLE test8(
	id INT,
	username VARCHAR(20) NOT NULL  -- 非空约束
);

INSERT INTO test8 VALUES(1,NULL);-- 成功
-- UNIQUE NOT NULL, 组合起来，给表中多个字段加，但他不等同于 主键约束，主键约束一个表中只能有一个。
CREATE TABLE test9(
	id INT UNIQUE NOT NULL,
	username VARCHAR(20) UNIQUE NOT NULL  -- 唯一 非空约束
);

4.外键约束

外键约束 针对多张表

实体：把现实的事物-----跟表对应起来

         学生:-------------student表
         
		学号        sid
		
		姓名        sname
		性别        sex
		年龄         age
		住址         address
		
-- java中   class Student------	student表


		学号        sid
		
		姓名        sname
		性别        sex
		年龄         age
		住址         address	
		
		
  表：就可以看做实体
  
  实体和实体之间存在一些对应关系	
  一对一：夫妻关系  一个人只能有一个身份证号
  一对多：用户表  订单表  一个用户可以对应多个订单。
  多对多：老师和学生 一个老师可以教很多学生，一个学生也可以被多个老师教	
         一个订单里面可以有多个商品  一个商品也可以属于多个订单
           
           
    商城项目：
    分析出来有哪些实体：用户，商品 订单  
    
    class USER  -----  user 用户表
    class ShangPin ---- 商品表
    class orders -----  订单表
    
    
    ER图
    ER图可以描述实体于实体之间的关系
	实体：用矩形表示
	属性：用椭圆表示
	关系：用菱形表示
ER图 
在ER图中有如下四个成分：
矩形框：表示实体，在框中记入实体名。
菱形框：表示联系，在框中记入联系名。
椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。
连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。
（对于一对一联系，要在两个实体连线方向各写1； 
对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M。) 


-- 创建用户表
	CREATE  TABLE USER(
		id INT PRIMARY KEY AUTO_INCREMENT,
		username VARCHAR(20)
	);
	
	-- 创建订单表
	CREATE  TABLE orders(
		id INT PRIMARY KEY AUTO_INCREMENT,
		totalprice DOUBLE,
		user_id INT   -- 这个字段的意思是 这个订单属于哪个用户，也就是说同个这个字段让表和表之前存在对应关系
	);
  
    -- 外键约束：是为了保证数据的有效性和完整性。
    -- 添加外键约束的目的，是为了保证数据的有效性和完整性。
    -- 我们在多表一方，添加外键约束去关联主表一方的主键。
    -- 添加了外键约束后有如下特点
    -- 1. 主表一方不能删除，多表一方还在引用的数据
    -- 2. 多表一方，不能添加，主表没有描述的数据。
    
    -- 通过修改表来添加外键约束
    -- alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);
    
   ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES USER(id);
 
   CREATE TABLE zhu(
    zid INT PRIMARY KEY AUTO_INCREMENT,  -- 主键
    zname VARCHAR(20)

);

CREATE TABLE cong(
    zid INT PRIMARY KEY AUTO_INCREMENT,
    zscore INT,
    zzid INT, -- 外键
 -- 方式2：建表的时候就加上了外键约束
    FOREIGN KEY(zzid) REFERENCES zhu(zid)
);

5.级联更新和级联删除

添加了外键约束之后有如下特点:★
	1.主表中不能删除从表中已引用的数据
	2.从表中不能添加主表中不存在的数据
开发中处理一对多:★
	在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致,
	为了保证数据的有效性和完整性,在多表的外键上添加外键约束即可.
那如果添加了外键后我想删除主表中的数据 怎么办 ?
	方式1: 级联删除
		ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE; 
        	          	然后你就可以删除主表中的数据了
		当然你可以级联删除和级联更新 都加上 FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE ON UPDATE CASCADE)
	方式2:先把带有外键的多表的数据删除,再删除一表中的数据

	方式2演示级联删除和级联更新：建表的时候就加上了级联更新和级联删除 -- 建表的时候就加上了级联更新和级联删除 
CREATE TABLE zhu(
    zid INT PRIMARY KEY AUTO_INCREMENT,
    zname VARCHAR(20)

);

CREATE TABLE cong(
    zid INT PRIMARY KEY AUTO_INCREMENT,
    zscore INT,
    zzid INT,
 -- 方式2：建表的时候就加上了级联更新和级联删除 
    FOREIGN KEY(zzid) REFERENCES zhu(zid) ON DELETE CASCADE ON UPDATE CASCADE
);

6.处理多对多

多对多
例子:商品和订单
– 创建商品表
create table product(
id int primary key auto_increment,
name varchar(20),
price double
);

-- 创建中间表
create table orderitem(
	oid int,
	pid int
);
	
-- 添加外键约束 
alter table orderitem add foreign key(oid) references orders(id);
alter table orderitem add foreign key(pid) references product(id);

开发中处理多对多:★
	引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分
	成两个一对多了
	为了保证数据的有效性和完整性
		需要在中间表上添加两个外键约束即可.

7.多表查询

内连接
外连接
子查询

笛卡尔积:了解
	多张表无条件的联合查询.没有任何意思
		select a.*,b.* from a,b;
		
内连接:★
	格式1:显式的内连接
		select a.*,b.* from a [inner] join b on ab的连接条件
	格式2:隐式的内连接
		select a.*,b.* from a,b where ab的连接条件
外连接:★
	左外连接:★
		select a.*,b.* from a left [outer] join b on 连接条件;  outer 可以不写
		意思:
			先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以null值展示.
	右外连接:
		select a.*,b.* from b right [outer] join a on 连接条件;  outer 可以不写
		意思:
			先展示jion右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以null值展示.
子查询:★
	一个查询依赖另一个查询.

练习1

	1.查询用户的订单,没有订单的用户不显示
		隐式内连接:
			select user.*,orders.* from user ,orders where user.id=orders.user_id;
		显示内连接
			select user.*,orders.* from user join orders on user.id=orders.user_id;
	2.查询所有用户的订单详情
		左外连接: user在左
			select user.*,orders.* from user left join orders on user.id=orders.user_id;
	3.查询所有订单的用户详情
		右外连接:orders 在右
			select orders.*,user.* from user right join orders on user.id=orders.user_id;

练习2

一. 查看用户为张三的订单详情
		1.先查询张三的id
			select id from User where username = '张三';// 3
		2.select * from orders where user_id = ?;
		
		两个合二为一  注意:后面的条件 把括号带上
			select * from orders where user_id = (select id from User where username = '张三'); --注意带上括号
二. 查询出订单的价格大于300的所有用户信息。
		1.先查询出订单价格>300的用户的id
			select user_id from orders where price >300;//(3,3,5,null)
		2.select * from user where id in(3,3,5,null);
		
		两个合二为一:
			select * from user where id in(select user_id from orders where price >300);
三. 查询订单价格大于300的订单信息及相关用户的信息。
		方式1:内连接:
			select orders.*,user.* from orders,user where user.id=orders.user_id  and orders.price>300 ;
		
		方式2:子查询: 是将一个查询的结果作为一张临时表	
			
			先查出 订单大于三百的 订单  然后把可以作为一个临时表    select * from orders where price>300
			合二为一条  注意给临时表 起个别名 注意给表起别名 名字不要带引号
			 其实用的就是内链接 查两张表 注意把临时表用户括号括起来
			select user.*,tmp.* from user,(select * from orders where price>300) as tmp where user.id=tmp.user_id;
	
	给表起别名
		格式: 表 [as] 别名

8.自连接查询

通过表的别名,给一张表起两个别名,将他视为两张表,来进行查询

 		 比如:我要查询emp表中 员工姓名 所对应的 老板姓名
 		 因为这些信息都在一张表 emp中 
  		比如 员工号7369 的 SMITH 他对应的老板编号是(MGR) 7902 而7902 又是员工FORD(7902) 那FORD 对应的老板编号又是 7566
  		所以说 一个员工既是某几个员工的老板,他也有自己的老板
  		所以我要查询这个员工的所对应的老板 就可以使用自连接查询

		我们假设有两张表一张员工表,一张老板表,如果员工的老板号=老板的员工号 就表示这个员工是另外一个员工的老板
		 select e.ename as 员工姓名,b.ename as 老板姓名 from emp e,emp b where e.mgr=b.empno;

mysql在把子查询结果作为删除表中数据的条件，mysql不允许在子查询的同时删除原表数据
目标表和原表一致 MYSQL不支持如此操作
数据库不能边查询边删除

我们的一般思路就是，
1、把子查询的结果创建临时表存储。
2、把这个临时表作为原表删除的条件。
3、删除表数据。

存储过程

-- 复制表
-- 创建一张表,表的字段和数据来一条查询语句
CREATE TABLE myemp AS SELECT * FROM emp;

-- 复制个别字段
CREATE TABLE myemp2 AS SELECT empno,ename,sal FROM emp;

-- 只复制表头,不要数据
CREATE TABLE myemp4 AS SELECT * FROM emp WHERE 1=2;
CREATE TABLE myemp3 AS SELECT empno,ename,sal FROM emp WHERE 1=1;

-- 我创建的这张表也可以来自一个子查循
-- create table 表名 as 子查询

SELECT 
  orders.id AS oid,orders.`price`,
  lsb.* 
FROM
  orders,
  (SELECT 
    * 
  FROM
    USER 
  WHERE id IN 
    (SELECT 
      user_id 
    FROM
      orders 
    WHERE price > 300)) AS lsb 
WHERE orders.`user_id` = lsb.id;

CREATE TABLE myuser AS SELECT 
  orders.id AS oid,orders.`price`,
  lsb.* 
FROM
  orders,
  (SELECT 
    * 
  FROM
    USER 
  WHERE id IN 
    (SELECT 
      user_id 
    FROM
      orders 
    WHERE price > 300)) AS lsb 
WHERE orders.`user_id` = lsb.id;
-- 调用存储过程
CALL mypro();
-- 存储过程分为  三类参数 in 输入 out 输出参数类似于返回值  inout 输入输出
CALL mypro1(7566);
-- 定义会话变量
SET @r=0;
-- 用会话变量接收，输出参数的值
CALL mypro2(7782,@r);
-- 查看会话变量的值
SELECT @r;
SET @num=7788;
CALL mypro3(@num);
SELECT @num;
SET @str='';
CALL mypro4(4,@str);
SELECT @str;
SET @sum=0;
CALL mypro5(100,@sum);
SELECT @sum;

Geek Li

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MYSQL——MYSQL的基本使用-约束

目录1.约束2.主键约束3.唯一约束和非空约束4.外键约束5.级联更新和级联删除6.处理多对多7.多表查询练习1练习28.自连接查询1.约束作用:为了保证数据的有效性和完整性mysql中常用的约束:主键约束(primary key) 自增长约束 auto_incrment 加在整数型的字段配和主键约束来使用唯一约束(unique) 非空约束(not null) 外键约束(foreign key) sex ENUM('男','女') -- 把一个字段
复制链接

扫一扫