MySQL | DML SQL语句 | 数据操作语言 | Data Manufacture Language

DML SQL 语句

DML 是指Data Manufacture Language，数据操作语言是指读写数据的SQL语句。包括插入、删除、修改、查询数据等。

插入数据

使用INSERT向表中插入数据。格式为：INSERT INTO table_name(COLUMN_NAME[,COLUMN_NAME])VALUES(‘value[,‘value’])

insert into tb_CRANE
(name, age, height, hobby) --列名
values
('千户', '18', '158', 'reading'); --值必须用单引号引起来，与列一一对应

mysql> insert into tb_CRANE
    -> (name, age, height, hobby)
    -> values
    -> ('千户', '18', '158', 'reading');
Query OK, 1 row affected (0.40 sec)

当列的类型为数值类型（int、float等）时，单引号可以省略。

查询数据

查询数据库使用SELECT命令。最简单的格式为SELECT column_name FROM table_name。如果查询多个列名，使用逗号隔开。星号（*）代表查询所有列

SELECT * FROM tb_CRANE; --查询tb_CRANE中的所有数据

mysql> SELECT * FROM tb_CRANE;
+----+----------+------+--------+----------+
| id | name     | age  | height | hobby    |
+----+----------+------+--------+----------+
|  1 | 千户     |   18 |    158 | reading  |
|  2 | 尤莉     |   19 |    167 | eating   |
|  3 | 野原广志 |   35 |    180 | drinking |
+----+----------+------+--------+----------+
3 rows in set (0.00 sec)

SELECT id, name, hobby FROM tb_CRANE; --只查询tb_CRANE里的三列数据

mysql> SELECT id, name, hobby FROM tb_CRANE;
+----+----------+----------+
| id | name     | hobby    |
+----+----------+----------+
|  1 | 千户     | reading  |
|  2 | 尤莉     | eating   |
|  3 | 野原广志 | drinking |
+----+----------+----------+
3 rows in set (0.00 sec)

也可以这样更完整得写

SELECT tb_CRANE.* FROM crane.tb_CRANE;
SELECT tb_CRANE.id, tb_CRANE_name, tb_CRANE.hobby FROM crane.tb_CRANE;

使用AS可以为列或者表重命名。AS关键帧也可以省略

SELECT id AS c_id, name AS c_name FROM tb_CRANE AS CCC; --使用AS重命名列与表
SELECT id c_id, name c_name FROM tb CRANE CCC; --不使用AS重命名

注意列的重命名只发生在结果集中，并不会对数据表产生任何作用。

SELECT语句后面跟WHERE子句，表示有选择地显示数据。常用的条件有大于’>’，小于’<’，等于’=’，不等于’<>'等。多个条件时，使用and，or。字符型数据必须使用单引号，数据型可以省略

SELECT * FROM tb_CRANE WHERE age > 18; --只显示所有age大于18的记录
SELECT * FROM tb_CRANE WHERE age > 18 and age <> 35; --只显示age>18且不等于35的记录
SELECT * FROM tb_CRANE WHERE age = 18; --显示age==18的记录
SELECT * FROM tb_CRANE WHERE name = '尤莉'; --显示name为'尤莉'的记录。字符类型，必须使用单引号

mysql> SELECT * FROM tb_CRANE WHERE age > 18;
+----+----------+------+--------+----------+
| id | name     | age  | height | hobby    |
+----+----------+------+--------+----------+
|  2 | 尤莉     |   19 |    167 | eating   |
|  3 | 野原广志 |   35 |    180 | drinking |
+----+----------+------+--------+----------+
2 rows in set (0.34 sec)

mysql> SELECT * FROM tb_CRANE WHERE age > 18 and age <> 35;
+----+------+------+--------+--------+
| id | name | age  | height | hobby  |
+----+------+------+--------+--------+
|  2 | 尤莉 |   19 |    167 | eating |
+----+------+------+--------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_CRANE WHERE age = 18;
+----+------+------+--------+---------+
| id | name | age  | height | hobby   |
+----+------+------+--------+---------+
|  1 | 千户 |   18 |    158 | reading |
+----+------+------+--------+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_CRANE WHERE name = '尤莉';
+----+------+------+--------+--------+
| id | name | age  | height | hobby  |
+----+------+------+--------+--------+
|  2 | 尤莉 |   19 |    167 | eating |
+----+------+------+--------+--------+
1 row in set (0.01 sec)

WHERE中还可以使用LIKE与’%'对字符类型的列进行模糊查询

SELECT * FROM tb_CRANE WHERE name LIKE '%户'; --检索name列以'户'结尾的记录
SELECT * FROM tb_CRANE WHERE name LIKE '尤%'; --检索name列以'尤'开始的记录
SELECT * FROM tb_CRANE WHERE name LIKE '%原%'; --检索name列包含'原'的记录（包含以其开始与结束的）

mysql> SELECT * FROM tb_CRANE WHERE name LIKE '%户';
+----+------+------+--------+---------+
| id | name | age  | height | hobby   |
+----+------+------+--------+---------+
|  1 | 千户 |   18 |    158 | reading |
+----+------+------+--------+---------+
1 row in set (0.01 sec)

mysql> SELECT * FROM tb_CRANE WHERE name LIKE '尤%';
+----+------+------+--------+--------+
| id | name | age  | height | hobby  |
+----+------+------+--------+--------+
|  2 | 尤莉 |   19 |    167 | eating |
+----+------+------+--------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_CRANE WHERE name LIKE '%原%';
+----+----------+------+--------+----------+
| id | name     | age  | height | hobby    |
+----+----------+------+--------+----------+
|  3 | 野原广志 |   35 |    180 | drinking |
+----+----------+------+--------+----------+
1 row in set (0.00 sec)

如果逻辑有先后顺序，可以使用括号括起来

SELECT * FROM tb_CRANE WHERE (id > 1 and id < 5) and (name like '%莉' or name like '野%');

mysql> SELECT * FROM tb_CRANE WHERE (id > 1 and id < 5) and (name like '%莉' or name like '野%');
+----+----------+------+--------+----------+
| id | name     | age  | height | hobby    |
+----+----------+------+--------+----------+
|  2 | 尤莉     |   19 |    167 | eating   |
|  3 | 野原广志 |   35 |    180 | drinking |
+----+----------+------+--------+----------+
2 rows in set (0.02 sec)

删除数据

使用delete删除数据

delete from tb_CRANE where id = 1;

mysql> delete from tb_CRANE where id = 1;
Query OK, 1 row affected (0.39 sec)

如果不指定where条件，将删除所有的数据行

修改数据

使用update修改数据

update tb_CRANE
set hobby = 'sleeping'
where id = 2;

mysql> update tb_CRANE
    -> set hobby = 'sleeping'
    -> where id = 2;
Query OK, 1 row affected (0.35 sec)
Rows matched: 1  Changed: 1  Warnings: 0

如果不指定where条件，将修改所有的数据行

创建，删除，授权用户

在mysql数据库下使用create user创建新用户

use mysql;
create user new_crane;

新创建后的用户没有任何授权。使用grant命令授权new_crane访问数据库crane下的所有表，密码为password123456

grant all privileges
on crane.*
to new_crane@'%'
indentified by 'password123456'
with grant option;

授权后的用户new_crane仅能访问数据库crane下的数据表

删除用户使用drop user命令

drop user new_crane;

批量执行SQL语句

SQL语句比较多时，通常将SQL语句写到.sql格式的文件中，使用批量的方式执行

使用命令source或者反斜杠加点.在控制台执行该SQL文件
注意该命令后面不能使用分号;

\. C:crane.sql
source c:\crane.sql