mysql知识点学习

MYSQL知识点

一、 数据库概念

1、mysql适用于500万以下数据。

二、 数据库分类

1、 关系型数据库(SQL)：mysql、oracle、sqlserver、DB2、sqllite。

2、 非关系型数据库(NoSQL)：不仅仅是数据库、Redis、MongDB。

3、 DB：DataBase数据库：存储数据。

4、 DBMS：DataBaseManagerSystem数据库管理系统：操作和管理数据。

三、 mysql简介

1、 mysql是一个关系型数据库管理系统(RDBMS)。

2、 前世：瑞典mysqlAB公司，今生：属于Oracle旗下公司。

3、 MySQL是最好的RDBMS应用软件之一，开源的数据库软件。

4、 体积小，速度快，总体拥有成本低，中小型网站。

5、 5.7版本比较稳定。

四、 安装mysql详细说明

1、 下载后得到zip压缩包

2、 解压到自己想要安装到的目录，一般解压到D:\Environment\mysql-5.7.19

3、 添加环境变量：

(1) 我的电脑→属性→高级→环境变量

(2) 选择PATH，在其后面添加，MySQL安装文件下的bin文件夹

(3) 在D:\Environment\mysql-5.7.19下新建my.ini文件

(4) 编辑my.ini文件

[mysqld]

basedir=D:\Environment\mysql-5.7.19\

datadir=D:\Environment\mysql-5.719\data\

port=3306

skip-grant-tables

(5) 启动管理员模式下的cmd，并将路径切换至mysql下的bin目录(cd D:\Environment\mysql-5.7.19\bin),然后输入mysqld -install(安装mysql)

(6) 再输入mysqld –initialize-insecure –user =mysql 初始化数据文件

(7) 然后再次启动mysql：net start 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) 输入exit退出mysql管理界面

(12) 修改my.ini文件：注释最后一句 skip -grant – tables

(13) 重启mysql即可正常使用：net stop mysql net start mysql

五、 SQLyog软件安装和使用

1、 SQLyog旗舰版的证书：名称：kuangshen 证书密钥：8d8120df-a5c3-4989-8f47-5afc79-c56e7c

六、 数据类型

1、 数值

(1) tinyint：十分小的数据、1个字节、java类型Integer

(2) smallint：较小的数据、2个字节、java类型Integer

(3) mediumint：中等大小的数据、3个字节、java类型Integer

(4) int：标准的整数、4个字节(常用的int)、java类型Integer

(5) bigint：较大的数据、5个字节

(6) float：浮点数、4个字节、java类型Float

(7) double：浮点数、8个字节、java类型Double

(8) decimal：字符串形式的浮点数、金融计算，一般用decimal、java类型BigDecimal

2 、字符串

(1) char：字符串固定大小的、0-255字节、java类型String

(2) varchar：可变字符串、0-65535字节(常用)、java类型String

(3) tinytext：微型文本、255个字符、java类型String

(4) text：文本串、65535个字符，相当于64kb、java类型String

3、时间日期

(1)date：YYYY-MM-DD、日期格式、java类型Date

(2)time：HH:mm:ss、时间格式、java类型Time

(3)datetime：YYYY-MM-DD HH:mm:ss、常用、java类型Date

(4)timestamp：时间戳、1970-1-1到现在的毫秒数

(5)year：表示年份、java类型Year

4、null

没有值、未知，注意：不要使用null进行运算，结果也为null。

七、数据库的字段属性

1、unsigned

无符号的整数、声明该列不能为负数。

2、zerofill

0填充，不足的位数，使用0来填充，例如：5→005

3、自增

Auto_increment

4、非空

not null

5、默认

default

6、注释

comment

八、创建数据库表：

1、注意事项

