Mysql入门笔记

一、Mysql数据类型

        在 MySQL 中，有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。

    

    

    

二、Mysql语法

    （1）select：select distinct column1,column2.... from table xxxxx：去某一列或者多列值相同的行记录（去重）

    （2）where子句：文本使用引号，数值不使用引号

            1.between and：Select * from emp where sal between 1500 and 3000; 指定值的范围

            2.In：Select * from emp where sal in (5000,3000,1500); 指定多个值

               not int：sal not in (5000,3000,1500);不等于某些值

            3.like：Select * from emp where ename like '%M%'; 模糊查询

            4.limit：select column_name(s) from table_name limit number1【，number2】;

                SELECT * FROM Websites LIMIT 2;相当于【limit 0,2】，表示返回从第一条（索引从0开始）开始，向右偏移2条数据，也就是0,1两条数据，通常用于分页

            5.and or混合使用： 括号( )的优先级高于AND运算符，AND运算符的运算级别要高于OR运算符

            例： 假如需要查询所有计算机系和生物系中，且工资收入（SAL）超过1000的教师的姓名（TNAME）、系（DNAME）、工资（SAL）信息。如果采用如下SQL代码：         

   SELECT TNAME, DNAME, SAL

        FROM TEACHER

        WHERE DNAME='计算机'

        OR DNAME='生物'

        AND SAL>1000

        ORDER BY SAL;

        查询结果

              

             显然没有得到预期的计算结果，这是因为AND运算符的运算级别要高于OR运算符，代码实际上实现的查询是计算机系的所有教师以及生物系中的工资大于1000的教师的相关信息。要实现例中需要的查询结果，正确的SQL代码为：           

  SELECT TNAME, DNAME, SAL

        FROM TEACHER

        WHERE (DNAME='计算机' OR DNAME='生物')

        AND SAL>1000

        ORDER BY SAL;

    （3）insert into：insert into table (column1,column2,column3,...) values (value1,value2,value3,...);插入数据

    （4）update...set：update table set column1=value1,column2=value2,... where some_column=some_value; 修改某条数据,不加where字句，修改所有行

    （5）delete from：DELETE FROM table_name where some_column=some_value;，不加where字句，删除所有行

    （6）drop：drop table tablename；        drop database dbname；删除表或者数据库

    （7）alert：增删该表的字段

            修改字段的属性：

  

alter table tabletablename  change old_column_name new_column_name varchar(20) not null;//修改字段名，(要重新指定该字段的类型)

alter table tablename  modify column_name varchar(20)  comment'注释';//修改字段数据类型及注释

alert table tabletablename add `new_column` varchar(255) not null comment '新字段的注释'; //增加一个字段

    

    （6）联接查询join：inner join内联，left join左联，right join右联，full join全联接（mysql不支持）

    （7）联合查询union： select语句返回的列必须具有相同或可转换的数据类型，大小和相同的顺序和数量                

SELECT  column1, column2 FROM table1 

UNION [ALL] 

ELECT column3, column4 FROM table2;

             首先执行两个SELECT语句来处理查询。 然后，它将两个单独的结果集合并为一个，【并消除重复的行】

            联合与联接不同，联接组合了多个表的列，而联合组合了表的行

三、sql约束

    在创建表的时候就给列规定约束：
    （1）NOT NULL - 指示某列不能存储 NULL 值。

            下面的 SQL 强制 "P_Id" 列和 "LastName" 列不接受 NULL 值：

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    UNIQUE (P_Id)//定义单个约束
    CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)//定义多个约束
)

    （2）UNIQUE - 保证某列的每行必须有唯一的值。

    （3）PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

P_Id int NOT NULL AUTO_INCREMENT//P_Id主键自增

PRIMARY KEY (P_Id)

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)//只有一个主键 PRIMARY KEY（pk_PersonID）

    然而，pk_PersonID 主键的值是由两个列（P_Id 和 LastName）组成的

    （4）FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

PRIMARY KEY (O_Id),

FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

        外键约束在实际开发中不建议使用

    （5）CHECK - 保证列中的值符合指定的条件。

CHECK (P_Id>0)

    （6）DEFAULT - 规定没有给列赋值时的默认值。

City varchar(255) DEFAULT 'Sandnes'

 

