MySQL基础(二)-数据的基本操作【增删改查】

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.14 sec)

我们先在数据库test01中创建两个表，分别为 MyISAM存储引擎的myisam 和 InnoDB存储引擎的innodb。

mysql> create table myisam(`set` int(4))engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `innodb`(`set` int(4))engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| innodb           |
| myisam           |
+------------------+
2 rows in set (0.00 sec)

MySQL文件的默认存储路径为：

Windows系统：C:\ProgramData\MySQL\MySQL Server 5.7\Date

Linux系统：/var/lib/mysql/test01

以Linux系统为例，在该目录下每个数据库文件均存放在以数据库命名的文件夹中，我们可以找到对应的test01数据库文件夹，数据文件目录如图所示。

MyISAM类型的表文件

关于MyISAM相关的数据文件有3个，扩展名分别为.frm、.MYD、.MYI。

.frm文件：格式文件。主要存放表的元数据，包括表结构定义信息等。该文件与存储引擎无关，任何存储类型的表都会有这个文件。
.MYD文件：数据文件。存放表中数据的文件。
.MYI文件：索引文件。主要存放MyISAM类型的表的索引信息，每个MyISAM类型的表都会有一个.MYI文件，存放位置与.frm文件相同。

MyISAM存储引擎的优点：

可被转换为压缩、只读表来节省空间。

InnoDB类型的表文件

MySQL默认的存储引擎，同时也是一个重量级的存储引擎。

InnoDB类型的表innodb有.ifm和.ibd两个相关文件。

.frm文件：表结构定义文件。作用与MyISAM类型的.ifm文件相同。
.idb文件：数据文件。保存所有InnoDB类型的表的数据。

主要有以下特点：

每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表的内容
提供一组用来记录事务性活动的日志文件
用commit(提交)、savepoint和rollbask(回滚)支持事务处理
提供ACID兼容
在MySQL服务器崩溃后提供自动恢复
多版本(MVCC)和行级锁定
支持外键及引用的完整性，包括级联删除和更新

一、插入数据记录 - insert

1、插入单行数据

语法格式如下：

insert into 表名 [(字段名列表)] values (值列表);

需要注意的是：

表的字段是可选的，如果省略，则依次插入所有字段。
多个列表和多个值之间使用逗号分隔。
值列表必须和字段名列表数量相同，且数据类型相符。
如果插入的是表中部分分列的数据，字段名列表必须填写。

另外，插入数据时需要注意这个表是否和其他表存在外键关系。如果该表存在外键，但是相关联的表中数据缺失，那么插入数据就会失败。

因此，外键关联可以确保数据的完整性。

实际使用时代码格式如下：

insert into stu(id,name) value(2,'ls');

2、插入多行数据

语法格式如下：

insert into 表名 [(字段名列表)] values (值列表1),(值列表2),......,(值列表n);

一次性向表格中添加2条数据，代码如下：

insert into stu(id,name) value(3,'ww'),(4,'zl');

注意：

在使用insert插入数据时，如果不包含字段名称，values关键字后面的值列表中各字段的顺序必须和表定义中各字段的顺序保持一致。

如果表结构发生了改变，那么值列表也要相应的变化，否则会报错。但如果指定了插入的字段名，就可以避免这个问题。

结论：在插入数据时最好指定具体的字段名。

3、将查询结果插入到新表中

create table 新表 (select 字段1, 字段2, ......from 原表);

使用时代码段如下：

mysql> create table newstu(select id,name from student);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from newstu;
+------+----------+
| id   | name     |
+------+----------+
| 1001 | zhangsan |
| 1002 | lisi     |
| 1003 | wangwu   |
+------+----------+
3 rows in set (0.00 sec)

除了使用create方法，我们也可以使用insert语句进行数据的插入，用法与create格式类似。

insert into 新表 (select 字段1, 字段2, ......from 原表);

4、从一张表中选取数据插入到另一张表中

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT
...
...
[INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]

实例：

SELECT * 
INTO persons_backup (备份表)
FROM persons (旧表)

SELECT INTO FROM与INSERT INTO SELECT区别：

1.INSERT INTO SELECT语句
语句形式为：

INSERT INTO table2(field1,field2,…) select value1,value2,… from table1

要求目标表table2必须存在，由于目标表table2已经存在，所以我们除了插入源表table1的字段外，还可以插入常量

2.SELECT INTO FROM语句
语句形式为：

SELECT vale1, value2 INTO table2 FROM table1

要求目标表table2不存在，因为在插入时会自动创建表table2，并将table1中指定字段数据复制到table2中。

比较两种语句的差别，选择适合自己情况的语句。
此外，ispass是个经过处理的字段，其值类型可能无法自动创建，所以，建议使用第一种形式的语句，建立一个表，然后再插入查询。

