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

尤莉

  • 7
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值