MySql之表的增删改查

最新推荐文章于 2023-11-05 01:06:39 发布

程序员小王java

最新推荐文章于 2023-11-05 01:06:39 发布

阅读量412

点赞数

分类专栏： mysql数据库文章标签： mysql 数据库 sql

程序员小王

本文链接：https://blog.csdn.net/weixin_44385486/article/details/120624756

版权

mysql数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

day03 表的增删改查

回顾讲解的内容:

注意：DQL：Data Query Language 数据查询语言

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QLmaY2Lf-1633503346242)(image/image.png)]

一、分析表结构

例：员工表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0viPdllH-1633503346245)(image/image_1.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DtQwVtHP-1633503346247)(image/image_2.png)]

二、表的创建、修改与销毁 DDL

DDL (Data Definition Language) ：数据定义语言，定义库，表结构等 ，包括create,drop,alter等

前提：数据库 DataBase

创建数据库

# 创建数据库的命令
create database database_name;
create database 数据库名;

#在mysql中字符集名称不要使用utf-8
create database 数据库名 charset 'utf8'; 
create database 数据库名 charset 'gbk';

使用数据库

use database_name;

查询数据库

show databases;

删除数据库

drop database database_name;
drop database suns1;

查看某个库下的所有表格

#前提是前面有use 数据库名;的语句
show tables ;  
show tables from 数据库名;

1、建表语句

表格的组成

语法：

create table [if not exists] 表名(
 字段名 数据类型 [约束], 
 字段名 数据类型 [约束], ... 
 );


# id name password balance 
create table table_name(
   列名1 数据类型1,
   列名2 数据类型2
   .....
);

create table t_account(
   id int,
   name varchar2(20),
   password varchar(24),
   balance double
);

表名和字段名

1. 合法的标识符即可。 
2. 表名一般以t_开头，字段名多个单词间以下划线分隔。 
3. 表名和字段名不区分大小写。

2、MySql数据类型

不同的数据库产品，虽然大面积的数据类型是相通的，但会存在差异

不同的数据库产品，虽然大面积数据类型是相通的，但是会存在稍许差异

MYSQL  double

Oracle  number(5,2)

MYSQL  varchar(12)

Oracle   varchar2(12) varchar(12)

作用：明确一列可以存储什么样的数据。

数据类型	解释	注意事项
tinyint [age]	1字节	有符号数：-128~127 无符号数(unsigned)：0~255
smallint	2字节	数有符号数：-32768~32767 无符号数：0~65535
mediumint	3字节	有符号数：-8833608~8833607 无符号数：0~1677215
int、integer[重点]	4字节整数	有符号数：-2147483648~2147483647 无符号数：0~4294967295
bigint	8字节整数	有符号数：-9223372036854775808~9223372036854775807 无符号数：0~18446744073709551615
float[重点]	4字节	-3.402823466E+38~3.402823466E+38
double[重点]	8字节	-1.7976931348623157E+308~1.7976931348623157E+308
decimal（m,n）[重点]十进制	定点数	m表示精度（数字有效长度），d表示标度（小数点的位数） m最大值65，默认值10；d最大值30，默认值0
char(n)	定长字符串255字节	无论保存的数据多少，一定占n的字符空间，n最大255 M 男 F 女
varchar(n)	变长字符串	根据保存的数据多少，占据对应的字符空间，n最大65535 用户名：suns
blob	二进制形式的长文本数据	图片，音频 .exe
text	大文本类型	能保存4G大小文字
enum	枚举类型	在给定的范围内选择一个值，比如性别 enum(‘F’,‘M’) M”是英文“male”的缩写，中文译为男性；“F”是英文“female”的缩写，中文译为女性；“male”和“female”用于表示性别时，
date	4字节	包含年月日，1000-01-01~9999-12-31
time	3字节	包含时分秒，-838:59:59~838:59:59
datetime[重点]	8字节	包含年月日时分秒,1000-01-01 00:00:00~9999-12-31 23:59:59
timestamp【时间戳】	4字节	包含年月日时分秒，1970-01-01 08:00:01~2038-01-19 11:14:07

timestamp和datetime的区别：

timestamp范围比较小

timestamp和时区有关

show variables like 'time_zone'

set time_zone = '+9:00';

实战建议：

1. 如果数字一定大于0，且不会进行减运算，可以使用unsigned无符号数。如非必要，还是建议 使用有符号数。
 2. float和double存在精度问题，实战中很少使用，MySQL后续版本将不再支持上述两个类型。 
 对精度要求较高时使用decimal。
  3. char和varchar在保存多字节字符（比如中文）底层实现是一样的，所以实战中建议使用 varchar 
  4. 实战中通常需要精确到秒，而timestamp的上限很快到达，建议使用datetime

