DML和常用SQL举例

1、服务器端命令：

• （1） DDL是数据定义语言，主要用于管理数据库组件，例如表、索引、视图、用户、存储过程
  CREATE、ALTER、DROP
• （2）DML是数据操纵语言，主要用管理表中的数据，实现数据的增、删、改、查；
  INSERT， DELETE， UPDATE， SELECT
• （3）DCL是数据库控制语言，用于对数据库账号的授权、角色控制等操作。

2、数据库管理（DDL）：

（1）、获取命令帮助：

• mysql> help KEYWORD

（2）、创建库命令：

• 格式：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 testdb;
Query OK, 1 row affected (0.00 sec)

（3）、修改库命令：

• 格式：ALTER {DATABASE | SCHEMA} [db_name]
  [DEFAULT] CHARACTER SET [=] charset_name #默认字符集设置
  [DEFAULT] COLLATE [=] collation_name #默认排序规则

(4)、删除库命令：

• 用于从数据库中删除对象，如删除数据库、删除数据表、删除索引等，其语法格式为：
• 格式：DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

示例：

MariaDB [(none)]> DROP DATABASE testdb;#删除testdb库
MariaDB [shooldb]> DROP TABLE tl1; #删除tl1表

（5）、查看库命令：

• 格式：SHOW DATABASES LIKE ’%db‘;%是代表任意字符

示例：
MariaDB [(none)]> SHOW DATABASES LIKE 'testdb';
+-------------------+
| Database (testdb) |
+-------------------+
| testdb            |
+-------------------+
1 row in set (0.00 sec)

2、表管理：

（1）查看命令：

• DESC tabla 显示表字段

• mysql> SHOW ENGINES; 查看数据库支持的所有存储引擎类型：
• mysql> SHOW TABLES STATUS [LIKE 'tbl_name'] 查看某表的存储引擎类型：
• mysql>show tables 查看某表的状态信息

示例：
MariaDB [testdb]> desc students;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(40) | NO   | PRI |         |       |
| id      | int(10)     | YES  |     | NULL    |       |
| address | varchar(40) | YES  |     | NULL    |       |
| class   | int(10)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| students         |
+------------------+
1 row in set (0.00 sec)

（2）创建表命令：

• 格式： CREATE TABLE [IF NOT EXISTS] [db_name.] tbl_name (create_defination) [table_options]不是默认库要指定库名

create_defination里面包括:

• 字段：col_name data_type

• 键：
  PRIMARY KEY (col1, col2, ...) #主键，唯一地标识表中的某一条记录，一个表只能有一个主键。
  UNIQUE KEY (col1, col2,...) #确保在非主键列中不输入重复的值，一个表定义多个 UNIQUE 约束。
  FOREIGN KEY (column) #指向另一个表中的 PRIMARY KEY，用于关联数据表。
  AUTO_INCREMENT：#自增主键
• 索引：
  KEY|INDEX [index_name] (col1, col2,...)
• table_options设置为：
  ENGINE [=] engine_name

示例：MariaDB [testdb]> CREATE TABLE students(
>name VARCHAR(40),#姓名字段类型为字符型 限40个字符
>id INT(10),   #id字段类型为整形 限定10个字符
>Address VARCHAR(40), #地址字段类型为字符型 限40个字符
>Class INT(10),      #班级字段类型为整形 限定10个字符
>primary key(name)); #name字段作为主键
Query OK, 0 rows affected (0.06 sec)

(3)修改表命令：

• 格式：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
  • 修改方法1：CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
    • 修改方法2：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

