mysql练习_创建库与列表、增加列表信息、列表查询(包含多列表查询)_月隐学python第23课

最新推荐文章于 2021-07-02 08:55:56 发布
最新推荐文章于 2021-07-02 08:55:56 发布
阅读量311 收藏
点赞数
分类专栏： python 文章标签： mysql创建库 mysql创建列表 mysql增加列表信息 mysql列表查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u014586651/article/details/104952451
版权
本文详细介绍了SQL数据库的设计过程，包括创建数据库、表格及字段，同时展示了如何通过SQL语句进行数据插入、更新和查询操作。特别关注了复杂查询语句的构建，如条件筛选、联合查询、子查询等，旨在提升读者对SQL数据库管理和数据检索的能力。
摘要由CSDN通过智能技术生成

目录

一 、题目与设计

二  、运行结果

一 、题目与设计

SHOW DATABASES;
drop DATABASE student;

-- **********创建库************************
create DATABASE student default charset = utf8;

-- SHOW tables;
use student;
show tables;
-- show create DATABASE student;
-- select DATABASE()
-- alter table student change ssno sno varchar(3) ;

-- **********创建列表***********************
create table if not exists student(
	sno varchar(3) primary key default 000 comment "学号",
	sname varchar(4) not null comment "姓名",
	ssex varchar(2) default 0 comment "性别",
	sbirthday datetime comment "生日",
	class varchar(5) not null comment "班级"
);
-- -- -- -- alter table student add ssex enum('男','女') default '男';
alter table student modify ssex enum('男','女') default '男';
desc student;

create table if not exists course(
	cno varchar(5) primary key,
	cname varchar(10) not null,
	tno varchar(10) not null
);

create table if not exists grade(
	sno varchar(3) ,
	cno varchar(5) ,
	degree numeric(3) not null,
	primary key(sno,cno)
);

create table if not exists teacher(
	tno varchar(3) primary key,
	tname varchar(10) not null,
	tsex varchar(2) ,
	tbirthday datetime,
	prof varchar(6) not null,
	depart varchar(10) not null
);

create table if not exists rank(
	down numeric(3) not null,
	up numeric(3) not null,
	rank varchar(1) primary key 
);

-- ***********插入列表信息***********************

INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (108 ,'曾华','女' , '1977-09-01',95033);
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (105 ,'匡明' ,'男' , '1975-10-02',95031);
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (107 ,'王丽' ,'女' , '1976-01-23',95033);
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (101 ,'李军' ,'男' , '1976-02-20',95033);
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (109 ,'王芳' ,'女' , '1975-02-10',95031);
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (103 ,'陆君' ,'男' , '1974-06-03',95031);

-- select * from student;

INSERT INTO course(cno,cname,tno) VALUES ('3-105' ,'计算机导论', '825');
INSERT INTO course(cno,cname,tno) VALUES ('3-245' ,'操作系统' , '804');
INSERT INTO course(cno,cname,tno) VALUES ('6-166' ,'数据电路' , '856');
INSERT INTO course(cno,cname,tno) VALUES ('9-888' ,'高等数学' , '100');

-- select * from course;

INSERT INTO grade(sno,cno,degree)VALUES (103,'3-245',86);
INSERT INTO grade(sno,cno,degree)VALUES (105,'3-245',75);
INSERT INTO grade(sno,cno,degree)VALUES (109,'3-245',68);
INSERT INTO grade(sno,cno,degree)VALUES (103,'3-105',92);
INSERT INTO grade(sno,cno,degree)VALUES (105,'3-105',88);
INSERT INTO grade(sno,cno,degree)VALUES (109,'3-105',76);
INSERT INTO grade(sno,cno,degree)VALUES (101,'3-105',64);
INSERT INTO grade(sno,cno,degree)VALUES (107,'3-105',91);
INSERT INTO grade(sno,cno,degree)VALUES (108,'3-105',78);
INSERT INTO grade(sno,cno,degree)VALUES (101,'6-166',85);
INSERT INTO grade(sno,cno,degree)VALUES (107,'6-106',79);
INSERT INTO grade(sno,cno,degree)VALUES (108,'6-166',81);

-- select * from grade;

INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

-- select * from teacher;

insert into rank(down,up,rank) values(90,100,'A');
insert into rank(down,up,rank) values(80,89,'B');
insert into rank(down,up,rank) values(70,79,'C');
insert into rank(down,up,rank) values(60,69,'D');
insert into rank(down,up,rank) values(0,59,'E');

-- select * from rank;

-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student;

-- 2、 查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher ;

-- 3、 查询Student表的所有记录。
select * from student;

-- 4、 查询Grade表中成绩在60到80之间的所有记录。
select * from grade where degree>=60 and degree<=80 ;

-- 5、 查询Grade表中成绩为85，86或88的记录。
select * from grade where degree=85 or degree=86 or degree=88;

-- 6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where class = 95031 or ssex = '女';

-- 7、 以Class降序查询Student表的所有记录。
select * from student order by class desc;

