MySQL数据库（1）-CSDN博客

本文链接：https://blog.csdn.net/m0_73895936/article/details/132691901

MySQL数据库（1）

1.1SQL数据库操作语句

# 创建数据库
CREATE DATABASE my_db;

# IF NOT EXISTS表示如果不存在再创建指定的数据库
CREATE DATABASE my_db IF NOT EXISTS;

#删除数据库，注意：下面的语句如果不存在则报错
DROP DATABASE my_db;

#IF EXISTS表示如果存在在删除指定的数据库
DROP DATABASE my_db IF EXISTS;
# 创建表之前先使用表
USE my_db;

# 创建表
CREATE TABLE IF NOT EXISTS student(
	# 指定表中的每一列，语法：列名 类型 [是否可以为空，默认是可以为空] [描述]，这里[]的部分可以省略
	# int是标准整型
	studentNo INT NOT NULL COMMENT '学号',
	# varchar是字符串型，这里的10是指定的最大长度
	studentName VARCHAR(10) NOT NULL COMMENT '姓名',
	# boolean是布尔类型，只能存储0和1,0表示false，1表示true
	sex BOOLEAN NOT NULL COMMENT '性别',
	age INT NOT NULL COMMENT '年龄',
	# text是长文本类型
	address TEXT NULL COMMENT '地址',
	# double是双精度浮点型
	score DOUBLE NOT NULL COMMENT '成绩',
	# decimal是指定长度的浮点型，这里的10是最大长度，2是小数点长度，比如：10000000.00
	money DECIMAL(10,2) NULL COMMENT '存款',
	bornDate DATETIME NOT NULL COMMENT '生日'
)ENGINE=INNODB COMMENT '学生表';
#删除表
DROP TABLE IF EXISTS student;
#创建数据库时，如果存在先删除
drop database if exists myschool;
CREATE DATABASE IF NOT EXISTS MYSCHOOL;
#2.使用该数据库
USE MYSCHOOL;
#3.创建年级表（grade）
DROP TABLE IF EXISTS GRADE;
CREATE TABLE GRADE(
		#PRIMARY KEY 表示主键，一张表只能有一个主键，主键不允许为空，主键列的数据不能重复
		#AUTO_INCREMENT 表示标识列，表示列的数据类型必须是int，标识列不需要自己输入，自己会标识，从1开始
		#标识列只能保证不重复，不能保证每次连续行，如果中途发生错误，那么那一次的标识列就会跳过
		GRADEID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
		#UNIQUE 表示唯一键，一张表中可以定义多个唯一键，唯一键的列可以允许为空但是不能重复
		GRADENAME VARCHAR(20) NOT NULL UNIQUE
);
#4.创建学生表（student）
DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student(
		studentId VARCHAR(30) NOT NULL PRIMARY KEY  COMMENT '学号',
		LOGINPSW VARCHAR(30) NOT NULL COMMENT '密码',
		studentNAME VARCHAR(30) NOT NULL COMMENT '姓名',
		SEX VARCHAR(20) NOT NULL COMMENT'性别',
		PHONE INT NULL COMMENT'电话',
		EMAIL VARCHAR(40) NULL COMMENT'电子邮件',
		ADDRESS VARCHAR(30) NULL COMMENT'地址',
		BRONDATE DATETIME NOT NULL COMMENT'出生日期',
		GRADEID INT NOT NULL COMMENT'班级',
		IDDENTITYCARD VARCHAR(11) NOT NULL UNIQUE
);
#更改表名
ALTER TABLE student RENAME AS xuehsheng;
#修改列名
ALTER TABLE XUEHSHENG CHANGE STUDENTNAME XINGMING VARCHAR(15) NOT NULL;
#删除列名
ALTER TABLE XUEHSHENG DROP BRONDATE;
#给学生表添加一个外键约束和年级表建立关系
#(ADD CONSTRAINT)添加约束,(FOREIGN KEY)表示外键约束,(REFERENCES)表示关联，这里表示grade表中的gradeId
#总结：就是STUDENT表中的gradeId要跟grade表中的gradeId建立外键约束关系（也就是建立主从关系）
ALTER TABLE student ADD CONSTRAINT fk_student_grade FOREIGN KEY grade(gradeId) REFERENCES grade(gradeId);
#删除外键约束，注意：同时还需要删除索引
ALTER TABLE student DROP FOREIGN KEY fk_student_grade;
ALTER TABLE student DROP INDEX fk_student_grade;

1.2DML数据库操作语言（以上述数据表为基础）

1.2.1插入语句语法（insert into…values…）

#插入语句的语法：
#向年级表中插入数据
#INSERT INTO 表名((字段1，字段2，字段3.。。。。))VALUES('值1','值2','值3');
INSERT INTO grade(GRADENAME) VALUES('一年级');
INSERT INTO grade(GRADENAME) VALUES('二年级');
INSERT INTO grade(GRADENAME) VALUES('三年级');

#可以一次插入多条数据
INSERT INTO grade(GRADENAME)VALUES('四年级'),('五年级'),('六年级');

#########################################################################
#向学生表中插入数据
#注意：插入数据的数据可以和跟数据表的顺序不一样
INSERT INTO student (SEX,studentId,BRONDATE,LOGINPSW,EMAIL,studentNAME,PHONE,GRADEID,ADDRESS,IDDENTITYCARD)
VALUES('男','s1001','2002-11-11','123456','101@.com','赵露思','10010','1','四川','123456789');

#定义为空的数据可以忽略不写
INSERT INTO student (studentId,LOGINPSW,studentNAME,SEX,BRONDATE,GRADEID,IDDENTITYCARD)
VALUES('S1002','1234567','王晓晨','女','2001-12-12','2','1212121211');

#注意：在插入语句可以省略列名，省略列名后，必须严格按照表中的顺序来写
INSERT INTO student VALUES('S1003','1234567890','景甜','女','112233','202.@com','北京','2003-12-11','3','13131313');

