php重点知识数据库sql语句学习

《PHP重点------数据库知识》

--创建数据库school

create database school

--删除数据库

drop database school

go--可以派出先后执行顺序

--更改当前操纵的数据库

use school

go

--创建教师表-->主表

create table teacher

(

class_name varchar(20)primary key,

tea_name varchar(20)not null,

t_time datetime default(getdate())

)

go

--创建学生表-->从表

create table student

(

stu_id int identity(1,1)primary key,

stu_name varchar(20)not null,

sex char(2)check(sex='男' or sex='女'),

score int default(0),

class_name varchar(20)foreign key references teacher(class_name)--class_name字段必须是teacher表的主键

)

go

--添加语句insert

insert into teacher(class_name,tea_name)values('s123','张老师')

insert into teacher values('s456','郭老师','2008-1-2 15:51:30')

insert into teacher(class_name,tea_name)values('t123','李老师')

insert into teacher(class_name,tea_name)values('t456','赵老师')

go

insert into student(stu_name,sex,score,class_name)values('李刚','男',68,'s123') insert into student(stu_name,sex,score,class_name)values('李小明','男',80,'t123') insert into student(stu_name,sex,score,class_name)values('李大刚','男',97,'t456') insert into student(stu_name,sex,score,class_name)values('王菲','女',100,'t123') insert into student(stu_name,sex,score,class_name)values('赵刚','男',90,'s123') insert into student(stu_name,sex,score,class_name)values('李宁','男',85,'t123') insert into student(stu_name,sex,score,class_name)values('赵姗','女',56,'s456') insert into student(stu_name,sex,score,class_name)values('郭蕊','女',78,'t456') insert into student(stu_name,sex,score,class_name)values('孙小旭','男',60,'s123') insert into student(stu_name,sex,score,class_name)values('孙津','女',72,'t123') insert into student(stu_name,sex,score,class_name)values('丁磊','男',23,'t456')

--更新语句

update student set score=80 where stu_name='孙津'

update student set score=81,class_name='t456' where stu_name='孙津'

--删除语句

delete from student--清空表

truncate table student--清空表不走日志快

delete from student where stu_name='丁磊'

delete from student where stu_name='丁磊' and stu_name='孙津'

--查询语句

select * from teacher

select * from student

select * from student where stu_name='李刚'

select stu_name,score from student where stu_name='李刚'

select * from student where score!=60

select * from student where score<>60

select * from student where score>60 and sex='女'

--排序order by

select * from student order by score --升序

select * from student order by score desc --降序

select * from student order by stu_name

select * from student order by stu_name,score

--desc 降序

排序的语法select * from 表名order by 字段名+排序方式