示例：
MariaDB [testdb]> ALTER TABLE students ADD INDEX(id,name);#给students的id和name字段添加索引
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> ALTER TABLE students DROP class;#删除students表的class字段
Query OK, 0 rows affected (0.14 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> ALTER TABLE students MODIFY id int(10) UNSIGNED NOT NULL;#修改students表中的id字段为无符号的非空整型字符
Query OK, 0 rows affected (0.14 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> desc students;#查询students表
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| name    | varchar(40)      | NO   | PRI |         |       |
| id      | int(10) unsigned | NO   | MUL | NULL    |       |
| address | varchar(40)      | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

（4）索引管理：

索引是特殊的数据结构；

索引：要有索引名称；

• 创建：CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [BTREE|HASH] ON tbl_name (col1, col2,,...)

• 删除：DROP INDEX index_name ON tbl_name

3、数据的操纵（DML）

(1)插入命令INSERT INTO：

• INSERT [INTO] tbl_name [(col1,...)] {VALUES|VALUE} (val1, ...),(...),...

• 注意：
  字符型：引号；
  数值型：不能用引号；

示例：
MariaDB [testdb]> INSERT INTO students(name,id,Address,Class)VALUES
>('zhao',001,'beijing',2017),
>('qian',002,'shanghai',2018),
>('sun',003,'chengdu',2017),
>('li',004,'tianjing',2018),
>('zhou',005,'chongqing',2017);

（2）、查询命令SELECT：

• 1. SELECT * FROM tbl_name;

示例：
MariaDB [testdb]> SELECT * FROM students;#查询students表所有字段内容（生产环境不能这样操作）
+------+------+-----------+-------+
| name | id   | Address   | Class |
+------+------+-----------+-------+
| li   |    4 | tianjing  |  2018 |
| qian |    2 | shanghai  |  2018 |
| sun  |    3 | chengdu   |  2017 |
| zhao |    1 | beijing   |  2017 |
| zhou |    5 | chongqing |  2017 |
+------+------+-----------+-------+

• 2. SELECT 字段1, 字段2, ... FROM tbl_name;
• 显示时，字段可以显示为别名；AS+（）
  col_name AS col_alias

示例：
MariaDB [testdb]> SELECT name,id FROM students;#查询id，name字段的列
+------+------+
| name | id   |
+------+------+
| li   |    4 |
| qian |    2 |
| sun  |    3 |
| zhao |    1 |
| zhou |    5 |
+------+------+

• 3. SELECT col1, ... FROM tbl_name WHERE clause;

• WHERE clause：用于指明挑选条件；
  col_name 操作符 value：
  例如： age > 30;

• 操作符(1) ：>, <, >=, <=, ==, !=

• 组合条件：and 、or、not

 示例：
 MariaDB [testdb]> SELECT name,id,Address FROM students WHERE id>3;查找ID大于3的行
+------+------+-----------+
| name | id   | Address   |
+------+------+-----------+
| li   |    4 | tianjing  |
| zhou |    5 | chongqing |
+------+------+-----------+

• 操作符(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 [testdb]> SELECT name,id,Address FROM students ORDER BY id ASC;#查找以ID字段升序排列表
+------+------+-----------+
| name | id   | Address   |
+------+------+-----------+
| zhao |    1 | beijing   |
| qian |    2 | shanghai  |
| sun  |    3 | chengdu   |
| li   |    4 | tianjing  |
| zhou |    5 | chongqing |
+------+------+-----------+

（3）删除命令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]

示例：
    MariaDB [testdb]> DELETE FROM students WHERE name='li';#把name为li的行删除

（4）修改命令UPDATE：

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

示例：MariaDB [testdb]> UPDATE students SET name='zhang' WHERE id=1;   #把ID为1的name改为zhang  

3、数据库控制DCL

（1）用户授权命令GRANT

若授权的用户不存在，GRANT可自动生成指定的授权用户，其语法为：

• 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：指定库上的存储过程或存储函数；

• 注意：msql的用户账号由两部分组成：'USERNAME'@'HOST';其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务；

示例：
GRANT ALL ON testdb.* TO ‘test’@’192.168.%.%’ INENTIFIED BY ‘123’;

（2）撤销授权REVOKE

• 撤销授权，其语法为：
  REVOKE priv_type, ... ON db_name.tbl_name FROM 'user'@'host';

示例：
REVOKE ALL ON testdb.*  FROM 'test'@'192.168.%.%';

• 撤销指定用户的授权，需给定USER和HOST值

REVOKE ALL ON test.* FROM 'test'@'192.168.%.%';