sql

一：数据库的基本概念：

　　1：数据库（DataBase）简称DB

　　2：数据库就是存储和管理数据的仓库

　　3：数据库的特点：（1）持久化存储数据，其实数据库就是一个文件系统

　　　　　　　　　　  （2）方便存储和管理数据

　　　　　　　　　　  （3）关系型数据库都使用统一的方式操作数据 --  SQL

 

 

二：SQL基本概念

　　SQL(Structured Query Language) 是结构化查询语言，这是一种所有关系型数据库的查询规范，不同的数据库都支持。

 

关系型数据库的优势：

　　　　　　　　　　1）复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。

　　　　　　　　　　2）事务支持使得对于安全性能很高的数据访问要求得以实现。对于这两类数据库，对方的优势就是自己的弱势，反之亦然。

 

三：SQL分类：

　　1. DDL： Data Definition Language 数据定义语言，如：建库，建表

　　　　（1）-- 创建数据库

　　　　　　CREATE DATABASE IF NOT EXISTS 数据库名字

　　　　（2）-- 创建数据库并指定编码

　　　　　　CREATE DATABASE IF NOT EXISTS 数据库名字 CHARACTER SET 编码类型

　　　　（3）-- 查看所有数据库的名字

　　　　　　SHOW DATABASES

　　　　（4）--  查看数据库创建语句

　　　　　　SHOW CREATE DATABASE 数据库名字

　　　　（5）-- 更改数据库编码

　　　　　　ALTER DATABASE 数据库名字 CHARACTER SET 编码类型

　　　　（6）-- 删除数据库

　　　　　　DROP DATABASE IF EXISTS 数据库名字

　　　　（7）-- 选择要使用的数据库

　　　　　　USE 数据库名字

　　　　（8）-- 查看当前数据库中的所有表

　　　　　　SHOW TABLES

　　　　（9）-- 查看当前数据库中的表结构

　　　　　　DESC 表名

　　　　（10）-- 查看建表语句

　　　　　　SHOW CREATE TABLE 表名

　　　　（11）-- 创建表　　　

　　　　　　CREATE TABLE student (
　　　　　　　　　　列名  数据类型
　　　　　　)

　　　　（12）-- 复制表

　　　　　　CREATE TABLE 新表名字 LIKE 被复制的表名字

　　　　（13）-- 删除表

　　　　　　DROP TABLE IF EXISTS 表名

　　　　（14）-- 修改表名

　　　　　　ALTER TABLE 旧表名 RENAME TO 新表名

　　　　（15）-- 修改表的字符集

　　　　　　ALTER TABLE 表名 CHARACTER SET 编码类型

　　　　（16）-- 在表中添加一列

　　　　　　ALTER TABLE 表名 ADD 列名 数据类型;

　　　　（17）-- 修改某一列的列名和数据类型

　　　　　　ALTER TABLE 表名 CHANGE 旧类名 新类名 数据类型;

　　　　（18）-- 修改一列的数据类型

　　　　　　ALTER TABLE 表名 MODIFY 列名 数据类型

　　　　（19）-- 删除一列

　　　　　　ALTER TABLE 表名 DROP 列名;

　　2. DML： Data Manipulation Language 数据操作语言，如对表的数据记录操作增删改

　　　　（1）在表中添加一个数据

　　　　　　INSERT INTO 表名 (列名1，列名2.。。) VALUE(列1的值，列2的值)

　　　　（2）删除表中的数据

　　　　　　DELETE FROM 表名 WHERE 条件

　　　　（3）删除表中的所有数据

　　　　　　TRUNCATE TABLE 表名

　　　　（4）修改一列的值

　　　　　　UPDATE 表名 SET 列名=值 WHERE 条件

　　3. DQL： Data Query Language·数据查询语言，如：对表中数据的查询操作

　　　　语法：select + 字段列表

　　　　　　　from + 表名列表

　　　　　　　where + 条件列表

　　　　　　　having + 分组之后的条件

　　　　　　　order by 排序

　　　　　　　limit　　分页操作

　　　　　　distinct    去重

　　　　　　as     起别名

　　　　运算符：<>　　　　不等于

　　　　　　　　!=　　　　不等于

　　　　　　　　<　　　　小于

　　　　　　　　>　　　　大于

　　　　　　　　=　　　　等于　　　　

　　　　　　　　<=　　　　小于等于

　　　　　　　　>=　　　　大于等于

　　　　　　　　between and 　　　　>=小值 并且<=大值

　　　　　　　　in　　　　集合

　　　　　　　　like　　　　模糊查询 

　　　　　　　　is null　　　　是null的值

　　　　　　　　and　　　　并且

　　　　　　　　&&　　　　并且

　　　　　　　　or　　　　或者

　　　　　　　　||　　　　或者

　　　　　　　　not　　　　不

　　　　占位符：_   单个字符

　　　　　　　　%  多个字符

　　　　排序：order by 排序字段  ASC　　　　升序（默认）

　　　　　　　order by 排序字段   DESC　　　　降序

　　　　聚合函数：count(语句)　　计算个数

　　　　　　　　　max(语句)　　　　计算最大值

　　　　　　　　　min(语句)　　计算最小值

　　　　　　　　　sum(语句)　　计算和

　　　　　　　　　avg(语句)　　　　计算平均值

　　　　分组：group by 分组字段　　　　

　　　　分页：limit  开始的索引，每页查询的条数

　　4. DCL： Data Control Language 数据控制语言，如：对用户权限的设置

　　　　（1）创建用户语法：create user 用户名 @ 主机名  identified  by  密码     　　主机名可以为%（任意主机）

　　　　（2）给用户授权语法： grant  权限1，权限2....  on  数据库名 .表名   to  用户名 @ 主机名  

　　　　　　　权限：select、update、insert等等

　　　　　　　数据库名和表名可以用*代替，表示全部的

　　　　（3）撤销授权语句：revoke  权限1,权限2,...  on  数据库名.表名 from 用户名 @  主机名

　　　　（4）查看权限语法：show grants for 用户名  @  主机名

　　　　（5）删除用户语法：drop user 用户名 @ 主机名

　　　　（6）修改管理员密码语法：mysqladmin -u旧密码  -p  password  新密码　　　　（在未登录mysql的情况下操作）

　　　　（7）修改普通用户密码语法：set password for  用户名 @ 主机名 = password(新密码)       （需要在登录mysql的情况下操作，新密码需要加单引号）

 

 

四：MySQL的数据类型

　　整数：tinyInt 　　微整形

　　　　　smallint　　小整形

　　　　　mediumint　　中整形

　　　　　int　　　　　　整形

　　小数：float　　　　单精度浮点数

　　　　   double　　　　双精度浮点型

　　日期：time　　　　表示时间类型

　　　　　date　　　　表示日期

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

　　　　　timestamp　　时间戳

　　字符串：char　　　　固定长度的字符串

　　　　　　varchar()　　可变长度的字符串

　　大文本：tinytext　　允许长度0~255字节

　　　　　　text　　　　允许长度0~65535字节

　　　　　　mediumt　　允许长度0~167772150字节

　　　　　　longtext　　允许长度0~4294967295字节

 

五：数据库表的约束

　　约束的作用：

　　　　对表的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法添加到表内，约束在创建表时添加比较合适。

　　约束的种类：

　　　　主键　　　　primary key  （主键只能有一个，可以是单列或多列）

　　　　唯一　　　　unique

　　　　非空　　　　not null

　　　　外键　　　　foreign key

　　　　检查约束　　check（mysql不支持）

　　语法：

　　　　删除主键　　alter table 表名 drop primary key

　　　　创表后增加主键　　alter table 表名 add primary key(列名)　　

　　　　建表时给主键设置自动增长初始值　　在建表的括号后面加上　　auto_increment=初始值

　　　　建表后给主键设置自动增长初始值　　altter table 表名 auto_increment=初始值

　　　　建表时设置默认值　　列名   数据类型    DEFAULT   默认值   

　　　　

　　　　添加外键： （1）建表时加外键

　　　　　　　　　　　　constraint  外键约束名   foreign key  (外键字段名 ) references   主表名(主键)   　

　　　　　　　　　　(2)已有表增加外键

　　　　　　　　　　　　alter table 从表  add  constraint   外键约束名称   foreign key (外键字段名)  references  主表 (主键字段名)　

　　　　删除外键：later table 表名  drop  foreign key 外键名　　

 

　　　　级联：正常情况下主表中的主键发生变动时从表的外键不会跟着一起变动，这样的效果不是想要的，所以就有了级联，添加级联的2个语法后主表主键变动从表的外键也会一起变

　　　　on update cascade　　主表的主键修改从表的外键也一起修改

　　　　on delete cascade　　主表删除列时从表也会发生变动

　　　　在从表添加外键时将级联语句加到后面即可

 

六：表与表之间的关系

　　一对多：比如部门和员工

　　一对一：比如员工和简历表

　　多对多：比如学生和选课

　　　　多对多需要建立中间表来联系，中间表至少有2个字段，分别作为外键指向各自的主键

 

七：三大范式（表的建立都应该遵循这三大范式）

　　1NF:原子性，每一列都应该不可拆分

　　2NF：不产生局部依赖，一张表只做一件事

　　3NF：表上每一列都应该直接的依赖主键，而不是通过其他列间接的依赖主键

 

八：多表查询

　　1:内连接查询（只有条件成立的数据才会展示出来）

　　　　（1）：隐式内连接查询

　　　　　　select  列名 from 表一  ，表二   where 条件

　　　　（2）：显示内连接

　　　　　　select  列名  from  表一   join   表二    on   条件

　　2:外连接查询

　　　　（1）：左外连接查询（左连接左边的表所有数据都展示，右表的数据符合条件才展示出来）

　　　　　　select  列名   from  表一   left   join   表二   on   条件

　　　　（2）：右外连接查询（右连接右边的表所有数据都展示，左表的数据符合条件才展示出来）

　　　　　　select 列名  from  表一  right   join  表二   on  条件

 

九：事务

　　1：什么是事务？

　　事务是一个整体，如果所有的sql语句都执行成功，那么提交，否则回滚。　

 　　2：事务操作语句：

　　　　（1）start transaction  开启事务

　　　　（2）commit    提交

　　　　（3）rollback   回滚

　　　　（4）回滚点：

　　　　　　　　设置回滚点名字：savepoint 回滚名字

　　　　　　　　回滚：rollback to 回滚名字  

　　3：自动提交事务

　　　　mysql默认每一条单独的DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完自动提交。mysql默认开始自动提交事务。

　　4：查看当期是否开启自动提交事务

　　　　select @@autocommit   -- @@表示全局变量  -- 0表示关闭，1表示开启

　　5：set @@autocommit=0  -- 设置是否自动提交事务

　　6：事务原理：

　　　　事务开启后，所有的操作都会临时保存到事务日志里，事务日志只有得到commit命令才会同步到数据库里，其他任何情况都会清空事务日志

　　7：事务特性：

　　　　（1）：原子性：每个事务都是一个整体，不可拆分，十五中的所有语句要么都执行成功，要么都执行失败

　　　　（2）：一致性：事务在执行数据库的状态与执行后数据库的状态保持一致。

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

　　　　（4）：一旦事务执行成功，对数据的修改是持久的，就算关机，也是保存下来的

　　8：事务在操作时的理想状态：所有的事务之间保持隔离，相互不影响。因为并发操作，多个用户同时访问一个数据，可能引发并发访问的问题：

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

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

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

　　　四种隔离级别：

　　　　（1）读未提交　　隔离级别：read uncommitted　　可脏读　　不可重复读　　可幻读

　　　　（2）读已提交　　隔离级别：read committed　　不可脏读　　不可重复读　　可幻读 

　　　　（3）可重复读　　隔离级别：repeatable read　　不可脏读　　可重复读　　可幻读

　　　　（4）串行化　　隔离级别：serializable　　不可脏读　　可重复读　　不可幻读

　　　　　　隔离级别越高，性能越差，安全性越高

　　查询全局事务隔离级别：select @@tx_isolation

　　设置隔离级别，需要退出重新登陆才能看到隔离级别的变化：set global transaction isolation level 级别字符串