mysql

数据库（DB,DataBase)

概念：数据仓库，存储大量数据

作用：存储数据，管理数据

分类：

关系型数据库：

• mysql,oracle,sql server,DB2,SQLlite

非关系型数据库：对象存储，通过对象自身属性决定

• Redis,MongDB

DBMS：数据库管理系统

• 数据库的管理软件，科学有效的管理数据，维护和获取数据。

• mysql是目前最流程的关系型数据库管理系统之一。体积小，速度快，总体拥有成本低，找人成本低适用中小型、大型网站。

连接数据库

mysql -uroot -p

刷新权限

flush privileges;

显示数据库

show databases;

切换使用数据库

如果库名或表名是特殊字符，需要带``

use test1;

查看所有表

show tables;

显示数据库中表的信息

describe  a;

创建数据库

create database if not exists wes1;

删除数据库

drop database if exists wes1;

数据库语言

DDL:定义语言

DML：操作语言

DQL:查询语言

DCL：控制语言

数据库的列类型

数值类型

tinyint:1Bytes; smallint:2Bytes;  mediumint:3Bytes;  int:4Bytes;  bigint:8Bytes;   float:4Bytes;   double:8Bytes;

decimal(M,D):M>D?M+2:D+2字符串形式的浮点数

日期时间类型

date:3;   time:3;   year:1;   datetime:8;    timestamp:4

字符类型

char:0-255bytes;   varchar:0-65535bytes;    tinyblob:0-255bytes;    tinytext:0-255bytes;    blob:0-65535bytes

text:0-65535bytes;    mediumblob:0-16777215bytes;   mediumtext:0-16777215bytes;

longblob:0-4294967295bytes;   longtext:0-4294967295bytes

数据库的字段属性

Unsigned:

• 无符号的整数

• 该列不能声明为负数

zerofill:

• 0填充

• 不足的位数，使用0来填充

自增：

• 通常理解为自增，自动在上一条记录的基础上+1

• 通常用来设计唯一的主键index，必须是整数

• 可以自定义设计主键自增的步长和初始值

非空not null:

• 假设设置为not null,如果不赋值，就会报错。

• 设置为null,不填写值，默认为null

default:

• 设置默认值

version:乐观锁

is_delete:伪删除

gmt_create:创建时间

gmt_update:更新时间

创建数据表

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;

查看创建数据库的语句

show create database school

查看创建表的语句

show create table student

显示表的结构

desc student

MyISAM和InnoDB的区别

InnoDB:

支持事务，支持数据行锁定，支持外键约束，不支持全文检索，表空间较大，约为2倍。安全性高

MyISAM:

不支持事务，不支持数据行锁定，不支持外键约束，支持全文检索，表空间较小，速度较快

所有的数据库文件都存在data目录下，本质还是文件的存储。

mysql引擎在无理文件上的区别：

• InnoDB在数据库表中只有一个*.frm,以及上级目录下的ibdata1文件。

• MYISAM对应文件：

• *.frm:表结构的定义文件

• *.MYD：数据文件

• *.MYI:索引文件

设置数据库表的字符集编码：

不设置的话，会是mysql的默认编码Latin1，不支持中文。

在my.ini中配置默认的编码

修改

alter table teacher rename as taecher1//修改表名
alter table teacher1 add age INT(11)//新添字段
alter table teacher1 modify age varchar(11) //修改表的字段的约束
alter table teachers change age age1 INT(1)//修改表中列名、
alter table teacher1 drop age1//删除表的字段

删除

drop table if exists teacher1//删除表

注意：

• ``字段名使用这个包裹

• 注释/**/

• sql关键字大小写不敏感，建议写小写

• 符号用英文

mysql数据管理

外键

方式一：在创建表的时候，增加约束

KEY FK_grade (`gradeid`),
CONSTRAINT  `FK_grade` FOREIGN KEY (`gradeid`) REFERENCES grade(`gradeid`)

方式二：采用alter方式

alter table s1 add CONSTRAINT `FK_grade` FOREIGN KEY(`gradeid`) REFERENCES grade(`gradeid`)

以上操作都是物理外键，数据库级别的外键，不建议使用。

删除有外键关系的表的时候，先删除引用别人的表，再删除被引用的表

DML语言

数据库意义：数据存储，数据管理

DML语言：insert,delete,update

添加

insert

insert into ...

字段和字段之间使用英文逗号隔开。

字段可以省略，但是后面数据要一一对应。

可以同时插入多条数据，values后面的值，需要使用“,”隔开

修改

update student set name='11' where id='1'

多个设置的属性之间，使用英文逗号隔开

删除

delete from tablename where id=1

truncate tablename:完全清空一个数据库表，表的结构、索引和约束不会变

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

不同点：

• truncate重新设置自增列

