账号root 密码123

1.初识MySQL

1.1数据库基础指令

mysql - uroot -p123 --链接数据库
==所有的语句都要用;结尾==

show databases; --查看所有的数据库
mysql>use school --切换数据库use + 数据库名

show tables; -- 查看数据库中的所有表
describe student; --显示数据库中所有表的信息

create database westos; --创建一个数据库

exit;--退出链接

-- 单行注释(SQL的本来的注释)
/*  (多行注释)
hello
*/

数据库xxx语言 CRUD增删改查

DDL 定义

DML 操作

DQL 查询

DCL 控制

2.操作数据库

2.1操作数据库

**[内容]**可选择写和不写

2.1.1创建数据库CREATE

CREATE DATABASE [IF NOT EXISTS] westos

2.1.2删除数据库DROP

DROP DATABASE [IF EXISTS] westos

2.1.3使用数据库USE

-- 如果表名或者字段名是一个特殊字符,就需要带``
USE school

2.1.4查看数据库SHOW

SHOW DATABASES --查看所有的数据库

2.2数据库的列类型

数值

• tinyint 十分小的数据 1个字节

• smallint 较小的数据 2个字节

• mediumint 中等大小的数据 3个字节

• int 标准的整数 4个字节

• bigint 较大的数据 8个字节

• float 浮点数 4个字节

• double 浮点数 8个字节

• decimal 字符串形式的浮点数 金融计算

字符串

• char 字符串固定大小的0~255
• varchar 可变字符串 0~65535
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 保存大文本

时间日期

• year 年份表示
• data HH:MM-DD 日期格式
• time HH:mm:ss 时间格式
• datetime YYYY-MM-DD:mm:ss 最常用的时间格式
• timestamp 时间戳 1970.1.1 到现在的毫秒数!

null

• 没有值,未知
• 注意,不要使用NULL进行运算,结果为NULL

2.3数据库的字段属性(重点)

Unsigned:

• 无符号的整数
• 声明了该列不能声明为负数

zerofill:

• 0填充的
• 不足的位数,使用0来填充 int(3), 5 ----- 005

==自增:==AUTO_INCREMENT

• 通常理解为自增,自动在上一条记录的基础上+1(默认)
• 通常用来设计唯一的主键~ index, 必须是整数类型
• 可以自定义设计主键自增的起始值和步长

==非空:==Null not null

• 假设设置为 not null ,如果不给它赋值,就会报错!
• Null 如果不填写值,默认就是null!

==默认:==DEFAULF

• 设置默认的值!
• sex,默认值为

2.4创建数据库表(重点)

注意

• 都是英文符合
• 表名和字段都用 ``
• 字符串用==’’==单引号
• 所有语句都要加==,,但是最后一个不用加逗号==

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
	'字段名' 列类型 [属性] [索引] [注释],
   	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    PEIMARY KEY(`字段名`)  --最后一个语句不加","号 
)[表类型] [字符集设置] [注释]

目标:

创建一个school数据库

创建学生表(列,字段) 使用SQL创建

学号 int 登陆密码varchear(20)

姓名 .性别 varchear(2)

出生日期(datetime),

