基本数据库语句
来源:互联网 发布:java 日志管理系统 编辑:程序博客网 时间:2024/05/22 04:54
基本数据库语句
创建数据库
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
- 数据库基本链接语句
- 数据库基本语句
- 数据库基本语句
- 基本数据库语句
- 启动数据库基本语句
- SQL数据库基本语句
- l数据库基本语句
- 数据库基本SQL语句
- 数据库基本语句
- 数据库基本语句
- MySql数据库基本语句
- 数据库基本语句
- 数据库基本语句
- 数据库查找基本语句
- 数据库基本sql语句
- sql数据库基本语句
- 数据库基本语句
- 基本数据库语句
- 空悬指针(dangling pointers)
- 回顾
- 我的青春是在江苏呆的:)
- 关于中国大多数php网站的杂谈
- 往oracle中插入timestamp型数据
- 基本数据库语句
- test code
- [转]影响中国软件开发的20人
- AT&T汇编与8086汇编区别
- Windows Server 2008 使用印象
- 郁闷ing
- 我自己收藏整理而成的计算机编程资料合集,欢迎大家下载
- 2009-5-10 15:58:15 org.apache.tomcat.util.threads.ThreadPool logFull
- Google App Engine