SQL 入门

April 6, 2019 ☼ MySQLMariaDBSQLRDBMSDatabase

What is SQL?

SQL 全称是 Structured Query Language(结构化查询语言),是一种编程语言,用于管理存储在关系数据库管理系统(RDBMS)中的数据。

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS).

from https://en.wikipedia.org/wiki/SQL

SQL 于 1987 年成为国际标准。

SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.

SQL 由许多类型的语句组成,包括数据查询语言(DQL)、数据定义语言(DDL)、数据访问控制语言(DCL)、数据操作语言(DML)。

SQL consists of many types of statements, which may be informally classed as sublanguages, commonly: a data query language (DQL), a data definition language (DDL), a data control language (DCL), and a data manipulation language (DML). The scope of SQL includes data query, data manipulation (insert, update and delete), data definition (schema creation and modification), and data access control.

每种类型的语句包含了不同的子语句:

MySQL 对语句的分类略有不同,同样有 DDL:

也有 DML,但 SELECT 也被归为这一类,因此就没有了 DQL 这个分类:

而用于控制数据访问权限的 GRANTREVOKE 所在的分类也不叫做 DCL:

我个人的理解是,DDL 用于管理关系数据库中「表」这个级别的对象,而 DML 用于管理「行」这个级别的对象,而所谓的「管理」大多数时候就是增、删、改、查。

常用 SQL 语句

以下记录的除了标准的 SQL 语句,还包括了可用于 MySQL 和 MariaDB 这两个 RDBMS 的管理语句。

管理 MariaDB server

显示版本号

select version();

显示进程列表

show processlist;

查看 MariaDB server 所在的主机名

-- method 1
-- `@@var_name` 是一个系统变量
-- `@var_name` 则是用户自定义的变量
select @@hostname;

-- method 2
show variables like 'hostname';

查看 MariaDB server 安装目录 2018-05-15

show variables like 'basedir';

查看 MariaDB server 监听端口 2018-05-15

show variables like 'port';

管理用户

查看用户列表

select user,host from mysql.user;

创建用户

create user 'test'@'172.16.125.71' identified by 'centos';

删除匿名用户

drop user ''@'localhost';

修改密码

-- Method 1a (MySQL-server version >= 5.7.6)
alter user 'test'@'172.16.125.71' identified by 'new_passwd';

-- Method 1b (MySQL-server version <= 5.7.5)
SET PASSWORD FOR 'user-name-here'@'hostname' = PASSWORD('new-password');

-- Method 2
UPDATE mysql.user SET Password=PASSWORD('new-password-here') 
WHERE USER='user-name-here' AND Host='host-name-here';

授予用户权限

grant all on tysql.* to 'test'@'172.16.125.71';

查看用户权限

show grants for 'test'@'172.16.125.%';

更改用户权限

-- 更改前:用户只能从一台主机远程访问
-- 更改后:用户可以从网段内的所有主机远程访问

-- step 1: update the mysql.user table
update mysql.user set host='172.16.125.%' 
where user='test' and host='172.16.125.71';

-- `172.16.125.%` 表示 `172.16.125.0/24` 这个网段
-- MariaDB 不支持 CIDR 写法,只能写成 `172.16.125.0/255.255.255.0`

-- step 2: update the mysql.db table
update mysql.db set host='172.16.125.%' 
where user='test' and host='172.16.125.71';

-- step 3
flush privileges;

更改用户权限还可以先撤销再重新授予,比 update 命令更简单:

-- 2018-08-15 updated

-- 显示现有的权限,作为重新授权的参考
show grants for 'repluser'@'172.16.125.%';

revoke all privileges on *.* from 'repluser'@'172.16.125.%';

grant replication slave on *.* to repluser@'172.16.125.%' identified by 'centos' require ssl;

flush privileges;

管理数据库

删除数据库

drop database test;

删除表 2019-04-01

drop table `Person`;

删除行 2019-04-06

delete Person -- Person 就是要从其中删除行的表名
from Person
where Id = 2;

创建表 2019-04-01

create table if not exists Person
(Id int, Email varchar(255));

查看表结构 2019-04-01

desc `Employee`;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| Id     | int(11) | YES  |     | <null>  |       |
| Salary | int(11) | YES  |     | <null>  |       |
+--------+---------+------+-----+---------+-------+

修改表结构——增加列 2019-04-01

-- 在 Employee 表的 Id 列的后面添加新的一列,
-- 新列的名称是 Name,数据类型是 varchar(255)
alter table Employee
add column Name varchar(255)
after Id;

-- add 后面的 column 可以省略
alter table `Employee`
add ManagerId int;

desc `Employee`;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Id        | int(11)      | YES  |     | <null>  |       |
| Name      | varchar(255) | YES  |     | <null>  |       |
| Salary    | int(11)      | YES  |     | <null>  |       |
| ManagerId | int(11)      | YES  |     | <null>  |       |
+-----------+--------------+------+-----+---------+-------+

修改表结构——删除列 2019-04-12

alter table `Employee`
drop column ManagerId;

explain `Employee`;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| Id     | int(11)      | YES  |     | <null>  |       |
| Name   | varchar(255) | YES  |     | <null>  |       |
| Salary | int(11)      | YES  |     | <null>  |       |
+--------+--------------+------+-----+---------+-------+

清除表中原有数据 2019-04-01

-- 生产环境中一定要慎用!
truncate table `Employee`;

批量插入多行数据 2019-04-01

insert into `Employee`
    (Id, Name, Salary, ManagerId)
values
    ('1', 'Joe', '70000', '3'),
    ('2', 'Henry', '80000', '4'),
    ('3', 'Sam', '60000', null),
    ('4', 'Max', '90000', null);
    
select * from `Employee`;
+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | <null>    |
| 4  | Max   | 90000  | <null>    |
+----+-------+--------+-----------+