基本数据库语句

基本数据库语句

创建数据库
create database longgang;

--删除数据库
drop database longgang;

--连接数据库
use longgang;

--创建表
create table student (
 user_id int identity primary key not null,
 user_name varchar(20) unique not null,
 user_sex char(2) default '男' not null,
 
 constraint check_sex check(user_sex = '男' or user_sex = '女')
);

--增加信息
insert into  student (user_name,user_sex) values ('张三','男');

insert into student (user_name) values ('李四');

insert into  student values('王五','女',23);

--修改信息
update student set user_sex = '女'  where user_name = '张三';

--删除信息
delete from student where user_id = 1;

--增加字段
alter  table student add age int default 20 not null;

alter table student add address varchar(50) constraint chack_default default '不详' not null;

--删除字段
alter table student drop column age;

alter table student drop constraint chack_default;
alter table student drop column address;

--查询表
select *from student;
select *from student where user_sex = '女';
select *from student order by user_sex;

--查看所有约束
exec sp_helpconstraint student;

--删除约束
alter table student drop constraint check_sex;

--增加约束
alter table student add constraint check_sex check(user_sex = '男' or user_sex = '女');

alter table phone add constraint p_no default '2222' for phone_no with values;
--删除表
drop table student;

--创建表
create table phone(
 phone_id int identity primary key not null,
 phone_no varchar(13) unique not null,
 student_id int foreign key references  student(user_id)
);

insert into phone values ('1111111',1);
insert into phone values ('555555',2);

--查询两个表
select *from student,phone where student.user_id = phone.student_id and student.user_id = 1;

--创建,删除索引
create index s_name_sex on student(user_name,user_sex);

drop index student.s_name_sex;

--创建临时表
create table #temp (studentName varchar(20),averageMark int);

insert into #temp
   select student1.s_name as studentName,
          avg() as averageMark  from studentExam inner join student1 on studentExam.student_id = student1.s_id
 group by studentName;

 

--创建studnetExam表
create table studentExam (
 Id int identity primary key not null,
 math int default 0 not null,
 eligsh int default 0 not null,
 chinese int default 0 not null,
 student_id int foreign key references student1(s_id)
);

drop table studentExam;

--学生表
create table student1(
 s_id int identity primary key not null,
 s_name varchar(20)
);

--插入数据
insert into student1 values('张三');
insert into student1 values('李四');
insert into student1 values('王五');

insert into studentExam values(70,60,80,1);
insert into studentExam values(80,90,80,2);
insert into studentExam values(60,60,70,3);

--操作student1 和studentExam
select *from studentExam;
select *from studentExam where math > 70;
select avg(math) from studentExam;

select student_id  as 学号,s_name as 姓名,math as 数学,eligsh as 英语,chinese as 语文 ,
 (math + eligsh + chinese)/3 as 平均成绩 from student1,studentExam
  where student1.s_id = studentExam.student_id;

select math as 数学,eligsh as 英语,chinese as 语文 from studentExam;

select *from studentExam where (math + eligsh + chinese)/3 > 70;

--降序
select *from studentExam order by math desc;
--升序
select *from studentExam order by math;

--创建emp
create table emp(
 deptno int,
 deptavg int
);
insert into emp values(1,444);
insert into emp values(6,554);
insert into emp values(4,744);
insert into emp values(8,624);
insert into emp values(2,111);
insert into emp values(3,222);

select *from emp;
select *from emp group by deptno; having deptavg > 2222;
select deptno,count(deptavg)数量 from emp group by deptno;
select deptno,avg(deptavg)平均工资 from emp group by deptno having avg(deptavg)>222;

--sum 值的和  count 个数和
 数据库中重复记录的删除

select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp