目录
1.SQL的基本数据类型
1.数值
名称 | 描述 | 范围 |
---|---|---|
smallint | 小范围整数 | -32768~+32768 |
interger | 整数的典型选择 | 0+-2147483648 |
bigint | 大范围整数 | 0+-9223372036854775807 |
decimal | 用户指定的精度 | 小数点之前可达131072位数,小数点之后可达16383位数 |
numeric | 用户指定的精度 | 小数点之前可达131072位数,小数点之后可达16383位数 |
real | 变量精度 | 6位小数精度 |
double_precision | 变量精度 | 15位小数精度 |
smallserial | 小的自动递增整数 | 1~32767 |
serial | 自动递增整数 | 1~2147483648 |
bigserial | 大的自动递增整数 | 1~9223372036854775807 |
2.字符
名称 | 描述 |
---|---|
varchar(n) | 长度受到限制的变量 |
char(n) | 固定不变,填充空格 |
text | 长度不受限制的变量 |
3.布尔值
布尔值 | 可接受的值 |
---|---|
True | t, true, y, yes, on, 1 |
False | f, false, n, no, off, 0 |
4.日期时间值
名称 | 描述 |
---|---|
Timestamp without timezone | 包含日期和时间(无时区) |
Timestamp with timezone | 包含日期和时间(含时区) |
date | 日期(不含时间) |
Time without timezone | 时间(无时区) |
Time with timezone | 时间(含时区) |
interval | 时间间隔 |
2.SELECT查询
1.select……from语句
select * from products;
select product_id,model from products;
select model,product_id from products;
2.where 子句
select model from products where year=2014;
3.and/or 子句
select model form products where year=2014 and msrp<=1000;
select model from products where year>2014 and year<2016 or product_type='scooter';
4.in / not in 子句
select model from products where year in (2014,2016,2019);
select model from products where year not in (2014,2016,2019);
5. order by 子句
select model from products order by production_start_date;
# ASC升序
select model from products order by production_start_date ASC;
# DESC降序
select model from products order by production_start_date DESC;
select model from products order by year DESC,base_msrp ASC;
6. limit 子句
select model from products order by production_start_date limit 5;
7. is null / is not null 子句
select * from products where production_end_date is null;
select * from products where production_end_date is not null;
3.创建表
1.创建空白表
create table {table_name} (
{column_name_1} {data_type_!} {column_constraint_1},
{column_name_2} {data_type_2} {column_constraint_2},
{column_name_3} {data_type_3} {column_constraint_3},
……
);
#{table_name}是表名
#{column_name}是列名
#{data_type_!}是列的数据类型
#{column_constraint_1}是一个或多个可选关键字。为列赋予特殊属性
2.列约束
NOT NULL:此约束保证列中的任何值都不能为NULL。
UNIQUE:此约束保证列的每一行都具有唯一值,并且没有重复值。
PRIMARY KEY:主键,对每一行都是唯一的。
create table state_populations(
state varchar(2) primary key,
population numeric
);
3.在SQL中创建表
#如果countries表已经存在,则执行下列语句删除该表1
drop table if exists countries;
create table countries(
key int primary key,
name text unique,
founding_year int,
capital text
);
4.使用select创建表
create table products_2014 as (
select * from products where year=2014
);
4.更新表
1.添加和删除列
#添加weight列
alter table products add column weight int;
#删除weight列
alter table products drop column weight int;
2.添加新数据
insert into products(
product_id,model year,
product_type,base_msrp,
production_start_date,production_end_date
)
values(
13,'Nimbus 5000',2019,
'scooter;,500.00,
'2019-03-03','2020-03-03'
);
insert into products(
product_id,model year,
product_type,base_msrp,
production_start_date,production_end_date
)
select * from products where year=2014;
3.更新现有行
update products set base_mrap=299.99
where product_type='scooter' and year<2018;
5.删除数据和表
1.从行中删除值
update customers set email=NULL where customer_id=3;
2.从表中删除行
delete from customers where email='qwertyuio@sdfgh.com';
# 删除customers表中的所有数据不删除表
delete from customers;
truncate table customers;
3.删除表
drop table customers;