SQL语言的设计与编写
一、结构化查询语言SQL(structured query language)
SQL语句的主要用途是,构造各种数据库的操作的指令,比如用来创建、修改、删除各种数据库的对象和查询命令
SQL语句分成四种DDL、DML、DQL、DCL,通常把DML和DQL放到一起,作为增删改查来看
数据库定义语言(DDL)Data Definition Language
用于定义和管理数据对象(什么是数据对象?库是对象、表是对象、索引是对象、视图是对象),
比如CREATE创建、DROP删除、ALTER更改,
也就是跟数据库、表、视图这些对象有关的,称为DDL语句就是数据定义语言。
数据操作语言(DML)Data Manipulation Language
数据操作语言就是和表中的数据记录有关的,我们可以插入记录,更新记录,删除记录,以及查询记录
用于操作数据库对象中所包含的数据,数据操纵语言。
例如:INSERT, UPDATE, DELETE语句
数据查询语言(DQL)Dimensional Query Language
用于查询数据库种所包含的数据,能够进行单表查询,链接查询,嵌套查询,以及集合查询,还有各种各样复杂程度不同的数据查询,
例如:SELECT
数据库控制语言(DCL)Data Control Language
是用来管理数据库语言的,包含管理权限及数据更改,比如赋予哪个用户什么权限,比如哪个用户可以链接,链接之后可以管理哪个数据库,对哪个库里的表有什么操作的权限,还有事务处理的提交,回滚等等操作都属于DCL
例如:crant, revoke, commit, rollback等语句
二、创建数据库dbshop,设计数据表
对程序员来说
1). 要为项目设计表,所以创建表是比较重要的
2). 最主要的SQL语句就是增、删、改、查
先创建一个数据库(数据库名称是dbshop),创建库就属于创建对象,属于DDL语句
1 | create database IF NOT EXISTS dbshop charset utf8; |
2 |
3 | use dbshop; |
4 |
5 | \s |
数据库有了,创建一个商品分类表和商品表
产品分类(类别)表cats
id
pid 父id
name 类别名称
desn 类别的描述
给id字段加上主键
name,pid 创建索引
1 | create table cats( |
2 | id int not null auto_increment, |
3 | pid int not null default '0' , |
4 | name varchar (60) not null default '' , |
5 | desn text, |
6 | primary key (id), |
7 | index nameid( name ,pid) |
8 | )engine=InnoDB default charset=utf8; |
产品表products
id
cid 产品所在的类别id
name 产品名称
price 产品价格
num 产品数量
desn 产品介绍
ptime 产品发布时间
01 | create table products( |
02 | id int not null auto_increment, |
03 | cid int not null default '0' , |
04 | name varchar (60) not null default '' , |
05 | price double (7,2) not null default '0.00' , |
06 | num int not null default '0' , |
07 | desn text, |
08 | ptime int not null default '0' , |
09 | primary key (id), |
10 | key pname( name , price) |
11 | )engine=InnoDB default charset=utf8; |
创建库、创建表是DDL语句,下面DML语句插入insert、更新update、删除delete
插入表数据insert
insert into 表名 ([字段列表]) values (值列表);
插入语句特点:
1). 如果表名后没有给出"字段列表",则"值列表"必须列出所有字段的值,必须按表中默认的字段顺序插入
id字段自动增长,插入null、插入0都是自动增长
2). 所有需要写"字段名"的地方,都不要加单引号或双引号,但所有值建议都要以字符串形式是使用
1 | insert into cats values ( null , 0, 'soft' , '这是一个软件分类' ); |
为什么所有值都要以字符串形式使用?
因为Mysql会自动类型转换
比如数字1加数字1,得到的结果是2
1 | select 1 + 1; |
+-------+
| 1 + 1 |
+-------+
| 2 |
+-------+
数字1加上字符串的'1',还是等于2,因为mysql会根据环境自动将类型转成整数
1 | select 1 + '1' ; |
+---------+
| 1 + '1' |
+---------+
| 2 |
+---------+
所以插入字符串的'1',会自动转成整数型
1 | insert into cats (id, pid, name , desn) values ( null , '1' , 'JAVA' , 'java分类' ); |
3). 建议插入数据时,最好给出"字段列表",则值要和"字段列表"对应即可,
1 | insert into cats (pid, name ) values ( '1' , 'PHP' ); |
"字段列表"可以不按表中字段的顺序,后面的值与前面的字段一一对应
1 | insert into cats ( name , pid) values ( 'JAVASCRIPT' , '1' ); |
4). 还可以插入多个值列表
insert into 表名 ( [字段名] ) values ( 值列表1 ), ( 值列表2 ), ( 值列表3 );
1 | insert into cats (pid, name , desn) |
2 | values |
3 | ( '2' , 'J2se' , '属于java类pid是2' ), |
4 | ( '2' , 'J2me' , '属于java类pid是2' ), |
5 | ( '2' , 'J2me' , '属于java类pid是2' ), |
6 | ( '3' , 'smarty' , '属于PHP类pid是3' ), |
7 | ( '3' , 'thinkphp' , '属于PHP类pid是3' ); |
8 |
9 | select * from cats; |
+----+-----+------------+------------------+
| id | pid | name | desn |
+----+-----+------------+------------------+
| 1 | 0 | soft | 这是一个软件分类|
| 2 | 1 | JAVA | java分类 |
| 3 | 1 | PHP | NULL |
| 4 | 1 | JAVASCRIPT | NULL |
| 5 | 2 | J2se | 属于java类pid是2|
| 6 | 2 | J2me | 属于java类pid是2|
| 7 | 2 | J2me | 属于java类pid是2|
| 8 | 3 | smarty | 属于PHP类pid是3|
| 9 | 3 | thinkphp | 属于PHP类pid是3|
+----+-----+------------+------------------+
当然还有将别的表的数据拷贝过来的插入语句
update更新修改语句
update 表名 set 字段 = '值' [ where 字段名 = '值' ];
修改语句必须给where条件,条件的目的是指定要更改的记录,可以通过条件指定一条,也可以指定多条
不仅是update更新语句需要条件,delete删除语句也需要条件,
delete from 表名 [ where 字段名 = '值' ];
删除语句的条件,跟更新语言的条件一样的,删除指定的记录
更新语句如果不给条件,整个表的数据都会被改变
删除语句如果不给条件,整个表所有的记录都会被删除,如果需要删除整个表这个写法效率还比较低,不如用truncate
update语句、delete语句、select语句条件的写法都一样,都通过where条件,谁大于谁,谁小于谁……
另外insert插入, update更新, select查询还有where条件,这四种语句都可以使用各种运算符,加,减,乘,除,都是可以的
可以把字段当做一个"变量"来使用
更新、删除、查找,只要写对条件就能准确找到要管理的一条或多条语句
1 | update cats set |
2 | name = 'J2ee pid是2' , |
3 | desn= 'J2ee属于java,所以pid是2' |
4 | where id = 6; |
用"比较运算符并且"更新了多条记录
1 | update cats set desn= '属于java,pid是2' where id >= 5 && id <=7; |
所有在查询语句写where条件,在更新和删除语句里都可以使用
delete删除语句
直接这样写就把表删空了
1 | delete from cats; -- 不要执行,执行后面的练习没法做了 |
如果要清空表 truncate table 表名; 语句效果会更高
所以删除语句一定要给一个条件
1 | delete from cats where id > 8; |
插入、更新、删除写法比较固定,给一些简单条件就行了,最复杂的就是select查询语句。
再学查询语句之前要先有数据,所以先给products商品表插入一些数据
01 | insert into |
02 | products(cid, name , price, num, desn, ptime) |
03 | values |
04 | (1, 'javaOne' , '34.5' , '10' , 'very good' , '1631413148' ), |
05 | (1, 'javaTwo' , '414.56' , '30' , 'very good' , '1631413180' ), |
06 | (1, 'javaThree' , '441.56' , '50' , 'very good' , '1631413180' ), |
07 | (1, 'javaFour' , '144.56' , '30' , 'very good' , '1631413180' ), |
08 | (3, 'dedeCms' , '44.56' , '30' , 'very good' , '1631413180' ), |
09 | (3, 'phpCms' , '44.56' , '30' , 'very good' , '1631413180' ), |
10 | (3, 'ecshop' , '84.56' , '30' , 'very good' , '1631413201' ), |
11 | (4, 'jquery' , '244.56' , '8' , 'very good' , '1631413201' ), |
12 | (4, 'Vue' , '44.56' , '30' , 'veryod' , '1631413201' ), |
13 | (4, 'React' , '944.56' , '30' , 'very good' , '1631413201' ), |
14 | (4, 'bootStrap' , '4444.56' , '30' , 'very good' , '1631413201' ), |
15 | (5, 'J2seONE' , '544.56' , '30' , 'very good' , '1631413201' ), |
16 | (5, 'J2seTwo' , '44.56' , '30' , 'very good' , '1631413201' ), |
17 | (5, 'J2seThree' , '44.56' , '90' , 'very good' , '1631413201' ), |
18 | (5, 'J2seFour' , '44.56' , '30' , 'very good' , '1631413201' ), |
19 | (5, 'J2seFive' , '4324.56' , '30' , 'very good' , '1631413243' ), |
20 | (5, 'J2seFix' , '454.56' , '30' , 'very good' , '1631413243' ), |
21 | (6, 'J2eeOne' , '44.56' , '30' , 'very good' , '1631413243' ), |
22 | (6, 'J2eeTwo' , '454.56' , '30' , 'very good' , '1631413243' ), |
23 | (6, 'J2eeThree' , '4.56' , '30' , 'very good' , '1631413243' ), |
24 | (6, 'J2eeFour' , '44.56' , '30' , 'very good' , '1631413243' ), |
25 | (6, 'J2eeFive' , '494.56' , '30' , 'very good' , '1631413201' ), |
26 | (7, 'J2meOne' , '48.56' , '30' , 'very good' , '1631413262' ), |
27 | (7, 'J2meTwo' , '324.56' , '30' , 'very good' , '1631413262' ), |
28 | (3, 'smartyOne' , '4432.56' , '30' , 'very good' , '1631413262' ), |
29 | (3, 'smartyTwo' , '434.56' , '30' , 'very good' , '1631413262' ), |
30 | (3, 'smartyThree' , '442.56' , '30' , 'very good' , '1631413262' ); |
SQL语言的设计与编写(下)
上节课学的是sql语句中的DDL还有DML,这节课学DQL命令,也就是通用的查询数据表中的数据,
查询数据表中的数据,可以完成简单的单表查询,也可以完成复杂的多表查询和嵌套查询,
所以select查询检索,是sql语句的核心,使用sql语句中使用频率最高,达到60%都在使用select语句
通过适当的sql语句查询(select)的编写,可以让数据库服务器根据用户的需求,检索出所需要的一些数据资料,并按照用户指定的格式进行整理并返回,所以sql语句可以对数据表或者视图进行检索
三、select语法
select [ALL | DISTINCT]
{* | table.* | [table.]field1[as alias1][,[table.]field2[as alias2]][……]}
FROM 表名1, [表名2]
[WHERE]
[GROUP BY]
[HAVING]
[ORDER BY]
[LIMLT count]
上面是select语法最主要的部分,
其中[中括号]的部分表示可选的部分,
{大括号}部分表示必须从中选择一个,如果有多个中间都是用逗号','隔开的,
from后面的多个表名,有可能是联合查询,中间也是使用逗号隔开
1、字段,要列出查询的字段
select语句用"*"选择字段有两个弊端
1). 所有字段全部查询出来
2). 会按表默认的字段顺序查出来,如果用索引数组处理,必须按照表的字段顺序
不要用"*"号,要用字段列表,
当然也可以用 表名.*(products.*)也是一样的
1 | select products.* from products; |
字段列表可以加 "表名.字段名",也可以直接用"字段名",当然一个表的时候很少用 表名.字段名
比如多个字段用逗号隔开,只查询name,price两个字段,
1 | select name , price from products; |
就查询两个字段,这样不仅效率提高了,读着也清晰
+-------------+---------+
| name | price |
+-------------+---------+
| J2eefive | 494.56 |
| J2eefour | 44.56 |
| J2eeone | 44.56 |
…………
还可以指定顺序,比如重点是看价格,把价格 price字段 放到前面
1 | select price, name from products; |
顺序就按照指定的顺序
+---------+-------------+
| price | name |
+---------+-------------+
| 494.56 | J2eefive |
| 44.56 | J2eefour |
| 44.56 | J2eeone |
…………
单表查询,字段前可以不用使用表名,也可以使用表名
1 | select products.price, products. name from products; |
当然这里是没有必要的,因为是单表查询,通常都不使用"字段名.表名",但是使用"字段列表"是比较有用的(不要使用通配符)
2、可以为每个字段起别名
如果觉得字段的名字不好,可以给字段起别名,怎么起别名?
比如价钱price可以起别名bookprice
1 | select price as bookprice from products; |
bookprice就是别名
+-----------+
| bookprice|
+-----------+
| 4444.56 |
| 44.56 |
| 84.56 |
……
name字段是书对名称,起别名bookname
1 | select price as bookprice, name as bookname from products; |
+-----------+-------------+
| bookprice| bookname |
+-----------+-------------+
| 4444.56 | bootStrap |
| 44.56 | dedecms |
| 84.56 | ecshop |
……
别名前面也可以不加as关键字,直接加空格也是可以的,前面是原字段名后面是起的别名
1 | select price bookprice, name bookname from products; |
什么时候用到别名?
1). 多表查询的时候,两个表字段名冲突,就必须起别名
2). 查Mysql版本,版本的括号,发生冲突的时候也要用到别名(关键字冲突的时候)
不仅是"字段"可以起别名,"表"也可起别名,当然也是在多表查询的时候起别名
3、distinct关键字作用,整个查询列表,取消重复的数据,只返回一个,而不是单独的一列
如果select语句返回的记录结果中,包含重复的值,可以使用关键字distinct
distinct作用是取消重复的数据,重复的数据只返回一个,但是不是针对单独的某一个字段取消重复的,而是针对整个记录去取消重复的
1 | select price, name from products; |
有27条记录,价钱44.56有很多重复的
+---------+-------------+
| price | name |
+---------+-------------+
| 4444.56 | bootstrap |
| 44.56 | dedecms |
| 84.56 | ecshop |
| 494.56 | J2eefive |
| 44.56 | J2eefour |
| 44.56 | J2eeone |
…………
27 rows in set (0.107 sec)
select后面默认的关键词是ALL意思取出所有的,不用默认的用distinct
1 | select distinct price, name from products; |
distinct是让整行不重复,没有起到效果,还是27条记录没有取到去重复的效果?
+---------+-------------+
| price | name |
+---------+-------------+
| 4444.56 | bootstrap |
| 44.56 | dedecms |
| 84.56 | ecshop |
| 494.56 | J2eefive |
| 44.56 | J2eefour |
| 44.56 | J2eeone |
…………
27 rows in set (0.107 sec)
distinct的作用是整行不重复,现在没有那个行是重复的,价钱priec字段44.56重复,但是后面的name字段的值不重复,
所以是整条记录上不重复,并不是某一单独字段
比如就单独查一个价钱price字段,就找不到重复的了
1 | select distinct price from products; |
+---------+
| price |
+---------+
| 4444.56 |
| 44.56 |
| 84.56 |
| 494.56 |
| 4.56 |
| 454.56 |
| 48.56 |
| 324.56 |
| 4324.56 |
| 544.56 |
| 144.56 |
| 34.50 |
| 441.56 |
| 414.56 |
| 244.56 |
| 944.56 |
| 4432.56 |
| 442.56 |
| 434.56 |
+---------+
19 rows in set (0.011 sec)
关键字distinct查询范围是整个查询的列表,并不是单独的一列,
如果同时对两列数据查询的时候,使用了distinct关键字,将返回这两列数据唯一的一个组合,
所以distinct不是每一个字段都能加的,是针对整个记录的
4、在SQL语句中都使用表达式
增,删,改,查中,可以使用任意的算术运行符号,条件运算符,逻辑运算符号,跟程序中是一样的
算术运算符号
1 | select 1+1; |
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 | select 10*5; |
+------+
| 10*5 |
+------+
| 50 |
+------+
1 | select 9*8; |
+-----+
| 9*8 |
+-----+
| 72 |
+-----+
加、减、乘、除、取余都可以,这是最常用,最简单的的用法
什么时候用?
比如update更新语句,num字段当做变量加1再赋给num字段
1 | update products set num = num+1 where id = 27; |
2 |
3 | update products set num = num+1 where id = 27; |
4 |
5 | update products set num = num+1 where id = 27; |
6 |
7 | select id, num from products where id = 27; |
每执行一次sql语句,num列就累加1一次
+----+-----+
| id | num |
+----+-----+
| 27 | 33 |
+----+-----+
查询select语句也可以计算,比如查询打八折后的价钱,起一个别名dzprice
1 | select name , price, price*0.8 dzprice from products; |
+-------------+---------+---------+
| name | price | dzprice |
+-------------+---------+---------+
| bootstrap | 4444.56 | 3555.65 |
| dedecms | 44.56 | 35.65 |
| ecshop | 84.56 | 67.65 |
…………
下面用比较运算符
比如查询
1). where条件是 "id > 4 或 id < 10"
2). 打八折
1 | select id, name , price, price*0.8 dzprice from products where id > 4 && id < 10; |
+----+---------+--------+---------+
| id | name | price | dzprice |
+----+---------+--------+---------+
| 5 | dedecms| 44.56 | 35.65 |
| 6 | phpcms | 44.56 | 35.65 |
| 7 | ecshop | 84.56 | 67.65 |
| 8 | jqurey | 244.56 | 195.65 |
| 9 | vue | 44.56 | 35.65 |
+----+---------+--------+---------+
乘号是算术运算符、大于小于是比较运算符、&&是逻辑运算符,还可以用位运算符
上面说查询关键字的时候要起别名,比如查询mysql版本 和 1.23 * 10
1 | select version(), 1.23 * 10; |
+------------------+-----------+
| version() | 1.23 * 10 |
+------------------+-----------+
| 5.7.18-cynos-log| 12.30 |
+------------------+-----------+
"version()" 这个字段名有括号
"1.23 * 10" 这个字段名有点,,有星号
在PHP程序里,返回的字段名列表包含了括号、点、星号,这些特殊字符,如果将这些sql语句嵌入到PHP中使用,会和PHP的运算符号混淆,极易产生错误,
因此遇到这些字段名不是标准字符串,有些特殊符号的情况下,就需要给字段起别名
1 | select version() mysql_version, 1.23 * 10 as experssion; |
+------------------+------------+
| mysql_version | experssion |
+------------------+------------+
| 5.7.18-cynos-log| 12.30 |
+------------------+------------+
5、where字句
where子句可以在select语句中,可以在update语句中,可以在delete语句,这三个语句都可以有where从句,
因为我们可以使用where子句去检索条件,实现从数据库列表中检索出符合条件的记录,条件当然可以由一个或多个,由逻辑运算表达式组成的。
不仅是select查询语句用where条件,update语句,delete语句都需要定义到某一条记录,或者某一些记录,都需要where字句。
但其它的[group by],[having],[order by],[limit]这些可选部分,update更新语句,delete删除语句不一定有,但where条件都可以使用,也就是说select怎么使用where,别的语句就怎么使用。
在where字句中可以使用,逻辑运算符号,将多个条件组合(还有比较运算符号)
逻辑运算符:
与 | 或 | 非 |
---|---|---|
&& | || | ! |
AND | OR | NOT |
比较运算符号(比较运算符比PHP程序里的要多):
= | 等于: 号在字段列表里是赋值,在where后面是判断等于的意思 再mysql里和程序中的两个等号(==)写法不一样 |
<=> | 特殊的等于,和等号=作用一样,但可以用于NULL值的比较 |
!= | 不等于 |
<> | 不等于 |
< | 大于 |
<= | 大于等于 |
> | 小于 |
>= | 小于等于 |
下面是数据库中有的,而程序中没有的 | |
IS NULL | |
IS NOT NULL | |
BETWEEN AND | |
NOT BETWEEN AND | |
LIKE | 两个通配符号: 1. _ 任意一个字符) 2. % 另个或多个任意字符 |
NOT LIKE | |
IN | |
REGEXP RLIKE | 正则表达式 |
比较运算符在where条件里用的是比较多的,逻辑运算符通常是串起来使用
在构造搜索条件的时候,不管是更新条件,删除条件,还是查询条件:
1). 如果是算术运算,只能对数值类型进行运算,并且只能在相同的数据类型之间进行记录的比较,
比如字符串和数值不能进行比较,除非他们转换成相同的数据类型
2). 如果使用字符串做为检索条件查询的时候,则值必要要用单引号括起来,
而对数值类型数据,单引号则不是必须的
比如,找出100元以下的产品,这是一个条件的运算符
1 | select id, price, name from products where price < 100; |
+----+-------+-----------+
| id | price | name |
+----+-------+-----------+
| 5 | 44.56 | dedeCms |
| 7 | 84.56 | ecshop |
| 21 | 44.56 | J2eeFour |
| 18 | 44.56 | J2eeOne |
| 20 | 4.56 | J2eeThree |
| 23 | 48.56 | J2meOne |
| 15 | 44.56 | J2seFour |
| 14 | 44.56 | J2seThree |
| 13 | 44.56 | J2seTwo |
| 1 | 34.50 | javaOne |
| 6 | 44.56 | phpCms |
| 9 | 44.56 | Vue |
+----+-------+-----------+
如果想要"大于50小于100"的商品,就要用多个条件运算符
1 | select id, price, name from products where price > 50 and price < 100; |
+----+-------+--------+
| id | price | name |
+----+-------+--------+
| 7 | 84.56 | ecshop|
+----+-------+--------+
= 等号
这里的等号(=)和程序中的两个等号(==)一样,只是写法不一样
1 | select id, price, name from products where id = 10; |
+----+--------+-------+
| id | price | name |
+----+--------+-------+
| 10 | 944.56 | React |
+----+--------+-------+
!= 不等于
查询条件,id不等于10的
1 | select id, price, name from products where id != 10; |
查询返回 26 rows in set (0.00 sec)
<> 不等于
或者用这个 <> 不等于
1 | select id, price, name from products where id <> 10; |
也是返回 26 rows in set (0.00 sec)
and 符号
条件也可以用and符号(&&也是and符号)
1 | select id, price, name from products where price > 50 && price < 100; |
+----+-------+--------+
| id | price | name |
+----+-------+--------+
| 7 | 84.56 | ecshop |
+----+-------+--------+
and和&&是别名的关系,!=和<>也是别名的关系
在<=>符号之前先看一下 is null 和 is not null
插入几行字段有null值的数据
1 | insert into products ( name ) values ( 'a' ); |
2 | insert into products ( name ) values ( 'b' ); |
3 | insert into products ( name ) values ( 'c' ); |
4 |
5 | select id, name , price, num, desn from products; |
设计表的时候其它字段都有默认值,只有desn字段没有指定默认值,没有给值的时候默认就是null(空)
+----+-------------+---------+-----+-----------+
| id | name | price | num | desn |
+----+-------------+---------+-----+-----------+
…………
| 26 | smartytwo | 434.56 | 30 | very good|
| 27 | smartythree | 442.56 | 30 | very good|
| 28 | a | 0.00 | 0 | NULL |
| 29 | b | 0.00 | 0 | NULL |
| 30 | c | 0.00 | 0 | NULL |
+----+-------------+---------+-----+-----------+
30 rows in set (0.00 sec)
NULL
NUll(空)值是不能用等号(=)处理的
1 | select id, name , desn from products where desn = null ; |
返回 Empty set (0.011 sec) 没有查出来
is null
null的值不能用等号去查,NULL是一种值,遍历条件的时候用 "is null"
1 | select id, name , desn from products where desn is null ; |
+----+------+------+
| id | name | desn |
+----+------+------+
| 28 | a | NULL|
| 29 | b | NULL|
| 30 | c | NULL|
+----+------+------+
is not null
查询"非空"用 is not null
1 | select id, name , desn from products where desn is not null ; |
27 rows in set (0.00 sec) 非空的数据都查出来了共27条
所以null值处理比较麻烦,创建表的时候,尽量使用非空(not null)
1). 非空的好处是,有默认的值,转成程序的时候,也会转成对应的数据类型
2). 而null(空)转成程序,有可能转成0,有可能转成null(PHP里面的空),有可能会转成空字符串,他是不一定的,所以不好处理
<=>
<=>这个符号是干什么的?
1). 可以判断等于(=)
1 | select id, name , desn from products where desn <=> 'veryod' ; |
+----+------+--------+
| id | name| desn |
+----+------+--------+
| 9 | Vue | veryod |
+----+------+--------+
2). 也可以判断null值,等于空的值
select id, name, desn from products where desn <=> null;
+----+------+------+
| id | name| desn |
+----+------+------+
| 28 | a | NULL |
| 29 | b | NULL |
| 30 | c | NULL |
+----+------+------+
<=> 和 = 作用一样,但<=>可以用于null(空)值比较
between and
查询id大于等于10并且小于等于20的数据,其实没有between用and也能查询出来
1 | select id, name from products where id >= 10 && id <= 20; |
id大于20和小于10,包括10和20的都查询出来了
+----+-----------+
| id | name |
+----+-----------+
| 10 | React |
| 11 | bootStrap |
| 12 | J2seONE |
| 13 | J2seTwo |
| 14 | J2seThree |
| 15 | J2seFour |
| 16 | J2seFive |
| 17 | J2seFix |
| 18 | J2eeOne |
| 19 | J2eeTwo |
| 20 | J2eeThree |
+----+-----------+
这样的语句就可以用between and
1 | select id, name from products where id between 10 and 20; |
+----+-----------+
| id | name |
+----+-----------+
| 10 | React |
| 11 | bootStrap |
| 12 | J2seONE |
| 13 | J2seTwo |
| 14 | J2seThree |
| 15 | J2seFour |
| 16 | J2seFive |
| 17 | J2seFix |
| 18 | J2eeOne |
| 19 | J2eeTwo |
| 20 | J2eeThree |
+----+-----------+
between 10 and 20 和 id >= 10 && id <= 20 作用是一样的,在10和20之间,包括两边的边界10和20的这个范围
not between
正好是取反
1 | select id, name from products where id not between 10 and 20; |
除了10和20之间的,也包括10和20都没有了
IN列表
IN列表是用单个处理的
1 | select id, name , price, num from products where id in (5, 10, 15, 20, 25); |
+----+-----------+---------+-----+
| id | name | price | num|
+----+-----------+---------+-----+
| 5 | dedeCms | 44.56 | 30 |
| 10 | React | 944.56 | 30 |
| 15 | J2seFour | 44.56 | 30 |
| 20 | J2eeThree | 4.56 | 30 |
| 25 | smartyOne| 4432.56 | 30 |
+----+-----------+---------+-----+
where条件不仅select语句能用,其它语句里面也能用
比如update更新语句,把符合条件的num字段值修改为77
1 | update products set num = 77 where id in (5, 10, 15, 20, 25); |
返回 Rows matched: 5 Changed: 5 Warnings: 0 五条记录被更新了
再查询products表,num字段被更新了
1 | select id, name , price, num from products where id in (5, 10, 15, 20, 25); |
+----+-----------+---------+-----+
| id | name | price | num |
+----+-----------+---------+-----+
| 5 | dedeCms | 44.56 | 77 |
| 10 | React | 944.56 | 77 |
| 15 | J2seFour | 44.56 | 77 |
| 20 | J2eeThree | 4.56 | 77 |
| 25 | smartyOne| 4432.56| 77 |
+----+-----------+---------+-----+
delete删除语句也是一样,删除where条件为 id in(5, 10, 15) 这三条数据
1 | delete from products where id in (5, 10, 15); |
返回 Query OK, 3 rows affected (0.011 sec) 三条语句受到影响
再查询where条件为 in (5, 10, 15, 20, 25) 就剩下20和25两条数据
1 | select id, name , price, num from products where id in (5, 10, 15, 20, 25); |
5、10、15已经删除,不存在了
+----+-----------+---------+-----+
| id | name | price | num |
+----+-----------+---------+-----+
| 20 | J2eeThree | 4.56 | 77 |
| 25 | smartyOne| 4432.56 | 77 |
+----+-----------+---------+-----+
like 和 not like
like和not like是模糊查询,还有正则regexp rlike都属于模糊查询中的一种
1). 也就是在select查询语句中where从句中,使用like对数据库进行模糊查询,
将查询的结果锁定在一个范围内
2). 那必须在查询条件中,有下划线(_)和百分号(%)这两个通配符号
_1. 下划线 _ 表示任意一个字符
_2. 百分号 % 可以表示零个或多个任意字符
比如查询由任意6个字符串组成的名字
1 | select id, name , price, num from products where name like '______' ; |
+----+--------+--------+-----+
| id | name | price | num |
+----+--------+--------+-----+
| 6 | phpCms| 44.56 | 30 |
| 7 | ecshop | 84.56 | 30 |
| 8 | jquery | 244.56 | 8 |
+----+--------+--------+-----+
查询java后面跟3个字符的(java___)
1 | select id, name , price, num from products where name like 'java___' ; |
+----+---------+--------+-----+
| id | name | price | num |
+----+---------+--------+-----+
| 1 | javaone | 34.50 | 10 |
| 2 | javatwo | 414.56 | 30 |
+----+---------+--------+-----+
一个下划线代表任意一个字符,百分号可以代表任意多个字符
比如以%s结尾的,字母s前面可以是任意多个字符
1 | select id, name , price, num from products where name like '%s' ; |
+----+--------+-------+-----+
| id | name | price | num |
+----+--------+-------+-----+
| 6 | phpcms| 44.56 | 30 |
+----+--------+-------+-----+
name字段以字母s开头的
1 | select id, name , price, num from products where name like 's%' ; |
+----+-------------+---------+-----+
| id | name | price | num |
+----+-------------+---------+-----+
| 25 | smartyOne | 4432.56| 77 |
| 27 | smartyThree | 442.56 | 30 |
| 26 | smartyTwo | 434.56 | 30 |
+----+-------------+---------+-----+
以字母j开头的
1 | select id, name , price, num from products where name like 'j%' ; |
下面这些都是以 字母j 开头的
+----+-----------+---------+-----+
| id | name | price | num |
+----+-----------+---------+-----+
| 1 | javaOne | 34.50 | 10 |
| 2 | javaTwo | 414.56 | 30 |
| 3 | javaThree | 441.56 | 50 |
| 4 | javaFour | 144.56 | 30 |
| 8 | jquery | 244.56 | 8 |
| 12 | J2seONE | 544.56 | 30 |
| 13 | J2seTwo | 44.56 | 30 |
| 14 | J2seThree | 44.56 | 90 |
| 16 | J2seFive | 4324.56 | 30 |
| 17 | J2seFix | 454.56 | 30 |
| 18 | J2eeOne | 44.56 | 30 |
| 19 | J2eeTwo | 454.56 | 30 |
| 20 | J2eeThree | 4.56 | 77 |
| 21 | J2eeFour | 44.56 | 30 |
| 22 | J2eeFive | 494.56 | 30 |
| 23 | J2meOne | 48.56 | 30 |
| 24 | J2meTwo | 324.56 | 30 |
+----+-----------+---------+-----+
查询名字(name字段)包含PHP的,%php%代表0个或多个PHP
1 | select id, name , price, num from products where name like '%PHP%' ; |
+----+--------+-------+-----+
| id | name | price | num |
+----+--------+-------+-----+
| 6 | phpCms| 44.56 | 30 |
+----+--------+-------+-----+
名字(name字段)中包含java的
1 | select id, name , price, num from products where name like '%java%' ; |
+----+-----------+--------+-----+
| id | name | price | num|
+----+-----------+--------+-----+
| 1 | javaOne | 34.50 | 10 |
| 2 | javaTwo | 414.56 | 30 |
| 3 | javaThree | 441.56 | 50 |
| 4 | javaFour | 144.56 | 30 |
+----+-----------+--------+-----+
名字(name字段)中包含two的
1 | select id, name , price, num from products where name like '%two%' ; |
+----+-----------+--------+-----+
| id | name | price | num |
+----+-----------+--------+-----+
| 2 | javaTwo | 414.56 | 30 |
| 13 | J2seTwo | 44.56 | 30 |
| 19 | J2eeTwo | 454.56 | 30 |
| 24 | J2meTwo | 324.56 | 30 |
| 26 | smartyTwo| 434.56 | 30 |
+----+-----------+--------+-----+
not like 模糊查询
比如名字name字段不包含two字样的
1 | select id, name , price, num from products where name not like '%two%' ; |
这些名字字段是不包含two的
+----+-------------+---------+-----+
| id | name | price | num|
+----+-------------+---------+-----+
| 1 | javaOne | 34.50 | 10 |
| 3 | javaThree | 441.56 | 50 |
| 4 | javaFour | 144.56 | 30 |
| 6 | phpCms | 44.56 | 30 |
| 7 | ecshop | 84.56 | 30 |
| 8 | jquery | 244.56 | 8 |
| 9 | Vue | 44.56 | 30 |
| 11 | bootStrap | 4444.56 | 30 |
| 12 | J2seONE | 544.56 | 30 |
| 14 | J2seThree | 44.56 | 90 |
| 16 | J2seFive | 4324.56 | 30 |
| 17 | J2seFix | 454.56 | 30 |
| 18 | J2eeOne | 44.56 | 30 |
| 20 | J2eeThree | 4.56 | 77 |
| 21 | J2eeFour | 44.56 | 30 |
| 22 | J2eeFive | 494.56 | 30 |
| 23 | J2meOne | 48.56 | 30 |
| 25 | smartyOne | 4432.56| 77 |
| 27 | smartyThree| 442.56 | 30 |
| 28 | a | 0.00 | 0 |
| 29 | b | 0.00 | 0 |
| 30 | c | 0.00 | 0 |
+----+-------------+---------+-----+
22 rows in set (0.00 sec)
当然,如果使用完整的字符串作为精确查询,最好不用like进行模糊查询,应该直接使用等号(=)
比如查询名字是 a 的行
1 | select id, name , price, num from products where name = 'a' ; |
+----+------+-------+-----+
| id | name| price | num|
+----+------+-------+-----+
| 28 | a | 0.00 | 0 |
+----+------+-------+-----+
regexp 正则的方式,正则不用加斜线(定界符)
查询名字以 字母j 开头的
1 | select id, name , price from products where name regexp '^j' ; |
+----+-----------+---------+
| id | name | price |
+----+-----------+---------+
| 22 | J2eeFive | 494.56 |
| 21 | J2eeFour | 44.56 |
| 18 | J2eeOne | 44.56 |
| 20 | J2eeThree | 4.56 |
| 19 | J2eeTwo | 454.56 |
| 23 | J2meOne | 48.56 |
| 24 | J2meTwo | 324.56 |
| 16 | J2seFive | 4324.56 |
| 17 | J2seFix | 454.56 |
| 12 | J2seONE | 544.56 |
| 14 | J2seThree | 44.56 |
| 13 | J2seTwo | 44.56 |
| 4 | javaFour | 144.56 |
| 1 | javaOne | 34.50 |
| 3 | javaThree | 441.56 |
| 2 | javaTwo | 414.56 |
| 8 | jquery | 244.56 |
+----+-----------+---------+
以字母s结尾的名字
1 | select id, name , price from products where name regexp 's$' ; |
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 6 | phpcms| 44.56 |
+----+--------+-------+
6、多表查询(也称为连接查询)
多表查询是数据库中经常用到的形式,
比如百位级数据的时候,就不能还是用一个表了,如果数据量很大的话,就需要像切豆腐块一样切掉,
怎么切?
1). 可以把长表分成短表,
比如20字段的分成五个四个字段的,当然要按照索引关系去分,
2). 可以按照类别分表,
比如某一类商品放到一个表里面,另外一类商品放到另外一个表里面
这样就能大大提高速度,但是使用的时候要链在一起,
比如cats分类表,prducts产品表,这两张表怎么链在一起查询呢?
如果要对多张表的数据同时进行查询,就可以通过 链接运算符 实现多个表查询,也叫链接查询,
多个表链接是关系型数据库模型的主要特点,也是区别于其它类型数据库管理系统的标志
在关系型数据库管理系统中,规范化的逻辑数库设计,也就是正规的方法,来将数据表分为多个相同的表,
也就是拥有大量的窄表,也就是列较少的表,是规范化数据库设计的特征,
而拥有少量的宽表,也就是列较多的表称为宽表,是非规范化数据库的特征
当我们检索的时候,通过链接操作查询出存在多个表中的信息,
多个表查询给用户带来很大的灵活性,可以在任意时候增加新的数据类型,
为不同的实体创建新的表,然后通过链接查询多个表,就可以将多个数据表一起拿过来
比如想知道prducts产品表,javaone属于那个分类,javaone分类在cats表id是2,想知道这个分类cats表中的分类名称怎么办呢?
多个表查询分:
1). 非等值的多表查询
2). 等值的多表查询
多表查询和普通的单表查询很相似,
1). 都是通过select语句,
2). 只不过在多表查询时,需要把多张表的名字全部填在from字句中,然后用逗号分开
3). 字段列表不能用*号,容易出错
查询pid字段和price字段,因为两张表里面这个两个字段名没有重复的
1 | select pid, price from cats, products; |
216 rows in set 返回216条数据,没有重复的字段名称,这么执行还可以
但是再加一个name字段,两张表里都有name字段就出错了
1 | select pid, price, name from cats, products; |
返回报错信息 ERROR 1052 (23000): Column 'name' in field list is ambiguous
因为不知道name字段,是cats表的还是products表的
所以对于这样,两个表有相同字段名的,必须用指定表名(不是起别名)
比如类别cats表里面的name字段,这样写cats.name
1 | select cats. name , products. name , products.price from cats, products; |
+-------------+-------------+---------+
| name | name | price |
+-------------+-------------+---------+
| J2ee pid是2 | javaOne | 34.50 |
| J2me | javaOne | 34.50 |
| J2se | javaOne | 34.50 |
| JAVA | javaOne | 34.50 |
……
216 rows in set (0.013 sec)
但是这样查询,sql语句中表名太长了,给表起简短别名
1 | select c. name , c.desn, p. name , p.desn from cats c, products as p; |
+-------------+------------------+-------------+-----------+
| name | desn | name | desn |
+-------------+------------------+-------------+-----------+
| soft | 这是一个软件分类| javaOne | very good |
| JAVA | java分类 | javaOne | very good |
| PHP | NULL | javaOne | very good |
……
216 rows in set (0.013 sec)
对于名称相同的字段,除了加表名之后,给每个字段起别名
1 | select c. name cname, c.desn cdesn, p. name pname, p.desn pdesn from cats c, products as p; |
+-------------+------------------+-------------+-----------+
| cname | cdesn | pname | pdesn |
+-------------+------------------+-------------+-----------+
| soft | 这是一个软件分类| javaOne | very good |
| JAVA | java分类 | javaOne | very good |
| PHP | NULL | javaOne | very good |
……
216 rows in set (0.013 sec)
三张表链接查询也是同样的,在from后面再加一个表名,
但是有一个问题,多个表查询的时候分为 链接查询 和 非等链接查询,
比如左链接、右链接也是处理 笛卡尔乘积 问题
怎么出现的笛卡尔乘积?
也就是两个表查询的时候,假如第一个表叫a表,第二个表加b表,
将a表的中的每一条记录和b表中的每一条记录依次匹配,
又将a表中第二条记录,和b表中再次匹配一次,所以是一个相乘的关系,这个相乘的结果就是笛卡尔乘积,
显然得到的结果是没有意义的
那么怎么办呢?
我们就需要按照等值查询,因为这两个表是有关系的,什么关系呢?
在产品表里面的保存的cid,等于类别表里的id,所以可以加一个where条件
1 | select c. name cname , c.desn cdesn, p. name pname, p.desn pdesn, p.num pnum |
2 | from |
3 | cats c, products as p |
4 | where |
5 | c.id = p.cid; |
查询出24条记录
+-------------+------------------+-------------+-----------+------+
| cname | cdesn | pname | pdesn | pnum|
+-------------+------------------+-------------+-----------+------+
| soft | 这是一个软件分类 | javaOne | very good | 10 |
| soft | 这是一个软件分类 | javaTwo | very good | 30 |
| soft | 这是一个软件分类 | javaThree | very good | 50 |
……
24 rows in set (0.008 sec)
也可以在增加一个where条件,并且c.id = 3 查询出是php类的
1 | select c. name cname , c.desn cdesn, p. name pname, p.price pprice, p.desn pdesn, p.num pnum |
2 | from |
3 | cats c, products as p |
4 | where |
5 | c.id = p.cid |
6 | and |
7 | c.id = 3; |
+-------+-------+-------------+---------+-----------+------+
| cname| cdesn | pname | pprice | pdesn | pnum|
+-------+-------+-------------+---------+-----------+------+
| PHP | NULL | phpCms | 44.56 | very good | 30 |
| PHP | NULL | ecshop | 84.56 | very good | 30 |
| PHP | NULL | smartyOne | 4432.56 | very good | 77 |
| PHP | NULL | smartyTwo | 434.56 | very good | 30 |
| PHP | NULL | smartyThree| 442.56 | very good | 33 |
+-------+-------+-------------+---------+-----------+------+
c.id = 4 是JAVASCRIPT类的
1 | select c. name cname , c.desn cdesn, p. name pname, p.price pprice, p.desn pdesn, p.num pnum |
2 | from |
3 | cats c, products as p |
4 | where |
5 | c.id = p.cid |
6 | and |
7 | c.id = 4; |
c.id = 6 是J2ee类的
1 | select c. name cname , c.desn cdesn, p. name pname, p.price pprice, p.desn pdesn, p.num pnum |
2 | from |
3 | cats c, products as p |
4 | where |
5 | c.id = p.cid |
6 | and |
7 | c.id = 6; |
也可以加其它条件
这叫做等值查询,如果非等值查询,就会出现笛卡尔乘积的查询
连接查询的操作,不仅可以用于多表之间,也可以是一个表与自己进行相链,成为自身的链接查询
查看cats表结构
1 | desc cats; |
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| pid | int(11) | NO | | 0 | |
| name | varchar(60) | NO | MUL| | |
| desn | text | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
cats表本身有id还是一个父pid,就是大类里面存小类,小类里面再存子类,这样一个关系,是无限分类的一种设计的方式
所以我们可以给同一个表起两个别名
1 | select a.id aid, a. name aname, b.id bid, b. name bname |
2 | from |
3 | cats as a, cats b; |
笛卡尔乘积出来了
+-----+------------+-----+------------+
| aid | aname | bid | bname |
+-----+------------+-----+------------+
| 6 | J2ee | 6 | J2ee |
| 7 | J2me | 6 | J2ee |
………………
64 rows in set (0.011 sec)
所以我们一定要加条件
1 | select a.id aid, a. name aname, b.id bid, b. name bname |
2 | from |
3 | cats as a, cats b |
4 | where |
5 | b.pid = a.id |
6 | order by aid asc ; |
+-----+-------+-----+-------------+
| aid | aname| bid | bname |
+-----+-------+-----+-------------+
| 1 | soft | 3 | PHP |
| 1 | soft | 2 | JAVA |
| 1 | soft | 4 | JAVASCRIPT|
| 2 | JAVA | 7 | J2me |
| 2 | JAVA | 5 | J2se |
| 2 | JAVA | 6 | J2ee pid是2 |
| 3 | PHP | 8 | smarty |
+-----+-------+-----+-------------+
可以看到
soft类别里面有javascript、PHP、java
java类别里面有j2se、j2ee、j2me
PHP类别里面有thinkphp、smarty
7、嵌套查询(子查询)
上面的select查询都是单句的查询,在关系型数据中还经常使用到嵌套查询,
嵌套查询是一个select语句中的where字句中,包含了另外一个查询语句,也称为子查询,
执行的时候先执行子查询的条件
比如想查找porducts表中,是以 字母j 开头的所有类别产品
1 | select id, name , cid from products where cid in ( select id from cats where name like 'j%' ); |
+----+-----------+-----+
| id | name | cid |
+----+-----------+-----+
| 8 | jquery | 4 |
| 9 | Vue | 4 |
| 11 | bootStrap | 4 |
| 12 | J2seONE | 5 |
| 13 | J2seTwo | 5 |
| 14 | J2seThree | 5 |
| 16 | J2seFive | 5 |
| 17 | J2seFix | 5 |
| 18 | J2eeOne | 6 |
| 19 | J2eeTwo | 6 |
| 20 | J2eeThree | 6 |
| 21 | J2eeFour | 6 |
| 22 | J2eeFive | 6 |
| 23 | J2meOne | 7 |
| 24 | J2meTwo | 7 |
+----+-----------+-----+
1). 执行的时候先执行子查询,子查询的结果作为父查询的条件,
子查询中还可以再有子查询
2). 这里子查询可能有多个值,所以用in列表
如果子查询中会出现一个值,where条件不仅可以in,还可以用等号、大于、小于号都是可以的
不过一般子查询的结果都是多个结果
8、order by 排序
order by 字段名 [asc正序] | desc倒序
比如产品表里面这么多列,是按什么排序,按前后顺序排序,按name字段排序,按id字段排序等等,这些都不一定
所以在写每一条查询语句的时候,最好给排序的条件
order by 和 where条件同是select查询的从句,所以他们可以同时存在,也可以不同时存在
按id排序,默认就正序排序
1 | select id, name from products order by id; |
+----+-------------+
| id | name |
+----+-------------+
| 1 | javaone |
| 2 | javatwo |
| 3 | javathree |
……
按照name字段排序
1 | select id, name from products order by name ; |
按首字母顺序排序
+----+-------------+
| id | name |
+----+-------------+
| 28 | a |
| 29 | b |
| 11 | bootstrap |
| 30 | c |
| 7 | ecshop |
| 22 | J2eefive |
| 21 | J2eefour |
……
价钱price字段,从低到高排序
1 | select id, name , price from products order by price asc ; |
+----+-------------+---------+
| id | name | price |
+----+-------------+---------+
| 28 | a | 0.00 |
| 30 | c | 0.00 |
| 29 | b | 0.00 |
| 20 | J2eethree | 4.56 |
| 1 | javaone | 34.50 |
| 6 | phpcms | 44.56 |
| 9 | vue | 44.56 |
……
价钱从高到低
1 | select id, name , price from products order by price desc ; |
+----+-------------+---------+
| id | name | price |
+----+-------------+---------+
| 11 | bootstrap | 4444.56 |
| 25 | smartyone | 4432.56 |
| 16 | J2sefive | 4324.56 |
| 12 | J2seONE | 544.56 |
……
正序排序是asc,倒序排序是desc,但正序asc排序是不用写的
排序语句和where语句可以同时使用的
id小于5的倒序排序
1 | select id, name from products where id < 5 order by id desc ; |
+----+-----------+
| id | name |
+----+-----------+
| 4 | javafour |
| 3 | javathree |
| 2 | javatwo |
| 1 | javaone |
+----+-----------+
9、limit count 限制查询的数量
limit也是单独的从句,不一定和where语句和order by一起使用,当然是可以联合使用的,也可能单独使用
查询5条语句
1 | select id, name , price from products limit 5; |
+----+-----------+---------+
| id | name | price |
+----+-----------+---------+
| 28 | a | 0.00 |
| 29 | b | 0.00 |
| 11 | bootStrap | 4444.56 |
| 30 | c | 0.00 |
| 7 | ecshop | 84.56 |
+----+-----------+---------+
limit和排序order by联合使用
1 | select id, name , price from products order by id desc limit 5; |
+----+-------------+--------+
| id | name | price |
+----+-------------+--------+
| 30 | c | 0.00 |
| 29 | b | 0.00 |
| 28 | a | 0.00 |
| 27 | smartythree | 442.56 |
| 26 | smartytwo | 434.56 |
+----+-------------+--------+
也可以加上where条件,id小于10
1 | select id, name , price from products where id < 10 order by id desc limit 5; |
+----+----------+--------+
| id | name | price |
+----+----------+--------+
| 9 | vue | 44.56 |
| 8 | jqurey | 244.56 |
| 7 | ecshop | 84.56 |
| 6 | phpcms | 44.56 |
| 4 | javafour | 144.56 |
+----+----------+--------+
知道当前文章id,怎么知道上一篇文章和下一篇文章呢?
这就需要where, order by, limit 这几个从句的配合了
先删除一些语句
1 | delete from products where id in (11,12,13,9,8,7,6,5); |
2 |
3 | select id, name from products order by id asc ; |
+----+-------------+
| id | name |
+----+-------------+
| 1 | javaOne |
| 2 | javaTwo |
| 3 | javaThree |
| 4 | javaFour |
| 14 | J2seThree |
| 16 | J2seFive |
| 17 | J2seFix |
| 18 | J2eeOne |
| 19 | J2eeTwo |
| 20 | J2eeThree |
| 21 | J2eeFour |
| 22 | J2eeFive |
| 23 | J2meOne |
| 24 | J2meTwo |
| 25 | smartyOne |
| 26 | smartyTwo |
| 27 | smartyThree|
| 28 | a |
| 29 | b |
| 30 | c |
+----+-------------+
比如当前文章的id是14的,查询下一篇
1). where id > 14 id大于14
select id, name from products where id > 14;
2). order by id asc 正序排列
select id, name from products where id > 14 order by id asc;
3). limit 0, 1 取第一条记录
1 | select id, name from products where id > 14 order by id asc limit 0, 1; |
+----+----------+
| id | name |
+----+----------+
| 16 | J2sefive |
+----+----------+
上一篇文章,改成id小于14的,倒序排序
1 | select id, name from products where id < 14 order by id desc limit 0, 1; |
+----+----------+
| id | name |
+----+----------+
| 4 | javafour |
+----+----------+
10、分组 guoup by
先看一些统计函数
count() 总数
sum() 总合
avg() 平均值
max() 最大值
min() 最小值
查询总数
1 | select count (*) from products; |
共有20条数据
+----------+
| count(*) |
+----------+
| 20 |
+----------+
查询价钱总商品数 count(*)
价钱的总和 sum(price)
平均价格 avg(price)
价钱最贵的 max(price)
价钱最便宜的 min(price)
1 | select count (*), sum (price), avg (price), max (price), min (price) from products; |
+----------+------------+------------+------------+------------+
| count(*) | sum(price) | avg(price) | max(price) | min(price) |
+----------+------------+------------+------------+------------+
| 20 | 12584.46 | 629.223000| 4432.56 | 0.00 |
+----------+------------+------------+------------+------------+
按照类别cid分组,统计出每个组
数据总行数 count(*)
价钱的总和 sum(price)
平均价格 avg(price)
价钱最贵的 max(price)
价钱最便宜的 min(price)
1 | select cid, count (*), sum (price), avg (price), max (price), min (price) from products group by cid; |
+-----+----------+------------+-------------+------------+------------+
| cid | count(*) | sum(price) | avg(price) | max(price) | min(price) |
+-----+----------+------------+-------------+------------+------------+
| 0 | 3 | 0.00 | 0.000000 | 0.00 | 0.00 |
| 2 | 4 | 1035.18 | 258.795000| 441.56 | 34.50 |
| 5 | 3 | 4823.68 | 1607.893333| 4324.56 | 44.56 |
| 6 | 5 | 1042.80 | 208.560000 | 494.56 | 4.56 |
| 7 | 2 | 373.12 | 186.560000 | 324.56 | 48.56 |
| 8 | 3 | 5309.68 | 1769.893333| 4432.56 | 434.56 |
+-----+----------+------------+-------------+------------+------------+
group by 字段名
group by 后面加上一个字段,就可以按照哪个字段分组,
如果用户表加上性别字段,性别字段有男和女两种值,那就分成男的一组,女的一组,
统计出每个组的最大值,最小值等等
如果是分组,想给每个组指定一个条件就不能用where了,每个组的条件用having从句
比如每个组评价价格大于500的条件
1 | select cid, count (*), sum (price), avg (price), max (price), min (price) from products group by cid |
2 | having |
3 | avg (price) > 500; |
+-----+----------+------------+-------------+------------+------------+
| cid | count(*) | sum(price) | avg(price) | max(price) | min(price) |
+-----+----------+------------+-------------+------------+------------+
| 5 | 3 | 4823.68 | 1607.893333| 4324.56 | 44.56 |
| 8 | 3 | 5309.68 | 1769.893333| 4432.56 | 434.56 |
+-----+----------+------------+-------------+------------+------------+
having是分组后给的条件,所以having做为group by的从句出现
MySQL中的内置系统函数
用在SELECCT语句, 能及子句 where order by having 中 UPDATE DELETE,
函数中可以将字段名作为变量来用,变量的值就是这个列对应的每一行记录