Mysql

介绍

DBMS 数据库管理系统-科学有效地管理数据

关系型数据库(SQL)：mysql，oracle，SqlServer，SQLlite等

如mysql是关系型数据库，数据存在一张张表中，表和表，行和列来组织管理数据

非关系型数据库(NOSQL=NOT ONLY SQL)：Redis，MongoDB

如redis非关系型数据库，对象存储，通过对象的自身属性组织和管理数据

mysql内2种数据库引擎：myisam和innoDB

MyIsam ：节约空间，速度较快

InnoDB：支持事务，安全性高，多表多用户操作

1.安装

配置环境变量

userpath中添加mysql的bin文件夹

2.常用命令行操作

--2.1登录
mysql -uroot -p123456
--2.2查看数据库
show databases;
--2.3选择进入某个表
use `表名`;-- 如果表明，字段名是mysql关键字，防止认错要加上 ` ` ps:最好以后都加上
--database changed
--2.4 进入表
show tables;
--退出
exit;

3.注释

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

4.SQL语言

DDL：数据定义语言 define create alter drop
DML：数据操作语言 mange 增删改 insert update delete
DQL：数据查询语言 query
DCL：数据控制语言 control 

5.DDL(定义数据库、表)

-- 创建数据库 --if exists 加判断条件防止已存在时创建或不存在时出错
create database[if exists] base;
-- 删除数据库
drop database [if exists] base;
-- 查看数据库
show databases;
-- 使用数据库 use 数据库名
use base;
-- 
-- 

字段属性解释

字段类型 常用

每张表内必备字段(阿里)

`id` 主键
`version` 乐观锁
`is_delete` 伪删除(改变值)
`gmt_create` 创建时间
`gmt_update` 修改时间