-- 8、 以Cno升序、Degree降序查询Grade表的所有记录。
select * from grade order by cno asc,degree desc;

-- 9、 查询“95031”班的学生人数。
select count(*) from student where class=95031; 

-- 10、查询Grade表中的最高分的学生学号和课程号。
-- select sno,cno from grade where degree=max(degree) ;
select max(degree), sno,cno from grade ;

-- 11、查询‘3-105’号课程的平均分。
select avg(degree) from grade where cno='3-105';

-- 12、查询Grade表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno, avg(degree) from grade where cno like '3%' group by cno having count(*)>5;

-- 13、查询最低分大于70，最高分小于90的Sno列。
select sno from grade where degree>70 and degree<90 ;

-- 14、查询所有学生的Sname、Cno和Degree列。
select sname,cno,degree from student s,grade g where s.sno = g.sno;

select sname,cno,degree from student s left outer join grade g on s.sno = g.sno;

-- 15、查询所有学生的Sno、Cname和Degree列。
select sno, cname, degree from course c, grade g where c.cno = g.cno;

select sno, cname, degree from course c left outer join grade g on c.cno = g.cno;

-- 16、查询所有学生的Sname、Cname和Degree列。
select sname, cname, degree from (student s inner join grade g on s.sno = g.sno)
	inner join course c on c.cno = g.cno;

-- 17、查询“95033”班所选课程的平均分。
select avg(degree) from grade g where sno in (select sno from student s where class = '95033');

select cno , avg(degree) from grade g inner join student s on g.sno = s.sno
	where class = '95033' group by cno ;

-- 18、查询选修课成绩为A等的学生信息
select * from student s inner join grade g on s.sno = g.sno 
	where degree>=(select down from rank where rank = 'A')
		and degree<=(select up from rank where rank = 'A');


-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from student 
	where sno in (select sno from grade where cno = '3-105' 
		and degree >= (select degree from grade where sno = 109 and cno = '3-105'));

select * from student s join grade g on s.sno = g.sno 
	where cno= '3-105' and degree >= (select degree from grade where sno = 109 and cno = '3-105');

-- 20、查询grade中选学一门以上课程的同学中分数为非最高分成绩的记录。
select g.sno, cno, degree ,max_dg from grade g 
	inner join (select sno, max(degree) max_dg from grade group by sno having count(*)>1 ) tmp 
		on g.sno = tmp.sno
	where degree != max_dg ;



-- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from student s join grade g on g.cno = '3-105' 
	where degree > (select degree from student s join grade g on s.sno = g.sno 
		where  g.cno = '3-105' and s.sno = '109');

-- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sno, sname, sbirthday from student 
	where year(sbirthday) = (select year(sbirthday) from student where sno = '108');


-- 23、查询“张旭“教师任课的学生成绩。
select sno, degree from grade 
	where cno = (select cno from course 
		where tno = (select tno from teacher where tname = '张旭'));

select sno, degree from grade g inner join course c on g.cno = c.cno 
	where c.tno = (select t.tno from teacher t where t.tname = '张旭') ; 

-- 24、查询选修某课程的同学人数多于5人的教师姓名。
select tname from teacher t 
	where t.tno = (select c.tno from course c 
	where c.cno in (select g.cno from grade g group by g.cno having count(*)>5));

select t.tname from teacher t  inner join course c on t.tno = c.tno 
	where c.cno in (select g.cno from grade g group by g.cno having count(*)>5) ;

-- 25、查询95033班和95031班全体学生的记录。
select * from student s left outer join  grade g on s.sno = g.sno 
	left outer join course c on g.cno = c.cno
	left outer join teacher t on c.tno = t.tno
	where s.class in ('95033','95031') ;

-- 26、查询存在有85分以上成绩的课程Cno.
select cno from grade g where degree > 85;

-- 27、查询出“计算机系“教师所教课程的成绩表。
select * from grade g 
	where g.cno in (
		select c.cno from course c 
		where c.tno in (select t.tno from teacher t where t.depart = '计算机系')
		); 

select * from grade g inner join course c on g.cno = c.cno
	where c.tno in (select t.tno from teacher t where t.depart = '计算机系');  

-- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select tname , prof from teacher 
	where prof not in (select t.prof from teacher t where t.depart = '电子工程系');   

-- 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select g1.sno, g1.cno, g1.degree from grade g1 
	inner join grade g2 on g1.cno = '3-105' and g2.cno = '3-245'
	where g1.sno = g2.sno and g1.degree >= g2.degree 
	order by g1.degree asc ;

-- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select g1.sno, g1.cno, g1.degree from grade g1 
	inner join grade g2 on g1.cno = '3-105' and g2.cno = '3-245'
	where g1.sno = g2.sno and g1.degree > g2.degree ;

-- 31、查询所有教师和同学的name、sex和birthday.
select tname, tsex, tbirthday from teacher
	union all
	select sname, ssex, sbirthday from student;