四、视图

    什么是视图：视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。可以向视图添加 SQL 函数、WHERE 以及 JOIN 语 句，也可以呈现数据，就像这些数据来自于某个单一的表一样。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。

    视图特性：视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；可以跟基本表一样，进行增删改查操作(ps:增删改操作有条件限制)

    视图作用：方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；

    使用场合：权限控制的时候，不希望用户访问表中某些含敏感信息的列，比如salary...

                     关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作；

    创建视图：

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

    更新视图：

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

    删除视图：

DROP VIEW view_name

    使用视图：和使用普通表一样

select * from viewname;

 

五、Mysql函数

    （1） MySQL 数值型函数

    

        使用示例：

        

    （2）字符串函数

        

    使用示例：

    

    （3） 日期和时间函数

        .......

    （4） 聚合函数

    

        使用示例：

        

六、分组查询

    group by：将查询结果按照1个或多个字段进行分组，字段值相同的为一组

    （1） 当group by单独使用时，只显示出每组的第一条记录，所以group by单独使用时的实际意义不大

    （2）group by + group_concat()：表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

        

    （3）group by + 集合函数：通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个"值的集合"做一些操作

    

    

    

    （4）group by + having：用来分组查询后指定一些条件来输出查询结果， having作用和where一样，但having只能用于group by

    

七、Msql执行顺序

    开始->FROM子句->ON->JOIN->WHERE子句->GROUP BY子句->聚合函数->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果

    理解记忆：

（1）当多表查询史，肯定是先处理多表的数据联接为一个表，所以先执行from字句选择表做基础表，生成虚拟表 vt1，

（2）然后on筛选器符合条件的行生成虚拟表vt2

（3）使用join语句将on筛选的vt2联接到基础表vt1，生成vt3

（4）然后在表中使用where筛选器筛选符合条件的记录生成虚拟表vt4，

（5）然后使用group by字句将指定列名中值一样的值筛选到一组，得到虚拟表vt5，如果应用了group by，那么后面的所有步骤都只能得到的vt5的列或者是聚合函数（count、sum、avg等），原因在于最终的结果集中只为每个组包含一行。这一点请牢记。

（6）下一步使用聚合函数进行运算，生成vt6，

（7）应用having筛选器，生成vt7，having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。

（8）处理select子句，将vt7中的在select中出现的列筛选出来，生成vt8。

（9）应用distinct子句，vt8中移除相同的行，生成vt9。事实上如果应用了group by子句那么distinct是多余的，原因同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么所以的记录都将是不相同的。

（10）应用order by子句。按照order_by_condition排序生成vt9，此时返回的一个游标，而不是虚拟表。sql是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用order by 子句查询不能应用于表表达式。排序是很需要成本的，除非你必须要排序，否则最好不要指定order by，最后，在这一步中是第一个也是唯一一个可以使用select列表中别名的步骤。

（11）应用top选项（mysql是limit关键字）指定需要哪些行的数据，此时才返回结果给请求者即用户。

 

 

