SQL基础知识总结

DDL  库

创建数据库

CREATE DATABASE 名字；

判断数据库是否已经存在，不存在则创建

CREATE DATABASE IF NOT EXISTS 数据库名；

创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集；

查看所有数据库

SHOW DATABASES;

查看某个数据库的定义信息

SHOW CREATE DATABASE 数据库名；

 

修改数据库

修改数据库默认字符集

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集

 

删除数据库

DROP DATABASE 数据库名；

 

使用数据库

USE 数据库名；

查看正在使用的数据库

SELECTE DATABASE();

 

DDL  表

创建表的格式

CREATE TABLE 表名(

       字段名   字段类型，

       字段名   字段类型

);

数据类型

整数       tinylnt                         微整型

              smallint                小整型：小的整数(占 16 位二进制)

              mediumint           中整型：中等长度的整数(占 24 位二进制)

             nt(integer)            整型：整数类型(占 32 位二进制)

小数       float                     单精度浮点数，占 4 个字节

              double                 双精度浮点数，占 8 个字节

日期       time                     表示时间类型

              date                     表示日期类型

              datetime              同时可以表示日期和时间类型        

字符串   char(m)                固定长度的字符串，无论使用几个字符都占满全部

              varchar(m)           可变长度的字符串，使用几个字符就占用几个

 

 

查看表

SHOW TABLES;

查看表结构

DESC 表名

查看创建表的SQL语句

SHOW CREATE TABLE 表名

 

快速创建一个表结构相同的表

CREATE TABLE 新表名 LIKE 旧表名

删除表

DROP TABLE 表名；

判断表是否存在 存在则删除

DROP TABLE IF EXISTS 表名；

 

修改表结构

添加表列

ALTER TABLE 表名 ADD 列名 类型；

修改列类型

ALTER TABLE 表名MODIFY 列名 新的类型；

修改列名

ALTER TABLE 表名 CHANGE 旧列名 新列名 类型；

删除列

ALTER TABLE 表名 DROP 列名；

修改表名

RENAME TABLE 表名 TO 新表名；

修改字符集

ALTER TABLE 表名 CHARACTER SET 字符集

 

DML 插入

插入记录

INSERT INTO 表名 【字段名】 VALUES 【字段值】

INSERT INTO 表名 （字段名1,2） VALUES  (值1,2)

INSERT INTO 表名 VALUES  (值1,2)

 

DOS解决乱码

SET NAMES GBK;

 

蠕虫复制   将一张已经存在的表中的数据复制到另一张表中

INSERT INTO 表1 SELECT * FROM 表2

INSERT INTO 表1（列1，列2） SELECT 列1，列2  FROM 表2；

 

更新表记录

UPDATE 表名 SET 列名 = 值 【WHERE 条件表达式】

 

删除表记录

DELETE FROM 表名 【WHERE 条件表达式】

使用truncate删除表中所有记录

TRUNVATE TABLE 表名；

区别：相当于删除表的结构，再创建一张表

 

DQL查询表

查询所有

SELECT * FROM 表名;

查询指定列

SELECT 字段名1,2 FROM 表名

 

指定列的别名查询

SELECT 字段名1 AS 别名 FROM 表名

清楚重复值

SELECT DISTINCT 字段名 FROM 表名

 

查询结果参与运算

SELECT 列名1 + 固定值 FROM 表名

SELECT 列名1 + 列名2 FROM 表名

 

条件查询

SELECT 字段名 FROM 表名 WHERE 条件

 

运算符

比较运算符          > 、< 、<= 、>= 、= 、<>（不等于）

                            BETWEEN…AND   在一个范围内，包头又包尾

                            IN(集合)   集合表示多个值，使用逗号分隔

                            LIKE’ 张%’  模糊查询

                            IS NULL    查询是否为空

逻辑运算符          and 或 && 与，SQL 中建议使用前者，后者并不通用。

                            or 或 || 或

                            not 或 ! 非

 

DQL查询语句

排序       ORDER BY 字段名   【ASC|DESC】

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 【ASC|DESC】     升序 降序

组合排序

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1，字段名2；

聚合函数

max( 列名) 求这一列的最大值                       min( 列名) 求这一列的最小值

avg( 列名) 求这一列的平均值                       count( 列名) 统计这一列有多少条记录

sum( 列名) 对这一列求总和

SELECT 聚合函数(列名) FROM 表名；

如果列名不空，返回这列的值，如果空，返回默认值

IF NULL(列名，默认值)

SELECT COUNT (IF NULL(ID,0)) FROM STUDENT;

 

分组

SELECT 字段1,2 FROM 表名 GROUP BY 分组字段  【HAVAING 条件】

SELECT SEX,AVG(MATH) FROM STUDENT3 GROUP BY SEX;

 

WHERE 和 HAVING 区别

WHERE：对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。where后 面不可以使用聚                    合函数

 

HAVING：having子句的作用是筛选满足条件的组，即在分组之后过滤数据，先分组再过滤

                 having后面可以使用聚合函数

 

