常用SQL汇总

--创建数据库
create database 数据库名 charset=utf8;

--删除数据库
drop database 数据库名;

--查看当前数据库中所有表
show tables;

--查看表结构
desc 表名;

--创建班级表
create table 表名(
    id int unsigned auto_increment primary key not null,
    name varchar(10)
)

--创建学生表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchear(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男'，'女')，
    cls_id int unsigned default 0
)

--修改表
--添加字段
alter table 表名 add 列名 类型;
例：
alter table students add birthday datetime;

--修改字段 重命名
alter table 表名 change 原名 新名 类型及约束;
例:
alter table students change birthday birth datetime not null;

--修改表-修改字段-不重命名
clter table 表名 modify 列名 类型及约束;
例：
alter table students drop birthday;

--删除表
drop table 表名;

--查看表的创建语句
show create table 表名；


--增删改查(curd)
创建(Create)  更新(Update) 读取(Retrive) 删除(Deleate)


--查询所有列
select * from 表名;
例：
select * from classes;

--可以使用as为列或表指定别名
select 列1，列2  from 表名；


--增加
insert into 表名 values(...)
例:
insert into students values(0,'郭靖','1','蒙古','2016-1-2');


--部分插入
insert into 表名(列1,...) values(值1,...)

--插入多行数据
insert into 表名 values(...),(...)...;
insert into 表名(列1,列2) values(值1，值2)，(值1，值2);


--修改
update 表名 set 列1=值1，列2=值2  where 条件；

--删除
delete from 表名 where 条件;

--备份数据库
mysqldump -uroot -p 数据库 > test.sql;
# 需要输入密码

--恢复数据库
mysql -uroot -p 数据库库名 < test.sql

--数据库设计
先设计E-R图模型
E表示一个实体
R表示关系
    一对一，一对多，多对多

三范式
    一、原子性 不能再拆分了
    二、一个表只有一个主键；二是没有包含再主键中的列必须完全依赖主键
    三、非主键列必须之间依赖于主键，不能存在传递依赖。


逻辑删除
    对于重要数据，不希望物理删除，一旦删除，数据无法找回



--查询
--消除重复行
# 在select后面列前使用distinct可以消除重复行
select distinct  列1，from 表名;

--条件
select * from 表名 where 条件；


--比较运算符
=
>
>=
<
<=
!=

--逻辑运算符
and
or
not


--模糊查询
like
%    表示任意多个任意字符
_    表示一个任意字符


--范围查询

in     表示在一个非连续的范围
--查询编号是1或是3或8的学生
select * from students where id in(1,3,8);

between... and...  表示在一个连续的范围内
--查询编号为3至8的学生
select * from students where id between 3 and 8;

--空判断
null 与 ‘’是不同的
判空 is null
判非空 is not null

例：
查询没有填身高的学生
select * from students where height is null；

判非空
select * from students where height is not null；

--优先级
由高到低  小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or 需要结合()使用


--排序
order by
默认从小到大 (asc)
desc 从大到小排序,即降序
例：
select * from students order by age desc, height desc;


--聚合函数
总数
count(*)

最大值
max(列)

最小值
min(列)

求和
sum(列)

平均值
avg(列)

--分组
group by
可以用于单个字段分组，也可用于多个字段分组
group_concat(字段名) 可以作为一个输出的字段来使用
group by + group_concat()

例：
根据性别分组，查看都有几种性别；
select gender from students group by gender;

根据性别分组，查看每种性别都包含谁
select gender,group_concat(id) from studetns group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男     | 3,4,8,9,14       |
| 女     | 1,2,5,7,10,12,13 |
| 中性   | 11               |
| 保密   | 6                |
+--------+------------------+


group by + 函数集合
例：
分别统计不同性别的平均年龄
select gender,avg(age) form students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男     |  32.6000 |
| 女     |  23.2857 |
| 中性   |  33.0000 |
| 保密   |  28.0000 |
+--------+----------+


group by + having
1.having 条件表达式： 用来分组查询后指定一些条件来输出结果
2.having 作用和where一样，但having只能用于group by
例：
根据性别分组，查询出大于2人的性别，和该性别的人数
select gender，count(*) from students group by gender having count(*) >2;


group by + with rollup
1.with rollup 的作用是：在最后新增一行，来记录单曲列里所有记录的总和
例如：
查询不同性别的人数并统计一共由多少人；
select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+
select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age)                         |
+--------+-------------------------------------------+
| 男     | 29,59,36,27,12                            |
| 女     | 18,18,38,18,25,12,34                      |
| 中性   | 33                                        |
| 保密   | 28                                        |
| NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+


