MySQL基础1
以下为在初次学习数据库有关知识时学习到的一些基本操作（部分）
创建表 create table tb_name;
查看存储引擎 show engines;
创建数据库 create database db_name;
可以查看choose数据库的相关信息 show create database choose;
删除数据库stu drop database stu;
显示表结构 desc tb_name;
创建部门信息表tb_dept
create table tb_dept(
id int(11) primary key,
name varchar(22) not null,
location varchar(50)
);
创建一个员工信息表tb_emp，设置其字段depId为外键，引用的是tb_dept表的主键id：
非空约束 NOT NULL
创建部门信息表tb_dept，要求部门的名称不能重复
create table tb_dept(
id int(11) primary key,
name varchar(22),
location varchar(50),
constraint sth UNIQUE(name)
);
或者 create table tb_dept(
id int(11) primary key,
name varchar(22) UNIQUE,
location varchar(50)
);
定义员工信息表tb_emp，指定员工的id为自动增长列
create table tb_emp(
id int(11) primary key AUTO_INCREMENT,
name varchar(25) NOT NULL,
deptId int(11) DEFAULT 1111,
salary float,
constraint fk_emp_dept foreign key(deptId) references tb_dept (id)
);
创建部门信息表tb_dept，要求部门的名称不能重复
create table tb_dept(
id int(11) primary key,
name varchar(22),
location varchar(50),
constraint sth UNIQUE(name)
);
默认约束 定义员工信息表tb_emp，指定员工的部门编号默认为1111
create table tb_emp
id int(11) primary key,
name varchar(25) NOT NULL,
deptId int(11) DEFAULT 1111,
salary float,
constraint fk_emp_dept foreign key(deptId) references tb_dept(id)
）;
修改数据表
将数据表tb_dept改名为tb_deptment
alter table tb_dept RENAME tb_deptment;
show tables;
将数据表tb_deptment中name字段的数据类型由varchar(22)改为varchar(30)
alter table tb_deptment MODIFY name VARCHAR(30);
desc tb_department；
将数据表tb_department中的location字段名改为loc，数据类型保持不变
alter table tb_deptment CHANGE location loc varchar(50);
将数据表tb_department中的loc字段名改为location，数据类型变varchar(60)
alter table tb_deptment CHANGE loc location varchar(60);
在数据表tb_department中添加一个int类型的字段managerId(部门经理编号)
alter table tb_deptment ADD managerId int(10);
在数据表tb_department中第一列添加一个int类型的字段column2
alter table tb_deptment ADD column2 int(11) FIRST;
在数据表tb_department中name列后添加一个int类型的字段column3
alter table tb_deptment ADD column3 int(11) AFTER name;
删除数据表tb_department中的column2字段
alter table tb_deptment DROP column2;
修改数据表tb_department中的column1字段为表的第1个字段
alter table tb_deptment MODIFY column1 varchar(12) first;
修改数据表tb_department中的column1字段插入到location字段后面
alter table tb_deptment MODIFY column1 varchar(12) AFTER location;
更改表的存储引擎。将数据表tb_deptment的存储引擎改为MyISAM
alter table tb_deptment ENGINE=MyISAM;
删除没有被关联的表。删除数据表tb_dept2
drop table if exists tb_dept2;
删除被其他表关联的主表。分几种情况：
先删除与它关联的子表，再删除父表
如果要保留子表，则只需将关联的表的外键约束条件取消，然后就可删除父表
删除被数据表tb_emp(子表)关联的数据表tb_dept2(父表)
alter table tb_emp DROP FOREIGN KEY fk_emp_dept;
drop table tb_dept2;
操作表数据
1插入记录
向XSCJ数据库的表XSB中插入如下的一行：
101101 王林 计算机 男 19900210 50
INSERT INTO XSB(XH, XM, XB, CSSJ, ZY, ZXF)
VALUES(‘101101’, ‘王林’, ‘男’,TO_DATE(‘19900210’,’YYYYMMDD’), ‘计算机’, 50);
使用SELECT语句查询是否添加了该行数据：
SELECT XH, XM, XB, CSSJ, ZY, ZXF
把一个表中的部分数据插入到另一个表中，但结果集中的每行数据的字段数、字段的数据类型要与被操作表完全一致
INSERT INTO table_name
derived_table
2修改记录
将XSCJ数据库的XSB表中学号为“101110”的学生备注列值改为“三好学生”，
UPDATE XSB
SET BZ=’三好学生’
WHERE XH=’101110’;
将XSB表中的所有学生的总学分都增加10。
UPDATE XSB
SET ZXF=ZXF+10;
将姓名为“罗林琳”的同学的专业改为“通信工程”，备注改为“转专业学习”，学号改为“101241”。
UPDATE XS
SET ZY='通信工程',
BZ='转专业学习',
XH='101241'
WHERE XM= '罗林琳';
查询XSB表中ZXF大于50同学的XH、XM和ZXF
SELECT XH, XM, ZXF
FROM XSB
WHERE ZXF>50;
查询XSB表中的所有列 SELECT * FROM XSB;
修改XSB表中计算机系同学的XH、XM和ZXF分别为学号、姓名和总学分
SELECT XH AS 学号,XM AS 姓名,ZXF AS 总学分
FROM XSB
WHERE ZY= '计算机';
As可以省略
对XSCJ数据库的XSB表只选择ZY和ZXF，消除结果集中的重复行。
SELECT DISTINCT ZY AS 专业,ZXF AS 总学分
FROM XSB;
对XSCJ数据库的XSB表选择ZY和ZXF，不消除结果集中的重复行。
SELECT ALL ZY AS 专业名,ZXF AS 总学分
FROM XSB;
查询XSB表中通信工程专业总学分大于等于42的同学的情况。
SELECT *
FROM XSB
WHERE ZY= '通信工程' AND ZXF>=42;
查询成绩表中期末成绩<60,总评成绩>=60分的同学
select * from xscj where zpcj>=60 and qmcj<60;
查询XSB表中姓“王”且单名的学生情况
SELECT * FROM XSB
WHERE XM LIKE '王_';
(插入出生年月类型yyyymmdd )
查询XSB表中不在1989年出生的学生情况
SELECT * FROM XSB
WHERE CSSJ NOT BETWEEN TO_DATE('19890101', 'YYYYMMDD')
AND TO_DATE('19891231', 'YYYYMMDD');
查询CP表中库存量为“200”“300”和“500”的情况。
SELECT *
FROM CP
WHERE KCL IN (200,300,500);
或者SELECT *
FROM CP
WHERE KCL=200 OR KCL=300 OR KCL=500;
查询XSCJ数据库中总学分尚不定的学生情况（即为空值的）
SELECT * FROM XSB
WHERE ZXF IS NULL;
子查询
在XSB表中查找1990年1月1日以前出生的学生的姓名和专业。
SELECT XM, ZY
FROM (SELECT * FROM XSB
WHERE CSSJ<TO_DATE('19900101', 'YYYYMMDD'));
查找比所有计算机系学生年龄都大的学生。
SELECT * FROM XSB
WHERE CSSJ <ALL
( SELECT CSSJ
FROM XSB
WHERE ZY= '计算机'
);
查找课程号206的成绩不低于课程号101的最低成绩的学生的学号。
SELECT XH FROM CJB
WHERE KCH = '206' AND CJ>= ANY
( SELECT CJ FROM CJB
WHERE KCH = '101'
);
查找选修了全部课程的同学姓名。
SELECT XM FROM XSB
WHERE NOT EXISTS
( SELECT *FROM KCB
WHERE NOT EXISTS
( SELECT *
FROM CJB
WHERE XH=XSB.XH AND KCH=KCB.KCH )
);
查找与101102号同学所选修课程一致的同学的学号。
SELECT DISTINCT XH FROM CJB CJ1
WHERE NOT EXISTS
( SELECT *FROM CJB CJ2
WHERE CJ2.XH ='101102' AND NOT EXISTS
( SELECT *FROM CJB CJ3
WHERE CJ3.XH= CJ1.XH
AND CJ3.KCH = CJ2. KCH)
);
在XSB表中查找1990年1月1日以前出生的学生的姓名和专业。
SELECT XM, ZY
FROM (SELECT * FROM XSB
WHERE CSSJ<TO_DATE('19900101', 'YYYYMMDD'));
查找XSCJ数据库每个学生的情况以及选修的课程情况
SELECT XSB.* ,CJB.*
FROM XSB , CJB
WHERE XSB.XH=CJB.XH;
查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。
SELECT XSB.XH, XM, KCM, CJ
FROM XSB, KCB, CJB
WHERE XSB.XH = CJB.XH AND KCB.CH = CJB. KCH
AND KCM = '计算机基础' AND CJ >= 80;
用FROM的JOIN关键字表达下列查询：查找选修了206课程且成绩在80分以上的学生姓名及成绩。
SELECT XM , CJ
FROM XSB JOIN CJB ON XSB.XH = CJB.XH
WHERE KCH = '206' AND CJ>=80;
用FROM的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。（三表连接）
SELECT XSB.XH , XM , KCM , CJ
FROM XSB
JOIN CJB JOIN KCB ON CJB.KCH = KCB.KCH
ON XSB.XH = CJB.XH
WHERE KCM = '计算机基础' AND CJ>=80;
查找不同课程成绩相同的学生的学号、课程号和成绩。
SELECT a.XH,a.KCH,b.KCH,a.CJ
FROM CJB a JOIN CJB b
ON a.CJ=b.CJ AND a.XH=b.XH AND a.KCH!=b.KCH;
A表 B表
a1 a2 b1 b2
------------ ------------
1 4 2
3
2 5 4
5
6 7 6
7
3 4 8
9
7 8
左连接
select a.a1,a.a2,b.b2
from a left outer join b
on a.a2=b.b1
执行结果为: a1
a2 b2
----------------
1 4
5
3 4
5
7 8
9
2 5
6 7
右连接
select a.a1,a.a2,b.b2
from a right outer join b
on a.a2=b.a1
执行结果为: a1
a2 b2
----------------
1 4
5
3 4
5
7 8
9
7
3
交叉连接
select a.a1,a.a2,b.b2
from a cross join b
执行结果为: a1
a2 b2
----------------
1 4
3
2 5
3
6 7
3
3 4
3
7 8
3
1 4
5
…………..
左连接右连接交叉连接具体问题
查找所有学生情况及他们选修的课程号，若学生未选修任何课，也要包括其情况。
SELECT XSB.* , KCH
FROM XSB LEFT OUTER JOIN CJB ON XSB.XH = CJB.XH;
查找被选修了的课程的选修情况和所有开设的课程名。
SELECT CJB.* , KCM
FROM CJB RIGHT JOIN KCB ON CJB.KCH= KCB.KCH;
列出学生所有可能的选课情况。
SELECT XH, XM, KCH, KCM
FROM XSB CROSS JOIN KCB;
求选修101课程的学生的平均成绩。
SELECT AVG(CJ) AS 课程101平均成绩
FROM CJB
WHERE KCH='101';
求选修101课程的学生的最高分和最低分。
SELECT MAX(CJ) AS 课程101的最高分, MIN(CJ) AS 课程101的最低分
FROM CJB
WHERE KCH='101';
求学生的总人数。
SELECT COUNT(*) AS 学生总数
FROM XSB;
求选修了课程的学生总人数。
SELECT COUNT(DISTINCT XH) AS 选修了课程的总人数
FROM CJB;
统计离散数学课程成绩在85分以上的人数。
SELECT COUNT(CJ) AS 离散数学85分以上的人数
FROM CJB
WHERE CJ>=85 AND KCH=
( SELECT KCH
FROM KCB
WHERE KCM= '离散数学'
);
将XSCJ数据库中各专业输出。
SELECT ZY AS 专业
FROM XSB
GROUP BY ZY;
求XSCJ数据库中各专业的学生数。
SELECT ZY AS 专业,COUNT(*) AS 学生数
FROM XSB
GROUP BY ZY;
求被选修的各门课程的平均成绩和选修该课程的人数。
SELECT KCH AS 课程号, AVG(CJ) AS 平均成绩,COUNT(XH) AS 选修人数
FROM CJB
GROUP BY KCH;
HAVING子句
查找XSCJ数据库中平均成绩在85分以上的学生的学号和平均成绩。
SELECT XH AS 学号, AVG(CJ) AS 平均成绩
FROM CJB
GROUP BY XH
HAVING AVG(CJ)>=85;
查找选修课程超过两门且成绩都在80分以上的学生的学号
SELECT XH AS 学号
FROM CJB
WHERE CJ>=80
GROUP BY XH
HAVING COUNT(*) > 2;
查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩
SELECT XH AS 学号,AVG(CJ) AS 平均成绩
FROM CJB
WHERE XH IN
( SELECT XH
FROM XSB
WHERE ZY= '通信工程'
)
GROUP BY XH
HAVING AVG(CJ) > =85;
将通信工程专业的学生按出生时间先后排序。
SELECT *
FROM XSB
WHERE ZY= '通信工程'
ORDER BY CSSJ;
将计算机专业学生的“计算机基础”课程成绩按降序排列。
SELECT XM AS 姓名, KCM AS 课程名, CJ AS 成绩
FROM XSB, KCB, CJB
WHERE XSB.XH=CJB.XH AND CJB.KCH= KCB.KCH
AND KCM= '计算机基础' AND ZY= '计算机'
ORDER BY CJ DESC;
查找xs表中学号最靠前的5位学生的信息。
select 学号, 姓名, 专业名, 性别, 出生日期, 总学分
from xs
order by 学号
limit 5;
--11.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。 SELECT student.Sname 学生姓名,student.Sno 学生学号,COUNT(SC.Sno) 选课门数 FROM student inner join SC ON SC.Sno=student.Sno GROUP BY student.Sname,student.Sno ORDER BY COUNT(SC.Sno) ASC
select student.sname,sc.sno,count(cno) cc
from sc inner join student
on sc.sno=student.sno
group by sno
order by cc asc;