例：创建t_person表

unsigned无符号的

create table t_person(
 person_id int, 
 person_name varchar(50), 
 age tinyint unsigned, 
 --在给定的范围内选择一个值
 sex enum('F','M'), 
 birthday datetime, 
 salary decimal(10,2), 
 mobile varchar(11), 
 address varchar(500)
  );

3、约束

duplicate:重复
作用：约束限制字段值格式和范围。

约束	解释	说明
primary key	主键	用来明确一列为主键列，唯一标识，且非空
not null	非空	约束一列的值不能为null
unique	唯一	约束一列的值不能重复，不检查null的
default	默认	当列没有值时设置默认值，附加值
references	外键约束	明确该列为外键列，并指明引用那一块表的那一列值，外键的值必须来源于另一张表的主键

primary key

1. 主键约束只能给主键列添加。唯一且非空
create table t_account1(
id int primary key,
password varchar(12) bot null,
password varchar(12) unique,
balance double
);

例：普通约束示例

-- 列级约束：直接将约束定义在列中 
create table t_person( 
person_id int primary key, 
person_name varchar(50) unique not null, 
age tinyint unsigned not null,
 sex enum('F','M') not null default 'M', 
 birthday datetime not null,
  salary decimal(10,2) not null, 
  mobile varchar(11) not null, 
  address varchar(500) not null );
  
  -- 表级约束：将约束定义在列定义语句后，not null、default 没有表级约束 
  create table t_person( 
  person_id int ,
  person_name varchar(50) ,
  age tinyint unsigned , 
  sex enum('F','M') not null default 'M', 
  birthday datetime not null, 
  salary decimal(10,2) not null, 
  mobile varchar(11) not null, 
  address varchar(500) not null, 
  -- [constraint 约束名] 约束类型(字段名) 
  constraint pk_person_id primary key(person_id), 
  constraint uq_person_name unique(person_name) );

表级约束：将约束定义在列定义语句后，not null、default 没有表级约束

id——》identify唯一标识

4、表的修改[了解]

# 修改已经存在的列，改数据类型，约束
# 前提这列不能存储数据。
alter table table_name modify name 数据类型 约束

# 修改表的列名
# alter table table_name rename column name to new_name

# 修改表的列名字 
alter table t_test1 change name new_name varchar(12);

# 添加列的名字 
alter table t_test1 add name 数据类型

# 删除列 
alter table t_test1 drop name

1. 对于表进行相关的操作
 创建表（create table) 
 删除表(drop table) 
 修改表(alter table)
2. 对表相关的操作 DDL语言

描述表结构

describe 【数据库名.]表名称;
desc t_test1;

修改字段列的类型( 该列不能有数据 )

  alter table 表名 modify 列名 类型；

例：

alter table student modify age int;

修改字段/列名

alter table 【数据库名.]表名称 change【column】 旧字段名 新字段名 新数据类型;

例：

alter table student rename column address to addr

追加新的列

alter table 表名 add 列名 类型 约束 默认值;

例：

alter table student add email varchar(59) not null;

修改表名

alter table 表名 rename 新表名;

5、表的销毁删除

语法：

drop table [if exists] 表名;
drop table table_name;

例：

drop table t_person; 
**注意：如果两张表有外键引用关系，那么必须先删子表，后删父表** 。

5、表的总结

1.创建表：create 修改表：alter 删除表：drop
2.对表的相关操作DDL语言

三、操作数据

1、添加数据

语法：

insert into table_name(field1,列名，列名,...) 
values(value1，值，值，...);

例：

insert into
 t_person(person_id,person_name,age,sex,birthday,salary,mobile,address) 
 values(1,'xiaohei',18,'M','2000-12-23 12:30:30',10000.00,'187xxxxxxx','郑 州硅谷');

注意：

1. values后小括号中的值要和表名后小括号对应！！！

2.  注意类型与约束

给所有列添加数据，表名后的小括号可以省略
语法：

insert into 表名 
values(值，值，值，...);

例：

insert into t_person 
values(2,'xiaobai',18,'F','2000-12-23 12:30:30',12000.00,'185xxxxxxx','郑 州硅谷');

注意：values前后两个括号中的值数量要一致

主键是自动增长，mysql自动填充值，不建议认为指定id值！

insert into user(name,nick,birthday) values ('Mr_lee','zz22','2000-01-01');

一次插入多条数据【常用】

insert into user9 (name,nick,birthday) values ('Mr_lee','zz22','2000-01-01'),
                                              ('Mr_lee','zz33','2000-01-01'),
                                              ('Mr_lee','zz44','2000-01-01')

