MySql-增删查改及其他语法

谁借莪１个温暖的怀抱￠ 2022-04-23 12:52 232阅读 0赞

**增删改查：**

**插入一条数据：**

insert into msg

(id,title,name,conten)

values

(2,'初来乍到','张三','那就这样吧');

**一次性插入多条数据**

insert into msg

(id,title,name,conten)

values

(3,'第三次','王五','爱你一万年'),

(4,'第四次','赵六','越过山丘'),

(5,'第五次','哈哈','漂洋过海来看你');

**更新一条数据：**

update msg

set

conten ='我爱你'

where

name = '张三';

update msg

set

conten ='我爱你'

where

name = '张三';

**删除：**

delete from msg;  删除整张表

delete from msg where  id =2;

**查(可以查询部分列部分行，部分列是通过from前面的字段控制的，部分行是通过where后面的条件控制的)：**

select \* from msg;

select id,title from msg;

select \* from msg where id>2;

所谓建表就是声明列的过程，

数据是以文件的形式存在硬盘（也有存放在内存中的）

列：不同的数据类型占的空间不一样

选列的原则：够用但不浪费

详解列类型：

整型：

Tintint(1)/smallint(2)/mediumint(3)/int(4)/bigint(8)

**Tintint(1)**

一个字符有八个字节，Tintint一个字符范围(0---255(2^8-1))，

Tintint默认是有符号的，存储-128（-2^7）-----127(2^7-1)之间

unsigned (无符号类型，非负0---255):

alter table class add age2 tinyint unsigned;

M：代表宽度（在zerofill时才有意义）

zerofill：零填充（如果某列zerofill，那么默认就是unsigned的）

M必须要配合zerofill才有意义，如果没有zerofill那么M也是没有意义的

列可以声明默认值，而且推荐声明默认值

Not null Default 0;

alter table class add age5 tinyint not null default 0;

**浮点 float(M,D) ：**

**M:精度（总位数，不包含小数点）**

**D：标度（小数位）**

**定点型 Decimal(M,D)：**

**M:精度（总位数，不包含小数点）**

**D：标度（小数位）**

**Decimal相对更精确**

**栗如：**

**float(4,2) -->-99.99到+99.99**

**float(6,2) -->-9999.99到+9999.99**

**float(4,2)  unsigned --->0.00****到+99.99**

create table goods(

name varchar (10) not null default '',

price float (6,2) not null default 0.00

)charset utf8;

insert into goods

(name,price)

values

('跑步机',688.896);

**字符型：**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70][]**

char:定长字符串  char(M)  M:代表宽度，可容纳的**字符数**

varchar：变长  varchar(M)  M:代表宽度，可容纳的**字符数**

区别：

char定长：M个字符，如果存储的小于M个字符，实占M个字符，利用率可以小于或者等于100%。

varchar变长：M个字符，如果存储的小于M个字符，设为N个字符（N<M）实占N个字符，还需要一到两个字节还记录这个变长的字符真实所占用的字符数，

所以实际应该占用N+(1或者2)，利用率永远是小于100%的不可能等于100%

char如果实际存储的的内容不足M个，则后面加空格补齐，

取出来的时候，在把后面的空格去掉，（所以如果内容最后有空格，将会被补齐）

M的范围的不一样，利用率不一样，对结尾处的空格处理不一样

text：不用加默认值，加了也没用

create table stu(

name varchar (10) not null default '',

waihao varchar(10) not null default  ''

)charset utf8;

**日期时间类型： **

Year :年类型

1个字节表示 1901年-2155年，【0000年：表示错误时的年】

如果输入两位，00-69之间，表示2000到2069年

如果输入两位，70-99之间，表示1970到1999年

Date ：日期类型

存储范围：1000-1-1--->9999-12-31

典型类型：1992-08-12

Time ：时间类型

存储范围：-838:59:59--->838:59:59

典型格式：hh:mm:ss

DateTime：日期时间类型

就是日期跟时间类型的和，1989-05-06 14:32:08

存储范围：1000-01-01 00:00:00--->9999-12-31    23:59:59

**注意**：在开发的过程中很少用日期时间类型来表示精确到秒的事情，因为当碰到一些边界性的问题时容易出现问题，一般都用时间戳来表示

时间戳是1970-01-01 00:00:00 到当前的秒数，