#注意：省略列名后，可以为空列，在插入值时可以直接写null插入
INSERT INTO student VALUES('S1004','88890090','张静怡','女',NULL,NULL,NULL,'2001-11-12','1','3434343434');
#######################################################################################################
#向课表中加入数据
INSERT INTO SUBJECT (SUBJECTNAME,CLASSHOUR,GRADEID) VALUES('历史','22','1'),('数学','23','2'),('英语','25','3');

############################################################################################################
#向成绩表中插入五条数据
INSERT INTO result (studentNo,SUBJECTID,studentResult) VALUES('S1002','1','90');
INSERT INTO result (studentNo,SUBJECTID,studentResult) VALUES('S1002','2','108');
INSERT INTO result (studentNo,SUBJECTID,studentResult) VALUES('S1003','1','90');
INSERT INTO result (studentNo,SUBJECTID,studentResult) VALUES('S1002','3','80');

1.2.2修改语法(update…set…)

#修改语句语法
#UPDATE 表名 SET[CLOUMN_NAME=VALUE=1,COLUMN_NAEM1=VALUE2.....][WHERE CONDITION];
#注意”WHERE是条件语句，修改通常要添加条件语句，没有条件语句就修改的是整张表
#将学号为S1001的学生姓名改成周杰伦，电话改为12345，地址改为台北
UPDATE student SET studentId='S1001',PHONE='123545',ADDRESS='台北' WHERE studentId='s1001';

#将课程表中英语课时改为50，年级改为三年级
UPDATE `subject` SET CLASSHOUR = '50' ,GRADEID=3 WHERE SUBJECTNAME='英语';

#将一年级的所有课时课程统一增加3
UPDATE `subject` SET CLASSHOUR=CLASSHOUR+3,GRADEID=1 WHERE GRADEID='1';

#将成绩小于95的所有成绩增加5
UPDATE result SET studentResult=studentResult+5  WHERE studentResult<=95;

#将学生表中所有台北的学生密码改为0000 AND表示并且即同时满足
UPDATE student SET LOGINPSW='0000' WHERE ADDRESS='台北' AND SEX='男';

# 将学生表中所有北京和台北的密码改成11111 OR表示两个条件满足其一即可
UPDATE student SET LOGINPSW='11111' WHERE ADDRESS='台北' OR ADDRESS='北京';

#将成绩表中80到100分之间的学生成绩减5
#写法一:使用and
UPDATE result SET studentResult=studentResult-5 WHERE studentResult>=80 AND studentResult<=100;
#写法二：使用BETWEEN AND语句
UPDATE result SET studentResult=studentResult-5 WHERE studentResult BETWEEN 80 AND 100;

1.2.3删除语法(delete from … )

#删除语句语法
#DELETE FROM 表名 [WHERE CONDITION]

#删除成绩表中小于90的成绩
DELETE FROM result WHERE studentResult<90;

#删除所有的成绩
#不加任何条件的删除语句就是删除整张表，对数据库的操作是不可逆的所以无法恢复
DELETE FROM  result;

#格式化表.效果等同于不加条件的删除语句，但是格式化后的表，标识列会重新标识
TRUNCATE TABLE result;

#删除学生表中学号为S1001的学生信息
DELETE FROM student WHERE studentId='S1001';

#删除学生表中学号为S1002的学生信息，由于在学生表存在该学生的成绩，必须先删除该学生的成绩
DELETE FROM result WHERE studentNo='S1002';
DELETE FROM student WHERE studentId='S1002';

#删除年级表中年级编号为1的年级信息
#需要把要删除的信息相关的所有信息都要删了然后才能删除该信息
DELETE FROM result WHERE SUBJECTID='1';
DELETE FROM `subject` WHERE GRADEID='1';
DELETE FROM student WHERE GRADEID='1';
DELETE FROM grade WHERE GRADEID='1';

1.2.4查询语句

基础数据库

/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.5.61 : Database - myschool
*********************************************************************
*/

DROP DATABASE myschool;

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`myschool` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `myschool`;

/*Table structure for table `admin` */

DROP TABLE IF EXISTS `admin`;

