面试必备的SQL基础语法：SQL四大分类

面试必备的SQL基础语法：SQL四大分类

1、数据库的概念：

1. 数据库的英文单词：DATABASE；
2. 数据库：用于存储和管路数据的仓库。
3. 数据库的特点：
   • 持久化存储数据。其实数据库就是一个文件系统。
   • 方便存储和管理数据。
   • 使用统一的方式操作数据库。 ——SQL。
4. 常见的数据库软件：
   • MySQL
   • Oracle
   • SQL server

2、SQL

2.1.1 概念：

• SQL【Structured Query Language】：结构化查询语言。
• 定义了操作所有关系型数据库的规则。
• 每个数据库操作的方式存在不一样的地方，称为“方言”。

2.1.2 通用语法：

1. SQL语句可以单行或多行书写，以分号结尾。
2. 可以使用制表符或空格增加文件的可读性。
3. 不区分大小写，关键字建议使用大写。
4. 注释：
   • 单行注释：
     • --注释内容
     • #注释内容
   • 多行注释：
     • /*注释内容 */
   • 注释符号与注释内容之间必须以空格分隔。

3、 SQL的四大分类

• DDL：【Data Difinition Language】 数据定义语言，一般用来对数据库和表进行创建和删除操作；
• DML：【Data Manipulation Language】 数据操作语言，一般用来做表中数据的增加和删除；
• DQL：【Data Query Language】 数据查询语言，用来在数据库中查询的语言；
• DCL：【Data Control Language】 数据控制语言，一般是DBA才会用到的数据库用户管理及日常维护语言。

3.1 DDL：操作数据库、表

3.1.1 操作数据库：【CRUD】

• C【Create】：创建

  • 创建数据库：CREATE DATABASE 数据库名称；
  • 先判断后创建： CREATE DATABASE IF NOT EXISTS 数据库名;
  • 创建并指定字符集；： CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;
  • 先判断 后创建指定字符集的数据库： CREATE DATABASE IF NOT EXISTS 数据库名称 CHARACTER SET 字符集名称;

• R【Retrieve】：查询

  • 查询所有数据库的名称： SHOW DATABASES;
  • 查询某个数据库的字符集：SHOW CREATE DATABASE 数据库名称;

• U【Update】：修改

  • 修改数据库的字符集：ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;

• D【Delete】：删除

  • 删除指定的数据库：DROP DATABASE 数据库名称;
  • 判断数据库是否存在，再进行删除：DROP DATABASE IF EXISTS 数据库名称;

• 使用数据库

  • 查询正在使用的数据库名称： SELECT DATABASE();
  • 使用指定的数据库： USE 数据库名称;

3.1.2 操作表【CRUD】

• C【Create】：创建

  • 复制表： CREATE TABLE 新表名 LIKE 旧表名;
  • 语法

  CREATE TABLE 表名(
  		字段名称1 值1,
  		字段名称2 值2,
  		...
  		字段名称n 值n
  );
  

  • 案例

  	CREATE TABLE student(
              id INT,
              NAME VARCHAR(3),
              age INT,
              score DOUBLE(4,1),
              birthday DATE,
              TIME TIMESTAMP
          );
  

  • 注意：最后一列，不需要加逗号。
  • 数据库类型：
    

• R【Retrieve】：查询

  • 查询数据库中所有的表名称：SHOW TABLES;
  • 查询表结构：DESC 表名;

• U【Update】：修改

  • 修改表名： ALTER TABLE 表名 RENAME TO 新表名;
  • 修改表的字符集： ALTER TABLE 表名 CHARACTER SET 字符集名称;
  • 添加一列： ALTER TABLE 表名 ADD 字段名称 字段类型;
  • 修改字段名称、类型： ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
  • 只修改字段类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型;
  • 删除列：ALTER TABLE 表名 DROP 字段名;

• D【Delete】：删除

  • 删除指定的表： DROP TABLE 表名;
  • 先判断表是否存在，再进行删除：DROP TABLE IF EXISTS 表名;

3.2 DML：增删改表中数据

3.2.1 添加数据

• 语法：INSERT INTO 表名(列1， 列2， ... 列N) VALUES (值1， 值2， ... 值3);
• 注意：
  • 列名要和值一一对应。
  • 如果表名后，不定义列名，则默认给所有字段添加值。
    • insert into 表名 values （值1， 值2 ... 值n）
    • 值必须和列完全匹配，否则将报错。
  • 除了数字之外的所有类型，赋值时，必须使用引号（单双都可以）。

3.2.2 删除数据

• 删除指定字段的数据：DELETE FROM 表名 【where 字段=值】
• 删除全部数据：
  • DELETE FROM 表名；– 不推荐；有多少条数据进行多少次删除操作，效率低下。
  • TRUNCATE TABLE 表名;– 效率更高 先删除表，再创建一个一模一样的空表。

3.2.3 修改数据

• 修改指定字段的指定列数据。UPDATE 表名 SET 列名1=值1，列名2=值2，... 列名n=值n WHERE 字段=值；
• 注意事项
  • 如果不加where条件，将会把表中所有数据都进行修改。

3.3 DQL：查询表中记录

3.3.1 语法

• select
  字段列表
• from
  表名列表
• where
  条件列表
• group by
  分组字段
• having
  分组之后的条件