但是SELECT ... INTO.. 用法不适用于MySQL直接备份表结构和数据,一些种方法可以代替,如下:

#MYSQL不支持: 
SELECT * INTO new_table_name FROM old_table_name; 这是sql server中的用法

#替代方法: 
CREATE table new_table_name (SELECT * FROM old_table_name);
或
INSERT INTO new_table_name(field1,field2,…) select value1,value2,… from old_table_name

二、更新数据记录 - update

数据更新是经常发生的事情，当我们需要对表中的数据进行更新的时候，会用到下面的SQL命令：

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

其中：

1、set关键字后面可跟多个“列名=更新值”，以修改多个数据列的值。不同列之间使用逗号进行分隔。

2、where子句是可选的，用来限制更新数据的条件。如不限制，则整个表的所有数据行都将被更新。

实际使用时效果如下：

# 原表格
mysql> select * from student;
+------+----------+--------+----------+-------+
| id   | name     | school | address  | score |
+------+----------+--------+----------+-------+
| 1001 | zhangsan | bdqn   | beijing  |    89 |
| 1002 | lisi     | bdqn   | nanjing  |    90 |
| 1003 | wangwu   | bdqn   | dongjing |    91 |
+------+----------+--------+----------+-------+
3 rows in set (0.00 sec)


# 将地址全部改为"nanjing"
mysql> update student set address = 'nanjing';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> select * from student;
+------+----------+--------+---------+-------+
| id   | name     | school | address | score |
+------+----------+--------+---------+-------+
| 1001 | zhangsan | bdqn   | nanjing |    89 |
| 1002 | lisi     | bdqn   | nanjing |    90 |
| 1003 | wangwu   | bdqn   | nanjing |    91 |
+------+----------+--------+---------+-------+
3 rows in set (0.00 sec)


# 将小于等于90分的分数，改为90分
mysql> update student set score=90 where score<=90;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> select * from student;
+------+----------+--------+---------+-------+
| id   | name     | school | address | score |
+------+----------+--------+---------+-------+
| 1001 | zhangsan | bdqn   | nanjing |    90 |
| 1002 | lisi     | bdqn   | nanjing |    90 |
| 1003 | wangwu   | bdqn   | nanjing |    91 |
+------+----------+--------+---------+-------+
3 rows in set (0.00 sec)

在更新数据时，一般都有条件限制，此时需要使用where子句进行限制，否则将更新表中所有行的数据，可能会导致有效数据的丢失。

三、删除数据记录

1、使用delete删除数据

使用delete删除表中的数据，语法格式如下：

delete [from] 表名 [where <删除条件>];

比如删除student表中id为1001的学生信息：

delete from student where id=1001;

delete语句删除的是整条记录，而不是单列。所以delete后面不能出现列名。否则会出现如下错误情况：

delete语句删除数据的原理：

使用delete语句进行数据的删除，会将数据抹掉，但是数据在硬盘上的真实存储空间不会被释放。

delete删除的优点：删除效率比较低。

delete删除的缺点：支持回滚，后悔了可以再恢复数据。

delete属于DML语句。

2、使用truncate table删除数据

truncate table语句可以用于删除表中的所有行，类似于没有where的delete子句。但是truncate table语句比delete语句执行速度快，使用的系统资源和事务日志资源更少，并且删除数据后表的标识列会重新开始编号。

truncate table语句会删除表中所有行，但表的结构、列、约束、索引等都不会改动。同时，truncate table语句也不能用于有外键约束引用的表。

由于使用truncate table语句删除的数据不能恢复，因此不建议使用。

使用delete语句对数据进行删除：

使用truncate table语句对数据进行删除：

使用truncate语句删除效率比较高，表被一次截断，物理删除。

truncate删除的缺点：不支持回滚。

truncate删除的优点：快速。

truncate属于DDL操作。删除表中的数据，表结构还在。

四、查询数据语言DQL

1、使用select语句进行查询

查询语句语法格式如下：

select < 列名 | 表达式 | 函数 | 常量 >
from < 表名 >
[left | out | inner join 表名2]
[where <条件表达式>]
[group by]
[having]
[order by < 排序的列名 >[asc 或 desc]]
[limit [ 位置偏移量 ], 行数]
;

其中：

【where】子句是可选的，用来限制查询数据的条件。若不限制，则查询返回整个表所有行的数据。

【order by】子句用来排序。降序desc，升序asc。

示例：

1、查询所有的行和列

# 实际开发中不建议使用 * 效率较低。
select * from [表名];
select * from stu;

【注】：标准sql语句中要求字符串使用 '单引号' 括起来。虽然MySQL支持双引号，但由于可能出现不兼容的问题，尽量使用但引号。

