File Services之mariadb数据库基础

什么是DML？常用SQL举例，每个命令至少1个例子，最多不超过3个例子

DML:Data Manipulation,数据操纵语言，主要用管理表中的数据，实现数据的增、删、改、查；`INSERT， DELETE， UPDATE， SELECT`,是mysql数据库服务器端的命令。

服务器端的命令除了DML，还有DDL,Data Definition,数据定义语言，主要用于管理数据库组件，例如表、索引、视图、用户、存储过程CREATE、ALTER、DROP

DDL命令：（数据库管理）

创建:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name;
[DEFAULT] CHARACTER SET [=] charset_name #设定数据库使用的字符集
[DEFAULT] COLLATE [=] collation_name #设定排序规则
查看支持的所有字符集：SHOW CHARACTER SET
查看支持的所有排序规则：SHOW COLLATION
```
 例：
     MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testdb ;
     Query OK, 1 row affected, 1 warning (0.00 sec)
     CREATE DATABASE IF NOT EXISTS testdb2 CHARACTER SET 'utf8';
     Query OK, 1 row affected (0.04 sec)
```

修改：
ALTER {DATABASE | SCHEMA } {db_name}
[DEFAULT] CHARACHTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name

 例：
 MariaDB [(none)]> ALTER DATABASE testdb CHARACTER SET 'gb2312';
 Query OK, 1 row affected (0.00 sec)
 MariaDB [(none)]> ALTER DATABASE testdb COLLATE 'binary';
 Query OK, 1 row affected (0.00 sec)

删除：
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
注意：mariadb数据库没有回收站，在生产环境中删除操作很危险；
```
 MariaDB [(none)]> DROP DATABASE IF EXISTS testdb;
```

查看：
SHOW DATABASES LIKE 'pattern';

 LIKE 'pattern':通配模式
 %：任意长度字符
 _：任意单个字符
 
 例：
 MariaDB [(none)]> SHOW DATABASES LIKE '%db';
 +----------------+
 | Database (%db) |
 +----------------+
 | testdb         |
 +----------------+
 1 row in set (0.01 sec)
 MariaDB [(none)]> SHOW DATABASES LIKE '_estdb';
 +-------------------+
 | Database (_estdb) |
 +-------------------+
 | testdb            |
 +-------------------+
 1 row in set (0.01 sec)

（表管理）

创建：
CREATE TABLE [IF NOT EXISTS] tb1_name (create_defination) [tabke_options]

 create_defination:
     字段：col_name  data_type
     键：
         PRIMARY KEY (col1, col2, ...)
         UNIQUE KEY  (col1, col2,...)
         FOREIGN KEY (column)
     索引：
         KEY|INDEX  [index_name]  (col1, col2,...)
                     
 table_options：
         ENGINE [=] engine_name
                 
 查看数据库支持的所有存储引擎类型：
         mysql> SHOW  ENGINES;
 查看数据库表状态：
         mysql> SHOW  STATUS\G;

例：

         MariaDB [testdb]> USE students;
         Database changed
         MariaDB [students]> CREATE TABLE tbl1 (id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL);
 Query OK, 0 rows affected (0.07 sec)
 
        MariaDB [students]> SHOW TABLE STATUS LIKE 'tbl2'\G
 *************************** 1. row ***************************
            Name: tbl2
          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: 0
  Auto_increment: 1
     Create_time: 2018-07-22 14:16:11
     Update_time: NULL
      Check_time: NULL
       Collation: latin1_swedish_ci
        Checksum: NULL
  Create_options: 
         Comment: 
 1 row in set (0.01 sec)

添加、修改：
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...]

 alter_specification:
     字段：
         添加：ADD  [COLUMN]  col_name  data_type  [FIRST | AFTER col_name ]
         删除：DROP  [COLUMN] col_name 
         修改：
             HANGE [COLUMN] old_col_name new_col_name column_definition  [FIRST|AFTER col_name]  
             MODIFY [COLUMN] col_name column_definition  [FIRST | AFTER col_name]
     键：
         添加：ADD  {PRIMARY|UNIQUE|FOREIGN}  KEY (col1, col2,...)
         删除：
             主键：DROP PRIMARY KEY
             外键：DROP FOREIGN KEY fk_symbol
         索引：
             添加：ADD {INDEX|KEY} [index_name]  (col1, col2,...)
             删除：DROP {INDEX|KEY}  index_name
         表选项：
             ENGINE [=] engine_name
                 
         查看表上的索引的信息：
             mysql> SHOW INDEXES FROM tbl_name;

