1. 操作数据库
1.1 创建数据库
create database 数据库名
如果想数据库没有就创建，有就不创建可以执行这句话sql
create database if not exists 数据库名
1.2 删除数据库
drop database 数据库名
如果存在就删除
drop database if not exists 数据库名
1.3 使用数据库
切换数据库
/* 如果你的表名或者字段是一个特殊符号，就需要带`` */
user 数据库名
查看所有的数据库
show database
查看数据库
show database 数据库名
2. 数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算的时候一般使用decimal
字符串
- char 字符串固定大小的 0 - 255
- varchar 可变字符串 0-65535
- tinytext 微型文本 2^8 - 1
- text 文本串 2^16 - 1
时间日期
java.util.Date
- date YYYY-MM-DD 日期格式
- time HH: mm: sss 时间格式
- datetime YYYY-MM-DD HH: mm: ss
- timestamp 时间戳 1970.1.1到现在的毫秒数
- year 年份表示
null
- 没有值，未知
注意，不要使用NULL进行运算，结果为NULL
3. 数据库的字段属性
Unsiged:
- 无符号的整数
- 声明了该列不能声明负数
zerofll:
- 0填充的
- 不足的位数
自增
- 通常理解为自增，自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键~index，必须是整数类型
- 可以自定义设计主键自动的起始值和步长
非空
- 假设设置为
not null，如果不给它赋值，就会报错 NULL，如果不填写值，默认就是null
默认
- default
- 设置默认的值
4. 操作数据表
4.1 创建数据表
-- comment 注释
-- default 默认值
-- auto_increment 自增
create table `student`(
`studentno` int(4) not null comment 学号,
`loginpwd` varchar(20) default null,
`studentname` varchar(20) default null comment 学生姓名,
`sex` tinyint(1) default null comment 性别，0或1,
`gradeid` int(11) 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=myisam default charset=utf8;
常用命令
show create database 数据库名 -- 查看创建数据库的语句
show create table 表名 -- 查看数据表的定义语句
desc student -- 显示表的结构
4.2 数据表的类型
-- 关于数据库引擎
-- INNODB 默认使用
-- MYISAM 早些年使用
| MYISAM | INNODB | |
|---|---|---|
| 事物支持 | NO | YES |
| 数据行锁定 | NO | YES |
| 外键约束 | NO | YES |
| 全文索引 | YES | NO |
| 表空间的大小 | 较少 | 较大，约为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
4.3 修改、删除数据表
修改数据表
修改数据表名
-- alter table 旧数据表名 rename as 新数据表名
alter table teacher rename as teacher1
新增表的字段
-- alter table 数据表名 add 字段名 列属性
alter table teacher add age int
修改表的字段（重命名，修改约束）
change：字段重命名
# alter table 表名 change 旧列名 新列名 类型
mysql> alter table t1 change number id char(2);
modify：修改列名的类型
# # alter table 表名 modify 列名 类型
alter table t1 modify num int(2);
alter table t1 modify id int(2) not null;
-- 字段重命名
-- alter table 表名 change 字段名 列属性[]
alter table teacher change age varchar(50)
-- 修改约束
-- alter table 表名 modify 字段名 列属性[]
alter table teacher modify age int
使用modify给现有的主键设置增值
alter table 表名 modify 字段 类型 primary key auto_incrment;
alter table yq modify id int primary key auto_incrment;
注意：modify只能修改列名类型，不能重命名列名，change既能修改列名类型也能重命名列名
删除表的字段
-- alter table 数据表名 drop 字段名
alter table teacher1 drop age1
所有的创建和删除操作尽量加上判断， 以免报错
注意点：
- ``字段名，使用这个包裹
- 注释 -- /**/
- sql关键字大小写不敏感，建议小写
- 所有的符号全部用英文
5. MySQL数据库管理
5.1 外键
建表的同时创建
constraint 外键名 forengin key (列名) references 数据表 (列名)
给现有的表添加外键约束
alter table 成绩 add constraint FK_成绩_学号11 foreign key(学号) references 学生表(学号)
5.2 DML语言
数据库意义：数据存储，数据管理
DML管理：数据操作语言
- insert
- update
- delete
5.3 添加
-- 添加单个数据
-- insert into 数据表名 values("值1", "值2", ...)
insert into teacher values(1, 'yefeng')
-- 添加多个数据
-- insert into 数据名 values("值1", "值2", ...),("值1", "值2", ...)
insert into teacher values(1, 'yefeng'), (2, 'LXY')
注意事项：
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的，但后面的值必须要一一对应，不能少
- 可以同时插入多条数据，values后面的值，需要使用
，隔开
5.4 修改
update 修改谁 （条件）set 原来的值 = 新值
-- 修改学员名字，带了简介
update 'student' set name = 'yefeng' where id = 1
-- 不指定条件的情况下，会改动整张表的内容
update 'student' set name = 'yefeng'
-- 修改多个属性，逗号隔开
update 'student' set name = 'yefeng', password = '12345' where id = 1
条件： where字句运算符id等于某个值，大于某个值，在某个区间内修改....
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <> or != | 不等于 | 5<>6 | true |
| > | |||
| < | |||
| <= | |||
| >= | |||
| between... and... | 在某个范围内容 | [2, 5] | |
| and | && | 5 >1 and 5 < 10 | true |
| or | || | 5 < 1 or 5 < 10 | true |
注意：
- 数据库的列，尽量带上``
- 条件，筛选的条件，如果没有指定，则会修改所有的列
- value，是一个具体的值，也可以是一个变量
5.5 删除
delete 命令
语法：delete from 表名 where 条件
-- 删除数据（避免这样写，会全部删除）
delete from 'student'
-- 删除指定的数据
delete from 'student' where id = 1
truncate命令
作用：完全清空一个数据表，表的结构和索引约束不会变
-- 清空student表
truncate 'student'
delete 的 truncate的区别
- 相同点： 都能删除数据，都不会删除表结构
- 不同点：
- truncate 重新设置自增列计数器会归零
- truncate 不会影响事物
delete from 数据表名 -- 不会影响自增
truncate table test -- 自增会归零
了解即可： delete删除的问题, 重启数据库，现象
- INNODB 自增列会重1开始（存在内存当中的，断点即失）
- MYISAM 继续从上一个自增量开始（存在文件中的，不会丢失）
6. DQL查询数据
6.1 DQL
（data query language：数据查询语言）
- 所有的查询操作都用它 select
- 简单的查询，复杂的查询它都能做
- 数据库中最核心的语言，最重要的语句
- 使用最频率最高的语句
6.2 指定查询字段
语法： select 字段， .... from 表
-- 查询全部的学生 select 字段 from 表
select * from student
-- 查询指定的字段
select 'studentID' 'studentName' from student
-- 别名，给结果起一个名字 as 可以给字段起别名，也可以给表起个别名
select 'studentNo' as 学号， 'studentName' 学生姓名 from student as s
-- 函数 Concat（a,b)
select cancat('姓名:' studentName) as 新名字 from student
去重 distinct
作用：去除select查询出来的结果中的重复的数据，重复的数据只显示一条
select distinct '字段列' from 表名
select distinct 'student' from result
数据库的列（表达式）
select version() -- 查询系统版本（函数）
select 100*3-1 as 结果 -- 用来计算（表达式）
select @@auto_increment_increment -- 查询自增的步数 （变量）
-- 学员考试成绩 + 1分 查看
select 'studentno', 'studentresult' + 1 as '提分后' from result
数据表中的表达式： 文本值， 列， null， 函数， 计算表达式，系统变量....
6.3 where 条件子句
作用： 检索数据中符合条件的值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与 |
| or || | a or a||b | 逻辑或 |
| not ！ | not a !a | 逻辑非 |
select studentNO, studentResult from result
-- 查询考试字段在95~100分之间
select studentNO, studentResult from result
where studentResult >= 95 and StudentResult <=100
模糊查询： 比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| 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 | sql匹配，如果a匹配b，结果为真 |
| in | a in（a1,a2,a3...) | 假设a在a1，或者a2...其中的某一个值中，结果为真 |
-- like 结合 %（代表0到任意个字符） _(一个字符)
select * from student where student like '刘%'
-- 查询姓刘的同学，名字后面只有一个字的
select * from student where student like '刘_'
-- 查询姓刘的同学， 名字后面只有两个字的
select * from student where student like '刘__'
6.4 联表查询 join
-- inner join
select s.name, t.name '老师名字' from student s inner join teacher t
on s.tid = t.id
-- left join
select s.name, t.name '老师名字' from student s left join teacher t
on s.tid = t.id
-- right join
select s.name, t.name '老师名字' from student s right join teacher t
on s.tid = t.id
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可
6.5 分页和排序
分页 limit
语法：select * from 表名 limit 查询起始下标, 显示多少条（下标从0开始）
-- 每页只显示3条数据
select s.id, s.name, t.name '老师名字' from student s inner join teacher t
on s.tid = t.id order by id desc limit 0, 3
排序 order by
排序： 升序asc，降序desc
select s.id, s.name, t.name '老师名字' from student s inner join teacher t
on s.tid = t.id order by id desc
7. Mysql函数
数学运算
| 函数 | 用法 | 功能 |
|---|---|---|
| abs | select abs(-8) | 绝对值 |
| ceiling | select ceiling(9.4) | 向上取整 |
| floor | select floor(9.4) | 向下取正 |
| rang | select rang() | 返回一个0~1之间的随机数 |
| sing | select sing(10) | 判断一个数的符号 0-0 负数返回-1，正数返回1 |
字符串函数
| 函数 | 用法 | 功能 |
|---|---|---|
| char_length | select char_langth("this") | 字符串长度 |
| concat | select concat('我','爱','你‘) | 拼接字符串 |
| insert | select insert("我来编程", 1, 2, '超级爱') | 查询，从某个位置开始替换某个长度 |
| lower | select lower('KUAN') | 转换成小写字母 |
| upper | select upper('xiao') | 转换成大写字母 |
| instr | select instr('yefeng', 'e') | 返回第一次出现的子串的索引 |
| replace | select replace("坚持就能成功", "坚持", "努力") | 替换出现的指定字符串 |
| substr | select substr("坚持就能成功", 4, 6) | 返回指定的子字符串 |
| reverse | select reverse(si isht) | 反转 |
时间日期函数
| 函数 | 用法 | 功能 |
|---|---|---|
| current_date | select current_date() | 获取当前日期 |
| curdate | select curdate() | 获取当前日期 |
| now | select now（） | 获取当前的时间 |
聚合函数
| 函数 | 描述 |
|---|---|
| count（） | 计数 |
| sum（） | 求和 |
| avg（） | 平均数 |
| max（） | 最大值 |
| min（） | 最小值 |
| ... | ... |
8. 事务
8.1 什么是事务
要么都成功， 要么都失败
- SQL执行 A给B转账 A 1000 --> 200 B 200
- SQL执行 B收到A的钱 A 800 --> B 400
将一组SQL放在一个批次中去执行~
事务原则：ACID原则 分别是原子性、一致性、隔离性和持久性
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操
作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每
一个用户开启的事务，不能被其他事务的操作数据所干
扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改
变就是永久性的，接下来即使数据库发生故障也不应该对
其有任何影响
脏读：
指一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
页面统计查询值
生成报表的时候，B有人转账进来300（B事务已经提交）
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
（一般是行影响，如下图所示：多了一行）
set autocommit = 0 -- 关闭事物
set autocommit = 1 -- 开启自动提交
start transaction -- 标记一个事物的开始，从这个之后的sql都在同一个事物内
-- 了解
savepoint 保存点名 -- 设置一个事物
rollback to savepoint 保存点名 -- 回滚到 保存点
release savepoint 保存点名 -- 撤销保存点
9. 索引
9.1 索引的分类
- 主键索引 （primary key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
- 唯一索引 （unique key)
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识位 唯一索引
- 常规索引 (key/index)
- 默认的， index。key关键字来设置
- 全文索引 (fulltext)
- 在特定的数据库引擎下才有，MyiSAM
- 快速定位数据
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
-- 显示所有的索引信息
show index from 表名
show index from student
-- 增加一个全文索引（索引名）列名
alter table school.student add fulltext index 'studentName' ('studentName')
-- explain分析sql执行的状况
explain select * from student -- 非全文索引
explain select * from student where match(studentName) against ('刘')