博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
MySQL学习——MySQL增、删、改、查语句学习
阅读量：3943 次
发布时间：2019-05-24

本文共 19225 字，大约阅读时间需要 64 分钟。

MySQL学习——MySQL增、删、改、查语句学习

环境搭建：创建好数据库和表

#创建数据库mydbCREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_general_ci;#选择数据库USE mydb;#创建表student并设置id为主键CREATE TABLE student(    id INT PRIMARY KEY,#设置id为主键PRIMARY KEY    `name` VARCHAR(40),    password VARCHAR(40));

1.MySQL 插入数据

语法：insert into 表名 ( 字段1, 字段2,…字段n) values ( 值1, 值2,…值n);

#添加四个学生数据INSERT INTO student( id, NAME,PASSWORD)  VALUES ( 001,"张三","123456");INSERT INTO student( id, NAME,PASSWORD)  VALUES ( 002,"李四","123456");INSERT INTO student( id, NAME,PASSWORD)  VALUES ( 003,"王五","123456");INSERT INTO student( id, NAME,PASSWORD)  VALUES ( 004,"赵六","123456");

在这里插入图片描述

2.MySQL 删除数据

语法：DELETE FROM 表名 WHERE 字段名=值;

#删除name为张三的一行数据DELETE FROM  student  WHERE  NAME='张三';

在这里插入图片描述

3.MySQL 更改数据

语法： UPDATE 表名 SET 字段名=要更改的值 where 字段名=值;

#将id为2的数据的姓名改为新版李四UPDATE student SET NAME = '新版李四' WHERE id = 2;

在这里插入图片描述

3.MySQL 查询数据（重点）

环境搭建：

