windows mysql-------学习路线（二）

表结构修改

复制表结构：

        create table 新表名 like 原表  （只复制结构）

        create table 新表名 select * from 原表   （复制表结构和数据）

修改表结构：

        alert table 表名 drop 字段名 ------删除字段名

        alert table 表名 add  新字段名  新数据类型 ------增加新字段

        alert table 表名 change 旧字段名  新字段名  新数据类型 ------修改字段（包括类型）

        alert table 表名 modify 字段名  新数据类型 ------修改字段的数据类型

        rename table 旧表名 to 新表名

        例1、

-- 将test字段改为test1
-- ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件
ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';

索引

索引是什么？

一个索引是存储的表中一个特定列的值数据结构，索引是在表的列上创建。所以，要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。请记住记住这一点：索引是一种数据结构 。

为什么需要使用索引？

索引是为了加快检索速度而建立的。（索引是否需要，索引的设计）

索引设计原则有哪些？

1、搜索的索引列，不一定是所要选择的列。也就是说，最适合索引的列是出现在where子句中的列，或者连接子句中指定的列，而不是出现在select关键字后的选择列表中的列。

2、使用唯一索引。考虑某列的分布，索引的列的基数越大，索引的效果越好。例如，对性别M/F列做索引没多大用处。

3、使用短索引。如果是对字符串进行索引，如果有可能应该指定前缀长度。

4、利用最左前缀。尽量将使用频繁且过滤效果好的字段放“左边”。

5、不要过度索引。

6、Innodb默认会按照一定的顺序保存数据，如果明确定义了主键，则按照主键顺序保存。如果没有主键，但有唯一索引，就按照唯一索引的顺序保存。如果有几个列都是唯一的，都可以作为主键的时候，为了提高查询效率，应选择最常用访问的列作为主键。另外，innodb的普通 index都会保存主键的键值，所有主键要尽可能选择较短的数据类型。可以看出，应当尽量避免对主键的修改。经过dba的测试，保证主键的递增可以提高插入性能。

索引的分类

    ------普通索引

   是最基本的索引，它没有任何限制。它有以下几种创建方式：

   （1）直接创建索引

CREATE INDEX index_name ON table(column(length))

  （2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

 （3）创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

  （4）删除索引

DROP INDEX index_name ON table

 

    ------唯一性索引

    与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种     创建方式：

 （1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

  （2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

  （3）创建表的时候直接指定

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

 

    ------主键索引

  是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

       注意：通过这个主键索引，我们就应该反应过来，其实我们以前声明的主键约束，就是一个主键索引，只是之前我们没学过，不知道而已。

    ------复合索引（也叫组合索引）

    指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

    ------全文索引
  主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简      单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table    ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个    没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
（1）创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

  （2）修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

  （3）直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)

表记录的插入、更新与删除

• 插入：insert 、批量插入、insert...select、replace

INSERT INTO 

items(name,city,price,number,picture) 

VALUES

('耐克运动鞋','广州',500,1000,'003.jpg'),

('耐克运动鞋2','广州2',500,1000,'002.jpg');

INSERT INTO task_user (t_id,name,age,type,u_id) SELECT 1,name,age,type,id as u_id FROM user where type=”金融” 

• 更新：update

UPDATE customers 
SET 
    salesRepEmployeeNumber = (SELECT 
            employeeNumber
        FROM
            employees
        WHERE
            jobtitle = 'Sales Rep'
        LIMIT 1)
WHERE
    salesRepEmployeeNumber IS NULL;

• 删除：delete、truncate 表名

truncate和不带where子句的delete、以及drop都会删除表内的数据。

区别：https://www.cnblogs.com/SaraMoring/p/5607537.html

select 语法

select 语句的语法格式：

 select 字段列表

 from  数据源

【where 条件表达式】

【group by 分组字段

   【having 条件表达式】

 】

【order by 排序字段【asc | desc】】

限制记录行数

• Distinct: 过滤重复记录
• Limit: 

select 字段列表
from 数据源
limit [start],length;


SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 

From 指定数据源

• From 表1，表2
• From 表1 join 表2

1. inner join
2. left  join
3. right  join

详细参考：https://www.jb51.net/article/39432.htm

逻辑运算符： ！，and，between，or，in

Where过滤结果集：

• 单一条件过滤
• Is  null
• 使用逻辑运算符
• Between...and...
• In
• Like模糊匹配
• 正则表达式模糊匹配

结果集排序

  order by 字段名1 [asc | desc]  [...,字段名n [asc | desc] ]

select ip from table_name order by inet_aton(ip);

常用聚合函数

sum

avg

count

max

min

group by分组统计

• group by子句将查询的结果按照某个字段（或多个字段）进行分组（字段相同的记录分为一组）
• group by与 having组合使用
• group_concat()
• with rollup :  用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息。https://blog.csdn.net/jiangnan2014/article/details/17229713
• concat ---CONCAT（）函数用于将多个字符串连接成一个字符串。

SELECT CONCAT_WS(',','First name',NULL,'Last Name');返回结果为
+----------------------------------------------+
| CONCAT_WS(',','First name',NULL,'Last Name') |
+----------------------------------------------+
| First name,Last Name                         |
+----------------------------------------------+

合并结果集：

• union all:  对两个结果集进行并集操作，包括重复行，不进行排序；
• union: 对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序。

select employee_id,job_id from employees
union/union all
select employee_id,job_id from job_history

子查询

• 子查询定义？

     一个select就是一个查询语句。如果它在 from 里面或者在where里，它就变成了一个 子查询语句。

    子查询里面还可以再有子查询，逻辑嵌套，可以无比复杂。

• 子查询应用场景？

常用子查询

• 比较运算符
• in运算符
• exists运算符
• any
• all

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

全文索引

• 定义

MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。

• 解决了什么问题？
• 举例

SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a')  //搜索`char`字段 'a' 值