时间戳用int来存储这样方便计算，对于显示也方便格式化

**表中插入一列：**

**alter table goods**

**add**

**bigpeices float(9.2) not null default 0.0;**

show full columns from msg;

**增：**

往那张表添，往那几列添加，分别的值是什么就ok了

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 1][]

**改：**

![20190225152241948.png][]

使用update时后面一定要加where这个习惯，不加有时候会出大问题

**删：**

**![20190225152303356.png][]**

只能删除行

**查：**

wherre

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 2][]

**in：在值1、值2...值N，等于值1--N任意一个都行**

![20190225152508751.png][]

**between：值1 and 值2.表示在值1和值2之间**

**![20190225152535421.png][]**

**or**

**![2019022515261583.png][]**

**not**

![20190225152635264.png][]

**模糊查询：**

like：像

%：通配任意字符

取goods\_name包含诺基亚的数据

![20190225152723465.png][]

\_ : 通配单个字符

取goods\_name中诺基亚N\*\*的数据：

**![20190225152737501.png][]**

**group：一般使用在统计场合，与统计函数一起使用**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 3][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 4][]

使用统计函数查询：

![20190225152919911.png][]

通过cat\_id 来分组查询：

![20190225152945287.png][]

**having：****having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。**

**having对查出的结果进行筛选    **

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 5][]

having跟where合用

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 6][]

having跟group by合用

![20190225153247810.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 7][]

![2019022515332316.png][]

使用子查询做：

![20190225153330770.png][]

**order by:**

Order by语句是用来排序的，经常我们会使用到Order by来进行排序，默认是升序排列的asc

可用字段名"**desc**" 来声明按降序排列

![20190225153415295.png][]

order by可以按照多字段进行排序

![20190225153424672.png][]

**limit：**在语句的最后，起到限制条目的作用（经常跟order by配合使用）

limit\[offset\]N

offset:偏移量

N:取出条目

offset如果不写，则相当于limit 0,3 (limit 3) 从第一条开始取

![20190225153511149.png][]

**查询的结果可以在内存中当一个表使：**

![20190225153532434.png][]

![20190225153539889.png][]

**子查询：**

**where 子查询**

把内层查询的结果，当做外层查询的比较条件

典型题：查询最大商品，最贵商品

![20190225153605662.png][]

![20190225153612209.png][]

**from子查询**

把内层的查询结果当做临时表，供外层继续查询，加"as" 临时表名

典型题：查询每个栏目下最新/最贵的商品

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 8][]

**exisits子查询**

把外层的查询结果拿到内层，看内层的查询是否成立

典型题：查询有商品的栏目

![2019022515370130.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 9][]

**union：联合**

![20190225153731401.png][]

作用：把2次或多次查询结果合并起来

用原来的知识：

![20190225153815419.png][]

用union来做：

![20190225153823382.png][]

**要求：两次查询的列数一致**

推荐：查询的每一列，相对应的列类型也一样

可以来自于多张表

**多次sql语句的取出的列名可以不一致，此时以第一个sql的列名为准**

**如果不同的语句中取出的行有完全相同的（每个列的值都相同），那么相同的行将会合并（去重复）**

**如果不去重复可以加all来指定（union all）**

**如果子句中有order by，limit这些语句需加（），推荐放到所有子句之后，即对最终排序后的结果来排序（如果只有order by来进行排序，必须要放到语句后面要不然合并后会错乱，但是如果order by跟limit合用后，合并后才不会错乱）**

**在子句中配合limit使用才有意义，如果order by不配合limit 使用，会被语法分析器优化分析时去除，所以对性能影响不大**

![20190225153917530.png][]

合并两张表，将id一样的值求和

![20190225153927471.png][]

**多表连接查询概念与左连接语法：**

集合：无序性，唯一性

数据库里面，一张表就是一个集合，一行数据就是集合的一个元素

理论上讲不可能存在完全相同的两个行（从集合的角度出发看），但是表中可以存在完全相同的两行，因为表内部有一个rowid

集合相乘就是笛卡尔积，其实就是两个集合的完全组合

两张表相乘(用逗号相隔)：

![20190225154045427.png][]

左连接，右连接，内连接

![20190225154055412.png][]

**左连接：**

select 列1,列2,列N from

**tableA left join tableB**

**on**

**tableA某一列 =tableB某一列   （此处表连接成一张大表，完全当成普通表看）**

