create database pangya;//建数据库pangya
use pangya;//使用数据库pangya
create table shapang(
sid int not null primary key auto_increment,
sname varchar(20) not null,
ssex varchar(5) not null check (ssex in ('男','女')),
sage int not null check(sage between 0 and 150),
stel varchar(11) unique
);//建表shapang
insert into shapang(sname,ssex,sage,stel) value(
'pangya',
'女',
20,
'15099061981'
);//插入一条数据
select * from shapang;//查询数据
select * from shapang where sname like'_a%';//模糊查询
alter table shapang drop stel;//删除shapang表中的stel字段
alter table shapang add stel varchar(11);//增加shapang表中的stel字段
alter table shapang modify stel varchar(13);//将stel字段的长度由11改为13(关键字为modify)
delete from shapang where sage=151;//删除shapang表中年龄为151的数据
提取系统当前日期:
select curdate();
提取系统当前年份:
select extract(year from curdate());
提取员工入职年份(在scott表下操作):
select extract(year from hiredate);
获取员工的工作年限(在scott表下操作):
select extract(year from curdate())-extract(year from hiredate) from emp;