CREATE TABLE IF NOT EXISTS `student` (
 `id` INT(4) 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 '性别',
 `birthday` DATETIME DEFAULT  NULL  COMMENT '出生日期',
 `address` VARCHAR(100) DEFAULT NULL  COMMENT '住址',
 `email` VARCHAR(20) DEFAULT NULL  COMMENT '邮箱',
 PRIMARY KEY(`id`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8

6.DML(表内增删改)

6.1 添加(insert)

标准写法 如果不写表的字段，从第一个字段一一匹配
insert into 表名(字段名1，字段名2，....) values (值1，值2，..)；
插入多行
insert into 表明 values (值1)，(值2);
插入2行 同一个字段的值

6.2 修改(update)

标准写法
update 表 set 字段1=值1，字段2=值2...where 字段=值

批量修改 使用in语句 修改字段n的值为1,2,3的内容
update 表set 字段1=值1，字段2=值2...where 字段n in(n值1,n值2,n值3)

6.3 删除(delete/truncate)

delete 和 truncate区别

相同点：都能删除数据，都不会影响表结构

不同点：truncate清空表数据，自增会归零，重新设置自增列，计数器归零，不会影响事务

delete不会影响自增

标准写法
delete from 表 where 字段n=值n
批量删除  删除字段n的值为1,2,3的内容
delete from 表 where 字段n in (n值1,n值2,n值3)
-- truncate关键字删除 完全清空一个数据库表，表的结构和索引约束不会变
truncate 表名

7.DQL(表内查询)

7.1 普通查询

1.查询所有
select * from 表 
4.查询选定字段所有的值
select (字段1，字段2，字段3) from 表

7.2 去重查询->distinct

去除select语句查询出的结果中重复的数据

select distinct from 表 

7.3 select计算查询 select时直接计算

例如： 查询出的studentResult全部+1

select studentno，studentResult+1 as 加分后 from result

7.4 条件查询->where子句+条件

查询数据中心符合条件的值

可用的条件语句

可用的模糊查询(区间)条件

between and

以上的条件语法都可以出现在select语句中

1.按字段值查询
select * from 表 where 字段=值
2.查询选定字段
select (字段1，字段2，字段3) from 表 where 字段=值
3.与条件查询
select * from 表 where 条件1 and 条件2
4.或条件查询
select * from 表 where 条件1 or 条件2
5.非条件查询
select * from 表 where not 条件1
6.模糊(区间)条件查询 where 字段 between a and b
select * from 表 where 字段 between 条件1 and 条件2

7.5 模糊查询

where 字段+运算符，对运算符进行匹配，如果匹配上，返回匹配上的结果

可用的运算符：is null,is not null ,between,like,in...

模糊查询字段的匹配符

like+

%，存在一个或多个

_，仅存在一个

具体值+

in ( 条件1,2,3)，查询符合条件的记录

实例:

select * form stu where name like '刘%'  --查询以刘字开头的姓名，后面不管有几个字
select * form stu where name like '刘_'  --查询以刘字开头的姓名，且后面只有一个字
select * from stu where name like '%刘%' --查询名字中包含刘字的姓名，不管名字有几个字
select * from stu where name like '%刘'  --查询以刘字结尾的姓名
select * from stu where name like '_刘'  --查询2个字的名字以刘字结尾
select * from stu where name like '_刘_' --查询3个字的名字中间是刘字
select * from stu where no in (1001,1002,1003) --查询学号在01,02,03范围内的记录

7.6.多表查询

创建数据表

dept 部门表

emp 员工表

多表连接查询的形式

1.笛卡尔积 （不常用）

select * from dept,emp;

查询结果，每张表的每一条数据都对应另一张表的每条数据，结果条数为2张表条数之积，没有参考意义

2.内连接 inner join on

-- 一种写法
select * from dept,emp where dept.id=emp.did
-- 第二种写法 inner join 内连接
select * from dept inner join emp on dept.id = emp.did

查询结果，查询出2张表内的内容都符合条件的条目，不会出现行政部

3.外连接

3.1 左外连接 left join

查询出左边表全部内容和右边表内符合查询条件的条目

select * from dept left join emp on dept.id=emp.did

查询结果，dept所有内容，包括行政部，右边表内，没有小赵

3.2右外连接 right join

查询出右边表全部内容和左边表内符合查询条件的条目

select * from dept right join emp on dept.id=emp.did

查询结果，dept表内不包括行政部，右边表内，包括小赵

3.3 全外连接(mysql不支持，oracle支持) full join

查询出左边表和右边表全部内容

select * from dept full join emp on dept.id = emp.did

查询结果，2张表内的内容都有

7.7 自连接，排序/分页

自连接：

一张表拆为2张一样的表，查询父子关系

select a.proname,b.prodname form prod a,prod b where a.prodid=b.prodpid;

排序：order by asc升序，desc降序

语法：order by 字段 asc/desc 默认升序

select stuno from stu order by stuno desc;

分页：limit

(n-1)pageSize，pagesize n 当前页，pagesize 每页条数 (n-1)pageSize 起始页

语法：所有语句语法的最后一条 limit 起始页，分页大小(每页数据条数)

select * from stu order by stu no desc limit 1,5;

7.8子查询/嵌套查询

本质，在where子句中嵌套一个子查询语句,好处 不用联表查询

子查询由里及外

实例

select stuNo,stuScore,subjectNo form stu where(select subjectNo form subject where subjectname = "科目1")

7.9 MySql函数

7.9.1常用函数 只有很少的常用

--数学函数
1.取绝对值 select(值) ABS
2.向上取整 select(值) ceiling
3.向下取整 select(值) floor
4.随机数 select rand(值)
--
5.返回参数的符号 select sign(值) 返回 -1 0 1
--字符串函数
1.字符串长度 select char_length(字符串) 
2.合并字符串 select Concat('值1'，'值2')
3.替换字符串 select insert()
4.转换大小写 select LOWER/UPPER
5.返回第一次出现的字符串的索引 select instr（） --相当于java indexof
6.替换整体字符串 select replace（）
7.截取字符串 select substr(原字符串，开始截取位置，截取长度) 返回指定的字符串
8.翻转字符串 select reverse

7.9.2聚合函数

用法：count(指定列)、count(*)、count(1)

都能够统计表中的数据条数

区别：

1.指定列会忽略所有null值

2.count(*)和count(1)不会忽略null值

3.*和1计算所有行数 count(1)常用

实例：

select SUM(stuScore) 总分 from student
select AVG(stuScore) 平均分 from student
select MAX(stuScore) 最高分 from student
select MIN(stuScore) 最低分 from student
select count(1) 总条数 from student	

7.10 分组 Group by 和having

通过什么字段来分组

有些数据分组后再过滤条件，用having

8.事务

8.1 事务特点

核心：要么都成功，要么都失败-- 案例 转账

事务四大特性

原子性：要么都成功，要么都失败

一致性：事务前后的数据完整性保持一直

隔离性：多个用户操作一行数据，为每个用户开启单独事务，互不影响*

持久性：事务一旦提交，不可逆转，没提交，还是原来的数据

*隔离性出现问题的情况：

脏读：

不可重复读：

虚读：

8.2 mysql事务

mysql默认开启事务自动提交

SET AUTOCOMMIT=0 关闭
SET AUTOCOMMIT=1 开启

手动处理事务

-- 标记事务开始
START TRANSATION 
-- 以下作为事务组内内容

insert...
insert...

-- 如果事务长，可以中间设置保存点
savepoint 保存点名 
--事务提交
COMMIT
--事务回滚
ROLLBACK
--事务结束

9.索引

9.1 啥是索引

索引是数据结构 通过索引可以更快获取数据

• 主键索引(PRIMARY KEY)
  • 唯一标识，主键不可重复,只能有一个列作为主键
• 唯一索引(UNIQUE KEY)
  • 避免重复的列出现，标记为索引 可以重复 可以有多个
• 常规索引(KEY INDEX)
  • 默认的，index或key关键字来设置
• 全文索引(FullText)
  • 在特定的数据库引擎才有，快速定位数据