MySQL基础查询大全

数据表准备

1.创建学生信息表

CREATE TABLE IF NOT EXISTS tb_student(
	sid INT(20) NOT NULL,
	sname VARCHAR(32) NOT NULL,
	ssex VARCHAR(2) NOT NULL,
	sbirthday DATETIME,
	class VARCHAR(10),
	PRIMARY KEY (sid)
)ENGINE=INNODB CHARSET= utf8;

2.创建教师信息表

CREATE TABLE IF NOT EXISTS tb_teacher(
	tid INT(20) NOT NULL PRIMARY KEY,
	tname VARCHAR(32) NOT NULL,
	tsex VARCHAR(2) NOT NULL,
	tbirthday DATETIME,
	profession VARCHAR(32),
	department VARCHAR(32) NOT NULL
)ENGINE=INNODB CHARSET= utf8;

3.创建课程信息表

CREATE TABLE IF NOT EXISTS tb_course(
	cid INT(20) NOT NULL,
	cname VARCHAR(32) NOT NULL,
	tid INT(20) NOT NULL,
	FOREIGN KEY(tid) REFERENCES tb_teacher(tid),
	PRIMARY KEY (cid)
)ENGINE=INNODB CHARSET= utf8;

4.创建成绩信息表

CREATE TABLE IF NOT EXISTS tb_score(
	scid INT(20) NOT NULL,
	sid int(20) NOT NULL,
	FOREIGN KEY(sid) REFERENCES tb_student(sid),
	cid INT(20) NOT NULL,
	FOREIGN KEY(cid) REFERENCES tb_course(cid),
	PRIMARY KEY (scid),
  degree DECIMAL
)ENGINE=INNODB CHARSET= utf8;

解析：

• AUTO_INCREMENT 定义列自增1，一般为主键设置

• PRIMARY KEY 定义此列为主键,使用这个关键字被修饰的字段：唯一和非空

• FOREIGN KEY 外键约束:使用这个关键字设置某主表的外键

• UNIQUE 唯一约束：使用这个关键字被修饰的字段：唯一,可以在结尾使用unique(field1,field2)。

• DEFAULT 设置默认值

• ENGINE 设置存储引擎

• CHARSET 设置表编码

• NULL设置字段默认为 NULL ,如果不想为 NULL 则设置为非空约束 NOT NULL

MySQL表插入数据

通用语法：

1.向指定字段插入数据

INSERT INTO table_name (field1,field2,...fieldn) VALUES (value1,value2,...valuen);

2.向所有字段依次插入数据。

INSERT INTO table_name VALUES(value1，value2,...);

给上述表都插入数据方便后续查询

• 向学生信息表插入数据

 INSERT INTO tb_student VALUES('108','Mary','女','1977-09-01','20031');
 INSERT INTO tb_student VALUES('105','Lisa','女','1975-10-02','20031');
 INSERT INTO tb_student VALUES('107','Tom','男','1976-01-23','20032');
 INSERT INTO tb_student VALUES('101','Helen','男','1976-02-20','20033');
 INSERT INTO tb_student VALUES('109','Rose','女','1975-02-10','20031');
 INSERT INTO tb_student VALUES('103','Jack','男','1974-06-03','20033');

• 向教师信息表添加数据

INSERT INTO tb_teacher VALUES('201','Ann','男','1958-13-06','副教授','计算机系');
INSERT INTO tb_teacher VALUES('202','Jane','女','1959-05-07','讲师','网络工程系');
INSERT INTO tb_teacher VALUES('203','Angela','男','1962-03-05','助教','计算机系');
INSERT INTO tb_teacher VALUES('204','Rebecca','女','1967-08-04','助教','网络工程系');

• 向课程信息表添加数据

INSERT INTO tb_course VALUES('3100','计算机导论','203');
INSERT INTO tb_course VALUES('3200','操作系统','201');
INSERT INTO tb_course VALUES('3300','C语言','202');
INSERT INTO tb_course VALUES('3400','计算机网络','204');

• 向成绩信息表添加数据

INSERT INTO tb_score VALUES(1,'103','3200','86');
INSERT INTO tb_score VALUES(2,'105','3200','75');
INSERT INTO tb_score VALUES(3,'106','3300','68');
INSERT INTO tb_score VALUES(4,'103','3100','92');
INSERT INTO tb_score VALUES(5,'102','3300','88');
INSERT INTO tb_score VALUES(6,'104','3400','76');
INSERT INTO tb_score VALUES(7,'106','3400','64');
INSERT INTO tb_score VALUES(8,'105','3300','91');
INSERT INTO tb_score VALUES(9,'101','3200','78');
INSERT INTO tb_score VALUES(10,'103','3300','85');
INSERT INTO tb_score VALUES(11,'105','3200','79');
INSERT INTO tb_score VALUES(12,'106','3100','81');