五、mysql索引及优化

    （1）什么是索引，以及索引的作用

           一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

       在数据量和访问量不大的情况下，mysql访问是非常快的，是否加索引对访问影响不大。但是当数据量和访问量剧增的时候，就会发现mysql变慢，甚至down掉，这就必须要考虑优化sql了，给数据库建立正确合理的          索引，是mysql优化的一个重要手段。索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你          可能需要把所有单词看一遍才能找到你想要的。除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同          时把随机事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。在创建索引时，需要考虑哪些列会用于 SQL 查询，然后为这些列创建一个或多个索引。

            事实上，索引也是一种表，保存着主键或索引字段，以及一个能将每个记录指向实际表的指针。数据库用户是看不到索引的，它们只是用来加速查询的。数据库搜索引擎使用索引来快速定位记录。 INSERT UPDATE        语句在拥有索引的表中执行会花费更多的时间，而SELECT 语句却会执行得更快。这是因为，在进行插入或更新时，数据库也需要插入或更新索引值。

    （2）索引的创建和删除

        索引的类型：

        •UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值

        •INDEX(普通索引)：允许出现相同的索引内容

        •PROMARY KEY(主键索引)：不允许出现相同的值，只能存在一个

        •fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维

        •组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

        1、创建索引：

        注意：

        1、索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够

        2、创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行

            (1)使用ALTER TABLE语句创建索性

  

    ALTER TABLE 表名 ADD 索引类型（unique,primary key,fulltext,index）[索引名]（字段名）

    //普通索引

    alter table table_name add index index_name (columns) ;

    //唯一索引

    alter table table_name add unique (columns) ;

    //主键索引

    alter table table_name add primary key (columns) ;

            ALTER TABLE可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式，table_name是要增加索引的表名，columns指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name                  可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引        

        

         （2）使用CREATE INDEX语句对表增加索引

                CREATE INDEX可用于对表增加普通索引或UNIQUE索引。

        CREATE INDEX index_name ON table_name(username(length));

               如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

        //create只能添加这两种索引;

        CREATE INDEX index_name ON table_name (columns)

        CREATE UNIQUE INDEX index_name ON table_name (columns)

                table_name、index_name和columns具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引

                在建表时添加索引：

        CREATE TABLE mytable(

            id INT NOT NULL,

            username VARCHAR(16) NOT NULL,

            INDEX [indexName] (username(length)

        );

        （3）删除索引

                 删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

        drop index index_name on table_name ;

        alter table table_name drop index index_name ;

        alter table table_name drop primary key ;

                其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如                   果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

                如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

        （4）组合索引与前缀索引

                在这里要指出，组合索引和前缀索引是对建立索引技巧的一种称呼，并不是索引的类型。为了更好的表述清楚，建立一个demo表如下：

        create table USER_DEMO

        (

           ID                   int not null auto_increment comment '主键',

           LOGIN_NAME           varchar(100) not null comment '登录名',

           PASSWORD             varchar(100) not null comment '密码',

           CITY                 varchar(30) not null comment '城市',

           AGE                  int not null comment '年龄',

           SEX                  int not null comment '性别(0:女 1：男)',

           primary key (ID)

        );

                为了进一步榨取mysql的效率，就可以考虑建立组合索引，即将LOGIN_NAME,CITY,AGE建到一个索引里：

       ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE);

                建表时，LOGIN_NAME长度为100，这里用16，是因为一般情况下名字的长度不会超过16，这样会加快索引查询速度，还会减少索引文件的大小，提高INSERT，UPDATE的更新速度。

                如果分别给LOGIN_NAME,CITY,AGE建立单列索引，让该表有3个单列索引，查询时和组合索引的效率是大不一样的，甚至远远低于我们的组合索引。虽然此时有三个索引，但mysql只能用到其中的那个它认为                   似乎是最有效率的单列索引，另外两个是用不到的，也就是说还是一个全表扫描的过程。

                建立这样的组合索引，就相当于分别建立如下三种组合索引：

        LOGIN_NAME,CITY,AGE

        LOGIN_NAME,CITY

        LOGIN_NAME

                为什么没有CITY,AGE等这样的组合索引呢？这是因为mysql组合索引“最左前缀”的结果。简单的理解就是只从最左边的开始组合，并不是只要包含这三列的查询都会用到该组合索引。也就是说                                            name_city_age(LOGIN_NAME(16),CITY,AGE)从左到右进行索引，如果没有左前索引，mysql不会执行索引查询。    
                如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引，前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)。

        SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- 这个值大于0.31就可以创建前缀索引,Distinct去重复

        ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- 增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度

           （5）索引使用情况

                1.单列索引（可以有多个单列索引）： 

                    A.只涉及到其中的一个字段时，都能使用到索引

                    B. 涉及到多个索引字段时,如果这些索引字段中，存在主键索引，那么只会使用该主键索引

                   C. 涉及到多个索引字段时,如果这些索引字段中，不存在主键索引的话，那么就会使用该使用的索引, 先使用哪个索引后使用哪个索引，是由MySQL的优化器经过一些列计算后作出的抉择

                2.组合索引

                    最左原则:假设组合索引为:a,b,c的话;

                    A.那么当SQL中对应有:a或a,b或a,b,c的时候，可称为完全满足最左原则; 会完整的走组合索引

                    B.当SQL中对应只有a,c的时候，可称为部分满足最左原则; a走索引，a之后不在走索引

                    C.当SQL中没有a的时候，可称为不满足最左原则。不走索引

                        其实也不是完全不走索引，用explain可以发现，如果sql中有b的过滤条件，那么mysql会使用type=index的方式检索索引（从索引表第一个，到最后，挨个检索，效率不高）

                    说明： 如果SQL语句里的字段里，满足了最左原则，但是不满足“索引”自身的使用规范，那么组合索引走到这里之后，不会再往下走了。

                              比如sql中使用了啊a，b，c，且b字段条件b>1，b 不满足“索引”自身的使用规范，那么组合索引走就只能走到a，b以及b以后的索引不会再往下走了。

    （3）索引使用原则及优化

          1.选择唯一性索引唯一性索引的值是唯一的,可以更快捷的通过该索引来确定某条记录.

          2.索引的列为where 后面经常作为条件的字段建立索引如果某个字段经常作为查询条件,而且又有较少的重复列或者是唯一咧可以考虑作为索隐列经常作为查询条件的列作为索引会提高速度

          3.位经常需要进行排序.分组和联合操作的的字段建立索引.order by  group by  distinct union这种情况下在查询的时候排序会浪费很多的时间,如果为其建立索引可以有效的避免排序操作.

          4.限制索引的的数目,索引的数目多,对系统的资源也是一种消耗,删除修改也会费资源.

          5.劲量使用数据量少的索引， 或者索引前缀索引.如果索引的值很长, 查询速度就会受到影响.

          6.尽量使用前缀来索引，如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

          7.删除不再使用的索引.数据或者业务变更,数据方式变更就需要,删除无用的索引.

          8.小表不应该建立索引.

          9.索隐列避免空列,一般选非空的列.

            简单记忆建议索引的原则是 :唯一列 经常被查询   排序 预先建立索引    总体控制数量   使用字段少的列索引  前缀索引   删除无用 小表不建

   

    不走索引的情况:

    1.没有查询条件，没where 后面的内容  查询条件没索引

    2.查询条件没引导列.  没有有索引的列

    3.查询数量是超过表的一部分,mysql的30%,oracle的20%

    4.索引失效,索引插入过多可能发生意外失效

    5.查询条件使用count，sum，avg，left等函数在索隐列上面计算等.

    查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

    错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10;

    6.对小表查询

    7.统计数据不真实.

    8.CBO计算走索引花费过大的情况

    9查询条件字符串和数字等的隐式转换.

    10.!= <>  

    11.%% 两个百分号不走索引,开始的结尾的百分号走索引.

    14 not in  not exist  in 尽量转换为union

    15.time 和date 时间格式不一致

    16.B-tree索引is null不会走,is not null会走,位图索引 is null,is not null 都会走

    17.如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字

        select * from dept where dname='xxx' or loc='xx' or deptno=45 

