Mysql语句集合

SQL基本语句:

    - 清屏 ctrl + L

    - 断开与服务器连接：Ctrl + D, exit, quit

    - 查看当前时间 select now()

    - 查看版本 select version()

    - 切换数据库 use mysql;

    - 查看当前使用的数据库 select database();

    - 查看所有数据库 show databases;

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

    - 删除数据库 drop database py7;

    - 查看创建的数据库SQL语句：show create database py7;

    - 创建表结构：以students为例:

      create table classes(

          id int unsigned primary key auto_increment not null,

          name varchar(20) default ‘’,

          age tinyint unsigned default 0,

          height decimal(5,2),

          gender enum('男', '女', '人妖', ‘保密’),

          cls_id int unsigned default 0

      );

    - 删除表 drop table stu；

    - 查看表：show tables;

    - 查看创建表的结构SQL语句 create table stu;

    - 查看数据库的表结构(describe)：desc test_table;

    - 添加字段 alter table 表名 add 字段名 类型 约束;

        - alter table stu add birth int unsigned;

    - 修改字段（重命名）alter table 表名 change 原名 新名 字段名 类型 约束;

        - alter table stu change birth birthday;

    - 修改字段（不重名）alter table modify 字段名 类型 约束;

    - 删除字段 alter table stu drop 字段名;

    - 查看表数据 select * from 表名;

        - select 字段名 from 表名;

        - select name * as '姓名', age * as '年龄' from students;

    - 插入数据 insert into 表名 values ('', ''), ('','');

        - # 插入是如果是主键，可以输入0或者null，自动增长。

          insert into students values (0,’zheng’,18, 1.72, ‘male’, 0);

          insert into students values (null,’ming’,18, 1.72, ‘male’, 0);

        - # 指定顺序输入

          insert into students (name,id,age,height,gender,cls_id) values

          -> (‘zheng’, 0, 12, 1.72, male, 0)，

          -> (‘ming’, 0, 12, 1.72, male, 0);

        - # 插入单列

          insert into focus(id)(select id from info where code = 00007);

    - 删除数据 delete from students where id = 7;

    - 更新数据 update students set height=1.65 where id=5;

    - 逻辑删除 alter table scores add is_deleter bit default 0;

        - update scores set is_delete=1 where score=22;

---

Mysql高级查询语句：

消除重复行：

    - # 只是查阅的时候消除，数据本身没有被删除。

      select distinct age from students;

重命名：

    - select s.age as ‘年龄’, s.name as ‘芳龄’ from students as s;

      错误写法：select students.age as ‘年龄’, students.name as ‘芳龄’ from students as s;

条件查询：

    - select * from students where gender >= 2;

不等于：!= 或 <>

    - select name from students where gender <> 1;

逻辑运算：and or not

    - select * from students where age < 25 or height > 175;

    - select * from students where not age < 45;

模糊查询：like；%代表任意多个字符；_代表任意一个字符；

    - select * from students where name like ‘郭%’;

    - select * from students where name like ‘黄_’;

范围查询：in

    - select * from students where id in (1,5,7);

范围查询：between … and … [闭区间]

    - select * from students where id between 2 and 9;

    - select * from students where age not between 20 and 50;

    - ps: 空判断 null 不等于 ‘'

      select * from students where height is not null;

优先级：从高到低：小括号，not，比较运算符，逻辑运算符；and 比 or 先运算。

排序：升序asc(ascending)，降序desc(descending)，默认为升序。

    - select * from students order by age desc;

    - select * from students order by age, height desc;

---

聚合函数：

计算总行数：

    - count(*)，一般来说*和列名，结果相同；除了某个字段拥有null

    - select count(*) from students;

去重：select count(distinct gender) from students;

最大值/最小值：

    - select max(height) from students;

    - select min(height) from students;

求和：select sum(age) from students;

