数据库学习代码_大数据系统

SELECt NOW();

--四大完整性

--域完整性、实体完整性、引用完整性、自定义完整性

--五大约束 CONSTRAINT

--主键约束 PRIMARY KEY PK_

--外键约束 FOREIGN KEY FK_

--默认约束 DEFAULT KEY DF_

--唯一约束 UNIQUE KEY UQ_

--检查约束 CHECK KEY CK_

--参考:REFERENCES

--新建数据库

CREATE DATAbase newdream_T80;

--删除数据库

DROP DATAbase newdream_T80;

--使用数据库

USE newdream_T80;

--新建表1：学生信息表

--方式1：在字段后面加约束

CREATE TABLE students
(
SCode INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
SName CHAr(10) NOT NULL,
SAddress VARCHAr(50) DEFAULT '湖南长沙',
SGrade FLOAT,
SEmail VARCHAr(50)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--方式2：在表后面加约束

CREATE TABLE students1
(
SCode INT NOT NULL AUTO_INCREMENT ,
SName CHAr(10) NOT NULL,
SAddress VARCHAr(50) DEFAULT '湖南长沙',
SGrade FLOAT,
SEmail VARCHAr(50),
PRIMARY KEY(SCode)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--在表外加约束

CREATE TABLE students2
(
SCode INT,
SName CHAr(10) NOT NULL,
SAddress VARCHAr(50) DEFAULT '湖南长沙',
SGrade FLOAT,
SEmail VARCHAr(50)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--表外加约束 ConSTRAINT 约束

--修改表：加约束

ALTER TABLE students2
ADD ConSTRAINT pk_scode_2 PRIMARY KEY(SCode);

--修改地址的默认值

ALTER TABLE students2
ALTER COLUMN SAddress SET DEFAULT 'changsha';

--显示表结构

DESC students

--建表信息

SHOW CREATE TABLE students;

--删除表

dorp TABLE students2;

--alter 为了需求的变更，要写升级的SQL脚本

--加一个性别字段新需求

ALTER TABLE students
ADD ssex INT CHECK(ssex=1 OR ssex=0);

--新增一个时间字段，并取当前时间为默认值

ALTER TABLE students ADD CreateTime DATETIME DEFAULT NOW();
ALTER TABLE students ADD CreateTime DATETIME DEFAULT '2017-01-01';

--删除字段

ALTER TABLE students
DROP ssex;

--改字段名字

ALTER TABLE students
CHANGE ssex sex INT;

ALTER TABLE students
CHANGE sex ssex INT;

--改字段类型

ALTER TABLE students
MODIFY sname CHAr(20);

--改回

ALTER TABLE students
MODIFY sname CHAr(10);

------------改表名-----------------

RENAME TABLE `students2` TO student3;

-------------------------------------------------------

------ 约束------

--邮箱加check约束 %表示任意字符

ALTER TABLE students
ADD ConSTRAINT ck_semail
CHECK(semail LIKE '%@%');

--添加性别约束

ALTER TABLE students
ADD ConSTRAINT CK_ssex CHECK( ssex BETWEEN 0 AND 1);

--删除表：

DROP TABLE students1;
DROP TABLE students2;

---------------------------------------

--创建表2：course

CREATE TABLE course
(
Courseid INT PRIMARY KEY,
CourseName NVARCHAr(50)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--创建表3：

CREATE TABLE score (
ScodeID INT(11) NOT NULL AUTO_INCREMENT,
Studentid INT(11) NOT NULL,
Courseid INT(11) NOT NULL,
score SMALLINT(6) DEFAULT NULL,
PRIMARY KEY (`ScodeID`),
ConSTRAINT `score_ibfk_1` FOREIGN KEY (`Studentid`) REFERENCES `students` (`SCode`),
ConSTRAINT `score_ibfk_2` FOREIGN KEY (`Courseid`) REFERENCES `course` (`CourseId`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--REFERENCES 参考

注意：1. 在从表上建外键去关联主表

2. 从表的数据必须来源于主表

--表一插入数据:学习信息表

INSERT INTO students(sname,saddress) VALUES('张三','上海');
INSERT INTO students(SName,SAddress,SGrade,SEmail,ssex)
VALUES('李四','上海',4,'ls@dream.com',0);
INSERT INTO students(SName,SAddress,SGrade,SEmail,ssex)
VALUES('王五','北京',4,'ww@dream.com',1);
INSERT INTO students(SName,SAddress,SGrade,SEmail,ssex)
VALUES('赵六','深圳',4,'zl@dream.com',0);
INSERT INTO students(SName,SAddress,SGrade,SEmail,ssex)
VALUES('田七','广州',4,'tq@dream.com',1);

--表2：课程表插入数据

INSERT INTO course(Courseid,CourseName) VALUES(1,'语文');
INSERT INTO course(Courseid,CourseName) VALUES(2,'数学');
INSERT INTO course(Courseid,CourseName) VALUES(3,'测试');
INSERT INTO course(Courseid,CourseName) VALUES(4,'英语');

--表三输入数据

--1. 科目1成绩要求有人不及格

INSERT INTO score(Studentid,CourseID,score)VALUES(1,1,98);
INSERT INTO score(Studentid,CourseID,score)VALUES(2,1,43);
INSERT INTO score(Studentid,CourseID,score)VALUES(3,1,23);
INSERT INTO score(Studentid,CourseID,score)VALUES(4,1,86);
INSERT INTO score(Studentid,CourseID,score)VALUES(5,1,67);

--2. 科目2成绩要求有人缺考

INSERT INTO score(Studentid,CourseID,score)VALUES(1,2,65);
INSERT INTO score(Studentid,CourseID,score)VALUES(2,2,77);
INSERT INTO score(Studentid,CourseID,score)VALUES(3,2,49);

--3. 科目1补考成绩

INSERT INTO score(Studentid,CourseID,score)VALUES(2,1,88);
INSERT INTO score(Studentid,CourseID,score)VALUES(3,1,75);

--------------------------------------------------

-------数据库操作DML--------------

--1. INSERT

--插入数据

INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX)
VALUES ('张青裁','上海松江',6,'ZQC@Sohu.com',0)

--1.values和列的数量一致 ;

INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX)
VALUES ('张青裁','上海松江',6,'ZQC@Sohu.com')

--2.标识列不需要赋值 --会成功，但后面的数据从33开始

INSERT INTO Students (scode,SName,SAddress,SGrade,SEmail,SSEX)
VALUES (32,'张青裁','上海松江',6,'ZQC@Sohu.com',0);

--六大法则：类型、长度、空、重复、默认值、业务约束

--3. 类型不一致

INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX)
VALUES ('张青裁','上海松江','abv','ZQC@Sohu.com',0);

--4.长度超长

INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX)
VALUES ('张青裁1124125252367','上海松江',6,'ZQC@Sohu.com',0);

--5. 必填的不能为NULL ,sname必填

INSERT INTO Students (SAddress,SGrade,SEmail,SSEX)
VALUES ('上海松江',6,'ZQC@Sohu.com',0);
INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX)
VALUES ('','上海松江',6,'ZQC@Sohu.com',0);

--6. 重复不满足主键约束科目ID重复主键、唯

INSERT INTO course(Courseid,CourseName) VALUES(1,'历史');

一约束不能重复

--7 取默认值

INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX)
VALUES ('张青裁',DEFAULT,6,'ZQC@Sohu.com',0);
INSERT INTO Students (SName,SGrade,SEmail,SSEX)
VALUES ('张青裁',6,'ZQC@Sohu.com',0);