家庭住址,email

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(2) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` VARCHAR(2) NOT NULL DEFAULT'女' COMMENT '性别',
	`pwd` VARCHAR(30) NOT NULL DEFAULT'123' COMMENT '密码',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
	`emaill` VARCHAR(10) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

常用命令

SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student --查看student数据表的定义语句
DESC student --显示表的结构

2.5 数据表的类型

--关于数据库的引擎INNODB  默认使用~MYISAM  早些年使用的

常规使用操作:

• MYISAM 节约空间,速度较快
• INNODB 安全性高,事务的处理,多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库

本质还是文件的存储!

MySQL引擎在物理文件上的区别

• INNODB 在数据库表中只要一个*.frm文件,以及上级目录xia的ibdata1文件
• MYISAM 对应文件
  • *.frm -表结构的定义文件
  • *.MYD 数据文件(data)
  • *MYI 索引文件(index)

设置数据库表的字符集编码

CHARSET=utf8

不设置的话,会是mysql默认的字符集编码~(不支持中文!)

MySQL的默认编码是Latin1 ,不支持中文

在my,ini中配置默认的编码

chara-set-server=utf8

2.6修改删除表

修改 ALTER

--修改表名:ALTER TABLE 旧表名 RENAME AS 新表名ALTER TABLE student RENAME AS teacher--增加表的字段:ALTER TABLE 表名 ADD 字段名 列属性--修改表的字段(重命名,修改约束!)--ALTER TABLE 表名 MODIFY 字段名 列属性[]ALTER TABLE teacher MODIFY age VARCHAR(3)--ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]ALTER TABLE teacher1 CHANGE age age1 INT(1) --字段重命名--删除表达字段:ALTER TABLE 表名 DROP 字段名

删除

--删除表 (如果表存在再删除)DROP TABLE IF EXISTS teacher1

3.MySQL数据管理

3.1 外键(了解即可)

方式一: 在创建表的时候, 增加约束(麻烦,比较复杂)

CREATE TABLE `grade`(	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名词',	PRIMARY KEY (`gradeid`))ENGINE = INNODB DEFAULT CHARSET=utf8-- 学生表的 gradeid 字段 要去引用年级表的 gradeid-- 定义外链key-- 给这个外键添加约束(执行引用) references 引用CREATE TABLE IF NOT EXISTS `student`(	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学号',	`name` VARCHAR(2) NOT NULL DEFAULT '匿名' COMMENT '姓名',	`sex` VARCHAR(2) NOT NULL DEFAULT'女' COMMENT '性别',	`pwd` VARCHAR(30) NOT NULL DEFAULT'123' COMMENT '密码',	`birthday` DATETIME DEFAULT NULL COMMENT '生日',	`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',	`emaill` VARCHAR(10) DEFAULT NULL COMMENT '邮箱',	`gradeid` VARCHAR(10) DEFAULT NULL COMMENT'年级',	PRIMARY KEY(`id`),	KEY `FK_gradeid` (`gradeid`),	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`))ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE `grade`(	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名词',	PRIMARY KEY (`gradeid`))ENGINE = INNODB DEFAULT CHARSET=utf8CREATE TABLE IF NOT EXISTS `student`(	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学号',	`name` VARCHAR(2) NOT NULL DEFAULT '匿名' COMMENT '姓名',	`sex` VARCHAR(2) NOT NULL DEFAULT'女' COMMENT '性别',	`pwd` VARCHAR(30) NOT NULL DEFAULT'123' COMMENT '密码',	`birthday` DATETIME DEFAULT NULL COMMENT '生日',	`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',	`emaill` VARCHAR(10) DEFAULT NULL COMMENT '邮箱',	PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8--创建表的时候没有外键关系ALTER TABLE `stdent`ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);-- ALTER TABLE 表 ADD CONSSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表(哪个字段)

所有的创建和删除操作尽量加上判断,以免报错~

注意点:

• `` 字段名,使用这个包裹!
• 注释 --/**/
• sql关键字大小写不铭感,建议小写

以上的操作都是物理外键,数据库级别的外键,我们不建议使用!(避免数据库过多造成困扰,这里了解即可~)

3.2DML语言（全部记住）

**数据库意义：：**数据存储，数据管理

DML语言：数据操作语言

• insert
• updata
• delete

3.3 添加inster

insert

--插入语句-- insert into 表名(字段名1,字段2,字段3) values('值1'),('值2'),('值3')INSERT INTO `grade` (`gradename`) VALUES ('大一')--由于主键自增我们可以省略(如果不写表的字段,他就会一一匹配)INSERT INTO `grade`  VALUES ('大二')   会报错因为 `grade` -- (id,name)  id和大二不是一一对应--一般写插入语句,我们一定要数据和字段--对应!--插入多个字段INSERT INTO `grade` (`gradename`) VALUES ('大三'),('大四')INSERT INTO `student` (`name`,`sex`,`pwd`) VALUES ('张三','男','aaaaa'),('王五','男','aaaaa')

