[数据库萌新入门] 第一章 数据库基础入门（第3节 DML）

数据的DML操作： 添加数据，修改数据，删除数据

添加数据：

# 格式： insert into 表名[(字段列表)] values(值列表);

-- 批量添加值

insert into stu values

    (null,'zhaoliu1',25,'w','lamp94'),

    (null,'zhaoliu2',26,'w','lamp94'),

    (null,'zhaoliu3',27,'w','lamp94');

修改数据：

# 格式： update 表名 set 字段1=值1,字段2=值2,..., where 条件

-- 将id值为12和14的数据值 sex 改为 m， classid 改为 lamp92

update stu set sex='m',classid='lamp92' where id=12 or id=14   --等价于下面

update stu set sex='m',classid='lamp92' where id in(12,14);

删除数据：

# 格式： delete from 表名 [where 条件]

-- 删除stu表中id值为100的数据

delete from stu where id=100;

-- 删除stu表中id值为20到30的数据

delete from stu where id>=20 and id<=30

-- 删除stu表中id值为20到30的数据（等价于上面的写法）
delete from stu where id between 20 and 30;

-- 删除stu表中id值大于200的数据

delete from stu where id>200;

 

检索数据select

检索单个列

# select 列名 from 表名;

    如果没有明确排序查询结果，则返回的数据的顺序没有特殊意义。

    返回数据的顺序可能是数据被添加到表中的顺序，也可能不是。只要返回相同数目的行，就是正常的。

检索多个列

# select 列1,列2,列3,列4 from 表名

     在选择多个列时，一定要在列名之间加上逗号，但最后一个列名后不加，否则将出错。

检索所有列

# select * from 表名;

    除非确实需要每个列，否则最好别用*通配符。

    使用通配符有一个大优点：可以检索出名字未知的列

检索不同的行DISTINCT

# select distinct 列名 from 表名;

如果给出 SELECT DISTINCT 列1,列2 from 表名;   # 除非指定的两个列都不同，不然所有行都会被检索出来。

限制结果

# select * from 表名 limit 3,4

    LIMIT 3,4 的含义是，从行3开始的4行。（跳过前3行，取4行）

    替代语法 LIMIT 4 OFFSET 3 含义同上 从行3开始取4行。

使用完全限定的表名

# select 列名 from 表名;

# select 表名.列名 from 表名;

# select 表名.列名 from 库名.表名;

对检索数据进行排序ORDER BY

排序数据 ORDER BY

# select * from 表名 order by 列名  -- 默认升序

# select * from 表名 order by 列名 DESC  -- 降序排序

按多个列排序

# select * from 表名 order by 列名1,列名2;  -- 会按照前后顺序依次对比排序

# select * from 表名 order by 列名1,列名2 DESC; -- 先按列1升序，再按列2降序

# select * from 表名 order by 列名1 DESC,列名2,列名3 DESC;  -- 先按列1降序，再按列2升序，再按列3降序

     注意：

        ORDER BY 子句的位置，应保证位于 FROM 子句之后。

        如果使用 LIMIT ，他必须位于 ORDER BY 之后。 

数据检索条件过滤WHERE

    注意：

        ORDER BY 语句 应在 WHERE 语句之后

过滤数据WHERE

# select 列名 from 表名 where 条件

select name from stu where age = 22;

组合WHERE字句

AND和OR

组合AND和OR有一个有趣的问题：

eg：查询1期或2期的学员，并且要求是女生

select * from stu where  class_id=1 or class_id=2 and sex='女';

以上语句不会按照预期检索出正确的数据，问题在哪？

原因在于计算的次序。SQL在处理OR操作符前，优先处理AND操作符。

因此 上述 语句的 真实意思是 2期的所有女生，或 1期的所有学员。

想要解决则需要提升优先级,用圆括号明确地分组相应操作符。

select * from stu where  (class_id=1 or class_id=2) and sex='女';

IN与NOT

select * from 表名 where 列名 in (1,2,3);

为什么要使用IN操作符?其优点具体如下。

 

1. 在使用长的合法选项清单时, IN操作符的语法更清楚且更直观。

2. 在使用IN时,计算的次序更容易管理(因为使用的操作符更少)。

3. IN操作符一般比OR操作符清单执行更快。

4. IN的最大优点是可以包含其他SELECT语句,使得能够更动态地建立WHERE子句。

select name from stu where class_id not in (1,2); # 找出class_id不等于1且不等于2的人的名字

通配符的使用

LIKE与通配符

    LIKE指示MySQL后面跟的搜索模式利用通配符进行匹配。

# select name from stu where name like '%a%'; -- 这个是表示，查询 name 中含有 字母 a 的

# select name from stu where name like '%a';    --  表示 查询 name 以 字母 a 结尾 的人

# select name from stu where name like '_a';    -- 表示查询 name 第二个 字符 是 字母 a， 且名字长度为2 的人

# select name from stu where name like '__a%';  -- 表示查询 name 第三个字符是字母 a ， 后续随便的人

    注意：通配符性能低，所以少用通配符，若要用有如下技巧：

         1、在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。
         2、把通配符置于搜索模式的开始处,搜索起来是最慢的。

了解下MySQL的正则 REGEXP

select name from stu where name regexp '[0-5]abc';

了解即可。

字段的计算

        计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的

拼接Concat

select concat(name,'(',phone,')') from stu;

下面是部分查询结果，用于给读者展示一下格式：

张三(13701104321) 
李四(13701104322)

使用别名AS

select concat(name,'(',phone,')') as phone_name from stu;

函数的使用：

文本处理函数：

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html

可能常用的函数：Substring()，Concat()

日期和时间处理函数：

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

可能常用的函数：CurDate(), CurTime(),DateDiff(),Now()

数值处理函数：

https://dev.mysql.com/doc/refman/5.7/en/numeric-functions.html

可能常用的函数： Rand()

聚集函数与分组 Group By

• 我们经常需要汇总数据而不用把它们实际检索出来,为此MysQL提供了专门的函数。
• 使用这些函数, MysQL查询可用于检索数据,以便分析和报表生成。

1. 确定表中行数(或者满足某个条件或包含某个特定值的行数) 。
2. 获得表中行组的和。
3. 找出表列(或所有行或某些特定的行)的最大值、最小值和平均值。

上述例子都需要对表中数据(而不是实际数据本身)汇总。因此,返回实际表数据是对时间和处理资源的一种浪费

在使用count时,如果指定列名，则指定列的值为空的行被忽略，但如果COUNT()函数中用的是星号（*），则不忽略.

数据分组Group By 与 Having

GROUP BY

例如 我们需要获取某个班级的学员人数：

select count(*) as nums from stu where class_id = 2;

但如果要返回每个班级的人数怎么办？

select class_id,count(*) as nums from stu group by class_id;

可以如下同时获取平均值，即聚合函数可以并列。

select class_id,count(*) as nums,avg(age) as avg_age from stu group by class_id;

HAVING

那么，如果需要返回人数小于5人的班级怎么办？或返回班级大于5人的班级怎么办？

WHERE过滤行，HAVING过滤分组。

select class_id,count(*) as nums from stu group by class_id;

用以下带having子句的语句可以完成任务。

select class_id,count(*) as nums from stu group by class_id having nums > 5;

注意：

      在使用group by进行分组时,按照标准的SQL模式,需要把select查询中的所有列(除了聚集函数外)全部都列在group by后面。

上面这个错误，在我自己的mysql中并没有复现，相反，很成功地运行了。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

本文地址：https://blog.csdn.net/predawnlove/article/details/107664456