Dragon's Note
MySQL/MariaDB 快速教程

此笔记使用的版本为 10.6.4-MariaDB

查看完整教程请前往 mariadbtutorial

基础教程

查询 SELECT

简单查询

一个简单的查询如下所示:

SELECT * from countries;

也可以定制输出的样式:

select name,area,national_day
from countries;

还可以直接查询函数:

SELECT now();

排序 order by

使用 order by进行排序

语法如下

select 
    select_list
from 
    table_name
order by
    sort_expression1 [asc | desc],
    sort_expression2 [asc | desc],
    ...;  

例如:

select name,area
from countries order by name;

或者

select name,area
from countries order by name desc ;

默认情况下,不加 desc 为升序 (asc)

条件 where

语法如下:

select
    select_list
from
    table_name
where
    search_condition
order by
    sort_expression;
select name,area,region_id
from countries where region_id =2 order by name ;

# 查询地区编号为2的国家并按照名称升序排序

也可以用各种运算符

select name,area,region_id
from countries where area > 2000000 order by area desc ;

# 查询地区面积大于二百万的国家并按照面积从大到小排序

也可以组合多个条件

select
    name,
    area,
    region_id
from
    countries
where
        region_id = 2 or
        area > 2000000
order by
    area desc ;
# 查询地区面积大于二百万的国家和region的id为2并按照面积从大到小排序

也可查询某一范围值

select
    name,
    area
from
    countries
where
    area between 1001449
        and 10255200
order by
    area;

# 查询面积在 1001449 到 1566500 (包括 1566500)之间的国家

可以通过子查询查找某一范围

select name,
       country_code2
from countries
where country_code2 in ('US', 'FR', 'JP')
order by name;

# 查询国家中代号为 'US' 'FR' 'JP' 的国家

还可以通过 Like 来模糊查询

select name
from countries
where name like 'J%'
order by name;

# 查询名称以 J 开头的国家

去重 distinct

查询一行并去除重复内容,仅保留一个

select distinct national_day
from countries;

请注意,如果您只想选择某些列的不同值,请使用 group by.

批量匹配 in

语法如下:

expression IN (v1, v2, v3, ...)

# 类似于
# expression = v1 or 
# expression = v2 or 
# expression = v3 or

或者

expression in (select-statement)

# 注意 select-statement必须返回只有一列值的列表

例如:

select name,
       region_id
from countries
where region_id in (1, 2, 3)
order by name;

# 查询区域id 为 1、2、3 的国家名称
select name,
       region_id
from countries
where region_id = 1
   or region_id = 2
   or region_id = 3
order by name; 
# 上面代码等价于此

子查询例子如下:

select region_id
from regions
where name like '%Asia%';

select name,
       region_id
from countries
where region_id in (2, 16, 18)
order by name;

# 上面两条可合并成一条语句

select name,
       region_id
from countries
where region_id in (
    select region_id
    from regions
    where name like '%Asia%'
)
order by name;

控制返回数

语法如下

select select_list
from tale_name
order by sort_expression
limit n [offset m];
# n是要返回的行数
# m是在返回之前要跳过的行数 n行。 
# LIMIT m, n;

示例

select name
from countries
order by name
limit 5 offset 1;

# 查询前5个国家并跳过第一个

空检查 is null

select name,
       national_day
from countries
where national_day is null
order by name;

# 查询 national_day 为空的国家

select name,
       national_day
from countries
where national_day is not null
order by name;

# 查询 national_day 不为空的国家

连接查询 join

内连接 inner join

输出匹配规则的列(取交集)

select g.guest_id,
       g.name,
       v.vip_id,
       v.name
from guests g
         inner join vips v
                    on v.name = g.name;

# 输出guests 表下与vips下同名的列字段组合

左连接 left join

left join以左表为基准,匹配右表字段,如果匹配成功,则 left join创建一个新行,其列包含由选择列表指定的两行的列。如果 left join在右表中没有找到任何匹配的行,它仍然创建一个新行,其列包含左表中的行的列和null

例如:

select g.guest_id,
       g.name,
       v.vip_id,
       v.name
from guests g
         left join vips v
                   using (name);

# 从guests表中检索他们是否有来自vips表的匹配行。如果没有则输出null

右连接 right join

left join 类似,只不过以右表为基准

例如:

select g.guest_id,
       g.name,
       v.vip_id,
       v.name
from guests g
         right join vips v
                   using (name);
笛卡尔积 cross join

对连接表中的行进行笛卡尔积处理。

select g.guest_id,
       g.name,
       v.vip_id,
       v.name
from guests g
         cross join vips v;

归组 group by

group by用于将结果的行分组,语法如下:

select
    column1,
    aggregate_function(column2)
from
    table_name
group by
    column1;

group by通常与聚合函数一起使用,包括 count(), min(), max(), sum(), 和 avg() 等等

例如:

select region_id,
       count(country_id) count
from countries
group by region_id
order by region_id;

# 查询此地区下有多少个国家

条件 having

where 子句允许您指定一个条件来过滤返回的select查询。 但是,它不能过滤 group by

想过滤 group by,你需要使用 having

示例:

select regions.name      region,
       count(country_id) country_count
from countries
         inner join regions
                    using (region_id)
group by (regions.name)
having count(region_id) > 10
order by country_count desc;

#使用 having子句查找拥有超过 10 个国家/地区的地区 

复杂子查询 in

子查询是嵌套在另一个查询中的查询。

标量子查询

必须而且也只能返回一行一列的查询结果,返回是一个单一的值。返回的单一的值可以和比较运算符一起使用。在where子句中不能使用汇总函数。

select *
from countries
where area = (
    select max(area)
    from countries
);

#查找面积最大的国家/地区
行子查询

行子查询返回单行。

select name
from country_stats
         inner join countries
                    using (country_id)
where year = 2018
  and (population, gdp) > (
    select avg(population) as a,
           avg(gdp)        as b
    from country_stats
    where year = 2018)
order by name;

FROM 下的子查询

你可以将子查询放在 FROM 后

select avg(region_area)
from (
         select sum(area) region_area
         from countries
         group by region_id
     ) t;

公用表达式 CTE

公用表表达式或 CTE 允许您在查询中创建临时结果集。

CTE 类似于派生表,因为它不存储为数据库对象,并且仅在执行查询期间存在。

与派生表不同,您可以在查询中多次引用 CTE。 此外,您可以在其内部引用 CTE。 此 CTE 称为递归 CTE。

CTE 可用于

  • 在同一语句中多次引用结果集。
  • 替换 视图 以避免创建视图。
  • 创建递归查询
  • 通过将复杂查询分解为多个简单且符合逻辑的构建块来简化它。

语法如下

with cte_name as (
    cte_body
)
cte_usage;

示例

select name,
       gdp
from country_stats
         inner join countries c
                    using (country_id)
where year = 2018
order by gdp desc
limit 10;

# 以下代码等价于上面代码

with d as (
    select country_id, gdp
    from country_stats
    where year = 2018
    order by gdp desc
    limit 10
)
select name, gdp
from countries
         inner join d using (country_id)

# 查询 2018 年排名前10 GDP

组合 union

union运算符组合两个或多个结果集合并为一个结果集。

语法如下 :

select-statement1
union [all | distinct]
select-statement2
union [all | distinct]

distinct选项表示 union运算符从最终结果集中删除重复行,而 all选项保留重复项。

示例:

select name from guests
union distinct
select name from vips
order by name;

union对比 join

join水平追加结果集,而 union垂直追加结果集。

相交 intersect

intersect运算符组合两个或多个结果集并从查询的结果集中返回相同的行。

语法如下:

select-statement1
intersect
select-statement2
intersect 
select-statement3
...
[order by sort_expression];

示例:

select name from guests
    intersect
    select name from vips
    order by name;
# 查询 guests 和 vips 下相同的人

排除 except

except运算符用于去除第一个结果集中其他结果集的数据

语法:

select-statement
except
select-statement;

示例:

select name from guests
    except
select name from vips
order by name;

# 从 guests 结果中排除 vips

插入 insert

简单插入

insert 允许你向表中插入新行。

语法:

insert into table_name(column_list)
values(value_list);

示例:

insert into contacts(first_name, last_name, phone)
values('John','Doe','(408)-934-2443');

你可以通过以下函数拿到插入ID

select last_insert_id();

你可以指定 default 占位符以使用默认值

insert into contacts(first_name, last_name, phone, contact_group)
values('Roberto','carlos','(408)-242-3845',default);

另一种插入方法:

语法如下:

insert into table_name
set column1 = value1,
    column2 = value2;

在此语法中,您不必按顺序排列列和值。

示例:

insert into contacts
set first_name = 'Jonathan',
    last_name = 'Van';	

插入多行语句

语法:

insert into
    table_name(column_list)
values
    (value_list_1),
    (value_list_2),
    (value_list_3),
    ...;

示例:

insert into contacts(first_name, last_name, phone, contact_group)
values
    ('James','Smith','(408)-232-2352','Customers'),
    ('Michael','Smith','(408)-232-6343','Customers'),
    ('Maria','Garcia','(408)-232-3434','Customers');

