SQL入门

2018-03-03|Categories: Database|

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).

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.

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

  • DDL = Data Definition Language:
    • CREATE
    • DROP
    • ALTER
  • DML = Data Manipulation Language:
    • INSERT
    • DELETE
    • UPDATE
  • DCL = Data Control Language:
    • GRANT
    • REVOKE
  • DQL = Data Query Language:
    • SELECT

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

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

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

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

常用SQL语句

以下记录的除了标准的SQL语句,还包括了可用于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;

Leave A Comment