既然是形成了一张大表，那么where，group，having，odery by，limit照常写

栗子：

![2019022515412734.png][]

**右连接：**

select 列1,列2,列N from

**tableA right join tableB**

**on**

**tableA某一列 =tableB某一列   （此处表连接成一张大表，完全当成普通表看）**

既然是形成了一张大表，那么where，group，having，odery by，limit照常写

内连接：inner

select 列1,列2,列N from

**tableA innner join tableB**

**on**

**tableA某一列 =tableB某一列   （此处表连接成一张大表，完全当成普通表看）**

既然是形成了一张大表，那么where，group，having，odery by，limit照常写

**左连接，右连接：**

**左连接：**即以左表为基准，到右表找匹配的数据，找不到匹配的用NULL补齐。

![20190225154226897.png][]

**如何记忆：（左右连接）**

1.左右连接是可以相互转化的

2.可以把右连接转换为左连接来使用(并推荐左连接来代替右连接，兼容性会好一些)

A 站在 B的左边   ---》 B 站在 A的右边

A left join B --->  B right join A 是同样的。

**内连接：**

查询左右表都有的数据，不要左/右中NULL的那一部分

内连接是左右连接的交集。

能否查出左右连接的并集呢？

目前的mysql是不能的，它不支持外连接，outer join,可以用union来达到目的。

![20190225154242474.png][]

思考：能否查出左右连接的并集？

答：至少目前不能，目前的mysql不支持外链接（outer join），可以用union来达到目的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 10][]

第一题：

![20190225154330166.png][]

第二题：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 11][]

**表管理之列的增删改：**

![20190225154436764.png][]

**创建一张表的语法**

create table tableName(

**列名 列类型 \[列属性\] \[默认值\], -->把这整行看成是 列声明的一条语句**

......

);

1.增加列

语法：alter tableName **add 列声明**

新增加的列默认放到表中的最后一列，如果要让新增的列放到具体位置

可以这样：alter tableName add **列声明 after 已有列名**

如果要让新增的列排到最前面，可以这样：

alter tableName add **列声明 first**

2.修改列

语法：alter talbeName **change** 被改变的列 **列声明**

![20190225154612389.png][]

3.删除列

语法：alter tableName **drop** 列名

**1.where 后面的条件，可以理解为把这个条件带入到每一行是看，哪一行满足就执行哪一行**

**如果  where 1;  因为1代表真，所以每一行都会被执行到**

**2.要把列名当成变量名来看，可以进行加减乘除运算**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 12][]

**3.可以给列或者运算结果取别名**

![20190225154721956.png][]

**小结：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 13][]

## **创建后表的修改** ##

alter table **语句用于创建后对表的修改, 基础用法如下:**

**添加列**

基本形式: alter table 表名 add 列名 列数据类型 \[after 插入位置\];

**示例:**

在表的最后追加列 address: alter table students add address char(60);

在名为 age 的列后插入列 birthday: alter table students add birthday date after age;

**修改列**

基本形式: alter table 表名 change 列名称 列新名称 新数据类型;

**示例:**

将表 tel 列改名为 telphone: alter table students change tel telphone char(13) default "-";

将 name 列的数据类型改为 char(16): alter table students change name name char(16) not null;

**删除列**

基本形式: alter table 表名 drop 列名称;

**示例:**

删除 birthday 列: alter table students drop birthday;

**重命名表**

基本形式: alter table 表名 rename 新表名;

**示例:**

重命名 students 表为 workmates: alter table students rename workmates;

**删除整张表**

基本形式: drop table 表名;

**示例:** 删除 workmates 表: drop table workmates;

**删除整个数据库**

基本形式: drop database 数据库名;

