• select查询操作(重要)五


    DQL查询语言(最重点)

    1.DQL

    (Data Query LANGUAGE:数据库查询语言

    所有的查询语言操作都是用它:select

    简单的查询,复杂的查询都能让它来做~

    数据库最核心的语言,最重要的语句

    使用频率最大的语句

    SELECT 完整语法:

    SELECT [ALL|DISTINCT]
    {*|table.*|[table.filed1[as alias1][,table.filed2[as alias2]][,...]]}
    FROM table_name [as table_alias]
    	[left|right|inner join table_name2...on...]  -- 联合查询
    	[WHERE ...] -- 指定结果需满足的条件
    	[GROUP BY ...] -- 指定结果按照哪几个字段来分组
    	[HAVING] -- 过滤分组的记录必须满足的次要条件
    	[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
    	[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条(分页)
    

    2.指定查询字段

    先导入sql文件:(有点长请耐心看哦~)

    USE `school`;
    
    /*Table structure for table `grade` */
    
    DROP TABLE IF EXISTS `grade`;
    
    CREATE 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;
    
    /*Data for the table `grade` */
    
    INSERT  INTO `grade`(`GradeID`,`GradeName`) VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
    
    /*Table structure for table `result` */
    
    DROP TABLE IF EXISTS `result`;
    
    CREATE 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;
    
    /*Data for the table `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);
    
    /*Table structure for table `student` */
    
    DROP TABLE IF EXISTS `student`;
    
    CREATE 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;
    
    /*Data for the table `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');
    
    /*Table structure for table `subject` */
    
    DROP TABLE IF EXISTS `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;
    
    /*Data for the table `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);
    
    --查询全部学生的   SELECT 字段 FROM 表
    SELECT * FROM student
    
    --查询指定的字段
    SELECT  `StudentNo`,`studentName` FROM student
    
    --别名 AS 给结果字段取一个名字也可以给表取别名
    SELECT `StudentNo` AS 学号, `StudentName` AS 姓名 FROM student
    
    --函数  Concat(a,b)拼接字符串
    SELECT Concat('姓名:',StudentName)AS 新名字 FROM student
    

    格式语法

    SELECT 字段1,字段2.... FROM 表

    有的时候列字段不是那么见名知意。我们其别名 AS 字段名 as 别名 表名 as 别名

    去重distinct

    作用:去除select语句查询出来的结果中重复的数据,重复的数据显示一条

    --查询一下有哪些同学参加了考试,成绩
    SELECT * FROM `result`  --查询全部的考试成绩
    SELECT `StudentNo` FROM result --查询那些同学参加了考试
    SELECT DISTINCT `StudentNo` FROM result --发现重复的数据,去重DISTINCT
    

    数据库的列(表达式)

    --查询一下有哪些同学参加了考试,成绩
    SELECT * FROM `result`  --查询全部的考试成绩
    SELECT `StudentNo` FROM result --查询那些同学参加了考试
    SELECT DISTINCT `StudentNo` FROM result --发现重复的数据,去重DISTINCT
    
    SELECT VERSION() AS 版本号--查看数据库版本号(函数)
    SELECT  100*3-1 AS 计算结果  --用来计算(表达式)
    SELECT @@auto_increment_increment --查询自增的步长(变量)
    
    --给所有学生的成绩都加1分查看
    SELECT  `StudentNo`,`StudentResult`+1 AS 加一分后 FROM result
    

    数据库中的表达式:文本值,列,NULL,函数,计算表达式,环境变量。。。

    select 表达式 from 表

    3.where条件子句

    作用:检索数据中符合条件的值

    逻辑运算符

    运算符 语法 描述
    and && a and b a&&b 逻辑与,两个都为真,结果为真
    or || a or b a||b 逻辑或,其中一个为真,则结果为真
    Not ! not a !a 逻辑非,真为假,假为真

    注意:尽量使用字母!

     --------------------where---------------------
    --查询考试成绩95~100分之间(3种)
    --用AND
    SELECT `StudentNo`,`StudentResult` AS 成绩 FROM result where `StudentResult` >=95 AND `StudentResult`<=100
    
    --用&&
    SELECT `StudentNo`,`StudentResult` AS 成绩 FROM result where `StudentResult` >=95 && `StudentResult`<=100
    
    --模糊查询(区间)
    SELECT studentNo, `StudentResult` AS 成绩 FROM result where StudentResult BETWEEN 95 AND 100
    
    --除了学号1000学生之外的同学成绩
    --使用 !=
    SELECT studentNo,`StudentResult`AS 成绩 FROM result WHERE studentNo !=1000
    --使用not
    SELECT studentNo,`StudentResult`AS 成绩 FROM  result WHERE NOT studentNo = 1000 
    

    模糊查询:比较运算符

    运算符 语法 描述
    IS NULL a is null 如果操作符为null,结果为真
    IS NOT NULL a is not null 如果操作符不为null,结果为真
    BETWEEN a between b and c 若在b和c之间,则结果为真
    Like a like b sql匹配,如果a匹配b,则结果为真
    In a in(a1,a2,a3....) 假设a在a1,或者a2...其中一个值,结果为真

    like: 小红 like '红xxxx' 返回真

    --===========模糊查询======================--
    --查询姓刘的同学
    --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` ,`address` AS 地址 FROM student  WHERE `address` LIKE ('北京%')
     
    --========in===============---
    --查询学号 1001 1002 1003的学生  in的具体的值或者多个值
     SELECT `studentNo`,`studentName` FROM student WHERE `studentNo` in (1001,1002,1003)
    --===null not null===----
    --查找地址为null的学生 ''
     SELECT `studentNo`,`studentName`,`address`  FROM student WHERE `address` is null or address=''
     
     --查找有出生日期的同学 不为空 
      SELECT `studentNo`,`studentName`,`address` ,`BornDate` FROM student  WHERE `BornDate` is NOT NULL
    

    4.连表查询join

    七种join理论

    --======连表查询join==========---
     --查询参加了考试的同学(学号,姓名,科目编号,分数)
     
     SELECT * FROM student 
     
     SELECT * FROM result
      
    	/*思路:
    	1.分析需求,分析查出来的字段来自哪些表,(连接查询)
    	2.确定使用哪一种连接? 7种
    	确定交叉点(这两张表那个数据是相同的)
    	判断的条件:学生表中 studentNo= 成绩表 studentNo
    	
    	*/
    	join (连接的表) on(判断的条件) 连接查询
    	where 等值查询
    	-- INNER JOIN 并集查询(内连接)
    SELECT s.studentNo,studentName,SubjectNo,StudentResult 
    FROM student AS s INNER JOIN result AS r
    ON s.studentNo =r.studentNo
    
    	--Right Join右连接
    SELECT s.studentNo,studentName,SubjectNo,StudentResult 
    FROM student AS s Right JOIN result AS r
    ON s.studentNo =r.studentNo
    
    
    	--left Join左连接
    SELECT s.studentNo,studentName,SubjectNo,StudentResult 
    FROM student AS s left Join result AS r
    ON s.studentNo =r.studentNo
    
    操作 描述
    Inner join 如果标准至少有一个匹配,就返回值
    left join 也会从左表中返回所有的值,即使右边没有匹配
    right join 也会从右表中返回所有的值,即使左边没有匹配
    --查询缺考的同学
    SELECT s.studentNo,studentName,SubjectNo,StudentResult 
    FROM student AS s left Join result AS r
    ON s.studentNo =r.studentNo   where studentResult IS NULL
    --思考题(查询了参加考试的同学信息,学号,学生姓名,科目名,分数)
    
    	/*思路:
    	1.分析需求,分析查出来的字段来自哪些表,student ,result ,subject(连接查询)
    	2.确定使用哪一种连接? 7种
    	确定交叉点(这两张表那个数据是相同的)
    	判断的条件:学生表中 studentNo= 成绩表 studentNo
    	*/
    	
    	SELECT s.StudentNo,studentName,SubjectName,StudentResult
    	FROM student AS s left JOIN result AS r
    	ON s.StudentNo=r.StudentNo 
     INNER JOIN  `subject` AS sub
     ON r.SubjectNo=sub.SubjectNo
     
     --我要查那些字段数据select ....
    --从那些表查 FROM 表 xxx Join 连接的表 on  交叉条件
    --假设存在一种多张表查询,慢慢来,先查询两张表然后慢慢添加
    
    
    --FROM a left join b --以a表为基准
    --FROM  a right join b --以b表为基准
    
    

    5.自连接:

    自连接:(了解即可)

    创建表并插入数据

    --===================自连接===================-------
    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,'办公信息');
    

    自己的表跟自己的表连接,核心:一张表拆为两张一样的表既可

    父类:

    categoryid categoryName
    2 信息技术
    3 软件开发
    5 美术设计

    子类 : 子类的pid就是categoryid

    pid categoryid categoryName
    3 4 数据库
    3 6 web开发
    2 8 办公信息
    5 7 ps技术

    操作:查询父类的子关系

    父类 子类
    信息技术 办公信息
    软件开发 数据库
    软件开发 web开发
    美术设计 ps技术

    演示:

    --查找父子信息:把一张表分为两张表
    
    SELECT a.`categoryName` AS 父栏目,b.`categoryName`AS 子栏目
    FROM `category` AS a,`category` AS b  WHERE a.`categoryid`=b.`pid`
    

    6.分页和排序

    排序:ORDER BY 升序ASC ,降序DESC

    --排序:升序ASC ,降序DESC
    --ORDER BY 给字段排序 怎么排
    
    --查询结果根据 成绩降序 排序
    SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
    FROM`student` AS s LEFT JOIN `result` AS r
    ON s.StudentNo=r.StudentNo
    INNER JOIN `subject` AS sub 
    ON r.SubjectNo=sub.SubjectNo
    WHERE SubjectName='数据库结构-1'
    ORDER BY   `StudentResult` DESC
    
    

    分页

    --假如数据有100万数据
    --为什么要分页?
    --缓解数据库的压力,给用户更加好的体验,  还有瀑布流适合用于图片
    
    --分页,每页只显示五条数据
    --语法,limit 启始值,页面的大小 LIMIT 0,5
    --网页应用:当前,总的页数,页面的大小
    --LIMIT 0,5 一到五
    
    
    SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
    FROM`student` AS s LEFT JOIN `result` AS r
    ON s.StudentNo=r.StudentNo
    INNER JOIN `subject` AS sub 
    ON r.SubjectNo=sub.SubjectNo
    WHERE SubjectName='数据库结构-1'
    ORDER BY   `StudentResult` DESC
    LIMIT 0,5 
    --第一页  LIMIT 0,5
    --第二页  LIMIT 5,5
    --第三页  LIMIT 10,5
    --第N页   LIMIT (n-1)*pageSize,pageSize
    --[pageSize:页面大小,n当前页]
    --[(n-1)*pageSize起始值]
    --[n:当前页]
    
    
    

    格式语法:limit(查询起始值 ,页面大小)

    思考题:

    --查询Java第一学年课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,课程编号,分数)

    --查询Java第一学年课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,课程编号,分数)
    SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult` 
    FROM `student` AS s INNER JOIN  `result` AS r
    ON s.StudentNo=r.StudentNo
    INNER JOIN `subject` 	AS sub
    ON r.SubjectNo=sub.SubjectNo
    WHERE `SubjectName`='JAVA第一学年' AND `StudentResult`>=80
    ORDER BY `StudentResult` DESC
    LIMIT 0,10
    

    7.子查询

    where(这个值是计算出来的)

    本质:

    --1.查询数据库结构-1的所有考试结果(学号,科目编号,成绩),降序排列
    --方式一使用连接查询
    SELECT  `StudentNo`,r.`SubjectNo`,`StudentResult`
    FROM `result` AS r INNER JOIN `subject` AS sub
    ON r.SubjectNo=sub.SubjectNo
    WHERE SubjectName='数据库结构-1'
    ORDER BY `StudentResult` DESC
    
    

    使用子查询

    --方式二:使用子查询()
    SELECT `StudentNo`,`SubjectNo`,`StudentResult`
    FROM `result`
    WHERE(
      SELECT `SubjectNo`
    	FROM `subject` WHERE SubjectName='数据库结构-1'
    )
    ORDER BY `StudentResult` DESC
    
    
    
    
    
    --分数不小于80分的学生的学号和姓名
    SELECT  s.StudentNo,StudentName ,StudentResult FROM 
    `student` AS s INNER JOIN `result`AS r
    ON s.StudentNo=r.StudentNo
    WHERE StudentResult>=80
    --在这个基础上添加一个科目,高等数学-2的
    SELECT DISTINCT s.StudentNo,StudentName ,StudentResult FROM 
    `student` AS s INNER JOIN `result`AS r
    ON s.StudentNo=r.StudentNo
    WHERE StudentResult>=80 AND `SubjectNo`=(SELECT SubjectNo FROM `subject` WHERE SubjectName='高等数学-2')
    
    
    --查询 课程为高等数学-2 并且分数不小于80的同学的学号姓名
    SELECT s.`StudentNo`,`StudentName` ,`StudentResult`,`SubjectName` FROM 
    `student` AS s INNER JOIN `result` AS r
    INNER JOIN
    `subject` AS sub
    ON r.SubjectNo=sub.SubjectNo
    WHERE `SubjectName`='高等数学-2'  AND  StudentResult>=80
     
    --在改造(先执行里面的,在执行外面的)
    SELECT  StudentNo,StudentName FROM student WHERE StudentNo IN(
    SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo=(
        SELECT SubjectNo FROM `subject` WHERE `SubjectName`='高等数学-2'
    )
    )
    
    

    分组和过滤

    -- GROUP BY:通过什么字段进行分组
    --HAVING :分组后用HAVING 过滤 不能使用where了 条件跟where是一样的只是位置不一样

    --查询不同课程的平均分,最高分,最低分
    SELECT  SubjectName AS 课程名:,AVG(StudentResult) AS 平均分:,MAX(StudentResult) AS 最高分:,MIN(StudentResult) AS 最低分
    FROM result AS r  INNER JOIN `subject` AS sub
    ON r.SubjectNo=sub.SubjectNo
     GROUP BY r.SubjectNo 
     HAVING AVG(StudentResult)>=80
    

    8.mysql常用函数:

    --=======常用函数=======---
    SELECT ABS(-8) --绝对值
    SELECT CEILING(9.4)--向上取整
    SELECT FLOOR(9.4)  --向下取整
    SELECT RAND()--返回一个0~1之间的随机数
    SELECT SING(10) --判断一个数的符号0-0 负数返回-1,正数返回1
    
    --字符串函数
    SELECT CHAR_LENGTH('即使再小的船') --字符串长度
    SELECT  CONCAT('我','爱你','你们')---字符串拼接
    SELECT INSERT('我爱编程hello world',1,2,'超级热爱')--插入输入,从某个位置开始替换某个长度
    SELECT LOWER('LINUHUAN')--小写字母
    SELECT UPPER('liuhuan')  -- 大写字母
    SELECT INSTR('liuhaun','h') -- 返回第一次出现的子串的索引
    SELECT REPLACE('刘欢说坚持就能成功','坚持','努力') -- 替换出现的指定字符串	
    SELECT SUBSTR('刘欢说坚持就能成功',4,6) -- 返回指定的子字符串(源字符串,截取的位置,截取的长度)
    SELECT REVERSE('刘欢说坚持就能成功') -- 反转
    
    
    -- 查询姓 周 的同学换成 邹
    SELECT REPLACE(`StudentName`,'周','邹')
    FROM `student`
    WHERE `StudentName` LIKE '周%'
    
    
    -- 时间和日期函数(重要,记住!!!)
    SELECT CURRENT_DATE() -- 获取当前日期
    SELECT CURDATE() -- 获取当前日期
    SELECT NOW()  -- 获取当前时间
    SELECT LOCALTIME()  -- 本地时间
    SELECT SYSDATE()  -- 系统时间
    SELECT YEAR(NOW())
    SELECT MONTH(NOW())
    SELECT DAY(NOW())
    SELECT HOUR(NOW())
    SELECT MINUTE(NOW())
    SELECT SECOND(NOW())
    
    -- 系统
    SELECT SYSTEM_USER() -- 数据库当前的用户
    SELECT USER() -- 数据库当前的用户
    SELECT VERSION() -- 数据库当前的版本
    
    
    
    
    
    
    

    9.聚合函数(常用)

    函数名称 描述
    COUNT() 计数
    SUM() 求和
    AVG() 平均值
    MAX() 最大值
    MIN() 最小值
    ..... ......
    --=======聚合函数=========---
    SELECT COUNT(`StudentName`) FROM `student` --COUNT(字段),会忽略所有的null值
    
    SELECT COUNT(*) FROM `student`  --COUNT(*),不会忽略null值    本质 计算行数
    
    SELECT COUNT(1) FROM result --COUNT(1),不会忽略所有的null值   本质  计算行数
    
    --总和 SUM('字段')
    SELECT SUM(`StudentResult`) AS 总和 FROM  `result`
    --平均值:AVG('字段')
    SELECT AVG(`StudentResult`) AS 平均分FROM  `result`
    --MAX('最大值')
    SELECT MAX(`StudentResult`) AS 最高分 FROM `result`
    --MIN('最低分')
    SELECT MIN(`StudentResult`) AS 最低分 FROM `result`
    

    10.数据库级别的MD5加密(扩展)

    什么是MD5?

    主要加强算法复杂度和不可逆性。

    MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密前的值

    --========测试MD5 加密=======
     
     CREATE TABLE `testmd5`(
       `id` INT(4) NOT NULL AUTO_INCREMENT,
    	 `name` VARCHAR(30) NOT NULL,
    	 `pwd` VARCHAR(50) NOT NULL,
    	 PRIMARY KEY (`id`)
     )ENGINE=INNODB  DEFAULT CHARSET=utf8
     
    --插入数据
    --明文密码
    INSERT into `testmd5`(`name`,`pwd`)VALUES('test1','123456'),('test2','1234df56'),('test3','1234fd56'),('test4','123f456'),('test5','12345d6')
    --给id为1的pwd进行md5加密
    UPDATE  `testmd5` set `pwd`=MD5(pwd) WHERE id=1
    --加密全部pwd
    UPDATE  `testmd5` set pwd=MD5(pwd)
    
    --插入的时候进行加密
    INSERT into `testmd5`(`name`,`pwd`)VALUES('xx',MD5('456456'))
    --如何验证:将用户传进来的密码进行md5加密,然后对比加密后的值
    INSERT into `testmd5`(`name`,`pwd`)VALUES('小红',MD5(123456))
    
    --如何验证:将用户传进来的密码进行md5加密,然后对比加密后的值
    SELECT `id`,`name`,`pwd`FROM testmd5 WHERE name='小红' and pwd=MD5('123456')
     
    

    select小结:

    语法顺序要正确:

    SELECT [ALL|DISTINCT]
    {*|table.*|[table.filed1[as alias1][,table.filed2[as alias2]][,...]]}
    FROM table_name [as table_alias]
    	[left|right|inner join table_name2...on...]  -- 联合查询
    	[WHERE ...] -- 指定结果需满足的条件
    	[GROUP BY ...] -- 指定结果按照哪几个字段来分组
    	[HAVING] -- 过滤分组的记录必须满足的次要条件
    	[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
    	[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条(分页)
    

    *分享一下我学习编程的途径希望大家循序渐进,共同进步:*

    How2J 的 Java教程:当下Java小白的引路人,以有趣和好理解的方式展示Java和Web的内容拥有当今流行的java路线。

    哔哩哔哩 (゜-゜)つロ 干杯~-bilibili:中国最大的学习平台没有之一,拥有海量的资源有时间的小伙伴可以免去重金花钱培训。

    廖雪峰的官方网站廖雪峰,十年软件开发经验,业余产品经理,精通Java/Python/Ruby/Scheme/Objective C等,对开源框架有深入研究..

    菜鸟教程 - 学的不仅是技术,更是梦想!:提供了编程的基础技术教程, 介绍了HTML、CSS、Javascript、Python,Java,Ruby,C,PHP , MySQL等各种编程语言的基础知识。

  • 相关阅读:
    leetcode 68 Text Justification
    了解HTTP协议
    对编码的一点理解
    极简WebSocket聊天室
    统一响应数据的封装
    BitMap
    SPI机制
    Holder类
    Java的标签
    二叉树的非递归遍历
  • 原文地址:https://www.cnblogs.com/jinronga/p/12502708.html
Copyright © 2020-2023  润新知