Day04 狂神说Java-MySQL＞P15-P19

P15>Delete和Truncate详解

-- 删除数据
DELETE FROM `student` WHERE id = 1;
-- 清空(完全清空一个数据库表，表的结构和索引约束不会变)
TRUNCATE `student`
/*
delete和truncate区别
相同点：都能删除数据，都不会删除表结构
不同点：1. TRUNCATE 重新设置自增列，计数器会清0
	2. TRUNCATE 不会影响事务
*/

-- 测试delete和truncate区别
CREATE TABLE `test`(
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `coll` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET utf8

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')
DELETE FROM `test` -- 不影响自增
TRUNCATE TABLE `test` -- 影响自增
/*
delete删除的问题，重启数据库
1. INNODB 自增列会从1开始（存在内存中的，断电即失）
2. MYISAM 继续从上一个自增量开始（存在文件中的不会丢失）
*/

P16>基本的Select语句和别名使用

DQL 数据查询语言 （很重要）

CREATE DATABASE `school`;
USE `school`;

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;

INSERT INTO `grade`(`GradeID`,`GradeName`) VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

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;

INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) VALUES (1000,1,'2013-11-11 16:00:00',85),(1000,2,'2013-11-12 16:00:00',70),(1000,3,'2013-11-11 09:00:00',68),(1000,4,'2013-11-13 16:00:00',98),(1000,5,'2013-11-14 16:00:00',58);

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;

INSERT INTO `student` (`Studentno`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`Borndate`,`Email`,`IdentityCard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');


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;

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程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

-- 查询全部学生
SELECT * FROM student
-- 查询全部成绩
SELECT * FROM result
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名，给结果起一个名字 AS
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s
-- 函数 Concat
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student

P17>去重及数据库的表达式

SELECT * FROM result
-- 查询有哪些同学参加了考试
SELECT `StudentNo` FROM result
-- 发现重复数据，去重
SELECT DISTINCT `StudentNo` FROM result
-- 查询系统版本
SELECT VERSION()
-- 查询计算结果
SELECT 100*3-1 AS 计算结果
-- 查询自增步长
SELECT @@auto_increment_increment
-- 学员考试成绩+1
SELECT `StudentNo`,`StudentResult`+1 AS '+1后' FROM result

P18>where子句之逻辑运算符

and &&
or ||
not !
一定返回布尔值

-- and 
SELECT StudentNo,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100

SELECT StudentNo,`StudentResult` FROM result
WHERE StudentResult>=95 && StudentResult<=100
-- 模糊查询
SELECT StudentNo,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 除了1000号学生之外同学的成绩
SELECT StudentNo,`StudentResult` FROM result
WHERE StudentNo!=1000

SELECT StudentNo,`StudentResult` FROM result
WHERE NOT StudentNo=1000

P19>模糊查询操作符详解

IS NULL
IS NOT NULL
BETWEEN … AND
LIKE
IN

-- 模糊查询
-- 查询姓刘的同学
-- 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 '%嘉%'
-- in
-- 查询1001 1002 1003号学员
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询在北京的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN ('北京');
-- null not null
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` OR address IS NULL
-- 查询有出生日期的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL
-- 查询没有出生日期的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL