Mysql初级学习-SQL语句大全

Mysql初级学习-SQL语句大全

数据数据库概述

通俗地被称为存储数据的仓库，只是这个仓库是存储在计算机存储设备上的，并且其所存储的数据是按一定的格式进行存储。

若从严格意义上讲，所谓数据库是指长期储存在计算机中的有组织的、可共享的数据集合，且数据库中的数据按一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性，系统易于扩展，并可以被多个用户共享。

概括而言，数据库中存储的数据具有三个基本特点：永久存储、有组织和可共享。

常见的关系型数据库有mysql 、SQL Server、Oracle、Sybase、DB2等

SQL

SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。
简单来说将数据库比作粮仓，将数据比做粮仓中的粮食，那么SQL则是何时管理粮食进出、除湿等工具或者方法。

SQL语句的分类

SQL语句的五种分类分别是DQL、DML、DDL、TCL和TCL，下面对SQL语句的五种分类进行列举：

1. 数据库查询语言（DQL）
   数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE 子句组成的查询块，简称DQL，Data Query Language。代表关键字为select。
   注：SELECT子句对数据本身不做修改，即只能对“粮仓”中的“粮食”进行查看和观察，但是不能触碰。

2. 数据库操作语言（DML）
   用户通过它可以实现对数据库的基本操作。简称DML，Data Manipulation Language。代表关键字为insert、delete 、update。
   DML即使可以对“粮仓”中的“粮食”进行触碰，可以存粮食（insert），取粮食（delete），换新粮食（update）

3. 数据库定义语言（DDL）
   数据定义语言DDL用来创建数据库中的各种对象，创建、删除、修改表的结构，比如表、视图、索引、同义词、聚簇等，简称DDL，Data Denifition Language。代表关键字为create、drop、alter。和DML相比，DML是修改数据库表中的数据，而 DDL 是修改数据中表的结构。
   DDL则是对“粮仓”搭建进行更改，将圆型粮仓换成正方形的粮仓。在实际开发中粮仓盖好后不会轻易更改。

4. 事务控制语言（TCL）
   TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面，简称：TCL，Trasactional Control Languag。代表关键字为commit、rollback。
   将一系列的DML语句进行管理，即可形成事务。

5. 数据控制语言（DCL）
   数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。简称：DCL，Data Control Language。代表关键字为grant、revoke。

数据库查询语言（DQL）

全部的查询语句使用方法如下：

select 
	字段 
from 
	表 
where 
	条件 [inner/left/right join 表1 on 条件] 
group by 
	列名 
having 
	组 
order by 
	列名 
limit 开始偏移量,偏移长度(开始偏移量从0开始)

数据库操作语言（DML）

全部的数据库操使用方法如下：

## 数据插入insert
-- 格式1
insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； -- 向表中插入某些数据
-- 格式2
insert into 表 values (值1,值2,值3...);     -- 向表中插入所有列

数据的修改update
## 
-- 格式1
update 表名 set 字段名=值,字段名=值...;
-- 格式2
update 表名 set 字段名=值,字段名=值... where 条件;

## 数据的删除delete
-- 格式1
delete from 表名;
-- 格式2
delete from 表名 [where 条件]
-- 格式3
truncate table 表名 
或者
truncate 表名
注意：delete和truncate的原理不同，delete只删除内容，而truncate类似于drop table ,可以理解为是将整个表删除，然后在创建一张新表；

数据库定义语言（DDL）

##显示当前MySQL中的数据库列表
show databases;
 
##显示指定名称的数据库
show create database 数据库名称;

## 创建数据库：数据库名称可⾃定义
create database 数据库名称;
 
## 创建数据库：当指定名称的数据库不存在时执⾏创建
create database if not exists 数据库名称;
 
## 在创建数据库的同时指定数据库的字符集（字符集：数据存储在数据库中采⽤的编码格式
utf8/gbk）
create database <dbName> character set utf8;

## 修改数据库的字符集
alter database 数据库名称 character set utf8; # utf8 gbk

## 删除数据库
drop database 数据库名称;
 
## 如果数据库存在则删除数据库
drop database is exists 数据库名称;

use 数据库名;

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1 数据类型 [字段属性|约束] [索引] [注释],
	字段2 数据类型 [字段属性|约束] [索引] [注释],
	···
	字段n 数据类型 [字段属性|约束] [索引] [注释],
)[表类型][表字符集][注释];

