《基本的数据库sql语句》.doc

基本数据库语句 创建数据库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