# 基础SQL语句总结 ## 测试环境 + Deepin 15.6 x64 桌面版 + CPU: Intel Core i5-3210M 2.5GHz + RAM: 8GB + DB: Mariadb 10.1.24

用户权限管理

创建用户

-- create
-- 创建用户dev, 密码为dev
-- %表示支持远程登录，如果设置为localhost，则只支持本地登录
create user 'dev'@'%' identified by 'dev';

授权

-- grant
-- 为用户授权，该用户可以操作名为mydb的数据库
grant all on mydb.* to 'dev'@'%';

flush privileges;

取消授权

-- revoke
-- 取消授权
revoke all on mydb.* from 'dev'@'%';

-- 刷新权限，确保grant/revoke生效
flush privileges;

创建表

创建表

-- decimal(6,2) 表示整数位4，小数位2
-- 判断不存在以后创建：create table if not exists mytest(...);
-- char(n)/varchar(n)中的n表示字符数，区别于字节数
create table mytest(
    id int primary key auto_increment,
    name varchar(16) not null default "test",
    uid int,
    xid int references mytt(id)
);

查看表

-- 查看表结构
desc mytest;

-- 查看表的创建语句
show create table mytest;

复制表（包括数据）

create table mytest_copy as
select * from mytest;

删除表（包括数据）

-- drop
-- drop table if exists mytest;
drop table mytest;

修改表

添加字段

-- alter
-- 添加列
alter table mytest
add mytest_name char(16);

alter table mytest
add mytest_name char(16),
add mytest_gender int;

删除字段

-- 删除列
alter table mytest
drop column mytest_name;

添加外键

alter table mytest
add constraint fk_mytest_mytt
foreign key (uid) references mytt(id);

删除外键

alter table mytest
drop foreign key
fk_mytest_mytt;

添加主键

alter table mytest
add constraint
primary key (id);

删除主键

alter table mytest
drop primary key;

重命名表

-- 修改表名不影响原有的外键关系，外键约束会自动对应到新的表名
rename table mytest to mytt;

CURD操作

INSERT

基本插入

-- create table test(
--   id int,
--   name varchar(16),
--   content varchar(32)
-- );

-- 插入一条
insert into test(id, name, content)
values(1, "me", "hello");

-- 插入多条
insert into test(id, name, content)
values
(1, "me", "hello"),
(2, "you", "world");

插入查询结果集

-- 插入select检索出的数据
insert into test(name, content)
select tc_name, tc_content from test_copy;

DELETE

条件删除

-- 删除id为1的数据
delete from mytest
where id = 1;

清空表数据

-- 删除表中的数据，不删除表本身
delete from mytest;

-- 删除表中数据的另一种方式
truncate mytest;

-- truncate实际是drop and re-create
-- 具体区别可查阅参考资料章节中的“MySQL8.0官方文档：truncate-table说明”，目前资料中的文档是基于MySQL8.0版本

UPDATE

更新特定行

update test
set name = "admin",
content = "hello"
where id = 1;

更新所有行

update test
set name = "admin";

将某一列的值置为NULL

update test
set name = null
where id = 2;

SELECT

简单查询

select * from test;

别名

select id as myid, name as myname
from test as mytest
where mytest.id = 1;

去重

-- 检索某一列的不同值
select distinct name
from test;

-- 注意：distinct作用于所有的列，而不仅仅是跟在其后的那一列。即，除非name,content的重复情况一样，否则可能会将test表的所有行检索出来
select distinct name, content
from test;

限制返回结果数

-- 限制返回结果数，只返回前5条记录
select name
from test
limit 5;

-- 从第二条记录开始，返回后面的三条记录，不包括第二条记录
select name
from test
limit 2, 3;

select name
from test
limit 3 offset 2;

排序

-- 降续desc，升序asc
-- 只有当age相同时，才会通过grade排序，对于grade和name同理
-- order by子句要位于sql语句的最后
select name, gender, tel
from people
order by age, grade, 1 desc;

条件查询

-- =, <>, !=, <, >, <=, >=
select name
from people
where id < 5;

select name
from people
where content is null;

-- 包括1和10
select name
from people
where id between 1 and 10;

-- in, not in, not between, not exists
select name
from people
where name in ('Taylor', 'Swift');

组合条件查询

select name, tel
from people
where (gender = 'F' and age < 26) or country = "China";

通配符查询

-- _匹配一个字符，%匹配0-n个字符，但是_和%匹配的列都不包括null
select name
from people
where name like '_y s%';

字段拼接

-- name、gender和age分别为people表的字段
select concat(name, ' (', gender, ')') as beauty
from people
where age < 26;

数值计算

-- +, -, *, /
-- select 3*6 as t;
select prod_name, prod_price, prod_quantity, prod_price*prod_quantity as prod_total
from orders
where order_id = 1;

常用函数

-- 时间日期类： curdate(), curtime(), now()
-- 字符串类： upper("abc"), lower("ABC"), substring("admin", 2, 1)=>d
-- 数值计算： sqrt()

聚集函数

-- avg(), count(), max(), min(), sum()

select avg(price) as avg_price,
max(price) as max_price,
min(price) as min_price
from products;

统计行数

-- count(*)包含所有行，即使某行某列为null
select count(*)
from people;

-- 不统计值为null的列
select count(name)
from people;

distinct与聚集函数的结合

-- distinct不能用于count(*)，可用于count(distinct column_name)
select avg(distinct prod_price) as avg_price
from products
where prod_id = 1;

分组

select country, count(*) as country_count
from people
group by country;

分组过滤

select vend_id, count(*) as num_prods
from products
where prod_price >= 4
group by vend_id
having count(*) >= 2
order by num_prods;

select子句基本顺序

-- select子句顺序:
-- select
-- from
-- where
-- group by
-- having
-- order by

子查询

select cust_name, cust_contact
from customers
where cust_id in (
    select cust_id
    from orders
    where order_num in (
        select order_num
        from orderitems
        where prod_id = 'RG001'
    )
);

select cust_name, (select count(*)
                    from orders
                    where orders.cust_id = customers.cust_id) as orders
from customers
order by cust_name;

联结查询

内联结

-- 内联结，等值联结
-- 本文建立的每一个内联结都是自然联结
-- 没有where条件得到的联结查询结果为笛卡尔积，是多个表行数的乘积，也称叉联结
select vend_name, prod_name
from vendors, products
where vendors.vend_id = products.vend_id;

select vend_name, prod_name
from vendors inner join products
on vendors.vend_id = products.vend_id;

select customers.cust_id, orders.order_num
from customers inner join orders
on customers.cust_id = orders.cust_id;

自联结

-- 查询和Jim同一公司的其他人员信息
select c1.cust_id, c1.cust_name, c1.cust_contact
from customers as c1, customers as c2
where c1.cust_company = c2.cust_company
and c2.cust_name = 'Jim Jones';

左联结（外联结）

-- 左联结，包括左表的所有行
select customers.cust_id, orders.order_num
from customers left outer join orders
on customers.cust_id = orders.cust_id;

右联结（外联结）

select customers.cust_id, orders.order_num
from customers right outer join orders
on orders.cust_id = customers.cust_id;

带聚集函数的联结

select customers.cust_id, count(orders.order_num) as num_ord
from customers left outer join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

组合查询

-- union默认去掉重复的行，可以通过union all显示全部行
select cust_name, cust_contact, cust_email
from customers
where cust_state in ('IL', 'IN', 'MI')
union
select cust_name, cust_contact, cust_email
from customers
where cust_name = 'Fun4All'
order by cust_name, cust_contact;

参考资料

• MySQL8.0官方文档：truncate-table说明 (opens new window)
• 《SQL必知必会》
• 《MySQL必知必会》