#创建数据库schoolCREATE DATABASE `school`;#选择数据库USE `school`;#创建表gradeCREATE TABLE `grade` (  `GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',  `GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',  PRIMARY KEY (`GradeID`)) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;#向表grade中插入数据INSERT  INTO `grade`(`GradeID`,`GradeName`) 	VALUES (1,'大一'),		(2,'大二'),		(3,'大三'),		(4,'大四'),		(5,'预科班');#创建表resultCREATE TABLE `result` (  `StudentNo` INT(4) NOT NULL COMMENT '学号',  `SubjectNo` INT(4) NOT NULL COMMENT '课程编号',  `ExamDate` DATETIME NOT NULL COMMENT '考试日期',  `StudentResult` INT(4) NOT NULL COMMENT '考试成绩',  KEY `SubjectNo` (`SubjectNo`)) ENGINE=INNODB DEFAULT CHARSET=utf8;#向result表中添加数据INSERT  INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) VALUES (1000,1,'2013-11-11 16:00:00',94),(1000,2,'2012-11-10 10:00:00',75),(1000,3,'2011-12-19 10:00:00',76),(1000,4,'2010-11-18 11:00:00',93),(1000,5,'2013-11-11 14:00:00',97),(1000,6,'2012-09-13 15:00:00',87),(1000,7,'2011-10-16 16:00:00',79),(1000,8,'2010-11-11 16:00:00',74),(1000,9,'2013-11-21 10:00:00',69),(1000,10,'2012-11-11 12:00:00',78),(1000,11,'2011-11-11 14:00:00',66),(1000,12,'2010-11-11 15:00:00',82),(1000,13,'2013-11-11 14:00:00',94),(1000,14,'2012-11-11 15:00:00',98),(1000,15,'2011-12-11 10:00:00',70),(1000,16,'2010-09-11 10:00:00',74),(1001,1,'2013-11-11 16:00:00',76),(1001,2,'2012-11-10 10:00:00',93),(1001,3,'2011-12-19 10:00:00',65),(1001,4,'2010-11-18 11:00:00',71),(1001,5,'2013-11-11 14:00:00',98),(1001,6,'2012-09-13 15:00:00',74),(1001,7,'2011-10-16 16:00:00',85),(1001,8,'2010-11-11 16:00:00',69),(1001,9,'2013-11-21 10:00:00',63),(1001,10,'2012-11-11 12:00:00',70),(1001,11,'2011-11-11 14:00:00',62),(1001,12,'2010-11-11 15:00:00',90),(1001,13,'2013-11-11 14:00:00',97),(1001,14,'2012-11-11 15:00:00',89),(1001,15,'2011-12-11 10:00:00',72),(1001,16,'2010-09-11 10:00:00',90),(1002,1,'2013-11-11 16:00:00',61),(1002,2,'2012-11-10 10:00:00',80),(1002,3,'2011-12-19 10:00:00',89),(1002,4,'2010-11-18 11:00:00',88),(1002,5,'2013-11-11 14:00:00',82),(1002,6,'2012-09-13 15:00:00',91),(1002,7,'2011-10-16 16:00:00',63),(1002,8,'2010-11-11 16:00:00',84),(1002,9,'2013-11-21 10:00:00',60),(1002,10,'2012-11-11 12:00:00',71),(1002,11,'2011-11-11 14:00:00',93),(1002,12,'2010-11-11 15:00:00',96),(1002,13,'2013-11-11 14:00:00',83),(1002,14,'2012-11-11 15:00:00',69),(1002,15,'2011-12-11 10:00:00',89),(1002,16,'2010-09-11 10:00:00',83),(1003,1,'2013-11-11 16:00:00',91),(1003,2,'2012-11-10 10:00:00',75),(1003,3,'2011-12-19 10:00:00',65),(1003,4,'2010-11-18 11:00:00',63),(1003,5,'2013-11-11 14:00:00',90),(1003,6,'2012-09-13 15:00:00',96),(1003,7,'2011-10-16 16:00:00',97),(1003,8,'2010-11-11 16:00:00',77),(1003,9,'2013-11-21 10:00:00',62),(1003,10,'2012-11-11 12:00:00',81),(1003,11,'2011-11-11 14:00:00',76),(1003,12,'2010-11-11 15:00:00',61),(1003,13,'2013-11-11 14:00:00',93),(1003,14,'2012-11-11 15:00:00',79),(1003,15,'2011-12-11 10:00:00',78),(1003,16,'2010-09-11 10:00:00',96),(1004,1,'2013-11-11 16:00:00',84),(1004,2,'2012-11-10 10:00:00',79),(1004,3,'2011-12-19 10:00:00',76),(1004,4,'2010-11-18 11:00:00',78),(1004,5,'2013-11-11 14:00:00',81),(1004,6,'2012-09-13 15:00:00',90),(1004,7,'2011-10-16 16:00:00',63),(1004,8,'2010-11-11 16:00:00',89),(1004,9,'2013-11-21 10:00:00',67),(1004,10,'2012-11-11 12:00:00',100),(1004,11,'2011-11-11 14:00:00',94),(1004,12,'2010-11-11 15:00:00',65),(1004,13,'2013-11-11 14:00:00',86),(1004,14,'2012-11-11 15:00:00',77),(1004,15,'2011-12-11 10:00:00',82),(1004,16,'2010-09-11 10:00:00',87),(1005,1,'2013-11-11 16:00:00',82),(1005,2,'2012-11-10 10:00:00',92),(1005,3,'2011-12-19 10:00:00',80),(1005,4,'2010-11-18 11:00:00',92),(1005,5,'2013-11-11 14:00:00',97),(1005,6,'2012-09-13 15:00:00',72),(1005,7,'2011-10-16 16:00:00',84),(1005,8,'2010-11-11 16:00:00',79),(1005,9,'2013-11-21 10:00:00',76),(1005,10,'2012-11-11 12:00:00',87),(1005,11,'2011-11-11 14:00:00',65),(1005,12,'2010-11-11 15:00:00',67),(1005,13,'2013-11-11 14:00:00',63),(1005,14,'2012-11-11 15:00:00',64),(1005,15,'2011-12-11 10:00:00',99),(1005,16,'2010-09-11 10:00:00',97),(1006,1,'2013-11-11 16:00:00',82),(1006,2,'2012-11-10 10:00:00',73),(1006,3,'2011-12-19 10:00:00',79),(1006,4,'2010-11-18 11:00:00',63),(1006,5,'2013-11-11 14:00:00',97),(1006,6,'2012-09-13 15:00:00',83),(1006,7,'2011-10-16 16:00:00',78),(1006,8,'2010-11-11 16:00:00',88),(1006,9,'2013-11-21 10:00:00',89),(1006,10,'2012-11-11 12:00:00',82),(1006,11,'2011-11-11 14:00:00',70),(1006,12,'2010-11-11 15:00:00',69),(1006,13,'2013-11-11 14:00:00',64),(1006,14,'2012-11-11 15:00:00',80),(1006,15,'2011-12-11 10:00:00',90),(1006,16,'2010-09-11 10:00:00',85),(1007,1,'2013-11-11 16:00:00',87),(1007,2,'2012-11-10 10:00:00',63),(1007,3,'2011-12-19 10:00:00',70),(1007,4,'2010-11-18 11:00:00',74),(1007,5,'2013-11-11 14:00:00',79),(1007,6,'2012-09-13 15:00:00',83),(1007,7,'2011-10-16 16:00:00',86),(1007,8,'2010-11-11 16:00:00',76),(1007,9,'2013-11-21 10:00:00',65),(1007,10,'2012-11-11 12:00:00',87),(1007,11,'2011-11-11 14:00:00',69),(1007,12,'2010-11-11 15:00:00',69),(1007,13,'2013-11-11 14:00:00',90),(1007,14,'2012-11-11 15:00:00',84),(1007,15,'2011-12-11 10:00:00',95),(1007,16,'2010-09-11 10:00:00',92),(1008,1,'2013-11-11 16:00:00',96),(1008,2,'2012-11-10 10:00:00',62),(1008,3,'2011-12-19 10:00:00',97),(1008,4,'2010-11-18 11:00:00',84),(1008,5,'2013-11-11 14:00:00',86),(1008,6,'2012-09-13 15:00:00',72),(1008,7,'2011-10-16 16:00:00',67),(1008,8,'2010-11-11 16:00:00',83),(1008,9,'2013-11-21 10:00:00',86),(1008,10,'2012-11-11 12:00:00',60),(1008,11,'2011-11-11 14:00:00',61),(1008,12,'2010-11-11 15:00:00',68),(1008,13,'2013-11-11 14:00:00',99),(1008,14,'2012-11-11 15:00:00',77),(1008,15,'2011-12-11 10:00:00',73),(1008,16,'2010-09-11 10:00:00',78),(1009,1,'2013-11-11 16:00:00',67),(1009,2,'2012-11-10 10:00:00',70),(1009,3,'2011-12-19 10:00:00',75),(1009,4,'2010-11-18 11:00:00',92),(1009,5,'2013-11-11 14:00:00',76),(1009,6,'2012-09-13 15:00:00',90),(1009,7,'2011-10-16 16:00:00',62),(1009,8,'2010-11-11 16:00:00',68),(1009,9,'2013-11-21 10:00:00',70),(1009,10,'2012-11-11 12:00:00',83),(1009,11,'2011-11-11 14:00:00',88),(1009,12,'2010-11-11 15:00:00',65),(1009,13,'2013-11-11 14:00:00',91),(1009,14,'2012-11-11 15:00:00',99),(1009,15,'2011-12-11 10:00:00',65),(1009,16,'2010-09-11 10:00:00',83),(1010,1,'2013-11-11 16:00:00',83),(1010,2,'2012-11-10 10:00:00',87),(1010,3,'2011-12-19 10:00:00',89),(1010,4,'2010-11-18 11:00:00',99),(1010,5,'2013-11-11 14:00:00',91),(1010,6,'2012-09-13 15:00:00',96),(1010,7,'2011-10-16 16:00:00',72),(1010,8,'2010-11-11 16:00:00',72),(1010,9,'2013-11-21 10:00:00',98),(1010,10,'2012-11-11 12:00:00',73),(1010,11,'2011-11-11 14:00:00',68),(1010,12,'2010-11-11 15:00:00',62),(1010,13,'2013-11-11 14:00:00',67),(1010,14,'2012-11-11 15:00:00',69),(1010,15,'2011-12-11 10:00:00',71),(1010,16,'2010-09-11 10:00:00',66),(1011,1,'2013-11-11 16:00:00',62),(1011,2,'2012-11-10 10:00:00',72),(1011,3,'2011-12-19 10:00:00',96),(1011,4,'2010-11-18 11:00:00',64),(1011,5,'2013-11-11 14:00:00',89),(1011,6,'2012-09-13 15:00:00',91),(1011,7,'2011-10-16 16:00:00',95),(1011,8,'2010-11-11 16:00:00',96),(1011,9,'2013-11-21 10:00:00',89),(1011,10,'2012-11-11 12:00:00',73),(1011,11,'2011-11-11 14:00:00',82),(1011,12,'2010-11-11 15:00:00',98),(1011,13,'2013-11-11 14:00:00',66),(1011,14,'2012-11-11 15:00:00',69),(1011,15,'2011-12-11 10:00:00',91),(1011,16,'2010-09-11 10:00:00',69),(1012,1,'2013-11-11 16:00:00',86),(1012,2,'2012-11-10 10:00:00',66),(1012,3,'2011-12-19 10:00:00',97),(1012,4,'2010-11-18 11:00:00',69),(1012,5,'2013-11-11 14:00:00',70),(1012,6,'2012-09-13 15:00:00',74),(1012,7,'2011-10-16 16:00:00',91),(1012,8,'2010-11-11 16:00:00',97),(1012,9,'2013-11-21 10:00:00',84),(1012,10,'2012-11-11 12:00:00',82),(1012,11,'2011-11-11 14:00:00',90),(1012,12,'2010-11-11 15:00:00',91),(1012,13,'2013-11-11 14:00:00',91),(1012,14,'2012-11-11 15:00:00',97),(1012,15,'2011-12-11 10:00:00',85),(1012,16,'2010-09-11 10:00:00',90),(1013,1,'2013-11-11 16:00:00',73),(1013,2,'2012-11-10 10:00:00',69),(1013,3,'2011-12-19 10:00:00',91),(1013,4,'2010-11-18 11:00:00',72),(1013,5,'2013-11-11 14:00:00',76),(1013,6,'2012-09-13 15:00:00',87),(1013,7,'2011-10-16 16:00:00',61),(1013,8,'2010-11-11 16:00:00',77),(1013,9,'2013-11-21 10:00:00',83),(1013,10,'2012-11-11 12:00:00',99),(1013,11,'2011-11-11 14:00:00',91),(1013,12,'2010-11-11 15:00:00',84),(1013,13,'2013-11-11 14:00:00',98),(1013,14,'2012-11-11 15:00:00',74),(1013,15,'2011-12-11 10:00:00',92),(1013,16,'2010-09-11 10:00:00',90),(1014,1,'2013-11-11 16:00:00',64),(1014,2,'2012-11-10 10:00:00',81),(1014,3,'2011-12-19 10:00:00',79),(1014,4,'2010-11-18 11:00:00',74),(1014,5,'2013-11-11 14:00:00',65),(1014,6,'2012-09-13 15:00:00',88),(1014,7,'2011-10-16 16:00:00',86),(1014,8,'2010-11-11 16:00:00',77),(1014,9,'2013-11-21 10:00:00',86),(1014,10,'2012-11-11 12:00:00',85),(1014,11,'2011-11-11 14:00:00',86),(1014,12,'2010-11-11 15:00:00',75),(1014,13,'2013-11-11 14:00:00',89),(1014,14,'2012-11-11 15:00:00',79),(1014,15,'2011-12-11 10:00:00',73),(1014,16,'2010-09-11 10:00:00',68),(1015,1,'2013-11-11 16:00:00',99),(1015,2,'2012-11-10 10:00:00',60),(1015,3,'2011-12-19 10:00:00',60),(1015,4,'2010-11-18 11:00:00',75),(1015,5,'2013-11-11 14:00:00',78),(1015,6,'2012-09-13 15:00:00',78),(1015,7,'2011-10-16 16:00:00',84),(1015,8,'2010-11-11 16:00:00',95),(1015,9,'2013-11-21 10:00:00',93),(1015,10,'2012-11-11 12:00:00',79),(1015,11,'2011-11-11 14:00:00',74),(1015,12,'2010-11-11 15:00:00',65),(1015,13,'2013-11-11 14:00:00',63),(1015,14,'2012-11-11 15:00:00',74),(1015,15,'2011-12-11 10:00:00',67),(1015,16,'2010-09-11 10:00:00',65),(1016,1,'2013-11-11 16:00:00',97),(1016,2,'2012-11-10 10:00:00',90),(1016,3,'2011-12-19 10:00:00',77),(1016,4,'2010-11-18 11:00:00',75),(1016,5,'2013-11-11 14:00:00',75),(1016,6,'2012-09-13 15:00:00',97),(1016,7,'2011-10-16 16:00:00',96),(1016,8,'2010-11-11 16:00:00',92),(1016,9,'2013-11-21 10:00:00',62),(1016,10,'2012-11-11 12:00:00',83),(1016,11,'2011-11-11 14:00:00',98),(1016,12,'2010-11-11 15:00:00',94),(1016,13,'2013-11-11 14:00:00',62),(1016,14,'2012-11-11 15:00:00',97),(1016,15,'2011-12-11 10:00:00',76),(1016,16,'2010-09-11 10:00:00',82),(1017,1,'2013-11-11 16:00:00',100),(1017,2,'2012-11-10 10:00:00',88),(1017,3,'2011-12-19 10:00:00',86),(1017,4,'2010-11-18 11:00:00',73),(1017,5,'2013-11-11 14:00:00',96),(1017,6,'2012-09-13 15:00:00',64),(1017,7,'2011-10-16 16:00:00',81),(1017,8,'2010-11-11 16:00:00',66),(1017,9,'2013-11-21 10:00:00',76),(1017,10,'2012-11-11 12:00:00',95),(1017,11,'2011-11-11 14:00:00',73),(1017,12,'2010-11-11 15:00:00',82),(1017,13,'2013-11-11 14:00:00',85),(1017,14,'2012-11-11 15:00:00',68),(1017,15,'2011-12-11 10:00:00',99),(1017,16,'2010-09-11 10:00:00',76);#创建表studentCREATE TABLE `student` (  `StudentNo` INT(4) NOT NULL COMMENT '学号',  `LoginPwd` VARCHAR(20) DEFAULT NULL,  `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',  `Sex` TINYINT(1) DEFAULT NULL COMMENT '性别，取值0或1',  `GradeId` INT(11) DEFAULT NULL COMMENT '年级编号',  `Phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空，即可选输入',  `Address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空，即可选输入',  `BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',  `Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号，允许为空，即可选输入',  `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',  PRIMARY KEY (`StudentNo`),  UNIQUE KEY `IdentityCard` (`IdentityCard`),  KEY `Email` (`Email`)) ENGINE=MYISAM DEFAULT CHARSET=utf8;#向student表中添加数据INSERT  INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) VALUES (1000,'111111','郭靖',1,1,'13500000001','北京海淀区中关村大街1号','1986-12-11 00:00:00','test1@bdqn.cn','450323198612111234'),(1001,'123456','李文才',1,2,'13500000002','河南洛阳','1981-12-31 00:00:00','test1@bdqn.cn','450323198112311234'),(1002,'111111','李斯文',1,1,'13500000003','天津市和平区','1986-11-30 00:00:00','test1@bdqn.cn','450323198611301234'),(1003,'123456','武松',1,3,'13500000004','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612314234'),(1004,'123456','张三',1,4,'13500000005','北京市通州','1989-12-31 00:00:00','test1@bdqn.cn','450323198612311244'),(1005,'123456','张秋丽 ',2,1,'13500000006','广西桂林市灵川','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311214'),(1006,'123456','肖梅',2,4,'13500000007','地址不详','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311134'),(1007,'111111','欧阳峻峰',1,1,'13500000008','北京东城区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311133'),(1008,'111111','梅超风',1,1,'13500000009','河南洛阳','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311221'),(1009,'123456','刘毅',1,2,'13500000011','安徽','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311231'),(1010,'111111','大凡',1,1,'13500000012','河南洛阳','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311044'),(1011,'111111','奥丹斯',1,1,'13500000013','北京海淀区中关村大街*号','1984-12-31 00:00:00','test1@bdqn.cn','450323198412311234'),(1012,'123456','多伦',2,3,'13500000014','广西南宁中央大街','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311334'),(1013,'123456','李梅',2,1,'13500000015','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311534'),(1014,'123456','张得',2,4,'13500000016','北京海淀区中关村大街*号','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311264'),(1015,'123456','李东方',1,4,'13500000017','广西桂林市灵川','1976-12-31 00:00:00','test1@bdqn.cn','450323197612311234'),(1016,'111111','刘奋斗',1,1,'13500000018','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311251'),(1017,'123456','可可',2,3,'13500000019','北京长安街1号','1981-09-10 00:00:00','test1@bdqn.cn','450323198109108311'),(10066,'','Tom',1,1,'13500000000','','0000-00-00 00:00:00','email@22.com','33123123123123123');#创建subject表CREATE TABLE `subject` (  `SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',  `SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',  `ClassHour` INT(4) DEFAULT NULL COMMENT '学时',  `GradeID` INT(4) DEFAULT NULL COMMENT '年级编号',  PRIMARY KEY (`SubjectNo`)) ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;#给subject表中插入数据INSERT  INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) 	VALUES (1,'高等数学-1',110,1),		(2,'高等数学-2',110,2),		(3,'高等数学-3',100,3),		(4,'高等数学-4',130,4),		(5,'C语言-1',110,1),		(6,'C语言-2',110,2),		(7,'C语言-3',100,3),		(8,'C语言-4',130,4),		(9,'JAVA第一学年',110,1),		(10,'JAVA第二学年',110,2),		(11,'JAVA第三学年',100,3),		(12,'JAVA第四学年',130,4),		(13,'数据库结构-1',110,1),		(14,'数据库结构-2',110,2),		(15,'数据库结构-3',100,3),		(16,'数据库结构-4',130,4),		(17,'C#基础',130,1);

