数据库的简单应用

数据库的简单应用

1.显示当前数据库

show databases

2.创建数据库

create database [数据库名]

3.使用数据库

use [数据库名]

4.删除数据库（警告：不要随便使用）

drop database [数据库名]

5.创建表

create table [表名] （id，int,name,varchar(50));

6.查看表

show [表名]

7.查看表结构

desc [表结构]

8.删除表

drop table [表名]

9.对表的使用

(1).插入语句

insert into student(Sno,Sname,Sdept,Sage)values(“20125128”,"陈东”，“男”，“is”，18）；
insert into student values(sno,sname,ssex,sdept,sage);
insert into sc(sno,cno)values(“2025”,“1”);
inser into sc values(“2012”,“1”,null);
insert into sc(sdept,avg_age)select sdept,avg(sage) from student group by sdept;

(2).修改语句

update student set age=22 where sno=“2020532”;
update student set sage=sage+1;
update sc et grate=0 where sno in
(select Sno from student where sdept=“cs”);

(3).删除语句

delete from student where sno=201215128;
delete from sc;
delete from sc where sno in (
selete sno from student where sdept=“cs”);

(4).查看语句（重点）

一.select里面有group by 和order by

group by 是聚居函数，如果group by子句带有having短句，则只需要满足指定的条件才给输出。
select Sname,Sage,Sdept from student;
select * from student；
select Sname,2014-Sage from student;
//去掉重复的行
select distinct Sno from SC;
//一般默认是all，保留表中重复的行
//where的范围between and ,not between and
确定集合 in,not in
模糊查询like not like
空值is null,not is null.
条件：and 。or ，not。
select Sname from student where Sdept=“cs”;
select Sname Sage from student Sage<20;
select distinct Sname from student where Grate<60;
select Sname,Sdept,Sage from student where Sage between 20 and 30;
select Sname,Ssex from student where Sdept in (“cs”,“cf’,“cg”);
//like是字符串的配备，也就是模糊查询。
百分号代表%任意长度
代表一个字符。
select Sname from student where Sno like "2012053；
等价于select Sname from student where sno=“202456”;
select Sname from studeng where Sno like “流%”;
select Sname from studentt where Sno like "流”;
select Snaem from studengt where Grate is null;
select Sname,sex from student where Sdept=“cs” or sdept=“ma” or Sdept=“is”;
//order by(asc,desc)
select Sno,SGrate from student where Cno=“3” order by Grate DESC;
select * from student order by Sdept,Sage,Sname DESC;

聚集函数group by
count()统计总个数
count（列名）统计一列中值的个数;
sum(列名)计算总和，必须是数值。
AVG（列名）计算一列值的平均数。
man（列名）计算一列值的最大值
min（列名）计算一列中的最小值。
select count() from student;
select count(distinct son )from sc;
select AVG(grate) from sc where Cno=“2”;
select man(GRAET) FROM SC WHERE cNO=“1”;
//查询学生202123选修课程的总学分数。
select sum(Ceredit) from Sc,course where Sno=“20125012” and sc.cno=course.con;
//注意：where子句中是不能用聚集函数作为条件表达式的，聚集函数只能用于select子句和group by中的Having子句。
group by子句将查询结果按某一列或者多列的值分组，值相等的为一组。

select Cno,COUNT(Sno) from SC group by Cno;
select Sno from SC group by Sno HAVING COUNT()>3;
//这里先用G肉片by子句按Sno进行分组，再用聚集函数count对每一个组进行计数，HAVING短语给出了选择组的条件，只有满足条件（即个数大于3）的组才会被选择出来。
where作用于表和视图，HAVING短语作用于组。
where连接两个表，当连接运算符为=时，称为等值连接，使用其他运算符称为非等值连接。
其中连接条件的类型是可比的，但名字不必相同。
select studengt.,SC.* from student,SC WHERE student.sno=SC.SNO;
select student.Sname,Sdept,Sno,Ssex,Sage from student,SC where student.sno=sc.sno;
select student.sno,sname from student,sc where student.sno=sc.sno and sc.cno=“2” and sc.grate>90;
多表连接
select student.sno,sname,sdept,cname,grate from student,sc,course where student.sno=sc.sno and sc.cno=course.cno;
外部连接
select student.sno,sname,ssex,sdept,cno,grate from student left outer jion sc on(student.sno=sc.sno);
嵌套查询
select sname from student where sno IN(Select sno from sc where cno=“2”);