SQL基础——表的基本操作-DML

简介：个人学习分享，如有错误，欢迎批评指正。

DML是指数据操作语言，英文全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新。

1.表数据插入

语法格式：

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些列

insert into 表 values (值1,值2,值3...);     //向表中插入所有列

 例子：

insert into stu (sid,name,gender,age,birth,address,score)
			values (1001,'张三','男',18,'2001-12-23','北京',85.5);
			
insert into stu (sid,name,gender,age,birth,address,score)
			values (1002,'李四','男',19,'2002-12-23','上海',82.5),
					(1003,'王五','女',17,'2003-12-23','深圳',95.5);
					
insert into stu (sid) values(1004);
insert into stu (sid,name) values(1005,'赵六');

insert into stu values (1006,'钱博','女',16,'2002-10-23','上海',91.5),
						(1007,'李九','男',29,'2002-12-03','上海',62.5);

 2.表数据修改

语法格式：

update 表名 set 字段名=值,字段名=值...;

update 表名 set 字段名=值,字段名=值... where 条件;

 例子：

-- 将所有学生的地址修改为重庆
update stu set address = '重庆';
-- 讲id为1004的学生的地址修改为北京 
update stu set address = '北京' where sid = 1004;
-- 讲id大于1004的学生的地址修改为北京 
update stu set address = '上海' where sid >1004;
-- 讲id为1005的学生的地址修改为北京，成绩修成绩修改为100
update stu set address = '北京', score = 100 where sid = 1005;

  3.表数据删除

语法格式：

delete from 表名 [where 条件];

truncate table  表名 或者 truncate 表名

 例子：

-- 1.删除sid为1004的学生数据
delete from stu where sid  = 1004;
-- 2.删除表所有数据
delete from stu;
-- 3.清空表数据
truncate table stu;
truncate stu;

注意：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table ，可以理解为是将整个表删除，然后再创建该表；

4.MySQL约束

约束（constraint）实际上就是表中数据的限制条件。表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。

4.1.MySQL约束-主键约束

MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。

主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。

每个表最多只允许一个主键。 主

键约束的关键字是：primary key 。

当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

操作-添加单列主键:创建单列主键有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键

方式1-语法：

-- 在 create table 语句中，通过 PRIMARY KEY 关键字来指定主键。

--在定义字段的同时指定主键，语法格式如下：

create table 表名(  

          ...    

        <字段名> <数据类型> primary key  

         ... )

方式1-实现：

create table mydb1.emp1(
	eid int primary key,
	name varchar(20),
	deptID int,
	salary double
)

方式2-语法：

--在定义字段之后再指定主键，语法格式如下：

create table 表名(  

         ...  

         [constraint <约束名>] primary key [字段名]

        );

 方式2-实现：

create table mydb1.emp2(
	eid int ,
	name varchar(20),
	deptID int,
	salary double,
	primary key(eid)
)

insert into emp2(eid, name, deptID,salary) values(1001,'张三',10,5000);
insert into emp2(eid,name, deptID,salary) values(1002,'李四',20,6000);
insert into emp2(eid,name, deptID,salary) values(1003,'王五',30,4000);

操作-添加多列主键(联合主键）:所谓的联合主键，就是这个主键是由一张表中多个字段组成的。 注意：  

 1. 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。  

 2. 一张表只能有一个主键，联合主键也是一个主键

语法：

create table 表名(  

         ...    

        primary key （字段1，字段2，…,字段n)

        );

实现：

create table mydb1.emp3(
	eid int ,
	name varchar(20),
	deptID int,
	salary double,
	primary key(eid,deptID)
)

insert into emp3 values(1001,'张三',10,5000)
insert into emp3 values(1001,'张三',20,5000)
insert into emp3 values(null,'张三',20,5000)

通过修改表结构添加主键：主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。

 语法：

create table 表名(  

         ...    

        );

alter table <表名> add primary key（字段列表);

实现：

create table mydb1.emp4(
	eid int ,
	name varchar(20),
	deptID int,
	salary double
)
alter table emp4 add primary key(eid);

删除主键约束：一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

 语法：

alter table <数据表名> drop primary key;

实现：

-- 删除单列主键 
alter table emp1 drop primary key;
 
