數(shù)據(jù)庫(kù)的結(jié)構(gòu)：
Databases > Schemas > Tables
一轰豆、數(shù)據(jù)庫(kù)&模式&表的操作
1.1我注、數(shù)據(jù)庫(kù)操作
創(chuàng)建數(shù)據(jù)庫(kù) CREATE DATABASE database_name;
刪除數(shù)據(jù)庫(kù)DROP DATABASE
1.2呀洲、表操作
創(chuàng)建表
-- 標(biāo)準(zhǔn)格式
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
CREATE TABLE study.student
(
id integer NOT NULL,
name character(100),
subjects character(1),
CONSTRAINT student_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE study.student
OWNER TO postgres;
COMMENT ON TABLE study.student
IS '這是一個(gè)學(xué)生信息表2';
create table study.employees
(
id integer not NULL,
"name" character(100),
"age" Integer,
"address" character(100),
"salary" Double Precision,
CONSTRAINT employee_key PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE study.employees
OWNER TO postgres;
COMMENT ON TABLE study.employees
IS '這是一個(gè)員工信息表';
-- 如果某表存在則刪除
DROP TABLE IF EXISTS "study"."house_lianjia_communities";
-- 建立表审胸，還有主鍵
CREATE TABLE "study"."house_lianjia_communities" (
"community_name" Character Varying(255) ,
"plate" Character Varying(255) ,
"site" Character Varying(100) ,
"age" Integer,
"building_density" Double Precision,
"building_type" Character Varying(255) ,
"house_count" Integer,
"building_count" Integer,
"green_rate" Double Precision,
"avr_price" Double Precision,
"develop_company" Character Varying(255) ,
"community_id" Character Varying(255) UNIQUE,
"lat" Double Precision,
"lng" Double Precision,
"growth" Double Precision,
"address" Character Varying(255)[]
);
UNIQUE表示主鍵
刪除表DROP TABLE table_name;
1.3尼变、模式操作
模式(也叫架構(gòu))是指定的表集合利凑。 它還可以包含視圖，索引嫌术，序列哀澈，數(shù)據(jù)類型，運(yùn)算符和函數(shù)度气。
創(chuàng)建模式 CREATE SCHEMA schema_name;
創(chuàng)建表的格式：模式.表
二割按、表的增刪改查
2.1、插入數(shù)據(jù)（INSERT語(yǔ)句）
-- 不必對(duì)應(yīng)全部字段寫入磷籍，不寫的會(huì)有默認(rèn)值
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);
INSERT INTO study.employees ( ID, NAME, AGE, ADDRESS, SALARY)
VALUES
(1, 'Maxsu', 25, '菏嗜伲口市人民大道2880號(hào)', 109990.00 ),
(2, 'minsu', 25, '廣州中山大道 ', 125000.00 ),
(3, '李洋', 21, '北京市朝陽(yáng)區(qū)', 185000.00),
(4, 'Manisha', 24, 'Mumbai', 65000.00),
(5, 'Larry', 21, 'Paris', 85000.00);
2.2、查詢數(shù)據(jù)（SELECT語(yǔ)句）
SELECT "column1", "column2".."column" FROM "table_name";
SELECT id,name FROM EMPLOYEES;
SELECT * FROM "table_name";
2.3院领、更新數(shù)據(jù)（UPDATE語(yǔ)句）
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
update study.employees
set age=29, salary=9800
where id=1;
2.4弛矛、刪除數(shù)據(jù)（DELETE語(yǔ)句）
DELETE FROM table_name
WHERE [condition];
delete from study.employees
where id =6
不用where限制則，全部刪除
2.5比然、ORDER BY子句
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
select * from study.employees
order by age asc
多列排序 ORDER BY
select * from study.employees
order by age, name asc;
2.6丈氓、分組（GROUP BY子句）
依據(jù)by后字段，合并相應(yīng)的數(shù)據(jù)
SELECT column-list
FROM table_name
WHERE [conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
SELECT NAME, SUM(SALARY)
FROM study.employees
GROUP BY NAME;
額外插入新數(shù)據(jù),有重復(fù)的name强法，便于sum函數(shù)的結(jié)果顯示
INSERT INTO study.employees VALUES
(6, '李洋', 24, '深圳市福田區(qū)中山路', 135000),
(7, 'Manisha', 19, 'Noida', 125000),
(8, 'Larry', 45, 'Texas', 165000);
2.7万俗、Having子句
用于「字段的函數(shù)結(jié)果」?jié)M足某些條件的特定行
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
SELECT NAME
FROM study.employees
GROUP BY NAME HAVING COUNT (NAME) < 2;
目前的數(shù)據(jù)，「李洋」饮怯、「Larry」闰歪、「 Manisha」三位名字都重復(fù)2次。
select name, count(name)
from study.employees
group by name having count(name) > 1;
三硕淑、條件查詢(對(duì) WHERE語(yǔ)句 進(jìn)一步限定范圍)
AND 條件
OR 條件
AND & OR 條件
NOT 條件
LIKE 條件
IN 條件
NOT IN 條件
BETWEEN 條件
