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注入的本质:
把传递进来的参数当作字符,用引号引起来,假设其中存在转义字符,会直接被转义,比如单引号’。