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)
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)
按数量：单个、复合主键(不推荐)
[En]
By quantity: single, compound primary key (not recommended)
性质：自然、业务主键(不推荐)
[En]
By nature: natural, business primary key (not recommended)
在实际开发中，不要使用业务中的属性作为主键。
[En]
In actual development, do not use an attribute in the business as the primary key.
主键约束只能有一个。
功能：是一条数据的唯一标识
[En]
Function: it is the unique identification of a piece of data
主键值
主键约束
主键字段
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)
FOREIGN KEY约束表明这两个表之间存在父子关系。
[En]
The foreign key constraint shows that there is a parent-child relationship between the two tables.
在建表的时候
例如：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
创建表时，请先创建父表，然后再创建子表
[En]
When creating a table, create the parent table first, and then create the child table
若要添加数据，请首先添加父表，然后添加子表
[En]
To add data, first add the parent table, and then add the child table
删除数据，先删除子表中的数据，再删除父表中的数据
[En]
Delete data, first delete the data in the child table, and then delete the data in the parent table
删除表，先删除子表，再删除父表
[En]
Delete the table, delete the child table first, and then delete the parent table
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
Original: https://www.cnblogs.com/journeyhch/p/15563870.html
Author: journeyhch
Title: mysql常用语句 4 + mysql的约束(非空，唯一，主键，外键)
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/508036/
转载文章受原作者版权保护。转载请注明原作者出处！