效果展示：

  • gread表：

    在这里插入图片描述

  • result表：

    在这里插入图片描述

  • student表：

    在这里插入图片描述

  • subject表：

    在这里插入图片描述

  • 1.查询表中所有数据（）

    #‘*’为通配符	SELECT * FROM student;

    在这里插入图片描述

  • 2.查询时使用AS取别名

    #给字段取别名SELECT studentno AS 学号,studentname AS 姓名 FROM student;

    在这里插入图片描述

    # 使用as也可以为表取别名SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
    # CONCAT()函数拼接字符串SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

    在这里插入图片描述

  • 3.DISTINCT去重

    SELECT studentno FROM result; # 通过学号查看哪些同学参加了考试

    在这里插入图片描述

    去重后：

    SELECT DISTINCT studentno FROM result;

    在这里插入图片描述

  • 4.selcet查询中可以使用表达式

    SELECT @@auto_increment_increment; # 查询自增步长

    在这里插入图片描述

    SELECT VERSION(); #查询版本号

    在这里插入图片描述

    SELECT 100*3-1 AS 计算结果; # 表达式

    在这里插入图片描述

  • 5.where条件语句（用来检索符合条件的数据）和逻辑操作符

    #查找学号为1000的学生学号和成绩SELECT Studentno,StudentResult FROM result where Studentno = 1000;

    在这里插入图片描述

    # 查询考试成绩在95-100之间的，其中AND也可以写成&&SELECT Studentno,StudentResult FROM result  WHERE StudentResult>=95 AND StudentResult<=100;

    在这里插入图片描述

    #查找成绩=95或者成绩=100的SELECT Studentno,StudentResult FROM result WHERE StudentResult=95 OR StudentResult=100;#也可以使用button ……and ……SELECT Studentno,StudentResult FROM result WHERE StudentResult BETWEEN 95 AND 100;

    在这里插入图片描述

    # 除了1000号同学,要其他同学的成绩SELECT studentno,studentresult FROM result WHERE studentno!=1000;#也可以使用notSELECT studentno,studentresult FROM result WHERE NOT studentno=1000;

    在这里插入图片描述

  • 6.模糊查询LIKE

    #查询姓李的同学的学号及姓名，% (代表0到任意个字符)SELECT studentno,studentname FROM student WHERE studentname LIKE '李%';

    在这里插入图片描述

    #查询姓李的同学,后面只有一个字的，_ (代表一个字符)SELECT studentno,studentname FROM student WHERE studentname LIKE '李_';

    在这里插入图片描述

    #查询姓李的同学,后面只有两个字的SELECT studentno,studentname FROM student WHERE studentname LIKE '李__';

    在这里插入图片描述

    #查询姓名中含有 文 字的SELECT studentno,studentname FROM student WHERE studentname LIKE '%文%';

    在这里插入图片描述

    注意：#查询姓名中含有特殊字符的需要使用转义符号 ‘’

  • 7.模糊查询IN

    #查询学号为1000,1001,1002的学生姓名SELECT studentno,studentname FROM student WHERE studentno IN (1000,1001,1002);

    在这里插入图片描述

    #查询地址在北京,南京,河南洛阳的学生SELECT studentno,studentname,address FROM student WHERE address IN ('北京','南京','河南洛阳');

    在这里插入图片描述

    8.NULL值查询

    #查询出生日期没有填写的同学SELECT studentname FROM student WHERE BornDate IS NULL;

    在这里插入图片描述

    注意：不能直接写=NULL , 这是代表错误的 , 用 is null

    #查询出生日期填写的同学SELECT studentname FROM student WHERE BornDate IS NOT NULL;

    在这里插入图片描述

    #查询没有写家庭住址的同学(空字符串不等于null)SELECT studentname FROM student WHERE Address='' OR Address IS NULL;

    在这里插入图片描述

  • 9.连接查询（多表查询）

    • 内连接
      查询两个表的交集
      #查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)SELECT s.studentno,studentname,subjectno,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno
      在这里插入图片描述
    • 外连接
      左连接：以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充
      SELECT s.studentno,studentname,subjectno,StudentResult FROM student s LEFT JOIN result r ON r.studentno 	= s.studentno
      在这里插入图片描述
      右连接：以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充
      SELECT s.studentno,studentname,subjectno,StudentResult FROM student s RIGHT JOIN result r ON r.studentno = s.studentno
      在这里插入图片描述
    • 等值连接和非等值连接
      SELECT s.studentno,studentname,subjectno,StudentResult FROM student s , result r WHERE r.studentno = s.studentno
      在这里插入图片描述

