SQL笔记 -- 黑马程序员

SQL目录

---

---

一、SQL分类

分类	说明
DDL	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	数据操作语言，用来对数据库表中的数据进行增删改
DQL	数据查询语言，用来查询数据库中表的记录
DCL	数据控制语言，用来创建数据库用户、控制数据库的访问权限

1、DDL

1. DDL的数据库操作

   • 查询

     • 查询所有数据库

       show datadases;
       

     • 查询当前数据库

       select datadase();
       

   • 创建

     create database [if not exists] 数据库名 [default charset 字符集] [coliate 排序规则]
     

   • 删除

     drop datadase [if exists] 数据库名
     

   • 使用

     use 数据库名
     

2. DDL的表操作

   • 查询

     • 查询当前数据库所有表

       show tables;
       

     • 查询表结构

       desc 表名
       

     • 查询指定表的建表语句

       show create table 表名
       

   • 创建

     create table 表名(字段1 字段1类型[comment 注释]，字段2 字段3类型[comment 注释]，字段3 字段4类型[comment 注释]，...字段n 字段n类型[comment 注释]
     )[comment 注释];
     

   • 修改

     • 添加字段

       alter table 表名 add 字段名 类型(长度)[comment 注释][约束];
       

     • 修改字段的数据类型

       alter table 表名 modify 字段名 新数据类型(长度);
       

     • 修改字段的数据类型

       alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束];
       

     • 删除字段

       alter table 表名 drop 字段名;
       

     • 修改表名

       alter table 表名 rename to 新表名;
       

   • 删除

     • 删除表

       drop table [if exists] 表名;
       

     • 删除指定表，并重新创建该表

       truncate table 表名;
       

---

2、数据类型

• 数值型

类型	大小	有符号（SIGNED)范围	无符号(UNSIGNED)范围
TINYINT	1 byte	(-128. 127)	(0,255)
SMALLINT	2 bytes	(-32768, 32767)	(0.65535)
MEDIUMINT	3 bytes
INT或INTEGER	4 bytes
BIGINT	8 bytes
FLOAT	4 bytes
DOUBLE	8 bytes
DECIMAL		依赖于M（精度）和D（标度）的值	依赖于M（精度）和D（标度）的值

• 字符型

类型	大小	描述
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

• 日期型

类型	大小	范围	格式
DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD
TIME	3	-838:59:59至 838:59:59	HH:MM:SS
YEAR	1	1901至2155	YYYY
DATETIME	8	1000-01-01 00:00:00至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS

---

3、DML

• 添加数据

  • 给指定字段添加数据

    insert into 表名(字段名1, 字段名2) values(值1, 值2);
    

  • 给所有字段添加数据

    insert into 表名 values (值1, 值2);
    

  • 批量添加数据

    insert into 表名(字段名1, 字段名2) values(值1, 值2), (值1, 值2);
    

    insert into 表名 values (值1, 值2), (值1, 值2);
    

• 修改数据

  update 表名 set 字段名1 = 值1, 字段名2 = 值2 [where 条件];
  

  注意：修改语句的条件可以有，如果没有，则修改整张表的所有数据。

• 删除数据

  delete from 表名 [where 条件];
  

  注意：

  • delete语句的条件可以有，如果没有，则修改整张表的所有数据。
  • delete语句不能删除某一个字段的值（可以用update）

---

4、DQL

• 语法 — 编写循序

  select 字段列表
  from 表名列表
  where条件列表
  group by分组字段列表
  having分组后条件列表
  order by排序字段列表
  limit分页参数
  

• 执行顺序

  from 表名列表where 条件列表	group by 分组字段列表having 分组后条件列表select 字段列表order by 排序字段列表limit 分页参数
  

1）基本查询

• 查询多个字段

  select 字段1, 字段2, 字段n from 表名;
  

  select * from 表名;
  

• 设置别名 - as 可以不写

  select 字段1 [as 别名], 字段2 别名, 字段n from 表名;
  

• 去除重复记录

  select distinct 字段列表 from 表名;
  

2）条件查询

• 语法

  select 字段列表 from 表名 where 条件列表;
  

• 条件

  比较运算符	功能
  <> 或 !=	不等于
  between … and …	某个范围之内(含最小，最大值)
  is null	空，null
  in()	在in之后的列表中的值，多选一
  like 占位符	模糊匹配( _ 匹配单个字符，% 匹配任意字符） ’ ‘ 单引号。‘___’ ‘%’

  逻辑运算符	功能
  and 或 &&	并且
  or 或 ||	或者
  not 或 ！	非

3）聚合函数查询

• 将一列数据作为一个整体，进行纵向计算。

  函数	功能
  count	统计数量
  max	最大值
  min	最小值
  avg	平均值
  sum	求和

• 语法

  select 聚合函数(字段列表) from 表名;
  

  NULL 不进行计算。

4）分组查询

• 语法

  select 字段列表 from [where 条件] group by 分组字段名 [having 分组后过滤条件];
  

• where与having区别
  执行时机不同： where是分组之前进行过滤不满足where条件，不参与分组;而having是分组之后对结果进行过滤。

  判断条件不同:：where不能对聚合函数进行判断，而having可以。

• 注意
  执行顺序:：where > 聚合函数 > having 。
  分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

5）排序查询

• 语法

  select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;
  

• 排序方式

  • asc — 升序（默认）
  • desc — 降序

• 注意：

  如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

6）分页查询

• 语法

  select 字段列表 from 表名 limit 起始索引, 查询记录;
  

• 注意

  • 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数。
  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
  • 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 查询记录。

---

5、DCL

• 管理用户

  • 查询用户

    use mysql;
    select * from user;
    

  • 创建用户

    create user '用户名'@'主机名' identified by '密码';create user '用户名'@'%' identified by '123456';# 任意主机用%表示
    

  • 修改用户密码

    alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
    

  • 删除用户

    drop user '用户名'@'主机名';
    

    注意：

    1. 主机名可以使用 % 通配。

• 权限控制

  权限	说明
  all, all privileges	所有权限
  select	查询数据
  insert	插入数据
  update	修改数据
  delete	删除数据
  alter	删除表
  drop	删除数据/表/视图
  create	创建数据库/表

  • 查询权限

    show grants for '用户名'@'主机名';
    

  • 授予权限

    grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
    

  • 撤销权限

    revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
    

    注意：

    1. 多个权限之间，用逗号分隔开。
    2. 授权时，数据库名和表名可以使用 * 进行通配，表示所有。