语法: insert into 表名(字段名1,字段2,字段3) values(‘值1’),(‘值2’),(‘值3’)

注意事项:

1. 字段和字段之间使用 英文逗号 隔开
2. 字段是可以省略的,但是后面的值必须要一一对应
3. 可以同时插入多条数据,VALUES 后面的值,需要使用,隔开即可 VAlUES(),()…

3.4 修改updata

update 修改谁 (条件) set 原来的值 = 新值

--修改学员名字UPDATE `student` SET `name` = '狂神' WHERE `id` = '1'--不指定条件的情况下,会改的所有的表!UPDATE `student` SET `name` = '狂神' --修改多个属性,逗号隔开UPDATE `student` SET `name` = '狂神',`sex` = '男' WHERE `id` = '2'--BETWEEN.....AND --在什么区间UPDATE `student` SET `name` = '狂神44' WHERE `id` BETWEEN '1'AND '5'--通过多个条件定位数据UPDATE `student` SET `name` = '啊哈' WHERE `id` = '2' AND `sex` = '男'UPDATE `student` SET `name` = '啊哈' WHERE `pwd` = '123' OR `sex` = '男'

**条件:**where子句 运算符 id 等于某个值,大于某个值,在某个区间内修改…

操作符会返回布尔值

**语法:**UPDATE 表名 SET colum_name = value, colum_name = value WHERE 条件

注意事项:

• colum_name 是数据库的列,尽量带上``
• 条件,筛选的条件,如果没有指定,则会修改所有的列
• value,是一个具体的值,也可以是一个变量
• 多个设置的属性之间,使用英文逗号隔开

3.5 删除 delete

delete 命令

**语法:**delete from 表名 where 条件

--删除数据 (避免这样写,会全部删除)DELETE FROM `student`--删除指定数据DELETE FROM `student` WHERE `id` = '1'

TRUNCATE 命令

**作用:**完全清空一个数据库表,表的结构和索引约束不会变!

--清空 student类TRUNCATE `student`

delete 和truncate 区别

• 相同点:都能删除数据,都不会删除表结构
• 不同:
  • TRUNCATE 重新设置自增列 计数器会归零
  • TRUNCATE 不会影响事务

4.DQL查询数据(最重点)

4.1DQL

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

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

4.2 指定查询字段

--查询全部的学生 SELECT 字段 FROM 表SELECT*FROM student-- 查询指定字段SELECT `studentno`,`loginpwd` FROM student--别名SELECT `studentno` AS 啊哈,`loginpwd` AS 喜喜 FROM student--函数 Concat(a,b)SELECT CONCAT('姓名',studentname) AS 新名字 FROM student

**语法:**SELECT 字段 ,…FROM 表

AS 有时候,列名字不是那么的见名知意. 我们起别名

字段名 AS别名

表名 as 别名

作用：

• 可给数据列取一个新别名
• 可给表取一个新别名
• 可把经计算或总结的结果用另一个新名称来代替

-- 这里是为列取别名(当然as关键词可以省略)SELECT studentno AS 学号,studentname AS 姓名 FROM student;-- 使用as也可以为表取别名SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;-- 使用as,为查询结果取一个新名字-- CONCAT()函数拼接字符串SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

去重 distinct

作用 : 去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条

-- # 查看哪些同学参加了考试(学号) 去除重复项SELECT * FROM result; -- 查看考试成绩SELECT studentno FROM result; -- 查看哪些同学参加了考试SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)

数据库的列(表达式)

-- selcet查询中可以使用表达式SELECT @@auto_increment_increment; -- 查询自增步长SELECT VERSION(); -- 查询版本号SELECT 100*3-1 AS 计算结果; -- 表达式-- 学员考试成绩集体提分一分查看SELECT studentno,StudentResult+1 AS '提分后' FROM result;

数据库中的表达式: 文本值,列 Null 函数 计算表达式 系统变量…

4.3 where条件子句

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

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

逻辑运算符

尽量使用英文字母