-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.
select tname, tsex, tbirthday from teacher where tsex = '女'
	union all
	select sname, ssex, sbirthday from student where ssex = '女' ;

-- 33、查询成绩比该课程平均成绩低的同学的成绩表。
select * from grade g1 
	inner join (select cno, avg(degree) avg_dg from grade group by cno) g2 on g1.cno = g2.cno
		where g1.degree < avg_dg;  

-- 34、查询所有任课教师的Tname和Depart.
select tname ,depart from course c inner join teacher t on c.tno = t.tno;

-- 35  查询所有未讲课的教师的Tname和Depart. 
select tname, depart from teacher t where t.tno not in (select c.tno from course c ) ;

-- 36、查询至少有2名男生的班号。
select class, count(*) from student where ssex = '男' group by class having count(*)>=2;

-- 37、查询Student表中不姓“王”的同学记录。
select * from student where sname not like '王%';

-- 38、查询Student表中每个学生的姓名和年龄。
select sname,year(now()) - year(sbirthday) from student; 

-- 39、查询Student表中最大和最小的Sbirthday日期值。
select sbirthday from student 
	where sbirthday in (select min(sbirthday) from student
	union 
	select max(sbirthday) from student);

-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student order by class desc, sbirthday asc;

-- 41、查询“男”教师及其所上的课程。
select * from teacher t inner join course c on t.tno = c.tno
	where tsex = '男';

-- 42、查询最高分同学的Sno、Cno和Degree列。
select * from grade g 
	inner join (select cno, max(degree) max_dg from grade group by cno) tmp on g.cno = tmp.cno
	where g.degree = max_dg;

-- 43、查询和“李军”同性别的所有同学的Sname.
select sname from student where ssex = (select ssex from student where sname = '李军') ;

-- 44、查询和“李军”同性别并同班的同学Sname.
select sname from student 
	where ssex = (select ssex from student where sname = '李军') 
		and class = (select class from student where sname = '李军') ;

-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表
select * from course c inner join grade g on c.cno = g.cno
	inner join student s on s.sno = g.sno
	where c.cname = '计算机导论' and s.ssex = '男';

-- 46、查询计算机系教师所教课程成绩为B等的课程信息
select * from course 
	where cno in (select cno from grade where degree >= (select down from rank where rank = 'B')
			and degree <=  (select up from rank where rank = 'B'))
		and tno in (select tno from teacher where depart = '计算机系') ;

select * from course c inner join grade g on c.cno = g.cno
	inner join teacher t on t.tno = c.tno
	where g.degree>= (select down from rank where rank = 'B')
		and degree <=  (select up from rank where rank = 'B')
		and depart = '计算机系';

-- 47、查询成绩在C等以上的学生的所在班级
select * from student s inner join grade g on s.sno = g.sno 
	where g.degree >= (select down from rank where rank = 'C')
	order by class asc,cno asc ;

select distinct class from student s inner join grade g on s.sno = g.sno 
	where g.degree >= (select down from rank where rank = 'C');

-- 48、查询班里B等以上学生人数最多的班级信息
select * from student s inner join grade g on s.sno = g.sno 
	where g.degree >= (select down from rank where rank = 'B');

select class, count(class) from student s inner join grade g on s.sno = g.sno 
	where g.degree >= (select down from rank where rank = 'B')
	group by class;

二  、运行结果

