1 create database student;
1 drop database student;
建表语句:
1 create table student( 2 sno varchar(255) primary key, 3 sname varchar(255), 4 ssex char(4), 5 sage smallint, 6 sdept char(4) 7 );
添加数据:
1 insert into student(sno,sname,ssex,sage,sdept) values ("20170707001","张一","男",20,"cs"); 2 insert into student values ("20170707002","张二","男",21,"cs"); 3 insert into student(sno,sname,ssex,sage,sdept) values ("20170707003","张三","女",22,"ma"); 4 insert into student(sno,sname,ssex,sage,sdept) values ("20170707004","张四","女",18,"is");
sql语句:
1 alter table student add s_entrance date;
结果:
mysql>>
alter table student modify column sage int;
alter table studentt rename to course;
2.3表的删除
drop table course;
1 -- 方式一 2 select * from student; 3 -- 方式二 4 select sno,sname,ssex,sage,sdept,s_entrance from student;
select sno,sname,sdept from student;
select sname,2014-sage as birthday from student;
1 select sname,lower(sdept) from student;
select distinct s_entrance from student;
1)年龄小于25岁的
select * from student where sage<25;
2)sno等于20170707001的
-- sno值可以加"",也可以不加"" select * from student where sno="20170707001";
3)年龄在20到30之间的
select * from student where sage between 20 and 30; -- 等价于 select * from student where sage >= 20 and sage <=30;
4)名字中含有“一”的
select * from student where sname like "_一";
5)名字中不含“一”的
select * from student where sname not like "_一";
6)按学号降序排列
-- 默认升序(asc) select * from student order by sno desc;
7)聚集函数
1 -- 统计元组个数 2 select count(*) from student; 3 -- 统计一列中的个数(空值不算) 4 select count(distinct s_entrance) from student; 5 -- 计算一列值的总合(必须为int) 6 select sum(sage) from student; 7 -- 计算一列值的平均值 8 select avg(distinct sage) from student; 9 -- 找一个值的最大值 10 select max(sage) from student; 11 -- 找一列值的最小值 12 select min(sage) from student;
8)group by:分组语句
select sname,sum(sage) from student group by sname; -- group by 与where语句不能共用 select sname,sum(sage) from student group by sname having sum(sage)>=50;
等值连接与非等值连接:当连接运算符为“=”时,称为等值连接。使用其它运算符称为非等值连接。
例:查询每个学生及其选修课程的情况
select student.*,sc.* from student,sc where student.sno=sc.sno;
把目标列中重复的属性列去掉则为自然连接。
例:查询每一门课的间接先修课
select f.cno,s.cpno from course f,course s where f.cpno=s.cno;
分为:左外连接,右外连接,内连接
left join,right join,inner join
如:
select * from course f left join course s on f.cpno=s.cno; select * from course f right join course s on f.cpno=s.cno; select * from course f inner join course s on f.cpno=s.cno;
两个表以上的操作称为多表连接
例:查询每个学生的学号,姓名,选修的课程名及成绩
select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno;
如对本文有疑问, 点击进行留言回复!!
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火,普罗米修斯的崛起
网友评论