SELECT Studentno,StudentResult FROM result;-- 查询考试成绩在95-100之间的SELECT Studentno,StudentResultFROM resultWHERE StudentResult>=95 AND StudentResult<=100;-- AND也可以写成 &&SELECT Studentno,StudentResultFROM resultWHERE StudentResult>=95 && StudentResult<=100;-- 模糊查询(对应的词:精确查询)SELECT Studentno,StudentResultFROM resultWHERE StudentResult BETWEEN 95 AND 100;-- 除了1000号同学,要其他同学的成绩SELECT studentno,studentresultFROM resultWHERE studentno!=1000;-- 使用NOTSELECT studentno,studentresultFROM resultWHERE NOT studentno=1000;

模糊查询:比较运算符

-- 模糊查询 between and \ like \ in \ null-- =============================================-- LIKE-- =============================================-- 查询姓刘的同学的学号及姓名-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)SELECT studentno,studentname FROM studentWHERE studentname LIKE '刘%';-- 查询姓刘的同学,后面只有一个字的SELECT studentno,studentname FROM studentWHERE studentname LIKE '刘_';-- 查询姓刘的同学,后面只有两个字的SELECT studentno,studentname FROM studentWHERE studentname LIKE '刘__';-- 查询姓名中含有 嘉 字的SELECT studentno,studentname FROM studentWHERE studentname LIKE '%嘉%';-- 查询姓名中含有特殊字符的需要使用转义符号 '\'-- 自定义转义符关键字: ESCAPE ':'-- =============================================-- IN-- =============================================-- 查询学号为1000,1001,1002的学生姓名SELECT studentno,studentname FROM studentWHERE studentno IN (1000,1001,1002);-- 查询地址在北京,南京,河南洛阳的学生SELECT studentno,studentname,address FROM studentWHERE address IN ('北京','南京','河南洛阳');-- =============================================-- NULL 空-- =============================================-- 查询出生日期没有填写的同学-- 不能直接写=NULL , 这是代表错误的 , 用 is nullSELECT studentname FROM studentWHERE BornDate IS NULL;-- 查询出生日期填写的同学SELECT studentname FROM studentWHERE BornDate IS NOT NULL;-- 查询没有写家庭住址的同学(空字符串不等于null)SELECT studentname FROM studentWHERE Address='' OR Address IS NULL;

4.4 联表查询

/*连接查询   如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询内连接 inner join   查询两个表中的结果集中的交集外连接 outer join   左外连接 left join       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)   右外连接 right join       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)       等值连接和非等值连接*/-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)SELECT * FROM student;SELECT * FROM result;/*思路:(1):分析需求,确定查询的列来源于两个类,student result,连接查询(2):确定使用哪种连接查询?(内连接)*/SELECT s.studentno,studentname,subjectno,StudentResultFROM student sINNER JOIN result rON r.studentno = s.studentno-- 右连接(也可实现)SELECT s.studentno,studentname,subjectno,StudentResultFROM student sRIGHT JOIN result rON r.studentno = s.studentno-- 等值连接SELECT s.studentno,studentname,subjectno,StudentResultFROM student s , result rWHERE r.studentno = s.studentno-- 左连接 (查询了所有同学,不考试的也会查出来)SELECT s.studentno,studentname,subjectno,StudentResultFROM student sLEFT JOIN result rON r.studentno = s.studentno-- 查一下缺考的同学(左连接应用场景)SELECT s.studentno,studentname,subjectno,StudentResultFROM student sLEFT JOIN result rON r.studentno = s.studentnoWHERE StudentResult IS NULL-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)SELECT s.studentno,studentname,subjectname,StudentResultFROM student sINNER JOIN result rON r.studentno = s.studentnoINNER JOIN `subject` subON sub.subjectno = r.subjectno

自连接(了解)

自己的表和自己的表连接,核心一张表拆为两张一样的表即可

父类

子类

操作:查询父类对应的子类关系

