MySQL数据库
1.1 数据库的发展史
1.1.1 萌芽阶段——文件系统
文件系统
1.1.2 第一代数据库——层次模型、网状模型
1.1.2.1层次模型
这是一种导航结构,导航结构的优点:分类管理;导航结构的缺点:如果保存不是同一类的数据,效率很低。
层次结构最大问题是失去了数据的完整性
1.1.2.2网状模型
1.1.3 第二代数据库——关系型数据库
每一个存放数据的表都是独立的,通过公共字段建立关系。
1、 优点:
a) 每个都独立了,查询的时候效率高
b) 对数据的约束功能强大
2、 缺点:多表查询效率低。
多学一招:非关系型数据库(NoSQL),这种数据库是一种键值对形式的数据库,查询效率高,但对数据约束功能很低。(redis、mongodb)。非关系型数据库是对关系型数据库的一种补充。
1.2 数据库系统
数据库的本质就是文件
数据库系统(DBS)=数据库(DB)+数据库管理系统(DBMS)
也就是说:数据库系统包括数据库文件和操作数据库文件的软件。
1.3 SQL
SQL是Structured Query Language(结构化查询语言)缩写,用来操作关系型数据库的语言。
SQL语句是一个标准SQL,可以用来操作所有的关系型数据库。
1.3.1 关系型数据库
每个公司为了更多的占有市场份额,在原来的标准SQL基础上扩展自己独有的东西用来吸引消费者。
数据库 |
使用语言 |
开发公司 |
access |
SQL |
微软 |
SQL Server |
T-SQL |
微软 |
MySQL |
MySQL |
Oracle公司收购 |
Oracle |
PL/SQL |
甲骨文公司 |
问题:已知MySQL和Oracle都支持标准SQL(SQL-92),请问在MySQL上编写的MySQL语句能否运行在Oracle上?
答:不能,因为MySQL是MySQL扩展的东西。不能运行在Oracle上。他们只能相互运行标准SQL。
1.4 MySQL操作过程
1.5 启动\关闭MySQL服务
1.5.1 方法一:在服务面板中启动或关闭
多学一招:通过命令打开服务面板
打开运行面板(win+R),在面板中输入services.msc
1.5.2 方法二:通过命令行启动和关闭
启动服务:net start +服务名
关闭服务:net stop +服务名
多学一招:net start 显示当前所有的服务列表。
1.6 MySQL客户端
1.6.1 MySQL自带的命令行客户端
安装MySQL后,会自带一个MySQL 5.5 Command Line Client命令行客户端。
双击打开,输入密码,就连接上服务器了。
缺点:此客户端只能连接本地的MySQL服务器。
1.6.2 window界面客户端
1、 MySQL-Front
2、 Navicat
1.6.3 web界面
phpMyAdmin
1.6.4 windows的命令行
运行——cmd
1.7 windows命令行连接服务器
host主机-h
username用户名-u
password密码-p
port端口-P
注意:如果配置了MySQL的环境变量,执行mysql命令就不需要进入mysql.exe的目录。
127.0.0.1表示本地地址,等价于localhost
多学一招:如果连接的是本地的MySQL服务器,-h可以省略;如果使用的是3306端口,-P也可以省略
可以通过密文来登录
1.8 退出MySQL客户端并关闭连接
1、 exit
2、 quit
3、 \q
1.9 数据库基本概念
1、 数据库和表
数据库用来存放表,表里面存放数据,一个数据库可以存放多个表,一个表中存放多个数据
2、 关系:两个表的公共字段称为关系
3、 行:也称为记录,也称为实体
4、 列:也称为字段,也称为属性
在结构上称为行和列
在数据上称为记录和字段
5、 数据冗余:相同的数据存储在不同地方
脚下留心:冗余只能减少,不能杜绝。减少冗余后,表的数量就增加了。
6、 数据完整性=正确性+准确性
正确性:数据类型正确
准确性:数据的范围准确
思考:学生年龄是int型,输入10000岁,正确性和准确性如何?
答:正确的,但是不准确
1.10 数据库操作
1.10.1 创建数据库
1、语法:create database if not exists`数据库名` charset=字符编码
2、如果创建的数据库已经存在,则会报错
解决:创建时候判断一下,数据库是否存在,如果不存在就创建
3、如果数据库名是关键字或特殊字符会报错
解决:是数据库名上加上反引号
为了保证创建数据库成功,可以在所有的数据库名上加上反引号
4、可以给创建的数据库指定字符编码
1.10.2 MySQL数据库目录
1、 此目录在安装mysql服务器的时候选择路径
2、 可以在my.ini中查看并更改
3、 创建一个数据库就在data文件夹下创建一个与数据库同名的文件夹,并在此文件夹下多了一个db.opt文件。db.opt文件是设置数据库的字符集和校对集。
1.10.3 显示数据库
语法:show databases;
information_schema:存储了mysql服务器的管理数据库的信息。比如:数据库名、表名、字段名、字段的数据类型、访问权限
performance_schema:MySQL5.5新增的一个数据库,主要用来收集数据库服务器性能参数。
mysql:mysql系统数据库,保存比如用户名、密码
test:给用户学习测试用的数据库
1.10.4 显示创建数据库的SQL语句
语法:show create database `数据名`
1.10.5 修改数据库
只能修改数据库字符编码
语法:alter database `数据库名` charset=字符编码
1.10.6 删除数据库
1、语法:drop database [if exists] `数据库名`
2、如果删除的数据库不存在会报错
解决:删除之前判断一下,存在就删除
1.10.7 选择数据库
语法:use `数据名`
1.11 表的操作
1.11.1 创建表
语法:
create table [if not exists] `表名`(
字段名 数据类型 [null|not null] [default] [auto_increment] [primary key] [comment],
……
)[engine=存储引擎] [charset=字符编码]
null|not null:是否为空
Default: 默认值
Auto_increment: 自动增长
Primary key: 主键
Comment: 备注
Engine:存储引擎,不同存储引擎表示不同的数据存储方式
Charset:设置表的字符编码
1、创建最简单的表
2、创建复杂的表
1.11.2 数据表文件
一个数据库对应一个文件夹,一个表对应一个或多个文件。
1、引擎是myisam,一个表对应三个文件
2、引擎是innodb,一个表对应是一个文件
所有的innodb的数据放在一个统一的文件中管理,路径在data文件夹下ibdata1文件。如果数据量很多,mysql会自动的生成ibdata2,ibdata3,…文件
多学一招:myisam引擎的表相互之间独立, Myisam的表可以随意的拷贝粘贴。innodb引擎的表相互之间不独立,
1.11.3 myisam和innodb引擎
1、 Myisam
a) 读取速度快
b) 对数据的约束能力低(不支持触发器,存储过程等等)
c) 容易产生大量碎片
2、 Innodb
a) 读取速度没有myisam快
b) 对数据的约束能力强(支持触发器,存储过程等等)
c) 不产生碎片
1.11.4 给指定的数据库中创建表
1.11.5 显示表
语法:show tables;
1.11.6 显示表的创建语句
语法:show create table 表名[\G]
\G的作用:
\G是将显示的字段竖着排列
1.11.7 查看表结构
语法:describe[desc] 表名
1.11.8 复制表
基于现在的表创建一个新表
语法一:create table 新表 select 字段1,字段2 from 旧表
脚下留心:这种方式创建表不能从父表中复制主键,自动增长;但是父表中的数据被复制过来。
语法二:create table 新表 like 旧表
脚下留心:like的方法只能复制表结构,不能复制表数据。
1.11.9 修改表
语法:alter table 表名,可以对表添加字段,删除字段等等操作
1、 添加字段 add [column]
1、在最后一列后面添加字段
2、将字段添加到第一列
3、将添加的字段放在指定字段之后
2、 删除字段
不能清空所有的字段。
3、 修改字段 modify change
a) 只改字段属性,不改字段段名(modify)
将name的属性改为varchar(30),并将位置移动到第一列
能修改字段的所有属性,除了字段名和默认值,备注,可以添加任意属性,
b) 改属性并改字段名
将name改名为myname varchar(10)并将位置放在id的后面
能修改字段的所有属性,可以添加任意属性,除了默认值,如果想设置
4、 修改引擎
5、 修改表名 rename to
6、将stu1表移动到data数据库下并改名为stu.
1.11.10 删除表
语法:drop table [if exists] 表1,表2.表3,…
删除多个表
删除之前可以判断表是否存在
可以一次判断多个表是否存在
1.12 数据操作
新建测试表
create table stuinfo(
id int auto_increment primary key comment '主键',
name varchar(20) not null comment '姓名',
sex char(1) not null default '男' comment '性别',
score int comment '成绩'
)engine=innodb charset=utf8;
1.12.1 插入数据
语法:insert into 表名 (字段名) values (值)
总结:
1、 值的个数、顺序和插入字段的个数、顺序必须一致。
2、 自动增长列可以手动输入数字,也可以通过null让MySQL自动增长
3、 插入字段和表字段顺序可以不一致,但是插入的值必须和插入字段的顺序一致。
4、 如果插入值的顺序、个数和表字段的顺序个数一致的话,插入字段可以省略
5、 插入默认值
a) 如果一个字段有默认值,此字段上没有值的插入就会自动的插入默认值
b) 通过default关键字插入默认值
6、 一次插入多条数据
7、 使用insert…set插入数据
1.12.2 修改数据
语法:update 表名 set 字段名=值 [where 条件] [order by 排序] [limit 限制]
将rose的性别改成“女”,成绩改成66
将班级的前3名同学性别改成男
1.12.3 删除数据
语法:delete from 表 [where条件] [order by 排序 asc|desc] [limit 数量]
--删除学号是1的学生
--删除第一名
--删除成绩在80分以下的
--清空表
多学一招:清空表的方法有两个,第一个是delete from 表名,还有一个是truncate table 表名。delete from 表名执行过程是将数据一条一条的依次删除。truncate table执行过程是将整个表删除同时创建一个相同的表,很显然这种方式清空表的效率高。
Select * from 表名 删除表的时候自增长的属性还在,在重新加入的时候会延续自增长的键值。
1.13 字符集
字符集:可见字符在保存和传输时对应的二进制编码集合。由概念可知,字符
集在两个地方使用
1、 数据存储的时候
2、 数据传输的时候
1.13.1 数据存储的时候使用字符集
MySQL可以在服务器、数据库、表、字段上设置字符编码
注意:字符编码在数据库上设置就可以了。
1.13.2 数据在传输的时候使用字符集
场景:
1、 创建表的时候只用中文就报错
2、 在插入数据中出现中文报错
分析
客户端发送的编码有客户端决定的。我们现在用的客户端是windows的命令行,查看命令行的编码:客户端右键——属性——
查看服务编码
解决:告诉服务器通过gbk编码解析发送SQL语句。语法:set 变量名=值
现在插入成功!
场景:查看插入的数据
原因:返回的编码是utf8,客户端用gbk去解析的。
解决:将返回的结果编码设置为gbk;
再次查询,成功
注意:我们一般只执行set names ‘字符编码’,因为这个SQL语句可以一次更改3个变量。
1.14 校对集
1.14.1 概念
在某种字符集下,字符和字符的比较关系。比如a和B的大小关系,如果区分大小写a>B,如果不区分大小写a<B。这种比较的关系是有校对集决定的。
校对集依赖于字符集,不同的字符集他们的比较规则也不一样,如果字符集发生更改,校对集也要重新定义。
不同的校对集对同一组字符比较结果不一致的。
1.14.2 定义校对集
语法:collate=校对集
定义两个表,校对集不一样
create table t1(
name char(1)
)charset=utf8 collate=utf8_general_ci;
create table t2(
name char(1)
)charset=utf8 collate=utf8_bin;
插入测试数据
insert into t1 values ('a'),('B');
insert into t2 values ('a'),('B');
通过排序查看结果
校对集名字规则
_bin:表示按二进制编码进行比较
_ci:不区分大小写
_cs:区分大小写
中文排序规则:按照汉字的拼音来排序
1.14.3 显示所有的字符集
1.14.4 显示所有的校对集