D:/Program Files (x86)/MySQL/MySQL Server 5.7/bin/MYSQL: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| student1           |
| sys                |
+--------------------+
+-----------+-------------------+------+-----+---------+-------+
| Field     | Type              | Null | Key | Default | Extra |
+-----------+-------------------+------+-----+---------+-------+
| sno       | varchar(3)        | NO   | PRI | 0       |       |
| sname     | varchar(4)        | NO   |     | NULL    |       |
| ssex      | enum('男','女')   | YES  |     | 男      |       |
| sbirthday | datetime          | YES  |     | NULL    |       |
| class     | varchar(5)        | NO   |     | NULL    |       |
+-----------+-------------------+------+-----+---------+-------+
+--------+------+-------+
| sname  | ssex | class |
+--------+------+-------+
| 李军   | 男   | 95033 |
| 陆君   | 男   | 95031 |
| 匡明   | 男   | 95031 |
| 王丽   | 女   | 95033 |
| 曾华   | 女   | 95033 |
| 王芳   | 女   | 95031 |
+--------+------+-------+
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 电子工程系      |
+-----------------+
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     64 |
| 105 | 3-245 |     75 |
| 107 | 6-106 |     79 |
| 108 | 3-105 |     78 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
+-----+-------+--------+
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 6-166 |     85 |
| 103 | 3-245 |     86 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 107 | 3-105 |     91 |
| 105 | 3-105 |     88 |
| 108 | 3-105 |     78 |
| 109 | 3-105 |     76 |
| 101 | 3-105 |     64 |
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 107 | 6-106 |     79 |
| 101 | 6-166 |     85 |
| 108 | 6-166 |     81 |
+-----+-------+--------+
+----------+
| count(*) |
+----------+
|        3 |
+----------+
+-------------+-----+-------+
| max(degree) | sno | cno   |
+-------------+-----+-------+
|          92 | 101 | 3-105 |
+-------------+-----+-------+
+-------------+
| avg(degree) |
+-------------+
|     81.5000 |
+-------------+
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     81.5000 |
+-------+-------------+
+-----+
| sno |
+-----+
| 101 |
| 103 |
| 105 |
| 105 |
| 107 |
| 108 |
| 108 |
| 109 |
+-----+
+--------+-------+--------+
| sname  | cno   | degree |
+--------+-------+--------+
| 李军   | 3-105 |     64 |
| 李军   | 6-166 |     85 |
| 陆君   | 3-105 |     92 |
| 陆君   | 3-245 |     86 |
| 匡明   | 3-105 |     88 |
| 匡明   | 3-245 |     75 |
| 王丽   | 3-105 |     91 |
| 王丽   | 6-106 |     79 |
| 曾华   | 3-105 |     78 |
| 曾华   | 6-166 |     81 |
| 王芳   | 3-105 |     76 |
| 王芳   | 3-245 |     68 |
+--------+-------+--------+
+--------+-------+--------+
| sname  | cno   | degree |
+--------+-------+--------+
| 李军   | 3-105 |     64 |
| 李军   | 6-166 |     85 |
| 陆君   | 3-105 |     92 |
| 陆君   | 3-245 |     86 |
| 匡明   | 3-105 |     88 |
| 匡明   | 3-245 |     75 |
| 王丽   | 3-105 |     91 |
| 王丽   | 6-106 |     79 |
| 曾华   | 3-105 |     78 |
| 曾华   | 6-166 |     81 |
| 王芳   | 3-105 |     76 |
| 王芳   | 3-245 |     68 |
+--------+-------+--------+
+-----+-----------------+--------+
| sno | cname           | degree |
+-----+-----------------+--------+
| 101 | 计算机导论      |     64 |
| 101 | 数据电路        |     85 |
| 103 | 计算机导论      |     92 |
| 103 | 操作系统        |     86 |
| 105 | 计算机导论      |     88 |
| 105 | 操作系统        |     75 |
| 107 | 计算机导论      |     91 |
| 108 | 计算机导论      |     78 |
| 108 | 数据电路        |     81 |
| 109 | 计算机导论      |     76 |
| 109 | 操作系统        |     68 |
+-----+-----------------+--------+
+------+-----------------+--------+
| sno  | cname           | degree |
+------+-----------------+--------+
| 101  | 计算机导论      |     64 |
| 101  | 数据电路        |     85 |
| 103  | 计算机导论      |     92 |
| 103  | 操作系统        |     86 |
| 105  | 计算机导论      |     88 |
| 105  | 操作系统        |     75 |
| 107  | 计算机导论      |     91 |
| 108  | 计算机导论      |     78 |
| 108  | 数据电路        |     81 |
| 109  | 计算机导论      |     76 |
| 109  | 操作系统        |     68 |
| NULL | 高等数学        |   NULL |
+------+-----------------+--------+
+--------+-----------------+--------+
| sname  | cname           | degree |
+--------+-----------------+--------+
| 李军   | 计算机导论      |     64 |
| 李军   | 数据电路        |     85 |
| 陆君   | 计算机导论      |     92 |
| 陆君   | 操作系统        |     86 |
| 匡明   | 计算机导论      |     88 |
| 匡明   | 操作系统        |     75 |
| 王丽   | 计算机导论      |     91 |
| 曾华   | 计算机导论      |     78 |
| 曾华   | 数据电路        |     81 |
| 王芳   | 计算机导论      |     76 |
| 王芳   | 操作系统        |     68 |
+--------+-----------------+--------+
+-------------+
| avg(degree) |
+-------------+
|     79.6667 |
+-------------+
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     77.6667 |
| 6-106 |     79.0000 |
| 6-166 |     83.0000 |
+-------+-------------+
+-----+--------+------+---------------------+-------+-----+-------+--------+
| sno | sname  | ssex | sbirthday           | class | sno | cno   | degree |
+-----+--------+------+---------------------+-------+-----+-------+--------+
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 |     92 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 |     91 |
+-----+--------+------+---------------------+-------+-----+-------+--------+
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
+-----+--------+------+---------------------+-------+-----+-------+--------+
| sno | sname  | ssex | sbirthday           | class | sno | cno   | degree |
+-----+--------+------+---------------------+-------+-----+-------+--------+
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 |     92 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 |     88 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 |     91 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 | 108 | 3-105 |     78 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 | 109 | 3-105 |     76 |
+-----+--------+------+---------------------+-------+-----+-------+--------+
+-----+-------+--------+--------+
| sno | cno   | degree | max_dg |
+-----+-------+--------+--------+
| 101 | 3-105 |     64 |     85 |
| 103 | 3-245 |     86 |     92 |
| 105 | 3-245 |     75 |     88 |
| 107 | 6-106 |     79 |     91 |
| 108 | 3-105 |     78 |     81 |
| 109 | 3-245 |     68 |     76 |
+-----+-------+--------+--------+
+-----+--------+------+---------------------+-------+-----+-------+--------+
| sno | sname  | ssex | sbirthday           | class | sno | cno   | degree |
+-----+--------+------+---------------------+-------+-----+-------+--------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 | 103 | 3-105 |     92 |
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 | 105 | 3-105 |     88 |
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 | 107 | 3-105 |     91 |
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 | 108 | 3-105 |     78 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 |     92 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 105 | 3-105 |     88 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 107 | 3-105 |     91 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 108 | 3-105 |     78 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 103 | 3-105 |     92 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 |     88 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 107 | 3-105 |     91 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 108 | 3-105 |     78 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 103 | 3-105 |     92 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 105 | 3-105 |     88 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 |     91 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 108 | 3-105 |     78 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 | 103 | 3-105 |     92 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 | 105 | 3-105 |     88 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 | 107 | 3-105 |     91 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 | 108 | 3-105 |     78 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 | 103 | 3-105 |     92 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 | 105 | 3-105 |     88 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 | 107 | 3-105 |     91 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 | 108 | 3-105 |     78 |
+-----+--------+------+---------------------+-------+-----+-------+--------+
+-----+--------+---------------------+
| sno | sname  | sbirthday           |
+-----+--------+---------------------+
| 108 | 曾华   | 1977-09-01 00:00:00 |
+-----+--------+---------------------+
+-----+--------+
| sno | degree |
+-----+--------+
| 101 |     85 |
| 108 |     81 |
+-----+--------+
+-----+--------+
| sno | degree |
+-----+--------+
| 101 |     85 |
| 108 |     81 |
+-----+--------+
+--------+
| tname  |
+--------+
| 王萍   |
+--------+
+--------+
| tname  |
+--------+
| 王萍   |
+--------+
+-----+--------+------+---------------------+-------+------+-------+--------+-------+-----------------+------+------+--------+------+---------------------+-----------+-----------------+
| sno | sname  | ssex | sbirthday           | class | sno  | cno   | degree | cno   | cname           | tno  | tno  | tname  | tsex | tbirthday           | prof      | depart          |
+-----+--------+------+---------------------+-------+------+-------+--------+-------+-----------------+------+------+--------+------+---------------------+-----------+-----------------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 | 101  | 3-105 |     64 | 3-105 | 计算机导论      | 825  | 825  | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系        |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 103  | 3-105 |     92 | 3-105 | 计算机导论      | 825  | 825  | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系        |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 105  | 3-105 |     88 | 3-105 | 计算机导论      | 825  | 825  | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系        |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 107  | 3-105 |     91 | 3-105 | 计算机导论      | 825  | 825  | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系        |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 | 108  | 3-105 |     78 | 3-105 | 计算机导论      | 825  | 825  | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系        |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 | 109  | 3-105 |     76 | 3-105 | 计算机导论      | 825  | 825  | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系        |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 103  | 3-245 |     86 | 3-245 | 操作系统        | 804  | 804  | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 105  | 3-245 |     75 | 3-245 | 操作系统        | 804  | 804  | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 | 109  | 3-245 |     68 | 3-245 | 操作系统        | 804  | 804  | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 | 101  | 6-166 |     85 | 6-166 | 数据电路        | 856  | 856  | 张旭   | 男   | 1969-03-12 00:00:00 | 讲师      | 电子工程系      |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 | 108  | 6-166 |     81 | 6-166 | 数据电路        | 856  | 856  | 张旭   | 男   | 1969-03-12 00:00:00 | 讲师      | 电子工程系      |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 107  | 6-106 |     79 | NULL  | NULL            | NULL | NULL | NULL   | NULL | NULL                | NULL      | NULL            |
+-----+--------+------+---------------------+-------+------+-------+--------+-------+-----------------+------+------+--------+------+---------------------+-----------+-----------------+
+-------+
| cno   |
+-------+
| 3-105 |
| 3-245 |
| 3-105 |
| 3-105 |
+-------+
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     64 |
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 107 | 3-105 |     91 |
| 108 | 3-105 |     78 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
+-----+-------+--------+
+-----+-------+--------+-------+-----------------+-----+
| sno | cno   | degree | cno   | cname           | tno |
+-----+-------+--------+-------+-----------------+-----+
| 101 | 3-105 |     64 | 3-105 | 计算机导论      | 825 |
| 103 | 3-105 |     92 | 3-105 | 计算机导论      | 825 |
| 103 | 3-245 |     86 | 3-245 | 操作系统        | 804 |
| 105 | 3-105 |     88 | 3-105 | 计算机导论      | 825 |
| 105 | 3-245 |     75 | 3-245 | 操作系统        | 804 |
| 107 | 3-105 |     91 | 3-105 | 计算机导论      | 825 |
| 108 | 3-105 |     78 | 3-105 | 计算机导论      | 825 |
| 109 | 3-105 |     76 | 3-105 | 计算机导论      | 825 |
| 109 | 3-245 |     68 | 3-245 | 操作系统        | 804 |
+-----+-------+--------+-------+-----------------+-----+
+--------+-----------+
| tname  | prof      |
+--------+-----------+
| 李诚   | 副教授    |
+--------+-----------+
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 109 | 3-105 |     76 |
| 105 | 3-105 |     88 |
| 103 | 3-105 |     92 |
+-----+-------+--------+
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
+--------+------+---------------------+
| tname  | tsex | tbirthday           |
+--------+------+---------------------+
| 李诚   | 男   | 1958-12-02 00:00:00 |
| 王萍   | 女   | 1972-05-05 00:00:00 |
| 刘冰   | 女   | 1977-08-14 00:00:00 |
| 张旭   | 男   | 1969-03-12 00:00:00 |
| 李军   | 男   | 1976-02-20 00:00:00 |
| 陆君   | 男   | 1974-06-03 00:00:00 |
| 匡明   | 男   | 1975-10-02 00:00:00 |
| 王丽   | 女   | 1976-01-23 00:00:00 |
| 曾华   | 女   | 1977-09-01 00:00:00 |
| 王芳   | 女   | 1975-02-10 00:00:00 |
+--------+------+---------------------+
+--------+------+---------------------+
| tname  | tsex | tbirthday           |
+--------+------+---------------------+
| 王萍   | 女   | 1972-05-05 00:00:00 |
| 刘冰   | 女   | 1977-08-14 00:00:00 |
| 王丽   | 女   | 1976-01-23 00:00:00 |
| 曾华   | 女   | 1977-09-01 00:00:00 |
| 王芳   | 女   | 1975-02-10 00:00:00 |
+--------+------+---------------------+
+-----+-------+--------+-------+---------+
| sno | cno   | degree | cno   | avg_dg  |
+-----+-------+--------+-------+---------+
| 101 | 3-105 |     64 | 3-105 | 81.5000 |
| 105 | 3-245 |     75 | 3-245 | 76.3333 |
| 108 | 3-105 |     78 | 3-105 | 81.5000 |
| 108 | 6-166 |     81 | 6-166 | 83.0000 |
| 109 | 3-105 |     76 | 3-105 | 81.5000 |
| 109 | 3-245 |     68 | 3-245 | 76.3333 |
+-----+-------+--------+-------+---------+
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 李诚   | 计算机系        |
| 王萍   | 计算机系        |
| 张旭   | 电子工程系      |
+--------+-----------------+
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 刘冰   | 电子工程系      |
+--------+-----------------+
+-------+----------+
| class | count(*) |
+-------+----------+
| 95031 |        2 |
+-------+----------+
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 |
+-----+--------+------+---------------------+-------+
+--------+-------------------------------+
| sname  | year(now()) - year(sbirthday) |
+--------+-------------------------------+
| 李军   |                            44 |
| 陆君   |                            46 |
| 匡明   |                            45 |
| 王丽   |                            44 |
| 曾华   |                            43 |
| 王芳   |                            45 |
+--------+-------------------------------+
+---------------------+
| sbirthday           |
+---------------------+
| 1974-06-03 00:00:00 |
| 1977-09-01 00:00:00 |
+---------------------+
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
+-----+--------+------+---------------------+-----------+-----------------+-------+--------------+-----+
| tno | tname  | tsex | tbirthday           | prof      | depart          | cno   | cname        | tno |
+-----+--------+------+---------------------+-----------+-----------------+-------+--------------+-----+
| 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        | 3-245 | 操作系统     | 804 |
| 856 | 张旭   | 男   | 1969-03-12 00:00:00 | 讲师      | 电子工程系      | 6-166 | 数据电路     | 856 |
+-----+--------+------+---------------------+-----------+-----------------+-------+--------------+-----+
+-----+-------+--------+-------+--------+
| sno | cno   | degree | cno   | max_dg |
+-----+-------+--------+-------+--------+
| 101 | 6-166 |     85 | 6-166 |     85 |
| 103 | 3-105 |     92 | 3-105 |     92 |
| 103 | 3-245 |     86 | 3-245 |     86 |
| 107 | 6-106 |     79 | 6-106 |     79 |
+-----+-------+--------+-------+--------+
+--------+
| sname  |
+--------+
| 李军   |
| 陆君   |
| 匡明   |
+--------+
+--------+
| sname  |
+--------+
| 李军   |
+--------+
+-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-------+
| cno   | cname           | tno | sno | cno   | degree | sno | sname  | ssex | sbirthday           | class |
+-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-------+
| 3-105 | 计算机导论      | 825 | 101 | 3-105 |     64 | 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 3-105 | 计算机导论      | 825 | 103 | 3-105 |     92 | 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 3-105 | 计算机导论      | 825 | 105 | 3-105 |     88 | 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
+-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-------+
+-------+-----------------+-----+
| cno   | cname           | tno |
+-------+-----------------+-----+
| 3-245 | 操作系统        | 804 |
| 3-105 | 计算机导论      | 825 |
+-------+-----------------+-----+
+-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-----------+--------------+
| cno   | cname           | tno | sno | cno   | degree | tno | tname  | tsex | tbirthday           | prof      | depart       |
+-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-----------+--------------+
| 3-245 | 操作系统        | 804 | 103 | 3-245 |     86 | 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系     |
| 3-105 | 计算机导论      | 825 | 105 | 3-105 |     88 | 825 | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系     |
+-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-----------+--------------+
+-----+--------+------+---------------------+-------+-----+-------+--------+
| sno | sname  | ssex | sbirthday           | class | sno | cno   | degree |
+-----+--------+------+---------------------+-------+-----+-------+--------+
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 |     88 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 |     92 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 | 109 | 3-105 |     76 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 105 | 3-245 |     75 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 103 | 3-245 |     86 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 | 108 | 3-105 |     78 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 |     91 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 107 | 6-106 |     79 |
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 | 101 | 6-166 |     85 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 | 108 | 6-166 |     81 |
+-----+--------+------+---------------------+-------+-----+-------+--------+
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
+-----+--------+------+---------------------+-------+-----+-------+--------+
| sno | sname  | ssex | sbirthday           | class | sno | cno   | degree |
+-----+--------+------+---------------------+-------+-----+-------+--------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 | 101 | 6-166 |     85 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 |     92 |
| 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 | 103 | 3-245 |     86 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 |     88 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 |     91 |
| 108 | 曾华   | 女   | 1977-09-01 00:00:00 | 95033 | 108 | 6-166 |     81 |
+-----+--------+------+---------------------+-------+-----+-------+--------+
+-------+--------------+
| class | count(class) |
+-------+--------------+
| 95031 |            3 |
| 95033 |            3 |
+-------+--------------+
[Finished in 0.3s]

 

