MySQL创建表 和 添加数据 查询数据

已于 2022-09-19 18:36:20 修改
阅读量925 收藏 7
点赞数 3
文章标签： 数据库
于 2022-09-18 14:43:13 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/red_HTML/article/details/126904796
版权

创建数据库 有就删除 没有就创建

CREATE DATABASE IF NOT EXISTS mysql1;
-- 使用表 
USE mysql1; 

-- 删除数据库   DROP DATABASE mysql1;

创建表

-- 学生表
CREATE TABLE IF NOT EXISTS Student
(
	Sno VARCHAR(20) NOT NULL PRIMARY KEY,
	Sname VARCHAR(20) NOT NULL,
	Ssex VARCHAR(20) NOT NULL,
	Sbirthday DATETIME,
	Class VARCHAR(20)
)
-- 课程表
CREATE TABLE IF NOT EXISTS Course
(
	Cno VARCHAR(20) NOT NULL PRIMARY KEY,
	Cname VARCHAR(20) NOT NULL,
	Tno VARCHAR(20) NOT NULL ,
	CONSTRAINT fk_Course_id FOREIGN KEY(Tno) REFERENCES Con(id)
)
-- 成绩表
CREATE TABLE IF NOT EXISTS Score
(
	Sno VARCHAR(20) NOT NULL PRIMARY KEY,
-- 	CONSTRAINT fk_Score_sno FOREIGN KEY(Sno) REFERENCES Sno(id),
	Cno VARCHAR(20) NOT NULL,
-- 	CONSTRAINT fk_Score_cno FOREIGN KEY(Cno) REFERENCES Cno(id),
	Degree VARCHAR(20) 
)
-- 教师表
CREATE TABLE IF NOT EXISTS Teacher
(
	Tno VARCHAR(20) NOT NULL PRIMARY KEY,
	Tname VARCHAR(20) NOT NULL,
	Tsex VARCHAR(20) NOT NULL,
	Tbirthday DATETIME,
	Prof VARCHAR(20),
	Depart VARCHAR(20) NOT NULL
)

使用表

SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Score;
SELECT * FROM Teacher;

添加数据

-- 学生
INSERT INTO Student(Sno,Sname,Ssex,Sbirthday,Class)VALUES
('108','曾华','男','1977-9-1','95033'),
('105','匡明','男','1975-10-2','95031'),
('107','王丽','女','1977-1-23','95033'),
('106','李军','男','1977-2-20','95033'),
('109','王芳','女','1977-2-10','95031'),
('100','陆军','男','1977-6-3','95031');
-- 课程
INSERT INTO Course(Cno,Cname,Tno)VALUES
('3-105','计算机导论','825'),
('3-245','操作系统','804'),
('6-166','数字电路','856'),
('9-888','高等数学','831');
-- 成绩
INSERT INTO Score(Sno,Cno,Degree)VALUES
('70','3-245','75'),
('60','3-245','68'),
('50','3-105','92'),
('77','3-105','88'),
('80','3-105','76'),
('88','3-105','64'),
('56','3-105','91'),
('76','3-105','78'),
('22','6-166','85'),
('90','6-166','79'),
('1000','6-166','81');
-- 教师
INSERT INTO Teacher(Tno,Tname,Tsex,Tbirthday,Prof,Depart)VALUES
('804','李诚','男','1985-12-2','副教授','计算机系'),
('856','张旭','男','1969-3-12','讲师','电子工程系'),
('825','王萍','女','1972-5-15','助教','计算机系'),
('831','刘冰','女','1977-8-14','助教','电子工程师');

查询数据

-- ①　查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname,Ssex,Class from Student
-- ②　查询教师所有的单位即不重复的Depart列。
select Depart from Teacher 
-- ③　查询Student表的所有记录。
select * from Student 
-- ④　查询Score表中成绩在60到80之间的所有记录。
select * from Score where Degree between 60 AND 80;
-- ⑤　查询Score表中成绩为85，86或88的记录。
select * from Score where Degree=85 or Degree=86 or Degree=88
-- ⑥　查询Student表中“95031”班或性别为“女”的同学记录。
select * from Student where Class=95031 or Ssex='女'
-- ⑦　以Class降序查询Student表的所有记录。
select * from Student ORDER BY Class
-- ⑧　以Cno升序、Degree降序查询Score表的所有记录。
select * from Score ORDER BY Degree
-- ⑨　查询分数大于70，小于90的Sno列。
select * from Score where Sno>70 AND Sno<90
-- ⑩　查询95033班和95031班全体学生的记录。
select * from Student where Class=95033 or Class=95031
-- 11　查询存在有85分以上成绩的课程Cno，注意去重。
select Cno from Score where Degree>85
-- 12　查询Student表中不姓“王”的同学记录。
select * from Student where Sname like '王%' -- 查询姓王的
select * from Student where Sname not like '王%'
-- 13　查询Student表中每个学生的姓名和年龄。
select Sname,Ssex from Student
-- 14　以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from Student ORDER BY Class DESC ,Sbirthday;