求平均average（不算null，有null只能算平均身高）：

    - select sum(height)/count(height) from students;

      ==> select avg(height) from students;

分组：group by

    - select gender from students group by gender;

    - # 只能显示一个二维表，所以需要追加显示group_concat()

      select gender, group_concat(name,age) from students group by gender;

分组和聚类函数一起计算：

    - select gender, arv(age) as '平均年龄’ from students group by gender;

    - 筛选条件，having，与where类似，但是只能用于group by：

    - select gender, count(name) from students group by gender having count(name) > 3;

汇总结果显示在下一行：

    - select gender, count(name), from students group by gender with rollup;   

分页：select * from 表明 limit [起始位置,] 数量;

    - # 显示第一页

      select * from students order by age limit 3;

    - # 显示第二页

      select * from students order by age limit (2-1)*3,3;

---

连接查询：当插叙结果来源于多个表示，需要将多个表的结果返回为一个大数据集，可以选择需要的列返回。

    1> 内连接：查询结果为两个表匹配到的交集。

        a> A inner join B 获得A和B的笛卡尔集：select * from classes inner join students;

        b> on 可以在笛卡尔集的基础上过滤：

           select * from classes inner join students on

           classes.id = students.cls_id order by classes.id;

        c> join可以获得笛卡尔积，在mysql中inner join 与 join 等同，但是在其他数据库不同。

    2> 右外连接：在内连接的基础上，添加额外数据，数据来源于右表未和左表匹配数据，其中左表列的字段用null表示

        a> 即右表全部显示，与左表不匹配的信息字段为null

        b> select * from students right join classes on classes.id = students.id;

    3> 左外连接：在内连接的基础上，左边中与右表不匹配的数据显示出来，其中右表列的字段用null表示

---

自关联：

    1> 例子：020  广东省, 0755 深圳市 020

         -> 020  广东省  null

            0755 深圳市  020

    2> 查询省市：

       select * from areas as pro inner join areas as city on pro.aid = city.pid limit 10；

    3> 查询某个省的市：

       select city.* from areas as pro inner join areas as city on

    -> pro.aid = city.pid where pro.atitle = ‘北京市’;

子查询：

    1> 在select中嵌入多一个select，嵌入的select称为子查询，第一条select为主查询。

    2> 子查询与主查询的关系：

        a> 子查询是嵌入到主查询中的。

        b> 子查询是辅助主查询的，要么充当条件，要么充当数据源。

        c> 子查询是可以独立存在的语句，是一条完整的select语句。

    3> 子查询分类：

        a> 标量子查询：子查询返回结果是一个数据（一行一列）

            i> 查询班级学生大于平均升高的人

               select * from students where age > (select avg(age) from students);

        b> 列子查询：返回结果是一列多行

            i> 查询还有学生在班的班级

               select * from classes where id in (select cls_id from students);

        c> 行子查询：返回结果是一行多列

            i> 查询身高和年龄同时最高的人

               select * from student where (age, height) =

            -> (select max(age), max(height) from students);

---

SQL 高级查询演练：

    1> 查询所有商品平均价格并且保留2位小数：

       select round(avg(price),2) as average from goods;

    2> 查询每个类型中最贵的商品信息：

       select * from goods join

       (select brand_id, max(price) as max_p, group_concat(price)

       from goods group by brand_id) as good_info

       on goods.price = good_info.max_p;

    3> 将查询结果直接插入另外一个表：

       insert into good_cates(name) select cate_name from goods group by cate_name;

    4> 创建商品品牌表：

       create table brand_cate(

       id int unsigned primary key auto_increment,

       name varchar(40) not null)

       select brand_name as name from goods group by brand_name;

       更新goods表内brand_name 为 id：

           a> select * from goods join brand_cates on goods.brand_name = brand_cates.name;

              # 先查询两个表的笛卡尔表中brand name 对应的数据；

           b> update goods join brand_cates on goods.brand_name = brand_cates.name

              set goods.brand_name = brand_cates.id;

              # 更新数据

           c> alter table goods change brand_name brand_id int unsigned not null;

