Mysql/MariaDB数据库入门
内容:
成都创新互联于2013年开始,先为青龙等服务建站,青龙等地企业,进行企业商务咨询服务。为青龙企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
1、数据库简介以及MySQL/mariadb背景介绍
2、数据库的一些名词
3、mysql的服务结构
4、mysql客户端的使用
5、数据类型
6、SQL语句介绍
7、mysql的事务机制
一、数据库简介以及mysql/mariadb背景介绍
数据可以存放在多种位置,如普通文件、专门的数据库中,而两者有什么区别,而为什么选择数据库存储?我们知道,假如数据存在普通文件中,当我们要查找其中的一个数据时,要把整个文件加载到内存中,再进行检索,这样速度慢不说,一旦文件较大,直接把内存撑爆了,而数据库的查询可以只加载符合条件的内容。
数据库常见的有三种模型:
网状模型
层次模型
关系模型
关系模型是一个二维关系:表
行:row
列:column
索引:数据结构,辅助完成数据查找的;
mysql是一个广泛使用的关系型数据库:
1、MySQL由瑞典MySQL AB 公司开发,目前属于 Oracle(被收购)
2、MySQL是目前最流行的关系型数据库管理系统,在 WEB 应用方面MySQL是最好的 RDBMS (Relational DatabaseManagement System,关系数据库管理系统) 应用软件之一。
3、MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
4、MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。
5、MySQL 软件采用了双授权政策:它分为社区版和商业版。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择MySQL作为网站数据库。由于其社区版的性能卓越,搭配 PHP 和 Apache(nginx) 可组成良好的开发环境
mysql与MariaDB的关系:
(1)因为mysql被sun公司给收购了,而sun公司又被oracle公司给收购了,而oracle是一个商业数据库公司,创始人担心MySQL会存在闭源风险,因此用mysql的源代码创立了MariaDB。
(2)MariaDB名字来源于创始人的三女儿,而MysDB则是源于创始人的大女儿。
(3)目前centos7的base源中MariaDB已经替代了mysql称为默认的数据库类型。
二、数据库的一些名词:
名词:
数据库:database
表:table
索引:index
视图:view
行:row
列:column
主键:primary key
外键:foreign key
唯一键:unique key
字符集:character
排序:collate
三、mysql的服务结构:
MySQL是单进程,多线程的工作方式
mysql是一个C/S的架构:
C:client
mysql:CLI交互式客户端程序
mysqldump:备份工具
mysqladmin:管理工具
S:server
可以监听三类套接字地址:
ipv4
ipv6
unix sock:只监听本机的客户端响应
其服务端的结构可以简单分为三个层次:
第一层,即最上一层,它们都是服务于C/S程序或者是这些程序所需要的 :接受用户请求,身份验证,安全性等等。
第二层,这是MySQL的核心部分。通常叫做 SQL Layer。在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的,包括权限判断, sql解析,行计划优化, query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层,如存储过程,触发器,视 图等。
第三层,包括了存储引擎。通常叫做StorEngine Layer ,也就是底层数据存取操作实现部分,由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏 了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事物,或者取出有特定主键的行。存储引擎不能解析SQL,互相之间也不能通信。仅仅是简单的响应服务器的请求。
四、mysql客户端的使用:
命令行交互式客户端程序:mysql
连接数据库:mysql [OPTIONS] [database]
常用选项:
-uUSERNAME
-hHOST
-p[PASSWORD]
-Ddb_name:连接并使用指定的数据库作为默认数据库
--socket=path, -S path:指定套接字方式
--port=port_num, -P port_num:指定端口
--execute=statement, -e statement:无需登录到mysql而是把一些执行的结果返回
命令:
客户端命令
mysql> help
\u db_name:设定默认数据库;
\q:退出客户端;
\d CHAR:自定义语句结束符,默认为分号;
\g:语句结束标记,将命令发往服务端运行;
\G:语句结束标记,将命令发往服务 端运行,行数据纵向显示;
\! SHELL_COMMAND:可以执行shell的命令
\s:连接状态及服务器运行状态
\. /path/to/some_sql_script:运行SQL脚本
...
服务端命令:发往服务器端执行的SQL语句
(1) 语句结束符,默认是分号(;)
(2) 建立了与某服务器有效通信连接;
五、数据类型:
表:由行和列组成,定义列时需要选定合适的数据类型,同时需要满足符合范式设计的要求;
字符型:
定义字符型:CHAR(#),BINARY(#)
变长字符型:VARCHAR(#) ,VARBINARY(#)
对象存储:TEXT,BLOB
内建:ENUM,SET
注意:所有字符型数据要使用引号;
数值型:
精确数值:INT
近似数值:FLOAT,DOUBLE
注意:不能使用引号;
日期时间型:
DATE,TIME,DATETIME,TIMESTAMP,YEAR
六、SQL语句:
DDL:数据定义语言,主要用于管理库组件,例如数据库、表、索引、视图、用户、存储过程、存储函数、触发器、……
CREATE,ALTER,DROP
DML:数据操纵语言,主要用于管理表中数据,实现数据CRUD操作
DCL:管理授权
获取帮助:
mysql> help KEYWORD
数据库管理:
创建:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
修改:
ALTER DATABASE
删除:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
表管理:
表创建:
CREATE TABLE [IF NOT EXISTS] tbl_name (create_defination) [table options]
create_defination:由逗号分隔的列表
字段定义:
colume_name COLUMN DEFINATION
约束:
PRIMARY KEY
UNIQUE KEY
FOREIGN KEY
CHECK(expr)
索引:
{INDEX|KEY}
{FULLTEXT|SPATIAL}
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[table options]
ENGINE [=] engine_name
……
查看数据库所支持所有存储引擎类型:
mysql> SHOW ENGINES;
查看表的属性信息:
SHOW TABLE STATUS [WHERE Name='tbl_name'][LIKE PATTERN]
修改:
ALTER TABLE tbl_name [alter_specification [, alter_specification] ...]
alter_specification:
(1) 表选项
table_options
(2)表组成
字段:
ADD col_name DATA_TYPE [FIRST|AFTER col_name]
DORP [COLUMN] col_name
CHANGE
MODIFY
索引:
ADD INDEX(col1, col2, ...)
DROP INDEX index_name;
键:
ADD {PRIMARY|UNIQUE|FOREIGN} key (col1, col2, ...)
DROP {PRIMARY|UNIQUE|FOREIGN} KEY key_name
删除:
DROP TABLE [IF EXISTS] tbl_name [, tbl_name]
索引管理:
创建:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
删除:
DROP INDEX index_name ON tbl_name
查看:
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
DML:INSERT, DELETE, UPDATE,SELECT
INSERT INTO:
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
SELECT:
(1) SELECT * FROM tbl_name;
(2) SELECT col1, col2, ... FROM tbl_name;
字段:column_name [AS Alias]
(3) SELECT col1, col2, ... FROM tbl_name WHERE CLUASE;
WHERE CLAUSE:过滤条件
col_name 操作符 value|col_name;
操作符:
>, <, >=, <=, =, !=
组合多个条件:
and, or, not
操作符(2):
BETWEEN ... AND ...
LIKE 'PATTERN':
通配符:
_:
%:
RLIKE 'PATTERN'
正则表达式模式;
IS NULL
IS NOT NULL
(4) SELECT col1, ... FROM tbl_name [WHERE CLAUSE] ORDER BY col1, col2, ... [ASC|DESC];
DELETE:
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
UPDATE:
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
演示:
A、DDL定义语言命令包含如下:
1、CREATE
2、ALTER
3、DROP
1、CREATE:
1.1:创建数据库
MariaDB [(none)]> SHOW DATABASES; #查看所有数据库(需要有指定权限) +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.01 sec) MariaDB [(none)]> CREATE DATABASE nihao; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | nihao | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
1.2、创建表
MariaDB [(none)]> SELECT DATABASE(); #内建函数,查看当前的数据库 +------------+ | DATABASE() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) MariaDB [(none)]> USE nihao; #切换默认的数据库 Database changed MariaDB [nihao]> CREATE TABLE users(id INT UNSIGNED NOT NULL PRIMARY KEY,name CHAR(50) NOT NULL,gender ENUM('F','M')); Query OK, 0 rows affected (0.31 sec) MariaDB [nihao]> SHOW TABLES; +-----------------+ | Tables_in_nihao | +-----------------+ | users | +-----------------+ 1 row in set (0.00 sec) MariaDB [nihao]> DESC users; #查看表的详细信息 +--------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | name | char(50) | NO | | NULL | | | gender | enum('F','M') | YES | | NULL | | +--------+------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) MariaDB [nihao]> SHOW TABLE STATUS FROM nihao\G #查看数据库的详细信息 *************************** 1. row *************************** Name: users Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 10485760 Auto_increment: NULL Create_time: 2016-10-14 11:22:12 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
2、ALTER 修改表
MariaDB [nihao]> ALTER TABLE users RENAME user; Query OK, 0 rows affected (0.29 sec) MariaDB [nihao]> SHOW TABLES; +-----------------+ | Tables_in_nihao | +-----------------+ | user | +-----------------+ 1 row in set (0.01 sec)
3、DROP
3.1删除表:
MariaDB [nihao]> SHOW TABLES; +-----------------+ | Tables_in_nihao | +-----------------+ | user | +-----------------+ 1 row in set (0.01 sec) MariaDB [nihao]> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | nihao | +------------+ 1 row in set (0.00 sec) MariaDB [nihao]> SHOW TABLES; +-----------------+ | Tables_in_nihao | +-----------------+ | user | +-----------------+ 1 row in set (0.00 sec) MariaDB [nihao]> DROP TABLE user; #删除表 Query OK, 0 rows affected (0.01 sec)
3.2、删除数据库:
MariaDB [nihao]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | nihao | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [nihao]> DROP DATABASE nihao; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
B、DML操纵语言命令如下
1、INSERT
2、DELETE
3、SELECT
4、UPDATE
1、INSERT 插入数据
MariaDB [nihao]> INSERT INTO users(id,name,gender) VALUES (1,'tom','M'),(2,'hill','M'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [nihao]> SELECT * FROM users; +----+------+--------+ | id | name | gender | +----+------+--------+ | 1 | tom | M | | 2 | hill | M | +----+------+--------+ 2 rows in set (0.00 sec)
2、DELETE 删除数据
MariaDB [nihao]> DELETE FROM users WHERE name='tom'; Query OK, 1 row affected (0.00 sec) MariaDB [nihao]> SELECT * FROM users; +----+------+--------+ | id | name | gender | +----+------+--------+ | 2 | hill | M | +----+------+--------+ 1 row in set (0.00 sec) MariaDB [nihao]> INSERT INTO users(id,name,gender) VALUES (3,'nihao','M'),(4,'herry','M'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [nihao]> SELECT * FROM users; +----+-------+--------+ | id | name | gender | +----+-------+--------+ | 2 | hill | M | | 3 | nihao | M | | 4 | herry | M | +----+-------+--------+ 3 rows in set (0.00 sec) MariaDB [nihao]> DELETE FROM users; Query OK, 3 rows affected (0.00 sec) MariaDB [nihao]> SELECT * FROM users; Empty set (0.00 sec)
3、SELECT 查看数据
MariaDB [nihao]> SELECT * FROM users; #没有指定条件 +----+-------+--------+ | id | name | gender | +----+-------+--------+ | 1 | nihao | M | | 2 | herry | M | | 3 | hill | M | | 4 | tom | M | +----+-------+--------+ 4 rows in set (0.00 sec) MariaDB [nihao]> SELECT * FROM users WHERE id = 2 #指定查询的条件 -> ; +----+-------+--------+ | id | name | gender | +----+-------+--------+ | 2 | herry | M | +----+-------+--------+ 1 row in set (0.00 sec) MariaDB [nihao]> SELECT * FROM users WHERE id > 2 -> ; +----+------+--------+ | id | name | gender | +----+------+--------+ | 3 | hill | M | | 4 | tom | M | +----+------+--------+ 2 rows in set (0.00 sec) MariaDB [nihao]> SELECT * FROM users WHERE name LIKE '%l'; +----+------+--------+ | id | name | gender | +----+------+--------+ | 3 | hill | M | +----+------+--------+ 1 row in set (0.00 sec)
4、UPDATE 更改数据
MariaDB [nihao]> UPDATE users SET id = 10 WHERE name = 'hill'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [nihao]> SELECT * FROM users ; +----+-------+--------+ | id | name | gender | +----+-------+--------+ | 1 | nihao | M | | 2 | herry | M | | 4 | tom | M | | 10 | hill | M | +----+-------+--------+ 4 rows in set (0.00 sec)
C、DCL控制语言命令如下
1、GRANT;当用户不存在时还会自动创建用户,创建完用户后需要重新刷新授权表
2、REVOKE:
1、GRANT
MariaDB [nihao]> GRANT ALL ON *.* TO 'hill'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.29 sec) MariaDB [nihao]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) MariaDB [nihao]> GRANT ALL ON *.* TO 'hill'@'127.0.0.1' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) MariaDB [nihao]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) MariaDB [nihao]> EXIT Bye [root@localhost ~]# mysql -uhill -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 19 Server version: 5.5.46-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [nihao]> INSERT INTO users (id,name,gender) VALUES (5,'alex','M'); Query OK, 1 row affected (0.01 sec) MariaDB [nihao]> SELECT * FROM users; #授权的用户可登录以及插入数据 +----+-------+--------+ | id | name | gender | +----+-------+--------+ | 1 | nihao | M | | 2 | herry | M | | 4 | tom | M | | 5 | alex | M | | 10 | hill | M | +----+-------+--------+ 5 rows in set (0.00 sec)
2、REVOKE
MariaDB [(none)]> REVOKE INSERT ON *.* FROM 'hill'@'127.0.0.1' ; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> REVOKE INSERT ON *.* FROM 'hill'@'localhost' ; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> FLUSH PRIVILEGES -> ; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> EXIT Bye [root@localhost ~]# mysql -uhill -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 21 Server version: 5.5.46-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> USE NIHAO ERROR 1049 (42000): Unknown database 'NIHAO' MariaDB [(none)]> USE nihao Database changed MariaDB [nihao]> INSERT INTO users (id,name,gender) VALUES (6,'hello','M'); ERROR 1142 (42000): INSERT command denied to user 'hill'@'localhost' for table 'users'
总结(源自网络)
七、mysql的事务机制
1、什么是事务:
事务:transaction
一个事务是一个连续的一组数据库操作,就好像它是一个单一的工作单元进行。换言之,永远不会是完整的事务,除非该组内的每个单独的操作是成功的。如果在事务的任何操作失败,则整个事务将失败。
2、为什么需要事务功能:
以一个经典的例子来举例事务的必要性:
假设一个银行的数据库有两张表:支票(checking)和储蓄(savings)。现在如果要从用户Jane的支票账户转移转移200美元到她的储蓄账户。
那么至少需要以下3个步骤:
(1)检查支票账户的余额是否大于200美元。
(2)如果为真则,从支票账户减去200美元。
(3)在储蓄账户余额中增加200美元。 显而易见,这3步必须打包在一个事务中,任何一个步骤失败,必须全部回滚。 假设,在执行到第3步时服务器崩溃了,这时会发生什么情况?——用户很可能会损失200美元。又或者在执行第二步和第三步之间时,另外一个进程要删除支票账户的所有余额,这时又会发生什么情况?——银行很可能白白给用户200美元。
3、一个事务的必须要同时支持ACID四个特性
ACID表示:原子性(atomicity)、一致性(consistency)、隔离性(Isolation)、持久性(durability)
A:原子性(atomicity)整个事务中的所有操作要么全部成功执行,要么全部失败后回滚;
C:一致性(consistency)数据库总是从一个一致性状态转换为另一个一致性状态;
I:隔离性(Isolation)一个事务所做出的操作在提交之前,是不能为其它所见;隔离有多种隔离级别;
D:持久性(durability)一旦事务提交,其所做的修改会永久保存于数据库中;
4、mysql的事务:
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关
MyISAM:不支持事务,用于只读程序提高性能
InnoDB:支持ACID事务、行级锁、并发
Berkeley DB:支持事务
在MySQL中,事务开始使用COMMIT或ROLLBACK语句开始工作和结束。开始和结束语句的SQL命令之间形成了大量的事务。
COMMIT & ROLLBACK:
这两个关键字提交和回滚主要用于MySQL的事务。
当一个成功的事务完成后,发出COMMIT命令应使所有参与表的更改才会生效。
如果发生故障时,应发出一个ROLLBACK命令返回的事务中引用的每一个表到以前的状态。
可以控制的事务行为称为AUTOCOMMIT设置会话变量。如果AUTOCOMMIT设置为1(默认值),然后每一个SQL语句(在事务与否)被认为是一个完整的事务,并承诺在默认情况下,当它完成。 AUTOCOMMIT设置为0时,发出SET AUTOCOMMIT =0命令,在随后的一系列语句的作用就像一个事务,直到一个明确的COMMIT语句时,没有活动的提交。
可以通过使用mysql_query()函数在PHP中执行这些SQL命令。
OK,更多文章请关注我的博客。
当前文章:Mysql/MariaDB数据库入门
文章路径:http://scyanting.com/article/igdodi.html