SQL学习笔记（学自黑马）---基本SQL语句

DDL：操作数据库、表

1. 操作数据库：CRUD

1.1. C（Create）：创建

• 创建数据库：
  • create database 数据库名称;
• 创建数据库，判断不存在，再创建：
  • create database if not exists 数据库名称;
• 创建数据库，并指定字符集：
  • create database 数据库名称 character set 字符集名称;
• 练习：创建db4数据库，判断是否存在，并制定字符集为gbk。
  • create database if not exists db4 character set gbk;

1.2. R（Retrieve）：查询

• 查询所有数据库的名称：
  • show database;
• 查询某个数据库的字符集：查询某个数据库的创建语句
  • show create database 数据库名称;

1.3. U（Update）：修改

• 修改数据库的字符集：
  • alter database 数据库名称 character set 字符集名称;

1.4. D（Delete）：删除

• 删除数据库：
  • drop database 数据库名称;
• 判断数据库是否存在，存在再删除：
  • drop database if exists 数据库名称;

1.5. 使用数据库

• 查询当前正在使用的数据库名称：
  • select database();
• 使用数据库
  • use 数据库名称;

2. 操作表：CRUD

2.1. C（Create）：创建

• 语法：
  • create table 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    …
    列名n 数据类型n
    );
  • 注意：最后一列，不需要加逗号
• 数据库类型：
  1. int：整数类型
     • age int;
  2. double：小数类型
     • score double(5,2);
  3. date：日期，只包含年月日，yyyy-MM-dd
  4. datetime：日期，包含年月日时分秒，yyyy-MM-dd HH:mm:ss
  5. timestamp：时间戳类型，包含年月日时分秒，yyyy-MM-dd HH:mm:ss
     • 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值。
  6. varchar：字符串
     • name varchar(20)：姓名最大20个字符
• 创建表
  create table student(
  id int,
  name varchar(32),
  age int,
  score double(4,1),
  birthday date,
  insert_time timestamp
  );
• 复制表
  • create table 表名 like 被赋值的表名;

2.2. R（Retrieve）：查询

• 查询某个数据库中所有的表名称：
  • show tables;
• 查询表的结构：
  • desc 表名;

2.3. U（Update）：修改

• 修改表名
  • alter table 表名 rename to 新表名;
  • 修改表的字符集
  • alter table 表名 character set 字符集名称;
• 添加一列
  • alter table 表名 add 列名 数据类型;
• 修改列名称/类型
  • alter table 表名 change 列名 新列名 新数据类型;
  • alter table 表名 modify 列名 新数据类型;
• 删除列
  • alter table 表名 drop 列名;

2.4. D（Delete）：删除

• drop table 表名;
• drop table if exists 表名;

DML：增删改表中的数据

1. 添加数据

• 语法
  • insert into 表名（列名1，列名2，…，列名n） values（值1，值2，…，值n）;
• 注意：
  1. 列名和值要一一对应。
  2. 如果表名后不定义列名，则默认给所有列添加值。
     insert into 表名 values（值1，值2，…，值n）;
  3. 除了数字类型，其他类型需要使用引号引起来，单双引号都可以。

2. 删除数据

• 语法
  • delete from 表名 [where 条件];
• 注意
  1. 如果不加条件，则删除表中所有记录。
  2. 如果要删除所有记录
     1. delete from 表名;（不推荐，效率低）
     2. truncate table 表名;（先删除表，再创建一张一样的表，推荐，效率高）

3. 修改数据

• 语法
  • update 表名 set 列名1=值1，列名2=值2，…，[where 条件];
• 注意
  如果不加任何条件，则会将表中所有记录全部修改。

DQL：查询表中的记录

1. 语法：

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

2. 基础查询

• 多个字段的查询
  select 字段名1，字段名2… from 表名;
  • 注意：如果查询所有字段，则可以使用*来替代字段列表。
• 去除重复:distinct
• 计算列
  • 一般可以使用四则运算进行计算一些列的值。（一般只会进行数值型的计算）
  • IFNULL(表达式1，表达式2).
    例如：
    SELECT COUNT(IFNULL(english,0)) FROM student;
    如果为空，则将english替换为0。
• 起别名：as（可省略）

3. 条件查询

• where子句后跟条件
• 运算符
  • >、<、<=、>=、=、<>
  • BETWEEN…AND
  • IN(集合)
  • LIKE
    • 占位符
      • _：单个任意字符
      • %：多个任意字符
  • IS NULL
  • and 或 &&
  • or 或 ||
  • not 或 ！

4. 排序查询

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

5. 聚合函数

• 作用：将一列数据作为一个整体，进行纵向的计算。
• 函数：
  • count：计算个数
    1. 一般选择非空列：主键。
    2. count(*)
  • max：计算最大值
  • min：计算最小值
  • sum：计算和
  • avg：计算平均值
• 注意：聚合函数的计算，会排除NULL值。
  • 解决方案：
    1. 选择不包含的非空的列
    2. IFNULL函数

6. 分组查询

• 语法：group by 分组字段;
• 注意：
  • 分组之后查询的字段：分组字段、聚合函数。
  • where和having的区别
    • where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后限定 ，如果不满足结果，则不会被查询出来。
    • where后不可以跟聚合函数，having可以进行聚合函数的判断。

7. 分页查询

• 语法：limit 开始的索引，每页查询的条数;
• 公式：开始的索引 = （当前的页码-1）*每页显示的条数。
• 注意：分页操作的limit只能在mysql中使用。

DCL：管理用户，授权

1. 管理用户

• 添加用户：
  • 语法：create user ‘用户名’@‘主机名’ identified by ‘密码’;
• 删除用户：
  • 语法：drop user ‘用户名’@‘主机名’;
• 修改用户密码：
  • 语法1：update user set password = password(‘新密码’) where user = ‘用户名’;
  • 语法2：set password for ‘用户名’@‘主机名’ = password(‘新密码’);
  • mysql中忘记了root用户的密码怎么办？
    • 以管理员身份运行cmd->net stop mysql 停止mysql的服务
    • 使用无验证启动mysql服务： mysql --skip-grant-tables
    • 打开新的cmd窗口，直接输入mysql命令，敲慧彻。就可以登陆成功
    • use mysql;
    • update user set password = password(‘新密码’) where user = ‘root’;
    • 关闭两个窗口
    • 打开任务管理器，手动结束mysqld.exe进程
    • 启动mysql服务
    • 使用新密码登陆
• 查询用户：
  • 切换到mysql数据库
    • use mysql;
  • 查询user表
    • select * from user;
  • 通配符：%表示可以在任意主机使用用户登陆数据库

2. 授权管理

• 查询权限
  • 语法：show grants for ‘用户名’@‘主机名’;
• 授予权限
  • 语法：
    • grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
    • 给予所有的权限到所有数据库和所有表中
    • grant all on . to ‘用户名’@‘主机名’;
• 撤销权限
  • 语法：revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;