MYSQL 基础总结

学习笔记

【mysql 是不区分大小写的，要区分可以用相应的函数；所有标点符号全是英文状态下的】

 

一、基础部分

//创建数据库

Create  database  database_name;

//使用数据库

Use  database_name;

【字段类型】

数值型：int  float  double

字符串：定长字符串（char（n））：实际长度超过n，截断；不足n，用空格补齐

可变字符串（varchar（n））：这里的n相当于给字符串设了个最大值，实际该多大就多大，不超过n就行了

长文本：text

日期时间：date   datetime

//创建表

Create  table table_name

(字段1  字段类型 约束，字段2  字段类型 约束。。。。) charset=utf8;

1、列【字段】操作

//添加表字段

Alter  table table_name add 字段1 字段类型 约束，.....；

//删除表字段

Alter  table table_name drop [column] 字段1 字段类型 约束，........；

//修改表字段类型

Alter  table table_name modify字段1 字段类型 约束，......；

//修改字段名和字段类型【一般选择change】

Alter  table table_name change 旧字段名  新字段名 字段类型 字段约束

 

2、行【记录】操作

【注意事项】 ：除数值型（float、double、int等）、布尔型等具体的值用【单引号】括起来

//增加记录：字段列表可以省略

Insert  into  table_name[(字段列表)]

values ( 字段值1，字段值2...... ),( 字段值1，字段值2...... )........

//更新记录

Update  table_name  set  字段名=字段值，.......

//删除记录

1、Delete  from  table_name  [where 条件]

【注意：会记录日志，删除的记录有办法恢复（delete from 中间不要写任何东西）】

2、Truncate  table  table_name （不能写where条件）

【注意：不会记录日志，删除的记录也不能恢复；

不能用于有外键约束引用的表；而delete可以

可以重置“auto_increment [自增长]”的值；delete不行】

i. 

 

重点内容：【查询DQL】

1、基本语法：

Select [distinct] * (字段列表)(聚合函数)     -- 投影

From  table_name [ as ] 表别名

[Where 条件]        -- 选择

[group by  分组字段]   

[having  分组条件]

[order by  ASC [DESC] ]

[limit m,n]

【说明】

执行顺序：from---where---group by---select---having---order by---limit

A、投影：针对列    选择：针对行 ；

B、Select 后面如果使用的是【聚合函数】，那么除了“分组字段”外就不能有其他字段了；

C、* 代表table_name中的所有字段，如果要用，紧跟select后，当然，也可以自己写要投影的字段；

D、Where 条件 可以省略，注意where条件后面不能使用【聚合函数】,但是时间日期函数还是可以使用的，这里如果要使用【聚合函数】作为条件筛选，那么可以写在“having”后面，当然，where中可以使用的having中也能使用；

E、如果有group by & order by那么“分组”一定写在“排序”之前,“having”分组条件紧跟group by 后

F、排序默认是ASC ,即升序，降序用DESC

G、limit  m,n ||  limit  n 【表示从m+1行开始查询，n代表要查询的行数】

H、 distinct : 去重复记录

I、给“表”或“字段”取别名的方式：as 别名 【这里as也可以省略，一般不建议省略，主要是查询错误的时候不方便，如果省略了，可能不知道那个别名到底是“已有的字段名”还是别名，不过你自己能分清楚也没关系】

 

 

二、基础知识介绍

1、通配符

%  【0个或多个任意字符】

 _(下划线)【单个字符】

2、模糊查询

  比较运算符：>=  <=  =  !=  >  <

关系运算符：not  and  or

A、Like / not like : 与通配符连用

Eg.找出姓“王”的同学

Select  *  from  student  where  sname  like  ‘王%’ ;

B、(not) between 下限  and 上限  ======  >= 下限  and <= 上限

C、(not) in( ) ======== 条件1  or  条件2  or  条件n

D、is null  ||  is  not  null   -- 判断是否空值

 

3、聚合函数：【可以写在“having”后，但不能写在‘where’后】

Count（常数）：统计 【非空分组字段】 || 【表记录】 的个数

Sum（字段名）：求和