10.自连接

自连接查询：数据表与自身进行连接，就是把一张表看成两张一模一样的表,然后将这两张表连接查询

环境搭建：

# 创建一个表CREATE TABLE `category` (  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',  `pid` INT(10) NOT NULL COMMENT '父id',  `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',  PRIMARY KEY (`categoryid`)) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8# 插入数据INSERT INTO `category` (`categoryid`, `pid`, `categoryName`) VALUES('2','1','信息技术'),('3','1','软件开发'),('4','3','数据库'),('5','1','美术设计'),('6','3','web开发'),('7','5','ps技术'),('8','2','办公信息');

表中内容展示：

在这里插入图片描述

#将表中有父子栏目关系分开展示在一张表上SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'  FROM category AS a,category AS b WHERE a.`categoryid`=b.`pid`

在这里插入图片描述

11.排序和分页

  • 排序：ORDER BY（默认按ASC升序对记录排序，DESC为降序）

    # 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)# 按成绩降序排序SELECT s.studentno,studentname,subjectname,StudentResultFROM student sINNER JOIN result rON r.studentno = s.studentnoINNER JOIN `subject` subON r.subjectno = sub.subjectnoWHERE subjectname='数据库结构-1'ORDER BY StudentResult DESC , studentno

    在这里插入图片描述

  • 分页：LIMIT

    limit(a,b)：a表示从第几个开始，b表示一页有几行数据

    #每页显示5条数据SELECT * FROM student LIMIT 0,5;

    在这里插入图片描述