/*自连接   数据表与自身进行连接需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中    查询父栏目名称和其他子栏目名称*/-- 创建一个表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','办公信息');-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'FROM category AS a,category AS bWHERE a.`categoryid`=b.`pid`-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)SELECT s.studentno,studentname,subjectname,StudentResultFROM student sINNER JOIN result rON r.studentno = s.studentnoINNER JOIN `subject` subON sub.subjectno = r.subjectno-- 查询学员及所属的年级(学号,学生姓名,年级名)SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称FROM student sINNER JOIN grade gON s.`GradeId` = g.`GradeID`-- 查询科目及所属的年级(科目名称,年级名称)SELECT subjectname AS 科目名称,gradename AS 年级名称FROM SUBJECT subINNER JOIN grade gON sub.gradeid = g.gradeid-- 查询 数据库结构-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'

4.5排序和分页

排序order by

/*============== 排序 ================语法 : ORDER BY   ORDER BY 语句用于根据指定的列对结果集进行排序。   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

分页limit

/*============== 分页 ================语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset好处 : (用户体验,网络传输,查询压力)推导:   第一页 : limit 0,5   第二页 : limit 5,5   第三页 : limit 10,5   ......   第N页 : limit (pageNo-1)*pageSzie,pageSzie   [pageNo:页码,pageSize:单页面显示条数]   */-- 每页显示5条数据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 , studentnoLIMIT 0,5-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)SELECT s.studentno,studentname,subjectname,StudentResultFROM student sINNER JOIN result rON r.studentno = s.studentnoINNER JOIN `subject` subON r.subjectno = sub.subjectnoWHERE subjectname='JAVA第一学年'ORDER BY StudentResult DESCLIMIT 0,10

4.6子查询

