1.创建数据库
CREATE DATABASE name;
DROP DATABASE name; ----删除数据库
2.创建模式
CREATE SCHEMA test ;
DROP SCHEMA test; ------删除模式
3.创建表
CREATE TABLE test.student (
id INTEGER not null,
sno char(10) PRIMARY KEY ,----主键
sname char(10) UNIQUE ,
sage SMALLINT,
sdept char(20)
);
CREATE TABLE test.course(
cno char(2) PRIMARY KEY , ----主键
cname char(20) not null,
cpno char(1),
ccredit smallint,
foreign key (cpno) references test.course(cno) ----外码
);
CREATE table test.sc(
sno char(10) not null,
cno char(2) not null,
grade SMALLINT,
foreign key (sno) references test.student(sno),
foreign key (cno) references test.course(cno)
);
CREATE table test.ceshi (
ID SERIAL, -----------------自增
cno CHAR(2) NOT NULL,
grade SMALLINT
);
insert into test.ceshi(cno,grade) values ('2',49);
DROP table test.ceshi -----删除表
4.表内查询
4.1 插入
INSERT INTO test.student(id,sno,sname,sage,sdept) VALUES (7,'14106','鱿鱼',32,'物理');
INSERT INTO test.course(cno,cname,cpno,ccredit) VALUES ('4','科学','2' ,10); ----cpno:先修课,要先插入null后再用
INSERT INTO test.sc(sno,cno,grade) VALUES ('14101','2',75);
4.2 查询
select id,* from test.student where sdept like '医学%';
4.3 更新
update test.student set sage=20 where sno='14100';
4.4 删除
DELETE from test.student where sno='14100';
4.5 ORDER BY(ASC/DESC)
4.5 GROUP BY
select sdept,count(*) 人数 from test.student where sno like '141%'group by sdept;
4.6 HAVING
HAVING子句与GROUP BY子句组合使用,用于选择函数结果满足某些条件的特定行。
SELECT sdept,count(*) FROM test.student WHERE sno like '141%' GROUP BY sdept HAVING count(*)>1 ORDER BY count(*) DESC
4.7 条件查询
AND 条件/OR 条件/AND & OR 条件/NOT 条件/LIKE 条件/IN 条件/NOT IN 条件/BETWEEN 条件
SELECT * FROM test.student WHERE (sno LIKE '141%' AND sdept IN ('医学系','计算机')) OR (sage BETWEEN 20 AND 24)
6.表级连接
6.1 内连接(INNER JOIN):返回满足条件的所有行
SELECT * from test.student a INNER JOIN test.sc b ON a.sno=b.sno;
6.2 左外连接(LEFT OUTER JOIN):左表全部列出来,而右表没有匹配上的项全留为空值。
SELECT * from test.student a LEFT JOIN test.sc b ON a.sno=b.sno;
6.3 右外连接(RIGHT OUTER JOIN):右表全部列出来,而左表没有匹配上的项全留为空值。
SELECT * from test.student a RIGHT JOIN test.sc b ON a.sno=b.sno;
6.4 全连接(FULL OUTER JOIN):左表和右表没有匹配上的项全留为空值。
SELECT * from test.student a FULL JOIN test.sc b ON a.sno=b.sno;
6.5 跨连接(CROSS JOIN):将第一个表的每一行与第二个表的每一行相匹配。列数=两表列数和,行数=两表行数积。
SELECT * from test.student a CROSS JOIN test.sc b;
备注:
*1.约束:
(1)主键约束:就是对一个列进行了约束,约束为(非空、不重复)(eg:要对一个列加主键,列名为id,表名为emp)
alter table 表格名称 add constraint 约束名称 增加的约束类型 (列名) -----格式
alter table emp add constraint ppp primary key (id) -----例子
(2)check约束:给一列的数据进行了限制(eg:年龄列的数据都要大于20的 表名(emp) )
alter table 表名称 add constraint 约束名称 增加的约束类型 (列名) -----格式
alter table emp add constraint xxx check(age>20) -----例子
(3)unique约束:给列的数据追加的不重复的约束类型
alter table 表名 add constraint 约束名称 约束类型(列名) -----格式
alter table emp add constraint qwe unique(ename) -------例子
(4)默认约束:让此列的数据默认为一定的数据(eg:emp表中的gongzi列默认为10000)
alter table 表名称 add constraint 约束名称 约束类型 默认值 for 列名 ------格式
alter table emp add constraint jfsd default 10000 for gongzi ------例子
(5)外键约束:外键其实就是引用(eg:要让emp表中的did列去引用dept表中的id)
alter table 表名 add constraint 约束名称 约束类型 (列名) references 被引用的表名称(列名) ------格式
alter table emp add constraint jfkdsj foreign key (did) references dept (id) ------例子
*2.数据库中delete和truncate的区别
(1)TRUNCATE TABLE比DELETE的速度快;
(2)TRUNCATE table 是删除表的所有行,而DELETE是删除表的一行或者多行(除非DELETE不带WHERE语句);
(3)在删除时如果遇到任何一行违反约束(主要是外键约束),TRUNCATE TABLE仍然删除,只是表的结构及其列、约束、索引等保持不变,但DELETE是直接返回错误;
(4)对于被外键约束的表,不能使用TRUNCATE TABLE,而应该使用不带WHERE语句的DELETE语句。
(5)如果想保留标识计数值,要用DELETE,因为TRUNCATE TABLE会对新行标志符列搜用的计数值重置为该列的种子。