MySQL详解

MySQL

1.初识mysql

  • JavaEE:企业级Java开发 Web
  • 前端(页面:展示:数据)
  • 后台 (连接点:连接数据库JDBC,连接前端(控制视图跳转,给前端传递数据))
  • 数据库(存数据,Txt,Excel,Word)
    • 只会写代码,学好数据库,基本混饭吃!
    • 操作系统,数据结构与算法!当一个不错的程序猿!
    • 离散数学,数字电路,体系结构,编译原理。+实战经验,优秀程序猿

1.1 为什么学数据库

  • 岗位需求
  • 现在的世界,大数据时代,得数据者得天下
  • 被迫需求:存数据
  • 数据库是所有软件体系中最核心的存在 DBA

1.2 什么是数据库

  • 数据库:(DB,DataBase)
    • 概念:数据仓库,软件,安装在操作系统之(windows,Linux。mac)上的!SQL,可以存储大量的数据,500万!
    • 作用:存储数据,管理数据 Excel

1.3 数据库分类

  • 关系型数据库:(SQL)
    • MySQL, Oracle, sql Server, DB2, SQLite
    • 通过表和表之间,行和列之间的关系进行数据的存储,学员信息表,考勤表,······
  • 非关系型数据库:(NoSQL) Not Only
    • Redis, MongDB
    • 非关系型数据库,对象存储,通过对象自身的属性来决定。
  • DBMS(数据库管理系统)
    • 数据库的管理软件,科学有效的管理我们的数据,维护和获取数据;
    • MySQL ,数据管理系统!

1.4 MySQL简介

  • MySQL是一个关系型数据库管理系统。
  • 发展史:
    • 前世: 瑞典MySQL AB 公司
    • 今身: 属于 Oracle 旗下产品
  • MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
    • 开源的数据库软件

    • 体积小,速度快,总体拥有成本低,招人成本比较低,所有人必须会~。

    • 中小型网站,或者大型网站,集群

    • 官网: https://www.mysql.com/

1.5 MySQL基本的命令行操作

mysql -uroot -p123456 -- 连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and host='localhost'; -- 修改用户密码

flush privileges; -- 刷新权限
----------------------------
-- 所有的语句都使用;结尾

show databases; -- 查看所有的数据库

mysql> use school; -- 切换数据库  use 数据库名
Database changed

mysql> show tables; -- 显示数据库中所有表的信息

mysql> describe student; -- 显示表的详细信息

create database zyy;  -- 创建一个数据库(这里过于简洁,后面详细介绍)

exit -- 退出连接

-- 单行注释
/**
多行注释
*/



2.操作数据库

2.1 MySQL结构化查询语句分类

名称 解释 命令
DDL (数据定义语言) 定义和管理数据对象,如数据库,数据表等 CREATE、DROP、ALTER
DML (数据操作语言) 用于操作数据库对象中所包含的数据 INSERT、UPDATE、DELETE
DQL (数据查询语言) 用于查询数据库数据 SELECT
DCL (数据控制语言) 用于管理数据库的语言,包括管理权限及数据更改 GRANT、commit、 rollback

2.2 操作数据库语句

  • 操作数据库 》 操作数据库中表 》操作数据库中表的数据
  • mysql关键字不区分大小写
  • 1.创建数据库
CREATE DATABASE [IF NOT EXISTS] student;-- create database [if not exists] 数据库名;
  • 2.删除数据库
DROP DATABASE [IF EXISTS] westos;-- drop database [if exists] 数据库名;
  • 3.使用数据库
-- tab 键上面,如果你的表名或者字段名是一个特殊字符,就需要带``
USE `school`--use 数据库名;
  • 4.查看数据库
SHOW DATABASES; -- 查看所有的数据库

2.3 列的数据类型讲解

数值
  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的数据 3个字节
  • int 标准的整数 4个字节

  • bigint 较大的数据 8个字节

  • float 浮点数 4个字节

  • double 浮点数 8个字节

  • decimal 字符串形式的浮点数 (金融计算的时候,一般是使用decimal)

