数据过滤

  • != 和 <> 作用相同
  • BETWEEN操作符：where id BETWEEN 1001 AND 1009;
  • AND优先级比OR高，多条件并列时最好用圆括号控制
  • IN操作符： where id IN (1002, 1003);
  • NOT操作符：支持对IN、BETWEEN、和EXISTS取反
  • LIKE操作符：
    • where name LIKE ‘张%’; # 以“张”开头
    • where name LIKE ‘%张%’; # 包含“张”字符
    • where name LIKE ‘张%强’; # 以‘“张”开头，以“强”结尾




数据汇总（聚集）

AVG() 函数


select AVG(price) as avg_price from products where id=’1001’;

avg_price 是别名，搜索时AVG函数会忽略值为NULL的行，如果想获得多个列的平均值就要用多个AVG函数。用此函数的时候可能会用到group by先分组，同时用到having来过滤。


还有COUNT() 、SUM() 、 MAX() 、MIN() ，规则类似。


注意不能用 max(sum(score)) 的复合形式，如果想找总和中的最大者，可以用 sum(score)>all(...select语句...)




数据排序

ORDER BY 列1名 ASC, 列2名 DESC LIMIT m, n;

  • 列1名 ASC, 列2名 DESC ：先按列1升序，再按列2降序。
  • LIMIT m,n：从第m+1开始的n条数据




数据分组

HAVING ：实现的是对分组的过滤，一般与聚集函数连用；WHERE是对行的过滤，与聚集函数无关


GROUP BY ：分组去重。group by候面不能跟where，where后面能跟group by

DISTINCT：分组去重。如果有多个NULL，仅保留一个。

二者效率：

  • 有索引的情况下：效率差不多。
  • 无索引的情况下：distinct效率更高。原因是二者都会进行分组操作，但group by可能会进行排序，触发filesort，导致执行效率低。

易用性：

  • distinct关键字会对后面跟的所有字段生效；
  • group by更灵活，能利用having等聚集函数对分组进行过滤。




子查询

把一条select语句返回的结果用于另一条select语句的where子句




联结表

select name, age, score
from student, exam
where student.id = exam.sid

-- 使用表别名，结果等同于上面
select name, age, score
from student as p1, exam as p2
where p1.id = p2 .sid

上面的语句查了三列数据，前两列属于student表，第三列属于exam表。这种需求需要在where语句中把两个表联结起来，用的就是等值联结，它基于两个表做相等测试。这也叫做内部联结，等价于下面的写法：

select name, age, score
from student inner join exam
on student.id = exam.sid

在使用这种语法时，联结条件用特定的on子句而不再用where，但传递的条件是一致的。

如果不进行联结，返回的结果就是笛卡儿积，检索出的行数等于两表行数的乘积，其中很多条数据都是数据库中不存在的。


关于内联结和外联结：

内连接分为等值连接和自然连接：

  • 等值连接：取两者笛卡尔积中属性值相等的元组
  • 自然连接是一种特殊的等值连接。它要求去除重复列。

外连接分为左连接、右连接和全连接：

  • 左连接：查询A表中所有数据以及B表中符合联结条件的数据
  • 右连接：查询B表中所有数据以及A表中符合联结条件的数据
  • 全连接：返回左右表的所有行。哪个表中没有的就用null填充。（MySQL不支持全外连接，所以只能采取关键字UNION来联合左、右连接）




建表语句

create table student(
	id int not null auto_increment,
	name char(10) not null,
	age int not null default 18,
	remark varchar(50) null,
	primary key(id)
	-- primary key(id,name)    -- 多列组成的主键，而非多列都做为索引
    
    -- -- 把course表中的id作为外键，在此表中别名为cid
    -- cid int not null,
	-- foreign key(cid) references course(id)  
);

auto_increment告诉MySQL，本列每当增加一行时自动增量。每个表只允许一个auto_increment列，而且它必须被索引（如，通过使它成为主键），可使用select last_insert_id()获取刚刚自增的值。

当然，在insert的时候可以手动指定一个值，只要它是唯一的（至今尚未被使用过）即可，该值将被用来替代自动生成的值，且后续将基于该手动插入的值继续增长。




插入语句

insert into 表名 (列1名,列2名,列3名)  values(列1数据,列2数据,列3数据), (列1数据,列2数据,列3数据)

其中列名是可以省略书写的，后面单个的value中就不用写对应的数据。

可省略的列名：可为空的列、具有default值的列、自动递增的列


也可以直接不写整个列名列表，直接写values，那么每个value中的数据个数要严格等于数据表的列数（包括上面提到的三种特殊的列）。