MySQL 基础语法

MySQL 基础语法

基础数据类型

MySQL 文件支持多种类型，大致可分为三类：数值，日期/时间和字符串(字符)类型。

类型	用途
int	用于储存整型变量
bigint	整型，相当于 Java 中的 long
double	浮点型
decimal(size,d) / numeric(size,d)	容纳带小数的数字，size 规定数字最大位数，d规定小数点右侧的最大位数
date(yyyymmdd)	容纳日期
datetime(yyyy-mm-dd hh:mm:ss)	日期类型
timestamp	日期类型，可容纳时间戳
char	定长字符
varchar	不定长字符
text	大文本，用于储存字符很长的内容

建表操作

首先创建或选择数据集：

create database student;
use student;

之后可以新建表：

create table 表名 (
    字段名 类型 约束(主键，非空，默认，唯一值),
    字段名 类型 约束(主键，非空，默认，唯一值)
)编码，存储引擎;

例如：

create table warehouse (
    w_id smallint,
    w_city varchar(20),
    w_state char(2),
    w_ytd dec(12, 2)
);
create table websites (
  id int(11) not null auto_increment,
  name char(20) not null  default '' comment '站点名称',
  url varchar(255) not null default '',
  alexa int(11) not null default '0' comment 'alexa 排名',
  sale double comment '广告收入',
  country char(10) not null default '' comment '国家',
  primary key (id)
)default charset = utf8 ;

在 SQL 中，我们又如下约束：

• NOT NULL：指定某列不能存储 NULL 值。
• UNIQUE： 指定某列的每行必须有唯一值。
• PRIMARY KEY：上述两者的结合，确保某列有唯一标识，有助于更容易快速找到表中的一个既定约束。
• FOREIGN KEY：保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK：保证列中的值符合指定的条件。
• DEFAULT：规定没有给列赋值时的默认值

删除表：

DROP TABLE IF EXISTS 表名;

插入，删除与更新

• 插入语句

对插入来说，可以直接指定表名称后填写数据，也可以对指定的列填写：

insert into table_name values (val1, val2, ...);
insert into table_name(row1, row2, ... rown) values (val1, val2, ... valn);

举例：

insert into websites(name, url, alexa, sale, country) values ('腾讯','https://www.qq.com',18,1000,'CN');
insert into websites values (4,'腾讯66','https://www.qq.com',18,1000,'CN');

• 删除语句

delete from websites where id = 5;

• 更新语句

update websites set sale = 5 where id = 3;

基本 SELECT 查询语句

select * from websites;
select id, name from websites;     -- 更推荐

分页查询

是较为优雅的查询方式，可以人为规定查询的数量

select * from websites limits 2;        -- 从第0条开始查，一共查看2条数据
select * from websites limits 1,2;	    -- 从第1条开始查，一共查看2条数据

distinct 关键字

查询时去除重复的字段

select distinct country from websites;

where 语句

当我们想要查询的内容具有某些条件时，可以使用 where 来进行筛选。值得一提的是，在 MySQL 语言中，判等是一个等号。

 select * from websites where sale > 50;

当然如果不止一个条件的时候，我们也能够用 and 或者 or 来连接多个条件：

select * from websites where sale > 20 and sale < 100;      -- 20 <  sale <  100
select * from websites where sale between 20 and 100;       -- 20 <= sale <= 100
select * from websutes where sale > 5 or sale is null;

需要注意的点：

• 在 MySQL 语法中， null = null 恒不成立，因此想判空需要写成 is null 或者 is not null。
• 判断是否相等只有一个等号，写作 = 即可。
• 如果使用 between 语法，此时为闭区间。

order 语句

在筛选的同时我们可以让输出结果为升序(asc)或者倒序(desc)。

select * from websites order by sale asc, alexa desc;   -- 先按照 sale 升序排列，再按照第二关键字 alax 降序排列

like 与通配符

like 可用来查询匹配字符串，其中 % 代表零个或多个字符，而 _ 代表只有一个字符。

select * from websites where name like '_o%';    -- 第二个字符为 o

in 多条件查询

将满足条件的多项目均查出来：

select * from websites where country in ('CN','USA','UK');

Group 组查询

如果此时我们想通过某一列的信息进行筛选，最后显示某一列值相同的组下的结果时，可以使用 group by：

select avg(sale) 'avg', country from websites where sale is not null group by country having avg > 1500;

下面对上述语句进行剖析：

