mysql

#truncate table test;仅删除表格中的数据，不删除表格
alter table table_name drop column column_name;删除表的列
ALTER TABLE table_name
ALTER COLUMN column_name datatype;修改列的数据类型
/*
ALTER TABLE Persons
ADD Birthday date;表增加一列
*/
/*
drop table if exists studentinfo;
create table studentinfo(
studentid VARCHAR(12) NOT NULL COMMENT '学生学号',PRIMARY KEY(studentid),KEY(studentid),
name VARCHAR(10) NOT NULL COMMENT '学生姓名',
sex VARCHAR(10) NOT NULL COMMENT '学生性别',
age int(10) NOT NULL COMMENT '学生年龄',
class VARCHAR(10) NOT NULL COMMENT '学生班级',
department VARCHAR(10) NOT NULL COMMENT '学生院系',KEY(department),
delphone VARCHAR(13) NOT NULL COMMENT '学生手机号码',
addrsee VARCHAR(30) COMMENT '家庭住址',
admintime VARCHAR(8) NOT NULL COMMENT '入学时间',
time VARCHAR(128) NOT NULL COMMENT '系统创建的时间'
);
drop table if exists subjectinfo;
create table subjectinfo(
studentid VARCHAR(12) NOT NULL COMMENT '学生学号',PRIMARY KEY(studentid),KEY(studentid),
subject VARCHAR(10) NOT NULL COMMENT '学习科目',
score VARCHAR(10) NOT NULL COMMENT '学科成绩',KEY(score),
time VARCHAR(128) COMMENT'系统创建的时间'
);
insert into studentinfo VALUES('01','a','girl',20,'class 1','computer','11','','2015-01-08','2014-09-09');
insert into studentinfo VALUES('02','b','boy',21,'class 2','computer','1111','','2015-01-08','2014-09-09');
insert into studentinfo VALUES('03','c','girl',20,'class 1','computer','1111','三号','2015-01-08','2014-09-09');
insert into studentinfo VALUES('04','d','boy',23,'class 1','computer','1111','四号','2015-01-08','2014-09-09');
insert into subjectinfo VALUES('01','chinese','80','');
insert into subjectinfo VALUES('02','chinese','80','');
insert into subjectinfo VALUES('03','math','100','');
*/
#select MAX(score) from subjectinfo a,studentinfo b where a.studentid = b.studentid and a.subject = 'math' and b.admintime like'2015%';
#update studentinfo set addrsee = '神舟九号' WHERE name = 'a';
#select AVG(score) from studentinfo b,subjectinfo a WHERE b.department = 'computer' and b.studentid = a.studentid and a.subject = 'math';
/*创建一个存储过程并执行*/
drop procedure if exists pro_test;
create procedure pro_test(a int,b int)
BEGIN
declare c int;
if a is null then
set a = 0;
end if;
if b is null THEN
set b = 0;
end if;
set c = a+b;
select c as sum;
end;
call pro_test(10,20);