mysql 基础 学习 笔记

开始

了解

类似于 excel 表。（不区分大小写）

select version() ⇒ 查询系统的版本

基础命令

操作	行为
mysql -u用户名 -p密码	CMD登录数据库
flush privileges	刷新操作
shou databases	展示所有的数据库
use 数据库	进入某个库
show tables	展示所有的表
ctrl+c	强行终止
describe 表	查看表中的所有信息
exit	退出
create databases 数据库/表	创建一个数据库/表

操作数据库

创建数据库

create database/table [ if not exists ] 数据库/表

删除数据库

drop database/table [ if exists ] 数据库/表

使用数据库

use 数据库

如果你的表名称是特殊字符的话，就需要用到 “ ` ” 符号 ⇒ 可以编程一个字段。

表的名称和字段尽量用 " `xx ` " 括起来

查看数据库

先略，下面会有。

数据库的列类型

数值

.	.	.
tinyint	十分小的数据	1个字符
smallint	较小的数据	2个字符
mediumint	中等大的数据	3个字符
int	标注整数	4个字节
bigint	较大的数据	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数，金融计算一般使用它	xx

字符串

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

时间跟日期

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

null

没有值，未知
不要使用运算之中，使用了也是null

数据库的字段属性（重点）

Unsigned：

无符号的整数，不能为负数

zerofill：

0填充，不足的位数使用0连填充

自增 auto_increment：

通常理解为自增，默认 +1，必须是整数类型。我们可以自主设置自增的步长。

非空 not null 或 null：

填写了默认不写，则为null，写了 not null，则会报错

默认（default）：

默认的值

备注（comment）：

备注

主键（primary key）：

一般一个表只有一个代表
primary key( `id` )

数据表的类型

INNODB：默认使用
MYISAM：早些年使用

	MYISAM	INNODB
事务支持（两个sql同事执行，要么都成功，要么都失败）	不支持	支持
数据行锁定	不支持 （表锁	支持（行锁
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约MYSIAM两倍

常规使用操作：
.

MYSIAM：节约空间，速度较快
INNODB：安全，事务的处理，多表多操作

在物理空间存在的位置
.

所有的数据库文件都存在于 data 目录下，一个文件夹就对应一个数据库。
本质还是文件的存储

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

• InnoDB在数据表中只有一个 *.frm 文件，以及上级目录下的 ibdata1 文件
• MYSIAM 对应文件
  • *.frm 表结构的定义文件
  • *.MYD 表数据文件（data）
• *.MYI 索引文件（index）

设置数据库字符集编码
.

CHARSET=UTF8

不设置的话，mysql的默认字符集编码是不支持中文的

创建展示：

修改表

修改表名：alter table xx rename xx1
增加表的字段：alter table xx add age xx int(11)
修改表的字段：alter table xx modify age varchar(11) --修改约束
字段重命名：alter table xx change age age1 int(11) --字段重命名
删除表的字段：alter table xx drop age1

外键（了解）

操作属于物理外键，不推荐使用。数据库级别的外键。（避免数据过多造成困扰）

ALERT TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为物理外键) REFERENCES 哪个表（哪个字段）

推荐：不使用物理外键于级联，一切外键概念在物理层尽可能去解决

DML语言（全部重点）

插入语句（insert）

insert into 表名（[字段名1]，[字段名2]，[字段名3]……）value ('值1','值2','值3'……)

如果不写，则会默认一一匹配。一般写插入语句，一定要数据和字段一一对应

修改语句（update）

update school set name = 'test1' where id = 2（条件符，也可以 id<2）

但是注意，不加条件的话，可能会修改整个表！！！ 所以一定要加条件哦~~

修改多个语句的话，用逗号要隔开

操作符	含义
=	等于
<>,!=	不等于
< , >	大于，小于
<=，>=	大于等于，小于等于
between…and	在某个范围之间
and	我和你，例如 5>a and 10<a,例如：&&
or	或者，例如||

案例：

update school set name = 'xxx' where a = 10 and b = 6

删除（delete）

delete from 表名 [where 条件]

完全清空一张数据库表（TRUNCATE）

作用：完全清空一张表，但是表的结构不变。

案例：

truncate '表名'

清空的优势：
①重新设置自增列，计数器会归零。但是delete删除的东西却不会。
②不会影响事务

了解 delete 删除问题（重启数据库现象）

数据库	delete删除清空，并且重启数据库的现象
InnoDB	自增列会重一开始（存储在内存中，会断电即失）
MyISAM	继续从上一个开始（存在文档中，不会丢失）

DQL 数据查询（重点）

select 语句

select 字段, … from xx

select xx1,xx2 + 1 as '分数' from test ⇒ 数据库可以直接用来计算哦 ~~。

as: 可以用来起别名

展示：

select ' Student ' as ' 学生 ' ,' Teacher ' as ' 老师 ' from 表名

concat ( a , b ) ⇒ 提取时追加字符

select concat ( ' 姓名： ' , name ) as nameNew from student

distinct ⇒ 去重

select distinct from 列名 from 表名

where

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

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

select * from test where id < 7 and age = 26

模糊查询：比较运算符

运算符	语法	描述
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，结果为真；%（代表0到任意一个字符，_代表一个字符）
in	a in (a1,a2,a3…)	假设a在a1，或者a2…其中的某一个值，结果为真

select name from test1 where name like “张%” ⇒ 查询名字为 “张x” 或者 “张xx”
.
select name from test1 where name like “张_” ⇒ 查询名字为 “张x”
.
select name from test1 where name like “%张%” ⇒ 查询名字带有“张”的学员信息
.
select age from test2 where age in (6,7,8) ⇒ 查询年龄为 6,7,8 的学员信息

连表查询7种（重点）⇒ join

inner join 展示：

select s.studentNo,studentName,subjectNo,subjectResult
from student As s
inner join result As r
where s.studentNo = r.studentNo

right join…no 展示：( on可以替换where！结果一样，where是等值查询，on是语法所在！)

select s.studentNo,studentName,subjectNo,subjectResult
from student s
right join result r
on r.studentNo = s.studentNo

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

select s.studentNo,studentName,subjectNo,subjectResult
from student s
right join result r
on r.studentNo = s.studentNo
where r.name is null ⇒ 查询第二张表所不存在的内容

自连接

自己的表和自己的表链接，核心一张表拆成两个一样的表

分页查询 和 排序

排序：升序【ASC】和降序【DESC】

语法：limit 页数,每页的大小

order by asc/desc limit 2,3

连表查询和子查询

连表查询案例

select s.`id` as `id`,s.`name` as `姓名1`,t.`name` AS `姓名2`
from `student` as s
INNER JOIN `test` as t
WHERE s.`id` != t.`id`
ORDER BY `id` DESC
LIMIT 2,3;

子查询案例

order by ⇒ 排序
groud by 分组名 having 条件 ⇒ 分组

SELECT `id`,`name`  
FROM `student`  
WHERE `id` IN (  
    SELECT id FROM `test`  
)

注意：子查询 是 先执行里面的，再执行外面的

使用时候的错误案例

select `id`,`name` FROM `student` where `id` = ( SELECT id from `test` )

如果你想选择与test表中所有id匹配的student：你可以使用IN操作符而不是=。
因为在数据库中，“=”是对比单个值的，但是 in 是可以有多个返回值的，所有在这只能用in，而不能用“=”符号！

常用函数

ABS ( -8 ) 绝对值
CEILING ( -8,5 ) ⇒ 9 向上取整
FLOOR ( -8.5 ) ⇒ 8向下取整
RAND ( ) ⇒ 返回一个0-1之间的随机数
SIGN（） ⇒ 判断一个数的符号，是否是正负

CHAR_LENGTH ( ' 即使再小的帆也能远航 ' ) ⇒ 10 返回字符串的长度
CONCAT ( ' 你 ' , ' 好 ' ) ⇒ 你好 拼接字符串
INSERT ( ' 我爱编程 helloWorld ' , 1 , 2 , ' 超级热爱 ') ⇒ 我超级热爱编程helloWorld
LOWER ( ) ⇒ 小写字母
UPPER ( ) ⇒ 大写字母
INSTR（"helloWorld" , "h"）返回第一次出现字符串的索引
REPLACE ( '小肖说坚持就能成功' ，' 坚持 ' , ' 成功 ') 替换出现的指定字符串
SUBSTR（' 小肖说坚持就能成功 ' ，4 , 6 ） 返回指定的子字符串
REVERSE ( ' 小肖说坚持就能成功 ' ) 反转

案例展示：

SELECT REPLCAE(studentName,'周'，'邹') FROM STUDENT
WHERE StudentName like '周%'

select now() 获取当前时间
select year/month/day/hour/minute/second (now()) 获取当前年月日时分秒

聚合函数及分组过滤

函数名称	描述
count	计数
sum	求和
avg	平均值
max	最大值
min	最小值

案例：
select count( student ) from student

但是会忽略所有的 null

select count(*) from student / select count(1) from student

不会忽略null值，但是“1”会比“ * ”更快一点

MD5加密

增强算法的复杂度 和 不可逆

加密：
MD5 ( 需要加密的值 )

校验：

将用户传进来的值，进行加密。加密后进行比对
select * from test where name = "xj" and pwd = md5(' 传进来的密码 ')

事务

要么都成功，要么都失败 ！

事务原则：原子性，一致性，隔离性，持久性（脏读，换读） ⇒ 有问题可以百度查查。

原子性：要么都成功，要么都失败
一致性：事务前后保持一致
持久性：事务一旦开始，便不可逆，被持久化到数据库中
隔离性：事务的隔离性是多用户并发访问到数据库时，数据库为每一个用户开启事务。不能被其他事务所干扰。要互相隔离。

隔离所导致的一些问题。

脏读：

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

幻读：

一个事务内读取到了另一个事务所插入的数据，导致前后读取不一致。

索引

主键索引（primary key）
唯一索引（unique key）
常规索引（key/index）
全文索引（fullText）

数据库用户管理

create user xiaoJian identified by ‘1234567’ ⇒ 创建用户 和 密码
set password = password ( ’ 123456 ’ ) ⇒ 设置密码
set password for xj = password(‘123456’) ⇒ 设置指定用户密码
rename user abc to xj ⇒ 更改用户名称
drop user xj ⇒ 删除用户

数据库的备份

备份方式：

导出：

①直接拷贝物理文件
②在 sqlYog 这种可视化工具中手动导出
③使用命令行导出 mysqldump
mysqldump -h 主机 -u 用户 -p 密码 数据库 表名 > 物理磁盘位置
不写表名就是导出整个数据库

导入：

命令行导入：
source 导入的备份文件

设计数据库

当数据库比较复杂的时候，我们就需要设计数据库了

数据库的三大范式

每张表只描述一件事情！并确保每一列都与主键相关！

关联查询的表不能超过三张表！（成本、用户体验、性能、速度）

JDBC（重点）

JDBC

sun公司为了简化开发人员对数据库的统一操作，提供了一个（java对数据库操作）的规范，俗称 JDBC。

sql注入

preparedStatement ⇒ 效率更高，防止sql注入