一 SQL概述

1.1 数据库的好处

1）实现数据持久化
2）使用完整的管理系统统一管理，易于查询

1.2 SQL相关操作概述

1）DQL（Data Query Language）：数据查询语言(select)。
2）DML（Data Manipulation Language）：数据操纵语句，用于添加、删除、修改数据库记录，并检查数据完整性。
3）DDL（Data Definition Language）：数据定义语句，用于库和表的创建、修改、删除。
4）DCL（Data Control Language）：数据控制语句，用于定义用户的访问权限和安全级别。
5）TCL（Transaction Control Language）：事务控制语言（commit，rollback）。

1.3 DML数据操纵语句

1）INSERT：添加数据到数据库中
2）UPDATE：修改数据库中的数据
3）DELETE：删除数据库中的数据

1.4 DDL数据定义语句

CREATE TABLE：创建数据库表
ALTER TABLE：更改表结构、添加、删除、修改列长度
DROP TABLE：删除表
CREATE INDEX：在表上建立索引
DROP INDEX：删除索引

1.5 DCL数据控制语句

GRANT：授予访问权限
REVOKE：撤销访问权限

1.6 TCL事务控制语句

COMMIT：提交事务处理
ROLLBACK：事务处理回退
SAVEPOINT：设置保存点
LOCK：对数据库的特定部分进行锁定

二 SQL常用命令

2.1 基本命令

show databases：查看所有的数据库
use 【库名】：打开指定的库
show tables：显示库中的所有表
show tables from 库名：显示指定库中的所有表
desc 【表名】：查看指定表的结构

2.2 查看MySQL版本

1）连接到MySQL

select version();

2）不连接到MySQL，Linux命令

mysql --version 或 mysql --V

三 DQL数据查询

3.1 基本查询

1）语法：select 查询列表 from 表名

# 查询单个字段
select sname from student;
# 查询多个字段
select sname,sid from student;
# 查询所有字段
SELECT * FROM student;

2）查询常量值

SELECT 100;
SELECT 'john';

3）查询表达式

SELECT 100%98;

4）查询函数

select VERSION();

5）给查询的字段起个别名（使用as，或者省略）

SELECT sname as name, sage  as age
FROM student;

6）去重，当查询到数据有多行重复可以选择去重（distinct）

SELECT DISTINCT ssex FROM student

7）+ ：运算符，仅做数学计算

SELECT (2+3)

3.2 条件查询

1）语法

select 
    查询列表
from
    表名
where
    筛选条件;

2）按条件表达式筛选

>    <   =    !=     <>    >=     <=

3）按逻辑表达式筛选

&& || !
and or not
&& 和 and：两个条件都为true，结果为true，反之为false
|| 或 or： 只要有一个条件为true，结果为true，反之为false
! 或 not： 如果连接的条件本身为false，结果为true，反之为false

4）模糊查询

like
between and
in
is null
is not null

3.3 排序查询

1）asc 代表的是升序，可以省略。desc代表的是降序
2）order by 子句可以支持单个字段、别名、表达式、函数、多个字段
3）order by 子句在查询语句的最后面，除了limit子句

select 查询列表
from 表名
【where  筛选条件】
order by 排序的字段或表达式;

-- 多个字段排序
SELECT *
FROM employees
ORDER BY salary DESC, employee_id ASC;

3.4 常见函数

1）概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
2）好处：1、隐藏了实现细节 2、提高代码的重用性
3）调用：select 函数名(实参列表) 【from 表】
4）特点：（1）叫什么（函数名） （2）干什么（函数功能）
5）分类：
（1）单行函数

• 如 concat、length、ifnull等

（2）分组函数

• 功能：做统计使用，又称为统计函数、聚合函数、组函数

  3.5 单行函数

  1）字符函数：

• length：获取字节个数(utf-8一个汉字代表3个字节，gbk为2个字节)

• concat，substr，instr，trim，upper，lower，lpad，rpad，replace

2）数学函数：

• round，ceil，floor，truncate，mod

3）日期函数：

• now，curdate，curtime，year，month，monthname，day，hour，minute，second，str_to_date，date_format

4）其他函数：

• version，database，user

5）控制函数