/*============== 子查询 ================什么是子查询?   在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句   嵌套查询可由多个子查询组成,求解的方式是由里及外;   子查询返回的结果一般都是集合,故而建议使用IN关键字;*/-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列-- 方法一:使用连接查询SELECT studentno,r.subjectno,StudentResultFROM result rINNER JOIN `subject` subON r.`SubjectNo`=sub.`SubjectNo`WHERE subjectname = '数据库结构-1'ORDER BY studentresult DESC;-- 方法二:使用子查询(执行顺序:由里及外)SELECT studentno,subjectno,StudentResultFROM resultWHERE subjectno=(   SELECT subjectno FROM `subject`   WHERE subjectname = '数据库结构-1')ORDER BY studentresult DESC;-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名-- 方法一:使用连接查询SELECT s.studentno,studentnameFROM student sINNER JOIN result rON s.`StudentNo` = r.`StudentNo`INNER JOIN `subject` subON sub.`SubjectNo` = r.`SubjectNo`WHERE subjectname = '高等数学-2' AND StudentResult>=80-- 方法二:使用连接查询+子查询-- 分数不小于80分的学生的学号和姓名SELECT r.studentno,studentname FROM student sINNER JOIN result r ON s.`StudentNo`=r.`StudentNo`WHERE StudentResult>=80-- 在上面SQL基础上,添加需求:课程为 高等数学-2SELECT r.studentno,studentname FROM student sINNER JOIN result r ON s.`StudentNo`=r.`StudentNo`WHERE StudentResult>=80 AND subjectno=(   SELECT subjectno FROM `subject`   WHERE subjectname = '高等数学-2')-- 方法三:使用子查询-- 分步写简单sql语句,然后将其嵌套起来SELECT studentno,studentname FROM student WHERE studentno IN(   SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(       SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'  ))/*练习题目:   查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)   使用子查询,查询郭靖同学所在的年级名称*/

select小结

SELECT语法

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

查询注意事项

有时候查询不见会跨表查询，还可能跨数据库查询，有可能是oracle,mongDB,Redis等等，这些层面是用Java写的
比如一个淘宝页面,淘宝号称千人千面，每个人进去的页面展现都是不同的，但是都有商品视频、图片、价格、评论这些，这些东西都是从不同的数据库里面查出来的。价格，标题，数量这些可以放在mysql，图片是非关系的，可能放在mongDB,视频可能放在一个专门的文件存储系统（hdfs或者fasthdfs,包括阿里云的os）,评论是一些热点数据，可能是放在redis里面，一个淘宝页面能显示出来，经过了很多数据库的查询

5.MySQL函数

5.1 常用函数

数据函数

 SELECT ABS(-8);  /*绝对值*/ SELECT CEILING(9.4); /*向上取整*/ SELECT FLOOR(9.4);   /*向下取整*/ SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/ SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

字符串函数

 SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/ SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/ SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度*/ SELECT LOWER('KuangShen'); /*小写*/ SELECT UPPER('KuangShen'); /*大写*/ SELECT LEFT('hello,world',5);   /*从左边截取*/ SELECT RIGHT('hello,world',5);  /*从右边截取*/ SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/ SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/ SELECT REVERSE('狂神说坚持就能成功'); /*反转  -- 查询姓周的同学,改成邹 SELECT REPLACE(studentname,'周','邹') AS 新名字 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 VERSION();  /*版本*/ SELECT USER();     /*用户*/  

5.2聚合函数

-- 聚合函数 /*COUNT:非空的*/ SELECT COUNT(studentname) FROM student; SELECT COUNT(*) FROM student; SELECT COUNT(1) FROM student;  /*推荐*/  SELECT SUM(StudentResult) AS 总和 FROM result; SELECT AVG(StudentResult) AS 平均分 FROM result; SELECT MAX(StudentResult) AS 最高分 FROM result; SELECT MIN(StudentResult) AS 最低分 FROM result;

题目：

 -- 查询不同课程的平均分,最高分,最低分 -- 前提:根据不同的课程进行分组  SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分 FROM result AS r INNER JOIN `subject` AS s ON r.subjectno = s.subjectno GROUP BY r.subjectno HAVING 平均分>80;  /* where写在group by前面. 要是放在分组后面的筛选 要使用HAVING.. 因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的 */

5.3 数据库级别的MD5加密

一、MD5简介

MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。

二、实现数据加密

新建一个表 testmd5

 CREATE TABLE `testmd5` (  `id` INT(4) NOT NULL,  `name` VARCHAR(20) NOT NULL,  `pwd` VARCHAR(50) NOT NULL,  PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8

插入一些数据

 INSERT INTO testmd5 VALUES(1,'kuangshen','123456'),(2,'qinjiang','456789')

如果我们要对pwd这一列数据进行加密，语法是：

 update testmd5 set pwd = md5(pwd);

如果单独对某个用户(如kuangshen)的密码加密：

 INSERT INTO testmd5 VALUES(3,'kuangshen2','123456') update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';

插入新的数据自动加密

 INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));

查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）

 SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');

6.事务

什么是事务

• 事务就是将一组SQL语句放在同一批次内去执行
• 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
• MySQL事务处理只支持InnoDB和BDB数据表类型

事务的ACID原则 百度 ACID

原子性(Atomic)

• 要么都成功,要么都失败

一致性(Consist)

• 事务前后的数据完整性要保持一致

隔离性(Isolated)

• 多个用户并发访问数据库是,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干涉

持久性(Durable)

• 事务一旦提交则不可逆,被持久化到数据库中

隔离所导致的一些问题

脏读:

指一个事务读取了另外一个事务未提交的数据.

不可重复读:

在一个事务内读取表中的某一行数据,都次读取结果不同(不一定是错误,只是某些场合不对)

虚读(幻读):

在一个事务内读取到了别人的事务插入的数据,导致前后读取不一致

基本语法

-- 使用set语句来改变自动提交模式SET autocommit = 0;   /*关闭*/SET autocommit = 1;   /*开启*/-- 注意:--- 1.MySQL中默认是自动提交--- 2.使用事务时应先关闭自动提交-- 开始一个事务,标记事务的起始点START TRANSACTION  -- 提交一个事务给数据库COMMIT-- 将事务回滚,数据回到本次事务的初始状态ROLLBACK-- 还原MySQL数据库的自动提交SET autocommit =1;-- 保存点SAVEPOINT 保存点名称 -- 设置一个事务保存点ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点RELEASE SAVEPOINT 保存点名称 -- 删除保存点

测试

/*课堂测试题目A在线买一款价格为500元商品,网上银行转账.A的银行卡余额为2000,然后给商家B支付500.商家B一开始的银行卡余额为10000创建数据库shop和创建表account并插入2条数据*/CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;USE `shop`;CREATE TABLE `account` (`id` INT(11) NOT NULL AUTO_INCREMENT,`name` VARCHAR(32) NOT NULL,`cash` DECIMAL(9,2) NOT NULL,PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO account (`name`,`cash`)VALUES('A',2000.00),('B',10000.00)-- 转账实现SET autocommit = 0; -- 关闭自动提交START TRANSACTION;  -- 开始一个事务,标记事务的起始点UPDATE account SET cash=cash-500 WHERE `name`='A';UPDATE account SET cash=cash+500 WHERE `name`='B';COMMIT; -- 提交事务# rollback;SET autocommit = 1; -- 恢复自动提交

7.索引

7.1 索引的分类

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引：primary key
唯一的标识，主键不可重复，只能有一个列作为主键

唯一索引 ：unique
避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

常规索引： （key/index）
默认的，可以用index关键字或者key关键字来标识

全文索引： FULLTEXT
在特定的数据库引擎才有，快速定位数据

7.2 sql编程创建100万条数据测试

CREATE TABLE `app_user` (`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',`password` VARCHAR(100) NOT NULL COMMENT '密码',`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',`create_time` DATETIME ,`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'-- 插入100万数据.DELIMITER $$-- 写函数之前必须要写，标志CREATE FUNCTION mock_data ()RETURNS INTBEGIN	DECLARE num INT DEFAULT 1000;	DECLARE i INT DEFAULT 0;	WHILE i<num DO		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)		VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));		SET i=i+1;	END WHILE;	RETURN i;END;SELECT mock_data() -- 执行此函数 生成一百万条数据

