数据库
- 数据库发展史 :
| 阶段 | header 2 |
|---|---|
| 萌芽阶段 | 文件系统 : 使用磁盘文件来存储数据 |
| 初级阶段 | 第一代数据库 : 网状模型、层次模型的数据库 |
| 中级阶段 | 第二代数据库 : 关系型数据库和结构化查询语言 |
| 高级阶段 | 新一代数据库 : “关系-对象”型数据库 |
- 关系型数据库名词解释 :
| 名词 | 解释 |
|---|---|
| 关系 | 关系就是二维表。表中的行、列次序并不重要 |
| 行row | 表中的每一行，又称为一条记录 |
| 列column | 表中的每一列，称为属性，字段 |
| 主键（Primary key） | 用于惟一确定一个记录的字段 |
| 域domain | 属性的取值范围，如，性别只能是‘男’和‘女’两个 |
- 事务transaction：多个操作被当作一个整体对待
- 具有如下四个特性 ACID:
- A: 原子性
- C：一致性
- I: 隔离性
- D：持久性
- 简易数据规划流程 :
- 第一阶段：
- 收集数据，得到字段
- 收集必要且完整的数据项
- 转换成数据表的字段
- 第二阶段：
- 把字段分类，归入表，建立表的关联
- 关联：表和表间的关系
- 分割数据表并建立关联的优点
- 节省空间
- 减少输入错误
- 方便数据修改
- 第三阶段：
- 规范化数据库
- 第一阶段：
SQL概念 :
- SQL: Structure Query Language 结构化查询语言
- 数据存储协议：应用层协议，C/S
- S：server, 监听于套接字，接收并处理客户端的应用请求
- C：Client
- 客户端程序接口 : CLI/GUI
- 应用编程接口
- ODBC：Open Database Connectivity
- JDBC：Java Data Base Connectivity
MySQL
- RPM包安装MySQL
- CentOS 7：安装光盘直接提供
- mariadb-server 服务器包
- mariadb 客户端工具包
- 提高安全性 : mysql_secure_installation
- 设置数据库管理员root口令
- 禁止root远程登录
- 删除anonymous用户帐号
- 删除test数据库
MySQL
- 客户端程序：
- mysql: 交互式的CLI工具
- mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中
- mysqladmin：基于mysql协议管理mysqld
- mysqlimport：数据导入工具
- MyISAM存储引擎的管理工具：
- myisamchk：检查MyISAM库
- myisampack：打包MyISAM表，只读
服务器端程序
- mysqld_safe
- mysqld
- mysqld_multi：多实例
- 示例：mysqld_multi –example
mysql用户账号由两部分组成:’USERNAME’@’HOST’
- HOST限制此用户可通过哪些远程主机连接mysql服务器
- 支持使用通配符：
- % 匹配任意长度的任意字符
- 172.16.0.0/255.255.0.0 或 172.16.%.%
- _ 匹配任意单个字符
mysql使用模式：
- 交互式模式：
- 客户端命令：
- \h, help
- \u，use
- \s，status
- !，system
- 服务器端命令：
- SQL, 需要语句结束符;
- 客户端命令：
脚本模式,与数据重定向经常配合使用：
- mysql –uUSERNAME -pPASSWORD < /path/somefile.sql
- mysql> source /path/from/somefile.sql
mysql客户端可用选项 ：
| 选项 | 解释 |
|---|---|
| -A, –no-auto-rehash | 禁止补全 |
| -u, –user= | 用户名,默认为root |
| -h, –host= | 服务器主机,默认为localhost |
| -p, –passowrd= | 用户密码,建议使用-p,默认为空密码 |
| -P, –port= | 服务器端口 |
| -S, –socket= | 指定连接socket文件路径 |
| -D, –database= | 指定默认数据库 |
| -C, –compress | 启用压缩 |
| -e | “SQL“ 执行SQL命令 |
| -V, –version | 显示版本 |
| -v –verbose | 显示详细信息 |
| –print-defaults | 获取程序默认使用的配置 |
- 服务器监听的两种socket地址：
1. ip socket: 监听在tcp的3306端口，支持远程通信
2. unix sock: 监听在sock文件上，仅支持本机通信
如：/var/lib/mysql/mysql.sock)
- host为localhost或127.0.0.1时,自动使用unix sock
服务器端配置 :
- 命令行选项：
- 配置文件：类ini格式 :
- 集中式的配置，能够为mysql的各应用程序提供配置信息
- [mysqld]
- [mysqld_safe]
- [mysqld_multi]
- [mysql]
- [mysqldump]
- [server]
- [client]
- 格式：parameter = value
- 说明：
- _和- 相同
- 1，ON，TRUE意义相同
- 0，OFF，FALSE意义相同
- 配置文件优先级:后面覆盖前面的配置文件，顺序如下：
- /etc/my.cnf Global选项
- /etc/mysql/my.cnf Global选项
- SYSCONFDIR/my.cnf Global选项
- $MYSQL_HOME/my.cnf Server-specific 选项
- –defaults-extra-file= path
- ~/.my.cnf User-specific 选项
- [mysqld]
- skip-networking=1 关闭网络连接，只侦听本地客户端， 所有和服务器的交互都通过一个socket实现，socket的配置存放在/var/lib/mysql/mysql.sock） 可在/etc/my.cnf修改
- prompt=(\u@\h) [\d]>\_ mysql交互界面显示当前用户 主机与数据库
关系型数据库的常见组件 :
- 据库：database
- 表：table
- 行：row
- 列：column
- 索引：index
- 视图：view
- 用户：user
- 权限：privilege
- 存储过程：procedure，无返回值
- 存储函数：function，有返回值
- 触发器：trigger
- 事件调度器：event scheduler，任务计划
数据库操作
- 创建数据库：
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';
CHARACTER SET 'character set name'
COLLATE 'collate name'- 删除数据库 :
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';- 查看支持所有字符集：SHOW CHARACTER SET;
- 查看支持所有排序规则：SHOW COLLATION;
- 获取命令使用帮助：mysql> HELP KEYWORD;
- 查看数据库列表：mysql> SHOW DATABASES;
- 创建表:
CREATE TABLE students #创建students表
(id int UNSIGNED NOT NULL PRIMARY KEY,#id列,无符号非空int型,主键
name VARCHAR（20）NOT NULL,#name列,可变长度字符串(最大20),非空
age tinyint UNSIGNED);#age列,无符号整数0~255
DESC students;#描述students表头信息.
CREATE TABLE students2
(id int UNSIGNED NOT NULL,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
PRIMARY KEY(id,name));#以id和name作主键数据库操作
| 目的 | SQL语句 |
|---|---|
| 创建数据库 | CREATE DATABASE|SCHEMA [IF NOT EXISTS] ‘DB_NAME’; |
| 设置字符编码 | CHARACTER SET ‘character set name’; |
| 设置排序规则 | COLLATE ‘collate name’; |
| 删除数据库 | DROP DATABASE|SCHEMA [IF EXISTS] ‘DB_NAME’; |
| 查看支持所有字符集 | SHOW CHARACTER SET; |
| 查看支持所有排序规则 | SHOW COLLATION; |
| 获取命令使用帮助 | HELP KEYWORD; |
| 查看数据库 | SHOW DATABASES; |
| 查看所有的引擎 | SHOW ENGINES; |
| 查看表 | SHOW TABLES [FROM db_name]; |
| 查看表结构 | DESC [db_name.]tb_name; |
| 复制表结构,不含数据 | CREATE TABLE new_tbl_name LIKE old_tbl_name; |
| 删除表 | DROP TABLE [IF EXISTS] tb_name; |
| 查看表创建命令 | SHOW CREATE TABLE tbl_name; |
| 查看表状态 | SHOW TABLE STATUS LIKE ‘tbl_name’; |
| 查看库中所有表状态 | SHOW TABLE STATUS FROM db_name; |
| 重命名表 | ALTER TABLE students RENAME s1; |
| s1表name列后添加phone列,并指明类型 | ALTER TABLE s1 ADD phone varchar(11) AFTER name; |
| 设定s1表phone列为int型 | ALTER TABLE s1 MODIFY phone int; |
| s1表phone列更名为mobile,并设定为char型 | ALTER TABLE s1 CHANGE COLUMN phone mobile char(11); |
| 删除s1表的mobile列 | ALTER TABLE s1 DROP COLUMN mobile; |
| studenst表添加gender列,枚举类型:可选值为’m’或’f’ | ALTER TABLE students ADD gender ENUM(‘m’,’f’); |
| students表id列更名为sid,无符号非空主键int型 | ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY; |
| students表以name列数据增加唯一键 | ALTER TABLE students ADD UNIQUE KEY(name); |
| students表以age列数据增加索引 | ALTER TABLE students ADD INDEX(age); |
| 查看students表的索引 | SHOW INDEXES FROM students; |
| studenst表删除age列 | ALTER TABLE students DROP age; |
MySQL用户和权限管理
- 元数据数据库：mysql
- 系统授权表：db, host, user,columns_priv, tables_priv, procs_priv, proxies_priv
- 用户账号：
- ’USERNAME’@’HOST’：
- @’HOST’:
- 主机名:IP地址或Network;
- 通配符：%, _: 172.16.%.%
- 创建用户:CREATE USER ‘USERNAME’@’HOST’ [IDENTIFIED BY ‘password’];
- 用户重命名:RENAME USER old_user_name TO new_user_name;
- 删除用户:DROP USER ‘USERNAME’@’HOST’;
删除默认的空用户:DROP USER ”@’localhost’;
修改密码：
- mysql>SET PASSWORD FOR ‘user’@’host’ = PASSWORD(‘password’);
- mysql>UPDATE mysql.user SET password=PASSWORD(‘password’) WHERE clause;
- 此方法需要执行下面指令才能生效：
- mysql> FLUSH PRIVILEGES;
- 执行Shell命令: mysqladmin -u root –poldpass password ‘newpass’
- 忘记管理员密码的解决办法：
- 使用如下选项启动mysqld进程：–skip-grant-tables –skip-networking
- 使用UPDATE命令修改管理员密码
- 关闭mysqld进程，移除上述两个选项，重启mysqld
授权
GRANT priv_type [(column_list)],...
ON [object_type] priv_level
TO 'user'@'host'
[IDENTIFIED BY 'password']
[WITH GRANT OPTION];- priv_type: ALL [PRIVILEGES]
- object_type:TABLE | FUNCTION | PROCEDURE
- priv_level:
- 或 .* (所有库)
- db_name.*
- db_name.tbl_name
- tbl_name(当前库的表)
- db_name.routine_name(指定库的函数,存储过程,触发器)
- with_option: GRANT OPTION
- MAX_QUERIES_PER_HOUR count
- MAX_UPDATES_PER_HOUR count
- MAX_CONNECTIONS_PER_HOUR count
- MAX_USER_CONNECTIONS count
- 示例：
GRANT SELECT (col1), INSERT (col1,col2)
ON mydb.mytbl
TO'someuser'@'somehost';回收授权：
REVOKE priv_type [(column_list)] [, priv_type
[(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...- 示例：REVOKE DELETE ON testdb.* FROM ‘testuser’@’%’
MySQL体系结构
存储引擎
MyISAM引擎特点：
- 不支持事务
- 表级锁定
- 读写相互阻塞，写入不能读，读时不能写
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 读取数据较快，占用资源较少
- 不支持MVCC（多版本并发控制机制）高并发
- 崩溃恢复性较差
- MySQL5.5.5前默认的数据库引擎
InnoDB引擎特点：
- 支持事务，适合处理大量短期事务
- 行级锁
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库引擎
- 关于引擎的常用命令
| 目的 | SQL语句 |
|---|---|
| 查看mysql支持的存储引擎 | show engines; |
| 查看当前默认的存储引擎 | show variables like ‘%storage_engine%’; |
| 查看库中所有表使用的存储引擎 | Show table status from db_name; |
| 查看库中指定表的存储引擎 | show table status like ’ tb_name ‘; |
| 查看库中指定表的存储引擎 | show create table tb_name; |
| 设置表的存储引擎 | CREATE TABLE tb_name(… ) ENGINE=InnoDB; |
| 设置表的存储引擎 | ALTER TABLE tb_name ENGINE=InnoDB; |
- 设置默认的存储引擎：
vim /etc/my.conf
[mysqld]
default_storage_engine= InnoDB;
扫一扫
1573
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