• if，case
  

  3.6 多表查询

  1）笛卡儿积：当查询两个表时，每条数据会相互的交互在一起形成 mn 条数据。
  2）笛卡尔积产生的原因：
  ①省略了表的连接条件
  ②连接条件无效
  ③所有表的所有行相互连接
  3）如何避免笛卡尔积：可以在 where 中加入有效的连接条件
  4）*等值连接

  SELECT e.employee_id, e.last_name, 
       e.department_id, d.department_id,d.location_id
  FROM   employees e, departments d
  WHERE  e.department_id = d.department_id;

  5）非等值连接
  常用在where中搭配的有 between-and

  SELECT e.last_name, e.salary, j.grade_level
  FROM   employees e, job_grades j
  WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

  6）自连接
  自连接是连接的一种用法，但并不是连接的一种类型，因为他的本质是把一张表当成两张表来使用。
  MySQL有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名。

  SELECT CONCAT(worker.last_name ,' works for ' 
       , manager.last_name)
  FROM   employees worker, employees manager
  WHERE  worker.manager_id = manager.employee_id ;

  7）内连接
  合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。
  8）外连接
  两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
  9）on子句：自然连接中是以具有相同名字的列为连接条件的，可以使用 ON 子句指定额外的连接条件。

  SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
  FROM   employees e JOIN departments d
  ON     (e.department_id = d.department_id);

  # 左外连接，使用 left outer join ，outer可以省略，会返回左表中不满足连接条件的行
  SELECT e.last_name, e.department_id, d.department_name
  FROM   employees e
  LEFT OUTER JOIN departments d
  ON   (e.department_id = d.department_id) ;

  # 右外连接，使用 right outer join，会返回右表中不满足连接条件的行
  SELECT e.last_name, e.department_id, d.department_name
  FROM   employees e
  RIGHT OUTER JOIN departments d
  ON    (e.department_id = d.department_id) ;

  3.7 分组查询

1. group by语句根据对结果集进行分组操作，可以使用 having 对结果进行过滤。
2. 在进行查询的时候，select 查询的字段只能是进行分组的字段，或者使用聚合函数操作过的结果
3. having和where的区别：where不能跟着聚合函数，having可以，where主要是对表的单条记录进行判断，而having则是对分组后的记录进行判断

   select id,count(id) from report_temp group by id having count(id) > 1;

   group by可以用于对字段去重，distinct关键字也是可以的，但是distinct对于大表查询时候，有着性能问题，所以可以使用group by的，优先使用group by。distinct还需要注意：只能放在select 的第一个字段之前。

   四 DML数据操纵

   主要用于数据的增删改数据的操作

4.1 新增数据

1）基本语法

insert into 表名(字段名，...)
values(值1，...);
# 还可以使用子查询在插入查询出的数据到表中
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';

2）案例：按照字段依次插入数据，当某个字段为空时，可以使用null关键字

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);

3）另一种方式插入数据，使用set，但是这种方式比较麻烦，而且不支持子查询

INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';

4.2 修改数据

1）基本语法

# 修改单表的记录
update 表名 set 字段=新值, 字段=新值【where 条件】

2）案例实践

UPDATE beauty SET phone = '13899888899'
WHERE NAME LIKE '唐%';

# 修改多表的记录
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;

4.3 删除数据

1）基本语法

delete from 表名 【where 筛选条件】
truncate table 表名 ：清空表

2）例子：删除手机号为8结尾的记录

DELETE FROM beauty WHERE phone LIKE '%9';

3）使用delete from 和 truncate 两者的区别

• delete 可以加where 条件，truncate不能加
• truncate删除，效率高一丢丢
• 假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始
• truncate删除没有返回值，delete删除有返回值
• truncate删除不能回滚，delete删除可以回滚

  五 DDL数据定义

  DDL：数据定义语句，主要用于库和表结构的创建，修改，删除

5.1 数据库的创建和删除

# 创建库
create database 库名
# 删除库
drop database 库名

5.2 表的创建和删除

最好加上这个语句判断：（if not exists，if exists）

# 创建一个表
CREATE TABLE IF NOT EXISTS studentinfo(
    stuId INT,
    stuName VARCHAR(20),
    gender CHAR,
    borDate DATETIME
);

# 删除一张表
DROP TABLE [IF EXISTS] studentinfo;

5.3 表结构的修改

1）概述：修改表中的字段结构，字段的新增，修改，删除
2）语法：alter table 表名 add | modify | drop | change column 字段名 【字段类型】

# 1、新增字段，email
alter table studentinfo add column email VARCHAR(20) first;
# 2、修改字段的名称，sex-->gender
alter table studentinfo change column sex gender char;
# 3、修改字段的类型和列级约束，将 bordate 字段类型 datetime 修改为 date
alter table studentinfo modify column bordate DATE ;
# 4、删除字段，email
alter table studentinfo drop column email;

