mysql 增删改查基础命令

数据库是企业的重要信息资产，在使用数据库时，要注意(查和增,无所谓,但是删和改,要谨慎! )

数据库管理系统(DBMS) :实现对数据的有效组织，管理和存取的系统软件

mysgl

数据库是一个系统， 是一个人机系统，硬件, gs,数据库, DBMS和数据库的用户共同组成。

用户是通过DBMS(各种数据库软件)来对数据库进行操作。

数据库的分类:

mysgl: 关系型数据库。

redis: 非关系型数据库

Es：  索引型数据库

关系型数据库:存储数据的结构是一个二 维表格

表:行列

行:记录，用来描述一个对象信息

列:字段，用来描述对象的一个属性。

关系型数据库: mysgl(中小类型的数据并发请求) oracle（大型数据）

多表关联最多可以关联几张表？3张表

非关系，型数据库（redis）：保存的不是一张表格，Key--->value键值对来保存数据，ky32=12

缓存的是数据库

MySQL的存储引擎: 版本5.5之前默认引擎是MYISAM,不支持事务和外键的存储引擎，适用读 写

5.6之后：默认的存储引擎是innodb,可以支持事务，外键，行基锁定的存储引擎，支持高并发行性能的应用

事务:在数据库当中，一个或者多个操作组成的数据操作的序列。

这些操作要么全部成功，失败一个就全部不执行。确保数据一致性和完整性。

数据库是企业的重要信息资产，在使用数据库时，要注意(查和增,无所谓,但是删和改,要谨慎! )

数据库管理系统(DBMS) :实现对数据的有效组织，管理和存取的系统软件

事务的特点：

最小单位原子性是数据库最小工作单元，要么全部执行成功，要么全部不执行，只要有一个操作时间，整个执行的序列都会被回滚，完成的操作也会被撤销

一致性:  事务执行前后，数据库的完整性约束不能被破坏。只有在满足所有的约束条件情况下，事务才能被提交。

隔离性:   事务的执行是相互隔离。一个事物的执行不能受到其他执行事务的干扰。并发事务之间互相隔离。防止数据不一致的情况发生。

持久性： 事务一旦提交，他所做的所有修改都会保存在数据库中，即使系统崩溃，数据也不丢失，

特点总结:确保数库始终处于一致性的状态，数据的完整性

Mysql 的名词：

1、数据库: database

2、表: table 行: row 列: column

3 索引: index

4视图: view

5存储过程: procedure

6存储函数：function

7触发器: trigger

8用户: user

9权限: privilege

mysgl的语句的规范:

在数据库系统中，sgl不区分大小写,但是建议大写。语句不区分，但是表名是严格区分大小写。

sgl语句可以单行也可以多行书写，但是默认都以;结尾。

关键词不能跨行或者简写。

子语句通常位于度路航，便于编辑，提高可读性。

数据库的命名规则:

必须以字母开头，后面可以包含数据,特殊字符: #_ $

不要使用mysgl的保留字: table select

数据库名 表名 用户名严格区分大小写

数据库的字符类型：

Int :    4个字节，用来存储整数

Char:   固定长度的字符串，用来存储固定长度的字符串

Varchar: 可变长度的字符类型，存储可变长度的字符串（根据类型的长度写）

Float(m,d) : 单精度浮点。存储浮点数，m表示总位数，d表示小数位

double(m,d): 双精度浮点数，存储浮点数，M也是总位数， d,表示小数位。

text:    用于存储大文本数据，文档，或者长字符串。

image:   二进制存储图像，图片,多媒体。

Decimal (5,2): 用于存储固定精度的浮点数数，其中5表示总位数，2表示小数位

Data:  存储日期：yyyy-mm-dd

Datatime: 存储日志 yyyy-mm-dd hh:mm:ss

Timestamp:和datetime格式一致，可以自动的更新为当前的时间戳

Char无论是否定义了值，都会占用固定长度的字节大小，

varchar保存时长度是14 实际长度是5，就占用5个，加一个隐藏符算6个

都是字符串，一定要用单引号引起来

varchar比'char节省磁盘空间

但是varchaer读写速度比char慢

sql语句的分类

DDL :数据定义语言，用于创建数据的对象，创建库 表 索引

CREATE：创建

DROP：删除库 和表

ALTER：修改

DML：数据操作语言，对表中的数据进行管理

Select

Update 更新修改数据

Insert  添加

Delete 删除对表进行的操作

DQL语言：查询数据库语言

Select

DCL: 数据控制语言，设置或者更改数据库用户或者用户的权限

GRANT 赋予权限

REVOKE 取消权限

TCL：事务控制语句，管理数据库当中的事务

Commit 确实提交事务

