SQL——DDL、DML、DQL、DCL

数据库的概念

1. DataBase 简称DB
2. 数据库：用于存储和管理数据的仓库
3. 数据库的特点
   持久化存储数据，数据库就是一个文件系统；
   方便存储和管理数据；
   使用了统一的方式操作数据库–SQL

SQL

1. Structured Query Language：结构化查询语言
   定义了操作所有关系型数据库的规则；
   每一种数据库操作的方式存在不一样的地方，称为“方言”
2. SQL通用语法
   SQL语句可以单行或多行书写，以分号结尾；
   可以使用空格和缩进来增强语句的可读性；
   MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

三种注释

单行注释：-- 注释内容或 # 注释内容
（MySQL特有的书写方式，"#“后可以不加空格，”–"后必须加空格）

多行注释：/* 注释 */

3. SQL分类
   • DDL（Data Definition Language）数据定义语言
     用来定义数据库对象：数据库、表、列等。关键字：create，drop，alter等
   • DML（Data Manipulation Language）数据操作语言
     用来对数据库中表的数据进行增删改。关键字：insert，delete，update等
   • DQL（Data Query Language）数据查询语言
     用来查询数据库中表的记录（数据）。关键字：select，where等
   • DCL（Data Control Language）数据控制语言（了解）
     用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT,REVOKE等

DDL

操作数据库CRUD

1. C （create）：创建

• 创建数据库：
  create database 数据库名称;

• 创建数据库，判断不存在，再创建：
  create database if not exists 数据库名称;

• 创建数据库，并指定字符集：
  create database 数据库名称 character set 字符集名;

• 创建db数据库，判断是否存在，并指定字符集gbk;
  create database if not exists db character set gbk;

2. R （Retrieve）：查询

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

3. U （Update）：修改
   修改数据库中的字符集
   alter database 数据库名称 character set 字符集名称;
4. D （Delete）：删除

• 删除数据库
  drop database 数据库名称;
• 判断数据库是否存在，存在再删除
  drop database if exists 数据库名称;
• 使用数据库
  • 查询当前正在使用的数据库名称
    select database();
  • 使用数据库
    use 数据库名称;

操作表

1. C （create）：创建
   语法：

    	create table 表名(
    		列名1 数据类型1,
    		列名2 数据类型2,
    		...
    		列名n 数据类型n
    	);
   

   注意：最后一列，不需要加逗号 “,”

数据类型

• int:整数类型
  age int,
• double:小数类型
  score double(5,2); – 共五位数字，小数点后两位
• date:日期，只包括年月日，yyyy-MM-dd
• datetime:日期，包括年月日时分秒 yyyy-MM-dd HH:mm:ss
• timestamp:时间戳类型 包括年月日时分秒 yyyy-MM-dd HH:mm:ss
  如果将来不给这个字段赋值，或者赋值为null，则默认使用当前系统时间，来自动赋值
• varchar:字符串
  name varchar(20):姓名最大20字符
  zhangsan 8个字符 张三 2个字符

例如：

		创建表
			create table student(
				id int,
				name varchar(32),
				age int,
				score double(4,1),
				birthday date,
				insert_time timestamp
			);
		复制表：
			create table 表名 like 被复制的表名;

2. R （Retrieve）：查询

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

3. 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 表名;

DML：增删改表中的数据

添加数据：

语法：

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

注意：

• 列名和值要一 一对应
• 如果表名后，不定义列名，则默认将所有的列进行赋值
  insert into 表名 values(值1,值2,…值n);
• 除了数字类型，其他类型需要使用单引号或双引号 引起来

删除数据：

语法：

	delete from 表名 [where 条件];

注意：

• 如果不加条件，则删除表中所有记录
• 如果要删除所有记录
  delete from 表名;-- 不建议使用，会执行n条记录的删除操作，效率低
  TRUNCATE TABLE 表名;-- 推荐使用，先删除表，然后创建一张一样的表，效率高

修改数据：

语法：

	update 表名 set 列名1 = 值1,列名2 = 值2, ... [where 条件];

注意：
如果不加条件，则会将表中所有记录全部修改

DQL：查询表中的记录

语法：

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

1、基础查询
多个字段的查询

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

注意：

• 如果查询所有字段，可以使用 * 来代替字段列表
• 去除重复 DISTINCT
• 计算列
  一般可以使用四则运算计算一些列的值（一般只会进行数值型计算）
• IFNULL(表达式1,表达式2) ：null参与的运算，计算结果都为null
  表达式1：哪个字段需要判断是否为null
  表达式2：如果该字段为null后的替换值
