目录
十三、DQL查询数据(最重点,基本的select语句和别名使用)
一、下载phpstudy,启动mysql
(下面会介绍两种安装MySQL数据库的方法,第一种是下载phpstudy,第二种是在官网上下载,如果只是上手学习数据库的话还是介意用第一种方法安装,更方便,简单)
1、下载phpstudy
我个人是去小皮面板(phpsyudy)下载得Windows版本(64位)
下载地址:
phpStudy v8.1 (64位)版本(http:I/public.xp.cn/upgrades/phpStudy_64.zip)
下载好了之后打开是一个压缩包的形式,将其解压,启动phpstudy.exe
启动之后在首页哪里点击启动APache和mysql,当看到下面运行运行状态显示已启动就表示启动完毕了
点击网站-管理-打开网站
如果打开之后是下面这种状态
就再返回到网站,点击网站-管理-打开根目录,将里面的文件都删了,新建一个php文件
在里面输入一段完整的代码(也可以用记事本写,以hello world为例)
之后再次点击网站-管理-打开网页,点击刷新后即可
2、Cmd(管理员模式下)启动mysql(每次打开方法都是这样)
在phpstudy启动后点击数据库-修改root密码,在设置完密码之后来到cmd,切换到MySQL所在的目录下(如果找不到目录在哪的可以打开phpstudy点击设置-MySQL,选择版本),输入mysql -uroot -p(加密码),启动MySQL管理界面
如果要退出的话输入exit就可以了
二、官网下载MySQL和MySQL启动(和一些注意事项)
注:如果要去官网下载的话不建议下载exe,卸载会很麻烦,尽可能使用压缩包,在此就只写使用压缩包下载的方法
1、压缩包安装mysql
(1)解压
(2)把这个包放在自己的电脑环境目录下
(3)配置环境变量(把这个MySQL的bin目录的路径复制到电脑环境变量的path路径下)
(4)在mysql文件下新建一个my.ini配置文件
打开这个配置文件,在里面编辑上
注意,basedir的路径要换成自己的,datadir后面的路径会自己生成,不需要我们填写
(5)在cmd(管理员)条件下运行下面所有命令
①将目录切换为MySQL所在的bin目录,然后输入:mysqld -install(安装mysql)
②上一个步骤成功后再输入以下内容,初始化数据文件(如果上一步骤不成功可能是没有切换到bin目录)
mysqld --initialize-insecure -—user=mysql
③启动MySQL然后用命令mysql -u -p进入MySQL管理界面(密码可为空,注意P后面不要有空格,p代表密码,空格也算是一个字符)
④修改密码和刷新权限,在mysql>后面输入:
update user set authentication_string=password(‘密码’) where user=’root’ and Host =’localhost’;
即可修改密码
再在mysql>后面输入:
flush privileges;
即可刷新权限
图例:
⑤跳过密码验证
修改之前的my.ini配置文件,在文件的最后一行的前面加上#
⑥重启mySQL即可正常使用
输入:
net stop mysql
net start mysql
三、下载sqlyog
(为什么要下载sqlyog呢,下载他之后就等于下载了一个可视的mysql,可以再命令行输入操作来sqlyog看命令执行效果,他们之间是相互对应的,也可以在sqlyog里面输入命令,不用打开命令行那么麻烦,下面很多操作也都是使用sqlyog来执行的)
1、下载安装好sqlyog(一般来说安装应该挺快的)
2、
安装完后打开会出现这个窗口(注意如果是安装phpstudy的话要注意记得打开里面的数据库才能再sqlyog下新建)
如果没有的话就按下下图的这个绿色按钮
然后点击新建,保存的链接可以随便取一个,然后其他的可以参照下图,之后再点击链接
然后它下面的数据库对应就是data目录下的数据库
这个目录下
以后在sqlyog上操作数据库会对应在data这个目录里
注意:不要动这几个数据库,不然之后会很麻烦
3、创建数据库
右键点击左侧的root@localhost,点击创建数据库,数据库名称根据需求自拟,字符集选utf8,数据库排序规则选utf8_general_ci,点击创建
4、查看历史纪录
创建好之后,点击历史记录,可以看到相应的创建代码,点击询问,代码就在这输入,每一个sqlyog的执行操作,本质就是对应了一个sql,可以在软件的历史记录中查看
四、介绍mysql
1、前端(页面:展示、,数据!)
后台(连接点:链接数据库JDBC,链接前端(控制,控制试图跳转,和给前端传递数据))
数据库(存数据、txt、excel、word)
2、什么是数据库(DB,DataBase)
(1)概念:数据仓库,软件,安装在操作系统(Windows,linusx,mac...之上)!SQL(SQL可以理解为一个语句),可以存储大量的数据。500万
(2)作用:存储数据,管理数据
3、数据库的五个单位:数据库服务器 、数据库、数据表、数据字段、数据行
(1)数据库服务器:是指用来运行数据库服务的一台电脑
(2)数据库:一个数据库服务器可以有多个数据库。主要来分类使用
(3)数据表:专门用来区分不同的数据
(4)数据字段:也叫数据列,就是我们日常所见表格例的列
(5)数据行:真正的数据春在每一个了的行里面,字段(列)划分出来的一个表应该按照声明扬的格式存数据。而行在每一个表的行里面。每一行需要遵循数据字段(列)的规范和要救进行存入数据
4、数据库分类
(1)关系型数据库:
mysql、oracle、sql server、DB2、sqllite
通过表和表之间,行和列之间的关系进行数据存储, 学员信息表,考核表,.....
(2)非关系型数据库:(NoSQL——Not Only)
①Redis,MongDB
②非关系型数据库,对象存储,通过对象的自身的属性来决定
(3)DBMS(数据库管理系统)
①数据库管理系统软件,科学有效管理我们的数据。维护和获取数据
②Mysql,可以理解为一个数据管理系统
理解图示如下:
5、MySQL介绍
MySQL是一个关系型数据库管理系统
前世:瑞典MySQL AB公司
今生:属于Oracle旗下公司
MySQL是最好的RDBMS(关系数据库管理系统)应用软件之一。
开源的数据库软件
体积小、速度快、总体拥有成本低,招人成本低
中小型网站、或者大型网站,集群!
五、链接数据库(简单了解一些命令行链接)
注意:每一个语句结束后都要有“;”,按下ctrl+c可以强行结束
1、查看所有数据
输入:
show databases ;
2、切换数据库用use
输入:use +数据库名
会自动出现:Database changed
示例:切换到数据库school ,并查看school下面的数据
3、查看数据库中所有的表
输入:
shwo tables;
4、显示数据库中该表的信息
输入:
describe 加表名;
5、创建数据库
输入:
create database +表名;
可以看到此时数据库中多了一个westos数据库
使用表之后,查看表数据,此时表中显示的是empty表示空表的意思
6、退出链接
输入:
exit;
7、注释:
(1)--单行注释(SQL本来的注释)
(2)
/*
注释内容
*/
8、数据库语言的基本分类
DDL 定义
DML 操作
DQL 查询
DCL 控制
六、操作数据库语句
1、主要的操作有:操作数据库 > 操作数据库中的表 > 操作数据库中的数据
(mysql的数据库不分大小写)
2、创建数据库
示例:创建已创建的westos数据库,输入create database westos,输入之后会显示error有错误和exists已经存在,无法创建表
我们给他一个判断,如果这个表不存在就创建
输入: create database if not exists +表名
此时的error就变为了waring警告,该语句变为可执行
3、删除数据库
输入:drop database +表名;
示例:删除一个不存在的数据库hellow
此时就会报错(有error),要想不报错可以加上一个判断,判断如果这个表存在就删除
输入:drop database if exists +表名;
此时的error就变为了警告,该语句变为可执行
4、使用特殊字符命名
如果你的表名或者字段名是一个特殊的字符,就需要带``(在tab键的上面)例如:如果user是一个特殊字符,就不能输入user,要输入`user`
5、查看数据库里的数据(上面有详细讲过)
输入: show databases ;
七、数据库的数据类型和字段属性
1、数值
①tinyint 十分小的数据 1个字节
②smallint 较小的数据 2个字节
③mediumint 中等大小的数据类型 3个字节
④int 标准的整数 4个字节 一般用int
⑤big 较大的数据 8个字节
⑥float 浮点数 4个字节
⑦double 浮点数 8个字节
⑧decimal 字符串形式的浮点数 金融计算的时候一般用decima
2、字符串
①char 字符串固定大小 0~255
②Varchar 可变字符串 0~65535 常用的变量 string
③tinytext 微型文本 2^8-1
④text 文本串 2^16-1 保存大文
3、时间日期
java.util.Date
①date YYYY-MM-DD, 日期格式
②time HH:mm:ss 时间格式
③datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
④timestamp 时间戳, 1970.1.1到现在的毫秒数!也较为常用
⑤year 年份表示
4、Null
①没有值,未知
②注意,不要使用null进行运算,结果为null
5、数据库的字段属性(重点)
(1)什么是字段
再SQLyog下右键要选择需要新建表的数据库,选择创/键-表
出现的主键、非空、自增等就是数据库的字段,通过改变表的字段,而改变表的属性
(2)字段属性
①Unsigned:
无符号的整数
声明了该列的表不能声明负数
②zerofill:
0填充的
不足的位数,使用0来填充,int(3),5…005
例如:创建一个表:长度选择3,勾选0填充。在输入id为1,2,3,4......,100时,会自动变为001,002,003,004,......,100
③自增:
通常理解为自增,自动在上移调记录的基础上+1(默认)
通常用来设计唯一的主键~index,必须是整数类型
可以自定义设计逐渐自增的起始值和步长
④非空 (not null)
假设设置为not null,如果不给它赋值,就会报错
NULL,如果不填写,默认就是null
⑤默认:
设置默认的值!
sex,默认值为 男 (如果不指定)
⑥一些字段(作为一个拓展)
每一个表,都必须存有下面五个字段!未来做项目用的,表示一个记录存在的意义!
id 主键
`Version` 乐观锁
is_delete 伪删除
qmt_create 创建时间
qmt_update 修改时间
八、创建数据库表(此处我使用sqlyog来写)
1、目标:创建一个school数据库
创建学生表(列,字段) 使用SQL创建
学号int ,登入密码verchar(20) ,姓名,性别varchar(2), 出生日期(datetime),家庭住址,emali
2、注意点:使用英文(),表的名称的字段尽量使用``括起来,避免我们碰到特殊字符而出错
3、对应的SQL语句:
CREATE TABLE IF NOT EXISTs `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30)NOT NULL DEFAULT '匿名’COMMENT '姓名',
`pwd` VARCHAR(20)NOT NULL DEFAULT '123456' COMMENT'密码',
`sex` VARCHAR(2)NOT NULL DEFAULT '女'COMMENT‘性别",
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
PRIMARY KEY( id )
)ENGINE=INNODB DEFAULT CHARSET=utf8
4、解释和注意点:-- AUTO_INCREMENT自增
--字竹串使用单引号括起来!
--所有的语句后面加,(英文的),最后一个不用加--
--PRIMARY KEY主键,一般一个表只有一个唯一的主键!
5、创建表的格式:
6、一些常用的命令:
SHOW CREATE DATABASE school --. 查看创建数据库的语句
SHOW CREATE TABLE student. -- 查看student数据表的定义语句
DESC :student, --显示表的结构(显示student表的结构)
九、表的收索引擎
1、
INNODB 默认使用
MYISAM 早些年使用的
2、对比:
MYISAM: 节约空间,速度较快
INNODB: 安全性高,事务的处理,多表多用户操作
MYISAM | INNODB | |
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大 |
3、在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库本质还是文件的存储!
MySQL引擎在物理文件上的区别:InnoDB在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件.
MYISAM对应文件: *.frm表结构的定义文件.*.MYD数据文件(data),*.MYI索引文件(index)
4、设置数据库表的字符编码
(不设置的话,会是mysql默认的字符集编码~(不支持中文! )MySQL的默认编码是Latin1,不支持中文)
①在每次写完代码后加上CHARSET=utf8
②在my.ini中配置默认的编码:character-set-server=utf8
十、修改和删除数据表字段
1、修改表的字段
(1)--修改表名格式:
ALTER TABLE旧表名 RENAME AS新表名
例:ALTER TABLE teacher RENAME AS teacher1
(2)--增加表的字段格式:
ALTER TABLE表名ADD字段名 列属性
例:ALTER TABLE teacher1 ADD age INT(11)
(3)--修改表的字段(重命名,修改约束!)
- 格式:
ALTER TABLE 表名 MODIFY 字段名 列属性[]
例:ALTER TABLE teacher1 MODIFY age VARCHAR(11)--修改约束
- 格式:
ALTER TABLE表名CHANGE旧名字 新名字 列属性 []
例:ALTER TABLE teacher1 CHANGE age age1 INT(1)--字段重名名和修改约束
2、删除表的字段
(1)-删除表的字段格式:
ALTER TABLE 表名DROP字段名
例: ALTER TABLE teacher1 DROP age1
(2)--删除表(如果表存在再删除)
例:DROP TABLE IF EXISTS teacher1
==所有的创建和删除操作尽量加上判断,以免报错~==
十一、外键
(了解即可)
1、用下图来解释以下外键
2、方法一:在创建表的时候,增加约束(麻烦 比较复杂)
示例:
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(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30)NOT NULL DEFAULT '匿名’COMMENT '姓名',
`pwd` VARCHAR(20)NOT NULL DEFAULT '123456'COMMENT '密码',
`sex` VARCHAR(2)NOT NULL DEFAULT '女'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_gradeid` ( `gradeid `),
CONSTRAINT `FK_gradeid` FOREIGN KEY`( `gradeid` ) REFERENCES `grade` ( `gradeid` )
)ENGINE=INNODB DEFAULT CHARSET=utf8
注:删除有外键关系的表的时候,必须要先删除引用别人的表(从表),再删除被引用的表(主表)
3、方式二︰创建表成功后,添加外键约束
示例:
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(4)NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30)NOT NULL DEFAULT '匿名’COMMENT '姓名',
`pwd` VARCHAR(20)NOT NULL DEFAULT '123456'COMMENT '密码',
`sex` VARCHAR(2)NOT NULL DEFAULT '女'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`student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY( gradeid ) REFERENCES `grade` (`gradeid`);
-- ALTER TABLE 表 ADD CONSTRAINT 约束名FOREIGN KEY(作为外键的列)REFERENCES那个表(哪个字段)
以上的操作都是物理外键,数据库级别的外键,我们不建议使用!(避免数据库过多造成困扰)
4、最佳实践
数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)我们想使用多张表的数据,想使用外键((程序去实现)
十二、DML语言(全部记住)
(包括insert、update、delete)
1、
数据库意义:数据存储,数据管理
DML语言:数据操作语言
Insert:添加
Update:修改
Delete:删除
2、insert:添加
(1)语法: insert into 表名 (`字段名1`,`字段2`,`字段3`)va1ues('值1'),(‘值2'),('值3',....)
例:
①在student表的name字段插入张三
INSERT INTO `student`(`name`)VALUES('张三')
②在student表中省略字段名插入’5’’李四’’123456’(注:student表中对应的字段是id,name,pwd)
INSERT INTO `student`VALUES('5','李四','123456')
③在student表中的那么字段插入王五,六七
INSERT INTO `student` (`name`)VALUE ('王五'),('六七')
(2)
注意事项:
①字段和字段之间使用英文逗号隔开
②字段是可以省略的,但是后面的值必须要要——对应,不能少
③可以同时插入多条数据,VALUES后面的值,需要使用,隔开即可
VALUES(),()....
3、update语句:修改
(1)语法: UPDATE 表名 set co1num_name = value,co 1num_name = value,...,where(条件)
(2)条件: where子句 运算符(结果为true或false)
(3)操作运算符:
操作符 | 含义 | 范围 | 结果 |
= | 等于 | 5=6 | false |
<>或!= | 不等于 | 5<>6 | true |
< | 小于 | 5<6 | true |
> | 大于 | 5>6 | false |
<= | 小于等于 | 5<=6 | true |
>= | 大于等于 | 5>=6 | false |
between...and... | 在某个范围内 | [2,5] | |
AND | 我和你&& | 5>1 and 1>2 | false |
or | 我或你 || | 5>1 or 1>2 | true |
(4)注意:
① coInum_name是数据库的列,尽量带上``
②条件,筛选的条件,如果没有指定,则会修改所有的列. value,是一个具体的值,也可以是一个变量
例:UPDATE `student` SET birthday = CURRENT_TIME WHERE `name`='长江7号’AND `sex`='女'
③多个设置的属性之间,使用英文逗号隔开
(5)示例:
① 修改student表下name字段 限制条件:id为
UPDATE `student`SET `name`='小明' WHERE `id`=2
执行结果:
②修改student表下name字段,无限制条件,会改动所有的表
UPDATE `student`SET `name`='大白'
执行结果:
③修改多个属性,逗号隔开,
UPDATE `student` SET `name`='明明',`pwd`='456789'WHERE `id`=3;
执行结果:
4、delete命令:删除
语法: delete from表名[where条件]
例:①-删除数据(避免这样写,会全部删除)
DELETE FROM`student`
②--删除指定数据
DELETE FROM `student` WHERE` id `= 1;
5、tuncate命令
(1)作用:完全清空一个数据库表,表的结构和索引约束不会变!
例:--清空student表
TRUNCATE `student`
(2)不同:
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=utf87
INSERT INTO test( coll) VALUES( '1'),('2 '),('3 ')
DELETE FROMt est --不会影响自增
TRUNCATE TABLE `test` --自增会归零
十三、DQL查询数据(最重点,基本的select语句和别名使用)
1、(Data Query LANGUAGE:数据查询语言)
所有的查询操作都用它Select
简单的查询,复杂的查询它都能做~
数据库中最核心的语言,最重要的语句
使用频率最高的语句
2、语法: SELECT 字段,... FROM表
例:
①查询全部的学生SELECT字段 FROM表
SELECT * FROM student
②查询指定字段
SELECT `studentNo` , 'studentName` FROM `student`
③别名,给结果起一个名字AS可以给字段起别名,也可以给表起别名
SELECT `studentNo` AS 学号,`studentName` As学生姓名 FROM student As s
④函数concat (a,b)
SELECT CONCAT('姓名: ' ,studentName)AS 新名字 FROM student
有的时候,列名字不是那么的见名知意。我们起别名AS字段名 as 别名表名as别名
3、select完整语法:
SELECT
[ALL | DISTINCT |DISTINCTROw ]
[HIGH_PRIORITY]
[STRAIGHT_30IN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT][SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_No_CACHE] [SQL_cALC_FOUND_ROwS]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GRoUP BY {co1_name | expr l position}
[ASC | DESc], ... [wITH ROLLUP]]
[HAVING where_condition]
[ORDER BY ico1_name / expr l position}
[ASC / DESC],...]
[LIMIT {[offset,] row_count l row_count OFFSET offset}]
[PROCEDURE procedure_name (argument_list)]
[INTO OUTFILE 'fi7e_name '
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'fi1e_name'
| INTO var_name l, var_name]]
[FOR UPDATE l LoCK INSHARE MODE]]
十四、去重及据库的表达式
1、去重复:distinct
2、作用:去除SELECT查询出来的结果中重复的数据,只显示一条
3、例:查询一下有哪些同学参加了考试,成绩
SELECT * FROM result --查询全部的考试成绩
SELECT `studentNo` FROM result --查询有哪些同学参加了考试
SELECT DISTINCT `studentNo` FROM result -- 发现重复数据,去重
4、结果对比:
(1)为去重前:
(2)去重后:
5、数据库的列表达式:
(1)SELECT VERSION --查询系统版本(函数)
(2)SELECT 100*3-1 AS 计算结果 --用来计算(表达式)
(3)SELECT @@auto_increment_increment --查询自增的步长(变量)
(4)--学员考试成绩+1分查看
SELECT `studentNo` , `studentResult `+1 AS '提分后’ FROM result
(5)数据库中的表达式: 文本值 ,列,Null,函数,计算表达式,系统变量....
格式:select 表达式 from 表
十五、where子句之逻辑运算符
1、where条件子句作用:检索数据中 符合条件的 值
搜索的条件由一个或者多个表达式组成!结果: 布尔值
2、逻辑运算符(尽量使用英文)
运算符 | 语法 | 描述 |
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a| |b | 逻辑或,其中一个为真,则结果为真 |
Not ! | not a ! a | 逻辑非,真为假,假为真! |
例:
十六、模糊查询操作符详解
1、模糊运算符:比较运算符
运算符 | 语法 | 描述 |
IS NULL | a is null | 如果操作符为NUll,结果为真 |
IS NOT NULL | a is not null | 如果操作符不为null,结果为真 |
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...其中的某一个值中,结果为真 |
例:
十七、联表查询joinON详解
1、图解:(有七种情况)
2、思路:
分析需求,分析查询的字段来自哪些表,(连接查询)
确定使用哪种连接查询? (如上图所示,有七种情况)
确定交叉点(这两个表中哪个数据是相同的)
判断的条件: (两个表之间相同的地方 )例:学生表的中 studentNo =成绩表studentNo
注意一些关键词的表达:
join (连接的表) on (判断的条件) 连接查询
where 等值查询
3、例子和总结:
result表下的字段:
Student表下的字段:
sbject表下的字段:
(1)
SELECT s.studentNo, studentName , subjectNo , StudentResult
FROM student AS s
INNER J0IN result AS r
WHERE s.studentNO = r. studentNO
(1)
SELECT s.studentNo,studentName , subjectNo , studentResult
FROM student AS s
INNERJOIN result As r
oN s.studentNo = r.studentNo
(3)
-- Right Join
SELECT s.studentNo,studentName , subjectNo ,studentResult
FROM student s
RIGHT J0IN result r
ON s.studentNO = r.studentNO
(4)
-- Left join
SELECT s.studentNo, studentName , subjectNo , studentResult
FROM student s
LEFT30IN result r
ON s.studentNO = r.studentNo
(5)
--思考题(查询了参加考试的同学信息:学号,学生姓名,科目名,分数)
思路:
分析需,,分析查询的字段来自哪些表,student. resu1t、subject(连接查询)
确定使川哪种连接查询?7种
确定交叉点〈这两个表中哪个数据是相同的)
判断的条件﹔学生表的中 studentNo =成绩表studentNo=科目表中studentNO
代码如下(多表查询):
SELECT s.studentNo, studentName , subjectName,studentResult,
FROM student s
RIGHT JOIN result r
ON r.studentNo = s.studentNo
INNER JOIN `subject' sub
ON r.subjectNo = sub. subjectNo
(6)多表查询的思路:
--我要查询哪些数据select ...
--从那几个表中查FROM表XXx Join连接的表on交叉条件--假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加
-- From a left join b
-- From a right join b
(7)操作比较
操作 | 描述 |
lnner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
十八、自链接
1、自链接:自己的表和自己的表链接
核心:一张表拆为两张一样的表即可
一下图的表为例解释:
父类:
categoryid | categoryName |
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类:
pid | categoryid | categoryName |
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | 美术设计 |
解释:也就是将第一张表拆为父类、子类两张表,这两张表的联系就是父类的categoryid等于子类的pid
2、例子:
操作:查询父类对应子类的关系
父类 | 子类 |
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
代码:
--查询父子信息: 把一张表看为两个一模一样的表
SELECT a.'categoryName` AS '父栏目' , b. 'categoryName AS `子栏目`
FROM 'category` As a , 'category` As b
WHERE a.'categoryid` = b.'pid`
十九、分页、排序、子查询和嵌套查询
1、select完整的语法顺序:
解释和示例:
排序:升序:ASC ,降序:DESC
--ORDER BY通过那个字段排序,怎么排
--查询的结果根据成绩降序排序
SELECT s. 'studentNo `, `studentName `, subjectName', 'studentResult`
FROM student s
INNER JOIN `result` r
ON s.studentNo = r.studentNo
INNER JOIN `subject` sub
ON r. `subjectNo` = sub. `subjectNo`
WHERE subjectName = '数据库结构-1
'ORDER BY StudentResult ASC
- 分页
(1)为什么要分页:环节数据库压力,给人更好的体验
(2)语法:limit 查询起始下标 , pagesize
(3)例题:
--分页,每页只显示五条数据
--语法:limit起始值,页面的大小
--网页应用:当前,总的页数,页面的大小
-- LIMIT 0,5 查询到1~5条数据
-- LIMIT 1,5 查询到2~6条数据
-- Limit 6,5
SELECT s. 'studentNo `, `studentName`, `subjectName` , `studentResult`
FROM student s
INNER JOIN `result` r
ON s.studentNo = r.studentNo
INNER JOIN `supject` sub
ON r.`subjectNo` = sub. `subjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY studentResult ASC
LIMIT 5,5
(4)
--第一页limit 0,5 (1-1) *5
--第二页limit 5, 5 (2-1)*5
--第三页limit 10,5 (3-1) * 5
--第N页limit 0,5 (n-1) pagesize,pagesize
--【pagesize:页面大小】
-- 【(n-1)* pagesize:起始值】
-- 【n :当前页】
--【数据总数/页面大小=总页数】
- 子查询
- Where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
Where(select *from)
(2)例题分析:
①例题一:
-- 查询数据库结构-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'
)
--查询所有数据库结构-1 的学生学号
②例题二:
--查询课程为高等数学-2且分数不小于80 的同学的学号和姓名
方法一:
SELECT s .studentNo, studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.StudentNolINNERJOIN`subject` sub
ON r. `subjectNo`= sub. `SubjectNo`
WHERE `SubjectName’= '高等数学-2'AND
studentResult>=80
方法二:
分数不小于80分的学生的学号和姓名
SELECT DISTINCT s. `studentNo, `studentName`
FROM student s
INNER JOIN result r
ON r.studentNo = s.studentNo
WHERE`studentResult`>=80
-在这个基础上增加一个科目,高等数学-2—―查询高等数学-2的编号
SELECT DISTINCT s. 'studentNo`, `studentName `
FROM student s
INNER JOIN result r
ON r.studentNo = s.studentNo
WHERE`studentResult`>=80 AND `subjectNo`= (
SELECT subjectNo FROM`subject`
WHERE`subjectName ` ='高等数学-2'
方法三:
--再改造
SELECT StudentNo, StudentName FROM student WHERE StudentNo IN (
SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo = (
SELECT SubjectNo FROM ‘subject` WHERE `SubjectName`= '高等数学-2')
)
二十、MySQL函数
(全部的MySQL函数可以在官网上查询MySQL手册)
1、官网地址:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
2、常用函数
(1)数学运算
SELECT ABS (-8) --绝对值
SELECT CEILING(9.4) --向上取整
SELECT FLOOR(9.4) --向下取整
SELECT RAND () --返回一个0~l 之间的随机数
SELECT SIGN(10) --判断一个数的符号0返回0负数返回-1,正数返回1
(2)字符串
SELECT CHAR_LENGTH('即使再小的帆也能远航') --字符串长度
SELECT CONCAT ('我','爱','你们') --拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') --查询,1,2指从某个位置开始替换某个长度,即重第一个开始,替换两个,被替换掉的就是‘我爱’
SELECT LOWER( ' Kuangshen' ) --转小写字母
SELECT UPPER('Kuangshen') --转大写字母
SELECT INSTR ( ' kuangshen' , 'h') -- 返回第一次出现的子串的索引
SELECT REPLACE('我说坚持就能成功','坚持','努力') --替换出现的指定字符串SELECT SUBSTR ('我说坚持就能成功',3,6) --返回指定的子字符串(源字符串,截取的位置,截取的长度),这里的3,6跟上面的1,2同个道理;3,指第四个,6指6个长度
SELECTREVERSE('清晨我上马') --反转
(3)时间和日期函数(记住)
SELECT CURRENT DATE() --获取当前日期
SELECT CURDATE() --获取当前日期
SELECT NOW() --获取当前的时间
SELECT LOCALTIME() --本地时间
SELECT SYSDATE() --系统时间
(4)系统
SELECT SYSTEMUSER()
SELECT USER()
SEIECT VERSION ()
3、聚合函数(常用)
(1)
函数名称 | 描述 |
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
...... | ...... |
(2)--都能够统计表中的数据(想查询一个表中有多少个记录,就使用这个count ())
SELECT COUNT ( `BornDate `) FROM student; -- Count (字段),会忽略所有的null值
SELECT COUNT(*) FROM student; -- Count(*),不会忽略null值,本质计算行数
SELECT COUNT (1) FROM result;-- Count (1),不会忽略忽略所有的 null值本质计算行数(3)
(3)
SELECT SUM(`StudentResult`)AS总和FROM result
SELECT AVG (`StudentResult)AS 平均分FROM result
SELECT MAX(`StudentResult`) As 最高分FROM result
SELECT MIN(`StudentResult `)As 最低分FROM result
二十一、分组、过滤和数据库级别MD5加密
--查询不同课程的平均分,最高分,最低分,平均分大于80
--核心:(根据不同的课程分组)
SELECT subjectName,AVG(studentResult) AS平均分,MAX(StudentResult) AS最高分,MIN(StudentResult) AS最低分
FROM result r
INNER J0IN `subject` sub
ON r.'subjectNo` = sub. `subjectNo`
GROUP BY r.subjectNo --通过什么字段来分组
HAVING 平均分>80
2、数据库级别的MD5加密
什么是MD5?
主要增强算法复杂度和不可逆性。
MD5不可逆,具体的值的md5是一样的
MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密的前值
3、测试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 VAIUES (1,' zhangsan', ' 123456'),(2,' lisi','123456'),(3, 'wangwu', '123456’)
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
UPDATE testmd5 SET pwd=MD5(pwd) --加密全部的密码
--插入的时候加密
INSERT INTO testmd5 VALUES (4, 'xiaoming ' ,MD5 ('123456' ) )
--如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值
4、
(1)顺序很重要:
select去重要查询的字段from表(注意:表和字段可以取别名)
xxx join要连接的表on等值判断
where(具体的值,子查询语句)
Group By(通过哪个字段来分组)
Having (过滤分组后的信息,条件和where是一样的,位置不同)
Order By ..(通过哪个字段排序)[升序/降序]
Limit startlndex,pagesize
(2)业务层面:
查询:跨表,跨数据库.…..