CREATE TABLE `admin` (
  `LoginId` VARCHAR(50) NOT NULL,
  `LoginPwd` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`LoginId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

/*Data for the table `admin` */

INSERT  INTO `admin`(`LoginId`,`LoginPwd`) VALUES ('admin','123456');

/*Table structure for table `grade` */

DROP TABLE IF EXISTS `grade`;

CREATE TABLE `grade` (
  `GradeId` int(4) NOT NULL AUTO_INCREMENT,
  `GradeName` varchar(50) NOT NULL,
  PRIMARY KEY (`GradeId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `grade` */

insert  into `grade`(`GradeId`,`GradeName`) values (1,'S1'),(2,'S2'),(3,'Y2');

/*Table structure for table `result` */

DROP TABLE IF EXISTS `result`;

CREATE TABLE `result` (
  `ResultId` int(11) NOT NULL AUTO_INCREMENT,
  `StudentNo` varchar(50) NOT NULL,
  `SubjectId` int(11) NOT NULL DEFAULT '0',
  `StudentResult` int(11) DEFAULT '0',
  `ExamDate` datetime NOT NULL,
  PRIMARY KEY (`ResultId`),
  KEY `fk_result_subject` (`SubjectId`),
  KEY `fk_result_student` (`StudentNo`),
  KEY `ResultId` (`ResultId`),
  CONSTRAINT `fk_result_student` FOREIGN KEY (`StudentNo`) REFERENCES `student` (`studentno`),
  CONSTRAINT `fk_result_subject` FOREIGN KEY (`SubjectId`) REFERENCES `subject` (`SubjectId`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;

/*Data for the table `result` */

insert  into `result`(`ResultId`,`StudentNo`,`SubjectId`,`StudentResult`,`ExamDate`) values (1,'S2102002',3,90,'2009-12-13 00:00:00'),(2,'S2102003',3,100,'2009-12-13 00:00:00'),(3,'S2102004',3,79,'2009-12-13 00:00:00'),(4,'S2102005',3,80,'2009-12-13 00:00:00'),(5,'S2102006',3,61,'2009-12-13 00:00:00'),(6,'S2102007',3,82,'2009-12-13 00:00:00'),(7,'S2102008',3,35,'2009-12-13 00:00:00'),(8,'S2102009',3,64,'2009-12-13 00:00:00'),(9,'S2102010',3,65,'2009-12-13 00:00:00'),(10,'S1101001',1,80,'2010-01-22 00:00:00'),(11,'S1101002',1,61,'2010-01-22 00:00:00'),(12,'S1101003',1,82,'2010-01-22 00:00:00'),(13,'S1101004',1,55,'2010-01-22 00:00:00'),(14,'S1101005',1,64,'2010-01-22 00:00:00'),(15,'S1101006',1,65,'2010-01-22 00:00:00'),(16,'S1101007',1,88,'2010-01-22 00:00:00'),(17,'S1101008',1,67,'2010-01-22 00:00:00'),(18,'S1101009',1,68,'2010-01-22 00:00:00'),(19,'S1101010',1,44,'2010-01-22 00:00:00'),(20,'S1101012',1,71,'2010-01-22 00:00:00'),(21,'S1101013',1,44,'2010-01-22 00:00:00'),(22,'S1101015',1,86,'2010-01-22 00:00:00'),(23,'S1101016',1,75,'2010-01-22 00:00:00'),(24,'S1101017',1,76,'2010-01-22 00:00:00'),(25,'S1101018',1,90,'2010-01-22 00:00:00'),(26,'S1101019',1,100,'2010-01-22 00:00:00'),(27,'S1101001',2,80,'2010-02-15 00:00:00'),(28,'S1101002',2,61,'2010-02-15 00:00:00'),(29,'S1101003',2,82,'2010-02-15 00:00:00'),(30,'S1101004',2,35,'2010-02-15 00:00:00'),(31,'S1101005',2,64,'2010-02-15 00:00:00'),(32,'S1101006',2,65,'2010-02-15 00:00:00'),(33,'S1101007',2,88,'2010-02-15 00:00:00'),(34,'S1101009',2,68,'2010-02-15 00:00:00'),(35,'S1101010',2,44,'2010-02-15 00:00:00'),(36,'S1101011',2,75,'2010-02-15 00:00:00'),(37,'S1101012',2,71,'2010-02-15 00:00:00'),(38,'S1101013',2,44,'2010-02-15 00:00:00'),(39,'S1101015',2,86,'2010-02-15 00:00:00'),(40,'S1101016',2,75,'2010-02-15 00:00:00'),(41,'S1101017',2,38,'2010-02-15 00:00:00'),(42,'S1101018',2,90,'2010-02-15 00:00:00'),(43,'S1101019',2,100,'2010-02-15 00:00:00'),(44,'S1101011',1,68,'2010-02-01 00:00:00'),(45,'S1101014',1,75,'2010-02-01 00:00:00'),(46,'S1101008',2,45,'2010-03-01 00:00:00'),(47,'S1101014',2,60,'2010-03-01 00:00:00'),(48,'S2102001',8,100,'2010-03-22 00:00:00'),(49,'S2102002',8,55,'2010-03-22 00:00:00'),(50,'S2102003',8,60,'2010-03-22 00:00:00'),(51,'S2102004',8,70,'2010-03-22 00:00:00'),(52,'S2102005',8,85,'2010-03-22 00:00:00'),(53,'S2102006',8,88,'2010-03-22 00:00:00'),(54,'S2102007',8,95,'2010-03-22 00:00:00'),(55,'S2102008',8,44,'2010-03-22 00:00:00'),(56,'S2102009',8,68,'2010-03-22 00:00:00'),(57,'S2102010',8,88,'2010-03-22 00:00:00'),(58,'S2102011',8,88,'2010-03-22 00:00:00'),(59,'S1101001',3,88,'2010-03-22 00:00:00'),(60,'S1101002',3,50,'2010-03-22 00:00:00'),(61,'S1101003',3,44,'2010-03-22 00:00:00'),(62,'S1101004',3,77,'2010-03-22 00:00:00'),(63,'S1101005',3,75,'2010-03-22 00:00:00'),(64,'S1101006',3,63,'2010-03-22 00:00:00'),(65,'S1101007',3,15,'2010-03-22 00:00:00'),(66,'S1101008',3,55,'2010-03-22 00:00:00'),(67,'S1101009',3,69,'2010-03-22 00:00:00'),(68,'S1101010',3,90,'2010-03-22 00:00:00'),(69,'S1101011',3,95,'2010-03-22 00:00:00'),(70,'S1101001',4,100,'2010-03-22 00:00:00'),(71,'S1101002',4,60,'2010-03-22 00:00:00'),(72,'S1101003',4,65,'2010-03-22 00:00:00'),(73,'S1101004',4,77,'2010-03-22 00:00:00'),(74,'S1101005',4,75,'2010-03-22 00:00:00'),(75,'S1101006',4,63,'2010-03-22 00:00:00'),(76,'S1101007',4,45,'2010-03-22 00:00:00'),(77,'S1101008',4,55,'2010-03-22 00:00:00'),(78,'S1101009',4,69,'2010-03-22 00:00:00'),(79,'S1101010',4,90,'2010-03-22 00:00:00'),(80,'S1101011',4,100,'2010-03-22 00:00:00');

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `studentno` varchar(16) NOT NULL,
  `loginPwd` varchar(20) NOT NULL,
  `studentName` varchar(50) NOT NULL,
  `sex` char(2) NOT NULL DEFAULT 'M',
  `gradeId` int(4) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `bornDate` datetime DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `identityCard` varchar(18) DEFAULT NULL,
  PRIMARY KEY (`studentno`),
  KEY `fk_student_grade` (`gradeId`),
  CONSTRAINT `fk_student_grade` FOREIGN KEY (`gradeId`) REFERENCES `grade` (`GradeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`studentno`,`loginPwd`,`studentName`,`sex`,`gradeId`,`phone`,`address`,`bornDate`,`email`,`identityCard`) values ('S1101001','228996248','姚恒','M',1,'123456789','南京中博软件学院','1991-09-01 00:00:00','yaoheng@qq.com','NUll'),('S1101002','228996249','王明','F',1,'987654321','南京中博软件学院','1992-09-01 00:00:00','wangming@qq.com','NUll'),('S1101003','228996250','杨长旺','M',1,'1234565412','南京中博软件学院','1993-09-01 00:00:00','yangzhangwang@qq.com','NUll'),('S1101004','228996251','白燕','M',1,'676151367','北京什刹海体校','1990-09-01 00:00:00','cxmnnrhfn@sjsam.com','NUll'),('S1101005','228996252','夏一桐','M',1,'397739963','北京市朝阳区大屯','1989-09-12 00:00:00','npgiygxox@ootml.com','NUll'),('S1101006','228996253','欧阳燕飞','M',1,'13512345678','河南省南阳市','1987-06-19 00:00:00','dkwkiqbrj@yrkro.com','NUll'),('S1101007','228996254','孟祥亚','M',1,'13512345679','河南省洛阳市涧西区','1993-02-22 00:00:00','gekdinmky@ijisq.com','NUll'),('S1101008','228996257','凌洋','F',1,'15812345680','湖南省长沙','1989-11-30 00:00:00','NULL','NUll'),('S1101009','228996259','杨阳','M',1,'13512345681','上海市长虹区','1989-01-19 00:00:00','NULL','NUll'),('S1101010','228996263','方晴','M',1,'13412345682','山东省聊城市','1990-07-12 00:00:00','NULL','NUll'),('S1101011','228996267','圆荷','F',1,'13512344483','河北省石家庄','1989-03-16 00:00:00','idfwxlbjr@bkxko.com','NUll'),('S1101012','228996270','崔今生','F',1,'13512345684','河北省邯郸市','1990-01-05 00:00:00','qrakldetd@ogtso.com','NUll'),('S1101013','228996271','姜北','M',1,'578346237','','1990-01-25 00:00:00','soppebati@hwxpo.com','NUll'),('S1101014','228996273','姜丫丫','M',1,'926457035','北京市朝阳区慧忠里','1992-06-24 00:00:00','rfkhgcxhy@oomkw.com','NUll'),('S1101015','228996274','孙河','M',1,'895684949','北京市通州区','1990-06-25 00:00:00','kdaqojpjj@olmcy.com','NUll'),('S1101016','228996275','王可','M',1,'328884827','北京市石景山','1990-06-26 00:00:00','bojzfsixf@qjwgw.com','NUll'),('S1101017','228996276','赵七','F',1,'511686053','北京市海淀区中关村','1985-06-27 00:00:00','ltshcitdp@qdpeh.com','NUll'),('S1101018','228996279','买燕','M',1,'155426854','河南省周口','1990-06-28 00:00:00','rmpnhbiop@mwrom.com','NUll'),('S1101019','228996281','冯征','M',1,'436987577','湖北省襄樊','1987-06-29 00:00:00','mdokehdic@omcte.com','NUll'),('S2102001','228996284','包卫丽','F',2,'13875940258','湖州德清','1990-07-01 00:00:00','sakuralove2829@yahoo.com','NUll'),('S2102002','228996285','吴可鹏','M',2,'13875940259','','1991-07-02 00:00:00','peggyonhkhk@yahoo.com','NUll'),('S2102003','228996287','於炉冰','M',2,'13875940260','','1990-07-03 00:00:00','yyyyyyyyyyu@yahoo.com','NUll'),('S2102004','228996290','朱月清','F',2,'13875940261','衢州开化','1990-07-04 00:00:00','yinyin01094@yahoo.com.hk','NUll'),('S2102005','228996291','赵献星','M',2,'13875940262','杭州建德','1989-11-30 00:00:00','ada19262002@hotmail.com','NUll'),('S2102006','228996292','吴宝玉','F',2,'13875940263','湖州埭溪','1986-12-01 00:00:00','NULL','NUll'),('S2102007','228996293','胡梦秋','F',2,'13875940264','杭州临安','1989-12-02 00:00:00','bobo_lok13@yahoo.com','NUll'),('S2102008','228996302','吴春红','F',2,'13875940265','金华金东区','1989-12-03 00:00:00','yintakli@yahoo.com.hk','NUll'),('S2102009','228996304','张文俊','M',2,'13974807750','绍兴上虞','1992-12-04 00:00:00','natalielam01098@yahoo.com','NUll'),('S2102010','228996305','乐晓燕','M',2,'13007426477','台州三门','1989-12-05 00:00:00','NULL','NUll'),('S2102011','228996307','裴珍','M',2,'13873140386','北京大兴区','1989-09-18 00:00:00','lingling52030996@hotmail.com','NUll'),('S2102012','228996308','周巧飞','M',2,'13107428484','','1989-09-19 00:00:00','yuenfunki@yahoo.com.hk','NUll'),('S2102013','228996309','王晓文','M',2,'13574220088','杭州建德','1992-09-20 00:00:00','kykwok9@hotmail.com','NUll'),('S2102014','228996312','卢凤华','F',2,'13574220089','衢州开化','1989-09-21 00:00:00','sulia123@yahoo.com','NUll'),('S2102015','228996316','胡灵','F',2,'13574220090','台州天台','1989-09-22 00:00:00','chankwaiching@yahoo.com','NUll'),('S2102016','228996317','盛立','M',2,'13574220091','安徽阜阳','1989-09-23 00:00:00','yankchan2004@yahoo.com','NUll'),('S2102017','228996318','吴孝红','M',2,'13678945617','黑龙江哈尔滨','1989-09-24 00:00:00','ayako04620@yahoo.com','NUll'),('S2102018','228996323','曾飞燕','M',2,'13678945618','杭州临安','1993-09-25 00:00:00','ayako04620@yahoo.com','NUll'),('S2102019','228996324','陈科','M',2,'13678945619','新疆乌鲁木齐','1989-06-12 00:00:00','NULL','NUll'),('S2102020','228996325','严丽丽','M',2,'13678945620','新疆伊宁','1990-05-04 00:00:00','phoebe_swy@yahoo.com','NUll'),('Y21003001','228996326','孙瑞晨','M',3,'13574220096','金华兰溪','1989-05-04 00:00:00','faat11@yahoo.com','NUll'),('Y21003002','228996327','黄金宵','M',3,'13574220097','绍兴诸暨','1989-01-24 00:00:00','NULL','NUll'),('Y21003003','228996329','陈婷婷','M',3,'13574220098','衢州常山','1989-04-07 00:00:00','mandylau1986@yahoo.com','NUll'),('Y21003004','228996334','章巧丹','F',3,'13174220099','宁波宁海','1989-06-09 00:00:00','NULL','NUll'),('Y21003005','228996340','刘丽云 ','F',3,'13174220100','','1988-11-12 00:00:00','raymondchoi04@yahoo.com','NUll'),('Y21003006','228996341','丁 亮','M',3,'13174220101','嘉兴嘉善','1989-01-13 00:00:00','tlh10202003@yahoo.com','NUll'),('Y21003007','228996342','李明华','F',3,'13574220102','金华兰溪','1988-03-19 00:00:00','mimichoi276@yahoo.com','NUll'),('Y21003008','228996343',' 金 晶','M',3,'13574220103','','1988-03-20 00:00:00','mapdhhljp@ncqfg.com','NUll'),('Y21003009','228996346','张世庆','M',3,'13574220104','湖州双林','1990-03-21 00:00:00','godspnrel@eblox.com','NUll'),('Y21003010','228996348','方汝滔','M',3,'13574220105','衢州常山','1988-03-22 00:00:00','wieiowzmq@jplop.com','NUll'),('Y21003011','228996349','王 波','M',3,'13574220106','北京朝阳区','1988-03-23 00:00:00','wengsanc14l@yahoo.com','NUll'),('Y21003012','228996351','颜俊俊','M',3,'13374220107','衢州龙游','1988-03-24 00:00:00','wengsanc15l@yahoo.com','NUll'),('Y21003013','228996352','刘雪琼','F',3,'13374220108','绍兴嵊州','1989-06-26 00:00:00','wengsanc16l@yahoo.com','NUll'),('Y21003014','228996353','胡晓奕','F',3,'13374220109','','1989-09-23 00:00:00','wengsanc17l@yahoo.com','NUll'),('Y21003015','228996357','王莉莉','F',3,'13374220110','湖州杨家埠','1989-11-07 00:00:00','NULL','NUll'),('Y21003016','228996362','金辉','M',3,'13574220111','台州温岭','1989-04-24 00:00:00','wengsanc19l@yahoo.com','NUll'),('Y21003017','228996364','郑峰','M',3,'13574220112','北京海淀区','1989-09-24 00:00:00','wengsanc20l@yahoo.com','NUll'),('Y21003018','228996365','何国英','M',3,'13574220113','台州温岭','1988-03-01 00:00:00','NULL','NUll'),('Y21003019','228996370','方振','M',3,'13574220114','台州三门','1989-11-29 00:00:00','wengsanc22l@yahoo.com','NUll'),('Y21003020','228996371','雷应飞','M',3,'13574220115','湖州吴兴区','1989-01-16 00:00:00','wengsanc23l@yahoo.com','NULL'),('S1101003','228996247','凌辉','F',1,'353149818','北京市海淀区成府路','1993-04-04 00:00:00','eepispykh@oitbl.com','NUll');

/*Table structure for table `subject` */

DROP TABLE IF EXISTS `subject`;

CREATE TABLE `subject` (
  `SubjectId` int(11) NOT NULL AUTO_INCREMENT,
  `SubjectName` varchar(50) NOT NULL DEFAULT '',
  `ClassHour` int(11) DEFAULT '0',
  `GradeId` int(4) NOT NULL,
  PRIMARY KEY (`SubjectId`),
  KEY `fk_subject_grade` (`GradeId`),
  CONSTRAINT `fk_subject_grade` FOREIGN KEY (`GradeId`) REFERENCES `grade` (`GradeId`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

/*Data for the table `subject` */

insert  into `subject`(`SubjectId`,`SubjectName`,`ClassHour`,`GradeId`) values (1,'使用C#语言开发数据库应用系统',36,1),(2,'深入.NET平台和C#编程',76,2),(3,'设计MySchool数据库',42,2),(4,'基于.NET平台的软件系统分层开发',44,2),(5,'面向对象程序设计',59,2),(6,'使用JavaScript增强交互效果',40,2),(7,'使用Java EE技术开发新闻发布系统',55,2),(8,'使用ASP.NET技术开发网上书店',86,3),(9,'开发基于Ajax和控件技术的Web应用系统',60,3),(10,'使用Java框架技术开发网上信息发布系统',52,3),(11,'使用Java企业级技术开发企业应用',68,3);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

1.精准查询语句

#查询语句的语法
#SELECT [列名、、、] FROM 表名 WHERE 条件

#查询学生表中的所有信息，*表示所有列
SELECT * FROM student;

#查询学生的学号、姓名、性别、电话、生日
SELECT studentno,studentName,sex,phone,bornDate FROM student;

#对查询的列名进行重命名,通过as进行重命名，AS可省略
SELECT studentno AS ID ,studentName AS NAME,sex AS XB, phone TEL,bornDate BIRTHDAY FROM student;

#查询年级编号为2的所有女生信息
SELECT * FROM student WHERE gradeId='2'AND sex='F';

#查询所有80后学生信息
SELECT * FROM student WHERE bornDate BETWEEN '1980-1-1' AND '1989-12-31';

#查询参加过考试的学号信息(使用DISTINCT去除关键字)
SELECT DISTINCT StudentNo FROM student;

2.模糊查询语句

#模糊查询
#LIKE是模糊查询的关键字，在模糊查询中，通过使用通配符表示模糊内容
#通配符%表示任意长度的任意字符
SELECT * FROM student WHERE studentName LIKE '%丽%';

#查询北京地区的所有男学生
SELECT * FROM student WHERE address LIKE '%北京%' AND sex='M';

#查询所有姓王的单名学生信息,
#通配符'_'表示任意一个字符
SELECT * FROM student WHERE studentName LIKE '王_';

#查询身份证号码为空的学生信息,查询数据为空的信息‘is NULL’,查询数据不为空的信息'NOT NULL';
SELECT * FROM student WHERE email is NULL;

3.复杂度查询

#查询排序
#查询所有的成绩然后升序排列
#ORDER BY 关键字，用于根据指定的列排序
SELECT * FROM result ORDER BY StudentResult;

#如果要对结果进行降序排列，对排序的列添加desc关键字
SELECT * FROM result ORDER BY StudentResult DESC;

#根据课程编号升序(关键字为ASC)，课程编号相同的成绩再根据成绩降序排列(先根据课程编号升序，若相同再根据成绩降序，中间用逗号隔开)
SELECT * FROM result ORDER BY SubjectId ASC,StudentResult DESC;

#查询课程编号为1的前五名成绩信息(若筛选和排序同在，先筛选再排序，若反之则报错)LIMIT关键字表示返回指定的区间内容
SELECT * FROM result WHERE SubjectId='1' ORDER BY StudentResult DESC LIMIT 5;

#查询课程编号为1的第六到第十五名学生信息
#LIMIT语句，指定一个数字，表示返回前多少条数据
#LIMIT语句，指定两个数字，第一个数字表示跳过前多少条数据，第二个数字表示再返回多少条数据 
SELECT * FROM result WHERE SubjectId='1' ORDER BY StudentResult DESC LIMIT 5,10;

#每页7条数据。显示第4页信息
SELECT * FROM student LIMIT 21,7;

#查询学生表中年龄最小的前三位学生的姓名和地址信息
SELECT studentName,address FROM student WHERE bornDate ORDER BY bornDate DESC LIMIT 3;

#查询学生表中北京地区年龄最大的三位女生姓名和地址
SELECT studentName 姓名,bornDate 生日, phone 电话,address 地址 FROM student WHERE address LIKE '%北京%' AND sex ='F'  ORDER BY bornDate ASC LIMIT 3;

4.内连接查询

USE myschool;
#连接查询
#查询学生的学号，姓名，性别，电话，年纪名称
#首先学生表中没有年级名称，只有年级编号，这里我们采用学生表和年级表连接查询
#使用INNER JOIN 连接查询年级表，使用ON指定连接查询的条件
#这里的含义是：当学生表的年级编号等于年级表的年级编号，返回年级表中对应的年级名称
SELECT studentno,studentName,sex,phone,GradeName  FROM student INNER JOIN grade ON student.`gradeId`=grade.`GradeId`;

#查询课程编号、课程名称、课时、年级名称
SELECT SubjectId,SubjectName,ClassHour,GradeName FROM SUBJECT INNER JOIN grade ON `subject`.GradeId=grade.GradeId;

#查询考试编号、学生姓名、课程名称、考试成绩、考试时间
SELECT ResultId,studentName,SubjectName,StudentResult,ExamDate FROM result 
INNER JOIN student ON result.StudentNo=student.studentno
INNER JOIN `subject` ON result.SubjectId=`subject`.SubjectId;

#查询考试编号、学生姓名、学生所属年纪名称、课程名称、考试成绩、考试时间
SELECT ResultId,studentName,GradeName,SubjectName,StudentResult,ExamDate FROM result
INNER JOIN student ON result.StudentNo=student.studentno
INNER JOIN `subject` ON result.SubjectId=`subject`.SubjectId
INNER JOIN grade ON student.gradeId=grade.GradeId

#查询考试编号、学生姓名、课程名称、课程所属年纪名称、考试成绩、考试时间
SELECT ResultId,studentName,SubjectName,GradeName,StudentResult,ExamDate FROM result
INNER JOIN student ON result.StudentNo=student.studentno
INNER JOIN `subject` ON result.SubjectId=`subject`.SubjectId
INNER JOIN grade ON `subject`.gradeId=grade.GradeId

#查询“使用C#语言开发数据库应用系统”课程的前三名成绩，查询对应的学生姓名，考试成绩，考试时间
SELECT studentName,StudentResult,ExamDate FROM result
INNER JOIN student ON result.StudentNo=student.studentno
INNER JOIN `subject` ON result.SubjectId=`subject`.SubjectId
WHERE SubjectName='使用C#语言开发数据库应用系统' ORDER BY StudentResult DESC LIMIT 3;

#查询学生的学号，姓名，性别，电话，年级名称
#注意在表连接查询中列如果唯一属于某个表，在查询时可以省略表名，列名
#如果某个列在两张表中都存在，就必须明确查询的该列属于哪张表
SELECT studentno,studentName,sex,phone,grade.GradeId,GradeName  FROM student 
INNER JOIN grade ON student.`gradeId`=grade.`GradeId`;

#在表连接查询中可以使用as关键字定义表的别名
SELECT studentno,studentName,sex,phone,g.GradeId,GradeName  FROM student AS s 
INNER JOIN grade AS g ON s.`gradeId`=g.`GradeId`;

5.外连接查询

#外连接查询
#查询参加过考试的学生姓名和电话
#内连接查询是两张表中关联的数据，外连接查询是即返回两张表中关联的数据也返回不关联的数据
#外连接查询，又分为：左外连接和右外连接
SELECT DISTINCT studentName,phone FROM result
INNER JOIN student ON result.StudentNo=student.studentno

#查询未参加过考试的学生姓名和电话
#内连接查询是两张表中关联的数据，外连接查询是即返回两张表中关联的数据也返回不关联的数据
#外连接查询，又分为：左外连接(LEFT JOIN)和右外连接(RIGHT JOIN);
SELECT studentName 姓名,phone 电话,StudentResult 成绩 FROM result 
RIGHT JOIN student ON result.StudentNo=student.studentno WHERE StudentResult IS NULL;

SELECT studentName 姓名,phone 电话,StudentResult 成绩 FROM student
LEFT  JOIN result ON result.StudentNo=student.studentno WHERE StudentResult IS NULL;

6.子查询

#子查询
#查询是S1年级的所有学生信息
#方式一:使用连接查询,注意表连接查询是将两张表的内容都返回
SELECT * FROM student INNER JOIN grade ON grade.GradeId=student.gradeId WHERE GradeName='S1';

#方式二:使用子查询，在where语句中写一个查询语句，返回查询条件
SELECT * FROM  student WHERE gradeId=(SELECT gradeId FROM grade WHERE GradeName='S1');

#查询S1年级学生的所有考试成绩
#1.查询S1年级的所属学生学号
#2.根据查询到的学号，再查询对应的考试成绩
#SELECT studentno FROM student WHERE gradeId = (SELECT gradeId FROM grade WHERE GradeName='S1');
#注意:子查询的条件返回的结果，如果不止列使用in，如果你分不清用=号还是用in，全部用in
SELECT * FROM result WHERE StudentNo IN (
    SELECT StudentNo FROM student WHERE gradeId IN (
          SELECT GradeId FROM grade WHERE GradeName='s1'
     )
);

#查询2009-12-13参加了“设计myschool数据库”课程考试的学生姓名和电话
SELECT studentName,phone FROM student
INNER JOIN result ON student.studentno = result.StudentNo
INNER JOIN `subject` ON result.SubjectId=`subject`.SubjectId
WHERE SubjectName = '设计myschool数据库' AND ExamDate='2009-12-13';

SELECT studentName,phone FROM student WHERE studentno IN (
 SELECT StudentNo FROM result WHERE ExamDate='2009-12-13' AND  SubjectId=(
	SELECT SubjectId FROM `subject` WHERE SubjectName='设计myschool数据库'
 )
);

#查询年龄比白燕小的学生信息
SELECT * FROM student WHERE bornDate>(
	SELECT bornDate FROM student WHERE studentName='白燕'
)

#查询“使用C#语言开发数据库应用系统”课程成绩至少有一次考试刚好等于44分的成绩
SELECT * FROM student WHERE StudentNo IN(
	SELECT StudentNo FROM result WHERE StudentResult=44 AND SubjectId = (
		SELECT SubjectId FROM `subject` WHERE SubjectName='使用C#语言开发数据库应用系统'
	)
);

#查询参加最近一次“使用C#语言开发数据库应用系统”课程最近一次考试的在读学生名单
SELECT * FROM student WHERE StudentNo IN(
	SELECT StudentNo FROM result WHERE SubjectId=(
	SELECT SubjectId FROM `subject` WHERE SubjectName='使用C#语言开发数据库应用系统'
	) AND ExamDate = (
			SELECT ExamDate FROM result WHERE SubjectId = (
					SELECT SubjectId FROM `subject` WHERE SubjectName='使用C#语言开发数据库应用系统'
			)ORDER BY ExamDate DESC LIMIT 1
	)
);

#查询未参加最近一次“使用C#语言开发数据库应用系统”课程最近一次考试的在读学生名单
SELECT * FROM student WHERE StudentNo NOT IN(
	SELECT StudentNo FROM result WHERE SubjectId=(
	SELECT SubjectId FROM `subject` WHERE SubjectName='使用C#语言开发数据库应用系统'
	) AND ExamDate = (
			SELECT ExamDate FROM result WHERE SubjectId = (
					SELECT SubjectId FROM `subject` WHERE SubjectName='使用C#语言开发数据库应用系统'
			)ORDER BY ExamDate DESC LIMIT 1
	)
);

#查询S1学期开设的课程
SELECT * FROM `subject` WHERE GradeId IN (
	SELECT GradeId FROM grade WHERE GradeName='S1'
);

#查询“深入.NET平台和C#编程”考试前五名学生信息(LIMIT 语句不可放在子查询中)
SELECT * FROM student WHERE studentno IN(
	SELECT StudentNo FROM result WHERE SubjectId=(
		SELECT SubjectId FROM `subject` WHERE SubjectName='深入.NET平台和C#编程'
			)ORDER BY StudentResult DESC
) LIMIT 5;

7.分组查询

USE myschool;
#使用分组查询统计每个年级的学生人数，分组查询的关键字是 GROUP BY
SELECT COUNT(*) AS 学生人数,GradeName AS 年级 FROM student 
INNER JOIN grade ON student.gradeId=grade.GradeId
GROUP BY GradeName;

#统计每个年级男生和女生各多少人，GROUP BY可以根据多个列进行分组
SELECT COUNT(*) AS 学生人数,GradeName AS 年级,sex AS 性别 FROM student 
INNER JOIN grade ON student.gradeId=grade.GradeId
GROUP BY GradeName,sex;

#统计每个同学的总成绩
SELECT studentName AS 学生姓名,SUM(StudentResult) 总成绩,AVG(StudentResult) 平均成绩 FROM result
INNER JOIN student ON student.studentno=result.StudentNo
GROUP BY studentName;

#统计平均分大于80分的学生名单,HAVING用于对分组后的成绩进行筛选，WHERE用与对分组前的结果进行筛选
SELECT studentName AS 学生姓名,SUM(StudentResult) 总成绩,AVG(StudentResult) 平均成绩 FROM result
INNER JOIN student ON student.studentno=result.StudentNo
GROUP BY studentName HAVING AVG(StudentResult)>80 ORDER BY AVG(StudentResult) DESC;

#返回平均分大于80的学生的姓名、生日、电话、地址
SELECT studentName 姓名,bornDate 生日,phone 电话,address 地址 FROM student WHERE studentno IN(
				SELECT StudentNo FROM result GROUP BY studentno HAVING AVG(StudentResult)>80
);

#返回每个年级平均分大于80分的人数
SELECT COUNT(*) 人数, GradeName 年级 FROM student 
INNER JOIN grade ON student.gradeId=grade.GradeId
WHERE studentno IN(
				SELECT StudentNo FROM result GROUP BY studentno HAVING AVG(StudentResult)>80
) GROUP BY GradeName

#返回及格的平均分大于80分人数超过四人的年级
SELECT COUNT(*) 人数,GradeName 年级 FROM student
INNER JOIN grade ON student.gradeId=grade.GradeId
WHERE studentno IN(
		SELECT StudentNo FROM result WHERE StudentResult>=60 GROUP BY studentno HAVING AVG(StudentResult)>80
)GROUP BY GradeName HAVING COUNT(*)>6;

1.2.5函数

# 字符串函数
# CONCAT()字符串连接
SELECT * FROM student WHERE address LIKE CONCAT('%','北京','%');
# INSERT()字符串替换
#从指定位置开始删除指定长度的字符串
SELECT INSERT('TABLESPACE',3,3,'');
#删除的内容可以共同函数的第四个参数指定替换的内容
SELECT INSERT('12345678901',4,5,'***');
#在查询语句中如何使用函数
SELECT studentName AS 姓名,INSERT(phone,4,5,'***') AS 电话 FROM student;
# LOWER()将字符串转为小写
SELECT LOWER('HAooooaaa');
# UPPER()将字符串转为大写
SELECT UPPER('HAooooaaa');
# SUBSTRING()字符串截取
#注意：这里的3表示初始位置，4是截取长度，如果只传位置则表示从该位置往后全部截取
SELECT SUBSTRING('HOQaaNaaaDQDaa',3,4);
#查询学生姓和名
SELECT SUBSTRING(studentName,1,1) AS 姓, SUBSTRING(studentName,2) AS 名 FROM student;

# 日期函数
# CURDATE()获取当前日期
SELECT CURRENT_DATE(); 
# CURTIME()获取当前时间
SELECT CURRENT_TIME();
# NOW()获取当前日期和时间
SELECT NOW();
# YEAR()获取年
SELECT YEAR(NOW())
# MONTH()获取月
SELECT MONTH(NOW())
# DAY()获取日
SELECT DAY(NOW())
# HOUR()获取小时
SELECT HOUR(NOW())
# MINUTE()获取分钟
SELECT MINUTE(NOW())
# SECOND()获取秒
SELECT SECOND(NOW())
# WEEK()获取周
SELECT WEEK(NOW())
# DATEDIFF()获取两个日期间隔天数
SELECT DATEDIFF(NOW(),'2001-11-6');
# ADDDATE()指定的日期上增加天数
SELECT ADDDATE(NOW(),10);

# 数学函数
# CEIL()向上取整
SELECT CEIL(666.9);
# FLOOR()向下取整
SELECT FLOOR(8008.9);
# RAND()随机数
SELECT RAND();

#查询学生的姓名和年龄
SELECT studentName 姓名,FLOOR(DATEDIFF(NOW(),bornDate)/365) 年龄 FROM student

#聚合函数：一般用于对查询结果进行统计
#	COUNT(DISTINCT expr,[expr...]):用于统计数量,方式有两种，一种：COUNT(*)表示统计行数,另一种：COUNT(列名)表示统计不为null的行数

#查询S1年级有多少学生
SELECT COUNT(*) AS 学生数量 FROM student WHERE GradeId = (
			SELECT GradeId FROM grade WHERE GradeName='S1'
);

#查询学生邮箱为null的人数
SELECT COUNT(*) FROM student WHERE email IS NULL;

#sum：用于求和
SELECT SUM(StudentResult) AS 总分 FROM result;

#查询白燕的总分,平均分，最高分，最低分
SELECT	SUM(StudentResult),CEIL(AVG(StudentResult)),MAX(StudentResult),MIN(StudentResult) FROM result WHERE StudentNo=(
SELECT StudentNo FROM student WHERE studentName='白燕'
);

#查询S2年级的所有学生的总分和平均分
SELECT SUM(StudentResult),CEIL(AVG(StudentResult)) FROM result WHERE StudentNo IN(
	SELECT StudentNo FROM student WHERE GradeId=(
		SELECT GradeId FROM grade WHERE GradeName='S2'
	)
);

1.2.6事务处理

#创建一张账户表
DROP TABLE BANK IF EXISTS;
CREATE TABLE IF NOT EXISTS BANK(
		ID INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(20) NOT NULL,
		MONEY DOUBLE NOT NULL
);

#插入数据
INSERT INTO	BANK(NAME,MONEY) VALUES ('张靓颖',1000000),('何超莲',203900);

#转账业务，[张靓颖，转账7000给何超莲]至少需要更新两条sql语句
#如果第一条sql语句更新成功了，第二条sql语句更新失败了，业务就会出现问题
#这个时候就需要使用事务对这个完整的业务进行保护，需要注意的是，在mysql中，默认开启的是自动事务，也就是每一条独立的sql语句就是一个事务，所以多个sql语句作为一个完整的事务就要先关闭自动服务
SET AUTOCOMMIT=0;#关闭自动提交事务
START TRANSACTION;#开启事务
UPDATE BANK SET MONEY = MONEY+7000 WHERE NAME = '张靓颖';
UPDATE BANK SET MONEY = MONEY-7000 WHERE NAME = '何超莲';
#假设转账过程中发生了意外，本次转账业务不能提交
#此时可以让事务回滚（撤销提交）
#ROLLBACK是回滚数据
ROLLBACK;
#假设转账过程中没有发函俄国意外，提交本次转账业务
#COMMIT是提交事务
COMMIT;
SET AUTOCOMMIT = 1 ;#重新开启自动提交事务

1.2.7视图的创建

#查询学号，姓名，学生所属年级，课程名称，考试成绩，考试时间，
#可以将下面的sql语句的返回结果创建一个视图
CREATE VIEW VW_RESULT
AS
SELECT student.`StudentNo`,studentName,SubjectName,StudentResult,ExamDate FROM result
INNER JOIN student ON result.StudentNo=student.studentno
INNER JOIN grade ON student.gradeId = grade.GradeId
INNER JOIN `subject` ON `subject`.SubjectId=result.SubjectId;

#在视图的基础上继续筛选结果：查询成绩大于80分学号，姓名，学生所属年级，课程名称，考试成绩，考试时间，
SELECT * FROM VW_RESULT WHERE StudentResult>80;

#在视图的基础上继续筛选结果：查询S1年级成绩大于80分学号，姓名，学生所属年级，课程名称，考试成绩，考试时间，
SELECT * FROM VW_RESULT WHERE StudentResult>80 AND  GradeName='S1';