beichen0518的博客

Python学习

SQL

SQL

  • 数据以表格的形式出现
  • 每行为各种记录名称
  • 每列为记录名称所对应的数据域
  • 许多的行和列组成一张表单
  • 若干的表组成database

Windows 下 运行mysql

  • 在命令行提示符下net start mysql57 开启mysql服务

SQL命令

  • limit 限制查询返回的条数
  • select * from table limit 1;
  • offset 指定select数据开始查询的数据偏移量
  • select * from table limit 1 offset 1;
  • 主键和外键关联
  • 方法1
  • select * from table1 t1
  • join table2 t2 on ti.id=t2.sid
  • join table3 t3 on t3.sid=t2.di
  • where t1.name=”;
  • 方法2
  • select * from table1 t1, table2 t2, table3 t3
  • where t1.name=” and t1.id=t2.sid and t3.sid=t2.id;
  • select * from table1 order by(-字段);从大到小,如果不加-,就是按从小到大
  • select * from table1 id in(1, 2); 搜索id是1,2的数
  • foreign key(g_id) references tb_grade(id) 创建外键的命令
  • default null 默认为空

聚合函数

  • group by 字段 按字段将记录分组
  • order by(-字段) 从大到小,如果不加-,就是按从小到大
  • count() 计数
  • sum() 求和
  • max() 找最大
  • min() 找最小
  • avg() 求平均

SQL应用

create database tb_test1 default charset utf8;
use tb_test1;
create table tb_grade(
id int auto_increment,
g_name varchar(30)  not null,
primary key(id)
 );
