百木园-与人分享,
就是让自己快乐。

mysql常用语句 4 + mysql的约束(非空,唯一,主键,外键)

1.更新语句
update dept1 set loc = \'wz\',dname = \'xueshenghui\' where deptno = 10;

mysql> update dept1 set loc = \'wz\',dname = \'xueshenghui\' where deptno = 10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from dept1;
+--------+-------------+---------+
| DEPTNO | DNAME | LOC |
+--------+-------------+---------+
| 10 | xueshenghui | wz |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+-------------+---------+
4 rows in set (0.00 sec)

更新语句格式:update 表名 set 字段=\'\',字段=\'\' where 字段=\'\';

2.更新所有记录,不加条件即可

update dept1 set dname = \'bangongshi\', loc = \'cqust\' ;
mysql> update dept1 set dname = \'bangongshi\', loc = \'cqust\' ;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> select * from dept1;
+--------+------------+-------+
| DEPTNO | DNAME | LOC |
+--------+------------+-------+
| 10 | bangongshi | cqust |
| 20 | bangongshi | cqust |
| 30 | bangongshi | cqust |
| 40 | bangongshi | cqust |
+--------+------------+-------+
4 rows in set (0.00 sec)

3.删除数据

delete from dept1 where deptno = 10;
mysql> delete from dept1 where deptno = 10;//这里如果不加条件会删除表中的所有数据。
和drop分开区别。drop是删除表,delete是删除数据。
Query OK, 1 row affected (0.01 sec)

mysql> select * from dept1;
+--------+------------+-------+
| DEPTNO | DNAME | LOC |
+--------+------------+-------+
| 20 | bangongshi | cqust |
| 30 | bangongshi | cqust |
| 40 | bangongshi | cqust |
+--------+------------+-------+
3 rows in set (0.00 sec)

  • delete from 表名//删除小表数据,可以回滚。
    truncate table 表名//删除大表数据,不可回滚
  • 5.约束:对字段进行合理的限制。
    not null非空,unique 唯一,primary key 主键,foreign key外键。

    6.非空约束
    drop table if exists t_user;
    create table t_user(

    id int(3) not null default 100,
    name varchar(7) default \'hch\'

    );
    insert into t_user (id,name) values(1,\'h1\');
    insert into t_user (id,name) values(null,\'h2\');

    //这里不能为空
    mysql> insert into t_user (id,name) values(null,\'h2\');
    ERROR 1048 (23000): Column \'id\' cannot be null

    7.唯一约束,存在列级约束,表级约束
    drop table if exists t_user;
    create table t_user(

    id int(3) ,
    name varchar(7),
    unique(id,name)

    );
    insert into t_user (id,name) values(1,\'h1\');
    insert into t_user (id,name) values(1,\'h2\');

    //这里添加了唯一约束,
    mysql> insert into t_user (id,name) values(1,\'h2\');
    ERROR 1062 (23000): Duplicate entry \'1\' for key \'id\'
    mysql>

    mysql> create table t_user(
    ->
    -> id int(3) unique ,
    -> name varchar(7) unique//这里两个字段都添加约束,要求都不一样才可以成功。
    -> );
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into t_user (id,name) values(1,\'h1\');
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into t_user (id,name) values(2,\'h2\');
    Query OK, 1 row affected (0.00 sec)

    mysql> create table t_user(
    ->
    -> id int(3) ,
    -> name varchar(7),
    -> unique(id,name)//表级约束,插入数据要求两个联合起来唯一
    -> );
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into t_user (id,name) values(1,\'h1\');
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into t_user (id,name) values(1,\'h2\');
    Query OK, 1 row affected (0.00 sec)

    8.主键(primary key)
    按照数量分:单一,复合主键(不推荐)
    按照性质:自然,业务主键(不推荐)
    在实际开发中不要把业务中的某一个属性作为主键。
    主键约束只能有一个。
    作用:是一条数据的唯一标识
    主键值
    主键约束
    主键字段

    drop table if exists t_user;
    create table t_user(

    id int(3) primary key auto_increment ,
    name varchar(7)

    );
    insert into t_user (name) values(\'h1\');
    insert into t_user (name) values(\'h2\');
    insert into t_user (name) values(\'h3\');
    insert into t_user (name) values(\'h4\');
    insert into t_user (name) values(\'h5\');
    insert into t_user (name) values(\'h6\');

    mysql> create table t_user(
    ->
    -> id int(3) primary key ,
    -> name varchar(7)
    ->
    -> );
    Query OK, 0 rows affected (0.02 sec)

    mysql> insert into t_user (id,name) values(1,\'h1\');
    Query OK, 1 row affected (0.00 sec)
    //主键不能重复,而且不能为空
    mysql> insert into t_user (id,name) values(1,\'h2\');
    ERROR 1062 (23000): Duplicate entry \'1\' for key \'PRIMARY\'

    mysql> create table t_user(
    ->
    -> id int(3) primary key auto_increment ,//主键自增,这里主键只是一个标识作用
    -> name varchar(7)
    ->
    -> );
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into t_user (name) values(\'h1\');
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into t_user (name) values(\'h2\');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user (name) values(\'h3\');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user (name) values(\'h4\');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user (name) values(\'h5\');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user (name) values(\'h6\');
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_user;
    +----+------+
    | id | name |
    +----+------+
    | 1 | h1 |
    | 2 | h2 |
    | 3 | h3 |
    | 4 | h4 |
    | 5 | h5 |
    | 6 | h6 |
    +----+------+
    6 rows in set (0.00 sec)

    9.外键(foreign key)
    外键约束表现出来两张表存在父子关系。
    在建表的时候
    例如:t_teacher(父表)
    t_id t_name
    1 laoyu
    2 laodu
    3 niexin
    4 ruanqidong

    t_student(子表)
    stu_id stu_name t_name(添加外键约束)
    1 h1 laoyu//这里的数据必须是父表中的数据。以此获得联系
    2 h2 laoyu
    3 h3 laoyu
    4 h4 laodu

    在创建表的时候,先创建父表,再创建子表
    添加数据,先添加父表,再添加子表
    删除数据,先删除子表中的数据,再删除父表中的数据
    删表,先删除子表,再删除父表
    drop table if exists t_teacher2;
    create table t_teacher2(
    t_id int(2) primary key,
    t_name1 varchar(10)
    );
    drop table if exists t_student2;
    create table t_student2(
    stu_id int(2) ,
    stu_name varchar(10),
    t_name2 int (10),
    foreign key (t_name2) references t_teacher2(t_id)//这里的字段一般是主键,至少具有唯一性。
    外键值可以为null

    );

    来源:https://www.cnblogs.com/journeyhch/p/15563870.html
    图文来源于网络,如有侵权请联系删除。

    未经允许不得转载:百木园 » mysql常用语句 4 + mysql的约束(非空,唯一,主键,外键)

    相关推荐

    • 暂无文章