2、查询部分行和列

查询部分列需要列举不同的列名，而查询部分行需要使用where字句进行条件限制。

执行顺序：先from，然后where，最后select

如，查询student表中address列值为‘nj’的值：

select * from student where address='nj';

3、在查询中使用列的别名

# 将进行运算操作后的字段名重命名
# 这里的as关键里可以省略
select 字段1 [运算操作] [as] 新字段1, ... from 表名;
select id+100 as newID,name from stu;

select id+100 newID,name from stu;

# 修改后有中文，用'单引号'，否则会报错
select id+100 as 'ID号',name from stu;

4、查询空值null

在SQL语句中采用“ is null ” 或者“ is not null ”来判断是否为空。

select 字段名 from 表名 where 字段名 is null;
select * from student where name is null;

5、查询结果的去重 - distinct

distinct关键字用于去除重复记录，且distinct关键字只能出现在所有字段的最前面。

select distinct job from emp;

案例：统计岗位的数量：

select count(distinct job) from emp;

2、常用函数

1、聚合函数/分组函数/多行处理函数

聚合函数用来对已有数据进行汇总，如求和、平均值、最大值、最小值等。

注：

1、所有的分组函数都是对“某一组”数据进行操作。

2、分组函数又称为多行处理函数，其特点是：输入多行，最终输出结果是1行。

3、分组函数自动忽略null。

4、分组函数不可直接使用在where子句当中。

函数名	作用
avg()	返回某字段的平均值
count()	返回某字段的行数
max()	返回某字段的最大值
min()	返回某字段的最小值
sum()	返回某字段的和

count(*) 和 count(具体某个字段) 的区别：

count(*)：不是统计某个字段中数据的个数，而是统计总记录条数（和某个字段无关）

count(字段)：表示统计该字段中不为null的数据总数量。

如：

# 计算学生的总成绩
mysql> select sum(score) from student;
+------------+
| sum(score) |
+------------+
|        717 |
+------------+
1 row in set (0.00 sec)

# 查询最高成绩和最低成绩
mysql> select max(score),min(score) from student;
+------------+------------+
| max(score) | min(score) |
+------------+------------+
|         95 |         80 |
+------------+------------+
1 row in set (0.00 sec)

分组函数也能组合起来使用。

2、单行处理函数

单行处理函数即输入一行，输出一行。我们在数据库中规定，只要有null参与的运算结果一定是null。所以需要对null进行预处理，有如下空处理函数：

ifnull（可能为null的数据，被当作什么处理）
select ename,ifnull(comm,0) as comm from emp;

3、字符串函数

字符串函数集用来对字符串进行各种处理。

函数名	作用
insert(str,pos,len,newstr)	将字符串str从pos开始，长度为len的子串替换为字符串newstr
lower(str)	将字符串str中的所有字符变为小写
upper(str)	将字符串str中的所有字符变为大写
substring(str,num,len)	返回字符串str第num个位置开始长度为len的子字符串
locate(substr,str)	返回字串substr在字符串str中第一次出现的位置

4、时间日期函数

函数名	作用
curdate()	获取当前日期
curtime()	获取当前时间
now()	获取当前日期和时间
week(date)	返回日期date为一年中的第几周
year(date)	返回日期date的年份
hour(date)	返回时间time的小时值
datediff(date1,date2)	返回日期参数date1和date2之间相隔的天数
adddate(date,n)	计算日期参数date加上n天后的日期

5、数学函数

函数名	作用
ceil(x)	返回大于或等于数值x的最小整数
floor(x)	返回小于或等于数值x的最大整数
rand()	返回0~1的随机数

3、排序 - order by子句

order by子句可以将查询结果按照一定顺序进行排序。排序可以是升序（asc）或者降序（desc），如果不指定asc或者desc，则默认按照asc升序排序。

越靠前的字段越能起到主导作用。只有当前面的字符无法完成排序的时候，才会启用后面的字段。

降序>
select 字段名 from 表名 order by 字段名 desc;

升序>
select 字段名 from 表名 order by 字段名 asc;

【select】【from】【where】【order by】中，order by是最后执行的。

当我们需要对多列进行排序的时候，列之间使用逗号分隔，并且可在每列后面设置排序的升降序。如，order by 列1 asc，列2 desc。

4、limit子句

语法格式如下：

select < 列名 | 表达式 | 函数 | 常量 >
from < 表名 >
[where <条件表达式>]
[group by< 分组的字段名 >]
[order by < 排序的列名 >[asc 或 desc]]
[limit [ 位置偏移量 ], 行数]
;

位置偏移量：