创建索引查询

-- 当数据过多，我们查找一条数据就会很慢，所以我们需要创建索引-- 创建索引格式 id_表名_字段名   create index 索引名 on 表（字段）create index id_app_user_name on `app_user`(`name`)  -- 会给我们的1000条数据都建立一个索引select * from app_user where `name`='用户99'  -- 我们创建了索引之后，查询会非常快 0.001 sec

结论：索引在小数据量的时候用处不大，在大数据量的时候，区别十分明显，可以节约时间

7.3 索引原则

• 索引不是越多越好（数据量大，比如500万条，再加索引）
• 不要对经常变动的数据加索引（数据经常变动的话，每一次都要重新来）
• 小数据量的表不需要加索引
• 索引应该加在常用来查询的字段上

8.权限管理和备份

8.1 用户管理

sql命令操作

操作删除用户就是操作 用户表 mysql.user
本质：对mysql.user这张表进行增删改查

-- 创建用户  CREATE USER 用户名 IDENTIFIED BY 密码CREATE USER csdn IDENTIFIED BY '14158'-- 修改密码（修改当前用户密码）SET PASSWORD =PASSWORD('123456')  -- 修改密码（修改指定用户密码）SET PASSWORD FOR csdn =PASSWORD('123456')  -- 重命名用户RENAME USER csdn TO csdn2-- 用户授权 All PRIVILEGES 全部的权限， 库.表GRANT ALL PRIVILEGES ON *.* to csdn2 -- 表示对所有的库和表都有操作权限-- 查看权限SHOW GRANTS FOR csdn2-- GRANT ALL PRIVILEGES ON *.* TO 'csdn2'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'-- 查看root权限SHOW GRANTS FOR root@localhost -- 查看主机权限需要加上@localhost-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION-- 撤销权限 revoke 哪些权限 在哪个库撤销，给谁撤销REVOKE  ALL PRIVILEGES ON *.* FROM  csdn2-- 删除用户

8.2 MySQL备份

为什么要备份：

• 保证重要的数据不丢失
• 数据转移

MySQL备份的方式

• 直接拷贝物理文件
• 使用命令行导出 mysqldump 命令行使用
  首先：win+R 打开命令行 输入如下指令

-- 导出1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)可以-w携带备份条件-- 导入1. 在登录mysql的情况下：-- source D:/a.sql　　source 备份文件2. 在不登录的情况下　　mysql -u用户名 -p密码 库名 < 备份文件