字符串
  • char 字符串固定大小的 0~255
  • varchar 可变字符串 0~65535 (常量的变量 String)
  • tinytext 微型文本 2^8 -1
  • text 文件串 2^16 -1 (保存大文本)
时间日期 java.util.Date
  • date YYYY-MM-DD 日期格式

  • time HH:mm:ss 时间格式

  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式

  • timestamp 时间戳,1970.1.1到现在的毫秒数!较为常用!

  • year 年份表示

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

2.4 数据库的字段属性

  • Unsigned:

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

    • 0填充的
    • 不足的位数,使用0来填充 int(3), 5 — 005
  • 自增:

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

    • not null,如果不给他赋值,就会报错
    • null,如果不给他赋值,默认就是null
  • 默认:

    • 设置默认的值
    • 如果不赋值,就会存默认值
  • 拓展:

/*
每个表,都必须存在以下五个字段  未来做项目用的,表示一个记录存在的意义

id          主键
version     乐观锁
is_delete   伪删除
gmt_create  创建时间
gmt_update  修改时间

*/

2.5 创建数据库表

-- 目标:创建一个school数据库
-- 创建student学生表,使用sql创建
-- 学号 姓名 性别 出生日期 家庭地址  email

-- 注意点:使用英文()  表的名称和字段尽量使用``括起来
-- AUTO_INCREMENT 自增
-- COMMENT 属性注释
-- DEFAULT 默认值
-- 字符串使用单引号括起来
-- 所有的语句后面加上英文逗号,最后一个不加
-- PRIMARY KEY主键,一个表一般只有一个唯一的主键
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME NOT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 查看建立数据库的语句
SHOW CREATE DATABASE SCHOOL
DESC STUDENT --显示表的结构
  • 格式
CREATE TABLE [IF NOT EXISTS] `表名` (
    `字段名` 列类型[属性]  [索引]  [注释],
    `字段名` 列类型[属性]  [索引]  [注释],
    `字段名` 列类型[属性]  [索引]  [注释],
    ...
)[表类型][字符集设置][注释]

  • 常用命令:
SHOW CREATE DATABASE `school` ; -- 查看创建数据库的语句
SHOW CREATE TABLE `student`; -- 查看student数据表的定义语句
DESC `student`; -- 查看表的结构

2.6 MyIASM和InnoDB区别


                      MYISAM             INNODB     
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大,约为MYISAM的2倍
  • 常规使用操作:

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

    • 所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
    • 本质还是文件的存储!
  • mysql引擎在物理文件上的区别

    • INNODB 在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
    • MYISAM 对应的文件
      • *.frm 表结构的定义文件
      • *.MYD 数据文件(data)
      • *.MYI 索引文件 (index)
  • 设置数据库表的字符集编码

    • CHARSET=utf8
    • 不设置的话,会是mysql默认的字符集编码(不支持中文),mysql的默认编码是Latin1,不支持中文
    • 在my.ini中配置默认的编码
       character-set-server=utf8

2.7 修改和删除数据表字段

  • 修改
-- 修改表名.  ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段.  ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段 (重命名,修改约束!)
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 字段名 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age  age1 INT(1)-- 字段重命名

-- 删除表的字段:ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1


  • 删除
-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS teacher1
  • 所有的创建和删除操作尽量加上判断,以免报错.

  • 注意点:

    • `` 字段名,使用这个包裹!
    • 注释:单行注释 # 注释内容  多行注释 /* 注释内容 */  单行注释 -- 注释内容
    • sql 关键字大小写不敏感,建议写小写。
    • 所有的符号全部用英文

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 `student3`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
		`name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT'姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT'密码',
		`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
		`gradeid` INT(10) NOT NULL COMMENT '学生年级',
		`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
		PRIMARY KEY(`id`),
		KEY `FK_gardeid` (`gradeid`),
    CONSTRAINT `FK_gardeid` 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=utf8

CREATE TABLE IF NOT EXISTS `student3`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
		`name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT'姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT'密码',
		`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
		`gradeid` INT(10) NOT NULL COMMENT '学生年级',
		`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 创建表的时候没有外键关系。