省略列名插入，要求values中必须是完整的列对应的值**[不建议]**

 insert into user9 values (1,'Mr_lee','zz','2000-01-01')

2、删除数据

语法：

delete from 表名 
[where 条件];

#全表数据全删除
delete from t_test1 

#根据id删除某些行的数据
delete from t_test1 where id = 10;

delete from t_test1 where id in (1,2,3);

例·：

delete from t_person
 where person_id = 1;

注意：不加条件，则删除表中所有数据。

删除所有数据

表截断：truncate table 表名;//删除表中所有数据 
表截断：直接在物理空间中，将保存数据的空间截断，效率更高
 delete from 表：一行一行的删除，效率比较低。

3、修改数据

语法：

update 表名 
set 列名 = 新值,列名=新值,... 
[where 条件];

update table_name 
set field1=new_value1,field2=new_value2
where....

注意：更新数据时务必指定where条件，否则将会影响所有行的值。

更新id为1的用户的姓名为Mr_lee

update user9
 set name='Mr_lee' 
 where id=1;

 update user9 set 
 name='xiaohei',
 age=30 
 where id = 20;

更新所有人的姓名**（慎用）**

update user9 
set name='Mr_lee' ;

例：

update t_person 
set age = 20,sex = 'F' 
where person_id = 1;

注意：不加where条件，则更新所有行。

4、数据操作的总结

1：数据操作语言

2：数据操作语言：DML Data modify language

3:
DQL:查询语句
DDL：建表相关语句 create alter drop
DML:数据操作语言数据插入 insert 数据修改update 数据删除delete

4：数据库中的增删改查(CRUD) create read update delete
增：insert into  
删：delete from
改：update set
查：select

四、外键（Foreign Key）

概念：外键也是一种约束，主要用于说明该列与其他列的关系。
分析如何让多张表产生关系

表结构：

在student表中增加一列，该列只用于让两张表产生关系：

我们通过student表中的class_id 就可以准确的找到另一张表与它有关系的数据。

该列的数据必须来源于 class 表中的 id 是一种约束

例：

-- 外键约束，必须使用表级约束方式定义  表级约束：constraint
create table t_class( 
class_id int primary key,
 class_name varchar(20) not null );
 
create table t_student(
 student_id int primary key,
  student_name varchar(20) not null, 
  class_id int not null, 
  -- 追加外键（关系列） 
  constraint fk_student_class foreign key(class_id)
   references t_class(class_id) )
   
   **--** ** 我们一般把有外键的表称之为子表（从表），被指向的表称之为父表（主表） **

外键：体现2张表之间关系的。
外键约束 表达 外键列的值必须来源于另一张表的主键

create table t_dept(
id int primary key,
dept_name varchar(12),
dept_code int
);

create table t_employee{
id int primary key,
name varchar(12),
age int,
salary double,
dept_id int,
foreign key(dept_id) references t_dept(id)
}

注意：

1、外键列的值可以重复、可以为null。
2、建表时应该先创建主表再创建从表 
3、删除主表数据时如果该数据被从表指向，
  必须先解除关系（update ,delete），再删除主表数据。

五、事务（Transaction）

作用：保证业务的完整性，要么一起成功要么一起失败
例：转载业务

思考：完整的业务需要执行两次更新操作，但如果第二次更新失败呢？

1、事务的控制

概念：我们在一个事务中，可以使用控制事务语句来控制提交所有操作或者回到事务最初状态、
transaction 事务 [trænˈzækʃn]

控制事务语句	作用
start transaction	开启事务
commit	提交
roolback	回滚（数据还原到事务开始时的状态）

概念：我们在一个事务中，可以使用控制事务语句来控制提交所有操作或者回到事务最初状态

例：

程序员误删数据： 
delete from t_account；--忘记写 
rollback; --回到事务最初状态

2、回滚段

rollback

commit

rollback为什么能够把数据恢复初始状态？

概念：

数据库为每个客户端连接分配的独立区域，当执行insert|update|delete
 操作时会暂时将原数据存储到回滚段中，
如果事务失败将会进行回滚（rollback）。

实现原理：

3、事务的特性【面试】ACID

特性	含义
原子性A （Atomicity） [ˌædəˈmɪsədi]	保证一组sql语句一起成功一起失败，原子操作。
一致性C （Consistency )[kənˈsɪstənsi]	事务执行前后，数据的状态是一致的，是正确的。（老公存，老婆花）
隔离性I (Lsolation)	保证用户并发访问数据安全，一个连接一个事务。
持久性D (Durability) [ˌdʊrəˈbɪləti]	一个事务一旦提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

隔离性，加锁

一致性

六、SQL分类

