SQL | SQL语句总结

一、什么是SQL

        SQL（Structured Query Language），是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server 等。

二、SQL分类

1、DDL（Data Definition Language）数据定义语言

用来定义库，表的创建等，负责操作数据库的单位

create alter drop   创建  库  表 

 （1）操作库

1）进入数据库

mysql -u用户名 -p密码eg:mysql -uroot -p123456;

2）查看所有库

show databases;

3）创建库

create database 库名;

4）使用数据库

use 库名;

5）查询当前库下有哪些表

show tables;

6）查询当前正在使用的库

select database();

7)删除数据库

drop database 库名;

（2）操作表

1）创建表

create table 表名 (列名 列的类型(长度) 当前列的约束/可以没有,列名 列的类型(长度) 约束,);>eg: create table person ( id int(10) primey key, name varchar(20), age int(3), sex varcahr(2));

2）查询表结构

desc 表名;

3）修改表

--修改表名alter table 表名 rename 新的表名;--在表中添加列alter table 表名 add column 列名 数据类型(长度);--修改表中某一列alter table 表名 modify column 列名 数据类型(长度);--删除表中某一列alter table 表名 drop column 列名; --重新添加列 添加约束alter table 表名 add 列名 数据类型(长度) 添加约束; not null default 默认值 

4）删除表

drop table 表名;

2、DML(Data Manipulation Language) 数据操作语言

负责操作表中的数据

insert添加 delete删除 update 修改

 （1）添加数据 

insert into 表名 (需要添加的列) values (值,值，);#字符串类型的数据 \'值\'--插入一条数据eg：insert into person (id,name,age,sex) values (1001,\'张三\',18,\'男\');--插入多条数据eg：insert into person (id,name,age,sex) values (1001,\'张三\',18,\'男\'), (1002,\'赵四\',23,\'女\');

（2）删除数据 

delete from 表名 where 条件 = 值eg：DELETE FROM employee WHERE age > 24 AND sex = \'女\';

（3）修改数据 

update 表名 set 列 = 值,列 = 值 。。。。。。。 eg：UPDATE employee SET address = \'南京\' WHERE name = \'张四\';

3.DQL(Data Query Language) 数据查询语句 

负责查询数据中的数据，分析 统计

（1）基础查询

1）指定字段查询

select 列1,列2 from 表名;

2）查询所有字段

select * from 表名；

 3）查询起别名

SELECT btype as 种类 from booksSELECT btype 种类 from booksas可以省略 

4）查询类型 不想要重复的数据  

SELECT DISTINCT btype from books 重复数据的去重

（2）条件查询

1）基本语法 

select 字段 from 表名 where 条件列表

2）条件

> = <= like(模糊查询) !=    and or  && ||

a.查询价格大于30的书籍  

SELECT * FROM books where bprice > 30

b.查询书名是计算的书籍 模糊查询 like

name like 计算% #以计算开头的数据name like %计算 #以计算结尾的数据name like %计算% #只要你有计算 就行SELECT * FROM books WHERE bname LIKE \'%计算%\'

c.价格在20 - 30 之间的书籍 查询

SELECT * from books WHERE bprice BETWEEN 20 and 30

d.查询 03年 1月 - 03 年12月份书籍

SELECT * from books where bdate BETWEEN \'2003-01\' AND \'2003-12\'--date \' \'

e.查询计算机类 和 动漫类图书 and or && ||

