Mysql数据库理论基础二
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-privilegesmysqld重读授权表，等同于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 command ] ...
eg: mysqladmin -uroot -p password 'NEW_PASS'
# mysqladmin create hellodb;
# 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 #修改配置文档，使得命令补全永久有效；
#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'> 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: #mysqladmin -uroot -h10.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'> #在数据库DB_NAME中的表TB_NAME中，用户USERNAME@'HOST'授予pri1，pri2等权限也可以设定密码IDENTIFIED BY 'PASSWORD'，若此用户不存在则直接新建该用户并授权 3.3.2REVOKE : 取消用户权限
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---
页:
[1]