-- 删除联合主键 
alter table emp3 drop primary key;

4.2.MySQL约束-自增长约束

特点：

默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。

一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。

auto_increment约束的字段必须具备 NOT NULL 属性。

auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等。

auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

指定自增字段初始值：如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加

-- 方式1，创建表时指定
create table mydb1.t_user1(
	id int primary key auto_increment,
	name varchar(20)
);

insert into t_user2 values(null,'张三');
insert into t_user2(name) values('李四');

create table mydb1.t_user2(
	id int primary key auto_increment,
	name varchar(20)
)auto_increment = 100;

-- 方式2，创建表之后指定
create table mydb1.t_user3(
	id int primary key auto_increment,
	name varchar(20)
);
alter table t_user3 auto_increment = 200;

insert into t_user3 values(null,'张三');
insert into t_user3(name) values('李四');

delete和truncate在删除后自增列的变化：delete数据之后自动增长从断点开始； truncate数据之后自动增长从默认起始值开始。

4.3.MySQL约束-非空约束

MySQL 非空约束（not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

添加非空约束

 语法：

方式1：<字段名><数据类型> not null;

方式2：alter table 表名 modify 字段 类型 not null;

实现：

-- 方式1，创建表时指定
create table mydb1.t_user4(
	id int,
	name varchar(20) not null,
	address varchar(20) not null
);

insert into t_user4(id) values(1001);
insert into t_user4(id,name, address) values(1001,null,null);
insert into t_user4(id,name, address) values(1001,'null','null');
insert into t_user4(id,name, address) values(1001,'','');

-- 方式2，创建表后修改

create table t_user7 ( 
  id int , 
  name varchar(20) , -- 指定非空约束 
  address varchar(20) -- 指定非空约束 
); 
alter table t_user7 modify name varchar(20) not null; 
alter table t_user7 modify address varchar(20) not null;

删除非空约束

-- alter table 表名 modify 字段 类型 
alter table t_user7 modify name varchar(20) ; 
alter table t_user7 modify address varchar(20) ;

4.4.MySQL约束-唯一约束

唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。

语法：

方式1：<字段名> <数据类型> unique

方式2： alter table 表名 add constraint 约束名 unique(列);

添加唯一约束-方式1

-- 创建表时指定
create table mydb1.t_user8 ( 
 id int , 
 name varchar(20) , 
 phone_number varchar(20) unique -- 指定唯一约束 
);

insert into t_user8 values(1001,'张三',138);
insert into t_user8 values(1002,'张三2',139);

添加唯一约束-方式2

create table t_user9 ( 
  id int , 
  name varchar(20) , 
  phone_number varchar(20) -- 指定唯一约束 
); 
alter table t_user9 add constraint unique_ph unique(phone_number);

insert into t_user9 values(1001,'张三',138);
insert into t_user9 values(1002,'张三2',138);

删除唯一约束

-- alter table <表名> drop index <唯一约束名>;
alter table t_user9 drop index unique_ph;

4.5.MySQL约束-默认约束

MySQL 默认值约束用来指定某列的默认值。

语法：

方式1： <字段名> <数据类型> default <默认值>;

方式2: alter table 表名 modify 列名 类型 default 默认值;

添加默认约束-方式1

create table mydb1.t_user10 ( 
  id int , 
  name varchar(20) , 
  address varchar(20) default '北京' -- 指定默认约束 
);

添加默认约束-方式2

-- alter table 表名 modify 列名 类型 default 默认值; 

create table t_user11 ( 
  id int , 
  name varchar(20) , 
  address varchar(20)  
);
alter table t_user11 modify address varchar(20) default  ‘北京’;

删除默认约束

-- alter table <表名> modify column <字段名> <类型> default null; 

insert into t_user10(id,name) values(1001,'张三');
alter table t_user10 modify address varchar(20) default null

4.6.MySQL约束- 零填充约束

1、插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0

2、zerofill默认为int(10)

3、当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128~+127，无符号为0~256。

操作

create table t_user12 ( 
  id int zerofill , -- 零填充约束
  name varchar(20)   
);

删除

alter table t_user12 modify id int;

该内容主要是本人对哔站up黑马程序员的mysql课程的学习总结！

结~~~