三种连接表的方式

交叉连接   左连接   右连接  内连接  limit  正序 和 升序 

左向外联接的结果集包括指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行的右表中没有匹配行，则在相关 连的结果集行中右表的所有选择列表列均为空值。

-- 交叉连接
select * from Student cross join Teacher
select * from Student , Teacher 
-- 左连接 那个在左边那个就是主表
select * from Student as a left join Teacher as b on a.Sno = b.Tno 
-- 右连接
select * from teacher b right join Student a on b.Tno = a.Sno
-- 内连接
select * from Student a inner join Teacher b on a.Sno = b.Tno
-- Limit
select * from Student limit 2,4
-- 排序
select * from Student ORDER BY Sno -- 升序
select * from Student ORDER BY Sno DESC-- 降序

小案例

drop database ku;
create database ku;
use ku;



create table student(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(16) NOT NULL,
	sage INT NOT NULL,
	ssex VARCHAR(256),
	snativeplace VARCHAR(256),
	smajor VARCHAR(256),
	sclass  VARCHAR(256),
	snative VARCHAR(256)
)AUTO_INCREMENT=10001;
CREATE TABLE course(
 cid INT NOT NULL  PRIMARY KEY auto_increment,
 cname VARCHAR(32) NOT NULL,
 cval  INT NOT NULL,
 ctime INT NOT NULL,
 tid INT NOT NULL
);
CREATE TABLE teacher(
 tid INT NOT NULL PRIMARY KEY auto_increment,
 tname VARCHAR(32) NOT NULL,
 tsex VARCHAR(32) NOT NULL,
 tage INT NOT NULL,
 tlvl VARCHAR(32) NOT NULL
 
);

create table mark(
 sid varchar(256) not null,
 cid varchar(256) not null,
 cmark varchar(256) not null
);
INSERT into student(sname,sage,ssex,snativeplace,smajor,sclass,snative) VALUES
("廖官忠",21,"男","福建","信计","2班","汉族"),
("张三",20,"男","江苏","信计","2班","汉族"),
("李四",19,"男","福建","信计","2班","傣族"),
("萧瑾",21,"女","福建","统计","1班","汉族"),
("叶晶",21,"女","上海","传媒","1班","朝鲜族"),
("萧瑾",19,"女","北京","统计","2班","汉族"),
("李宁",21,"男","云南","信控","1班","傣族"),
("唐品",18,"男","江苏","信计","2班","汉族"),
("吴强",20,"男","山东","统计","1班","汉族"),
('欧阳锋',22,'男','四川','应数','1班','汉族'),
('王充样',23,'男','黑龙江','统计','2班','汉族'),
('韦小宝',17,'女','福建','统计','2班','汉族'),
('李绍',21,'女','福建','信计','2班','汉族'),
('黄马华',22,'男','浙江','心理学','1班','汉族'),
('艾蔚儿',19,'女','福建','计算机','1班','黎族'),
('赵若辰',21,'男','江苏','心理学','1班','汉族'),
('徐扬',22,'男','青海','计算机','1班','汉族'),
('徐静静',19,'女','安徽','计算机','1班','黎族');

INSERT INTO course(cid,cname,cval,ctime,tid)VALUES(2001,'数学',6,64,30001),(2002,'英语',4,64,30002),(2003,'体育',2,32,30003),(2004,'马克思主义',6,64,30004),(2005,'计算机基础',3,48,30005),(2006,'心理学',4,48,30006),(2007,'空间天气学',6,64,30007);
INSERT INTO teacher(tid,tname,tsex,tage,tlvl)VALUES(30001,'马六','男',45,'高级教师'),(30002,'胡美丽','女',32,'中级教师'),(30003,'李强','男',40,'高级教师'),(30004,'胡适','男',55,'教授'),(30005,'钱枫','男',37,'高级教师'),(30006,'戴安安','女',27,'中级教师'),(30007,'张伯伦','男',47,'教授');

insert into mark(sid,cid,cmark) values
(10001,2001,85),
(10001,2002,75),
(10001,2003,80),
(10001,2004,70),
(10001,2005,60),
(10001,2006,95),
(10001,2007,70),
(10002,2001,80),
(10002,2002,65),
(10002,2003,70),
(10002,2004,80),
(10002,2005,55),
(10002,2006,78),
(10002,2007,82),
(10003,2001,69),
(10003,2002,57),
(10003,2003,90),
(10003,2004,80),
(10003,2005,77),
(10003,2006,92),
(10003,2007,80),
(10004,2001,85),
(10004,2002,76),
(10004,2003,66),
(10004,2004,54),
(10004,2005,80),
(10004,2006,73),
(10004,2007,80);
select * from student;
select * from course;
select * from teacher;
select * from mark;

-- – 1、与张三同乡的男生姓名’
SELECT sname FROM student WHERE ssex='男' AND snativeplace=(
	SELECT snativeplace FROM student WHERE sname='张三') AND sname != '张三'

