Fork me on GitHub

MySQL4种语言

Posted on | In , |
Words count in article: 3k | Reading time ≈ 12

MySQL学习-4种语言

在MySQL数据库中存在4种常见的语言，它们分别是：

  1. 数据定义语言：DDL，data defination language
  2. 数据操作语言：DML，data manipulation language
  3. 数据控制语言：DCL，data control language
  4. 数据查询语言：DQL，data query language

在接下来的内容中，本文将会对这4种语言进行详细地讲解：

进入数据库

在网上安装MySQL数据库的教程很多，不阐述安装过程。在讲解4种数据库之前，先看看怎么进入到数据库中，以macOS系统为例：

方式1

第一种是通过密码明文方式进入数据库中：

1
mysql -uroot -p123456   # 假设用户名是root，密码是123456

这种方式的好处是我们能够看到自己的用户名和密码，方便检查；缺点就是在系统中暴露了我们的密码，不够安全

方式2

第二种是通过密码暗文方式进入数据库

1
mysql -uroot -p   # 按下回车再输入密码

这种方式后面有跟上用户名，但是密码需要再输入，而且看不到我们用户的内容。

优点就是安全，保证密码不泄露；缺点就是看不到输入的内容，我们可能输入错误的密码也不方便检查，需要我们很有把握保证我们输入正确的密码。笔者一般使用的此种方式

数据定义语言

数据定义语言指的是：data defination language，简称是DDL。

DDL的主要作用是创建数据库，以及对库和表的结构进行删除和修改等基本操作，下面是针对库表的操作命令进行解释

全部命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 1、数据库操作
show databases;    -- 查看全部的数据库
use test;   -- 使用test数据库（假设存在test）
create database school;   -- 创建school数据库
drop database school;    -- 删除school数据库

-- 2、表操作
-- 创建user表，同时指定多个字段
create table user(字段1,字段2,......,字段n);

-- 查看创建表的语言
show create table user;

-- 查看表的结构
desc user;

-- 删除表
drop table  user;   -- 删除user表（user为表名）
-- 修改表名
alter table user rename to users;   -- 将user改成users

创建表

1
2
3
4
5
6
7
8
create table user( id int(10) unsigned not null auto_increment comment "user_id",   -- id作为主键
                  name varchar(20) not null comment "user_name",
                  email varchar(50) not null comment "user_email",
                  age tinyint unsigned not null comment "user_age",
                  fee decimal(10,2) not null default 0.00 comment "user_fee",
                  createTime timestamp not null comment "user_time",
                  primary key(id)
                 );    -- 末尾记得分号

查看表结构

查看上面创建的表结构

1
desc user;    -- 查看创建表的结构

显示创建表的SQL语句

1
show create table user;

字段操作

字段操作的关键词是alter，我们先选中需要操作的表，常见的操作有：

  • modify：修改
  • change：改变名字
  • add：添加字段，默认是在末尾添加，也可以指定位置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--  修改字段信息
alter table user modify name varchar(50) not null;  -- 将字段name 从20改为50个字符

--  修改字段名字
alter table user change email user_email varchar(50) not null;  -- 将email改成user_email

-- 末尾添加字段
alter table user add password char(30) not null comment "user_password";   # 增加password字段

-- 指定位置添加字段
alter table user add password1 char(30) not null comment "user_password1" after user_name;   # 在name后面增加password1字段

-- 删除字段
alter table user drop password1;        #删除字段password1

比如我们添加了字段password之后的建表语句：

数据库操作语言

数据操作语言指的是DML,data manipulation language。主要是对数据库中的表记录进行操作的语言，包含往表中插入数据、表中数据的更新、表的删除等

插入数据

我们往上面创建的user表中插入数据，我们可以只插入部分字段，未插入的则为NULL；如果是时间则默认为当前时间：

1
2
3
-- 将字段名和字段的值一一对应起来，可以只插入部分字段
-- 省略了id和createtime字段
mysql> insert into user(name,email,age,fee,password) values("xiaoming","123456@qq.com",20,56.56,"1234");