Avg（字段名）：求平均值

Max（字段名）：最大值   min（字段名）：最小值

 

其他函数：year（）：返回年份

Month（）：返回月份

Day（）：返回天

三、表连接

【横向连接】

1、内连接：

【第一种方式】

Select * from  表1  as  A

JOIN 表2  as  B

On A.sno = B.SNO【一般是a 表的主键 匹配 b 表的外键】

【第2种方式】

Select *

From 表1，表2

where 表1.sno = 表2.sno

【多个表就不断的加“join  on” 或者是 from后面加表名称，然后“where” 后面把连接条件写好】

2、外连接

【左外连接：】

结构：表1  left  join 表2

查询结果以【表1】为准，即【表1】中的字段全部显示，‘ 表2 ’的数据匹配【表1】的才会显示。【表1】在left  join 左边，所以以【表1】为准

 

【右外连接：】

结构：表1  right  join 表2

查询结果以【表2】为准，即【表2】中的字段全部显示，‘ 表1 ’的数据匹配【表2】的才会显示。【表2】在 right  join 右边，所以以【表2】为准

 

 

【纵向连接】

Union

Eg.

-- 31、查询所有教师和同学的name、sex和birthday.

SELECT tname AS tsname,tsex AS tssex,tbirthday AS tsbirthday FROM teacher

UNION SELECT sname,ssex,sbirthday FROM student;-- 纵向联合

 

 先就这么多，很多概念性的东西没有写进来，如果不理解可以在【菜鸟教程】等网站学习；

有一些例子，欢迎指正！

CREATE DATABASE day4;
USE day4;


CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))CHARSET=utf8;

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)CHARSET=utf8;

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)CHARSET=utf8;

CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL)CHARSET=utf8;

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);

 DELETE 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);


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

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','助教','电子工程系');



-- 题目：
-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname,ssex,class FROM student;

-- 2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart FROM teacher;

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

-- 4、 查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;

-- 5、 查询Score表中成绩为85，86或88的记录。
SELECT * FROM score WHERE degree IN(85,86,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降序查询Score表的所有记录。
SELECT * FROM score ORDER BY cno,degree DESC;

-- 9、 查询“95031”班的学生人数。
SELECT COUNT(1) AS stunum,class FROM student WHERE class='95031' GROUP BY class;

-- 10、查询Score表中的最高分的学生学号和课程号。
SELECT sno,cno FROM score ORDER BY degree DESC LIMIT 1;

-- 11、查询‘3-105’号课程的平均分。
SELECT cno,AVG(degree) AS average FROM score WHERE cno='3-105';

-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT sno,AVG(degree) 
FROM score
WHERE cno LIKE '3%'
GROUP BY sno
HAVING COUNT(sno)>5 

-- 检查代码：select * from score order by sno

-- 13、查询最低分大于70，最高分小于90的Sno列。
SELECT sno FROM score GROUP BY sno HAVING MIN(degree)>70 AND MAX(degree)<90

-- 14、查询所有学生的Sname、Cno和Degree列。
SELECT a.SNO,b.CNO,b.DEGREE
FROM student AS a
JOIN score AS b
ON a.sno=b.sno

-- 15、查询所有学生的Sno、Cname和Degree列。
SELECT b.SNO,a.cname,b.DEGREE
FROM course AS a
JOIN score AS b
ON a.cno=b.cno


-- 16、查询所有学生的Sname、Cname和Degree列。
SELECT b.SName,a.cname,c.DEGREE
FROM course AS a,student AS b,score AS c
WHERE a.cno=c.cno AND b.sno=c.sno;

-- 17、查询“95033”班所选课程的平均分。
SELECT AVG(degree) AS average
FROM score
WHERE cno =(SELECT cno FROM course WHERE course.CNO=score.cno)
AND sno =(SELECT sno FROM student WHERE student.sno = score.sno);

-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT * FROM score WHERE degree>(SELECT degree FROM score WHERE cno='3-105' AND sno='109');

-- 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
SELECT * FROM score
GROUP BY cno
HAVING COUNT(cno)>1 AND degree NOT IN(SELECT MAX(degree) FROM score);

-- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * FROM score WHERE degree>(SELECT degree FROM score WHERE cno='3-105' AND sno='109');

-- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT sno,sname,sbirthday
FROM student
WHERE YEAR(SBIRTHDAY) =(SELECT YEAR(SBIRTHDAY) FROM student WHERE sno='108');
-- where 条件中是可以使用时间函数的

-- 23、查询“张旭“教师任课的学生成绩。
SELECT degree
FROM score
WHERE cno IN(SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher WHERE tname='张旭'))