**示例:** 删除 samp\_db 数据库: drop database samp\_db;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70]: /images/20220317/2901c9e61a3640358ce7ea0a6cb4139b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 1]: /images/20220317/f24bd001258048fe91354512b905b010.png
[20190225152241948.png]: /images/20220317/ff24442d50084044a2aaf64833901210.png
[20190225152303356.png]: /images/20220317/787bc12e323748d0ba3eeea350eb419b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 2]: /images/20220317/0c4f5b69132645d0a59d735699956a57.png
[20190225152508751.png]: /images/20220317/831e60d93aba4816bec52d0894a2cbdf.png
[20190225152535421.png]: /images/20220317/9edacf6286164a2d92bd08d66d19d9a6.png
[2019022515261583.png]: /images/20220317/a6d8421fbd2843ad8500876f92a26528.png
[20190225152635264.png]: /images/20220317/c5363d1a20174eee99abe4ffc3f49ca7.png
[20190225152723465.png]: /images/20220317/09b69f47ff274586b1444dba5cc86094.png
[20190225152737501.png]: /images/20220317/b7a5944c5cd44b3dacd8298a904cc2be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190225152859927.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 4]: /images/20220317/2c80078b371f46bb8a32c8038fbd5ee4.png
[20190225152919911.png]: /images/20220317/251e8815d14b484ba3825ea1dc3314be.png
[20190225152945287.png]: /images/20220317/bb1ae4ca897a4088abc3c46ca7435c5b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 5]: /images/20220317/49cb03ae14e94f128c7eb7288904eb1f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 6]: /images/20220317/d731cdb1353d47eb93f73b9ec8d921c3.png
[20190225153247810.png]: /images/20220317/ccbbf972899f4e8880829da52d8b1175.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 7]: /images/20220317/b59ea3548ce94f1da778ab4df2ffa460.png
[2019022515332316.png]: /images/20220317/1ac17fd652944c15b92b954c78de73d2.png
[20190225153330770.png]: /images/20220317/c9cf5485b2e04ca2b0f9851911f5e218.png
[20190225153415295.png]: /images/20220317/0938a2e6fb2c4b3bb7029a4857f580cd.png
[20190225153424672.png]: /images/20220317/4eb65cf4193b49cebfdbbdf8fda8b089.png
[20190225153511149.png]: /images/20220317/d99cbe7c4a8f46fa9d96a4f875daef9a.png
[20190225153532434.png]: /images/20220317/8f57b8e0b7434e7a893552fcda6e6705.png
[20190225153539889.png]: /images/20220317/c2014ee064dd4121b4fe608a76bdb1a1.png
[20190225153605662.png]: /images/20220317/197c8cea68d145a899304b5a09425b23.png
[20190225153612209.png]: /images/20220317/bc1a75a2f79142089c1336a8641f6b1c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 8]: /images/20220317/9d5ccb2170644635ae7c7b8e8f5c224c.png
[2019022515370130.png]: /images/20220317/4bf8f3b92e21495c8277e8195c11a154.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 9]: /images/20220317/e69559e9550543d49d4c095534047581.png
[20190225153731401.png]: /images/20220317/56744194a9f14940933bf6427c891355.png
[20190225153815419.png]: /images/20220317/7ea92adade1846bd8946eca0517c1b82.png
[20190225153823382.png]: /images/20220317/6d424bb296f54a348dc794e3be25d8e2.png
[20190225153917530.png]: /images/20220317/80483efbb2e347489f5841feeea68eb5.png
[20190225153927471.png]: /images/20220317/21f4e9cbec194c509d15367e78e12cdd.png
[20190225154045427.png]: /images/20220317/153c300773b34a13b88c15f4284db256.png
[20190225154055412.png]: /images/20220317/12a90dfe42d548539e0e60f304751cba.png
[2019022515412734.png]: /images/20220317/370334afc3fe4883819a62bf251ea864.png
[20190225154226897.png]: /images/20220317/00fadda2cf5746c68667339fc3bc6d86.png
[20190225154242474.png]: /images/20220317/0209742f467e4061b2ffa5753eb12217.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 10]: /images/20220317/a88cb76808764a6dbdfbef19a0fc6aa4.png
[20190225154330166.png]: /images/20220317/3e512210c7ab4806939c50a44298887e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 11]: /images/20220317/bce0c5cc92194deda81be0bdab8d173b.png
[20190225154436764.png]: /images/20220317/8d7e4398f7b342028f06e1ae4051bc7b.png
[20190225154612389.png]: /images/20220317/b95554435589404caec40c77cb3c92e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 12]: /images/20220317/355cb0420b054dd4b377e82bb3ee9892.png
[20190225154721956.png]: /images/20220317/bc47637b538c41f285e89741e0eae863.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x3emhhbmcxMTAx_size_16_color_FFFFFF_t_70 13]: /images/20220317/2501ed10c4b3472b8b8ea23a09be1fb6.png