SELECT * FROM books WHERE btype IN (\'计算机类\',\'动漫类\')SELECT * FROM books WHERE btype = \'计算机类\' OR btype = \'动漫类\'

f.查询种类为空的书籍

SELECT * FROM books WHERE btype IS NULL

 （3）聚合函数

count   统计数量
max     统计最大值
min      统计最小值
avg      平均值
sum     求和 

语法：

select 聚合函数（字段） from 表名

1）统计当前一共有多少本书

SELECT COUNT(id) count from books

2）统计最贵的书籍

SELECT MAX(bprice) from books

3）统计最便宜的书籍

SELECT MIN(bprice) from books

4）统计所有书籍的平均价格

SELECT AVG(bprice) from books

5）查询所有书籍的价格总和

SELECT SUM(bprice) from books

（4）分组查询

语法：

select 列 from 表名 [where条件] group by 列 [having 分组之后的过滤条件]

 注意：

1.执行时机不同：[where条件]分组之前执行 不参与分组 [having 分组之后的过滤条件] 分组之后执行

2.判断条件不同：where条件不能对与聚合函数 条件判断 having可以对聚合函数 过滤 （筛选数据 条件 ）

分组主要应用场景：

对数据进行分类汇总，数据集合中提取有用的信息

将数据按照某个字段 进行分组 分组的结果 聚合函数函数应用

1）基础分组 根据书籍种类 分组

SELECT btype FROM books GROUP BY btype --自动去重

 2）标准分组 搭配聚合函数使用

SELECT btype,COUNT(id) FROM books GROUP BY btype

 3）统计科幻类图书有多少本  where

SELECT btype,COUNT(id) from books WHERE btype = \'科幻类\' GROUP BY btype --一定要先分组再统计

4）分组之后 过滤条件 科幻类

SELECT btype,COUNT(id) cnt from books GROUP BY btype HAVING btype = \'科幻类\'

（5）排序查询   order by

select 查询的列 from 表名 order by 列1 排序方式1,列2 排序方式2

排序方式：

ASC：升序 默认值 

DESC ：降序

1）根据价格 完成降序查询SELECT * FROM books ORDER BY bprice DESC

 （6）分页查询  limit

select 查询列 from 表名 limit 起始索引,插叙记录数

起始索引从0开始 第一次查询第一条数据 0 

1）查询employee表 一页要查询5条数据SELECT * FROM employee LIMIT 0,5

 （7）连接查询

1）内连接

a.等值连接

select 查询的字段from 表1 [起别名] join 表2 [起别名]on 连接条件 （等量关系）[where]

b.非等值连接（on 后面的条件 不是等量关系 > < <= = ）

select 查询的字段from 表1 [起别名] join 表2 [起别名]on 连接条件 （非等量关系）[where]

c.自连接

把一个表当成两个表处理 自己跟自己连

select 查询的字段from 表1 [起别名] join 表2 [起别名]on 连接条件 （非等量关系）[where,]

eg：找出价格中 价格差最大的书籍SELECT b1.name,b1.price,b2.name,b2.price,ABS(b1.price - b2.price) AS chaFROM book b1 JOIN book b2 ON b1.id < b2.id ORDER BY cha DESCLIMIT 1

d.显式内连接

-- 使用INNER JOIN显式连接book和reader表SELECT b.book_id, b.title, b.author, r.reader_id, r.name AS reader_name,FROM book AS bINNER JOIN reader AS r ON b.reader_id = r.reader_id; 

e.隐式内连接

-- 隐式连接 book 和 reader 表SELECT book.title AS 书名, reader.name AS 借阅人FROM book,readerWHERE book.reader_id = reader.reader_id; 

2）外连接

a.左外连接

SELECT b.book_id, b.title, r.name AS reader_nameFROM book bLEFT OUTER JOIN reader r ON b.reader_id = r.reader_id; -- 以book表为主

b.右外连接

SELECT r.reader_id, r.name, b.title AS book_titleFROM book bRIGHT OUTER JOIN reader r ON b.reader_id = r.reader_id; -- 以reader表为主

c.全连接

SELECT b.title AS 书名, r.name AS 读者FROM book bFULL OUTER JOIN reader r ON b.reader_id = r.reader_id;

4.DCL(Data Control Language) 数据控制查询

负责控制数据库中用户权限 现在是root 超级管理员

（1）管理用户

1）添加用户

-- 创建一个名为\'username\'的新用户，密码为\'username\'CREATE USER \'username\'@\'localhost\' IDENTIFIED BY \'username\';

2）删除用户 

-- 删除名为\'username\'的用户DROP USER \'username\'@\'localhost\';

（2）权限管理

1）查询权限

-- 查询当前用户的权限SHOW GRANTS FOR CURRENT_USER;-- 查询名为\'username\'用户的权限SHOW GRANTS FOR \'username\'@\'localhost\';

 2）授予权限

-- 授予\'username\'用户对One_db数据库的所有权限GRANT ALL PRIVILEGES ON One_db.* TO \'username\'@\'localhost\';-- 授予\'username\'用户对所有数据库的SELECT和INSERT权限GRANT SELECT, INSERT ON *.* TO \'username\'@\'localhost\';

3）撤销权限

-- 撤销\'username\'用户对One_db数据库的所有权限REVOKE ALL PRIVILEGES ON One_db.* FROM \'username\'@\'localhost\';-- 撤销\'username\'用户对所有数据库的SELECT和INSERT权限REVOKE SELECT, INSERT ON *.* FROM \'username\'@\'localhost\';