create database stu2;
#创建utf8字符集的数据库create database stu3 character set utf8;
#查看数据库列表show databases;
#删除数据库drop database stu3;
#使用数据库use stu;
#创建老师表 #primary key:主键 auto_increment：自增长create table teacher
(
id int(4) primary key auto_increment,
name varchar(20) not null,
sex char(1) not null,
age int(3)
);
#查看表show tables;
#查看表定义desc teacher;
#删除表drop table teacher;
#插入一条数据insert into info(name,sex,age)values('刘文龙','男',48);
#更新数据update info set age=60 where name='刘文龙';
#删除数据delete from info where id=2;
#查询所有信息select*from info;
#查询部分字段select id,name from info;
#查询编号为3的人编号，姓名，年龄select id,name,age from info where id=3;
#查询编号为5的人编号，姓名，年龄（用中文显示）select id as 编号,name 姓名 from info where id=3;
#查询“姓张”的人的姓名select name from info where name like '刘%';
#查询“姓刘”和“姓桑”的人的姓名select name from info where name like '刘%' or name like '桑%';
#查询年龄为空的人的姓名和年龄select name,age from info where age is null;
#查询年龄不为空的人的姓名和年龄select name,age from info where age is not null;
#查询年龄在20--80之间的人的姓名和年龄select name,age from info where age between 20 and 80;
select name,age from info where age>=20 and age<=80;
select name,age from info where age>=20 && age<=80;
#查询所有年龄段的人的姓名和年龄select name,age from info where age>=20 or age<=80;
#查询班级中名字是“杨瑞”的一个select distinct name from info where name='杨瑞';
#查询姓名和年龄按年龄升序排列select name,age from info order by age;
select name,age from info order by age asc;
#查询姓名和年龄按年龄降序排列select name,age from info order by age desc;
#查询编号，姓名和年龄按年龄升序排列，如果年龄相同，再年龄相同的人的id按降序排列select id,name,age from info order by age asc,id desc;
#查询前三条学生编号，姓名select id,name from info limit 0,3;
select id,name from info limit 3;
#查询4，5，6条学生编号，姓名select id,name from info limit 3,3;0
#查询学生姓名和成绩（内连接:跟查询位置无关，只要有成绩的人都能查出来）select i.name,s.grade from info i inner join score s on s.sid=i.id;
select i.name,s.grade from score s inner join info i on s.sid=i.id;
#查询学生姓名和成绩（左外连接:跟位置有关，能查出来没有成绩的人）select i.name,s.grade from info i left join score s on s.sid=i.id;
select i.name,s.grade from score s left join info i on s.sid=i.id;
#查询学生姓名和成绩（右外连接:跟位置有关，能查出来没有成绩的人）select i.name,s.grade from info i right join score s on s.sid=i.id;
select i.name,s.grade from score s right join info i on s.sid=i.id;
#查询学生姓名，课程和成绩select i.name,c.name,s.grade from info i
inner join score s on i.id=s.sid
inner join course c on c.id=s.cid;
#查询学生姓名，课程和成绩(笛卡尔积)select i.name,c.name,s.grade from info i,score s,course c where i.id=s.sid and c.id=s.cid;
#求平均年龄select avg(age) from info;
#求总年龄select sum(age) from info;
#求总人数select count(*) from info;
select count(id) from info;
select count(1) from info;
#求最大和最小年龄select max(age),min(age) from info;
#查询学生所有课程的平均分select cid,avg(grade) from score group by cid
#查询学生语文的平均分select cid,avg(grade) from score where cid=1 group by cid
