MySQL-1
1、数据库简介
1.1、数据库概述
什么是数据库？数据库 ( DataBase , 简称DB )
概念 : 长期存放在计算机内,有组织,可共享的大量数据的集合,是一个数据 “仓库”。 它是一个软件，安装在操作系统上的。
作用 : 保存,并能安全管理数据(如:增删改查等),减少冗余… 即保存数据和存储数据
数据库的使用和Java之间的联系：对于javaEE，企业级的java开发，web应用中，
- 前端：就是页面，用于演示和展示数据
- 后台：
- 连接点：如连接数据库 JDBC；
- 连接前端：控制、控制视图跳转，给前端传递数据。 去进行控制响应前端的请求，即前端页面给了一个请求，网址的访问，那么后台要去接收这个请求并响应，然后进行视图跳转，把数据传递给前端，展示对应的页面。
- 数据库：存放数据的地方。比如 txt、Excel、word等。
Java数据库连接（Java Database Connectivity，简称JDBC）是java中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。
1.2、数据库分类
数据库分为关系型和非关系型。
- 关系型数据库 ( SQL )
- MySQL , Oracle , SQL Server , SQLite , DB2 , …
- 关系型数据库通过外键关联来建立表与表之间的关系
- 非关系型数据库 ( NOSQL )
- Redis , MongoDB , …
- 非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定
关系系型数据库：类似于Excel，表和表格。
非关系型数据库：类似于(key value)。
DBMS（数据库管理系统）：
- 数据库管理系统 ( DataBase Management System )
- 数据库管理软件 , 科学组织和存储数据 , 高效地获取和维护数据
- MySQL，就是一种数据库管理系统。
1.3、MySQL简介
MySQL是一个关系型数据库管理系统。由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。
MySQL使用的SQL语言是用于访问数据库的最常用标准语言。它是一个关系型数据库管理系统，它会将数据去保存在不用的表中，而不是将所有的数据放在一个大仓库中，这样增加了速度和灵活性。
特点 :
- 免费 , 开源数据库
- 小巧 , 功能齐全
- 使用便捷
- 可运行于Windows或Linux操作系统
- 可适用于中小型甚至大型网站应用
1.4、安装MySQL
这里建议大家使用压缩版,安装快,方便.不复杂.
软件下载
mysql5.7 64位下载地址:
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
电脑是64位的就下载使用64位版本的！
安装步骤：
1、下载后得到zip压缩包.
2、解压到自己想要安装到的目录。我解压到的目录为： D:\MySQL\mysql-5.7.19
3、添加环境变量：我的电脑->属性->高级系统设置->环境变量。然后选择path编辑，新建：
新建的名称为 D:\MySQL\mysql-5.7.19\bin 这个就是你上面解压文件到指定路径的地址。
如下所示：
4、在mysql文件下新建一个 my.ini文件。文件中的内容同样是你解压的指定路径地址。
my.ini文件的内容如下：
[mysqld]
basedir=D:\MySQL\mysql-5.7.19\
datadir=D:\MySQL\mysql-5.7.19\data\
port=3306
#skip-grant-tables5、启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)。 注意，一定要以管理员身份运行。
6、再输入 mysqld --initialize-insecure --user=mysql 这个表示初始化数据文件
7、输入 net start mysql 启动mysql。
8、然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）
9、进入界面后更改root密码
update mysql.user set authentication_string=password('123456') where user='root'and Host = 'localhost';
10、刷新权限
flush privileges;11、退出mysql，使用 exit 命令。或者net stop mysql
12、修改 my.ini文件。删除最后一句skip-grant-tables
13、重启mysql即可正常使用
net stop mysql
net start mysql
14、重启mysql，进行连接即 net start mysql， 连接成功后，再次输入 mysql -u root -p123456。
连接成功后的标志如下所示：
一步步去做 , 理论上是没有任何问题的 .
我的安装时出现了错误，输入mysqld --console 查看错误日志，发现是端口被占用，这时候在命令窗口可以用 netstat -ano查看所有端口。 查看端口 3306， 因为你的mysql使用的端口为3306，所以查看这个。
也可以输入 netstat -aon|findstr “3306” 查看端口占用情况。终止占用端口的这个进程。这次能成功启动了。
在安装发生错误时，可以参考这个链接去解决：https://www.jianshu.com/p/2a7092d0eea6
1.5、SQLyog
SQLyog ：
- 可手动操作,管理MySQL数据库的软件工具
- 特点 : 简洁 , 易用 , 图形化
安装SQLyog后，打开进行数据库的连接和创建一个school表数据。
使用SQLyog管理工具自己完成以下操作 :
- 连接本地MySQL数据库
- 新建MySchool数据库
- 字段
- GradeID : int(11) , Primary Key (pk)
- GradeName : varchar(50)
- 数据库名称MySchool
- 新建数据库表(grade)
在历史记录中可以看到相对应的数据库操作的语句 .
具体方式如下所示：本机地址为： localhost 127.0.0.1
在前面的mysql中已经设置了用户名为 root， 密码为123456 。如下所示：进行连接：
新建数据库的步骤：
创建一个表格：
创建完成后，和数据库建立连接。
连接数据库：
打开命令窗口，打开数据库，连接数据库，查看数据库中的数据。
net start mysql --打开数据库
mysql -u root -p123456 -- --连接数据库
#update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; --修改用户密码
flush privileges; --刷新权限
--注意，所有的sql语句都要使用 ; 结尾
show databases; --查看所有的数据库
mysql> use school --切换数据库 use为数据库名
Database changed
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表的信息
create database westos; --创建一个数据库
exit; --退出连接
? 命令关键词 : 寻求帮助
-- 表示注释查看自己创建的 school数据库内容如下所示：
连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码
注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !
2、数据库操作
2.1、数据库语言分类
结构化查询语句分类：
- DDL (数据定义语言) 定义和管理数据对象，如数据库，数据表等 。 命令为： CREATE、DROP、ALTER
- DML (数据操作语言) 用于操作数据库对象中所包含的数据。 命令为：INSERT、UPDATE、DELETE
- DQL（数据查询语言) 用于查询数据库数据。 命令为：SELECT
- DCL（数据控制语言) 用于管理数据库的语言，包括管理权限及数据更改。 命令为：GRANT、commit. rollback
2.2、数据库操作
命令行操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
在mysql中关键字不区分大小写。
- 创建数据库 : create database [if not exists] 数据库名;
- 删除数据库 : drop database [if exists] 数据库名;
- 查看数据库 : show databases;
- 使用数据库 : use 数据库名;
学习方法：
- 对照SQLyog工具自动生成的语句学习
- 固定语法中的单词需要记忆
在创建数据库时，引擎的选择区别：
名称 | MyISAM | InnoDB |
事务处理 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大，约2倍 |
2.3、创建数据表
CREATE 属于DDL的一种，语法 如下所示：
create table [if not exists] `表名`(
'字段名1' 列类型 [属性][索引][注释],
'字段名2' 列类型 [属性][索引][注释],
#...
'字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];我们表示 表名 的时候使用反引号 ` ，反引号用于区别MySQL保留字与普通字符而引入的 (键盘tab上面的键)。
2.3.1、数据库的列类型
我们在创建数据库的时候，会对字段名的 列类型进行选择，即这一列中存放的数据类型，有数值型，字符串等，和我们的java中的基本数据类型能够对应上。
列类型 : 规定数据库中该列存放的数据类型。可以选择的数据类型有：
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小数据 3个字节
- int 标准的整数 4个字节 常用 int
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节（精度有问题！）
- decimal 字符串形式的浮点数，用于金融计算的时候，一般用decimal
字符串
- char 字符串固定大小的 0-255
- varchar 可变字符串 0-65535 常用的 String
- tingtest 微型文本 2^8-1
- text 文本串 2^16-1 保存大文本
时间日期
- date YYYY–MM-DD 日期
- time HH：MM：ss 时间格式
- datetime date YYYY–MM-DD HH：MM：ss 最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数！
- year 年份表示
null
- 没有值，未知
- 注意，不要使用NUll进行运算，结果为NUll
2.3.2、数据字段属性
UnSigned：无符号的整数
- 声明该数据列不允许负数 .
ZEROFILL：0填充的
- 不足位数的用0来填充 , 如int(3),5 则为005
Auto_InCrement：自增
- 自动增长的 , 每添加一条数据 , 自动在上一个记录的基础上 +1(默认)
- 通常用来设计唯一的主键·index，必须是整数类型
- 可定义起始值和步长
- 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
- SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
NULL 和 NOT NULL
- 默认为NULL , 即没有插入该列的数值。 我们没有填写该值时，就默认为是 null。
- 如果设置为NOT NULL , 则该列必须有值。如果不给它赋值，就会报错！
DEFAULT：默认的
- 用于设置默认值
- 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值
主键 PRIMARY KEY ，是唯一的，一个表里面只能设置一个。
/* 每一个表，都必须存在以下五个字段！未来做项目用，表示一个记录存在意义。
id 主键
`vestion` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/2.3.3、创建数据库表
-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段) 使用SQL创建
-- 学号int类型， 登录密码 varchar(20) 姓名,性别varchar(2)， 出生日期(datatime), 家庭住址, email
-- 创建表之前 , 一定要先选择数据库
--注意：括号() 必须要使用英文的。 尽量在使用表的名称和字段时 使用 ` ` 去括起来
-- 字符串使用单引号 ''
-- 所有的语句后面加英文逗号 , 最后一个可以不用加
-- PRIMARY KEY 主键， 一般一个表中只有一个唯一的主键！！！
CREATE TABLE IF NOT EXISTS `school`.`student2`(
`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结果：
发现：当表格文件student2 存在的时候，重新创建的表格内容并不会被覆盖掉。
常用命令：
SHOW CREATE DATABASE school; -- 查看数据库的定义
SHOW CREATE TABLE student; -- 查看数据表的定义
DESC student; -- 显示表结构。 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';使用常用命令的结果：
- SHOW CREATE DATABASE school – 查看创建数据库的语句：
Database Create Database
school CREATE DATABASEschool/*!40100 DEFAULT CHARACTER SET utf8 */ - SHOW CREATE TABLE student – 查看student数据表定义的语句：
Table Create Table
student CREATE TABLEstudent(idint(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号’,namevarchar(30) NOT NULL DEFAULT ‘匿名’ COMMENT ‘姓名’,pwdvarchar(20) NOT NULL DEFAULT ‘123456’ COMMENT ‘密码’,sexvarchar(2) NOT NULL DEFAULT ‘女’ COMMENT ‘性别’,birthdaydatetime DEFAULT NULL COMMENT ‘生日’,addressvarchar(100) DEFAULT NULL COMMENT ‘家庭住址’,emailvarchar(50) DEFAULT NULL COMMENT ‘邮箱’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 - DESC student – 显示表的结构 ：
2.4、数据表的类型
CREATE TABLE 表名(
-- 省略一些代码
-- Mysql注释
-- 1. # 单行注释
-- 2. /*...*/ 多行注释
)ENGINE = MyISAM (or InnoDB)
-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;2.4.1、数据表的引擎
MySQL的数据表的引擎类型 : MyISAM , InnoDB , HEAP , BOB , CSV等…
MYISAM | INNODB | |
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 支持（全英文） |
表空间大小 | 较小 | 较大 约MYISAM2倍 |
- INNODB 默认使用，安全性高，事务的处理，多表多用户操作
- MYISAM 早些年使用的，节约空间，速度较快
在物理空间存在的位置
MySQL数据表以文件方式存放在磁盘中
- 包括表文件 , 数据文件 , 以及数据库的选项文件
- 所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库，该文件夹目录下的文件名就对应着数据表。
本质还是文件的存储！
MySQL引擎在物理文件上的区别
- innoDB 在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1文件
- MYISAM对应文件
- *.frm -表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
2.4.2、设置数据库表的字符集编码
charset=utf8
不设置的话，会是mysql默认的字符集集编码~不支持中文。
如果要设置，有以下两种方法：
- 在我们安装数据库MySQL中的配置文件 my.ini 中配置默认的编码：character-set-server=utf8
- 创建时通过命令来设置 , 如 : CREATE TABLE 表名()CHARSET = utf8;
2.4.3、自动生成表的SQL 语句：
小技巧：通过 show create table student语句
显示表信息后右键复制，粘贴到查询面板上，就自动生成表的sql代码。
SHOW CREATE DATABASE school;
SHOW CREATE TABLE `student`;
TABLE CREATE TABLE
student CREATE TABLE `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 AUTO_INCREMENT=12 DEFAULT CHARSET=utf82.5、修改和删除数据表字段
- DDL (数据定义语言) 定义和管理数据对象，如数据库，数据表等 。 命令为： CREATE、DROP、ALTER
- DML (数据操作语言) 用于操作数据库对象中所包含的数据。 命令为：INSERT、UPDATE、DELETE
- DQL（数据查询语言) 用于查询数据库数据。 命令为：SELECT
修改表和删除表：
修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]
修改字段 :
- ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
删除字段 : ALTER TABLE 表名 DROP 字段名
语法：DROP TABLE [IF EXISTS] 表名
- IF EXISTS为可选 , 判断是否存在该数据表
- 如删除不存在的数据表会抛出错误
-- 修改表名: ALTER TABLE 旧表名 rename AS 新表名
ALTER TABLE user1 RENAME AS user
-- 增加表的字段: ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE `user` ADD `age` INT(11)
-- 修改表的字段（重命名，修改约束！）:
ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE user1 MODIFY `age` VARCHAR(11)-- 修改约束
ALTER TABLE 表名 CHANGE `旧字段名` `新字段名` 列属性
ALTER TABLE user1 CHANGE `age` `age1` INT(1) -- 重命名
-- 删除表的字段
alter TABLE user1 DROP `age1`
--删除表(如果表存在再删除)
DROP TABLE IF EXISTS `user1所有的创建和删除操作尽量加上判断，以免报错。
if not exists 如果不存在就创建
if exists 如果存在就删除
注意点：
- ==``==字段名，使用这个包起来。可以使用这个反引号去将 库名、表名、字段名、索引、别名 都包裹，可以避免和关键字重复。 而字符串使用的是 单引号 ’ ’
- 注释： **–注释内容 //
- sql关键字大小写不敏感，建议写小写
- 所有的符号全部用英文
数据库级别的外键
外键
概念：如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。
作用：保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。
创建表的时候指定外键的约束：
方式一：在创建表的时候，增加约束（麻烦，复杂）
CREATE TABLE IF NOT EXISTS `school`.`grade` (
`gradeid` INT(10) NOT NULL auto_increment COMMENT '年级编号',
`gradename` VARCHAR(10) not null COMMENT '年级名字',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
CREATE TABLE if not EXISTS`student` (
`id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`gradeid` INT(10) NOT NULL auto_increment COMMENT '年级编号',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`address` varchar(60) 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创建表之后再去修改：
方式二：**创建表之后再创建外键关系**
alter table `student`
add constraint `FK_gradeid` foreign key(`gradeid`) references `grade` (`gradeid`);
-- alter table `表名`
-- add constraint `约束名` foreign key(作为外键的列) references 哪个表（哪个字段）;删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除自己的表（）。即先删除子表，再删除主表。
以上的操作都是物理外键，数据库级别的外键，，我们不建议使用，避免数据库过多造成困扰。
最佳实践
- 数据库就是单纯的表，只用来存数据，只有行和列
- 我们想使用多张表的数据，想使用外键，程序去实现
2.6、添加和修改数据库
数据库的意义在于：数据存储、数据管理。
DML语言：数据操作语言。用于操作数据库对象中所包含的数据
- insert
- update
- delete
2.6.1、添加 insert
- insert
-- 插入语句（添加）
-- insert into 表名（[字段1,字段2,字段3])VALUES(`值1`),(`2`),(`3`)
INSERT INTO `grade`(`gradename`)VALUES('大四'),
-- 由于主键自增我们可以省略（如果不写字段，它就会一一匹配）
-- 一般写插入语句，我们一定要数据和字段一一对应！
-- 插入多个字段
INSERT INTO `grade` (`gradename`)VALUES('大二'),('大一')
语法：insert into 表名（[字段1,字段2,字段3])VALUES(值1),(2),(3)注意事项：
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的，但是后面的值必须一一对应
- 可以同时插入多条数据，values后面的值，需要使用,隔开即可。 values（）,（）,…
例子：
-- 创建数据表， grade
CREATE TABLE `grade` (
`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '班级id',
`name` VARCHAR(20) NOT NULL COMMENT '班级名称',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
--插入数据
INSERT INTO `grade`(`name`)
VALUES('大二'),('大三')
INSERT INTO `grade`(`id`,`name`)
VALUES('16','猛男'),('17','笑了')结果：我们在使用
INSERT INTO grade(id,name)
VALUES(‘16’,‘猛男’),(‘17’,‘笑了’)
进行插入的时候，我们自己在这里定义了 id，虽然没必要，因为是自增的。 但你要加的话，必须要比当前的 id 大。所以再次插入的时候， 16 17 可以写成 19 120 都行。
2.6.2、修改update
- update
--update 修改谁（条件） set 原来的值=新值
-- 修改学员名字，带了简介
UPDATE `student` SET `name`='ALZN' WHERE id=1
-- 不指定条件的情况下，会改动所有表！
UPDATE `student` SET `name` = 'the shy'
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name` = 'ALZN', `email`='1356207897@qq.com'
--语法：
-- UPDATE 表名 set colnum_name = value.,[colnum_name = value, ...] where [条件]条件：where 运算符 id等于某个值，大于某个值，在某个区间内修改…
操作符会返回布尔值。
操作符 | 含义 | 范围 | 结果 |
= | 等语 | 5=6 | false |
<>或!= | 不等于 | 5<>6 | true |
> | |||
< | |||
<= | |||
>= | |||
between…and… | 在某个范围内 | [2,5] | |
and | 我和你&& | 5>1&&1>2 | false |
or | 我或你|| | 5>1&&1>2 | true |
-- 通过多个条件定位数据
UPDATE `student` SET `name` = 'xiaohu' WHERE id!=2 && id!=3
-- update `student` set `name`='爸爸' where name='大哥' and sex='男'语法：update 表名 set colnum_name=value,[colnum_name=value,…] where[条件]
注意：
- colnum_name 是数据库的列，尽量带上反引号==``== 。只要不是自己添加的数值或字符，数据库的其它表名和列表标签都带上这个反引号。
- 条件，筛选的条件，如果没有指定条件，则会修改所有的列。
- value，是一个具体的值，也可以是一个变量.
- 多个设置的属性之间，使用英文逗号隔开
例子：
SHOW CREATE TABLE `student`; #使用这个技巧，查看自己创建的数据表 的 sql语句
TABLE CREATE TABLE
student CREATE TABLE `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 AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
-- DML 语言. 数据操作语言， INSERT UPDATE DELETE
-- 修改名字
UPDATE `student` SET `name`='ALZN' WHERE id=1;
-- 在不指定条件的情况下，会直接改动所有的表
UPDATE `student` SET `name` = 'the shy';
-- 在修改多个属性的时候，使用逗号隔开
UPDATE `student` SET `name` = 'ALZN', `email`='1356207897@qq.com';
-- 条件下进行修改
UPDATE `student` SET `name` = 'xiaohu' WHERE id!=2 && id!=3;
UPDATE `student` SET `name` = 'the shy' WHERE id=4;
-- value，即修改的值可以是一个变量，并不一定是定值
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE id=5;结果：
2.6.3、delete和truncate详解
- delete
--语法：delete from 表名 [where] 条件
-- 删除数据（避免这样写，会全部删除）
delete from `student`
--删除指定数据
delete from `student` where id=1;- truncate 命令
作用：完全清空一个数据库表，表的结构和索引约束不会变！
– 清空数据库
truncate student
delete 和truncate区别：
- 相同点：都能删除数据，都不会删除表结构
- 不同点：
- TRUNCATE 重新设置 自增列 计数器会归零
- TRUNCATE 不会影响事务
- delete from
test– 不会影响自增 - truncate table
test– 自增会归零
了解即可：DELETE删除的问题，重启数据库，现象如下：
- Innodb 自增列会从1开始（存在内存当中，断电即失）
- MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）
Delete from test 不会影响自增的意思为：在表格中去观察高级栏目中的，即advanced里面的部分：
2.6.4、select语句和别名使用
DQL（数据查询语言) 用于查询数据库数据。 命令为：SELECT
(Data Query LANGUAGE:数据查询语言)
- 所有的查询操作都用它 select
- 简单的查询，复杂的查询它都能做
- 数据库中最核心的语言，最重要的语言
- 使用频率最高的语句
select完整的语法：
select [ALL | distinct]
{*|table.* |[table.field1[as alias1][table.field1[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 字段名 ... from 表名
-- 查询全部的学生 select 字段 from 表
select * from student
-- 查询指定字段
SELECT `studentNo`,`studentName` from studentAS起别名
有的时候，列名字不是那么的见名知意。我们起别名
- **As 字段名 **
- **as 别名 **
- as 表名
-- 别名，给结果起一个名字，AS 给可以给字段起别名，也可以给表起
select `studentNo` AS 学号, `studentName` AS 学生姓名 FROM student AS 学生
--函数 concat（a,b） 将表格中的两列或者自己选中的拼接起来
SELECT CONCAT('姓名', studentName) AS 新名字 FROM student去重 distinct
作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条
-- 查询一下有哪些同学参加了考试，成绩
select * from result -- 查询全部的考试成绩
select `studentNo` from result --查询有哪些同学参加了考试
select distinct `studentNo` from result --发现重复数据， 去重使用表达式的列
数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量
select version() -- 查询系统版本（函数）
select 100*3-1 as 计算结果 --用来计算（表达式）
select @@auto_increment_increment --查询自增的步长（变量）
--学员考试成绩 +1分查看
select `studentNo`,`studentResult`+1 as '提分后' from result例子：
-- 查询所有的学生
SELECT * FROM `student`;
-- 查询指定字段
SELECT `name` FROM `student`;
-- 别名
SELECT `name` AS 姓名, `pwd` AS 密码 FROM `student` AS 学生;
-- 函数 CONCAT(a,b)
SELECT CONCAT("姓名:",`name`) AS 学生名单 FROM `student`;
-- 去重 DISTINCT,去除结果中的重复数据
SELECT `grade` FROM `student`;
SELECT DISTINCT `grade` FROM `student`;
SELECT DISTINCT `name` FROM `student`;
SELECT VERSION(); -- 查询系统版本（函数）2.6.5、where条件子句
where条件子句
作用：检索数据中符合条件的值
搜索的条件一般由一个或多个表达式组成。 结果为 布尔值。
逻辑运算符：
运算符 | 语法 | 描述 |
and && | a and b a&&b | 逻辑与，两个都为真，结果为真 |
or || | a or b a||b | 逻辑或，有一个为真，结果为真 |
not ！ | not a !a | 逻辑非，真为假！假为真！ |
尽量使用英文字母
2.6.6、模糊查询
模糊查询：比较运算符
运算符 | 语法 | 描述 |
is null | a is null | 如果操作符为null，则结果为真 |
is not null | a is not null | 如果操作符为 not null，则结果为真 |
between | a between b and c | 若a在b和c之间，则结果为真 |
Like | a like b | 如果a能匹配到b则结果为真 |
In | a in（a1,a2,a3） | 假设a在a1,a2,a3其中的某一个值，结果为真 |
例子：
-- 查询成绩在90-100之间的
SELECT `name`,`grade` FROM student
WHERE grade>=90 AND grade<=100
SELECT `name`,`grade` FROM student
WHERE grade>=90 && grade<100
-- 模糊查询
SELECT `name`,`grade` FROM student
WHERE grade BETWEEN 90 AND 100
-- id 不等于2的学生成绩
SELECT `name`,`grade` FROM student
WHERE id != 2
SELECT `name`,`grade` FROM student
WHERE NOT id = 2
################## 模糊查询 ###############
-- 查询AL的同学
-- like %表示任意大小的字符 一个下划线 _ 表示一个字符
SELECT `id`, `name` FROM student
WHERE NAME LIKE 'AL%'
SELECT `id`, `name` FROM student
WHERE NAME LIKE 'AL_'
SELECT `id`, `name` FROM student
WHERE NAME LIKE 'AL__'
-- 查询生日为空的同学
SELECT `id`, `name` FROM student
WHERE birthday IS NULL
-- 查询学号 id 为 2 3的成绩
SELECT `id`, `name`, `grade` FROM student
WHERE `id` IN(2,3)2.7、联表查询join
数据库中的表一般都会和其他表中有联系，比如一个学生的表中的年级信息 可能会 年级表中的年纪挂钩，有联系，这两个表中的 年纪就是相同的。
/*
思路：
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定使用哪些连接查询？ 7种
确定交叉点，这两个表中哪个数据是相同的
判断条件：学生表中的studentno=成绩表中的studentno
join（连接的表） on （判断的条件） 连接查询
where 等值查询
*/
-- select完整的语法：
select [ALL | distinct]
{*|table.* |[table.field1[as alias1][table.field1[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}]; --指定查询的记录从哪条至哪条注意：[]括号代表可选的，{}括号代表必选的。且这些查询功能顺序不能改变。即order by不能在where的前面。
在进行这些联合表查询时，会采用： join on —连接查询， where on --等值查询.
-- inner JOIN中间查询
select s.studentno as 学号,studentname as 名字,subjectno as 排名,studentresult as 分数
from student as s
inner join result as r
on s.studentno=r.studentno
-- right join右查询
select s.studentno as 学号,studentname as 名字,subjectno as 排名,studentresult as 分数
from student as s
RIGHT JOIN result as r
on s.studentno=r.studentno
-- left join右查询
select s.studentno as 学号,studentname as 名字,subjectno as 排名,studentresult as 分数
from student as s
left JOIN result as r
on s.studentno=r.studentnoinner JOIN中间查询、right join右查询、left join右查询三者之间的区别：
操作 | 描述 |
inner join | 如果表中至少有一个匹配，就返回行 |
left join | 会从左表中返回所有的值，即使右表中没有匹配 |
right join | 会从右表中返回所有的值，即使左表中没有匹配 |
例子：
-- 我们要 查询哪些数据 SELECT
-- 需要从哪几个表中查 FROM 表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表增加然后再慢慢增加
-- FROM a left join b
-- from a right join b
-- 查询 学号，姓名，科目名，成绩，
SELECT s.studentno ,studentname,subjectname,studentresult
from student as s
left join result r
on s.studentno=r.studentno
inner join subject as sub
on r.subjectno=sub.subjectno
-- 查询缺考同学信息
select s.studentno as 学号,studentname as 名字,subjectno as 排名,studentresult as 分数
from student as s
left JOIN result as r
on s.studentno=r.studentno
where studentresult is null ;自连接及联表查询练习
自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可。
父类表：
categoryid | categoryName |
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类表
categoryid | pid | categoryName |
4 | 3 | 数据库 |
6 | 3 | web开发 |
7 | 5 | ps技术 |
8 | 2 | 办公信息 |
操作：查询父类对应的子类关系。
父类 | 子类 |
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
例子：
CREATE TABLE `school`.`category`(
`categoryid` INT(10) UNSIGNED NOT NULL 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','办公信息');
SHOW CREATE TABLE `category`
DESC `category`
-- 查询父子信息：把一张表看为两个一模一样的表
SELECT a.`categoryName` AS `父栏目`, b.`categoryName` AS `子栏目`
FROM `category` AS a, `category` AS b
WHERE a.`categoryid`=b.`pid`结果：
2.8、分页和排序
排序：可以按照**升序ASC, ** 降序DESC 进行排序。关键词为 order by … asc/desc
/*
语法 : ORDER BY
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。
*/
-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC分页：当数据有100万时？ 主要是为了缓解数据库压力，有更好的体验。
[limit{[offset,]row_count |row_countoffset offset}]; --指定查询的记录从哪条至哪条。
Limit 起始值，页面的大小。 即例子中这两个的页面长度都为5。
例子：
-- 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
LIMIT 1,5注意：起始值的索引是从 0 开始算的。
那么对于页面的起始值，和页面数目的关系为：
-- 第一页 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： 页面大小
-- (n-1)* pageSize： 为起始值
-- n：当前页
-- 数据总数/页面大小 = 总页数2.9、子查询和嵌套查询
-- 查询java第一学年 课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
from student as s
inner join result as r
on s.`studentno`=r.`studentno`
inner join `subject` as sub
r.`subjectno`=sub.`subjectno`
where `subjectname`='java第一学年' and `studentresult`>=80
order by `studentresult` desc
limit 0,10where（这个值是计算出来的）
本质：在where语句中嵌套一个子查询语句
子查询：
- 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
- 嵌套查询可由多个子查询组成,求解的方式是由里及外;
- 子查询返回的结果一般都是集合,故而建议使用IN关键字;
-- 狂神视频中的例子
-- 查询学生编号，学生姓名，和高等数学-2大于80分的编号
select s.`studentno`,`studentname`
from student as s
inner join result as r
on s.`studentno`=r.`studentno`
inner join subject as sub
on r.`subjectno`=sub.`subjectno`
where `subjectname`='高等数学-2' and `studentresult`>80
-- 子查询
select s.`studentno`,`studentname`
from student as s
inner join result as r
on s.`studentno`=r.`studentno`
where `studentresult`>80 and `studentno`=(
select `studentname`
from subject where `subjectname`='高等数学-2'
)
-- 再改造（由里及外）
select `studentno`,`studentname` from student where `studentno`=(
select `studentno` from result where `subjectno`=(
select `subjectno` from `subject` where `subjectname`='高等数学-2'
)
)2.10、分组和过滤
例子：
-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同课程分组）
SELECT s.`subjectname`,avg(`studentresult`) as 平均分,max(`studentresult`),min(`studentresult`)
from result as s
inner join `subject` as sub
on s.`subjectno`=sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING 平均分>80关于 select小结：
顺序很重要
select 去重 要查询的字段 from 表（注意：表和字段可以取别名）
xxx join 要链接的表 on 等值判断
where(具体的值，子查询语句)
group by(通过哪个字段来分组)
having （过滤分组后的信息，条件和where是一样的，位置不同）
order by （通过哪个字段排序）[升序asc 降序desc]
limit startindex, pagesize
业务层面：
查询：跨表，跨数据
3、MySQL函数
常用函数
----------常用函数------------------------------
-- 数学运算
select ABS(-8) -- 绝对值 ABS(X)
select CEILING(9.4) -- 向上取整 ceiling(x)
SELECT FLOOR(9.4) -- 向下取整 FLOOR(X)
SELECT RAND() -- 返回一个0-1之间的随机数
SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1，整数返回1-- 字符串函数
SELECT CHAR_LENGTH("生活不仅有眼前的苟且，还有明天和后一天的苟且") -- 查找字符串长度
SELECT CONCAT("你是在逗我","?") -- 拼接字符串
SELECT INSERT('我爱变成hello',1,4,'超级热爱编程') -- 查询，从某个位置开始替换某个长度。索引位置从1开始。测试结果为：
INSERT(‘我爱变成hello’,1,4,‘超级热爱编程’) – 从某个位置开始替换某个长度。索引位置从1开始。
超级热爱编程hello
SELECT LOWER('ALZN') -- 小写字母
SELECT UPPER('alzn') -- 大写字母
SELECT INSTR('ALZNalzn','n') -- 但会第一次出现的子串的索引. 不论大小
SELECT REPLACE('弟弟只会说坚持就能成功','坚持','努力') -- 一段字符串，替换指定字符串
SELECT SUBSTR('弟弟说坚持就能成功',4.6) -- 一段字符串，从第几个字符开始，截取多少个字符
SELECT REVERSE('清晨我上马') -- 反转字符串-- 查询 姓 周的同学， 改为 邹
SELECT REPLACE(studentname,'周','邹') 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 SYSTEM_USER() -- 用户名
select user() -- 用户名
select version() -- 版本聚合函数及分组过滤
聚合函数（常用）
函数名称 | 描述 |
count() | 计数 |
sum（） | 求和 |
avg（） | 平均值 |
max（） | 最大值 |
min（） | 最小值 |
… |
聚合函数
-- 都能统计 表中的数据
select count(studentname) from student; -- count(字段)，会忽略所有的null值。 指定列的行数。
SELECT count(*) from student; --count(*)，不会忽略null值,本质 计算行数。 这个运行了所有列。
SELECT count(1) from result; -- count(1),不会忽略所有null值 本质 计算行数。 这个只运行了一个列
SELECT sum(`studentresult`) as 总和 from result
SELECT avg(`studentresult`) as 平均分 from result
SELECT max(`studentresult`) as 最高分 from result
SELECT min(`studentresult`) as 最低分 from result4、数据库级别的MD5加密
MD5加密：
什么是MD5？
主要增强算法复杂度和不可逆性。
MD5不可逆，具体的值的MD5是一样的。
MD5 破解网站的原理，背后有一个字典，MD5加密后的值 加密前的值
测试MD5加密：
CREATE TABLE `school`.`testmd5`(
`id` INT(10) NOT NULL,
`name` VARCHAR(10) NOT NULL,
`pwd` VARCHAR(100) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 添加三个用户信息
INSERT INTO `testmd5` VALUES(1,'鑫仔','123'),(2,'ALZN','123'),(3,'果果','123');
-- 给指定用户的密码加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id!=1;
-- 添加信息的时候，给密码加密
INSERT INTO `testmd5` VALUES(4,'小明',MD5('123'));
-- 如何校验，将用户传递进来的密码进行md5加密，用值来对比
SELECT * FROM testmd5 WHERE NAME ='小强' AND pwd= MD5('123');
