最新公告
  • 欢迎您光临菡秭网,本站秉承服务宗旨 履行“站长”责任,销售只是起点 服务永无止境!立即加入我们
  •  

    mysql建表约束.sql

    ---------------主键约束 -------------
    它能够唯一确定一张表中的一条记录,也就是我们通过给某个字段添加约束,就可以使得该字段不重复且不为空

    create table user(
    id int primary key,
    name varchar(20)
    );
    mysql> desc user;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(20) | YES | | NULL | |
    +-------+-------------+------+-----+---------+-------+

    insert into user values(1,'张三');
    mysql> insert into user values(1,'张三');
    Query OK, 1 row affected
    mysql> insert into user values(1,'张三');
    1062 - Duplicate entry '1' for key 'PRIMARY'

    主键只能独自存在一个!!!!

    mysql> insert into user values(2,'张三');
    mysql> select * from user;
    +----+------+
    | id | name |
    +----+------+
    | 1 | 张三 |
    | 2 | 张三 |
    +----+------+

    mysql> insert into user values(null,'张三');
    1048 - Column 'id' cannot be null
    ----主键约束不能为空!!!!!------

    ---------联合主键---------
    --只要联合的主键值加起来不重复就可以,但两个主键都不可以为空!
    create table user2(
    id int,
    name varchar(20),
    password varchar(20),
    primary key(id,name)
    );
    mysql> insert into user2 values(1,'张三','123');
    Query OK, 1 row affected
    mysql> insert into user2 values(2,'张三','123');
    Query OK, 1 row affected

    mysql> select * from user2;
    +----+------+----------+
    | id | name | password |
    +----+------+----------+
    | 1 | 张三 | 123 |
    | 2 | 张三 | 123 |
    +----+------+----------+
    2 rows in set

    ------自增约束-----------
    name varchar(20))' at line 3
    mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(20));
    Query OK, 0 rows affected
    mysql> insert into user3(name) values('张三');
    Query OK, 1 row affected
    mysql> select * from user3;
    +----+------+
    | id | name |
    +----+------+
    | 1 | 张三 |
    +----+------+
    1 row in set

    mysql> insert into user3(name) values('张三');
    Query OK, 1 row affected
    mysql> select * from user3;
    +----+------+
    | id | name |
    +----+------+
    | 1 | 张三 |
    | 2 | 张三 |
    +----+------+
    2 rows in set

    -------------如果我们创建表的时候,忘记创建吗主键约束了?该怎么办?------
    create table user4(
    id int ,
    name varchar(20)
    );
    mysql> desc user4;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | YES | | NULL | |
    | name | varchar(20) | YES | | NULL | |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set
    ----修改表结构,添加主键
    alter table user4 add primary key(id);
    mysql> desc user4;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(20) | YES | | NULL | |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set

    -----如何删除?
    alter table user4 drop primary key;
    mysql> desc user4;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | | NULL | |
    | name | varchar(20) | YES | | NULL | |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set
    ------使用modify修改字段,添加约束
    alter table user4 modify id int primary key;
    mysql> desc user4;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(20) | YES | | NULL | |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set

    -------------唯一约束----------
    create table user5(
    id int,
    name varchar(20)
    );
    alter table user5 add unique(name);
    mysql> desc user5;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | YES | | NULL | |
    | name | varchar(20) | YES | UNI | NULL | |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set

    insert into user5 values(1,'张三');
    mysql> insert into user5 values(1,'张三');
    1062 - Duplicate entry '张三' for key 'name'
    -----name=李四
    mysql> insert into user5 values(1,'李四');
    Query OK, 1 row affected

    ---------------申请unique的几种方法------------------
    create table user6(
    id int,
    name varchar(20),
    unique(name)
    );
    create table user7(
    id int,
    name varchar(20) unique
    );
    -----------------unique(id,name)表示两个键在一起不重复就行
    create table use8(
    id int,
    name varchar(20) unique
    unique(id,name)
    );

    -----------------如何删除唯一约束?
    alter table user7 drop index name;------------不加index会把name整个字段给删除了
    mysql> desc user7;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | YES | | NULL | |
    | name | varchar(20) | YES | | NULL | |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set

    --------------modeify添加
    alter table user7 modify name varchar(20) unique;
    mysql> desc user7;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | YES | | NULL | |
    | name | varchar(20) | YES | UNI | NULL | |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set

    -----总结:
    1.建表的时候就添加约束
    2.可以使用alter....add.....
    3.alter...modif.......
    4.删除alter.....drop.......

    --------------------------非空约束--------------
    ----约束的字段不能为空null
    mysql> create table user9(
    -> id int,
    -> name varchar(20) not null unique);
    Query OK, 0 rows affected
    mysql> desc user9;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | YES | | NULL | |
    | name | varchar(20) | NO | PRI | NULL | |
    +-------+-------------+------+-----+---------+-------+
    id为null,insert into user9(id) values(1)不可以执行,name不能为Null
    mysql> insert into user9 values(1,"张三")

    -----默认约束--------------------
    ---就是当我们插入字段值的时候,如果没有传值,就会使用默认值deafault
    mysql> create table user10(
    -> id int,
    -> name varchar(20),
    -> age int default 10
    -> );
    Query OK, 0 rows affected
    mysql> desc user10;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | YES | | NULL | |
    | name | varchar(20) | YES | | NULL | |
    | age | int(11) | YES | | 10 | |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set
    mysql> insert into user10 (id,name) values(1,'李四');///////这里没有定义age的值,但默认为10
    Query OK, 1 row affected
    mysql> select*from user10;
    +----+------+-----+
    | id | name | age |
    +----+------+-----+
    | 1 | 李四 | 10 |
    +----+------+-----+
    ---------------------------传了值就不会使用默认值------------------
    mysql> insert into user10 values(1,'zhangsan',19);
    Query OK, 1 row affected
    mysql> select * from user10;
    +----+----------+-----+
    | id | name | age |
    +----+----------+-----+
    | 1 | 李四 | 10 |
    | 1 | zhangsan | 19 |
    +----+----------+-----+
    2 rows in set
    ------------------------------------------------------------

    ------------------------外键约束--------------------------
    --涉及到两个表:父表,子表
    --主表,附表。

    --班级
    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)
    );

    mysql> desc students;
    +----------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(20) | YES | | NULL | |
    | class_id | int(11) | YES | MUL | NULL | |
    +----------+-------------+------+-----+---------+-------+
    3 rows in set

    insert into classes values(1,'一班');
    insert into classes values(2,'二班');
    insert into classes values(3,'三班');
    insert into classes values(4,'四班');
    mysql> select * from classes;
    +----+--------+
    | id | name |
    +----+--------+
    | 1 | 一班 |
    | 2 | 浜岀彮 |
    | 3 | 涓夌彮 |
    | 4 | 鍥涚彮 |
    +----+--------+
    4 rows in set------------------这里我是四个命令一起执行的,出现了乱码,要注意有些编辑器不能将代码一起复制进去执行

    mysql> insert into students values(1001,'张三',1);
    Query OK, 1 row affected

    mysql> insert into students values(1001,'张三',6);
    1062 - Duplicate entry '1001' for key 'PRIMARY'
    在学生表里只能插入班级表里存在班级的班级号5,这里并不存在6班,所以出现了错误

    --1.主表(父表)classes中没有的数据值,在副表(子表)中,是不可以使用的
    --2.主表中的记录被副表引用,是不可以被删除的。
    mysql> delete from classes where id= 1;
    1451 - Cannot delete or update a parent row: a foreign key constraint fails (`gramdem`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

    -----------------------------------------------------------------------栗子------------------
    create table depatment(
    Department int not null primary key comment '部门编号,关键字',
    DepartmentName varchar(30) not null comment '部门名称',
    Manager char(8) comment '部门主管',
    Depart_Desdription varchar(50) comment '备注,有关部门的说明'
    );

    create table Employee(
    EmployeeID int not null primary key,
    EmployeeName varchar(50) not null comment '姓名',
    Sex char(2) not null check(sex in('男','女')),
    irthDate datetime comment '出生年月',
    HireDate datetime comment '聘任日期',
    Salary decimal comment '工资',
    DepartmentID int not null ,
    foreign key(DepartmentID) references depatment(Department)
    );

    create table Customer(
    CustomerID int not null primary key,
    CompanyName varchar(50) not null comment '公司名称',
    ContacName char(8) not null comment '联系人的姓名',
    Phone varchar(15) comment '联系人电话',
    address varchar(100) comment '客户的地址',
    EmailAddress varchar(20) comment '客户的Email地址'
    );
    create table Provider(
    ProviderID int not null primary key,
    ProviderName varchar(50) comment '供应商名称',
    ContactName char(8) comment '联系人的姓名',
    ProviderPhone varchar(15) comment '供应商联系电话',
    Provideraddress varchar(100) comment '供应商的地址',
    ProviderEmail varchar(20) comment '供应商的email地址'
    );
    create table Product(
    ProductId int not null primary key,
    ProductName varchar(50) not null comment '商品名称',
    price decimal(18,2) comment '单价',
    ProductStockNumber int comment '现有库存量',
    ProductSellNumber int comment '已经销售的商品量'
    );
    create table Sell_Order(
    SellOrderID int not null primary key,
    ProductID int comment '商品编号,描述该订单定够的商品',
    EmployeeID int comment'员工号,描述该订单由哪位员工签订',
    CustomID int comment '客户号,描述该订单与哪位客户签订',
    SellOrderNumber int comment '订货数量',
    SellOrderDate datetime comment'订单签订的日期',
    foreign key(ProductID) references Product(ProductID),
    foreign key(EmployeeID) references Employee(EmployeeID),
    foreign key(CustomId) references Customer(CustomerID)
    );
    create table Purchase_order(
    PurchaseOrderId int not null primary key,
    ProductID int comment '商品编号,描述该订单定够的商品',
    EmployeeID int comment'员工号,描述该订单由哪位员工签订',
    CustomID int comment '客户号,描述该订单与哪位客户签订',
    PurchaseOrderNumber int comment '采购数量',
    PurchaseOrderDate datetime comment '订单签订的日期',
    foreign key(ProductID) references Product(ProductID),
    foreign key(EmployeeID) references Employee(EmployeeID),
    foreign key(CustomId) references Customer(CustomerID)
    );

    常见问题FAQ

    免费下载或者VIP会员专享资源能否直接商用?
    本站所有资源版权均属于原作者所有,这里所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。更多说明请参考 VIP介绍。
    提示下载完但解压或打开不了?
    最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用百度网盘软件或迅雷下载。若排除这种情况,可在对应资源底部留言,或 联络我们.。
    找不到素材资源介绍文章里的示例图片?
    对于PPT,KEY,Mockups,APP,网页模版等类型的素材,文章内用于介绍的图片通常并不包含在对应可供下载素材包内。这些相关商业图片需另外购买,且本站不负责(也没有办法)找到出处。 同样地一些字体文件也是这种情况,但部分素材会在素材包内有一份字体下载链接清单。
    下载的手机APP提示非法包不能安装?
    因为修改版更改了非官方签名,只要手机自带应用商店对该APP有收录都会提示非法包,一般在提示页面会有一行小字“直接安装”即可。
    • 89会员总数(位)
    • 73资源总数(个)
    • 2本周发布(个)
    • 0 今日发布(个)
    • 132稳定运行(天)

    提供最优质的资源集合

    立即查看 了解详情
    升级SVIP尊享更多特权立即升级
    Copy Protected by Chetan's WP-Copyprotect.