使用英文括号()，表的名称和字段尽量使用“`”飘符号括起来，字符串使用单引号括起来，所有的语句后面加英文的逗号,最后一个不用加。

2、sql语句

create table if not exists `student`(
​
`id` int(3) not null auto_increment comment '学号',
​
`name` varchar(30) not null default '匿名' comment '姓名',
​
`pwd` varchar(50) not null default '123456' comment '密码',
​
`sex` varchar(3) 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;

3、格式

create table [if not exists] 表名(

字段名 列类型 [属性] [索引] [注释],

……

字段名 列类型 [属性] [索引] [注释]

) [表类型] [字符集设置] [注释];

4、 常用命令

show create database school：查看创建数据库语句。

show create table student：查看student数据表的定义语句。

desc student：显示表的结构。

九、MyISAM和InnoDB区别：

1、都是数据库引擎(数据库引擎是用于存储、处理和保护数据的核心服务)

2、事务支持：MyISAM不支持、InnoDB支持

数据行锁定：MyISAM不支持、InnoDB支持

外键约束：MyISAM不支持、InnoDB支持

全文索引：MyISAM支持、InnoDB不支持

表空间的大小：MyISAM较小、InnoDB较大，约为2倍

3、MyISAM：节约空间，速度较快、InnoDB：安全性高，事务的处理，多表多用户操作。

十、修改数据表字段：

1、修改表名

alter table 旧表名 rename as 新表名;

alter table teacher rename as teacher1;

2、增加表的字段

alter table 表名 add 字段名 列属性;

alter table teacher1 add age int(11);

3、修改表的字段

alter table 表名 modify 字段名 列属性;

alter table teacher1 modify age varchar(11);

4、字段重命名

alter table 表名 change 旧名字 新名字 列属性;

alter table teacher1 change age age1 int(1);

5、删除表的字段

alter table 表名 drop 字段名;

alter table teacher1 drop age1;

6、删除表

drop table if exists 表名;

drop table if exists teacher1;

十一、DML：数据操作语言

1、注意

数据库中为什么不推荐使用外键约束？

原因：数据库中不得使用外键与级联，一切外键概念必须在应用层解决。每次做delete或者update都必须考虑外键约束，会导致开发的时候很痛苦，测试数据极为不方便。

2、插入数据

语法：insert into 表名([字段名1,字段名2,字段名3……]) values (值1,值2,值3……),( 值1,值2,值3……),( 值1,值2,值3……)……;

注意事项：(1)字段和字段之间使用英文逗号隔开

(2)字段是可以省略的，但是后面的值必须要一一对应，不能少

（3）可以同时插入多条数据，values后面的值，需要使用英文逗号隔开即可，values(),(),()……

3、修改

语法：update 表名 set 列名=值,(列名=值,……) where (条件)

条件：where子句运算符，包括=(等于)，<>或!=(不等于)，>(大于)，<(小于)，>=(大于等于)，<=(小于等于)，between……and……，and，or

4、删除

语法：delete from 表名 [条件]

(1)truncate命令：完全清空一个数据库表，表的结构和索引约束不会变。

(2)delete和truncate区别：相同点：都能删除数据，都不会删表结构；不同点：truncate重新设置自增列计数器归零，不会影响事务。

(3)delete删除的问题：重启数据库时，InnoDB：自增列会从1开始(存在内存中，断电即失)；MyISAM：继续从上一个自增量开始(存在文件中的，不会丢失)

十二、DQL：数据查询语言

1、查询全部学生

select * from 表名

例如：select * from student;

2、查询指定字段

select 字段名 from 表名

例如：select studentname from student;

3、as用法

给字段、表都可以起别名

select studentname as 姓名 from student as s;

4、concat(a,b)

连接函数

select concat(‘姓名：’，studentname) as 新名字 from student;

5、select version()

查询系统版本(函数)

select 100*3-1 as 计算结果：用于计算(表达式)

select @@auto_increment_increment：查询自增的步长(变量)

6、去重：distinct

Select distinct ‘studentno’ from result;

7、where条件子句

运算符：语法：描述

and &&：a and b a&&b：逻辑与，两个都为真，结果为真

or ||：a or b a||b：逻辑或，其中一个为真，则结果为真

not !：not a !a：逻辑非，真为假，假为真

8、模糊查询：比较运算符

运算符：语法：描述

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：%：代表多个字符；_：代表一个字符

in：a in(a1,a2,a3……)：a在a1,a2,a3……中

9、联表查询

左连接：left join：以左表为基准，会从左表中返回所有的值，即使右表中没有匹配。

内连接：inner join：以两表交集为基准，如果表中至少有一个匹配，就返回行。

右连接：right join：以右表为基准，会从右表中返回所有的值，即使左表中没有匹配。

10、自连接查询

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可。

11、select完整的语法

select [all|distinct]

{|table.|table.field1 [as alias1][,table.field2] [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}];

12、排序

order by asc：升序 desc：降序

13、分页：limit

语法：limit 起始值,页面大小

limit (n-1)*pagesize,pagesize

n：当前页 pagesize：页面大小

14、MySQL函数

1、常用函数

1.1、数学运算函数

abs(-8)：绝对值

ceiling(9.4)：向上取整

floor(9.4)：向下取整

rand()：返回0-1的随机数

sign(-10)：判断一个数的符号，0返回0，负数返回-1，正数返回1。

1.2、字符串函数

char_length(‘kuangshen’)：字符串长度

concat(‘kuang’,’shen’)：拼接字符串

insert(‘kuangshen’,1,5,’ai’)：替换指定位置字符串，结果为’aishen’

lower(‘KuangShen’)：转小写字母，结果为‘KUANGSHEN’

upper(‘KuangShen’)：转大写字母，结果为‘KUANGSHEN’

instr(‘KuangShen’,’h’)：返回第一次出现子串的索引，结果为7

replace(‘kuangshen’,’kuang’,’ai’)：替换出现的指定字符串，结果为’aishen’

substr(‘kuangshen’,6,4)：返回指定的子字符串(原字符串,截取位置,截取长度)，结果为’shen’

reverse(‘清晨我上马’)：反转，结果为‘马上我晨清’

1.3、时间和日期函数

current_date()：获取当前日期

curdate()：获取当前日期

now()：获取当前时间

localtime()：获取本地时间

sysdate()：获取系统时间

year(now())：获取当前时间的年

month(now())：获取当前时间的月

day(now())：获取当前时间的日

hour(now())：获取当前时间的时

minute(now())：获取当前时间的分

second(now())：获取当前时间的秒

1.4、系统函数：

system_user()：获取用户

user()：获取用户

version()：获取版本

2、聚合函数

count()：计数：count(指定列)，会忽略所有的null值，null不会计入数中；count(*)，不会忽略null值；count(1)，也不会忽略null值

sum()：求和

avg()：平均数

max()：最大值

min()：最小值

15、数据库级别的MD5加密

MD5(‘123456’)

16、select小结

顺序很重要：

select 去重 要查询的字段 from表(注意：表和字段可以取别名)

inner|left|right join 要连接的表 on 等值判断

where (具体的值或子查询语句)

group by (通过哪个字段分组)

having (过滤分组后的信息，条件和where是一样的，仅位置不同)

order by (通过哪个字段排序)[升序|降序]

limit startindex pagesize;

十三、事务

1、 概念

事务：要么都成功，要么都失败。

2、 事务原则

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

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

一致性：事务前后的数据完整性要保证一致

持久性：事务一旦提交则不可逆，被持久化到数据库中

隔离性：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

3、 隔离导致的问题

脏读：一个事务读取了另一个事务未提交的数据。

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。

虚读(幻读)：一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

4、 语法

mysql是默认开启事务自动提交的

set autocommit = 0 关闭

set autocommit = 1 开启(默认的 )

5、事务流程

(1) 手动处理事务：set autocommit = 0，关闭事务自动提交

(2) 事务开启：start transaction，标记一个事务开始，从这个之后的sql都在同一个事务内

(3) 要执行的sql语句

(4) 提交：持久化(成功)：commit

(5) 回滚：回到原样子(失败)：rollback

(6) 事务结束：set autocommit = 1，开启事务自动提交

6、图解

十四、索引

1、 概念

索引是帮助MySQL高效获取数据的数据结构。

2、 索引的分类

(1) 主键索引(primary key)：唯一标识，不可重复，只能一个列作为主键

(2) 唯一索引(unique key)：可以多个列都为唯一索引

(3) 常规索引(key|index)：默认的，用key|index关键字来设置

(4) 全文索引(fulltext)：快速定位数据，以前在特定的数据库引擎才有

3、 显示所有的索引信息

show index from student;

4、 分析sql执行的状况

explain select * from student; 分析非全文索引

explain select * from student where match(studentName) against(‘刘’);

5、 索引的使用

5.1、在创建表的时候给字段增加索引

语法：

        primary key(‘StudentNo’) 主键索引
        unique key(‘IdentityCard’) 唯一索引
        key(‘Email’) 常规索引

5.2、创建表完成后，增加索引

语法：

alter table school.student add fulltext index ‘StudentName’(‘StudentName’)

5.3、通过create创建索引

语法：索引名的命名习惯：id表名字段名

                create index 索引名 on 表名(字段名)

例如：create index id_app_user_name on app_user(‘name’)

注意：索引在小数量的时候，用处不大，但是在大数据的时候，区别十分明显。

6、 索引原则

(1) 索引不是越多越好

(2) 不要对经常变动数据加索引

(3) 小数据量的表不需加索引

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

7、 索引的数据结构

(1) Hash类型的索引

(2) Btree：InnoDB的默认数据结构

十五、数据库用户管理：

1、 创建用户

create user 用户名 identified by ‘密码‘

create user ‘kuangshen’ identified by ‘123456’

2、 修改当前用户密码

set password = password(‘123456’)

3、 修改指定用户密码

set password for kuangshen = password(‘123456’)

4、 重命名用户名

rename user 原用户名 to 新用户名

rename user kuangshen to kuangshen2

5、 用户授权

all privileges：全部权限，除了不能给别人授权之外，其他权限都可以。

grant all privileges on *.* to kuangshen2

6、 查询权限

查询指定用户的权限：show grant for kuangshen2

show grant for root@localhost

7、 撤销权限

revoke all privileges on *.* from kuangshen2

8、 删除用户

drop user kuangshen2

十六、MySQL备份

1、 直接拷贝物理文件

2、 在sqlyog可视化工具中手动导出

3、 在命令行，通过命令备份：

导出：mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名＞物理磁盘位置文件名

导入：登录的情况下，使用source d:/a.sql

未登录情况下，mysql -u 用户名 -p 密码 库名 ＜ 备份文件

十七、规范数据库设计

1、 糟糕的数据库设计

数据冗余，浪费空间；数据库插入和删除都麻烦；程序的性能差。

2、 良好的数据库设计

节省内存空间；保证数据库的完整性；方便开发系统。

3、 软件开发中，关于数据库的设计

分析需求：分析业务和需要处理的数据库的需求

概要设计：设计关系图E-R图

十八、数据库的三大范式

1、 为什么需要数据规范化

(1)信息重复

(2)更新异常

(3)插入异常

(4)删除异常

2、三大范式概念：

(1)第一范式(1NF)：原子性：保证每一列不可再分

(2)第二范式(2NF)：前提：满足第一范式；每张表只描述一件事

(3)第三范式(3NF)：前提：满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

3、规范性和性能的选举问题：

(1)关联查询的表不得超过三张表(阿里规定)

(2)考虑商业化得需求和目标，(成本、用户体验)数据库的性能更加重要

(3)在提高性能的问题上，需要适当的考虑下规范性。

(4)故意给某些表增加一些冗余的字段(从多表查询中变为单表查询)

(5)故意增加一些计算列(从大数据量降低为小数据量的查询：索引)

十九、JDBC：

1、概念

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

2、 图解

3、 JDBC连接步骤

3.1、 加载驱动

Class.forName(“com.mysql.jdbc.Driver”);//固定写法

3.2、用户信息和url

//useUnicode=true(支持中文编码) characterEncoding=utf8(设置字符集) useSSL=true(使用安全的连接)

String url =“jdbc:mysql://localhost:3306/jdbcstudyuseUnicode=true&characterEncoding=utf8&usrSSL=true”;

String username = “root”;

String password = “123456”;

3.3、 连接成功，数据库对象connection代表数据库

Connection connection = DriverManager.getConnection(url,username,password);

3.4、执行SQL的对象Statement，执行sql的对象

Statement statement = connection.createStatement();

3.5、执行SQL的对象去执行SQL，可能存在结果，查询返回结果

String sql = “select * from users“;

ResultSet resultSet = statement.executeQuery(sql);

while(){

resultSet.getObject(“id”);//id：对应表中的字段名

}

3.6、释放连接

resultSet.close();

statement.close();

connection.close();

4、 JDBC中对象解释

4.1、url语法：

mysql的url写法：jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3

mysql的默认端口3306

oracle的url写法：jdbc:oracle:thin:@主机地址:1521:sid

oracle的默认端口：1521

4.2、Connection代表数据库

connection.setAutoCommit();//数据库设置自动提交

connection.rollback();//事务回滚

connection.commit();//事务提交

4.3、Statement执行SQL的对象(PrepareStatement执行SQL的对象)

statement.executeQuery();//查询操作返回ResultSet

statement.execute();//执行任何SQL

statement.executeUpdate();//更新、插入、删除，均是用这个，返回一个受影响的行数。

4.4、ResultSet查询的结果集：封装了所有的查询结果

resultSet.getObject();//在不知道列类型的情况下使用

resultSet.getString();

resultSet.getDate();

resultSet.getInt();

resultSet.getFloat();

4.5、遍历，指针

resultSet.beforeFirst();//移动到最前面

resultSet.afterLast();//移动到最后面

resultSet.next();//移动到下一个数据

resultSet.previous();//移动到前一行

resultSet.absolute(row);//移动到指定行

4.6、释放资源

connection.close();//耗资源，用完关掉

5、 Statement对象详解

(1) 定义：JDBC中的statement对象用于向数据库发送SQL语句

二十、SQL注入问题

1、 定义

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

2、 SQL注入原理

SQL语句：select * from users where name = ‘kuangshen’ and password = ‘123456’;

被SQL注入的SQL语句：select * from users where name = ‘’ or ‘1=1’ and password = ‘’ or ‘1=1’;

String sql = “select * from users where name = ‘”+username+”’ and password = ‘”+password+”’”;

二十一、PreparedStatement对象

1、 与Statement对象相比较下，PreparedStatement可以防止SQL注入，效率更好

2、 区别：

(1) 使用?占位符代替参数：String sql = ”select * from users where id = ?”

(2) PreparedStatement pst = conn.preparedStatement(sql);//预编译SQL，先写sql，然后不执行

(3) 手动给参数赋值：

pst.setInt(1,4);

注意点：sql.Date：针对于数据库的jar包：new java.sql.Date()

Util.Date：针对于Java的jar包：new Date().getTime()，获取时间戳

3、 PreparedStatement对象防止SQL注入的本质：

把传递进来的参数当作字符，用引号引起来，假设其中存在转义字符，会直接被转义，比如单引号’。