ALTER TABLE `student3`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE `表` ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列)引用到哪个表的哪个字段.

  • 以上的操作都是物理外键,数据库级别外键,我们不建议使用!(避免数据库过多造成困扰)
  • 最佳实践
    • 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)。
    • 我们想使用多张表的数据,想使用外键(程序去实现)。

3.2 DML语言(全部记住)

  • 数据库意义:数据存储,数据管理.
  • DML语言:数据操作语言.
    • Insert (添加数据语句)
    • update (更新数据语句)
    • delete (删除数据语句)

3.3 添加

  • Insert
-- 插入语句(添加)
-- insert into 表名([字段名1,字段2,字段3]) values ('值1','值2','值3',······)
INSERT INTO `grade`(`gradename`) VALUES('大四')

-- 由于主键自增我们可以省略(如果不写表的字段,他就会一一匹配)

-- 一般写插入语句,我们一定要数据和字段一一对应!

-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一')

INSERT INTO `student3`(`name`) VALUES ('张三')

INSERT INTO `student3`(`name`,`pwd`) VALUES ('张三','aaaaaa')

INSERT INTO `student3`(`name`,`pwd`) 
VALUES('李四','lalalala'),('王五','wuwuwuwu')

INSERT INTO `student3` 
VALUES (5,'王二麻子','7777777','1999-12-23',1,'新疆','email')

  • 语法: insert into 表名([字段名1,字段2,字段3]) values ('值1','值2','值3',······)

  • 注意事项:

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

3.4 修改

  • update  修改谁  (条件) set 原来的值=新值
-- 语法:
-- UPDATE 表名 SET colnum_name = value where [条件]

-- 修改学员名字,带了条件。
UPDATE `student3` SET `name`='狂神' WHERE id=1;
UPDATE `student3` SET `name`='狂神' WHERE id>=1;
UPDATE `student3` SET `name`='狂神11' WHERE id<>1;
UPDATE `student3` SET `name`='狂神22' WHERE id!=1;
UPDATE `student3` SET `name`='狂神33' WHERE id BETWEEN 2 AND 5;

-- 不指定条件的情况下,会改动所有表!
UPDATE `student3` SET `name`='长江7号'

-- 修改多个属性,逗号隔开。
UPDATE `student3` SET `name`='狂神',`email`='21312312qq.com' WHERE id = 1;

-- 通过多个条件定位数据,无上限!
UPDATE `student3` SET `name`='狂神' WHERE `name`='长江7号' AND pwd='123456'
UPDATE `student3` SET `birthday`=CURRENT_TIME WHERE `name`='长江7号' AND pwd='123456'

  • 条件: where 子句 运算符 id 等于某个值,大于某个值,在某个区间内修改…
  • 语法: UPDATE 表名 SET colnum_name = value,[colnum_name = value,......] where [条件]

  • 操作符会返回布尔值
      操作符          含义           范围            结果     
= 等于 5 = 6 false
<> 或 != 不等于 5 <> 6 true
> 大于 5 > 6 false
< 小于 5 < 6 true
>= 大于等于 6,7 >= 6 true
<= 小于等于 5,6 <= 6 true
BETWEEN…AND… 在某个范围内 [2 , 5] 3 true
AND 我和你 && 5 > 1 and 1 > 2 true
OR 我或你 5 > 1 or 1 > 2 true
  • 注意事项:

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

3.5 删除

  • delete 命令
  • 语法: delete from 表名 [where 条件]