MySQL 更新数据

UPDATE table_name SET field1=new_value1, field2=new_value2
WHERE clause

• 可以使用 WHERE 添加指定条件

• 可以同时更新一个或多个字段

MySQL 查询数据

通用语法：

SELECT column_name,column_name,... FROM table_name WHRER clause

• WHERE 可以指定查询条件

• 可以使用 * 查询所有字段

• 查询多个表使用 , 将表分开

查询开始！！！！！！

Example1

查询所有学生数据信息

SELECT * FROM tb_student;

SELECT sid,sname,ssex,sbirthday,class FROM tb_student;

结果如下：

Example2

查询20031班的学生信息

SELECT sid,sname,ssex,sbirthday,class FROM tb_student WHERE class='20031';

结果如下：

Example3

查询tb_score表成绩在70-80之间的信息

• 使用 BETWEEN AND 关键字

SELECT scid,sid,cid,degree FROM tb_score WHERE degree BETWEEN 70 AND 80;

• 使用 >= 和 <=

SELECT scid,sid,cid,degree FROM tb_score WHERE degree >= 70 AND degree <= 80;

结果如下：

Example4

查询tb_score表成绩为64,88或92的记录

SELECT scid,sid,cid,degree FROM tb_score WHERE degree in(64,88,92);

结果如下：

Example5

查询tb_teacher表为’助教’或’女性’老师信息

使用 or 关键字

SELECT tid,tname,tsex,tbirthday,profession,department FROM tb_teacher WHERE profession='助教' or tsex='女';

结果如下：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HAbujngz-1607949945880)(https://cdn.jsdelivr.net/gh/yunshanmister/picture/imgs{HQ7IJSQW(8`UFV[2J7C}W0.png)]

Example6

查询tb_score表按成绩升序信息

MySQL使用ORDER BY关键字和 ASC 或 DESC关键字升序/降序查询

SELECT scid,sid,cid,degree FROM tb_score ORDER BY degree ASC;

结果如下：

Example7

查询教书所有职称【使用distinct去重复】

SELECT DISTINCT profession FROM tb_teacher;

结果如下：

Example8

查询20031班学生人数

SELECT COUNT(*) FROM tb_student WHERE class='20031'

结果如下：

Example9

查询每门课的平均成绩【使用 avg() 函数查平均值, AS 关键字起别名 】

SELECT sid,cid,AVG(degree) AS 平均成绩  FROM tb_score GROUP BY cid

结果如下：

Example10

查询tb_score表中的最高分的学生学号和课程号

• 使用 limit 关键字

SELECT sid,cid FROM tb_score ORDER BY degree DESC LIMIT 0,1;

• 使用子查询

SELECT sid,cid FROM tb_score WHERE degree=(SELECT MAX(degree) FROM tb_score);

结果如下：

Example11

查询名字中带有字母a的学生

1. LIKE 关键字

• % 表示零个到多个的任意字符串

• _ 表示任意单个字符

• [] 表示自己可以指定范围。如 [A-X] 或者 [ABCDEFGHIJK]中的字符。

• [^] 不是不包含

SELECT * FROM tb_student WHERE sname LIKE '%a%';

2. INSTR(str,substr) 函数

SELECT * FROM tb_student WHERE INSTR(sname,'a');

3. LOCATE(substr,str) 函数

SELECT * FROM tb_student WHERE LOCATE('a',sname);

4. POSITION(substr IN str)函数

SELECT * FROM tb_student WHERE POSITION('a' IN sname);

结果如下：

Example12

查询tb_core表中至少有4名学生选修的并以且课程id包含4的课程的平均分数。

SELECT AVG(degree) FROM tb_score WHERE cid LIKE '%3%' AND cid IN (SELECT cid FROM tb_score GROUP BY cid HAVING COUNT(*)>3);

简化语句后

SELECT AVG(degree) FROM tb_score WHERE cid LIKE '%3%' AND GROUP BY cid HAVING COUNT(*)>3);

总结如下

上述查询出现的关键字及语法总结

• SELECT 表示的是查询结果

• DISTINCT 去除重复

• * 通配符查询所有

• FROM 查询后面接要查询的表

• WHERE 查询要限制的条件

• ORDER BY ASC DESC 排序查询

• LIMIT 分组查询

• GROUP BY 分组

• HAVING 过滤条件

• LIKE INSTR(str,substr) LOCATE(substr,str) POSITION(substr IN str) 模糊查询其中

• AS 起别名

综合使用顺序SELECT DISTINCT * FROM table_name WHERE '限制条件' GROUP BY '分组依据' HAVING '过滤条件' ORDER BY LIMIT 'X,Y'