Day11 MySQL基础04

MySQL基础04

3.5删除

delete 命令

-- 避免这样
delete from `stuedent`

delete from `stuedent` where id='1'

TRUNCATE命令

TRUNCATE `student`

作用：完全清空一个数据库表，表的结构和索引约束不会变

delete和TRUNCATE的区别

• 相同点：都能删除数据，都不会删除表结构
• 不同：

TRUNCATE 重新设置自增列 计数器会归零

TRUNCATE不会影响事务

-- 测试delete和truncate的区别
CREATE TABLE `car`(
`nb` INT(6) NOT NULL AUTO_INCREMENT,
`LTT` VARCHAR(6)NOT NULL,
PRIMARY KEY(`nb`)
)ENGINE INNODB CHARSET utf8


INSERT INTO `car`(`LTT`) VALUES('11'),('22')


DELETE FROM `car`  -- 不会影响自增


TRUNCATE TABLE `car`  -- 自增会清零

-- 测试语句多写方便以后查阅

DELETE删除的问题

• 使用完后重启数据库

INNODB 自增列会从1开始（存在内存当中，断电即失）

MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）

---

4、DQL语言（查询数据）（重点的重点）

---

4.1DQL

(Data Query Language：数据查询语言)

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询都能做
• 数据库中最核心的语言
• 使用频率最高的语句

---

4.2指定查询字段

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
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;

-- 创建年级表
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;

-- 创建科目表
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 = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
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;



-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(10002,'123456','张伟',0,2,'13800001234','湖南省长沙市雨花区','1980-1-1','text123@qq.com','123456198234'),
(10401,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','12345619001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'datetime',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);

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');



-- 插入科目数据
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);


-- 查询全部的student
SELECT *FROM `student`

-- 查询指定字段
SELECT `address`,`email`FROM `student`


-- 使用别名，给结果起名字  AS（可以给字段起别名，也可以表起别名）
SELECT `address`AS 地址,`email` AS 电子邮件 FROM `student`[AS s]


-- 函数 concat
SELECT CONCAT('地址：',`address`)AS 新名字 FROM student

去重 distinct

-- 查询有哪些同学参加了考试，成绩
SELECT *FROM `result`-- 查询全部的成绩
-- 查询了哪些同学参加了考试
SELECT`studentno`FROM`result`
-- 发现数据重复，决定去重
SELECT DISTINCT `studentno`FROM`result`

作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条

数据库的列（表达式）

-- 查询系统版本(函数)
SELECT VERSION()
-- 用来计算（表达式）
SELECT 100*3/3 AS 我是sb
-- 查询自增的步长（变量）
SELECT @@auto_increment_increment
-- 学员考试成绩+1分查看
SELECT `studentno`,`studentresult`+1 FROM`result`

数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量…

• select 表达式 from 表

---

4.3where条件子句

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

搜索的条件由一个或者多个表达式组成 结果-布尔值

逻辑运算符

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

• 尽量使用英文字母

-- where
SELECT studentno,`studentresult`FROM result

-- 查询考试成绩在95-100之间的
SELECT studentno,`studentresult`FROM result WHERE studentresult>=95 AND 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  

---

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null，结果为真
is not null	a is not null	如果操作符为not null，结果为真（如果为null，结果为flase）
BETWEEN	a between b and c	若a在b和c之间，则结果为真
Like	a like b	SQL匹配，如果a匹配b,则结果为真
in	a in (a1,a2,a3…)	假设a在a1、或者a2…其中的某一个值中，结果为真

-- 模糊查询
-- 查询姓赵的同学
-- 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'赵__';


-- in
-- 查询1001,1002,1003号学员
SELECT `studentno`,`studentname`FROM student WHERE studentno IN (1000,10002,1003);


-- 查询在长沙的同学
SELECT `studentno`,`studentname`FROM student WHERE address IN('长沙')


-- null not 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