SQL基础
DMBS和DB
- DB(Database):数据库
- DBMS(Database Management System):数据库管理系统
1,HDB(Hierarchical Database):层次数据库,通过树形结构表现
2,RDB(Relational Database):关系数据库,通过行和列的二维关系表表现,同时使用专门的SQL对数据进行操作,RDBMS是管理关系数据库的,笔记哦啊有代表性的MySQL、Oracle、SQL Server、PostgreSQL,都是RDBMS
3,OODB(Object Oriented Database):面向对象数据库,数据和操作是以对象为单位的
4,XMLDB(XMLDatabase):XML数据库,可以对XML形式的数据进行处理
5,KVS(Key-Value Store): 键值存储系统
关系型数据库与SQL
关系型数据库以行列组成的二维关系表维护数据,行也称为记录,列也称为字段,关系型数据库以行为单位读写数据,行与列交汇的地方称为单元格,一个单元格只能存储一个数据
SQL:国际标准化组织为SQL制定了专门的标准,以此为基础的SQL称为标准SQL,但是不同RDMS没有完全遵从这一标准,在不同RDBMS中写SQL可能会有小差异
SQL种类:
- DDL(Data Defination Language):数据定义语言,用来创建、删除、修改数据库,create、drop、alter分别用于创建删除和修改表或数据库
- DML(Data Manipulation Language):数据操纵语言,select、insert、update、delete用于对表中记录的增删改查,工作中绝大多数需要使用的是DML语言
- DCL(Data Conrol Language):数据控制语言,commit、rollback指令用于确定或取消对数据的变更,grant、revoke用于赋予或取消用户的操作权限
SQL书写基本规则
1,SQL在RDBMS中是逐条执行的,每条语句以**分号(;)**结尾
2,SQL不区分关键字大小写(SELECT与select相同),但是存储在数据库中的数据是区分大小写的
3,字符串和日期需要用单引号括起来(‘张三’ , ‘2020-01-01’),数字不需要
表与数据库创建
create database <数据库名称>;
create table <表名称>
(
<列名1> <数据类型> <列约束>,
<列名2> <数据类型> <列约束>,
...
<列名n> <数据类型> <列约束>
);
命名:只能以英文字母,数字,下划线(_)作为数据库、表、列名称,同一数据库表名不能相同,同一表列名不能相同
数据类型指定:每一列必须指定数据类型,制定后该列不能存储其它数据类型的数据
表删除和更新
删除
drop table <表名>;
更新
alter table <表名> add column <列定义>;
alter table <表名> drop column <列名>;
alter table <表名> drop column (<列名1>,<列名2>...);
<注意>:表修改后无法恢复,必须先仔细确认
SQL查询基础
product表
create table product (
product_id char(4) primary key not null,
product_name varchar(100) not null,
product_type varchar(32) not null,
sale_price integer,
purchase_price integer,
regist_date DATE
);
insert into PRODUCT
values ('0001', '衬衫', '衣服', 1000, 500, '2009-9-20');
insert into PRODUCT
values ('0002', '打孔器', '办公用品', 500, 320, '2009-9-11');
insert into PRODUCT
values ('0003', '运动T恤', '衣服', 4000, 2800);
insert into PRODUCT
values ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-9-20');
insert into PRODUCT
values ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-1-15');
insert into PRODUCT
values ('0006', '叉子', '厨房用具', 500, null, '2009-9-20');
insert into PRODUCT
values ('0007', '擦菜板', '厨房用具', 880, 790, '2008-4-28');
insert into PRODUCT
values ('0008', '圆珠笔', '办公用品', 100, null, '2009-11-11');
语句和子句:
select * from product where product_name = '衬衫';
select语句:是一个完整的从表中取出数据的语句
子句:多个子句构成一个语句,如上语句包括三个子句
select *
是select子句from product
是from子句where product_name = '衬衫'
是where子
查询结果
查询结果中列的顺序与select子句中的列顺序是相同的,使用*
时列顺序与创建表的列顺序相同
别名
select <列名> as <别名> from <表名>;
select produt_name as name from product;
<注意>:在as后使用中文别名时用双引号括起来,并且可以使用空格(不推荐)
select produt_name as "商品名称" from product;
where子句
where子句用来指定查询数据的条件
select <列名1>,<列名2> ... from <表名> where <条件表达式>;
select product_name , sale_price from product where product_type = '办公用品';
执行顺序:
1,from子句:使用product表
2,where子句:从product表中筛选符合条件的行(记录)
3,select子句:从筛选出的行中,选择select字句中指定的列对应的数据
where子句必须紧跟在from子句之后
<from子句的必要性>:一般开发工作中的from子句是必要的,少数情况下可以没有from子句,比如常数查询
select 100 * 3 as "价格" ;
此时from子句不是必须的
运算符
1,算术运算符:+、-、*、/ 四则运算,可以在select子句中使用
select sale_price * 2 as sale_price_x2 from product;
运算是以行为单位的,每一行sale_price都会乘2
<注意null>:算术运算符可以与null做运算,但是所得结果都将是null,null / 0
写法上不会出错,但是结果依然是null
2,比较运算符:=,<>,<,<=,>,>=,用于where子句中条件筛选,可以和算术运算符搭配使用
<注意>:!=
在有些RDBMS可以使用,但不被SQL标准承认(在Mybatis的XML文件中不能用<>
需要使用!=
)
select * from product where sale_price + 100 > 2000;
<注意null>:null与任何比较运算符比较都没有结果,SQL不能识别=null
或<>null,
需要用is not null
或is null
判断是否与null相等
3,字符串比较
示例:‘101’,‘2021’,‘231’,‘231’
与数字的比较大小不同
获取小于221
的字符串:101
,2021
,比较时先看第一位,小于2
的则比221小,否则比221大,当第一位相同时,则比较两个字符串的第二位,以此类推
4,逻辑运算符:not、and、or
not:表示否定,不能单独使用,需要和其他的条件查询一起使用
select * from product where not sale_price > 500;
select * from product where sale_price <= 500;
这两条语句是等价的,结果相同
and:表示同时满足
or:表示满足任意一个
去重
1,distinct
关键字:需要紧跟在select之后
select distinct product_type from product;
会有三条记录,分别是衣服,办公用品,厨房用具
<注意>:如果distinct后有多个字段,那么所有字段值都相同的记录将会被去重
select distinct product_type ,product_name from product;
这里在类型域名城都相同的情况下才会被去重,此时会有8条记录!!
2,group by去重
select product_typw from product group by product_type;
也可以达到去重的效果,后面会说明用法
聚合与排序
聚合函数:对多行统计汇总为一行输出
常用聚合函数:max、min、avg(求平均值)、count、sum
<注意>:聚合函数中null都不会算作其中!(count函数极端情况下比较特殊)
举几个例子
1,count:对指定列统计个数
select count(purchase_price) from product ;
结果是6,此时purchase_price为null的记录并没有被统计
count函数参数列不同,结果是不同的
create table null_tb(
col varchar(5)
);
insert into null_tb values (null),(null),(null);
此时表中有三条记录全部为null
select count(*), count(col) from null_tb;
查询的结果分别是3和0
2,avg:对指定列求平均值
select avg(purchase_price) from product;
(500+320+2800+2800+5000+790)/ 6 = 2035!purchase_price为null的没有被算进去!
3,distinct与count混用
select distinct count(sale_price) from product;
select count(distinct sale_price) from product;
结果分别是8和7
分组group by
select <字段名1>,<字段名2> from product group by <字段名1>,<字段名2>...;
<注意>:在标准SQL中,使用group by子句时,select 子句中只能使用,常数、聚合函数、group by子句中指定的列名不然会报错,但是Mysql中select子句可以出现未指定的列名,其他DBMS均不支持
-- 错误示例
select product_name ,product_type from product group by product_type;
-- [42803] ERROR: column "product.product_name" must appear in the GROUP BY clause or be -- used in an aggregate function 位置:8
正确示例
select product_type from product group by product_type;
结果为:衣服,办公用品,厨房用具,共三条记录
select product_name ,product_type from product group by product_name,product_type;
结果有8条记录
<注意>:当group by后多个列名时,这些列名对应的值全部相同的记录才会被算作一组
select purchase_price from product group by purchase_price;
<注意>:该字段中有null时,所有字段值为null的会被单独分为一组
含有where子句时
书写顺序
select -> from -> where -> group by
执行顺序
from -> where -> group by -> select
对分组后的结果统计
select count(*) from product group by product_type;
结果:2,2,4
<注意>:根据执行顺序可以看出,是先分组,在进行统计的,group by 后数据被分为三组,分别进行统计,在没有group by子句时,相当于把所有数据当成一组
别名
在group by子句中不应该使用select字句中指定的别名;尽管在MySQL和PostgreSQL不出错
原因是因为select子句在group by子句之后执行,在这里指定别名,找不到对应的列
HAVING
having可以为分组后的结果指定筛选条件,与where指定条件的区别在于:where是对于行指定条件,而having是对分组指定条件
select <列名1>,<列名2>... from <表名> group by <列名1>,<列名2>... having <分组结果制定的条件>;
示例
筛选出商品数量等于2的商品种类
select product_type,count(*) from product
group by product_name,product_type
having count(*) = 2;
结果:衣服,2
,办公用品,2
having中可以使用的要素:常数,聚合函数,group by中指定的列名
having不使用这些要素时会报错
select product_type from product group by product_type having product_id = '0001';
ERROR: column "product.product_id" must appear in the GROUP BY clause or be used in an aggregate function
排序order by
语法
select <列名1>,<列名2>,<列名3>... from <表名> order by <排序基准列1>,<排序基准列2>...;
排序规则:asc升序,desc降序,不写默认为asc升序
示例
按照出售价格排序
select * from product order by sale_price;
多个排序基准列
select * from product order by sale_price,product_id;
意思是先根据出售价格排序,如果价格相同,再按照id排序
不同排序基准列制定不同排序规则
select * from product order by sale_price desc ,product_id asc ;
意思是先根据超售价格降序排列,出售价格相同时,按照id升序排列
书写顺序和执行顺序
-- 书写顺序
select -> from -> where -> group by -> having -> order by
-- 大致执行顺序,不同的DBMS可能有差异
from -> where -> group by -> having -> select -> order by
order by执行在select之后,因此可以使用select子句中指定的别名
group by执行在select之前,因此无法使用select子句中的别名
当排序基准列含有null时
select * from product order by purchase_price;
含有null的结果会出现在开头或者尾部
order by子句中可以使用的列
``
意思是先根据超售价格降序排列,出售价格相同时,按照id升序排列
书写顺序和执行顺序
-- 书写顺序
select -> from -> where -> group by -> having -> order by
-- 大致执行顺序,不同的DBMS可能有差异
from -> where -> group by -> having -> select -> order by
order by执行在select之后,因此可以使用select子句中指定的别名
group by执行在select之前,因此无法使用select子句中的别名
当排序基准列含有null时
select * from product order by purchase_price;
含有null的结果会出现在开头或者尾部
order by子句中可以使用的列
可以使用select子句未出现的列名,也可以使用select子句中没有出现过的聚合函数