MariaDB基础
关系型数据库的基础概念：
1)数据类型:作用：存储格式、数据范围、所能参与的运算、排序方式 字符型： 定长字符型：CHAR(#)，BIARNY(#) 变长字符型：VARCHAR(#)，VARBINARY(#) 数值型： 精确数值型： 近似数值型： 日期时间型： DATE TIME 2)数据库的设计范式： 第一范式：字段是原子性的； 第二范式：存在可用的主键； 第三范式：任何字段都不应该依赖于其它表的非主键字段； 3)数据约束：向数据表中插入数据时要遵守的限制规则 主键：primary key，表上一个或多个字段的组合，填入主键字段中的数据，必须不同于已经存在的其它行的相同字段上的数据，而且也不能为空；一个表只能存一个主键； 惟一键：unique key，表上一个或多个字段的组合，填入其中字段中的数据，必须不同于已经存在的其它行的相同字段上的数据，但可以为空；一个表可以有多个惟键； 外键：foreign key，一个表中的外键字段中所能够插入的数据取值范围，取决于引用的另一个表上主键字段上的已经存在数据集合；
MariaDB的基础应用：
MariaDB程序组成：C/S结构
C：Client:客户端程序 mysql mysqladmin mysqldump mysqlbinlog S：Server:服务端程序 mysqld mysqld_safe mysqld_multi 管理工具程序： myisampack myisamchk ... 套接字地址：mysql支持的如下三类 IPv4:PORT IPv6:PORT Unix_Sock：/var/lib/mysql/mysql.sock, /tmp/mysql.sock
命令行客户端程序mysql
mysql [OPTIONS] [database] 常用选项： -uUSERNAME -hHOST -pPASSWORD -Ddb_name -S sock_file_path -P port -e 'STATEMENT' 不用进入mysql交互式界面在shell命令行进行操作 示例:]# mysql -e 'CREATE DATABASE IF NOT EXISTS mydb; SHOW DATABASES;' mysql客户端的另一种执行方式;sql脚本;比如写一个包含了sql语句的脚本;如下写一个文件/tmp/test.sql,然后利用重定向的方式交给mysql执行 CREATE DATABASE alidb; CREATE TABLE alidb.tbl1 (id TINTINT); INSERT INTO alidb.tbl1 VALUES (1),(99); ]# mysql < /tmp/test.sql 客户端命令:在客户端执行； mysql> help \?：获取可用的命令帮助； \q：退出客户端程序； \d CHAR：自定义语句结束符； \g：语句结束标记； \G：语句结束标记，竖排显示结果； \! SHELL_CMD：运行shell命令； \s：当前连接及服务器相关的状态信息； \.:执行一个sql脚本 服务端命令：SQL语句，发往服务端运行，并取回结果；需要显式的语句结束符； DDL：数据定义语言，主要用于管理数据库组件，例如数据库、表、索引、视图、触发器、事件调度器、存储过程、存储函数； CREATE， ALTER， DROP DML：数据操纵语言，CRUD操作，主要用于操作表中的数据； INSERT，DELETE，UPDATE，SELECT DCL：数据控制语言 GRANT(授权), REVOKE(回收权限)
SQL语句：
获取帮助：
mysql> help KEYWORD
mysql> help contents 获取命令分类
数据库管理：
创建:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
修改:
ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name
删除:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
相关命令：
SHOW CHARACTER SET :查看有哪些字符集
SHOW COLLATION :显示排序规则
SHOW CREATE DATABASE db_name :显示数据库是使用什么语句创建的
表管理：
表创建：
CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options]
create_definition:创建定义由逗号分隔的列表,列表中包含了如下三类定义;
字段定义：
column_name column_defination
data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
约束定义：
PRIMARY KEY(col1[,col2, ....])
UNIQUE KEY
FOREIGN KEY
CHECK(expr)
索引定义：
{INDEX|KEY}
{FULLTEXT|SPATIAL}
字段是必须的,索引和约束是可选的
设定表选项
table_option:
ENGINE [=] engine_name 设定存储引擎类型
查看数据库支持的存储引擎种类：
SHOW ENGINES;
查看表状态信息：
SHOW TABLE STATUS [WHERE CLAUSE] [LIKE CLAUSE]
表修改：
ALTER TABLE tbl_name [alter_specification [, alter_specification] ...]
alter_specification:
(1) 表选项
ENGINE=engine_name
(2) 表定义
(a) 字段
ADD :加字段
DROP :删字段
CHANGE :改字段 大改
MODIFY :改字段 小改
(b) 键和索引
ADD {PRIMARY|UNIQUE|FOREIGN} key (col1, col2, ...) 增加键
ADD INDEX(col1, col2, ...) 增加索引
DROP {PRIMARY|UNIQUE|FOREIGN} KEY key_name； 删除键
DROP INDEX index_name；删除索引
查看表上的索引信息：
SHOW INDEXES FROM tbl_name；
表删除：
DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...
查看表创建语句：
SHOW CREATE TABLE 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
示例:定义一个表
MariaDB [(none)]> CREATE DATABASE mydb;
MariaDB [(none)]> use mydb;
MariaDB [mydb]> CREATE TABLE tbl1(id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, name CHAR(30) NOT NULL, age TINYINT UNSIGNED, gender ENUM('F','M'),UNIQUE KEY(name,gender),INDEX(name));
命令解析:
CREATE DATABASE mydb; 创建一个数据库
MariaDB [(none)]> use mydb;进入数据库
CREATE TABLE tbl1:创建一个名为tbl1的表
(…):使用小括号扩起来给定一个字段定义,以学生信息为例括号里边的内容如下;
id:第一个字段叫id
SMALLINT:假设学生数量不超过六万个,使用SMALLINT表示小整型
UNSIGEND:假设学生的学号不会有负数出现,使用UNSIGNED;UNSIGNED是SMALLINT的修饰符,必须要紧跟在SMALLINT的背后,中间不能隔任何字符
NOT NULL:不允许为空
PRIMARY KEY:把它定义成主键
AUTO_INCREMENT:让其自动增长:主键的修饰符
,name:逗号隔开,第二个字段叫name
CHAR(30):名字可以使用字符串,定长30个字符
NOT NULL:名字不能为空:NOT NULL的修饰符
,age:逗号隔开,第三个字段叫age年龄
TINYINT:指定年龄数量为非常小的整数
UNSIGEND:指定年龄不能为负数
,gender:,逗号隔开,第三个字段叫gender性别
ENUM性别的类型不会特别多这里指定使用枚举类型
('F','M'):指定F和M两种性别
DEFAULT 'M':假设学生中男性为多,这里设定M为默认
UNIQUE KEY(name,gender):假设学生的名字和学生的性别不能重叠,联合起来是唯一的.这里使用UNIQUE KEY设置唯一键
INDEX(name)):在姓名上额外做一个索引
使用DESC tbl1:显示表结构
MariaDB [mydb]> SHOW ENGINES; :显示表状态信息
示例:删除一个索引: ALTER TABLE tbl1 DROP INDEX name_2;
示例:新增一个表的字段(默认排序在表中的最后一个字段):MariaDB [mydb]> ALTER TABLE tbl1 ADD ClassID TINYINT UNSIGNED NOT NULL;
示例:修改一个表中的一个字段并且指定其在age字段后的位置: MariaDB [mydb]> ALTER TABLE tbl1 MODIFY ClassID TINYINT UNSIGNED NOT NULL after age;
示例:删除tbl1表中的一个索引:MariaDB [mydb]> DROP INDEX name ON tbl1;
示例:在tbl1表中为name字段的前五个字节上和gender字段创建一个名为nameandgender的索引 MariaDB [mydb]> CREATE INDEX nameandgender ON tbl1(name(5),gender);
示例:查看在tbl1表中以name开头的索引:MariaDB [mydb]> SHOW INDEXES FROM tbl1 WHERE Key_name LIKE 'name%';
DML语句
INSERT，SELECT，DELETE，UPDATE
INSERT：插入
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
SELECT：查询
(1) SELECT * FROM tbl_name; 查询一个指定表的所有行,不要在生产环境中使用
(2) SELECT col1, col2, ... FROM tbl_name; 查询一个表中所有行中指定的字段
指定字段时可以指定别名:字段别名：col1 AS ALIAS
(3) ELECT col1, col2, ... FROM tbl_name WHERE CLUASE;
WHERE expr：布尔表达式；
col_name OPERATOR value|col_name;
操作符：
>, <, <=, >=, =, !=
BETWEEN ... AND ...
LIKE 'PATTERN'：
通配符：
_：匹配任意单个字符；
%：任意长度的任意字符；
RLIKE 'PATTERN':
IN(list)
组合条件：
and, or, not
(4) SELECT col1, ... FROM tbl1_name [WEHRE CLAUSE] ORDER BY col1, col2, ... [ASC|DESC]
DELETE：
DELETE FROM tbl_name
[WHERE where_condition]
UPDATE：
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
UPDATE：
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
示例:在tbl1表中插入一行数据:INSERT INTO tbl1 (name,ClassID) VALUE ('tom',1);
示例:在tbl1表中插入多行数据:INSERT INTO tbl1 (name,ClassID) VALUE ('jerry',1),('clinton',2);
示例:在tbl1表中插入每个字段插入数据
示例:查询tbl1中name和gender字段:MariaDB [mydb]> SELECT name,gender FROM tbl1;
示例:使用别名Stuname显示name字段查询tbl1中name和gender字段:MariaDB [mydb]> SELECT name AS Stuname,gender FROM tbl1;
示例:挑选查询tbl1表中ClassID大于2的:MariaDB [mydb]> SELECT * FROM tbl1 WHERE ClassID > 2;
示例:挑选查询tbl1表中ClassID大于等于2和小于等于5的:MariaDB [mydb]> SELECT * FROM tbl1 WHERE ClassID >=2 and ClassID <=5;
示例:挑选查询tbl1表中ClassID在2和5之间的:MariaDB [mydb]> SELECT * FROM tbl1 WHERE ClassID BETWEEN 2 AND 5;
示例:查询tbl1表中name字段包含O的:MariaDB [mydb]> select name from tbl1 where name like '%o%';
示例:查询tbl1表中name字段以r结尾的:MariaDB [mydb]> select name from tbl1 where name like '%r';
示例:使用RLIKE支持正则表达式的方法查询tbl1中name字段包含o的:MariaDB [mydb]> select name from tbl1 where name rlike '^.o.$';
示例:在tbl1表中挑选出ClassID字段是1和2的:MariaDB [mydb]> select name,ClassID from tbl1 where ClassID in (1,2);
示例:以名字查询tbl1中的name和ClassID字段,并且升序排序:MariaDB [mydb]> select * from tbl1 order by ClassID,name;
示例:以名字查询tbl1中的name和ClassID字段,并且逆序排序:MariaDB [mydb]> select * from tbl1 order by ClassID,name desc;
示例:以ClassID查询tbl1表中的ClassID字段,并且逆序排序:MariaDB [mydb]> select * from tbl1 order by ClassID desc;
示例:以ClassID字段逆序和name字段升序查询tbl1表:MariaDB [mydb]> select * from tbl1 order by ClassID desc,name;
示例:以ClassID字段逆序和name字段逆序查询tbl1表:
示例:删除tbl1表中ClassID字段中为5的:MariaDB [mydb]> delete from tbl1 where ClassID = 5;
示例:把tbl1表中id为2的age更新为17:MariaDB [mydb]> update tbl1 set age=17 where id=2;
示例:把tbl1表中id为1的age更新为18,ClassID更新为3:MariaDB [mydb]> update tbl1 set age=18,ClassID=3 WHERE id=1;
原创文章，作者：M20-1马星，如若转载，请注明出处：http://www.178linux.com/58121