ROLLBACK:事务提交之后无法回滚

Savepoint 保存点，可以回滚

#查看当前数据库的端口

#查看表的结构

#查询数据库

#使用mysql数据库

#查看当前数据库下有哪些表呢

#查看表的结构

#纵向查看表的结构

#新建数据库

#删除数据库

#创建表Ky32 以下几个列id 是整数型4位 指定属性不能为空，名字指定长度10属性不能为空，分数指定小数5位小数点后2位，密码长度48位，设定主键为id 不能为空和不能重复

create table ky32 (id int(4) not null,

name varchar(10) not null,

sroce decimal(5,2),

passwd char(48),

primary key(id));

Nul和空格的区别

null什么都没有，没有任何的内容描述

#desc 查看表的结构

#插入到Ky32韩文升，密码为空（添加字符串是要引起来）

#详细查询表的结构

#清空表名

#插入到ky32  分数 姓名 密码

#插入数据韩文升 分数  密码456（对列）

#加密处理

#update指定行列的数据进行修改 where指定唯一值（防止重复）

#delete删除表信息指定id 删除

#查询 指定id 进行查询

#查询第4行到7行（加一行）

#查询前2行 只跟一个数字表示前几行

#第二行开始走4个

#指定分数查询

#去重查询

#去重多条件查询

.#多条件查询

#修改表的名称

#添加表32 生日一列

#更新生日

#修改删除生日这列

#修改数据的库的类型

Alter table ky32 modify

扩展语句

##复制通过Like语法直接复制ky11的表结构，不能复制表里的数据

create table test like ky11;

##把ky32 表里面的数据复制到test里面，两个表的数据结构要一致

insert into test select * from ky32;

###创建一张表，test2 的数据从ky32来，表结构也是y32

create table test2 (select * from ky32);

推荐工作中清表用 truncate test1;

清空表的数据，而且会把表的结构重新建立，速度比delete 快，

语句解释：

if not exists ky32 -----这个表的不存在，才会创建

zerofill ---- 自动填充补齐位置 

primary key -----当前表的主键，主键只能有一个，唯一，不能为空

auto_increment -----表示该字段可以自增长，默认从1开始，每条记录会自动递增1

unique key:---- 唯一个 ，不能重复，可以为空

default '地址不详' ---- 默认值如果不写，默认为地址不详

MYSQL的6种常见的约束方式：

1 主键约束，用于唯一标识表中的每一行，主键列的值必须是唯一而且不能为空，一个表只能有一个

2 外键约束，用于表与表之间的关系，一般是和另一张表的主键关联，保证数据引用的完整性，一个表可以有多个外键

3 非空约束， Not NULL 必须要有有一个值.

4 唯一约束性，unique ，确保列中的所有值都是唯一的类似于主键，可以为空，而且一个表可以有多个唯一约束

5 默认约束 default 在插入表数据时，如果没有定义值，会提供一个默认值

6 自增约束 每行自动生成一个标识，通常和主键一起使用

MySQL主键和外键在关系数据库中起着非常重要的作用。主键是一种用于唯一标识表中每一行数据的列，它可以确保表中的每一行数据都有一个唯一的标识符。而外键则是一种用于建立表与表之间关联的列，它可以确保数据的一致性和完整性。

在MySQL中，我们可以通过使用CREATE TABLE语句来创建表，并在其中定义主键和外键。例如，我们可以使用以下语句来创建一个包含主键和外键的表：