3）修改表结构的表名
语法：alter table 表名 rename [to 可省] 新表名

# 将表名 studentinfo 修改为 student
alter table studentinfo rename [to] student;

六 DCL数据控制

DCL：数据控制语句，用户创建用户，定义访问权限和安全级别

6.1 创建用户

create user "jinliz"@"%" identified by "123456";

6.2 给用户授权

GRANT all on tb_for_jinliz.* to "jinliz"@"%";

6.3 撤销授权

revoke all on tb_for_jinliz.* from 'jinliz'@'%';

6.4 查看授权

show grants for 'jinliz'@'%';

6.5 修改用户密码

update mysql.user set authentication_string=password('hjewp123') where user='jinliz' and host='%';
flush PRIVILEGES; // 刷新权限

6.6 删除用户

drop user 'jinliz'@'%'

七 数据类型

7.1 整型（xxint）

特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
④ 长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

# 设置无符号，有符号类型
CREATE TABLE tab_int(
    t1 INT unsigned,
    t2 INT(7) ZEROFILL 
);
DESC tab_int;

7.2 小数

1）浮点型（float占4个字节，double占8个字节）
（1）float(M,D)
（2）double(M,D)
2）定点型
（1）dec(M，D)
（2）decimal(M,D)

DROP TABLE tab_float;
CREATE TABLE tab_float(
    f1 FLOAT,
    f2 DOUBLE,
    f3 DECIMAL
);
SELECT * FROM tab_float;
DESC tab_float;
INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);
INSERT INTO tab_float VALUES(123.456,123.456,123.456);
INSERT INTO tab_float VALUES(123.4,123.4,123.4);
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);

7.3 字符型

字符型常用有三种：char，varchar，text

7.3.1 三者的区别

（1）char是一种固定长度的类型，varchar则是一种可变长度的类型。
（2）char如果不指定(M)则表示长度默认是1个字符。varchar必须指定(M)。
（3）char(M)类型的数据列里，每个值都占用M个字符，如果某个长度小于M，MySQL就会在它的右边用空格字符补足（在检索操作中那些填补出来的空格字符将被去掉；如果存入时右边本身就带空格，检索时也会被去掉）；在varchar(M)类型的数据列里，每个值只占用刚好够用的字符再加上一个到两个用来记录其长度的字节（即总长度为L字符+1/2字字节）。（4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；5.0版本以上，varchar(20)，指的是20字符）。
（4）由于某种原因char 固定长度，所以在处理速度上要比varchar快速很多，但相对费存储空间，所以对存储不大，但在速度上有要求的可以使用char类型，反之可以用varchar类型来实例。
（5）text文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用char，varchar来代替。还有text类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含text类型字段，建议单独分出去，单独用一个表。

7.3.2 哪些情况适合使用char或varchar

1）存储很短的信息，比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的现在得不偿失。
2）固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。
3）十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。
4）MyISAM和MEMORY存储引擎中无论使用char还是varchar其实都是作为char类型处理的。
5）其他像InnoDB存储引擎，建议使用varchar类型，因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。

7.4 日期型

1）date：只保存日期 2）time：只保存时间 3）year：只保存年
4）datetime：保存日期+时间 5）timestamp：保存日期+时间

7.5 特殊的NULL值

NULL的特征：
（1）所有的类型的值都可以是null，包括int、float等数据类型
（2）空字符串””，不等于null，0也不等于null，false也不等于null
（3）任何运算符，判断符碰到NULL，都得NULL
（4）NULL的判断只能用is null，is not null
（5）NULL 影响查询速度，一般避免使值为NULL

八 常见约束

约束：是一种限制，用于限制表中存储的数据，为了保证表中的数据的准确性和可靠性

NOT NULL，DEFAULT，UNIQUE，CHECK
PRIMARY KEY，FOREIGN KEY

NOT NULL # 非空，用于保证该字段的值不能为空。比如姓名、学号等
DEFAULT  # 默认，用于保证该字段有默认值。比如性别
PRIMARY KEY # 主键，用于保证该字段的值具有唯一性，并且非空。比如学号、员工编号等
UNIQUE  # 唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号
CHECK  # 检查约束【mysql中不支持】。比如年龄、性别
FOREIGN KEY  # 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
    在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号