1. 首先应用 group by country 意味着将原数据首先按照不同国家进行分组，得到一系列的组数据。
2. 对每一组数据进行筛选，剔除掉那些 sale 为 null 的无效数据。
3. 将剩余的数据运算求出平均值 avg(sale) ，并赋予一个新的名字(别名) 为 avg。
4. 在显示的时候，由于 having avg > 1500 因此只显示运算结果大于 1500 的国家
5. 显示格式为每条数据两列，第一列为 avg 结果，第二列为国家 country。

值得提醒的点：

• 常见的运算有： avg() 求平均， sum() 求和，max() 最大值，min() 最小值和 count() 求数量。
• 分组筛选的时候要用关键词 having 而不是 where。

连接查询

当查询涉及到多个表之间的数据交互时，需要用到连接查询。

当谈到多表时，首先想到的即是笛卡尔积。我们可以通过对两个表进行笛卡尔积来构造一个新的表，再通过对这个表的查询实现多表的操作。详情可见链接。

内连接查询

内连接是等值查询，它使用 =、>、<、<> 等运算符根据每个表共有的列的值匹配两个表中的行。

写作：

--查询每个学生的学号、姓名、籍贯、年龄、专业、班级
--涉及到student和major两张表，用共有字段“学号”为连接字段

--写法1：使用INNER JOIN
select A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
from student A
inner join major B   
on  A.学号=B.学号

--写法2：--省去了INNER，直接写JOIN，与INNER JOIN没有区别(推荐)
select A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
from student A
join major B  
on  A.学号=B.学号

--写法3： --使用WHERE，已经逐渐被淘汰
select A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
from student A, major B
where A.学号=B.学号

此时将会只匹配两个表中均出现的字段。其一般形式为：

select <属性或表达式列表>
from <表名> [inner] join <表名>
on <连接条件>
[where <限定条件>]

在内连接中特殊情况是自身连接(自连接)，即自己与自己连接。由于此时全部属性名都是同名属性，因此必须有别名前缀加以区分。

外连接

普通连接操作只输出满足连接条件的元组；外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。

\[\begin{cases} left \ \ outer \ \ join \ 或 \ left \ \ join \rightarrow 获取左表全部记录\\ right \ \ outer \ \ join \ 或 \ right \ \ join \rightarrow 获取右表全部记录 \\ full \ \ outer \ \ join \ 或 \ full \ \ join \rightarrow 获取全部记录 \\ \end{cases} \]

例如：

--左连接：显示左表student所有记录，如右表中没有与之
--匹配的项则以NULL值代替。
select A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
from student A left join major B  
on  A.学号=B.学号

--右连接：显示右表major所有记录，如左表中没有与之
--匹配的项则以NULL值代替。
select A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
from student A right join major B  
on  A.学号=B.学号

--完全连接：显示两张表的并集，如果其中一张表的记录
--在另一张表中没有匹配的行，则对应的数据项填充NULL
select A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
from student A full join major B  
on  A.学号=B.学号

交叉连接

即最基础的笛卡尔积操作：

--交叉连接：一张表中的数据依次取出分别与另一张表中的
--每条数据挨个组合，最后记录数量为两张表记录数的乘积
select * from student cross join major

--本例student和major都为7条记录，则结果为7*7=49条记录

延伸连接

不难发现，在上述的外连接中，如果一个表有值而另外一个表没有匹配项时，此时会自动填充 null 项，那我们就可以利用这一点，来实现对表求差的运算，称之为延伸连接。

--左表唯一：将右表B以及两张表交集的部分过滤掉，
--得到的记录是左表中唯一存在的。
select A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
from student A left join major B  
on  A.学号=B.学号
where B.学号 is NULL

--右表唯一：将左表A以及两张表交集的部分过滤掉，
--得到的记录是右表中唯一存在的。
select A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
from student A right join major B  
on  A.学号=B.学号
where A.学号 is NULL

--非交集连接：查找两张表中没有关联的记录项。
select A.学号, A.姓名, A.籍贯, A.年龄, B.专业, B.班级
from student A full join major B  
on  A.学号=B.学号
where A.学号 is NULL or B.学号 is NULL

子查询

一个 select-from-where 语句称为一个查询块。

通常将一个查询块嵌套在另一个查询块的 where 子句或 having 短语的条件中（称为嵌套查询），此时称其为子查询。

子查询分类：

• 相关子查询：子查询的查询条件依赖于父查询
• 不相关子查询：由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

此时的谓词可以用 in, any，all ，exist 以及 not exist 来连接。

集合查询

可以用 union 连接两个查询语句以实现集合的并集运算。如：查询计算机科学系的学生及年龄不大于19岁的学生。

select *
from Student
where Sdept= 'CS'
union
select *
from Student
where Sage<=19；

此时求并集的两个集合元组的属性需要相同。