CREATE TABLE orders (

order_id INT PRIMARY KEY,

customer_id INT,

order_date DATE,

 FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

在上面的例子中，我们创建了一个名为orders的表，其中包含一个名为order_id的主键列和一个名为customer_id的外键列。外键列引用了另一个名为customers的表中的customer_id列，以建立orders表和customers表之间的关联。

总之，MySQL主键和外键是关系数据库中非常重要的概念，它们可以帮助我们建立表与表之间的关联，并确保数据的一致性和完整性。12

##也可以通过修改的方式添加外键

主表和从表

插入数据： 先插入主表，再插入从表

删除数据： 先删除主表数据，在删除从表

#先的desc 表的结构

##先删除表class的外键

alter table class drop FORIGN key class_ibfk_1;

##再删除索引

alter table class drop index card_id

##先更改类型，再删除主键

alter table class modify stud_id int(12);

alter table class drop primary key;

表一：

create table school (

de_id int(4) zerofill primary key auto_increment,

name VARCHAR(15) not null,

email varchar(45) not null UNIQUE,

money int(12) not null UNIQUE

);

表二：

create table cloud_ky32 (

id int(5) not null primary key auto_increment,

class_name varchar(15) not null,

de_id int(4) zerofill,

address varchar(50) default '地址不详',

phone int(11) not null unique,

foreign key(de_id) references school(de_id)

);

加密创建用户

select password(密码);

复制运行的结果

#内容输入到下方密码区域

create user ‘ky2’@'20.0.0.30' identified by '*6691484EA6B50DDDE1926A220DA01FA9E575C18A';

create user 'ky2'@'localhost' identified by '*6691484EA6B50DDDE1926A220DA01FA9E575C18A';

create user----创建用户

ky2-------------表示用户

localhost------表示新建的用户可以在哪些主机上登录网段，IP，主机名等

'ky2’@'%'------表示任意端

grant 赋权

grant all privileges on *.* to 'Ky2'@'localhost' identified by '123456';

grant ----赋权

all pricvileges ----- 所有权限

on kgc.*-----对指定的库进行赋权

to 'ky2'@'localhost'----赋权给某个用户

identified by '123456' 使用这个密码登录，创建用户时不写，密码为空

##使用 mysql -h 20.0.0.10 -u test -p 命令登录

#f赋予查的权限

grant select on kgc.* to 'test'@'20.0.0.10' IDENTIFIED by '123456';

#如何给一个用户赋权 多个权限

grant select,drop,insert on kgc.* to 'test'@‘20.0.0.10’ IDENTIFIED by '123456';

revoke权限

#删除用户的权限

revoke all privileges on kgc.* from 'test1'@'20.0.0.10';

#查询权限

show grants for 'test'@'20.0.0.10';

#刷新权限

flush privileges;

##如何移除权限

revoke drop on kgc.* from 'test1'@'20.0.0.10' ;

##如何删除所有权限

revoke all on kgc.* from ‘test1’@‘20.0.0.10’

其他用户设置：

#用户重命名

rename user 'test1'@'20.0.0.10' to 'guoqi'@'20.0.0.10';

#删除用户

drop user 'ky33'@'locahost'; 

#修改当前登录用户的密码

set password = password('abc123');

#修改其他用户的密码

set password for 'han'@'20.0.0.10' = password('abc123');

#如何找回密码

修改mysql 的配置文件

设置免密登录

重启

不用输密码 直接登录

查询用户信息

密码在这里面

#设置密码修改

#在去掉免密登录，后可以密码登录

索引的创建

索引类型：

MySQL orcal 默认是都是Btree结构的索引

创建  表的时候需要考虑的因素：

1关联程度，3张表，选好关联字段

2 每个字段的长度 也要考虑

3设计合理的索引列

4表的数据要控制在合理的范围内5秒内可接受，10秒查询不出可能是缓存失效，缓存击穿

创建表和普通索引

Create table member (

Id int(4) patmary key

Name varchar(10),

Card_id int(18),

Phone int(11),

Index name_index (name)

);

创建索引（name）

Show index from member;（查）索引

#表外为card_id 添加普通索引

Alter table member add index card_id_index (card_id);  索引 为card_id

唯一索引： 

unique 和普通索引类似，唯一索引的每个值都是唯一的，唯一索引允许空值，添加唯一键才会创建唯一键，最好不要为空 NotNULL 不然会没有意义

Create UNIQUE index phone_index on member (phone);  创建唯一索引

show index from member; 查索引

#添加列address

#为address列添加唯一索引

Alter table member add unique address_index (address);

创建唯一索引

能在创建表时创建好的条件，尽量在创建条件约束好时创建唯一索引

Create table test1 (

Id int(4) parmary key,

Name varchar(10),

Card_id int(18) not null,

Phone int(11) not nul,l

Unique cardid_index (card_id),

Unique phone_index (phone)

);

#查索引

Show index from test1;

#表外创建唯一索引

create unique index vipnb_index on vip_member (vip_nb);

表示为0   索引不为空

主键索引：

创建表的的指定的主键就是索引，添加主键自动就是主键索引

主键： 一个表只能有一个主键，不能为空，不允许空值

创建主键，自动创建主键索引

##primary key 创建主键索引

create table vip_member (

vip_bh int(5) ZEROFILL PRIMARY key,

vip_name varchar(10) not null,

vip_id int(18) unique,

vip_nb int(11) unique,

vip_dz varchar(50) default 'text',

index vipname_index (vip_name),

unique vipid_index (vip_id)

);

#创建表格同时创建普通索引 主键索引 唯一索引

create table vip_member (

vip_bh int(5) ZEROFILL PRIMARY key,

vip_name varchar(10) not null,

vip_id int(18) unique,

vip_nb int(11) unique,

vip_dz varchar(50) default 'text',

index vipname_index (vip_name),

unique vipid_index (vip_id)

);

全文索引：

适合在模糊查询时使用可以在一篇文章中，检索文本信息。

Create table test12(

Id int(4) parmary key,

Name varchar(10),

Card_id int(18) not null,

Phone int(11) not nul,l

Notes test，

Unique cardid_index (card_id),

Unique phone_index (phone)

);

#查索引

Show index from test1;

##创建全文索引

Create fulltext index 列名_index on 表名(列名);

使用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

Create table test3(

Id int(4) parmary key,

Name varchar(10),

Card_id int(18) not null,

Phone int(11) not nul,l

Notes test，

Unique cardid_index (card_id),

Unique phone_index (phone)，

Fulltext notes_index (notes)

);

Show index from test1;

#去除全文索引

Drop index notes_index on test3;

#添加全文索引

Alter table test3 add fulltext notes_index (notes);

联合索引

一个索引名 对应多个列名

联合索引。查询时按照创建时的顺序来

Explain 检测索引是否生效

#创建联合索引

Create index index_union on test (列名,列名)

创建联合索引

联合索引，从左到右开始，不能跳过索引，否则索引会失效

范围查询：有可能右侧的表会失效

如果索引是字符串，但是不加引号，索引也会失效

#explain 加在查询语句前面可以查看索引使用情况

使用or 语句 索引会失效，

有时候索引失效，多半是空值，索引失效

Select * from test5 where age > 12 and address = ‘’

Explain 加在查询语句前面可以查看索引的使用情况

事务

Mysql 的事务 一个操作序列，包含了一条件撤销的操作，组数据库的操作命令，所有的命令都是一个整体，向系统提交或者要么都执行，么不执行

不可分割的单位

事物的特点acid :

A 原子性 最小单位，事务里的所有条件都是一个整体，不可分割 要么成功，要么失败

C 一致性 事务开始前和借宿之后事务的完整性约束没有破坏，事务完成时数据必须属于一致状态，事务开始前，数据库的存储数据处于一致状态，在事务最终完成时，必须再次回到一致的状态

I 隔离性 在并发环境中，当不同事物同时操作相同的的数据时，每个事务都有各自的完整的数据控制

对数据进行修改的所有的并发事务是彼此隔离的表明事务是独立的

修改数据的事务可以时另一个使用数据的事务开始之前访问这些数据，或者在另一个使用相同的数据之后访问

MySQL的支持的四种隔离级别：

1未提交读，允许脏读，允许一个事物可以看到其他事物未提交的数据

2提交读 只能查看提交修改，为提交的修改私不可见的，防止脏读 （orcale使用的是提交读）

3 可重复读，也是mysql 的默认隔离级别，确保如果在一个事务中执行两次相同的select语时都能得到相同的结果，

不管其他事物是否提交修改，可以防止脏读以及不可重复读

4 串行读，锁表，完全串行化，每一个事务都隔离，读写性能阻塞

脏 读：另外一个事务能够看大另外一个事务未提交的修改结果

#查询全局事务的隔离级别:

Show global variables like ‘%isolation’;

Select @@global.tx isolation;

查#会话事务的隔离级别：

Show session variables like ‘%isolation’;

Selec @@session.tx_isolation;

全局设置隔离级别:

Set global transaction isolation level read UNCOMMITTED；

Set @@global.tx_isolation=’read uncommitted’;(临时修改，重启即使)

Begin

开始一个事务

开终端可以select到数据的更新

1不可重复度：一个事务内，多次读同一数据

读取的数据不一致

#全局 会话 改为 可提交读

软件端也执行

#comit提交之后查询数据变更

幻读：一个事务对一个表中的数据进行了修改，可能会涉及到表中的全部数据

另一个事物也修改了表中的数据，插入一行新的数据，前一个事务发现表中的还有数据没有修改，类似域幻觉

丢失更新：

两个事务同时修改一条记录，a先记录，b 也会修改记录，b一旦提交，会覆盖a 的修改

以上情况如何避免：

1权限控制，

2根据情况使用隔离级别，

生产环境中，最好是提交读

测试环境 无所谓

4 生产环境只能允许一个人对一个事务进行修改，其他人不允许操作。

持久性：数据提交，事务的效果将会被永久的保留在数据库中，而且不会被回滚

总结; 在事务管理中。原子性是基础。隔离性是手段，一致性是目的。持久性是最终的结果

BEGIN :开始

START transaction：开始

显示的开启事务

Commit

Commit work t

都是提交事务

Rollback

Rollback work

都是回滚 回滚撤销正在进行的所有为提交的修改

Savepoint s1 ：创建回滚点，一个事务可以有多个回滚点

Rollback to s1 回到s1回滚点

#一旦提交事务 回滚点失效