9.规范数据库设计

9.1为什么需要数据库设计

当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计 :

• 数据冗余,存储空间浪费
• 数据更新和插入的异常
• 程序性能差

良好的数据库设计 :

• 节省数据的存储空间
• 能够保证数据的完整性
• 方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

• 需求分析阶段: 分析客户的业务和数据处理需求
• 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

• 收集信息 分析需求

• • 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.

• 标识实体[把需求落地到每个字段]

• 标识实体之间的关系

• • 标识数据库要管理的关键对象或实体,实体一般是名词

• 标识每个实体需要存储的详细信息[Attribute]

• 标识实体之间的关系[Relationship]

9.2三大范式

问题 : 为什么需要数据规范化?

不合规范的表设计会导致的问题：

• 信息重复

• 更新异常

• 插入异常

• • 无法正确表示信息

• 删除异常

• • 丢失有效信息

三大范式

第一范式 (1st NF)

原子性: 保证不可再分

第二范式(2nd NF)

前提: 满足第一范式

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

前提: 满足第一范式 和 第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范化和性能的关系

为满足某种商业目标 , 数据库性能比规范化数据库更重要

在数据规范化的同时 , 要综合考虑数据库的性能

通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间

通过在给定的表中插入计算列,以方便查询

10.JDBC(重点)

10.1第一个JDBC 程序

1. 创建一个普通项目
2. 导入数据库驱动

import java.sql.*;public class JdbcFirstDemo {    public static void main(String[] args) throws ClassNotFoundException, SQLException {        //1.加载驱动        Class.forName("com.mysql.jdbc.Driver");        //2.用户信息和url        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&userSSl=true";        String username = "root";        String password = "123";        //3.连接成功,数据库对象        // Connection代表数据库        Connection connection = DriverManager.getConnection(url, username, password);        //4.执行SQL的对象        // Statement 执行sql的对象        Statement statement = connection.createStatement();        //5.执行SQL的对象去执行SQL 可能存在的结果        String sql = "SELECT*FROM users";        //返回的结果集,结果集中封装了我们全部的查询出来的结果        ResultSet resultSet = statement.executeQuery(sql);        while (resultSet.next()){            System.out.println("id"+resultSet.getObject("id"));            System.out.println("name"+resultSet.getObject("NAME"));            System.out.println("pwd"+resultSet.getObject("PASSWORD"));            System.out.println("email"+resultSet.getObject("email"));            System.out.println("birth"+resultSet.getObject("birthday"));        }        //6.释放连接        resultSet.close();        statement.close();        connection.close();    }}

步骤总结:

1. 加载驱动 Class.forName
2. 连接数据库 DriverManager
3. 获得执行sql的对象 Statement
4. 获得返回的结果集
5. 释放连接

10.2 JDBC中对象解释

DriverManager 驱动程序管理器

// DriverManager.registerDriver(new com.mysql.jdbc.Driver()); // Class.forName(数据库驱动实现类)Class.forName("com.mysql.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, userName, pwd);// connection 代表数据库// 数据库设置自动提交// 事务提交// 事务回滚connection.setAutoCommit();connection.commit();connection.rollback();

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useSSL=false&useUnicode=true&characterEncoding=utf8";
// 如果数据库出现乱码，可以指定参数: characterEncoding=utf8，表示让数据库以 UTF-8 编码来处理数据
// mysql--3306
// 协议:子协议://主机地址:端口号/数据库名?参数1&参数2...

// oracle--1521
// jdbc:oracle:thin:@localhost:1521:sid

ResultSet 查询返回的结果集

• 获得指定的数据类型

//不知道类型就用Object
resultSet.getObject();
//知道类型可以直接使用对应类型获取
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDouble();
1234567

• 遍历 指针

resultSet.next();//移动到下一行数据
resultSet.beforeFirst();//移动到最前
resultSet.afterLast();//移动到最后
resultSet.previous();//移动到前一行
resultSet.absolute(i);//移动到第i行
12345

释放资源

resultSet.close();
statement.close();
connection.close(); // 耗资源，用完关掉