-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student3`

-- 删除指定数据
DELETE FROM `student3` WHERE id=1
  • TRUNCATE 命令
    • 作用:完全清空一个数据库表,表的结构和索引约束不会变.
-- 清空student3表
TRUNCATE TABLE `student3`

  • delete 和 TRUNCATE 区别
    • 相同点: 都能删除数据,都不会删除表结构.
    • 不同:
      • TRUNCATE 重新设置自增列 计数器会归零.
      • 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删除的问题 重启数据库,现象.
    • innoDB 自增列会从1开始(存在内存当中,断电即失)
    • MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)


4.DQL查询数据

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

SELECT 语法
  • 注意 : [ ] 括号代表可选的 , { }括号代表必选得
SELECT [ALL | DISTINCT] -- 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}];
   -- 指定查询的记录从哪条至哪条


  • 测试数据SQL
-- 创建一个school数据库
create  database  `school`;
-- 创建一张年级表
use `school`;
drop table if exists `grade`;
 create  table  `grade`(
     `GradeID` int(4) 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,'预科班');

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

-- 创建 student学生表
drop table if exists `student`;
create  table  `student`(
   `StudentNo` int(4) not null  comment '学号',
    `LoginPwd` varchar(20) not null,
    `StudentName` varchar(20) DEFAULT  NULL comment '学生姓名',
    `Sex` TINYINT(1) DEFAULT NULL  comment  '性别,取值0或1',
    `GradeID` int(4) 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=INNODB DEFAULT CHARSET=utf8;

-- 添加数据插入学生数据 
insert  into  `student` (`StudentNo`,`LoginPwd`,`StudentName`,`Sex`, `GradeID`,`Phone` ,`Address`,`BornDate`, `Email`, `IdentityCard`) values(1001,'123456','李四',1,3,'18786506942','山海','1999-12-11 00:00:00','test@qq.com','522428199912110812'),(1002,'123456','李林',0,3,'18786506942','西安','1999-02-11 00:00:00','test@qq.com','522428199912110832'),(1003,'123456','韩立',1,3,'1878655542','北京','1999-02-11 00:00:00','test@qq.com','412428199912110812');

-- subject  表
-- 创建科目表
drop table if exists `subject`;
create  table `subject`(
  `SubjectNO`  int(4) 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 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);


4.1 指定查询字段

  • 查询指定字段
-- 查询全部的学生   SELECT 字段 FROM 表名;
  SELECT * FROM student;
  
-- 查询指定字段
  SELECT student_name, student_no FROM student;
  • AS 别名
-- 别名,给结果起一个名字 AS  可以给字段起别名,也可以给表起别名
  
  SELECT student_name AS '学号', student_no AS '姓名' FROM student;
  
-- 函数 concat(a,b)
  SELECT CONCAT('姓名:', student_no)  AS '新姓名' FROM student;

  • 语法 : SELECT 字段,... FROM 表名
  • 有的时候,列名字不是那么的见名知义。所以我们会对其起别名 AS 字段名 as 新名字

  • 去重 distinct: 作用:去除Select 查询出来的结果中重复的数据 只显示一条
-- 查询一下有哪些同学参加了考试,成绩
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 `name`,`gradeid` +1 AS '升学后' FROM student

  • 数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量
  • 语法: select 表达式 from 表名

4.2 where 条件子句

  • 作用:检索数据中符合条件的值
  • 搜索的条件由一个或者多个表达式组成,结果布尔值
逻辑运算符
       运算符               语法             描述     
and    && a and b    a&&b 逻辑与两个都为真,结果为真
or  \(\mid\)\(\mid\) a or b  a\(\mid\)\(\mid\)b 逻辑或,其中一个为真则结果为真
not  ! not a   !a 逻辑非,真为假,假为真
  • 尽量使用英文字母
-- ====================  where  ====================

-- 查询考试成绩在 95 ~ 100分之间

SELECT student_no,student_result FROM result;

-- and
SELECT student_no,student_result FROM result
WHERE student_result>95 AND student_result<=100;
-- && 
SELECT student_no,student_result FROM result
WHERE student_result>95 && student_result<=100;


-- 模糊查询(区间)
SELECT student_no,student_result FROM result
WHERE student_result BETWEEN 95 AND 100;

-- 除了1000号学生之外的学生的成绩
-- !=
SELECT student_no,student_result FROM result
WHERE student_no != 1000;
-- not
SELECT student_no,student_result FROM result
WHERE NOT student_no = 1000;


  • 模糊查询操作符详解
模糊查询:比较运算符
       运算符               语法             描述     
IS NULL a is null 如果操作符为null,结果为真
IS NOT NULL a is not null 如果操作符不为null,结果为真
BWTWEEN…AND… a between b and c 若a在b和c之间,则结果为真
LIKE a like b SQL匹配,如果a匹配b,则结果为真
IN a in (a1,a2,a3,…) 假设a在a1或者a2或者a3,…其中的某一个,则结果为真
-- ====================  模糊查询  ======================
  
 -- 查询姓刘的同学

-- like 结合 
--   %(代表0到任意个字符)
--   _(代表1)

-- 查询姓刘的同学
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘%';

-- 查询姓刘的同学,名字后面只有一个字的
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘_';

-- 查询姓刘的同学,名字后面有两个字的
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘__';

-- 查询名字中间有嘉字的同学
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '%%嘉%';

===================IN(具体的一个或者多个值)===========================
-- in (具体的一个或者多个值)
-- 查询学号1001,1002,1003号学号
SELECT `student_no`,`student_name` FROM `student`
WHERE student_no IN ('1001','1002','1003');

SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1003

-- 查询在北京的学生
SELECT `student_no`,`student_name` FROM `student`
WHERE address IN ('北京');

===================NULL NOT NULL===================================
-- null
-- 查询地址为空的学生
SELECT `student_no`,`student_name` FROM `student`
WHERE address = '' OR address IS NULL;

-- not null
-- 查询有出生日期的同学 不为空
SELECT `student_no`,`student_name` FROM `student`
WHERE born_date IS NOT NULL;

-- 查询没有出生日期的同学 为空
SELECT `student_no`,`student_name` FROM `student`
WHERE born_date IS NULL;

4.3 联表查询

  • JOIN对比与 七种jion理论

ffd


fff

  • 联表查询 join
-- =====================联表查询 join ==============================

-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;


/*
1. 分析需求,分析查询的字段来自哪些表
2.确定使用哪种连接查询?7种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件: 学生表中 studentNo = 成绩表中 studentNo 

*/
-- join on 连接查询
-- where 等值查询


-- inner join
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result` 
FROM student AS st
INNER JOIN result AS re ON 
st.`student_no`=re.`student_no`;

-- right join
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result` 
FROM student st
RIGHT JOIN result re ON 
st.`student_no`=re.`student_no`;

-- left join
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result` 
FROM student st
LEFT JOIN result re ON 
st.`student_no`=re.`student_no`;

-- 查询缺考的同学
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result` 
FROM student st
LEFT JOIN result re ON 
st.`student_no`=re.`student_no`
WHERE re.`student_result` IS NULL;

-- 查询了参加考试的同学信息(学号,学生姓名,科目名称,分数)
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM `student` stu
RIGHT JOIN `result` res 
ON res.`student_no`=stu.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`;

-- 查询学员所属的年级(学号,学生的姓名,年级名称)
SELECT `student_no`,`student_name`,`grade_name`
FROM student stu
INNER JOIN `grade` gra
ON stu.`grade_id`=gra.`grade_id`;

-- 查询了参加数据结构-1考试的同学信息(学号,学生姓名,科目名称,分数)
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `result` res
ON stu.`student_no` = res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='数据结构-1';


-- 我要查询哪些数据 select ...
-- 从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加

--FROM a LEFT JOIN b   左为准
--FROM a RIGHT JOIN b	右为准

       操作               描述     
inner join 如果表中至少有一个匹配,就返回行
left join 会从左边中返回所有的值,即使右表中没有匹配
right join 会从右边中返回所有的值,即使左表中没有匹配

  • 自连接及联表查询
  • 自连接: 自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
父类
       pid               category_id             category_name     
1 2 信息技术
1 3 软件开发
1 5 美术设计

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

  • 操作:查询父类对应的子类关系
       父类               子类     
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 ps技术
  • 创建自连接需要的表
