数据库SQL语言

发表于 更新于 阅读次数： Changyan： 本文字数： 3.6k 阅读时长 ≈ 3 分钟
  1. DDL (Data Definition Language): 数据定义语言
  2. DML (Data Manipulation Language)：数据操作语言
  3. DCL (Data Control Language): 数据控制语言
  4. DQL (Data Query Language): 数据查询语言
  • ddl：数据库或表的结构操作(****)
  • dml：对表的记录进行更新(增、删、改)(****)
  • dql：对表的记录的查询(****，难点)
  • dcl：对用户的创建及授权(****)

DDL

一、数据库

  • 查看所有数据库：

    1
    SHOW DATABASES

  • 切换数据库：

    1
    USE DATABASENAME

  • 创建数据库：

    1
    CREATE DATABASE IF NOT EXISTS MYDB CHARSET=utf8

  • 修改数据库：

    1
    AlTER DATABASE MYDB CHARSET SET utf8

  • 删除数据库

    1
    drop database MYDB

数据类型

  • int：整型;
  • double ：浮点型，例如 double(5,2)表示最多 5 位，其中必须有 2 位小数，即最大值为 999.99；
  • decimal：浮点型 ，在表单钱方面使用该类型，因为不会出现精度缺失问题；
  • char：固定长度字符串类型，char(255);
  • varchar: 可变字符串类型：varchar(65535);
  • text(clob):字符串类型；
  1. tinytext 28 - 1 Byte
  2. text 216 - 1 Byte
  3. mediumtext 224 - 1 Byte
  4. longtext 234 - 11 Byte
  • blob ：字节类型；
  1. tinyblob 28 - 1 Byte
  2. blob 216 - 1 Byte
  3. mediumblob 224 - 1 Byte
  4. longblob 234 - 1 Byte
  • date：日期类型，格式为：yyyy-MM-dd;
  • time: 时间类型 格式为：hh:mm:ss
  • timestamp: 时间戳类型；

二、表

  • 创建表：
1
CREATE TABLE [IF NOT EXISTS] TABLENAME (cloumn01 type01,cloumn02 type02,cloumn03 type03);
  • 查看当前数据库中所有表名称：SHOW TABLES;
  • 查看指定表的创建语句：SHOW CREATE TABLE 表名(了解)；
  • 查看表结构：DESC 表名;
  • 删除表结构：DROP TABLE 表名;
  • 修改表：前缀：ALTER TABLE 表名;
  1. 修改之添加列：
    AlTER TABLE 表名 ADD （
    列名 列类型，
    列名 列类型，
    …
    ）
  2. 修改列类型（如果被修改的列已存在数据，那么新的列类型可能会影响到已存在数据）：ALTER TABLE 表名 MODIFY 列名 列类型
  3. 修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 新类型
  4. 删除列：ALTER TABLE 表名 DROP 列名
  5. 修改表名称：ALTER TABLE 原表名 RENAME TO 新表名

DML

  • 添加表记录
1
INSERT INTO tableName (c1,c2,c3)values(v1,v2,v3);
  • 修改表记录
1
2
3
update tableName set c1 = v1,c2 = v2;
update tableName set c1 = v1,c2 = v2 where c3 = v3 or c4 = v4;
delete * from stu where gender is null;

DCL

  • 一个项目创建一个用户！一个项目对应的数据库只有一个！
  • 这个用户只能对这个数据库有权限，其他数据库不能操作
  1. 创建用户
  • 在指定的 IP 地址上登录
1
CREATE USER username@IP IDENTIFIED BY 'password'
  • 在任意的 IP 地址上登录
1
CREATE USER username@'%' IDENTIFIED BY 'password'
  1. 给用户授权
1
2
3
4
-- 部分权限授权
 GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb.* TO shawn@IP
-- 所有权限授权
GRANT ALL ON mydb.* TO username@IP
  1. 撤销授权
1
REVOKE CREATE,ALTER,DROP,UPDATE,DELETE ON mydb.* FROM username@IP
  1. 查看权限
1
SHOW GRANTS FOR username@IP
  1. 删除用户
1
DROP USER username@IP

DQL

1
SELECT * FROM tableName;

常用函数

1
2
3
4
5
6
7
8
9
IFNUll(c1,v1);
DATE_FORMAT("yyyy-MM-dd",'%Y-%m-%d');
CONCAT(v1,v2,v3);
distinct;
-- '_'匹配一个字符
-- '%'匹配零个到N个字符
like '_%';
-- （当前页-1）* 每页记录数
limit (17-1)*8,8

聚合函数

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
-- 统计 employees 表中的记录数量
SELECT COUNT(*) FROM employees;

-- 统计 employees 表中 department_id 列不为空的记录数量
SELECT COUNT(department_id) FROM employees;

-- 计算 employees 表中 salary 列的平均值
SELECT AVG(salary) FROM employees;

-- 找出 employees 表中 salary 列的最大值
SELECT MAX(salary) FROM employees;

-- 找出 employees 表中 salary 列的最小值
SELECT MIN(salary) FROM employees;

-- 按 department_id 分组，统计每个部门的员工数量
SELECT department_id, COUNT(*) 
FROM employees
GROUP BY department_id;

-- 按 department_id 分组，计算每个部门的平均工资
SELECT department_id, AVG(salary) 
FROM employees
GROUP BY department_id;

-- 按 department_id 分组，计算每个部门的平均工资，筛选出平均工资大于 5000 的部门
SELECT department_id, AVG(salary) 
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

联合查询

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
-- 查询 table1 和 table2 的 column1 列，去除重复行
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

-- 查询 table1 和 table2 的 column1 列，保留所有行
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

-- 查询订单信息及对应的客户信息
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.id;

-- 查询所有客户信息及他们的订单信息，如果客户没有订单，订单信息为 NULL
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

-- 查询所有订单信息及对应的客户信息，如果订单没有对应的客户，客户信息为 NULL
SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.id;

-- 模拟 FULL OUTER JOIN，查询所有客户和订单信息，不管是否匹配
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
UNION
SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;