• 起别名
  AS（可省略）

2、排序查询

• 语法：order by 子句

    order by 排序字段1 排序方式1,  排序字段2 排序方式2 ...
  

• 排序方式：
  ASC:升序，默认
  DESC:降序

• 注意：
  如果多个排序条件，则将前面的条件值一样时，才会判断第二条件

3、聚合查询:将一列数据作为一个整体，进行纵向计算

• count: 计算个数
  SELECT COUNT(列名) from 表名;
  一般选择非空的列:主键
  count(*)
• max: 计算最大值
  SELECT MAX(列名)from 表名;
• min: 计算最小值
  SELECT MIN(列名)from 表名;
• sum:计算和
  SELECT SUM(列名)from 表名;
• avg:计算平均值
  SELECT AVG(列名)from 表名;
• 注意：聚合函数的计算，排除null值
  • 解决方案：
    选择不包含非空的列进行计算
    IFNULL函数
    SELECT COUNT(IFNULL(列名,0)) FROM 表名;

分组查询：

语法：group by 分组字段
注意：
分组之后查询的字段：分组字段，聚合函数
where和having区别

• where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
• where后不可以跟聚合函数，having可以进行聚合函数的判断

分页查询

• 语法：limit 开始的索引，每页查询的条数
  SELECT * FROM 表名 LIMIT 开始的索引,每页查询的条数;
• 公式：开始的索引 = （当前的页码-1）*每页显示的条数
  limit只能在MySQL中使用

条件查询

where 子句后跟条件

• 运算符
  > 、<、<=、>=、=、<> 或 !=
• BETWEEN…AND
  where 列名 BETWEEN 条件1 AND 条件2
  例：where age BETWEEN 15 AND 20
• LIKE 模糊查询
  • 占位符
    _ :单个任意字符
    % :多个任意字符
  • 例如
    • 查询姓名中第一个字为张的用户
      WHERE u_name LIKE ‘张%’
    • 查询姓名中第二个字为三的用户
      WHERE u_name LIKE ‘_三%’
    • 查询姓名为三个字的用户
      WHERE u_name LIKE ‘___’
    • 查询姓名中含有张的用户
      WHERE u_name LIKE ‘%张%’
• IS NULL
  查询表中为null值的数据时不能用"=“或”!="判断
  where 列名 IS [NOT] NULL
  *例：WHERE score IS [NOT] NULL
• and 或 &&
  where 条件1 and/&& 条件2
  *例：where age<=20 and/&& age>15;
• or 或 ||
  WHERE 条件1 OR/ || 条件2 OR/ || 条件3 …
  例：
  WHERE age=15 OR age=18 or age=21
  或
  WHERE age IN(15,18,21)
• not 或 ！

DCL

管理用户

1、添加用户
语法：
– 创建用户
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED by ‘密码’;
CREATE USER ‘zhangsan’@‘localhost’ IDENTIFIED by ‘123’;
2、删除用户
语法：
– 删除用户
DROP USER ‘用户名’@‘主机名’;
DROP USER ‘zhangsan’@‘localhost’;
3、修改用户密码
语法：
– 修改用户密码
UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;
UPDATE USER SET PASSWORD = PASSWORD(‘abc’) WHERE USER = ‘zhangsan’;

SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);
SET PASSWORD FOR ‘zhangsan’@‘localhost’ = PASSWORD(‘000’);
4、查询用户

• 切换到mysql数据库
  USE mysql;
• 查询user表
  SELECT * FROM USER;
  通配符：% 表示可以在任意主机使用用户登录数据库

mysql中忘记root用户的密码

• cmd -> net stop mysql 停止mysql服务
  需要管理员运行该cmd
• 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
  打开新的cmd窗口，直接输入mysql命令，敲回车就可以登录成功
• use mysql;
• UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘root’;
• 关闭两个窗口
• 打开任务管理器，手动结束mysqld.exe的进程
• 启动mysql服务
• 使用新密码登录

授权

1、查询权限
SHOW GRANTS FOR ‘用户名’@‘主机名’;
SHOW GRANTS FOR ‘zhangsan’@‘localhost’;

2、授予权限
grant 权限列表 on 数据库.表名 to ‘用户名’@‘主机名’;
GRANT SELECT,DELETE ,UPDATE ON db.user TO ‘zhangsan’@‘localhost’;

– 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON * . * TO ‘zhangsan’@‘localhost’;

3、撤销权限：
revoke 权限列表 on 数据库.表名 from ‘用户名’@‘主机名’;
revoke UPDATE on db.user from ‘zhangsan’@‘localhost’;