...

    示例：
    MariaDB [students]> DESC tbl1;
    +-------+---------------------+------+-----+---------+----------------+
    | Field | Type                | Null | Key | Default | Extra          |
    +-------+---------------------+------+-----+---------+----------------+
    | id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(60)         | NO   |     | NULL    |                |
    +-------+---------------------+------+-----+---------+----------------+
    2 rows in set (0.00 sec)
    #添加新的gender字段
    MariaDB [students]> ALTER TABLE tbl1 ADD gender ENUM('F','M') after id;
    Query OK, 0 rows affected (0.09 sec)               
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [students]> DESC tbl1;
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
    | gender | enum('F','M')       | YES  |     | NULL    |                |
    | name   | varchar(60)         | NO   |     | NULL    |                |
    +--------+---------------------+------+-----+---------+----------------+
    3 rows in set (0.01 sec)
    
    #删除主键
    MariaDB [students]> ALTER TABLE tbl1 DROP PRIMARY KEY;
    Query OK, 0 rows affected (0.06 sec)               
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [students]> DESC tbl1;
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
    | gender | enum('F','M')       | YES  | UNI | NULL    |                |
    | name   | varchar(60)         | NO   |     | NULL    |                |
    +--------+---------------------+------+-----+---------+----------------+
    3 rows in set (0.01 sec)
    #添加主键
    MariaDB [students]> ALTER TABLE tbl1 ADD PRIMARY KEY(gender);
    Query OK, 0 rows affected (0.08 sec)               
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [students]> DESC tbl1;
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | tinyint(3) unsigned | NO   | UNI | NULL    | auto_increment |
    | gender | enum('F','M')       | NO   | PRI | F       |                |
    | name   | varchar(60)         | NO   |     | NULL    |                |
    +--------+---------------------+------+-----+---------+----------------+
    3 rows in set (0.01 sec)
    #创建索引
    MariaDB [students]> ALTER TABLE tbl1 ADD INDEX(id);
    Query OK, 0 rows affected (0.06 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    #查看索引
    MariaDB [students]> DESC tbl1;
    MariaDB [students]> SHOW INDEXES FROM tbl1;
    #s删除索引
    MariaDB [students]> ALTER TABLE tbl1 DROP INDEXES id;

删除：
DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...
注意：危险操作；

另外两种创建方式：
[1]. 复制表结构MariaDB [testdb]> CREATE TABLE tbl4 LIKE mysql.user; Query OK, 0 rows affected (0.02 sec)
[2]. 复制表数据
MariaDB [testdb]> CREATE TABLE tbl5 SELECT Host,User,Password FROM mysql.user; Query OK, 5 rows affected (0.06 sec) Records: 5 Duplicates: 0 Warnings: 0

 MariaDB [testdb]> SELECT * FROM tbl5;
 +-------------+--------+-------------------------------------------+
 | Host        | User   | Password                                  |
 +-------------+--------+-------------------------------------------+
 | localhost   | root   | *89367322F7FE6F2C97B8BF805585800F2E90EB76 |
 | 127.0.0.1   | root   | *89367322F7FE6F2C97B8BF805585800F2E90EB76 |
 | ::1         | root   | *89367322F7FE6F2C97B8BF805585800F2E90EB76 |
 | %           | wpuser | *C9B2DB1CA193280B971CA3602D5174A5D637D2BF |
 | 192.168.%.% | myuser | *85E26B8AB29FEE8453201A3511DAE24A24059109 |
 +-------------+--------+-------------------------------------------+

索引管理：
索引是特殊的数据结构；
索引：要有索引名称；

     创建：
     CREATE  [UNIQUE|FULLTEXT|SPATIAL] INDEX  index_name  [BTREE|HASH]  ON tbl_name (col1, col2,,...)
     UNIQUE:创建多键索引；
     
     删除：
     DROP  INDEX index_name ON tbl_name

DDL：INSERT， DELETE， UPDATE， SELECT

INSERT INTO：
INSERT [INTO] tbl_name [(col1,...)] {VALUES|VALUE} (val1, ...),(...),...
注意：
字符型：引号；
数值型：不能用引号；

 示例：
     MariaDB [students]> SELECT * FROM tbl1;
     Empty set (0.01 sec)
     
     MariaDB [students]> INSERT INTO tbl1(name,gender) VALUES('GUO JING','M'),('Ding Dian','F');
     Query OK, 2 rows affected (0.02 sec)
     Records: 2  Duplicates: 0  Warnings: 0
     
     MariaDB [students]> SELECT * FROM tbl1;
     +----+--------+-----------+
     | id | gender | name      |
     +----+--------+-----------+
     |  2 | F      | Ding Dian |
     |  1 | M      | GUO JING  |
     +----+--------+-----------+
     2 rows in set (0.00 sec)
     #替换表中数据
     MariaDB [students]> REPLACE INTO tbl1 VALUE(3,'F','DONGFANGBUBAI');
     Query OK, 2 rows affected (0.01 sec)

SELECT：
(1) SELECT * FROM tbl_name;
(2) SELECT col1, col2, ... FROM tbl_name;
显示时，字段可以显示为别名； col_name AS col_alias
(3) SELECT col1, ... FROM tbl_name WHERE clause;
WHERE clause：用于指明挑选条件；
col_name 操作符 value：
age > 30;
操作符(1) ：
>, <, >=, <=, ==, !=
组合条件：
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 col_name, col_name2, ... [ASC|DESC];
ASC: 升序；
DESC：降序；

    示例：
        MariaDB [students]> SELECT * FROM tbl1;
        +----+--------+---------------+---------+
        | id | gender | name          | age     |
        +----+--------+---------------+---------+
        |  1 | M      | Guo Jing      | NULL    |
        |  2 | M      | Ding Dian     | NULL    |
        |  3 | F      | DONGFANGBUBAI | 1001-01 |
        |  4 | F      | DING DANG     | NULL    |
        +----+--------+---------------+---------+
        MariaDB [students]> SELECT id,name FROM tbl1;
        +----+---------------+
        | id | name          |
        +----+---------------+
        |  1 | Guo Jing      |
        |  2 | Ding Dian     |
        |  3 | DONGFANGBUBAI |
        |  4 | DING DANG     |
        +----+---------------+
        4 rows in set (0.01 sec)
        MariaDB [students]> SELECT id,gender,name FROM tbl1 WHERE gender='M';
        +----+--------+-----------+
        | id | gender | name      |
        +----+--------+-----------+
        |  1 | M      | Guo Jing  |
        |  2 | M      | Ding Dian |
        +----+--------+-----------+

DELETE：
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
(1) DELETE FROM tbl_name WHERE where_condition
(2) DELETE FROM tbl_name [ORDER BY ...] [LIMIT row_count]

注意：不加WHERE 会将删除整个表

    示例：
        MariaDB [students]> SELECT * FROM tbl1;
        +----+--------+---------------+---------+
        | id | gender | name          | age     |
        +----+--------+---------------+---------+
        |  1 | M      | Guo Jing      | NULL    |
        |  2 | M      | Ding Dian     | NULL    |
        |  3 | F      | DONGFANGBUBAI | 1001-01 |
        |  4 | F      | DING DANG     | NULL    |
        +----+--------+---------------+---------+
        4 rows in set (0.00 sec)
        
        MariaDB [students]> DELETE FROM tbl1 WHERE id=3;
        Query OK, 1 row affected (0.01 sec)
        
        MariaDB [students]> SELECT * FROM tbl1;
        +----+--------+-----------+------+
        | id | gender | name      | age  |
        +----+--------+-----------+------+
        |  1 | M      | Guo Jing  | NULL |
        |  2 | M      | Ding Dian | NULL |
        |  4 | F      | DING DANG | NULL |
        +----+--------+-----------+------+
        3 rows in set (0.00 sec)

UPDATE：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

     示例：
     MariaDB [students]> UPDATE tbl1 SET age=33 WHERE id=2;
     Query OK, 1 row affected (0.03 sec)
     Rows matched: 1  Changed: 1  Warnings: 0
     
     MariaDB [students]> SELECT * FROM tbl1;
     +----+--------+-----------+------+
     | id | gender | name      | age  |
     +----+--------+-----------+------+
     |  1 | M      | Guo Jing  | NULL |
     |  2 | M      | Ding Dian | 33   |
     |  4 | F      | DING DANG | NULL |
     +----+--------+-----------+------+

用户账号及权限管理：

用户账号：'username'@'host'

host：此用户访问当前mysql服务器时，允许其通过哪些主机远程创建连接；

表示方式：IP，网络地址、主机名、通配符(%和_)；

禁止检查主机名：my.cnf
[mysqld] skip_name_resolve = ON

创建用户账号：
CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
删除用户账号：
DROP USER ’user‘@’host' [, user@host] ...
授权：

权限级别：管理权限、数据库、表、字段、存储例程；
GRANT priv_type,... ON [object_type] db_name.tbl_name TO 'user'@'host' [IDENTIFIED BY 'password'];

      priv_type： ALL  [PRIVILEGES]
      db_name.tbl_name：
              *.*：所有库的所有表；
              db_name.*：指定库的所有表；
              db_name.tbl_name：指定库的特定表；
              db_name.routine_name：指定库上的存储过程或存储函数；
              [object_type]
                      TABLE
                      FUNCTION
                      PROCEDURE

查看指定用户所获得的授权：
SHOW GRANTS FOR 'user'@'host'
SHOW GRANTS FOR CURRENT_USER;
回收权限：
REVOKE priv_type, ... ON db_name.tbl_name FROM 'user'@'host';

注意：MariaDB服务进程启动时，会读取mysql库的所有授权表至内存中；
(1) GRANT或REVOKE命令等执行的权限操作会保存于表中，MariaDB此时一般会自动重读授权表，权限修改会立即生效；
(2) 其它方式实现的权限修改，要想生效，必须手动运行FLUSH PRIVILEGES命令方可；

加固mysql服务器，在安装完成后，运行mysql_secure_installation命令；

最后编辑于：2018.09.11 23:09:35

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 205,132评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,802评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,566评论 0赞 338
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,858评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,867评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,695评论 1赞 282
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,064评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,705评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 42,915评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,677评论 2赞 323
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,796评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,432评论 4赞 322
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,041评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,992评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,223评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,185评论 2赞 352
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,535评论 2赞 343