插入查询结果

语法:

insert into table_name(column_list)
select select_list
from table_name
...;

示例:

insert into small_countries
    (country_id, name, area)
select country_id,
       name,
       area
from countries
where area < 50000;

# 插入面积小于 50,000 平方公里的国家到small_countries表中.

更新 update

update语句允许您修改表中一列或多列的数据。

语法:

update table_name
set column1 = value1,
    column2 = value2,
    ...
[where search_condition];

示例:

update contacts
set last_name = 'Smith'
where id = 1;
#  将ID为1的行的姓氏更改为的语句 'Smith'

不带 where 条件可更新所有数据

update
    contacts
set phone = replace(phone, '-', ' ')

删除 delete

语法:

delete from table_name
[where search_condition];

删除一行:

delete from contacts
where id = 1;

删除全部:

delete from contacts;

数据库操作

创建数据库

语法:

create [or replace] database [if not exists] database_name
[character set = charset_name]
[collate = collation_name];
``

可以使用以下语法删除到创建数据库

```sql
drop database if exists database_name;
create database database_name;

更改数据库

语法

alter database [database_name]
[character set charset_name]
[collate collation_name]

示例:

alter database crm
	character set = 'latin1'
	collate = 'latin1_swedish_ci';

删除数据库

示例:

drop database crm;

数据类型

数字数据类型

数字类型描述
TINYINT一个非常小的整数
SMALLINT一个小整数
MEDIUMINT一个中等大小的整数
INT标准整数
BIGINT一个大整数
DECIMAL一个定点数
FLOAT单精度浮点数
DOUBLE双精度浮点数
BIT一比特

字符串数据类型

字符串类型描述
CHAR固定长度的非二进制(字符)字符串
VARCHAR可变长度的非二进制字符串
BINARY一个固定长度的二进制字符串
VARBINARY可变长度的二进制字符串
TINYBLOB一个非常小的 BLOB(二进制大对象)
BLOB一个小BLOB
MEDIUMBLOB一个中等大小的 BLOB
LONGBLOB一个大BLOB
TINYTEXT一个非常小的非二进制字符串
TEXT一个小的非二进制字符串
MEDIUMTEXT一个中等大小的非二进制字符串
LONGTEXT一个大的非二进制字符串
ENUM枚举
SETSET

时间类型

时态数据类型描述
DATE中的日期值 CCYY-MM-DD格式
TIME时间值在 hh:mm:ss格式
DATETIME中的日期和时间值 CCYY-MM-DD hh:mm:ss格式
TIMESTAMP中的时间戳值 CCYY-MM-DD hh:mm:ss格式
YEAR一年中的价值 CCYY或者 YY格式

空间数据类型

空间数据类型描述
GEOMETRY任何类型的空间值
POINT一个点(一对XY坐标)
LINESTRING曲线(一个或多个 POINT值)
POLYGON一个多边形
GEOMETRYCOLLECTIONGEOMETRY
MULTILINESTRINGLINESTRING
MULTIPOINTPOINT
MULTIPOLYGONPOLYGON

表管理

创建表

基本语法:

create table [if not exists] table_name(
    column_1_definition,
    column_2_definition,
    ...,
    table_constraints
) engine=storage_engine;

列定义语法:

column_name data_type(length) [not null] [default value] [auto_increment] column_constraint;
  • 首先,指定列的名称。
  • 接下来,如果数据类型需要,指定列的数据类型和最大长度。
  • 然后,使用 not null强制列中的非空值。 除了非空约束,您还可以对列使用检查和主键列约束。
  • 之后,使用 default value当插入和更新语句未明确指定时,子句为列指定默认值。
  • 最后,使用 auto_increment属性指示 MariaDB 为列隐式生成连续整数。 一张表只有一列 auto_increment

例如:

create table projects(
    project_id int auto_increment,
    project_name varchar(255) not null,
    begin_date date,
    end_date date,
    cost decimal(15,2) not null,
    created_at timestamp default current_timestamp,
    primary key(project_id)
)

定义外键:

示例:

create table milestones(
    milestone_id int auto_increment,
    project_id int,
    milestone varchar(255) not null,
    start_date date not null,
    end_date date not null,
    completed bool default false,
    primary key(milestone_id, project_id),
    foreign key(project_id)
        references projects(project_id)
);

使用以下语句进行关联:

foreign key(project_id)
    references projects(project_id)

更改表

添加列

语法:

alter table table_name
add 
    new_column_name column_definition
    [first | after column_name]

示例:

alter table customers
add email varchar(255) not null;
# 在customers后添加一行email

向表中添加多列

语法:

alter table table_name
    add new_column_name column_definition
    [first | after column_name],
    add new_column_name column_definition
    [first | after column_name],
    ...;

示例:

alter table customers
add phone varchar(15),
add address varchar(255);

修改列

语法:

alter table table_name
modify column_name column_definition
[ first | after column_name];    

示例:

alter table customers 
modify phone varchar(20) not null;

修改多列

语法:

alter table table_name
    modify column_name column_definition
    [ first | after column_name],
    modify column_name column_definition
    [ first | after column_name],
    ...;

示例:

alter table customers 
modify email varchar(255),
modify address varchar(255) after name;

重命名列

语法:

alter table table_name
change column original_name new_name column_definition
[first | after column_name];

示例:

alter table customers
change column address office_address varchar(255) not null;

删除列

语法:

alter table table_name
drop column column_name;  

示例:

alter table customers
drop column office_address;

重命名表

语法:

alter table table_name
rename to new_table_name;

示例:

alter table customers 
rename to clients; 

删除表

语法:

drop table [if exists] table_name;

一次删除多个:

drop table [if exists]
    table1,
    table2,
       ...;

查看所有表

语法:

show full tables;

可以附加条件

例如:

show full tables
like 'country%'

或者:

show full tables
where table_type = 'view';

清空表

语法:

truncate [table] table_name;

表约束

主键约束

定义主键

语法:

create table table_name(
    pk_column type primary key,
    ...
);

如果有多个主键则使用如下定义

create table table_name(
    pk_column1 type,
    pk_column2 type,
    ...
    primary key(pk_column1,pk_column2,)
);
添加主键

语法:

alter table table_name
add constraint constraint_name
primary key (column_list);
删除主键
alter table table_name
drop primary key;
定义自增主键

例如:

create table categories(
    category_id int auto_increment,
    name varchar(50) not null,
    primary key(category_id)
);

外键约束

外键是表中的一列或一组列,它引用另一个表中的一列或一组列,它强制执行两个表之间的参照完整性。 具有外键的表称为子表,而外键引用的表称为父表。通常,子表中的外键列引用父表的主键列。

创建外键
create table table_name(
    column_list,
    ...,
    [constraint constraint_name]
	foreign key [fk_name](column_list) references parent_table(column_list)
	[on delete reference_option]
	[on update reference_option]
);

示例:

create table gadgets
(
    gadget_id   int auto_increment,
    gadget_name varchar(100) not null,
    type_id     int,
    primary key (gadget_id),
    constraint fk_type
        foreign key (type_id)
            references gadget_types (type_id)
);
添加外键
alter table table_name
[constraint fk_constraint_name]
foreign key [fk_name](column_list) references parent_table(column_list)
[on delete reference_option]
[on update reference_option]

示例:

alter table gadgets
    add constraint fk_type
        foreign key (type_id)
            references gadget_types (type_id)
            on delete cascade
            on update cascade;
删除外键
alter table table_name
drop constraint fk_constraint_name;

示例:

alter table gadgets
drop constraint fk_type;

检查约束

示例:

create table classes(
    class_id int auto_increment,
    class_name varchar(255) not null,
    student_count int check(student_count >0),
    primary key(class_id)
);

语法:

column_name datatype check(expression)

向现有的表添加检查约束:

语法:

alter table table_name
add constraint constraint_name 
check(expression);

示例:

alter table classes
add constraint valid_begin_date 
check(begin_date >= '2019-01-01');

删除检查约束

语法:

alter table table_name
drop constraint constraint_name;

示例:

alter table classes
drop constraint valid_begin_date;

唯一约束

有时,您可能希望确保一列或一组列中的值是唯一的,例如,国家/地区的国家/地区代码、客户的电子邮件地址等。这时你就可以使用唯一约束.

语法:

create table table_name(
    ...,
    column_name datatype unique,
    ...
);

或者使用如下语法:

create table table_name(
    ...
    column1 datatype,
    column2 datatype,
    ...,
    unique(column1,column2,...)
);

你也可以对现有的表添加约束

alter table table_name
add constraint constraint_name 
unique (column_list);

删除约束

drop index_name on table_name;

或者

alter table table_name
drop index index_name;

非空约束

语法:

column_name datatype not null;

示例:

create table courses(
    course_id int auto_increment,
    course_name varchar(100) not null,
    summary varchar(255),
    primary key(course_id)
);

更新表约束

alter table courses
modify summary varchar(255) not null;

注意,你需要先将列中 null数据更新为非 null数据

例如:

update courses
set summary = 'N/A'
where summary is null;

删除约束

alter table table_name
modify column_name datatype;