-- 24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT tname
FROM teacher
WHERE (SELECT COUNT(1) FROM score GROUP BY cno HAVING COUNT(1)>5)

-- 25、查询95033班和95031班全体学生的记录。
SELECT *
FROM student
WHERE class IN('95033','95031');

-- 26、查询存在有85分以上成绩的课程Cno.
SELECT DISTINCT cno
FROM score
WHERE degree>85;

-- 27、查询出“计算机系“教师所教课程的成绩表。
SELECT *
FROM score
WHERE cno IN(SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher WHERE depart='计算机系'));

-- SELECT * FROM course

-- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT tname,prof
FROM teacher
WHERE depart IN('计算机系','电子工程系')
GROUP BY prof

-- 29、查询选修编号为“3-105“课程
-- 且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
SELECT * 
FROM score AS a
WHERE cno='3-105' AND degree>(SELECT degree FROM score AS b WHERE a.sno = b.sno AND cno = '3-245')
ORDER BY degree DESC;
-- 30、查询选修编号为“3-105”
-- 且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
SELECT * 
FROM score AS a
WHERE cno='3-105' AND degree>(SELECT degree FROM score AS b WHERE a.sno = b.sno AND cno = '3-245');



-- 31、查询所有教师和同学的name、sex和birthday.


SELECT tname AS tsname,tsex AS tssex,tbirthday AS tsbirthday FROM teacher
UNION SELECT sname,ssex,sbirthday FROM student;-- 纵向联合


-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT tname AS tsname,tsex AS tssex,tbirthday AS tsbirthday FROM teacher WHERE tsex='女'
UNION SELECT sname,ssex,sbirthday FROM student WHERE ssex='女';-- 纵向联合

-- 33、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT * FROM score AS s1
WHERE degree <(SELECT AVG(degree) FROM score AS s2 WHERE s1.cno=s2.cno GROUP BY cno)

-- 34、查询所有任课教师的Tname和Depart.
SELECT tname,depart
FROM teacher 
WHERE tno IN(SELECT tno FROM course)

-- 35? 查询所有未讲课的教师的Tname和Depart. 
SELECT tname,depart
FROM teacher 
WHERE tno NOT IN(SELECT tno FROM course)


-- 36、查询至少有2名男生的班号。
SELECT class
FROM student
GROUP BY class
HAVING COUNT(1)>=2

-- 37、查询Student表中不姓“王”的同学记录。
SELECT * FROM student WHERE sname NOT LIKE '王%';

-- 38、查询Student表中每个学生的姓名和年龄。
SELECT ssex,(YEAR(NOW())-YEAR(SBIRTHDAY)) AS sage FROM student;


-- 39、查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(sbirthday) AS maxb, MIN(sbirthday) AS minb FROM student;

-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM student ORDER BY class DESC,(YEAR(NOW())-YEAR(SBIRTHDAY)) DESC;

-- 41、查询“男”教师及其所上的课程。
SELECT t.*,cname
FROM teacher AS t LEFT JOIN course AS c ON t.TNO=c.tno 
WHERE t.TSEX='男';
        
-- 42、查询最高分同学的Sno、Cno和Degree列。
SELECT * FROM score ORDER BY degree DESC LIMIT 1;

-- 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 student AS stu
JOIN score s ON s.sno=stu.sno
JOIN course c ON c.cno=s.cno
WHERE cname='计算机导论' AND ssex='男'

　　

若想更加深入的了解，详细可以看：http://www.runoob.com/mysql/mysql-tutorial.html