下面是插入全部的字段信息：

1
2
-- 包含所有字段信息
insert into user values(10, "xiaohong", "78173828@qq.com", 38, 89.19, "2020-11-11 00:00:00", "2468");

几个需要注意的地方：

  • 字符串字段必须用引号括起来
  • 允许只插入部分字段，则未插入的字段显示为NULL
  • 可以省去字段名，直接插入数据的具体值，但是此时需要加上自增的id字段，而且必须填写所有的字段信息，不能只添加部分字段数据

数据更新

数据更新update使用最多的是where语句，指定某个条件下执行；如果不加where，则所有的字段都会被更改，需要慎重考虑此操作

  • 指定id号
  • 指定字段的具体值
  • 字段允许有多个，用逗号隔开
1
2
3
4
5
mysql> update user set name="nangying" where id=6;   --  通过id指定
mysql> update user set fee=88.76 where fee=56.90;   -- 通过字段名直接指定
mysql> update user set email="81847919@qq.com", age=54 where id=7;  -- 同时修改多个值
mysql> update user set fee=88.88 where id in(2,4,6);   -- in的用法
mysql> update user set fee=66.66 where id between 2 and 6;  -- between ... and ...

删除

表的删除有两种操作：

  • delete：删除表或者删除表中的某条记录，插入数据从上一次结束的id号开始继续插入；删除的记录仍存在
  • truncate：清空表，重新插入数据id从1开始；不占内存空间

两种方式都可以直接删除表：

1
2
delete table user;
truncate table user;

假设现在我们的表中存在如下的4条记录，来演示上面两种操作的区别：

1、首先是delete操作，我们删除其中id=8的记录：

1
delete from user where id=8;   --  删除某条记录

接下来我们尝试插入新的数据（没有指定id号），生成新的数据之后我们发现：系统分配的id号是11，也就是说是从当前最大的id号开始往后生成的。

2、我们在看看truncate的操作

truncate是删除整个表，相当于是清空了整个表，之后生成的数据的编号是从id=1开始

再次插入数据之后id号默认从1开始：

数据控制语言

数据控制语言指的是data control language，简称是DCL。主要是对数据库中的登录和用户的权限进行控制的语言。

主要命令

下面是DCL语言中涉及到的主要命令：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- 1、查看数据库中的用户及信息
mysql -uroot -p  -- 暗文数据库
show databases;
use mysql;   -- 自带的mysql库
show tables;
select user, host, passord from user;    # 所有的用户信息都在user表中

-- 2、创建新用户、授权、撤销权限和删除
-- 创建新用户：指定用户名test、ip和密码password
create user "test"@"192.168.2.10" identified by"password";
flush privileges;  # 刷新权限
mysql -utest -h192.168.2.10 -p  # 用test用户登录

-- 3、授权
-- 给shop数据库中的全部信息授权，test是数据库中的一个表
grant select, insert, delete on shop.* to "test"@"192.168.2.10";
flush privileges;  # 刷新权限
systemctl restart mysql;  # 重启mysql服务

-- 4、创建用户的同时进行授权
grant select, insert, delete on shop.* to "test"@"192.168.2.10" identified  by"password";

-- 5、查看权限
show grants for "test"@"192.168.2.10"\G   # \G参数是为了输出格式化

-- 6、撤销权限
-- 撤销shop数据库中test用户的delete权限：一定要通过root账户进入数据库
revoke delete on shop.* to "test"@"192.168.2.10";
flush privileges;  # 刷新权限
systemctl restart mysql;  # 重启mysql服务

-- 7、删除用户
drop user "test"@"192.168.2.10";

-- 8、所有的权限给所有数据库（谨慎操作）
grant all privileges on *.* to "test"@"192.168.2.10"

-- 9、修改用户密码
-- 用原来的用户和密码登陆再进行修改
mysql -uroot -p
show databases;
use mysql;
show tables;
select user, host, password from user;    # 所有的用户都在user表中
update user set password=PASSWORD("123456admin") where user="test";  # 将test用户的密码改成123456admin
flush privileges;