• MyISAM 存储引擎索引键长度总和不能超过1000 字节；
• BLOB 和TEXT 类型的列只能创建前缀索引；（索引字段值过长也最好使用前缀索引）
• MySQL 目前不支持函数索引；
• 使用不等于（!= 或者<>）的时候MySQL 无法使用索引；
• 过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引；
• Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引；
• 使用LIKE 操作的时候如果条件以通配符开始（ '%abc...'）MySQL 无法使用索引；
• 使用非等值查询的时候MySQL 无法使用Hash 索引；
• 在我们使用索引的时候，需要注意上面的这些限制，
• 尤其是要注意无法使用索引的情况，因为这很容易让我们因为疏忽而造成极大的性能隐患。

 

    （4）MySQL前缀索引和索引选择性

         有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值（也        称为基数，cardinality)和数据表的记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索        引选择性，性能也是最好的。

         一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。诀窍在于要选择足够长        的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引的整个列。换句话说，前缀的”基数“应该接近于完整的列的”基数“。

        前缀到底多长合适呢？通过下面方法来计算：

    

    //计算完整列的选择性    

    

select count(distinct city) / count(*) from city_demo;

        

           //计算不同前缀长度的选择性,选择其中最接近完整列选择性的前缀长度

            

select count(distinct left(city,3))/count(*) as sel3,count(distinct left(city,4))/count(*) as sel4,count(distinct left(city,5))/count(*) as sel5,count(distinct left(city,6))/count(*) as sel6 from city_demo;

        

            可以看见当索引前缀为6时的基数是0.4267，已经接近完整列选择性0.4283。

            在上面的示例中，已经找到了合适的前缀长度，下面创建前缀索引：

  alter table city_demo add key (city(6));