LIMIT  限制的意思，所以limit的作用就是限制查询记录的条数

SELECT 字段列表 FROM 表名 WHERE 子句 GROUP BY 子句 HAVING 子句 ORDER BY 子句 LIMIT 子句

LIMIT OFFSET,LENGTH

OFFSET  起始行数，从0开始，如果省略，默认0

LENGTH 返回的行数、

 

数据库的备份和还原

备份：DOS 下 未登录的时候  这是一个可执行文件，在bin文件夹下

MYSQLDUMP -U用户名 -P 密码  数据库 > 文件的路径

还原：MYSQL中的命令，登录后操作

SE 数据库;

SOURCE 导入文件的路径；

 

 

数据库的约束

主键              PRIMARY KEY

唯一              UNIQUE

非空              NOT NULL

外键              FOREIGN KEY

 

主键       创建主键

字段名 字段类型 PRIMARY KEY

在已有表中添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名)；

主键自增

AUTO_INCREMENT

 

 

 

修改自增默认的起始值

CREATE TABLE 表名{

       列名 INT PRIMARY KEY AUTO_INCREMENT

}AUTO_INCREMENT = 起始值

创建好后修改默认起始值

ALTER TABLE 表名 AUTO_INCREMENT = 起始值

 

唯一约束  

字段名 字段类型 UNIQUE

 

非空约束

字段名 字段类型 NOT NULL;

 

默认值

字段名 字段类型 DEFAULT 默认值

 

外键约束

新建表时增加外键

CONSTRAINT 外键约束名称 FOREIGN KEY （字段名） REFERENCES 主表名(主键字段名)

已有表增加外键

ALTER TABLE 从表 ADD CONSTRAINT 外键约束名称 FOREIGN KEY (字段名) REFERENCES  主表名 （主键字段名）

删除外键

ALTER TABLE 从表 DROP FOREIGN KEY 外键名称

 

外键的级联

ON UPDATE CASCADE 级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，                                   从表中外键也自动同步更新

ON DELETE CASCADE 级联删除

 

 

 

数据规范化

第一范式：数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据              项。即表中的某个列有多个值时，必                    须拆分为不同的列。简而言之，第一范式每        一列不可再拆分，称为原子性。

第二范式：在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。第二范式是在              第一范式的基础上所有列完全依赖于                   主键列。

第三范式：第三范式就是所有列不依赖于其他非主键列，在满足第二范式的基础上，任何非              主列不得传递依赖于主键

 

1NF        原子性，表中每列不可再拆分

2NF        不产生局部依赖，一张表只描述一件事情

3NF        不产生传递依赖，表中每一列都直接依赖于主键，而不是通过其他列间接依赖于主键

内连接：用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表,外键=主表.主键

隐式内连接：看不到JOIN关键字，条件使用WHERE指定

SELECT 字段名 FROM 左表，右表 WHERE 条件

显式内连接：使用INNER JOIN…ON 语句，可以省略INNER

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

SELECT * FROM EMP [INNER] JOIN DEPT ON EMP.’DEPT_ID’ = DEPT.’ID’ WHERE EMP.’NAME’ = ‘唐僧’

 

左外连接

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

右外连接

SELECT 字段名 FROM 右表 RIGHT [OUTER] JOIN 左表 ON 条件

 

子查询          一个查询的结果作为另一个查询的条件

                     有查询的嵌套，内部的查询称为子查询                子查询要使用括号

 

子查询结果

单行单列       肯定在WHERE后面作为条件，父查询使用比较运算符等

                     SELECT 查询字段 FROM 表 WHERE 字段=（子查询）

                     SELECT * FROM EMP WHERE SALARY = (SELECT MAX(SALARY) FROM EMP)

多行单列       结果集类似于一个数组，父查询使用IN运算符

                     SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询)

                     SELECT NAME FROM DEPT WHERE ID IN (SELECT DEPT_ID FROM EMP WHERE   SALARY >5000)

多行多列       在FROM后面作为表

                     SELECT 查询字段 FROM （子查询）表别名 WHERE 条件

                     SELECT * FROM DEPT D,(SELECT * FROM EMP WHERE JOIN_DATE >= ‘2020-1-1’) E   WHERE D.’ID’ = E.DEPT_ID;

 

事务

开启事务       START TRANSACTION

提交事务       COMMIT

回滚事务       ROLLBACK

 

事务的隔离级别

四大特性

原子性   每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，

                     要么都失败。

一致性   事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2 个人的

                     总金额是 2000 ，转账后 2 个人总金额也是 2000

隔离性   事务与事务之间不应该相互影响，执行时保持隔离的状态。

持久性   一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。

 

 

 

隔离级别

脏读                     一个事务读取到了另一个事务中尚未提交的数据

不可重复读          一个事务中两次读取的数据 内容 不一致，要求的是一个事务中多次读取时数据是  一致的， 这是事务 update 时引发的                                问题

幻读                     一个事务中两次读取的数据的 数量 不一致，要求在一个事务多次读取的数据的数  量是一致的，这是 insert 或 或 delete                             时引发的问题