---

外键约束：

    1> 允许我们在更新或者检测数据的时候，对数据的有效性进行检测，防止插入无效数据。

    2> 添加外键：

       alter table goods add foreign key(good_id) references good_cates(id);

       # 前提要求goods表中没有异常数据，且后期插入异常数据时会抛出异常。

       删除外键：

       show create table goods;

       # 获取系统自动生成的外键名。

       alter table goods drop foreign key 外键名；

    3> 外键插入容易造成死锁，如在goods插入外键时，会查找brand_cates的id，如果后者在更新，就会造成死锁。

    4> 因此一般会在流量少的时候，去检测数据是否符合：

       select * from goods left join brand_cates on goods.brand_id = brand_cates.id

       left join good_cates on goods.brand_id = good_cates.id;

    5> 当创建表时如果需要设置外键，优先创建外键表。

---

视图：

    1> 通俗来讲，就是一条select语句返回的值，封装了对基本表的查询操作，因此只能够查看select，不能够通过update修改数据

    2> create view v_视图名称 as select语句；   # 创建时，多个列表中重名字段会报异常。

       select * from v_视图名称;

       drop view v_视图名称；

    3> create table - 基本表，create view - 虚表

    4> 作用：

        i> 提高了重用性，像一个函数。

        ii> 对数据库重构，却不影响程序的运行。

        iii> 提高了安全性能，只能够select，不能够update。

        iv> 让数据更加清晰。

---

事务：

    1> 事务是多个SQL操作序列，不可分割的工作单位，保证多个操作要么同时成功，如果有其中一个失败，就回滚到最初状态。

    2> 四大特性：

        -> 原子性(atomicity): 不可分割的最小工作单元。不能够只是执行某一部分。

        -> 一致性(consistency): 即使某一条操作语句错误，因为事务最终没有提交，所做的修改也不会保存到真实数据库中。

        -> 隔离性(isolation): 事务所做的修改在最终修改之前，对其他事务是不可见的。

        -> 持久性(durability): 一旦事务提交，所做的修改将会永久保存，不允许撤销。

    3> 表的引擎类型必须是innodb类型，才可以使用事务。

    4> 事务的操作命令：

        开始事务 -> begin; 或 start transaction;

        提交事务 -> commit；

        回滚事务 -> rollback;

    5> 注意事项：

           -> 以下命令会自动执行事务：对表数据增删改查命令，如delete，update，insert等。

           -> 但是系统默认自动commit，可以通过set autocommit = 0; 修改为不自动提交

           -> 以下命令会自动提交：凡是对于数据库、表的操作是不能够回滚的，即便在begin里也一样。如drop，alter，create等。

---

索引：

    1> 简介：是一种特殊的文件（innodb数据表上索引是表空间的一个组成成分），他们包含着对数据表例所有记录的引用指针。

            一般是主键，自带非空，唯一性和索引属性。

    2> 特点：数据无序，指针有序，所以在插入数据时索引会更新，影响效率；所以一般不执行插入，主要用于查询。

    3> 索引的使用：

        a> 查看索引：show index from 表名;

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

           # 如果指定字段是字符串，需要指定长度，建议长度与字段长度一致

           create index idx_name on students(name(32))

           create index idx_age on students(age)

        c> 删除索引：drop index 索引名称 on 表格;

    4> 查询操作的时间：

        -> set profiling = 1;

        -> show profiles; # 查看所有

        -> show profile; # 查看当前

    5> 注意点：

        a> 索引会占用磁盘空间，对于需要经常插入和更新的表格不建议使用。

        b> 对于小的表格也没有必要使用索引。

---

mysql> select * from student where id in (select stu_id from score where c_name = '英语' or c_name = '计算机' group by stu_id having count(c_name) = 2);