12. 子查询

子查询的意思就是用已经查询出的结果作为新表，然后嵌套查询

SELECT studentno,studentname FROM student WHERE studentno IN(    SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(        SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'))

在这里插入图片描述

转载地址：http://oaiwi.baihongyu.com/

你可能感兴趣的文章
Python:ModuleNotFoundError: No module named 模块名 错误及解决方案
查看>>
Python中os与sys两模块的区别
查看>>
nohup详解
查看>>
idea .gitignore对.idea不起作用解决
查看>>
深度学习中的注意力机制(2017版)-易理解
查看>>
Transformer解析-易理解
查看>>
多维数组[:,0]和[:0:1]获取的区别
查看>>
复原Ip地址
查看>>
重建二叉树
查看>>
二叉树根节点到叶子节点的路径数字之和
查看>>
根节点到叶子节点的节点值之和等于 sum的路径
查看>>
判断二叉树是否有从根节点到叶子节点的节点值之和等于sum的路径
查看>>
反转字符串
查看>>
环形链表
查看>>
删除链表的倒数第N个节点
查看>>
回文链表
查看>>
容器盛水问题
查看>>
滑动窗口最大值
查看>>
win7 文件删除后要刷新后才会消失
查看>>
用ffmpeg转多音轨的mkv文件
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2024-09-18 19:08:00   当前IP: 3.15.237.46   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我