-- 10、忘记原来的密码：借助跳跃权限表，重启守护进程
mysql skip-grant-tables  # 跳跃权限表
mysql  # 重新进入mysql
show databases;
use mysql;
show tables;
select user, host, password from user;    # 所有的用户都在user表中
update user set password=PASSWORD("123456admin") where user="test";  # 将test用户的密码改成123456admin
flush privileges;   -- 刷新权限

注意点

在进行DCL语言操作的时候需要注意：

  1. MySQL服务默认的端口号是3306；
  2. 撤销权限必须通过root账户来解决
  3. 将所有权限授权给某个数据库必须谨慎操作
  4. DCL语言修改之后都要刷新权限，重启MySQL服务才会生效

关于MySQL的相关权限和配置文件详细解析，请参考文章：https://www.cnblogs.com/langdashu/p/5889352.html

数据查询语言

数据查询语言指的是：data query language，简称是DQL。查询语言在工作中其实使用的是最为频繁，查询的方式多种多样，也有很多的技巧。在本文中主要讲解常见的DQL`语言操作。后续会针对具体的技巧进行详细讲解

基本查询

1、查询全部信息：

1
select * from user;

2、通过指定的字段信息来查询

1
2
3
select * from user where id=2;
select * from user where name=wangwu;
select * from user where age=20;

3、查询指定字段信息

1
select name,age from user;

关键字in

1
select * from user where id in (1,2,3);

关键词between…and….

关键词between…and…的作用是限定字段的取值，头尾两个取值都是包含的

1
select * from user where age between 20 and 35;

关键词distinct

关键词distinct的作用是去重之后查出数据，相当于是过滤查询数据：

关键词concat

关键词语concat的作用是连接查询显示数据：

  • 直接使用系统默认的连接方式，将原来的字段通过下划线进行连接
  • 使用concat...as...，as后面自己指定连接的新字段名
  • 带上连接符号的查询concat_ws("+", 列名1，列名2)；其中"+"就是指定连接符

我们也是可以使用连接符：

1
select concat_ws("+",name,age) as name_age from user;  -- 指定连接符，同时指定别名

模糊查询like

模糊查询的关键字是like，中文翻译成像，使用的符号是%分为3种情况：

  • 左匹配
  • 右匹配
  • 左右匹配

排序查询

对表中的记录进行升序asc或者降序desc的排列，默认的是升序asc，同时需要使用order by关键字：

  • 升序：asc，默认情况
  • 降序：desc

查询年龄age字段，默认是升序的情况：

指定降序的排列：

聚合查询

聚合查询使用的是聚合函数：

  • avg()：求均值
  • sum()：求和
  • max()：最大值
  • min()：最小值
  • count()：求个数，统计值
1
2
3
4
select count(*) from user;    -- 总记录
select sum(age) from user;  -- 总和
select avg(age) from user;   -- 平均值
select max/min(age) from user;  -- 最大/小值

限制查询limit

有时候我们只想查询部分数据，防止查崩数据库，可以使用limit关键字来进行限制输出的数目：

  • 直接使用limit
  • 使用limit ... offset ...：指定从哪里开始显示，显示多少行
  • 简写：limit 3, 2：表示从第3行开始，显示2行数据
1
2
3
select name, age from user limit 3;    -- 只显示3行数据
select name, age from user limit 3 offset 2;   -- 从第4（offset）行开始显示3(limit)行数据
select name, age from user limit 2, 3 ;  -- 效果同上：逗号之前是offset的内容，逗号之后是limit内容

DQL语言是使用最为广泛的，本文中只是简单地介绍了基本查询语句，后续会介绍多种进阶的查询。

本文标题:MySQL4种语言

发布时间:2020年12月09日 - 11:12

原始链接:http://www.renpeter.cn/2020/12/09/MySQL4%E7%A7%8D%E8%AF%AD%E8%A8%80.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

Coffee or Tea