-- – 2、选修了马六老师所讲课程的学生人数
SELECT  COUNT(*) FROM mark WHERE cid=(
SELECT cid FROM course WHERE tid=(
SELECT tid FROM teacher WHERE tname='马六'))
GROUP BY cid

-- 
-- – 3、查询没学过"马"姓老师课的同学的学号、姓名
SELECT sid,sname FROM student WHERE sid NOT in(
SELECT  sid FROM mark WHERE cid in (
SELECT cid FROM course WHERE tid in (
SELECT tid FROM teacher WHERE tname LIKE '马%')))

-- 
-- – 4、“数学”课程得最高分的学生姓名，性别
SELECT sname,ssex from student WHERE sid =(
SELECT sid FROM mark WHERE cmark=(
SELECT MAX(cmark) FROM mark WHERE cid=(
SELECT cid FROM course WHERE cname='数学')) AND cid=(
SELECT cid FROM course WHERE cname='数学'))

-- – 5、统计每门课程的平均成绩，并按照成绩降序排序
-- 
-- – 6、子查询实现查询“2班”“张三”同学的“英语成绩”
SELECT cmark FROM mark WHERE sid=(
SELECT sid FROM student WHERE sname='张三' AND sclass='2班') AND cid=(
SELECT cid FROM course WHERE cname='英语')

-- 
-- – 7、查询“福建”地区学生所选修的全部课程名称
SELECT cname FROM course WHERE cid in(
SELECT cid FROM mark WHERE sid in(
SELECT sid FROM student WHERE snativeplace='福建'))

-- 
-- – 10、用子查询实现查询选修“高等数学”课的全部学生的高等数学总成绩
SELECT SUM(cmark) FROM mark WHERE cid =(
SELECT cid FROM course WHERE cname='数学') 

-- – 11、用子查询实现查询选修“高等数学”课的全部学生的所有课程总成绩
SELECT SUM(cmark) FROM mark WHERE sid in(
SELECT sid FROM mark WHERE cid=(
SELECT cid FROM course WHERE cname='数学'))

-- 
-- – 12、请用两种方法实现：查找所有成绩都在68分以上的学生姓名
SELECT sname FROM student s,mark m WHERE s.sid=m.sid GROUP BY s.sid HAVING MIN(cmark)>=68
-- SELECT sname FROM student WHERE sid in(SELECT sid FROM mark GROUP BY sid HAVING MIN(cmark)>=68)
-- 
-- – 13、查找至少两门成绩在80分以上的学生姓名
SELECT sname FROM student WHERE sid in(
SELECT sid FROM (
SELECT COUNT(*) b,sid FROM mark WHERE cmark>80 or cmark=80 GROUP BY sid) a WHERE b>2 OR b=2)


-- 
-- – 14、查询至少有一门课与张三同学所学相同的同学的学号和姓名
SELECT sname,sid FROM student WHERE sid not in(
SELECT sid FROM mark WHERE cid NOT in(
SELECT cid FROM mark WHERE sid=(
SELECT sid FROM student WHERE sname='张三')) )

-- 
-- – 15、没有选修“数学”课的学生的姓名
SELECT sname FROM student WHERE sid NOT in(
SELECT sid FROM mark WHERE cid=(
SELECT cid FROM course WHERE cname='数学'))

-- 
-- – 16、查询个人总成绩小于平均总成绩的学生姓名
SELECT sname FROM student WHERE sid in(
SELECT sid FROM (
SELECT sid,SUM(cmark) b FROM mark GROUP BY sid) a WHERE b>(
SELECT AVG(b) from (
SELECT sid,SUM(cmark) b FROM mark GROUP BY sid) a ))

-- 
-- – 17、用子查询实现张三在其各科成绩中最高分成绩所对应的课程号和成绩
SELECT cid,cmark FROM mark WHERE cmark=(
SELECT MAX(cmark) FROM mark WHERE sid=(
SELECT sid FROM student WHERE sname='张三') ) AND sid=(
SELECT sid FROM student WHERE sname='张三')

-- 
-- – 18、找出张三的最高分和最低分以及对应的课程名
SELECT cname,cmark FROM course c,mark m WHERE c.cid=m.cid AND sid=(SELECT sid FROM student WHERE sname='张三') AND m.cid in(
SELECT cid FROM mark WHERE (cmark=(
SELECT MAX(cmark) x FROM mark WHERE sid=(SELECT sid FROM student WHERE sname='张三')) OR cmark=(
SELECT MIN(cmark) x FROM mark WHERE sid=(SELECT sid FROM student WHERE sname='张三'))) AND sid=(
SELECT sid FROM student WHERE sname='张三'))

-- 5
-- – 19、哪些学生的各科成绩均高于张三
SELECT sname FROM student WHERE sid NOT in(
SELECT DISTINCT sid FROM mark ,(
SELECT cmark c1,cid FROM mark WHERE sid=(
SELECT sid FROM student WHERE sname='张三')) a WHERE mark.cid=a.cid AND cmark<c1 ) AND sid != (SELECT sid FROM student WHERE sname='张三')

大坤...
关注
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值