## 主键
CREATE TABLE [IF NOT EXISTS] 表名(
	字段1 数据类型 PRIMARY KEY auto_increment,
	···
);
或
CREATE TABLE [IF NOt EXISTS] 表名(
	字段1 数据类型,
	···
	 PRIMARY KEY[列名]
);

## 外键
CREATE TABLE [IF NOt EXISTS] 表名(
	字段1 数据类型,
	···
	 FOREIGN KEY(自己的字段) references 主表(主表字段)
);
或
alter table 从表名 add foreign key (从表字段) references 主表(主表字段);
在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率

事务控制语言（TCL）

事务（transaction）： 主要用来处理操作量大，复杂度高的数据，事务是原子操作，不可再分，要么同时成功，要么同时失败，即一个事务是一个完整的业务逻辑单元。在MySQL数据库中只有使用InnoDB引擎的数据库才能使用事务。事务主要用来管理DML（数据操作语言），即insert语言、update语言以及delete语言，其他不可以。

一般来说事务必须满足四个特征：即ACID

• 原子性（Atomicity）：又叫不可分割性，它是指一个事务中的所有操作，要么全部完成，要么全部不完成，不会在中间某个阶段结束。在执行的过程中一旦事务发生错误就会被**回滚（rollback）**到最初执行时的位置（状态），就像这个事务没有执行过一样。
• 一致性（Consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。
• 隔离性（Isolation）：又叫独立性，它是指每个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发执行的其他事务是隔离的，各个事务之间不能互相干扰。事务的隔离级别一般有四种，即读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable），分别对应1-4个级别，其中mysql数据库默认的隔离级别是第三级别（可重复读）。
• 持久性（Durability）：也称为永久性，它是指一个事务一旦成功提交，对数据库中的数据改变使永久的，不会轻易丢失，就算系统故障也不会丢失。

事务的控制语句（TCL）

事务（Transaction）：一批操作，主要是DML。

开启事务：Start Transaction /begin

回滚事务:**rollback,**回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

提交事务: commit,对数据库进行的所有修改成为永久性的

自动提交模式:set sutocommit

禁止自动提交：set autocommit = 0
开启自动提交：set autocommit = 1
保存点：savepoint，允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT

删除保存点：release savepoin，删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常

设置隔离级别：set transaction

在MySQL中 InnoDB 存储引擎实现了四个隔离级别（其他存储引擎不支持事务），用以控制事务所做的修改，并将修改通告至其它并发的事务，现在一般有4个，一般用第一级别：

1. 第一级别：读未提交（read uncommitted），即对方事务没有提交，但是已经读到数据，会造成读脏数据。
2. 第二级别：读已提交（read commintted），即对方事务已经提交，我方可以读到数据，可以解决读脏数据，但是会造成不可重复读数据。
3. 第三级别：可重复读（repeatable read），可以解决不能重复读数据，但是可能会造成幻读
4. 第四级别：序列化读/串行化读(serializable)，可以解决所有问题，但是效率低

​ 事务在并发执行读取数据时可能会出现几个问题：
脏读（Dirty Read）：一个事务（A）已经更新了某条数据（假设为M），但没有来的及提交写回数据库时，另一个事务（B）就开始读取这条数据（M），此时事务B读取到的数据不是最新的数据，这就出现了脏读取。
不可重复读（Non-repeatable Read）：在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。
幻读（Phantom Read）： 幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。

​ mysql默认的隔离级别为 可重复读。oracle默认的隔离级别是读已提交。

数据控制语言（DCL）

数据控制语言，用来定义访问权限和安全级别。

1.创建用户
语法：
CREATE USER 用户名@地址 IDENTIFIED BY '密码';
例：
create user testuser1@localhost identified by '111';

2.给用户授权
语法：
GRANT 权限1, … , 权限n ON 数据库.* TO 用户名;
例：
grant create,alter,drop,insert,update,delete,select on test999.* to testuser1@localhost;

3.撤销授权
语法：
REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名;
例:
revoke create,alter,drop on test999.* to testuser1@localhost;

4.查看用户权限
语法：
SHOW GRANTS FOR 用户名;
例:
show grants for testuser1@localhost;

5.删除用户
语法：
DROP USER 用户名;
例:
drop user testuser1@localhost;

6.修改用户密码
语法：
USE mysql;
UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;
FLUSH PRIVILEGES;
例:
update user set password=password('1234') where user='user2' and host=’localhost’;
flush privileges;