Mysql数据库理论基础二
一、简介
由MySQL AB公司开发,是最流行的开放源码SQL数据库管理系统,主要特点:
1、是一种数据库管理系统
2、是一种关联数据库管理系统
3、是一种开放源码软件,且有大量可用的共享MySQL软件
4、MySQL数据库服务器具有快速、可靠和易于使用的特点
5、MySQL服务器工作在客户端/服务器模式下,或嵌入式系统中
InnoDB存储引擎将InnoDB表保存在一个表空间内,该表空间可由数个文件创建。这样,表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区,从而使得很大的表成为可能。表空间的最大容量为64TB。
二、MySQL相关概念:MySQL是单进程多线程接收应用的请求
2.1. SQL/MySQL
1.事务,隔离,并发控制,锁
2.用户和权限
3.监控
STATUS
4.索引类型:查询
VARIABLES
5.备份和恢复
6.复制功能
7.集群
2.2. DML:数据操作语言
INSERT:插入
DELETE:删除
SELECT:挑选,选择,查询
UPDATE:更新,修改
2.3. DDL:数据定义语言
CREATE:创建
DROP:删除
ALTER:修改
2.4. DCL:数据控制语言
GRANT:授权
REVOKE:取消权限
2.5. MySQL插件式存储引擎:
1.MyISAM: 不支持事务处理,应用于查询比较多,但修改比较少的数据存储仓库
该引擎中,每个表有三个文件:
.frm: 表结构文件
.MYD: 表数据文件
.MYI: 表索引文件
2.InnoDB:支持事务处理,查询没有那么快,应用于经常需要修改的,比如论坛数据存储
该引擎所以表共享一个表空间文件;
建议:每表一个独立的表空间文件;
.frm:表结构文件
.ibd:表空间(包含表数据和表索引文件)
----------------------------------------------------------------------
为mysql打开innodb每表建立一个独立表空间文件的设置:
编辑配置文件/etc/my.cnf添加一行后重启mysql服务:
#vim /etc/my.cnf
....
innoda_file_per_table = 1 #1为启用,0为禁用
----------------------------------------------------------------------
2.6. MYSQL数据库功能:
1、数据库创建、删除
2、创建表、删除表、修改表
3、索引的创建、删除
4、用户和权限
5、数据增、删、改
6、查询
2.7. MySQL二进制程序:mysql数据库的database存放路径为/var/lib/mysql目录中
2.8. 客户端命令:mysql
-u USERNAME #以那个用户连接mysql (不指定默认用户为root)
-p #指定输入用户密码 (不指定默认是空密码)
-h MYSQL_SERVER #指定mysql服务器主机 (不指定默认为localhost本机)
-D DATA #连接mysql的时候指定默认的数据库
eg1: mysql -D mydb -uroot -p -h 10.109.134.249 -D mydb
-uroot 指定用户 -p:输入用户密码 -h:指定连接那台mysql服务器(ip地址)
-D: 指定登录mysql默认的数据库(相当于在客户端内输入USE mydb;命令)
MySQL客户端:
交互式模式:一步一步的手动输入执行
批处理模式:执行mysql脚本,批处理执行
MySQL客户端工具:
mysql
mysqldump:常见的mysql备份工具
mysqladmin:mysql管理工具
mysqladmin extended-status 显示状态变量
mysqladmin status
--sleep N 显示频率
--count N 显示多个状态
mysqladmin variables 显示服务器变量;
mysqladmin flush-privileges mysqld重读授权表,等同于reload;
mysqladmin flush-status 重置大多数服务器状态变量;
mysqladmin flush-logs 二进制和中继日志滚动;
mysqladmin flush-hosts 刷新主机列表;
mysqladmin refresh 刷新日志和主机列表,相当于同时执行flush-hosts和flush-logs;
mysqladmin shutdown 关闭mysql服务器进程;
mysqladmin version 服务器版本及当前状态信息;
mysqladmin start-slave 启动复制,启动从服务器复制线程;
mysqladmin stop-slave 关闭复制,关闭从服务器复制线程。
格式:mysqladmin [option] command [arg] [command [arg]] ...
eg: mysqladmin -uroot -p password 'NEW_PASS'
[root@lamp ~]# mysqladmin create hellodb;
[root@lamp ~]# mysql
.........
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | #通过mysqladmin可以直接管理mysql
...
+--------------------+
7 rows in set (0.00 sec)
mysqlcheck:检查工具
mysqlimport:接口工具
2.9. MySQL非客户端工具:
myisamchk
myisampack
2.10. 交互式模式中的命令类别:
客户端命令:
? (\?) Synonym for `help'. 获取帮助
quit (\q) Quit mysql. 退出mysql客户端
clear (\c) Clear the current input statement.
\c:提前终止语句执行,但是必须在结束符前,否则该条指令依旧执行。
go (\g) Send command to mysql server.
\g:无论语句结束符是什么,直接将此语句送至服务器端执行;
ego (\G) Send command to mysql server, display result vertically.
\G:无论语句结束符是什么,直接将此语句送至服务器端执行,而且结果以竖排显示;
system (\!) Execute a system shell command.
\! COMMAND:执行shell命令;
warnings (\W) Show warnings after every statement.
\W:语句执行结束后显示警告信息;
服务器端命令:
必须使用语句结束符,默认结束符为分号;SHOW DATABASES; #查看数据库
delimiter (\d) Set statement delimiter.
\d:定义语句结束符 (默认语句结束符为分号,可以设定为其他)
help KEYWORD: 获取关键字命令的帮助信息;
eg: mysql>help SELECT; #获取SELECT命令的相关帮助信息;
--------------------------------------------------------------------------
在mysql客户端中,名称补全命令: rehash
# vim /etc/my.cnf #修改配置文档,使得命令补全永久有效;
[mysql]
#no-auto-rehash
auto-rehash = 1
# Remove the next comment character if you are not familiar with SQL
#safe-updates
mysql> rehash #若不改配制,用命令rehash可tab键补全命令,但重新打开mysql将失效
mysql> u #按tab键就有很多以U开头的命令出来
unlock tables user.Execute_priv user.Shutdown_priv
updates user.File_priv user.Super_priv
...
--------------------------------------------------------------------------
三、MySQL关系数据库对象:
库、表、索引、视图、约束、存储过程、存储函数、触发器、游标、用户、权限、事务。
数据库最基本的对象-->表:行(row),列(field,column)
服务端命名:mysqld
Mysql常用命令不区分大小写,但是需保持一致(都大写或者都小写):
3.1. DDL:定义数据对象 (Data Definition Language)
3.1.1关键字:CREATE(创建)、ALTER(修改)、DROP(删除)
主要作用的范围:数据库,表,表的字段
创建用户:
CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD'; #可以不指定密码
USERNAME:用户名 HOST:主机 IDENTIFIED:指定密码 BY‘密码’
删除用户:
DROP USER 'USERNAME'@'HOST'; 删除用户(需具体指定删除那个用户)
--------------------------------------------------------------------------
为用户设定密码方法:(建议用1和3这两种)
*1、mysql>SET PASSWORD FOR 'USERNAME'@'HOST'=PASSWORD('PASSWORD')
eg. SET PASSWORD FOR 'root'@'localhost'=PASSWORD('123456')
#设定root@localhost用户名密码为123456
2、直接在linux下输入,不需要进入mysql客户端输入(易导致修改同user不同host的密码):
# mysqladmin -uUSERNAME -hHOST -p password 'password'
eg: [root@johntest ~]#mysqladmin -uroot -h20.109.13.9 -p password '123456'
*3、直接修改表中的内容:
mysql>UPDATE user SET password=PASSWORD('password') WHERE USER='root' AND Host='10.109.13.9';
#AND与关系(需同时满足) OR或关系(两者满足一个即可) NOT非关系(取反)
--------------------------------------------------------------------------
3.1.2. HOST可以使用的格式:
IP , HOSTNAME(主机名),NETWORK(网络地址),通配符(需用'通配符'引号)
通配符包含:
_:匹配任意单个字符,172.16.0._
%:匹配任意字符
3.1.3. 新建数据库:
创建数据库:CREATE DATABASE IF NOT EXISTS db_name; 同名数据库不存在时创建
创建表: CREATE TABLE tb_name(col1,col2,...); 创建tb_name表 col表示字段
查看库中的表: SHOW TABLES FROM db_name 查看db_name数据库中的表
查看表的结构: DESC tb_name 查看tb_name表的结构
删除表:DROP TABLE IF EXISTS tb_name 如果表存在删除tb_name表
3.1.4. 修改表:ALTER TABLE tb_name
MODIFY: 修改某字段 (变更属性,权限)
CHANGE: 改变某字段 (变更字段名称)
ADD: 增加某字段
DROP : 删除某字段
eg: ALTER TABLE students ADD course VARCHAR(100); #新增空间长度为100course字段
3.2. DML:数据操纵语言 (Data Manipulation Language)
关键字: INSERT (插入)、 DELETE (删除)、 UPDATE (更新、修改)
主要作用范围:针对表中字段的值进行操作
插入数据: col表示字段
INSERT INTO tb_name (col1,col2,...) VALUES|VALUE ('STRING',NUM,...), ('STRING',NUM,...);
更新、修改数据:
WHERE表示修改那个具体数据,并不是把column整个这个字段的值修改为value.
UPDATE tb_name SET column=value WHERE ..... ;
eg: UPDATE students SET Course='Jiuyinzhenjing' WHERE Name='LHC'; #表示修改表students中Name值为LHC的Course字段的值变为‘Jiuyinzhenjing’
3.3. DCL:数据库控制语言 (Data Control Language)
关键字: GRANT (允许、授权)、 REVOKE (取消权限)
ALL PRIVILEGES: 表示所有权限
3.3.1 GRANT: 给用户授权
eg: GRANT pri1,pri2,... ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD'
#在数据库DB_NAME中的表TB_NAME中,用户USERNAME@'HOST'授予pri1,pri2等权限也可以设定密码IDENTIFIED BY 'PASSWORD',若此用户不存在则直接新建该用户并授权
3.3.2 REVOKE : 取消用户权限
eg: REVOKE pri1,pri2,... ON DB_NAME.TB_NAME FROM 'USERNAME'@'HOST';
#在数据库DB_NAME中的表TB_NAME中,取消用户'USERNAME'@'HOST'的pri1,pri2等权限
3.3.3 查看用户的授权: SHOW GRANTS FOR 'USERNAME'@'HOST';
3.3.4 刷新用户权限列表: FLUSH PRIVILEGES;
3.4. DQL:数据库查询语言 ( Data Query Language )
关键字:SELECT (挑选,选择)
SELECT 字段 FROM tb_name WHERE CONDITION;
*:表示所有字段
WHERE:没有条件表示显示所有行;
eg: SELECT Name,Course FROM students WHERE Gender='M';
#表示只选择表students中的Gender字段值为M的Name和Course字段的信息
---end---