--8. 不满足check约束注意：mysql不检查check,通过约束字段类型要控制

INSERT INTO Students (SName,SGrade,SEmail,SSEX)
VALUES ('张青裁',6,'ZQC@Sohu.com',3);
INSERT INTO Students (SName,SGrade,SEmail,SSEX)
VALUES ('张青裁',6,'Sohu.com',1);

-----------一次插入多笔数据-----------------------

CREATE TABLE stus
(
sname CHAr(10) PRIMARY KEY,
ssex INT
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--方式1：

INSERT INTO stus VALUES('张三',18),('李四',19),('王五',20);

--方式2：使用insert….select插入结果

--把A表的数据复制到B表，B表存在

INSERT INTO stus
SELECT sname,ssex FROM students WHERe scode >=4;
SELECt * FROM stus;

--------------replace插入语法格式有三种语法格式-------------------------

--1.如果是主键，有相同的不变，不存在则增加

--假如表中的一个旧记录与一个用于PRIMARY

KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。

SELECT * FROM stus;
REPLACe INTO stus VALUES('张三',1),('李四',0),('王五',1);
REPLACE INTO stus VALUES('王八',1),('老九',0),('万年龟',1);

--2.如果不是主键，执行多次会增加多次

CREATE TABLE stus2
(
sname CHAr(10),
ssex INT
)ENGINE=INNODB DEFAULT CHARSET=utf8;
REPLACE INTO stus2 VALUES('张三',1),('李四',0),('王五',1);

--上面的语句执行3，3次都新增了

SELECT * FROM stus2

------------------

方式2：

REPLACe INTO stus
SELECT sname,ssex FROM students;
SELECt * FROM stus;
REPLACe INTO stus2
SELECT sname,ssex FROM students;
SELECt * FROM stus2;

方式3：

REPLACe INTO stus SET sname='张三',age=11;
SELECT * FROM stus;
REPLACe INTO stus2 SET sname='张三',ssex=10;

--2. UPDATE

UPDATE students SET SAddress='上海浦东',SGrade=4 WHERe saddress='上海';

--练习

--2.1 把姓名张三改成张一，同时把性别改写 0；

UPDATE students SET sname='张一山',ssex=0 WHERe sname='张三';

--2.2 把CreateTime 全部更新为当前时间

UPDATE students SET CreateTime='2017-12-19';

--2.3 把不及格的人的分数，在本身的基础上，全部加2分

UPDATE score SET score=score+2 WHERe score <60;

--2.4 把不及格的人的分数，扣-3分

UPDATE score SET score=score-3 WHERe score <60;

--3. DELETE

--3.1将students的sname,saddress导入到students1

--3.2将students1的数据全部删除

SELECT * FROM students;
SELECt * FROM students1;
INSERT INTO students1(sname,saddress)
SELECt sname,saddress FROM students;
DELETe FROM students1;

--3.2将students里面scode>5的数据删除掉

DELETe FROM students WHERe scode>5;

--3.3 将studentsd sname,saddress导入到students1

INSERT INTO students1(sname,saddress)
SELECt sname,saddress FROM students;

使用truncate再次删除

TRUNCATE TABLE students1;

将studentsd sname,saddress再次导入到students1表，注意自动填充的scode的取值

--3.4 总结一下DELETe和truncate的区别

TRUNCATE 清除数据后，自动填充的数据重置

DELETE可以部分删除也可以全部删除

TRUNCATE TABLE students1; 等同于 DELETE FROM students1;

----------------------------------------------------------------------------

--4. SELECt

--补充1：

--重点***** 主从关系

--1. 从表的数据必须来源于主表 --学号100不存在

INSERT INTO score(studentid,courseid,score) VALUES(100,3,100);

--2. 2.1 删除数据时，不能删除主表数据，导致从表数据孤立

DELETE FROM students WHERe scode=1;
DELETe FROM students WHERe scode>6;

--2.1 如果必须删除，先删除从表后删除主表数据

INSERT INTO score(studentid,courseid,score) VALUES(6,3,100);
DELETe FROM students WHERe scode=6;
--
DELETe FROM score WHERe studentid=6;
DELETe FROM students WHERe scode=6;

--3. 更新主表，如果数据已被引用，不允许，会导致从表数据孤立

UPDATe course SET courseID=5 WHERe courseid=1;
--
UPDATE course SET courseID=5 WHERe courseid=3;
UPDATE course SET courseID=3 WHERe courseid=5;

-----补充2：----备份表-----------

--1. 把A表的数据备份到B表，B表不存在

--1.1 表结构和数据全部备份

CREATE TABLE stu_1
AS
SELECT * FROM students;
SELECt * FROM stu_1;

--1.2 备份部分字段的数据

CREATE TABLE stu_2
AS
SELECt scode,sname,saddress FROM students;

SELECt * FROM stu_2;

--1.3 只备份表结构

CREATE TABLE stu_3
AS
SELECt * FROM students WHERe 1=2;
SELECt * FROM stu_3;

--2. 把A表的数据备份到B表，B表存在

INSERT INTO stu_3
SELECt * FROM students;

小结：

INSERT

UPDATe

DELETE

SELECT

---------第三章数据查询（一）-----

--一、单表检查查询

--1.查询表中全部数据

SELECT * FROM students;

--2.查询表中特定字段

SELECt scode,sname,saddress FROM students;

--3.为字段取别名

SELECt scode AS 学号,sname 姓名,saddress 地址 FROM students;
SELECt scode AS '学号',sname '姓名',saddress '地址' FROM students;

--4.为表取别名

SELECt a.* FROM students a;

--5.为字段指定表名

SELECt a.scode,a.sname FROM students a;

--6.为表名指定库名

SELECt newdream_t80.`students`.`SCode` FROM newdream_t80.students;

--7.使用常量进行查询常量列，常量不变的值

SELECt scode AS 学号,
sname 姓名,
saddress AS '地址',
'新梦想软件测试' AS '学校',
'中国' AS '国家'
FROM students;

---二、order by排序

--1. 成绩升序

SELECt *
FROM score
ORDER BY score ASC;

--2. 成绩降序

SELECt * FROM score ORDER BY score DESC;

--3. 2个自段排序,如果分数相同，按学号升序

SELECt *
FROM score
ORDER BY score DESC,studentid ASC;

--4. 求科目1考试前3名的成绩

SELECt *
FROM score
WHERe courseid=1
ORDER BY score DESC
LIMIT 3;

--三、Limit关键字限制

SELECt * FROM students;

--第一个参数是从什么位置开始去，起始值为1，第二个数为去的数量

SELECt * FROM students LIMIT 0,2;
SELECt * FROM students LIMIT 1,2;

--从第3笔数据到最后, 最后那个数取足够大，大于总的数量

SELECt * FROM students LIMIT 2,1000;

--取前3笔

SELECt * FROM students LIMIT 3;

--四、distinct关键字去重

--1.查询科目1参加考试的人员的学号，如果重考，取一次

SELECt studentid FROM score WHERe courseid=1;

--2.去重

SELECt DISTINCT(studentid) FROM score WHERe courseid=1;

--3.思考重考的取一笔？重考的都显示不？

SELECt DISTINCT(studentid),score FROM score WHERe courseid=1;

--3.2 再看看这句

SELECt studentid,score FROM score WHERe courseid=1;

--思考：如果重考，取分数高那一笔怎么处理？

--作业:如果表里面有二笔相同的数据，删除一笔（重考，删除分数低的）

上交：建表、插入重复数据、删除其中一笔的SQL语句

--五、带条件的查询

-----条件比较----------------------

SELECt * FROM score WHERe score <60;

----指定范围查询-BETWEEN-AND----------------

--查成绩良 60-80分注意：包括边界

SELECt * FROM score WHERe score BETWEEN 60 AND 79;
--及格
SELECt * FROM score WHERe score BETWEEN 60 AND 100;
--不及格
SELECt * FROM score WHERe score NOT BETWEEN 60 AND 100;

---------------指定范围查询---IN

SELECt * FROM students;
SELECt * FROM students WHERe saddress IN('北京','深圳','广州');

--in子查询

--查询科目2参加考试的人员姓名

SELECt sname
FROM students
WHERe scode IN (SELECt Studentid FROM score WHERe courseid=2);

--查询科目2,缺考的人员姓名

SELECt sname
FROM students
WHERe scode NOT IN (SELECt Studentid FROM score WHERe courseid=2);

----匹配字符查询---LIKE----- 正则表达式----

--1.查询是上海人的学生

SELECt * FROM students WHERe saddress LIKE '%上海%';

--2.查询张同学

SELECt * FROM students WHERe sname LIKE '张%';
SELECt * FROM students WHERe sname RLIKE '^张';
SELECt * FROM SCORE WHERe SCORE RLIKE '^[20-80]';
SELECt * FROM students WHERe sname RLIKE '^[张李]';
SELECt * FROM students WHERe sname RLIKE '山$';

----是否为空----IS NULL------------------------

--------------准备数据-------------------

CREATE TABLE testNULL
AS
SELECt * FROM students WHERe 1=2;

--插入部分数据

INSERT INTO testNULL(scode,sname,saddress)
SELECt scode,sname,saddress FROM students;

--更新数据为'' NULL

UPDATe testNULL SET saddress='' WHERe saddress LIKE '上海%';
UPDATE testNULL SET sgrade=2 WHERe scode >=3;
UPDATE testNULL SET saddress=NULL WHERe scode =5;

----------------------------------------------------------------

SELECT * FROM testNULL;
SELECt * FROM testNULL WHERe sgrade IS NULL;
SELECt * FROM testNULL WHERe sgrade IS NOT NULL;
SELECt * FROM testNULL WHERe saddress='';
SELECt * FROM testNULL WHERe saddress!='';

思考？查询没有的地址的数据

SELECt * FROM testNULL WHERe saddress IS NULL ;
SELECt * FROM testNULL WHERe saddress IS NOT NULL ;
SELECt * FROM testNULL WHERe saddress IS NOT NULL AND saddress='';

SELECt * FROM testNULL WHERe saddress = NULL; //错误 查不到数据
SELECt * FROM testNULL WHERe saddress <=> NULL; //正确，可以查到数据

-----多个查询条件-逻辑表达式------

SELECt * FROM students;
SELECt * FROM course;
SELECt * FROM score;

--------补充-------------

1.备份方式1

--转储为sql语句文件，复制到需要还原的数据库上，运行sql文件

2.方式2：再数据库新建一个备份时间点，可以直接备份。还原

操作1.1:备份数据

1.2 删除数据

delete from score;
select * from score;

1.3 还原数据库，然后查询数据

select * from score;

---------------------------------------------------

---------------第四章--查询（二）--------

提问：1. 集合函数有几个，分别是？

2. group by ,having

一、数学函数

1. 求绝对值

select ABS(-2) as 取绝对值;

2. 截取多少位小数

select TRUNCATE(123.456,2)；

3.四舍五入

select ROUND(123.456,2);
select ROUND(123.454,2);

刘广语录：处理一个事情，不是我们记得多少知识，而是我们能调用多少知识，知道到那里找

二、字符串函数

1.连接字符串

select CONCAt('新','梦','想');

2.使用间隔符号连接字符串

select CONCAT_Ws('_','新','梦','想');

3.左边截取

select LEFt('liuguang',3);

4.右边截取

select right('liuguang',5);

思考：如果要取 gua怎么办

select right(left('liuguang',6),3);

5.去掉左边空格

6.去掉右边空格

7.去掉二边的空格

select ' 新梦想 ';
select LTRIm(' 新梦想 ');
select CONCAt(LTRIm(' 新梦想 '),'软件测试');
select CONCAt(RTRIm(' 新梦想 '),'软件测试');
select CONCAt(TRIm(' 新梦想 '),'软件测试');

8.求长度

SELECT LENGTH('LIUGUANG');

9.大写变小写

SELECT LOWER('LIUGUANG');

10.小写变大写

SELECT UPPER('LiuGuang');

三、日期和时间函数

1.返回当前日期+时间

select now();

2.返回当前日期

select CURDATE();
select CURRENT_DATE();

3.返回当前时间

select CURTIME();
select CURRENT_TIME();

4.时间加

我们还有几个月毕业？

select DATE_ADD(now(),INTERVAL 3 MONTH)
select DATE_ADD(now(),INTERVAL 4 MONTH)

3年前我们在做什么？

select DATE_ADD(now(),INTERVAL -3 YEAR)

5.时间减注意：时间是将来为大，前减后

--求自己活了多少天了

--求女朋友谈恋爱谈了多少天了

--求我们活到100岁还能活多少天

SELECT DATEDIFF("2017-12-21",'1984-10-04');
SELECT DATEDIFF('2084-10-04','2017-12-20');

刘广语句：人生百年，不过三万多天，睁眼闭眼就没了，万古长空，一朝风月！

6.提取小时

select HOUR(NOW())

7.提取星期

select DAYNAME(NOW());

8.提取月份

select MonTHNAME(CURRENT_DATE())

9.获取当前时间戳为

时间戳：是一种时间表示方式，定义为从格林威治时间1970年01月01日00时00分00秒起至现在的总秒数取统一的时间，被广泛时间，例如：微信支付的参数;

select unix_timestamp(now());

四、系统信息函数

1.当前数据库名

select DATAbase();

2.当前客户的连接ID

select CONNECTION_ID();

3.当前登陆用户名

select USER();
select SYSTEM_USER();

4.MySQL服务器的版本

SELECT VERSION();

---------系统转换函数----

•二进制，同带binary前缀的效果 : BINARY

•字符型，可带参数 : CHAr()

•日期 : DATE

•时间: TIME

•日期时间型 : DATETIME

•浮点数 : DECIMAL

•整数 : SIGNED

•无符号整数 : UNSIGNED

--ConVERT(expr,type)
--CAST(expr AS type)

5.1 字符转数字

select '123'+456; //自动转 从字符转数字
select '123a'+456; //不能有字母
select ConVERT('123',SIGNED)+456; //注意一下，不能用int

5.2 数字转字符

select concat('123','456');
select CONCAt(ConVERT(123,char(3)),'456');

5.3 字符转日期

select '2017-12-21';

五、聚合函数

--数据数据库的重点之一：聚合函数+分组，被广泛应用于报表查询

1. 五大聚合函数作用：从一个聚在一起的集合里面查询数据

select avg(score) as '平均分',
min(score) as '最低值',
max(score) as '最高分',
count(*) as '参考人数',
sum(score) as '总分'
from score
where courseid=1;

- 求所有人的平均分

SELECt avg(score) from score;--
select sum(score) from score where courseid=1;

--求科目1的考试记录数，可以重考算一次

select count(DISTINCT(studentid)) from score where courseid=1;

---------------------------------------

2. group by 作用：按指定的字段分组

--注意：非集合列必须包含在group by子句里面

2.0 求每科的记录数

select courseid,count(*)
from score
group by courseid;

2.1 求每个科目的：平均分

select courseid,AVG(score)
from score
group by courseid;

2.1 求每同学的平均分，总分

select studentid,avg(score),sum(score)
from score
group by studentid;

2.3 求每个人每科的成绩，如果重考了，取最高分

SELECt studentid,courseid,max(score)
from score
group by studentid,courseid
order by courseid,studentid;

3. HAVINg 子句作用：过滤分组的数据

3.1 求科目1重考人员的学号

select studentid,max(score)
from score
where courseid=1
group by studentid
having count(*)>=2
order by studentid asc;

3.2 求科目1重考人员的姓名

select sname
from students
where scode in
(select studentid
from score
where courseid=1
group by studentid
having count(*)>=2
order by studentid asc
);

总结：

1. 数学函数、字符函数、时间函数、系统函数

2. 五大聚合函数、group by分组 having 子句

---------第五章高级查询-------

一、主外键关系

1. 新增数据时间

从表里面的数据必须来源于主表，例如：成绩表的学号，必须来源于学生信息表

2. 修改数据的时候

2.1 修改主表的记录，如果改记录被从表引用了，不能修改；

修改主表的记录，如果改记录没有被从表引用了，可以直接删除；

2.2 修改从表的记录，更新后的值，必须来源于主表

3. 删除数据的时候

3.1 删除主表数据，如果给从表引用了，不允许删除；

3.2 删除主表数据，如果没有被引用，可以直接删除

3.3 如果主表数据被引用了，非删可不·，先删除从表再删除主表的数据

二、连接查询

什么是连接查询？作用：从多个表查询数据

内联的原理：二个表的地位一样，做笛卡尔集 4*5=5*5

select * from students a,score b;
select * from score b,students a;
select * from students a
inner join score b on 1=1;
select * from score a
inner join students on 1=1;

-----------------------------------------

1. 等值与非等值连接查询（内联）

1.1 2表联查：查询科目1的学号、姓名、分数；

select a.scode as 学号,a.sname as 姓名,b. score as 分数
from students a,score b
where a.scode=b.studentid;

1.2 3表联查：查询所有科目的成绩，包括：学号、姓名、科目名称、分数

select a.scode as 学号,sname as 姓名, b.coursename as 科目名称,c.score as 分数
from students a,course b,score c
where a.scode=c.studentid and b.courseid=c.courseid;

1.3 使用inner join 做内联

SELECt a.scode as 学号,a.sname as 姓名,b. score as 分数
from students a
INNER JOIN score b on a.scode=b.studentid;
select a.scode as 学号,sname as 姓名, b.coursename as 科目名称,c.score as 分数
from students a
INNER JOIN score c on a.SCode=c.Studentid
INNER JOIN course b on b.Courseid=c.Courseid;

-----------------------------------------------------------------

3. 外连接重点：左表的数据必须至少出现一次

select * from students;
select * from score where courseid=2;

--3.1 左连接

3.1.1 求所有人科目2的成绩，如果缺考了，显示了null

select a.scode as 学号, a.sname 姓名, b.score 分数
from students a
LEFT JOIN score b on b.courseid=2 and a.scode=b.studentid ;

3.1.2 求科目2缺考人员名单

select a.scode as 学号, a.sname 姓名, b.score 分数
from students a
LEFT JOIN score b on b.courseid=2 and a.scode=b.studentid
where b.score is null;

--3.2 右连接注意：选择什么连接方式是根据业务需要来的

--作用：右连接表的数据，至少出现一次

SELECt a.scode as 学号, a.sname 姓名, b.score 分数
from score b
RIGHT JOIN students a on b.courseid=2 and a.scode=b.studentid;

--------------------------------------------------------------

2. 自身连接查询自己和自己比

2.1 查询考试成绩，如果重考的，就取分数高的那一笔

select a.*
from score a,score b
where a.studentid=b.studentid
and a.courseid=1 and a.score>b.score

1.5 难点选学：[表名1.] BETWEEN [表名2.] AND [表名2.]

1.新建一个成绩级别表

create table levels
(
levels char(2),
beginScore int,
endScore int
)

2. 插入数据

insert into levels VALUES('A',90,100);
insert into levels VALUES('B',80,89);
insert into levels VALUES('C',60,79);
insert into levels VALUES('D',0,59);
select * from levels;

--连接

select a.studentid as 学号, a.score 分数, b.levels as 等级
from score a
INNER JOIN levels b on a.score between beginScore and endScore;

总结：

1. 连接查询：多个表查数据

2. 内联：这个表的地位一样,二个表要所有数据都要连接相乘

3. 左外联：左连表的数据至少要出现一次

-----------------------------------------------

二、子查询

什么是子查询：有多个select,后一个select叫子查询

1. 标量子查询返回一个值

-1.1 求分数为85分的学生姓名（注意要唯一的分数）

select studentid from score where score=85;
select sname from students where scode =4;
-------------
select sname
from students
where scode =(select studentid from score where score=85);

2. 列子查询 in子查询同时返回多笔数据,从聚合里面取

2.1 求科目2参加考试的人员姓名

select sname
from students
where scode in (select studentid from score where courseid=2);

2.2 求科目2缺考的名单

select sname
from students
where scode not in (select studentid from score where courseid=2);

3. 行子查询

4. 表子查询

练习：

1. 内联

1.1 2表联查

1.2 3表联查

1.3 使用inner join改写

2. 左外联

2.1查科目2的成绩，如果缺考，显示为null

2.3查科目2缺考的人员的名单

3. 自身联

3.1 查科目1的成绩，如果重考，取高分的那一笔

4. 标量子查询

4.1 给一个分数，查姓名

5. 列子查询

5.1求科目2参加考试的人员姓名

5.2求科目2缺考的名单

6. 选学：使用 BETWEEN AND 做表连接

三、EXIST

四、派生表

五、联合查询

select *from score;

--建表后加索引

普通索引

ALTER TABLE scre
ADD INDEX index_stuid(studentid);

复合索引

ALTER TABLE students
dd INDEX index_sname_saddress(sname,saddress);

使用索引

SELECt tudentid,score
FROM score USE INDEX(index_stuid)
WHERe studentid>=3;

删除索引

DROP INDEX index_sname_saddress ON students;

--------改字符集-----------------

SHOW VARIABLES LIKE 'character_set_%';
SET character_set_database = utf8 ;

数据库学习代码

大数据系统相关栏目本月热门文章