【数据库操作】

一、sql基础语法
库：
CREATE DATABASE 数据库名称；
DROP DATABASE 数据库名称；
ALTER DATABASE RENAME 老名 TO 新名字
SHOW DATABASE 数据库名称
–一、表：
–1.1增
create table t_student(s_number integer,s_name char(20),s_sex char(10),s_class char(20))
CREATE TABLE t_table（id int PRIMARY KEY AUTO_INCREMENT，userName VARCHAR(20),birthday DATE ）;
create table t_grade(sname char(20),subject char(20),score integer)
–1.2删
drop table c_table
–1.3改
alter table rename_table rename to name_table
–1.4查
select * from c_table

–二、行：
–2.1增
insert into t_student(s_number,s_name,s_sex,s_class)VALUES
(1,‘李明’,‘男’,‘三五班’),
(2,‘王明’,‘男’,‘三二班’),
(3,‘张明明’,‘男’,‘三一班’),
(4,‘李花’,‘女’,‘三四班’),
(5,‘李敏’,‘女’,‘三二班’),
(6,‘李广’,‘男’,‘三五班’),
(7,‘王玉明’,‘男’,‘三五班’),
(8,‘陈灯会’,‘男’,‘三四班’),
(9,‘赵明’,‘男’,‘三一班’),
(10,‘王晓春’,‘女’,‘三五班’)

insert into t_grade(sname,subject,score)VALUES
(‘李明’,‘数学’,89),
(‘王明’,‘语文’,88),
(‘张明明’,‘数学’,100),
(‘李花’,‘女’,61),
(‘李敏’,‘数学’,66),
(‘李广’,‘数学’,78),
(‘王玉明’,‘历史’,55),
(‘陈灯会’,‘地理’,49),
(‘赵明’,‘历史’,72),
(‘王晓春’,‘语文’,83)

–2.2删
delete from name_table where sname=‘老王’
–2.3改
update name_table set sname=‘王中王’ where sname=‘老王’
–2.4查
select * from name_table where sname=‘王中王’

–三、列
–3.1增
alter table name_table add company char(30)
–3.2删
alter table name_table drop COLUMN company
–3.3改
alter table name_table rename COLUMN age to sage
–3.4查
select sage from name_table
–四、其他
–4.1排序 order by
select * from name_table order by sage
–4.2倒叙 desc
select * from name_table order by sage desc
–4.3升序 asc
select * from name_table order by sage asc
–4.4限制 LIMIT
select * from name_table order by sage desc LIMIT 3
–4.5分组
select sname, count(sname) from name_table group by sname
–4.6左关联
select (select * from t_student as ts ,t_grade as tg where ts.s_name=tg.sname )GROUP BY tg.subject
–五、存储过程
–5.1创建函数
–5.1.1模板postgreSQL
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variabl{e}_{n}ame$
DECLARE
declaration;
[…]
BEGIN
< function_body >
[…]
RETURN { variable_name | value }
END; LANGUAGE plpgsql;
–5.1.2案例
CREATE OR REPLACE FUNCTION getstudentnamebyid(sutid int)
RETURNS varchar as $studentName$
DECLARE
studentName varchar;
BEGIN
SELECT s_name into studentName FROM t_student where s_number=sutid;
RETURN studentName;
END;$studentName$ LANGUAGE plpgsql
–5.2查询函数
select getstudentnamebyid(1)
show procedure status;