分行
select * from 表名 limit start，count

例:
    查询出前三行男生信息
    select * from students where gender=1 limit 0,3;

例：
    求第n页的数据
    m 条数
    n  第n页
    select * from students where is_delete=0 limit (n-1)*m,m
    

--连接查询
    inner join 内连接：查询的结果为两个表匹配到的数据
    right join 右连接：查询结果为两个表的数据，匹配右表数据，对于左表不存在的数据使用null填充
    left join 左连接：与右连接同理

例：
    select * from 表1 [inner 或 left 或 right] join 表2 on 表1.列 = 表2.列；

    使用内连接查询班级表与学生表
    select * from students inner join classes on students.cls_id = classes.id;
    
    使用左连接查询班级表与学生表
    select * from students as s right join classes as c on s.cls_id = c.id;

    查询学生姓名及班级
    select  s.name,c.name from students as s inner join classes as c on c.id = s.cid;


--自关联
    建表：
        create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
    );

    表中的某一列，关联了这个表中的另外一列

    例：
        查询一共有多少个省
        select  count(*) from areas where pid is null;

        查询省的名称为“山西省”的所有城市
        select city.* from areas as city
        inner join areas as province on city.pid=province.aid
        where province.atitle='山西省'；

        查询市的名称为'广州市'的所有区县
        select dis.* from areas as dis
        inner join areas as city on city.aid=dis.pid
        where city.atitle='广州市'；



--子查询
在一个select语句中，嵌入了另一个select语句，那么被嵌入的select语句称之为子查询语句

例：
    查询年龄大于平均年龄的学生
    select * from students where age > (select avg(age) from students);

    查询还有学生在班的所有班级名字
    1.找出学生表中所有的班级id
    2.找出班级表中对应得名字
    select name from classes where id in ( select cls_id form students);

    查找班级年龄最大，身高最高得学生
    select * from students where (height, age) = (select max(height), max(age) form students);

--查询的完整格式
SELECT select_expr [,select_expr,...] [      
      FROM tb_name
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...]
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count



SQL查询示例：
求所有电脑产品的平均价格，并且保留两位小数
select round(avg(prive),2) as avg_prive from goods;

显示每种商品的平均价格
select cate_name,avg(price) from goods group by cate_name;

查询每种类型的商品中 最贵 最便宜 平均价 数量
select cate_name, max(price), min(price), avg(price)，count(*) from goods group by cate_name;

查询所有价格大于平均价格的商品，并且按价格降序排序
select id,name, price from goods
where price > (select round(avg(price),2) as avg_price form goods)

查询每种类型中最贵的电脑信息
select * from goods
inner join
        (
            select
                        cate_name, 
                        max(price) as max_price,
                        min(price) as min_price,
                        avg(price) as avg_price,
                        count(*) from goods group by cate_name
        ) as goods_new_info 
on goods.cate_name=goods_new_info.cate_name and goods.price=goods_new_info.max_price;
        


--视图
视图就是一条select 语句执行后返回的结果集
视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据(基本表数据发生了改变，视图也会跟着改变)；

方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

定义视图
    create view 视图名称 as select 语句;

查看视图
    show tables；

使用视图
select * from v_stu_score;

删除视图
drop view 视图名称;


--事务
事务四大特性(ACID)
原子性 Atomicity
一致性 Consistency
隔离性 lsolation
持久性 Durabililty

在一个事务中，任何一个步骤失败，必须回滚所有的步骤。

--开启事务
begin；
或者
start transaction；

--提交事务
commit;

--回滚事务
rollback;


--索引
目的是为了提高查询效率 (用空间换时间)

--查看索引
show index  from 表名；

--创建索引
create index 索引名称 on 表名(字段名称(长度))

--删除索引
drop index 索引名称 on 表名；

建立太多的索引将会影响更新和插入的速度

--账户管理
查看user表结构
desc user；
Host 表示允许访问的主机
User 表示用户名
authentication_string 表示密码，为加密后的值

查看所有用户
select host，user，authentication_string from user;

--创建账户、授权
* 需要使用实例级账户登录后操作，以root为例
* 常用权限主要包括：create、alter、drop、insert、update、delete、select
* 如果分配所有权限，可以使用all privileges

grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码'；


创建账户并授予所有权限
grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';

查看用户有哪些权限
show grants for laowang@localhost;

修改权限
grant 权限名称 on 数据库 to 账户@主机 with grant option;

修改密码
update user set authentication_string=password('新密码') where user='用户名';

刷新权限
flush privileges