mysql表键、增删改查

最新推荐文章于 2024-09-14 18:46:49 发布

水星灭绝

最新推荐文章于 2024-09-14 18:46:49 发布

阅读量122

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/wulong710/article/details/109616686

版权

数据库专栏收录该内容

21 篇文章 1 订阅

订阅专栏

本文介绍MySQL中唯一键(unique index)的使用方法及其对数据重复性的限制，并演示了如何创建带有唯一键约束的表，进行数据插入操作时的响应情况。此外，还展示了外键(foreign key)的应用及批量插入数据的方法。

摘要由CSDN通过智能技术生成

唯一键 unique index不许重复

use fishdb;
drop table if exists testtb;

create table if not exists testtb(
mid int auto_increment primary key,
user_id int,
mname   varchar(40),
unique index(user_id)
);

结果：

mysql> insert into testtb (uer_id,mname) values (89,"i89");
ERROR 1054 (42S22): Unknown column 'uer_id' in 'field list'
mysql> insert into testtb (user_id,mname) values (89,"i89");
Query OK, 1 row affected (0.01 sec)

mysql> insert into testtb (user_id,mname) values (89,"i89");
ERROR 1062 (23000): Duplicate entry '89' for key 'user_id'
mysql>

外键 foreign key

use fishdb;
drop table if exists foreigntb;
drop table if exists testtb;


create table if not exists testtb(
mid int auto_increment primary key,
user_id int,
mname   varchar(40),
unique index(user_id)
); 

create table if not exists foreigntb(
fid int auto_increment primary key,
user_id int,
foreign key (user_id) references testtb(user_id)
);

批量插入：

use fishdb;
drop table if exists ftb;
drop table if exists testtb;

create table if not exists testtb(
mid int auto_increment primary key,
user_id int,
mname varchar(40)
); 

create table if not exists ftb(
fid int auto_increment primary key,
user_id int,
fname varchar(40) default ""
);



insert into testtb (user_id,mname) values (89,"m89"),(90,'m90'),(91,'m91');
-- insert into ftb (user_id) select user_id from testtb;

insert into ftb (user_id) values (7),(83),(91),(90);
select * from ftb where user_id in (select user_id from testtb);

结果：

mysql> select * from ftb where user_id in (select user_id from testtb);
+-----+---------+-------+
| fid | user_id | fname |
+-----+---------+-------+
|   3 |      91 |       |
|   4 |      90 |       |
+-----+---------+-------+
2 rows in set (0.00 sec)

显示表结构：

mysql> show create table ftb;

结果：

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ftb   | CREATE TABLE `ftb` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `fname` varchar(40) DEFAULT '',
  PRIMARY KEY (`fid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

显示表

增

use fishdb;
drop table if exists foreigntb;
drop table if exists testtb;

create table if not exists testtb(
mid int auto_increment primary key,
user_id int,
mname varchar(40)
); 

create table if not exists ftb(
fid int auto_increment primary key,
user_id int,
fname varchar(40) default ""
);



insert into testtb (user_id,mname) values (89,"m89"),(90,'m90'),(91,'m91');
insert into ftb (user_id) select user_id from testtb;

删


			delete from tb12;
			delete from tb12 where id !=2 
			delete from tb12 where id =2 
			delete from tb12 where id > 2 
			delete from tb12 where id >=2 
			delete from tb12 where id >=2 or name='a'

改

update tb12 set name='a' where id>12 and name='xx'
update tb12 set name='a',age=19 where id>12 and name='xx'

查

select * from tb12;
select id,name from tb12;
select id,name from tb12 where id > 10 or name ='xxx';
select id,name as cname from tb12 where id > 10 or name ='xxx';
select name,age,11 from tb12;

select * from tb12 where id != 1
select * from tb12 where id in (1,5,12);
select * from tb12 where id not in (1,5,12);
select * from tb12 where id in (select id from tb11)
select * from tb12 where id between 5 and 12;
select * from tb12 where name like "a%"
select * from tb12 where name like "aa_"