create table tb_student(
id int auto_increment,
s_name varchar(10) not null,
s_tel varchar(11),
g_id int default null,
primary key(id),
foreign key(g_id) references tb_grade(id)
);
insert into tb_grade(g_name) values('python');
insert into tb_grade(g_name) values('java');
insert into tb_grade(g_name) values('php');
insert into tb_grade(g_name) values('c');
insert into tb_grade(g_name) values('c++');
# 查看表
desc tb_student;
insert into tb_student (s_name, s_tel, g_id) values('李佩', '13441242541'
,1);
insert into tb_student(s_name, s_tel, g_id) values('李四', '15192776631',
2);
insert into tb_student (s_name, s_tel, g_id) values('王菲', '15784322678'
,3);
insert into tb_student(s_name, s_tel, g_id) values('张三', '19999991234',
4);
select * from tb_student s join tb_grade g on g.id=s.g_id where g.g_name=
'python';
# 下面这种方法也是关联主键外键的方法,但不推荐使用
select * from tb_student as s, tb_grade as g where g.id=s.g_id;
insert into tb_student(s_name, s_tel, g_id) values('学生1', '12345678901'
,1);
insert into tb_student(s_name, s_tel, g_id) values('学生2', '12445678901'
,1);
insert into tb_student(s_name, s_tel, g_id) values('学生3', '13445678901'
,2);
insert into tb_student(s_name, s_tel, g_id) values('学生4', '13445688901'
,2);
insert into tb_student(s_name, s_tel, g_id) values('学生5', '13445688902'
,3);
insert into tb_student(s_name, s_tel, g_id) values('学生6', '13445608902'
,3);
# 按字段g_id将记录分组,并求每个组的人数
select count(*) as '学生个数', g.g_name as '班级名称' from tb_student s join tb_grade g
on s.g_id=g.id group by(g.id);
# 求和
select sum(s.id) as '学生个数', g.g_name from tb_student s join tb_grade
g on s.g_id=g.id group by(g.id);
alter table tb_student add s_grade varchar(5);
#删除字段
alter table tb_student drop s_grade;
alter table tb_student change s_grade s_price int;
alter table tb_student add s_grades int;
update tb_student set s_grades=70 where id=1;
update tb_student set s_grades=80 where id=2;
update tb_student set s_grades=90 where id=3;
update tb_student set s_grades=96 where id=4;
update tb_student set s_grades=96 where id=5;
update tb_student set s_grades=60 where id=6;
update tb_student set s_grades=60 where id=7;
update tb_student set s_grades=60 where id=8;
update tb_student set s_grades=60 where id=9;
update tb_student set s_grades=60 where id=10;
#找最大值
select max(s_grades) from tb_student;
#找最小值
select min(s_grades) from tb_student;
#求平均值
select avg(s_grades) from tb_student;
"""
作业:
1. 创建学生表Student,有字段姓名s_name, 家庭地址s_addr, 性别s_sex, 电话s_tel, 
语文成绩s_philology, 数学成绩s_math,主键id,外键g_id(关键班级表)
2. 创建班级表Grade,有字段 班级名称g_name, 班级描述g_desc, 班级创建时间g_create_time, 
主键id
3. 创建学生扩展表StudentInfo, 有字段 家长姓名i_name, 家长电话i_phone,
主键id, 外键i_id(关联学生表)

习题:
1.插入班级表数据
2.插入学生表数据
3.插入学生扩展表数据

4.查询每个班级下有多少学生
5.查询每个班级下学生的语文成绩最高的学生,一个学生
6.查询每个班级下学生的数学成绩最低的学生,前三个学生

7.统计每个班级下语文成绩的平均成绩,以及总和
8.查询每个班级下语文成绩大于60分的学生信息
"""
"""
#创建数据库
create database test2 charset=utf8;
use test2;
#创建班级表
create table tb_grade (
id int auto_increment,
g_name varchar(30) not null,
g_desc varchar(30),
g_create_time date,
primary key(id)
);
#创建学生表
create table tb_Student(
id int auto_increment,
s_name varchar(20) not null,
s_sex varchar(5) not null,
s_addr varchar(50),
s_tel varchar(11),
s_philology int,
s_math int,
g_id int,
primary key(id),
foreign key(g_id) references tb_grade(id)
);
#创建学生扩展表
create table tb_studentinfo(
id int auto_increment,
i_name varchar(20),
i_phone varchar(11),
i_id int,
primary key(id),
foreign key(i_id) references tb_student(id)
);
#插入班级表数据
insert into tb_grade(g_name, g_desc, g_create_time) values('python','stud
y python', '2018-3-1');
insert into tb_grade(g_name, g_desc, g_create_time) values('java','study
java', '2018-3-26');
insert into tb_grade(g_name, g_desc, g_create_time) values('php', 'study
php', '2018-4-1');
insert into tb_grade(g_name, g_desc, g_create_time) values('HTML5', 'stud
y HTML5', '2018-4-26');
#插入学生表数据
insert into tb_student(s_name, s_sex, s_addr, s_tel, s_philology, s_math,
g_id) values('刘备', '男', '蜀国', '12345678901', '100', '60', 1);
insert into tb_student(s_name, s_sex, s_addr, s_tel, s_philology, s_math,
g_id) values('关羽', '男', '蜀国', '12345678902', '80', '80', 1);
insert into tb_student(s_name, s_sex, s_addr, s_tel, s_philology, s_math,
g_id) values('张飞', '男', '蜀国', '12345678903', '59', '89', 1);
insert into tb_student(s_name, s_sex, s_addr, s_tel, s_philology, s_math,
g_id) values('孙权', '男', '吴国', '12345678910', '98', '75', 2);
insert into tb_student(s_name, s_sex, s_addr, s_tel, s_philology, s_math,
g_id) values('小乔', '女', '吴国', '12345678911', '99', '59', 2);
insert into tb_student(s_name, s_sex, s_addr, s_tel, s_philology, s_math,
g_id) values('周瑜', '男', '吴国', '12345678912', '99', '99', 2);
insert into tb_student(s_name, s_sex, s_addr, s_tel, s_philology, s_math,
g_id) values('曹操', '男', '魏国', '12345678920', '100', '89', 3);
insert into tb_student(s_name, s_sex, s_addr, s_tel, s_philology, s_math,
g_id) values('张辽', '男', '魏国', '12345678921', '59', '89', 3);
insert into tb_student(s_name, s_sex, s_addr, s_tel, s_philology, s_math,
g_id) values('王大锤', '男', '中国', '12345678999', '35', '59', 4);
#插入学生扩展表
insert into tb_studentinfo(i_name, i_phone, i_id) values('刘母', '1234567
8901', 1);
insert into tb_studentinfo(i_name, i_phone, i_id) values('刘父', '1234567
8909', 1);
insert into tb_studentinfo(i_name, i_phone, i_id) values('关父', '1234567
8908', 2);
insert into tb_studentinfo(i_name, i_phone, i_id) values('关母', '1234567
8907', 2);
insert into tb_studentinfo(i_name, i_phone, i_id) values('张母', '1234567
8906', 3);
insert into tb_studentinfo(i_name, i_phone, i_id) values('张父', '1234567
8905', 3);
insert into tb_studentinfo(i_name, i_phone, i_id) values('孙父', '1234567
8915', 4);
insert into tb_studentinfo(i_name, i_phone, i_id) values('孙母', '1234567
8916', 4);
insert into tb_studentinfo(i_name, i_phone, i_id) values('乔父', '1234567
8917', 5);
insert into tb_studentinfo(i_name, i_phone, i_id) values('乔母', '1234567
8918', 5);
insert into tb_studentinfo(i_name, i_phone, i_id) values('周母', '1234567
8919', 6);
insert into tb_studentinfo(i_name, i_phone, i_id) values('周父', '1234567
8914', 6);
insert into tb_studentinfo(i_name, i_phone, i_id) values('曹父', '1234567
8929', 7);
insert into tb_studentinfo(i_name, i_phone, i_id) values('曹母', '1234567
8928', 7);
insert into tb_studentinfo(i_name, i_phone, i_id) values('张母', '1234567
8927', 8);
insert into tb_studentinfo(i_name, i_phone, i_id) values('张父', '1234567
8926', 8);
insert into tb_studentinfo(i_name, i_phone, i_id) values('王父', '1234567
8998', 9);
insert into tb_studentinfo(i_name, i_phone, i_id) values('王母', '1234567
8997', 9);
insert into tb_student(s_name, s_sex, s_addr, s_tel, s_philology, s_math,
 g_id) values('赵云', '男', '蜀国', '12345678100', 99, 99, 1);
insert into tb_studentinfo(i_name, i_phone, i_id) values('赵父', '1234567
1010', 10);
#查询每个班级下有多少个学生
select count(*) as '学生人数', g_name as '班级名称' from tb_student s joi
n tb_grade g on s.g_id=g.id group by(g_name);
#查询每个班级下学生的语文成绩最高的学生,一个学生
select * from (select max(s_philology) as c, g_id from tb_student s1 grou
p by s1.g_id) t1 join tb_student t2 on t2.s_philology=t1.c join tb_grade g on g.
id=t2.g_id and t1.g_id=t2.g_id;
查询每个班级下学生的数学成绩最低的学生,前三个学生
select * from tb_student s where 2>=(select count(*) from tb_student 
s1 where s.s_math > s1.s_math and s1.g_id=s.g_id) order by s.g_id, s.s_math;
"""
阅读更多
上一篇AttributeError: module 'pip' has no attribute 'main'问题解决办法
下一篇SQL模糊查询及相关练习
想对作者说点什么? 我来说一句

SQL_基础

2008年03月03日 218KB 下载

sql管理sql管理

2011年06月29日 75KB 下载

sql sql sql

2010年05月10日 3.54MB 下载

SQL 语法 SQL 总结 SQL教程

2010年02月21日 241KB 下载

SQL全部分析面试题

2008年10月10日 1KB 下载

sqldeveloper安装包

2018年06月15日 170.29MB 下载

SQL是实例_bj

2011年09月19日 6KB 下载

sql优化

2011年06月14日 31KB 下载

数据库 SQL 试题

2009年12月12日 26KB 下载

没有更多推荐了,返回首页

关闭
关闭