• truncate不影响事务

DELETE删除后，重启数据库，此时：

• InnoDB：自增列从1开始，存在内存当中，断电即失

• MyISAM:继续从上一个自增量开始，存在文件中，不会丢失

查询DQL

指定查询字段

select * from student;
select name from student;
select name as studentName from student;
select name studentName from student;
select * from student as st;
select * from student st;
//函数concat(a,b)
select concat('姓名',name) new_name from student;

去重

select * from result;--查询全部成绩
select studentNo from result;--查询哪些学生参加考试
select distinct studentNo from result;--去重查询哪些学生参加考试

查询版本号

select version()

查询计算

select 100*3-1 as result;

查询自增步长

select @@auto_increment increment

where

检索数据中心符合条件的值

逻辑运算符

add &&

or ||

Not !

select studentNo,studentresult from result where sutdentresult between 90 and 100;
select studentNo,studentresult from result where not sutdentresult=100;

模糊查询

is null
is not null
between
like:like+%(代表0到任意字符),like+_(代表一个字符)
in(具体的一个或多个值)

联表查询

join

left join：会把左表中返回所有的值，即使右表中没有匹配的值

right join ：会把右表中返回所有的值，即使左表中没有匹配的值

inner join:如果表中至少有一个匹配，就会返回行

select s1.studentno,s1.studentname,
re1.subjectno,re1.studentresult from student s1 
inner join 
result re1 
on s1.studentno=re1.studentno;

join on:连接查询；join where:等值查询

自连接

自己的表和自己的表连接。

select p.categoryName pname,c.categoryName cname 
from category p,category c 
where p.categoryId = c.pid
--------------------------------------------
select studentNo,studentName,gradeName 
from student s 
inner join grade g
on s.gradeId=g.gradeId

分页和排序

排序：升序：asc  降序：desc(默认)
select studentname,studentgrade from 
students order by studentgrade desc;
分页：缓解数据库压力，加强用户体验
select studentname,studentgrade from 
students order by studentgrade desc 
limit 0,5;(0:起始值，5：大小size)

[pageSize]：页面大小

[(n-1)*pageSize]：起始值

[n]：当前页

[数据总数/页面大小=总页数]

子查询

where里的值不是确定的，在where语句中嵌套一个查询语句

--方式一，连接查询
select studentno,subjectname,studentresult 
from result r 
inner join subject sub 
on r.subjectno=sub.subjectno 
where subjectno='aaa'
order by studentresult desc
--方式二，使用子查询
select studentno,subjectno,studentresult 
from result 
where (select subjectno from subject 
      where subjectname='aaa') 
      order by studentresult desc;

mysql函数

常用函数

abs(-9):绝对值
ceiling(9.4)//10,向上取整
floor(9.7)//9:向下取整
rand()//0-1随机数
sign(10):判断一个数的符号
char_length('123')//字符串长度
concat('1','2')//拼接字符串
insert('eretyy',1,2,'44')//插入
upper(),lower()//大小写
instr('abc','a')//返回第一次出现子串的索引
replace('asdfggv','v','c')//替换
substr('asdfghj',4,3)//返回指定字符串
reverse('asdfghj')//反转
current_date()//当前日期
now()//当前时间
localtime()//本地时间
sysdate()//系统时间
version()//版本
user()//用户

聚合函数

count(*):count(字段)会忽略所有的null值
count(name)：会忽略null值
count(1)：不会忽略所有的null值，本质是计算行数，一些情况下速度最快
count(*):不会忽略null值，本质是计算行数
sum()
avg()
max()
min()

数据库级别的MD5加密

MD5不可逆，具体的值的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
update testmd5 set pwd=MD5(pwd) where id=1;
//校验
select * from testmd5 where name='a' and pwd=MD5('123456')

事务

ACID原则：原子性，一致性，隔离性，持久性

脏读：一个事务读取另一个事务未提交的内容

不可重复读：一个事务在读取时，读到了另一个事务在修改的内容

幻读：一个事务在读取时，读到了另一个事务新增的内容

set autocommit=0|1:设置事务自动开启和关闭
set autocommit=0--手动处理事务
start transaction--标记一个事务的开始
commit--提交，持久化
rollback--回滚
set autocommit=1

--了解
savepoint 点名--保存点名，设置一个事务的保存点
rollback to savepoint 点名--回滚到保存点
release savepoint 点名--删除保存点
create database shop character set utf8 collate utf8_general_ci

index索引

索引是帮助mysql高校获取数据的数据结构。

索引的分类

• 主键索引primary key

• 唯一的标识，主键不可重复，只能有一个列作主键

• 唯一索引unique key

• 避免重复的列出现，唯一索引可以重复，可以设置多个列为唯一索引

• 常规索引key/index

• 默认的，index，key关键字设置