风中月隐
关注
专栏目录
python入门到高级全栈工程师培训 第3期 附课件代码
06-07
04 多表查询之复合查询与子查询 05 mysql之索引 第48章 01 python操作数据库pymysql 02 数据库之事务 03 mysql事务之savepoint 第49章 01 http协议之请求协议 02 http协议之响应协议 03 web框架的概念 04 做一个...
创建表的性别字段使用ENUM遇到的问题
a595601220的博客
12-07 3682
问题描述： 　　　　创建了一个学生表(student)，其中的性别字段语句如下：ALTER TABLE student ADD sex ENUM('男','女','保密') NOT NULL DEFAULT '男'； 　　 2.报错：ERROR 1291 (HY000): Column ‘sex’ has duplicated value ‘?’ in ENUM 经过...
mysql 约束
09-24 170
约束 为了防止数据的准确性和可靠性 防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容 约束条件和数据类型的宽度一样都是可选参数，主要分为： NOT NULL # 非空约束，指定某列（字段）不能为空。比如学生姓名 UNIQUE # 唯一约束，指定...
mysql 用DML操作数据库(二)
分享我的点点滴滴，在成长路上与你同行！
03-15 498
约束种类 非空约束(not null)：字段不能为null值，必须给定具体的数据 唯一性约束(unique) ：具有唯一性，不可重复，但可以为null 主键约束(primary key) PK：该字段不能重复也不能为空 外键约束(foreign key) FK：主要用来维护两个表之间数据的一致性。 检查约束(目前MySQL不支持、Oracle支持) 数据表添加相应的约束： 创建表的同...
`sex` enum('男','女','保密') default '保密'#1067 - Invalid default value for 'sex'
一个程序媛的奋斗史
08-21 4655
`sex` enum('男','女','保密') default '保密' MySql数据库执行该行出现 #1067 - Invalid default value for ‘sex’的错误，应该考虑创建数据库时有没有将字符集设置为utf8解决办法： 1.利用数据库管理工具直接将数据库的字符集设置为utf8 2.命令行 alter database [数据库名] character set u
老男孩Mysql DBA运维课程(19部全) Mysql DBA高级运维系列课程
12-30
02-第二部-老男孩MySQL多实例安装与企业应用场景（10节） 03-第三部-老男孩MySQL应用管理及进阶实战操作（29节） 04-第四部-老男孩MySQL乱码问题及字符集实战（14节） 05-第五部-老男孩MySQL备份-增量备份及数据恢复...
老男孩Python完美实战课程 14期视频教程 28周Python视频教程 1-14周部分
01-07
├<老男孩 python3 14期 25周全python3教程> │ ├老男孩教育-Python14期VIP视频-14节-mp4> │ │ ├01 python s14 day1 开课介绍.avi │ │ ├02 python s14 day1 开课介绍2.avi │ │ ├03 python s14 day1 开课...
Python教程资料400页.pdf
11-19
3. **模块与包**：导入和使用外部模块，创建自定义模块，理解sys、os、numpy、pandas等常用库的使用。 4. **文件操作**：读写文件，掌握文件和目录管理。 5. **错误和异常处理**：理解并使用try/except语句，编写...
3.3创建查阅字段列示例
复行数十步
07-02 4839
在数据库中向表中的某个字段输入数据时，经常出现输入的数据是一个数据集合中的某个值的情况。例如教师职称_定是“教授、副教授、讲师、助教竹这个数据集合中的其中一个元素的值。对于输入这种数据的字段列，最简单的方法是把该字段列设置为“查阅向导”数据类型。严格地说“查阅向导”不是一种新的数据类型，它是建立一种在某个数据集合中选择数据值的关系。Access提供的这种数据类型给用户带来了很大的方便。 当完成字段的查阅设置后，在这个字段输人数据时就可以不用输入数据，而从一个列表中选择数据，这样既加快了数据输入的...
创建和查看数据库表
金州饿霸吃不饱的博客
05-13 5373
1创建数据库表：CREATE TABLE &lt;表名&gt;(    列名1 数据类型 [列级别约束条件][默认值],    列名2 数据类型 [列级别约束条件][默认值],    ……     [表级别约束条件]);2 查看数据库表：（1）查看数据列表：SHOW TABLES [FROM db-name];（2）查看数据表的基本结构：        法一：SHOW COLUMNS FROM t...
MySQL SELECT查询语句练习2（中级篇）
qq_41978139的博客
03-26 450
MySQL SELECT查询语句练习2（中级篇） 原创 ...
SQL练习50题整理
mitsuki_blog的博客
07-21 1万+
CREATE DATABASE 50q; USE 50q; CREATE TABLE STUDENT ( SNO VARCHAR(3) NOT NULL, SNAME VARCHAR(4) NOT NULL, SSEX VARCHAR(2) NOT NULL, SBIRTHDAY DATETIME, CLASS VARCHAR(5) );
mysql查询某学期开设的课程_Mysql----sql语句练习题(二)
weixin_35342111的博客
01-19 6461
今天有时间，咱们继续把数据库的查询内容继续补充，上次sql语句练习一是单表查询，今天的练习二是多表查询，多表查询1.1内连接查询计算机系学生的选课情况，列出学生的名字、所选课程的课程号和考试成绩查询“信息管理系”选了“计算机文学”课程的学生信息，列出学生姓名、课程名和成绩查询所有选了"微机原理"课程的学生姓名和所在系统计每个系学生的考试平均成绩统计计算机系学生中每门课程的选课人数、平均成绩、最高成...
Mysql学习积累之一[网摘收藏 个人学习参考]
weixin_34087301的博客
11-16 116
之前主要使用Oracle,对MySQL不熟悉.以为两者差不多,但发现其实还是有点区别,现在开始学习Mysql. 任何时候,技术没有早晚,看到自己缺少的,就只能自己主动补钙.:) 收藏一篇文章如下,参考:http://www.centos.bz/2011/07/mysql-frequently-used-commands/.所有权归原作者. ==========================...
PostgreSQL数据库中的常见错误
热门推荐
步行者的专栏
11-28 10万+
 我们在操作数据库的时候，我们总会遇到很多错误。下面整理了一下常见的错误。错误1      FATAL: connection limit exceeded for non-superusers      原因：非超级用户的连接数（max_connections - superuser_reserved_connections）超过了设定值      解决办法：增加max_co