• order by
  排序
• limit
  分页限定

3.3.2 基础查询

• 多个字段查询：SELECT 字段1，字段2... FROM 表名;
  • 注意：
    • 如果查询多个字段可以使用：select * from 表名；
• 去除重复：SELECT DISTINCT 字段1，字段2... FROM 表名;
• 计算列：
  • SELECT 字段1 + 字段2 ... FROM 表名;
  • 可以使用四则运算；一般只会进行数值型计算。
  • 如果想取出NULL：
    • 可以使用 ifnull（可能出现null的字段, 期望的值）。
• 起别名
  • SELECT 字段1 AS 别名1, 字段1 + 字段2 AS 别名2 ... FROM 表名;
  • AS可以省略不写，但之间必须有一个或多个空格。
• 实例：

      SELECT
      	id AS 学号,
      	NAME AS 姓名,
      	age AS 年龄,
     	 	sex AS 性别,
      	math + IFNULL(english,0) AS 总分
  	FROM
      	student;
  	```
  

3.3.3 条件查询

• where子句后跟条件
• 运算符
  • ＞ ，＜, <= , >= ， =，<>
  • between … and
    • where 字段名 between 左区件 and 右区间。
  • in（集合）
  • like ：模糊查询
  • is null – 判断null
  • and 或 &&
  • or 或 ||
  • not 或 ！
• 占位符
  • _：单个任意字符
  • %：多个任意字符，0或多个

3.3.4 排序查询

• 语法：
  • order by 子句
  • order by 字段1 排序1，字段2 排序2…
• 排序方式：
  • ASC：升序，默认
  • DESC：降序。
• 注意：
  • 若省略排序条件，则默认为升序。
  • 如果多个排序条件时，则当前面条件值一样时，才会判断第二条件。

3.3.5 聚合函数

• 概念：将一列数据作为一个整体，进行纵向计算。
• 语法：select 聚合函数 form 表名;
• count ：计算个数
  • 一般选择非空的列：主键。
  • count（*）
• max：计算最大值
• min：计算最小值
• sum：计算和
• avg：计算平均值。、
• 实例：

      SELECT
       	COUNT(id) AS 总数, 
       	MAX(math) AS 数学最高分, 
       	SUM(IFNULL(math,0)) AS 数学总分,
       	AVG(math) AS 数学平均分, 
       	MIN(english) AS 英语最低分 
       FROM 
       	student;
  

• 注意
  • 聚合函数的计算，会排除null值。
  • 选择不包含非空的列进行计算。
  • 使用ifnull函数。

3.3.6 分组查询

• 语法：
  • select 分组字段，聚合函数1...n from 表名 where 分组条件 group by 分组的字段 having 分组后条件。
• 注意：
  • 分组之后查询的字段：分组字段和聚合函数，不能为其他字段。
• Where和havin区别
  • where 在分组之前进行限定，如果不满足条件则不参与分组；
    havin在分组之后进行限定，如果不满足条件则不会被查询出来。
  • where之后不可以跟聚合函数；having后可以跟聚合函数。

3.3.7 分页查询

• 语法
  • select 查询字段 from 表名 limit 开始的索引，每页查询的条数。
• 公式：
  • 开始的索引 = （当前的页码-1） × 每页显示条数。
  • limit 是一个mysql的方言

3.4 DCL：管理用户、授权

• %：代表任意IP地址。

3.4.1 管理用户

• 添加用户：
  • 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
  • 案例：CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123';
• 删除用户：
  • 语法：DROP USER '用户名'@'主机名';
  • 案例：DROP USER 'zhangsan'@'localhost';
• 修改密码：
  • 语法：
    • UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
    • SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
  • 案例：
    • UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
    • SET PASSWORD FOR 'lisi'@'%' = PASSWORD('abc');
  • MySQL中忘记root用户的密码：
    1. cmd --> net stop mysql 停止mysql服务。
       • 需要以管理员身份运行
    2. 使用无验证方式启动mysql服务：mysqld --skip-grant-tables
    3. 打开新的cmd窗口，直接输入mysql命令，就可以登录成功。
    4. use mysql; 使用mysql数据库
    5. update user set password = password('新密码') where user = 'root';设置新密码
    6. 关闭所有窗口，并在任务管理器中手动结束 mysqld.exe进程。
    7. cmd --> net start mysql 启动mysql服务
    8. 使用新的密码登录数据库。
• 查询用户：
  • 语法：SELECT * FROM USER;

3.4.2 授权

• 查询权限：

  • 语法：SHOW GRANTS FOR '用户名'@'主机名';
  • 案例：SHOW GRANTS FOR 'lisi'@'%';

• 授予权限：

  • 语法：GRANT 权限1, 权限2...权限n ON 数据库名.表名 TO '用户名'@'主机名';
  • 案例：
    • GRANT SELECT,UPDATE ON db1.`emp` TO 'lisi'@'%';
    • GRANT ALL ON *.* TO 'lisi'@'%';
      • all：全部权限的通配符。
      • *.*：全部数据库的通配符。

• 撤销权限：

  • 语法：REVOKE 权限1,权限2...权限n ON 数据库名.表名 FROM '用户名'@'主机名';
  • 案例：REVOKE UPDATE ON db1.* FROM 'lisi'@'%';