-- 创建表
-- unsigned 无符号
-- auto_increment=9 自增的起始值
DROP TABLE IF EXISTS `category` ;
CREATE TABLE `category` (
  `category_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `category_name` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`category_id`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- 插入值
INSERT INTO `category`(`category_id`,`pid`,`category_name`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

SELECT * FROM `category`;

  • 自连接
-- 查询父子信息,把一张表看为两个一模一样的表
SELECT a.`category_name` AS '父栏目',b.`category_name` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`category_id`=b.`pid`;


4.4 分页和排序

  • 排序
-- 排序: 升序 ASC  降序 DESC
-- ORDER BY 通过那个字段排序,怎么排
-- 查询的结果根据成绩降序 排序
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `result` res
ON stu.`student_no` = res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='数据结构-1'
ORDER BY `student_result` DESC;


  • 分页
-- 100w
-- 为什么要分页
-- 缓解数据库压力,给人更好的体验   瀑布流
-- 分页,每页只显示五条数据
-- 语法 : limit 起始值,页面的大小
-- 网页应用:当前,总的页数,每页大小
-- LIMIT 0,5    1~5
-- LIMIT 1,5    2~6
-- LIMIT 6,5
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `result` res
ON stu.`student_no` = res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='数据结构-1'
ORDER BY `student_result` DESC
LIMIT 1,5;
-- 第一页 limit 0,5    (1-1)*5
-- 第二页 limit 5,5    (2-1)*5
-- 第三页 limit 10,5   (3-1)*5
-- 第N页 limit 10,5    (n-1)*pageSize,pageSize
-- pageSize,页面大小
-- (n-1)*pageSize,起始值
-- n,当前页
-- 总页数 = (数据总数%页面大小==0)? (数据总数/页面大小) : (数据总数/页面大小 + 1)

-- 思考:
-- 查询科目高等数学-2,课程成绩排名前十的学生,并且分数要大于60的学生信息(学号,姓名,课程名称,分数)
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `subject` sub
ON stu.`grade_id`=sub.`grade_id`
INNER JOIN `result` res
ON sub.`subject_no`=res.`subject_no`
WHERE sub.`subject_name`='高等数学-2'
AND res.`student_result`>60
ORDER BY res.`student_result`
LIMIT 0,10;

  • 语法: limit (查询起始下标,页面大小)

4.5 子查询和嵌套查询

  • where (这个值是计算出来的)
  • 本质: 在where语句中嵌套一个子查询语句
/*============== 子查询和嵌套查询 ================
什么是子查询?
   在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
   嵌套查询可由多个子查询组成,求解的方式是由里及外;
   子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/

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

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80

-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname FROM student s
INNER 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'
  )
)


4.5 分组和过滤

  • 分组和过滤
 -- 查询不同课程的平均分,最高分,最低分
 -- 前提:根据不同的课程进行分组
 
 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是从数据表中的>字段直接进行的筛选的
 */


  • HAVING:再筛选

4.6 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}];
   -- 指定查询的记录从哪条至哪条

select小结中文解释
顺序很重要:

select 去重 要查询的字段 from 表 (注意:表和字段可以取别名)
XXX join 要连接的表 on 等值判断
where (具体的值,子查询语句)
Group by (通过哪个字段来分组)
Having (过滤分组后的信息,条件和where是一样的,位置不同)
Order by (通过哪个字段排序)[升序/降序]
Limit startindex,pagesize

业务层面:
查询:跨表,跨数据库

5.MySQL 常用函数

5.1 常用函数

-- ============================== 常用函数=====================================
-- 数学运算

SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4); -- 向上取整
SELECT FLOOR(9.4);  -- 向下取整
SELECT RAND(); -- 返回0-1随机数
SELECT SIGN(-10); -- 判断一个数的符号 0-0 负数返回-1 正数返回1



-- 字符串函数
SELECT CHAR_LENGTH('2323232'); -- 返回字符串长度
SELECT CONCAT('我','233'); -- 拼接字符串
SELECT INSERT('java',1,2,'cccc'); -- 从某个位置开始替换某个长度
SELECT UPPER('abc'); -- 小写
SELECT LOWER('ABC');-- 大写
SELECT REPLACE('坚持就能成功','坚持','努力'); -- 替换出现的指定字符串
SELECT INSTR('kuangshen','h'); -- 返回第一次出现的子串的索引
SELECT SUBSTR('狂神说坚持就能!!!成功',4,6); -- 返回指定的子字符串(原字符串,截取的位置,截取的长度)
SELECT REVERSE('狂神说坚持就能成功'); -- 反转


-- 查询姓 周 的同学 ,改成邹
SELECT REPLACE(studentname,'周','邹'); FROM student
WHERE studentname LIKE '周%';

-- 时间跟日期函数(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME()  -- 本地时间
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() /*版本*/

5.2 聚合函数及分组过滤

       函数名称               描述     
COUNT() 计数
SUM() 求和
AVG() 平均值
MAX() 最大值
MIN() 最小值
-- 聚合函数

-- 都能统计 表中数据(想查询一个表中有多少个记录,就使用这个count())


 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;-- Count(字段),会忽略所有的 null 值
 SELECT COUNT(*) FROM student;-- Count(*),不会忽略 null 值,本质 计算行数
 SELECT COUNT(1) FROM student;  /*推荐*/ -- Count(1),不会忽略所有的 null 值,本质 计算行数
 

 -- 从含义上讲,count(1) 与 count(*) 都表示对全部数据行的查询。
 -- count(字段) 会统计该字段在表中出现的次数,忽略字段为null 的情况。即不统计字段
 为null 的记录。
 -- count(*) 包括了所有的列,相当于行数,在统计结果的时候,包含字段为null 的记录;
 -- count(1) 用1代表代码行,在统计结果的时候,包含字段为null 的记录 。
 /*
 很多人认为count(1)执行的效率会比count(*)高,原因是count(*)会存在全表扫描,
 而count(1)可以针对一个字段进行查询。其实不然,count(1)和count(*)都会对全表进行扫描,
 统计所有记录的条数,包括那些为null的记录,因此,它们的效率可以说是相差无几。而
 count(字段)则与前两者不同,它会统计该字段不为null的记录条数。
 
 下面它们之间的一些对比:
 
 1)在表没有主键时,count(1)比count(*)快
 2)有主键时,主键作为计算条件,count(主键)效率最高;
 3)若表格只有一个字段,则count(*)效率较高。
 */
 
 SELECT SUM(StudentResult) AS 总和 FROM result;
 SELECT AVG(StudentResult) AS 平均分 FROM result;
 SELECT MAX(StudentResult) AS 最高分 FROM result;
 SELECT MIN(StudentResult) AS 最低分 FROM result;

  • 分组查询和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
--核心:根据不同的课程分组
SELECT sub.subject_name AS '课程',
AVG(res.student_result) AS '平均分',
MAX(res.student_result) AS '最高分',
MIN(res.student_result) AS '最低分'
FROM result res
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
GROUP BY res.`subject_no`--通过什么字段来分组
HAVING AVG(res.student_result) >80;


5.3 数据库级别的MD5加密

  • 什么是MD5:MD5即Message-Digest Algorithm 5(信息-摘要算法5),用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一(又译摘要算法、哈希算法),主流编程语言普遍已有MD5实现。将数据(如汉字)运算为另一固定长度值,是杂凑算法的基础原理,MD5的前身有MD2、MD3和MD4。
    • 主要增强算法复杂度和不可逆性
    • MD5不可逆,具体的值的md5是一样的
    • MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密前的值

--  ===================测试MD5 加密====================
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,'张三','123456'),
(2,'zhaoda','123456'),
(3,'lisi','123456'),
(4,'wangwu','123456');

-- 加密
UPDATE  testmd5 SET pwd=MD5(pwd) WHERE id=1;

UPDATE  testmd5 SET pwd=MD5(pwd);-- 加密全部密码
-- 插入的时候加密
INSERT  INTO testmd5  VALUES(6,'小明',MD5('123456'));
select * from  testmd5;

-- 如何校验:将用户传递进来的密码  进行md5加密  然后对比加密后的值
SELECT *FROM testmd5  WHERE name='小明' AND pwd=MD5('123456');

6.更多参考

狂神-MySQL最新教程

posted @ 2022-10-12 15:45  哼哼哈¥  阅读(36)  评论(0编辑  收藏  举报