• 全文索引fullText(InnoDB和Myisam现在都支持 )

• 在特定的数据库引擎下支持，之前是myISAM

• 快速定位数据

索引的使用

1.创建表的时候给字段增加索引

2.创建完毕后，增加索引
show index from student--显示所有索引信息
alter table student add fulltext index `studentName`(`studentName`);
explain--在性能分析中，type为fulltext则是全文索引
select * from student where match(studentName) against('a');--使用全文索引查询
--插入多条数据
delimiter $$ --写函数之前必须写，标志
create function mock_data()
return int 
begin 
 declare num int default 1000000;
 declare i int default 0;
 while i<num do
  set i =i+1;
  insert into app_user(`name`,`email`,`phone`,`gender`,
                    `password`,`age`)
                    values
                    (concat('user',i),'[email protected]',
                    concat('18', floor(rand()*((999999999-100000000)+100000000))),floor(rand()*2),UUID(),floor(rand()*100));
    end while;
    return i;
end;
create index id_app_user_name on app_user(`name`);

索引原则

索引不是越多越好

不要 对经常变动的数据加索引

小数据量的表不需要加索引

索引一般加在常用来查询的字段上

索引的数据结构

hash类型的索引

Btree：InnoDB的默认索引

权限管理

用户管理

mysql.user用户表
本质：对这张表进行增删改查
create user user1 identified by '123456'---创建用户
--修改当前密码
set password=password('111111')
--修改指定用户密码
set password for user1 = PASSWORD('111111')
--用户重命名
rename user user1 to user2
--用户授权:全部的权限，库.表;all privileges除了给别人授权，其他都能干
grant all privileges on *.* to user2
--查看权限
show grants for user2 
show grants for root@localhost
--撤销权限
revoke grant privileges on *.* from user2
--删除用户
drop user user2

备份

mysql数据库备份方式

• 直接拷贝物理文件

• 在可视化工具中手动导出

• 命令行导出mysqldump

导出
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
导入
先登录数据库,切换到指定数据库
source D:/a.sql
mysql -uroot -proot 库名 备份文件

规范数据库设计

糟糕的数据库设计

• 数据冗余，浪费空间

• 数据库插入和删除麻烦（尽量不用物理外键）

• 程序的性能差

良好的数据库设计

• 节省内存空间

• 保证数据库完整性

• 方便开发系统

设计数据库步骤（个人博客）

• 收集信息，分析需求

• 用户表

• 分类表

• 文章表

• 友链表

• 自定义表（系统信息，某个关键的字，或一些主字段）（可有可无）

• 标识实体（把需求落地）

• 标识实体之间的关系

数据库的三大范式

第一范式（原子性）：要求数据库表的每一列都是不可分割的原子数据项

第二范式：满足第一范式，非码属性必须完全依赖于候选码，每张表只描述一件事。

第三范式：满足第一、 第二范式，每一列数据都和主键直接相关，而不能是间接相关。

JDBC

数据库驱动

程序通过数据库驱动，来和数据库进行访问。

JDBC

为简化操作，提供java操作数据库规范，就是JDBC。

1.导入数据库驱动

//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//用户信息和url
String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username="root";
String password="123456";
//连接
Connection conn = DriverManager.getConnection(url,username,password);
//conn.rollback();
//conn.commit();
//conn.setAutoCommit();
//执行SQL
Statement st = conn.creatStatement();
String sql = "select * from student";
ResultSet rs = statement.executeQuery(sql);
//st.execute(),st.executeUpdate()
//处理返回结果
while(rs.next()){
    rs.getObject("id");
    //rs.getString();...
    //rs.absolute(row)移动到指定行
}
//关闭连接
rs.close();
st.close();
conn.close();

sql注入

判断不严，导致SQL被攻击，获取数据。eg:or 1=1

PreparedStatement

防止SQL注入，效率更好：把传递进来的参数当做字符

使用？占位符代替参数，先写SQL，后执行

conn.preparedStatement(sql);

st.setInt(1, 5):第几个参数，什么值

st.executeUpdate();//不需要传参了，因为已经传进去了，直接执行就可以

数据库连接池

数据库连接-执行完毕-释放十分耗费资源。

池化技术：准备一些预先的资源，过来就连接预先准备好的资源。

最小连接数

常用 连接数

最大连接数：业务最高承载上限。

等待超时：没有连接

编写连接池，实现接口DataSource

开源数据源实现

DBCP

C3P0

Druid

使用这些数据库连接池之后，就不需要编写连接数据库的代码了。

DBCP

需要用到jar包：

commons-dbcp-1.4,commons-pool-1.6

C3P0

c3p0-0.9.5.5.jar,mchange-commons-java-0.2.19.jar

结论

无论使用哪种数据源，DataSource接口不会变，方法就不会变。