位置偏移量是指从结果第几行数据开始显示。（如第一条数据的偏移量是0，第二条数据偏移量是1，......，以此类推）如果省略，则默认从第一条数据开始显示。

行数：

显示记录的条数。从一批结果中规定每页显示多少条数据，也可以查询中间某页的记录。通过这种方式可以实现数据的分页查询。

5、总结：DQL语句的完整写法

执行顺序：

1、from

2、where

3、group by

4、having

5、select

6、order by

五、条件查询

条线查询需要用到where语句，where必须放到from语句表的后面。

支持运算符如：=、<>或！=、<、<=、>、>=、is null、is not null、and、or、in、not、like等。

当运算符优先级不确定的时候，加小括号。

1、通配符

通配符是一类字符，可以代替一个或多个真正的字符，在查找信息是作为替代字符使用。通配符必须与like关键字一起使用。

通配符	解释
_	一个字符
%	任意长度的字符串
[]	括号中指定范围内的一个字符
[^]	不在括号中指定范围内的一个字符

2、模糊查询 - like

select < 列名 | 表达式 | 函数 | 常量 >
from < 表名 >
where <列名> like 条件1 [and [or]] <列名>='值'
;

like子句用于匹配字符串或字符串的一部分，该子句只用于字符串，因此只与字符数据类型（如char、varchar等）联合使用。

如：查询subject表中subjectname含有math的数据

select * from subject where subjectname like 'math%';

由于在SQL语句中"_"下划线表示任意一个字符。当我们需要查找字段中含有"_"该字符时，需要进行转义。语法如下：
select name from user where name like '%\_%';

3、范围查询 - between...and

关键字between可以查询介于两个已知值之间的未知值。要实现这种查询方式，必须知道查询的初值和终值，并且初值和终值之间用and关键字分开。

如：查询学生成绩在80到100之间的分数。这里between...and...是闭区间 [80~100]。

 select * from result where studentresult between 80 and 100;

【注】between...and...除了可以用在数字方面，还可以用在字符串方面。用在字符方面时左闭右开。

4、列举值内查询 - in

如果查询的值是指定的某些值之一，可以使用带列举值的in关键字进行查询，将列举值放在括号中，用逗号分隔。

需要注意的是，in后面的值不是区间，是具体的数值。

 select * from result where studentresult in(100,98);

注意，列举值类型必须与匹配到的列具有相同的数据类型。

六、分组查询 - group by 和 having

group by：按照某个字段或者某些字段进行分组

having：对分组之后的数据进行再次过滤。

【注】分组函数一般都会和group by联合使用，并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才执行。

SQL语句中没有group by，整张表的数据会自成一组。所以where语句后不能用分组函数。

下面通过一个例子来说明，

案例1：找出工资高于平均工资的员工

# 第一步：找出平均工资
select avg(sal) from emp;

# 第二步：找出高于平均工资的员工
select ename,sal from emp where sal>平均工资;

可以合并为以下一段代码：
select ename,sal from emp where sal>(select avg(sal) from emp);

案例2：找出每个工作岗位的最高薪资

select job,max(sal) from emp group by job;

当一条语句中有【group by】的话， select 后面只能跟【参加分组的字段】，和【分组函数】。

多个字段联合分组

案例3：找出不同工作岗位的最高薪资

select depno,job,max(sal) from emp group by depno,job;

案例4：找出每个部门的最高薪资，要求显示薪资大于2500的数据。

第一步：找出每个部门的最高薪资
select max(sal) ,deptno from emp group by deptno;

第二步：找出薪资大于2900
select max(sal) ,deptno from emp group by deptno having max(sal) > 2900;

 但是通过上面这种方式效率低，尽量使用while进行过滤

select max(sal) ,deptno from emp where sal > 2900 group by deptno;

但是where不是万能的，当我们的判断条件涉及到分组的时候，分组函数无法用在where后面。这时候我们需要进行数据的比较，就引入了having。

案例5：找出每个部门的平均薪资，要求显示薪资大于2000的数据

第一步：找出每个部门的平均薪资
select deptno,avg(sal) from emp group by deptno;

第二步：要求显示薪资大于2000的数据
这种情况只能使用having过滤
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

five小点心

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础(二)-数据的基本操作【增删改查】

上文介绍了MySQL存储引擎的设置，那么MySQL的数据文件存放在哪里呢？又是如何存放的呢？不同的存储引擎涉及的数据文件有所不同。我们先在数据库test01中创建两个表，分别为 MyISAM存储引擎的myisam 和 InnoDB存储引擎的innodb。| innodb || myisam |MySQL文件的默认存储路径为：Windows系统：C:\ProgramData\MySQL\MySQL Server 5.7\DateLinux系统：/var/lib/mysql/test01。
复制链接

扫一扫

专栏目录