数据库建表查阅
diaolianxian9049的博客
10-10 374
一、 设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。 表1-1数据库的表结构 表（一）Student (学生表) ...
python学习之turtle库基本操作
月隐博客
11-07 1万+
目录   一段运行绘制蟒蛇的代码示例 turtle函数基本说明 一段运行绘制蟒蛇的代码示例 import turtle turtle.setup(650,350,200,200) turtle.penup() turtle.fd(-250) turtle.pendown() turtle.pensize(25) turtle.pencolor("purple") turtle.seth(...
分支和循环_月隐学python第5课
月隐博客
01-12 2040
1.多向条件分⽀支 【后续代码】 执行流程：多选一，如果满足条件1，执行【代码段1】，然后跳出if-elif语句，执行【后续代码】，否则判断是否满足条件2，如果满足执行【代码段2】，然后跳出ifelif语句，执⾏【后续代码】 ...。如果所有条件都不满足，执行【else语句块】，然后再执⾏行【后续代码】。 注意： else是可选的，可以写也可以不写。 2 循环 广义：一个周期现象或者...
Python递归实现MySQL菜单查询与JSON转换实战
在本文档中，作者分享了一个关于使用Python在MySQL数据库环境中实现递归查询菜单并将其转换为JSON格式的实例。该场景涉及一个名为`tb_menu`的数据库表，用于存储菜单信息，包括菜单ID、名称、链接、类型（可能是层级...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

风中月隐

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值