类别	含义	事务
DQL（Data Query Language）	数据查询语言 select	支持
DDL（Data Definition Language）	数据定义语言 create,alter,drop	隐式提交
DML（Data Manipulation Language）	数据修改语言 insert,update,delete	显式提交
TCL（Transaction Control Lanager）	事务控制语言 commit,rollback。

七、主键自增&列的附加值

作用：生成一段连续的不可重复的数值，每次+1，一般用于生成主键值。

使用方法：

建表时指定主键为自动增长

语法

可在建表主键后使用 AUTO_INCREMENT ,插入数据时可以不用插入主键的值，自动生成

例：

create table t_person( 
person_id int primary key auto_increment, 
person_name varchar(50) unique not null, -- 略.... 
);

插入数据时不必插入主键的值，自动增长

例：

insert into t_person(
person_name,age,sex,birthday,salary,mobile,address) 
values('xiaohei23',18,'M','2000-12-23 12:30:30',10000.00,'187xxxxxxx','郑州硅谷');

注意：在sql语句执行时，无论成功与否 auto_increment 都会自动+1

指定 auto_increment 起始值

语法

alter table 表名 auto_increment = 起始值

为表中所有使用了auto_increment 自增功能的列，指定起始值

获取auto_increment当前值

语法：

select last_insert_id()

1、auto_increment

create table t_dept1(
  id int primary key,
  dept_name varchar(12),
  dept_code int
);

insert into t_dept1 (id,dept_name,dept_code) values (1,'dev','001');
insert into t_dept1 (id,dept_name,dept_code) values (2,'dev','001');

create table t_dept1(
  id int primary key auto_increment, // 主键自动增长
  dept_name varchar(12),
  dept_code int
);

insert into t_dept1 (dept_name,dept_code) values ('dev','001'); mysql 自动的为id 设置值 为1
insert into t_dept1 (dept_name,dept_code) values ('dev','001'); id+1 ---> 2

2、列默认值

create table t_dept2(
  id int primary key auto_increment,
  dept_name varchar(12) default 'xiaohei',
  dept_code int
);

insert into t_dept2 (dept_code) values (1);

MYSQL 
假定 id 5

id  dept_name dept_code
 6  xiaohei          1

3、表附加值

engine 引擎选择:一般使用innodb
default charset 表中的数据编码格式

1、建库最好选择字符集utf-8建表时会继承库的字符集
2、如果建库时没有选择字符集，则建表必须设置字符集，否则会出现中文乱码
3、建议将my.ini配置文件中的字符集更改为utf8则数据库将会继承配置中的字符集`

auto_increment 自增长列的起始值

create table t_xxx(
   name varchar(12) xxx xxx
   ...
   ....
)xxxx;

create table user9(
  id int auto_increment primary key,
  name varchar(20) NOT NULL DEFAULT 'Mr_lee',
  nick varchar(15) UNIQUE,
   birth timestamp
)
ENGINE = InnoDB default charset = utf8 auto_increment = 9;

八、视图

作用：把查询（select）结果存储在数据库中并重新命名，方便重复使用。

1、创建视图

语法：

create view v_user as 查询SQL;

例：

create view v_employee_departments
 as select e.*,d.department_name,d.location_id
  from employees e 
  left join departments d 
  on e.department_id = d.department_id;

2、使用视图

如果表中的数据改变，视图也会改变

语法：

select 列，列,... 
from 视图名 
[where 条件] ...

例：

create view v_employee_departments 
as 
select e.*,d.department_name,d.location_id 
from employees e 
left join departments d 
on e.department_id = d.department_id;

3、销毁视图

语法：

drop view 视图名;

例：

drop view v_employee_departments;

4、视图的特点

视图本质上就是一个复杂的查询SQL
视图本身并不能提升查询效率，只能提升开发效率
视图本身不是表，不存储任何数据

九、索引

概念：索引是数据库为提高查询速度而提供一种机制

从数据库中查询数据和从字典中查询一个字的过程相似。数据库中的索引和字典中的拼音、偏旁部首、笔画这些组成类似，用来提高查询效率的。

- 使用索引提高查询效率

相关语法：

创建：
create index 索引名
 on 表名(列名); 
 
查看：
show index from 表名; 

删除索引：
drop index 索引名 on 表名;

使用索引：

当使用添加了索引的列作为查询条件时，数据库会自动使用索引。 
提前准备千万级的数据能看出索引的效果。

索引的特点

1. 索引用来提高查询效率 

2. 索引也会占用磁盘空间 

3. 索引不是越多越好，一般每张表不要超过5个。
   原因：
   1.索引会占空间，影响性能
   2.影响增删改的效率 
   
**4. 注意，索引不是任何时候都有效，例如在is null或者模糊查询时索引会失效**