sql数据库基础语法

---

1.创建一个数据库

create database student

2.删除一个数据库

drop database student

3.使用数据库

use student

4.查看数据库

shou databases

5.创建一个表

create table student（
id int(4) not null auto_increment ,
name varchar(20) not null default’小红’ comment’名字’,
password varchar(20),
birthday datetime defalut null comment’出生日期’,
address varchar(100) defalut null comment’家庭地址’,
email varchar(50) default null comment’邮箱’
primary key(id)
）

7.show table student 查看表

8.desc student 显示表的结构

9.修改表名

alter table student rename as student1

10.增加表的字段

alter table student add age int(11)

11.修改表的字段 重命名

alter table student modify age varchar(20)
alter table student change age age1 int(1)

12.删除表的字段

alter table student drop age1

13.删除表

drop table student

14.外键

create table school(
gradeid int(10) not null commnet’年级id’,
gradename varchar(20) not null comment’年级名称’,
primary key(gradeid)
)
create table student（
id int(4) not null auto_increment ,
name varchar(20) not null default’小红’ comment’名字’,
password varchar(20),
birthday datetime defalut null comment’出生日期’,
gradeid int(10) not null commnet’学生的年级’,
address varchar(100) defalut null comment’家庭地址’,
email varchar(50) default null comment’邮箱’,
primary key(id),
key fk_gradeid(gradeid),
constraint fk_gradeid foreing key(gradeid) references shool(gradeid)
）

alter table student add constraint fk_gradeid foreing key (gradeid) references school(gradeid)

15.插入语句

insert into student (name,password) values(‘李类’，‘12312’)

16.修改语句

update student set name=‘大学’ where id=1

17.删除语句

delete from student where id=1

18清空表

truncate student

19.查询

select*from student
select studentid,name from student
select studentid as 学号,name as 姓名 from student as 学生表
select concat(‘姓名:’,name) as 新名字 from student
去重
select distinct studentid from student
select studentid,age+1 as 提分后 from student

20.where条件子句

select id,age from student where age>=95 and<=100
select id,age from student where age between 95 and 100
select id,age from student where age !=21

21.模糊查询

selectfrom student where name like ‘刘%’
selectfrom student where name like ‘刘_’
selectfrom student where name like ‘刘__’
selectfrom student where name like ‘%龙%’
selectfrom student where age in(13,14)
selectfrom student where address in(‘北京’)
selectfrom student where address like ‘%北京%’
selectfrom student where age is null selectfrom student where age=‘’
selectfrom student where age is not null

22.联表查询

select s.studentid,age,student from student s left join school sc where s.studentid=sc.studentid
1
select s.studentid,age,student from student s left join school sc on s.studentid=sc.studentid where

select s.studentid studentname,SubjectName,StudentResult from student s right join result r on s.studentid=r.studentid inner join subject sub on r.subjectno=sub.subjectno

23.自连接

SELECT s.name FROM student s INNER JOIN student a ON s.password=a.grateid

24.升降序

ASC DESC
select s.studentid,age,student from student s left join school sc on s.studentid=sc.studentid where age=11
order by age ASC/DESC
//limit 1,5

25.子查询

select StudentNo,StudentName from student where StudentNo= (
select StudentNo from result where StudentResult>=80
)

26.函数

select count(name)from student
select count(*)from student 忽略null
select count(1)from student null

select sum(name) from student
select AVG(name) from student
select max(name) from student
select min(name) from student
select SubjectName,AVG(StudentResult),MAX(StudentResult),min(StudentResult)from result r inner join subject sub on
r.Subjectno=sub.Subjectno
group by r.subjectno
Having AVG(StudentResult)>=80