初识MySQL（二）SQL语句

Silently • • Linux干货, 系统运维

    MySQL是关系型数据库的一种，基于二维表实现数据的存储与读取，通过索引实现快速查询，而实现数据库、表、索引的操作则是由SQL语句来完成的。

    1、MySQL中字符大小写

      （1）、SQL关键字以及函数名不区分大小写，但为增加缓存命中率，建议使用统一风格。

      （2）、数据库、表、索引视图名称等是否区分大小写取决与底层的OS以及文件系统（FS）。

      （3）、存储过程、存储函数以及事件调度器不区分大小写；但触发器区分字符大小写。

      （4）、表别名不区分大小写。

      （5）、字段中的数据类型为BINARY,BLOB,VARBINARY时区分大小写，其它不区分大小写。

    2、MySQL中引用对象规则

      MySQL使用单引号或者双引号引用字符串；使用反引号引用字段、表、视图等名称。

    DDL语句操作

    3、数据库

      （1）、创建数据库       

        CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

            [create_specification] …        

        create_specification:

            [DEFAULT] CHARACTER SET [=] charset_name   //指定字符集

          | [DEFAULT] COLLATE [=] collation_name      //指定校验类型

         blob.png

        例：

        blob.png        

      （2）、修改数据库        

        ALTER {DATABASE | SCHEMA} [db_name]  //修改数据库属性

            alter_specification …

        ALTER {DATABASE | SCHEMA} db_name

            UPGRADE DATA DIRECTORY NAME   //修改数据库datadir名称        

        alter_specification:

            [DEFAULT] CHARACTER SET [=] charset_name

          | [DEFAULT] COLLATE [=] collation_name

       （3）、删除数据库

         DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

    4、表

       （1）、创建表        

        CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

            (create_definition,…)

            [table_options]

            [partition_options]        

        Or:        

        CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

            [(create_definition,…)]

            [table_options]

            [partition_options]

            select_statement        

        Or:

        CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

            { LIKE old_tbl_name | (LIKE old_tbl_name) }        

          定义字段约束

          create_definition:              

            col_name column_definition

          | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)

              [index_option] …

          | {INDEX|KEY} [index_name] [index_type] (index_col_name,…)

              [index_option] …

          | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]

              [index_name] [index_type] (index_col_name,…)

              [index_option] …

          | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,…)

              [index_option] …

          | [CONSTRAINT [symbol]] FOREIGN KEY

              [index_name] (index_col_name,…) reference_definition

          | CHECK (expr)  

         定义字段数据类型      

        column_definition:  

            data_type [NOT NULL | NULL] [DEFAULT default_value]

              [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

              [COMMENT 'string']

              [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]

              [STORAGE {DISK|MEMORY|DEFAULT}]

              [reference_definition]

         数据类型

        data_type:

            BIT[(length)]

          | TINYINT[(length)] [UNSIGNED] [ZEROFILL]

          | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]

          | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]

          | INT[(length)] [UNSIGNED] [ZEROFILL]

          | INTEGER[(length)] [UNSIGNED] [ZEROFILL]

          | BIGINT[(length)] [UNSIGNED] [ZEROFILL]

          | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]

          | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]

          | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]

          | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]

          | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]

          | DATE

          | TIME

          | TIMESTAMP

          | DATETIME

          | YEAR

          | CHAR[(length)]

              [CHARACTER SET charset_name] [COLLATE collation_name]

          | VARCHAR(length)

              [CHARACTER SET charset_name] [COLLATE collation_name]

          | BINARY[(length)]

          | VARBINARY(length)

          | TINYBLOB

          | BLOB

          | MEDIUMBLOB

          | LONGBLOB

          | TINYTEXT [BINARY]

              [CHARACTER SET charset_name] [COLLATE collation_name]

          | TEXT [BINARY]

              [CHARACTER SET charset_name] [COLLATE collation_name]

          | MEDIUMTEXT [BINARY]

              [CHARACTER SET charset_name] [COLLATE collation_name]

          | LONGTEXT [BINARY]

              [CHARACTER SET charset_name] [COLLATE collation_name]

          | ENUM(value1,value2,value3,…)

              [CHARACTER SET charset_name] [COLLATE collation_name]

          | SET(value1,value2,value3,…)

              [CHARACTER SET charset_name] [COLLATE collation_name]

          | spatial_type

        定义表属性

        table_options:

            table_option [[,] table_option] …

        表属性

        table_option:

            ENGINE [=] engine_name

          | AUTO_INCREMENT [=] value

          | AVG_ROW_LENGTH [=] value

          | [DEFAULT] CHARACTER SET [=] charset_name

          | CHECKSUM [=] {0 | 1}

          | [DEFAULT] COLLATE [=] collation_name

          | COMMENT [=] 'string'

          | CONNECTION [=] 'connect_string'

          | DATA DIRECTORY [=] 'absolute path to directory'

          | DELAY_KEY_WRITE [=] {0 | 1}

          | INDEX DIRECTORY [=] 'absolute path to directory'

          | INSERT_METHOD [=] { NO | FIRST | LAST }

          | KEY_BLOCK_SIZE [=] value

          | MAX_ROWS [=] value

          | MIN_ROWS [=] value

          | PACK_KEYS [=] {0 | 1 | DEFAULT}

          | PASSWORD [=] 'string'

          | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

          | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

          | UNION [=] (tbl_name[,tbl_name]…)、

    （2）、删除表        

        DROP [TEMPORARY] TABLE [IF EXISTS]

            tbl_name [, tbl_name] …

    （3）、修改表

        ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name

            [alter_specification [, alter_specification] …]

            [partition_options]
        修改方式

        alter_specification:

            table_options

          | ADD [COLUMN] col_name column_definition

                [FIRST | AFTER col_name ]

          | ADD [COLUMN] (col_name column_definition,…)

          | ADD {INDEX|KEY} [index_name]

                [index_type] (index_col_name,…) [index_option] …

          | ADD [CONSTRAINT [symbol]] PRIMARY KEY

                [index_type] (index_col_name,…) [index_option] …

          | ADD [CONSTRAINT [symbol]]

                UNIQUE [INDEX|KEY] [index_name]

                [index_type] (index_col_name,…) [index_option] …

          | ADD FULLTEXT [INDEX|KEY] [index_name]

                (index_col_name,…) [index_option] …

          | ADD SPATIAL [INDEX|KEY] [index_name]

                (index_col_name,…) [index_option] …

          | ADD [CONSTRAINT [symbol]]

                FOREIGN KEY [index_name] (index_col_name,…)

                reference_definition

          | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

          | CHANGE [COLUMN] old_col_name new_col_name column_definition   //改变字段名称和字段定义

                [FIRST|AFTER col_name]

          | MODIFY [COLUMN] col_name column_definition              //仅修改字段定义

                [FIRST | AFTER col_name]        

          | DROP [COLUMN] col_name

          | DROP PRIMARY KEY

          | DROP {INDEX|KEY} index_name

          | DROP FOREIGN KEY fk_symbol

          | MAX_ROWS = rows

          | DISABLE KEYS

          | ENABLE KEYS

          | RENAME [TO|AS] new_tbl_name   //重命名表

          | ORDER BY col_name [, col_name] …

          | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

          | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]

          | DISCARD TABLESPACE         //表空间删除，前提是innodb_file_per_table开启

          | IMPORT TABLESPACE        //导入表空间，前提是innodb_file_per_table开启

          | FORCE

          | ADD PARTITION (partition_definition)

          | DROP PARTITION partition_names

          | TRUNCATE PARTITION {partition_names | ALL}

          | COALESCE PARTITION number

          | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]

          | ANALYZE PARTITION {partition_names | ALL}

          | CHECK PARTITION {partition_names | ALL}

          | OPTIMIZE PARTITION {partition_names | ALL}

          | REBUILD PARTITION {partition_names | ALL}

          | REPAIR PARTITION {partition_names | ALL}

          | PARTITION BY partitioning_expression

          | REMOVE PARTITIONING

    5、索引

      （1）、索引的创建法则：基于搜索键来创建索引，一般是SELECT语句中的WHERE子句中的查询条件中的字段。

      （2）、索引使用策略

        使用独立的列，索引不能是表达式的一部分；

        使用前缀索引来提高搜索效率；

        多列索引中，如使用and则使用组合索引，如果使用or，使用简单索引；

        使用索引查询时，索引选择性高的写在索引最前面；

        聚簇索引固然会提升查询速度，但同样会提高更新代价；

        必要情况下，尽量使用覆盖索引；

        遵循避免使用冗余索引，避免使用索引的原则。

      （3）、创建索引：

        CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

            [index_type]

            ON tbl_name (index_col_name,…)

            [index_option] …

        定义索引字段

        index_col_name:

            col_name [(length)] [ASC | DESC]

        定义索引类型

        index_type:

            USING {BTREE | HASH}

        定义索引属性

        index_option:

            KEY_BLOCK_SIZE [=] value

          | index_type

          | WITH PARSER parser_name

          | COMMENT 'string'

        值得注意的是，CREATE INDEX不能添加、修改索引，需借助于ALTER TABLE.

      （4）、显示特定表上的索引

        SHOW {INDEX | INDEXES | KEYS}

            {FROM | IN} tbl_name

            [{FROM | IN} db_name]

            [WHERE expr]

      （5）、删除索引

        DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name

    6、DML语句操作

      （1）、插入记录   

        INSERT [INTO] tbl_name [(col_name,…)] {VALUES | VALUE} (…),…  //可以执行批量插入值

        INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, … 

        INSERT [INTO] tbl_name [(col_name,…)] SELECT …

      （2）、更新记录

        PDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] …

            [WHERE where_condition]

            [ORDER BY …]

            [LIMIT row_count]       

        ORDER BY col1[,…] {ASC|DESC} LIMIT #   //排序后取前#条记录

      （3）、删除记录

        UDELETE

        DELETE  FROM tbl_name

            [WHERE where_condition]

            [ORDER BY …]      //排序方式

            [LIMIT row_count]    //排序后显示前多少行记录

    7、SELECT查询      

       （1）、SELECT查询执行过程：FROM –> WHERE –> GROUP BY –> HAVING –> ORDER –> SELECT –> LIMIT；

       （2）、SELECT可以使用查询缓存来提升查询速度，查询缓存中的数据成对存在的键和值（key-value），其中键（key）是查询语句hash码，值（value）是查询所得结果。

        >SHOW GLOBAL VARIABLES LIKE '%query_cache%';   //使用此命令查询缓存变量

        +——————————+———-+

        | Variable_name                | Value    |

        +——————————+———-+

        | have_query_cache             | YES      |

        | query_cache_limit            | 1048576  

        | query_cache_min_res_unit     | 4096    

        | query_cache_size             | 16777216 |    //此值不为0说明查询缓存开启

        | query_cache_strip_comments   | OFF      |

        | query_cache_type             | ON       |  //缓存服务类型选择

        | query_cache_wlock_invalidate | OFF      |

        +——————————+———-+

        7 rows in set (0.00 sec)

        其中query_cache_type变量可取值为

        ON：表是mysqld自行决定需缓存对象     

        DEMAND：按需缓存，只有明确制定需缓存的对象才被缓存

        OFF：关闭缓存，取此值并且query_cache_size为零时，mysql服务器缓存功能关闭。        

        MariaDB [(none)]> SELECT @@GLOBAL.query_cache_type;

        +—————————+

        | @@GLOBAL.query_cache_type |

        +—————————+        

        | ON                        |       

        +—————————+

        1 row in set (0.00 sec)

       （3）、SELECT语法:        

        SELECT [ALL | DISTINCT | DISTINCTROW ]

        value_to_display    //查询值

        FROM table_name     //查询对象

        WHERE expression    //查询条件表达式

        GROUP BY how_to_group //分组依据字段

        HAVING expression    //分组后查询条件表达式    

        ORDER BY how_to_sort  //排序依据

        LIMIT row_count;    //限定显示行数

      （4）、mysql查询子句

        如下示例，均使用hellodb数据库中的students表

        blob.png

        WHERE(条件查询）、HAVING(筛选）、GROUP BY（分组）、ORDER BY（排序）、LIMIT(限制结果数）

         1、WHERE常用运算符

        比较运算符：>,<,=,!=,>=,<=

        IN(v1,v2…)

        BETWEEN v1 AND v2 在v1和v2之间，包含v1和v2

        SELECT Name,Age FROM students WHERE Age>=20 AND Age<=30 OR Age>=50 AND Age<=60;

        blob.png

        SELECT Name,Age FROM students WHERE Age  NOT BETWEEN 30 AND 100;

        blob.png

        逻辑运算符：

          NOT(!)逻辑非

          OR(||)逻辑或

          AND(&&)逻辑与

        模糊查询

          LIKE 像

          通配符：%任意字符；_任意单个字符

         SELECT Name,Age FROM students WHERE Name LIKE '%shi%';

        blob.png

        2、GROUP BY 分组

        一般情况下，group by与统计函数（聚合函数）一起使用

        mysql中的5种统计函数：

           （1）、max求最大值

         SELECT max(Age) FROM students;

         blob.png

           （2）、min求最小值

           （3）、求总数和

         SELECT sum(Age) FROM students;

         blob.png

           （4）、avg求平均值

           （5）、count求总行数、

        3、having和where

          having和where类似，可以筛选数据，两者后面跟的表达式通用。

          where针对表中的列发挥作用，查询数据；having针对查询结果的列发挥作用，查询数据

          blob.png 

        4、order by

          （1） order by price  //默认升序排列

          （2）order by price desc //降序排列

          （3）order by price asc //升序排列，与默认一样

          （4）order by rand() //随机排列，效率不高        

        5、limit

          limit [offset,] N

          offset 偏移量，可选，不写则相当于limit 0,N

          N     取出条目 

        注意：特殊表达式        

           IS NULL; NOT NULL   //判断值是否为空

           LIKE：可使用通配符 % _，其中%表示任意多个字符，_表示任意单个字符。

           RLIKE或REGEXP：可以使用正则表达式的模式

      （5）、mysql子查询

        1、where型子查询，把内层查询结果当作外层查询的比较条件

            例如，取出年龄最大的人员

           blob.png

        2、from型子查询，把内存查询得到的结果供外层再次查询

          例如，从年龄大于30的人员中选出年龄大于等于50的人员

           blob.png    

        3、exists型子查询，把外层的查询结果拿到内层，看内层的查询是否成立。

          例如，创建表type,object要求如下

          blob.png

            利用exists实现，object表中有记录的对应的tid，则将表type中有相同tid的行显示出来

           blob.png

          上面语句是，查询语句循环从表type中取出tid到exists子语句中看是否有相同的tid，有就显示该tid所在行，没有则不显示，本例中，tid为1，2，3，的显示，tid为4的记录则不显示。           

        5、联合查询，UNION的用法

          把两次或多次的查询结果合并起来，要求查询的列数一致，查询对应类型一致，可查询多张表；多次查询如果列名不一致，则取第一次查询列名为准，若不同的查询语句取出的不同的行中的列中的值完全相同，默认采取去重操作，可以使用UNION ALL 声明保留重复行。

           示例，创建表tb1,tb2，要求如下

         blob.png

           查询两张表并使用UNION

         blob.png

          可以看到id为b的重复项自动去重，声明UNION ALL 

           blob.png

          可以看到使用UNION ALL id为b的重复项不再去重         

          笛卡尔积连接：CROSS JOIN

          内连接：INNER JOIN，分为等值连接和不等值连接

          外连接：OUTERJOIN

              左外连接：LEFT OUTER JOIN

              右外联接：RIGHT OUTER JOIN

              自然连接：NATURAL JOIN，即等值连接。

        6、外连接：

            （1）、左外连接：以左表为准，去右表找数据，如果没有匹配的数据，则以NULL补空，输出结果，大于等于左表数。

          语法：SELECT n1,n2,n3 FROM tb1 LEFT JOIN tb2 ON tb1.n1=tb2.n2 注意，此处ON后面的表达式，不仅仅能使用=，还可以使用>,<等算术、逻辑运算符，连接完成后，可以当成一张新表来进行表操作。

           示例，两张表如下

         blob.png

           通过，左外连接显示对应StuID的同学每一门课程的得分

         blob.png

           下面把上面的结果当成新表，用表students与之再次左连接，查询出每个人员的名字、年龄、课程和得分。

         students表如下

         blob.png

         再次执行左连接查询

         > SELECT Name,Age,t.Course,t.Score FROM students LEFT JOIN (SELECT StuID,Score,courses.Course FROM scores LEFT JOIN courses ON scores.CourseID=courses.CourseID) AS t ON students.StuID=t.StuID;

         blob.png

         小结：所谓多表联合查询，无非是通过两张或多张表中有着某种关系的字段将参与查询的表联系起来（如字段间的等于、大于或其他关系），在通过此联系在组成的联合表中查询获得数据，使之呈现出来。

        （2）、右外连接：a表右外连接b表，相当于b表左外连接a表，一般使用左外连接取代右外连接。

         （3）、内连接：内连接，又叫等值连接，这种连接取相应左右连接的交集，NULL项和重复项将会被去除，该连接无需特殊连接关键字，因此又称自然连接。

          查询表students中的每个学生对应的每个科目的成绩

           > SELECT students.Name,t.Course,t.Score FROM students,(SELECT scores.StuID,scores.Score,courses.Course FROM scores,courses WHERE scores.CourseID=courses.CourseID) AS t WHERE students.StuID=t.StuID;

          blob.png

           

    


    

         

        

    

    

        

       

        

    

    

        

        

                

                    

        

        

        

        

        

        

        

        

        

        

    

                   

    

原创文章，作者：Silently，如若转载，请注明出处：http://www.178linux.com/7286

赞 (0)
SilentlySilently
1
上一篇 2015-08-24
下一篇 2015-08-26

相关推荐

  • RHCE系列之ntp服务器—-实践篇

    原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://nolinux.blog.51cto.com/4824967/1319520 上篇Linux实战部署系列之ntp服务器—-理论篇为大家介绍时间的相关概念和ntp的原理，本篇博文将带大家一起部署生产环境中实现简单…

    Linux干货 2016-08-15

  • CentOS7编译安装LAMP—php-fpm

    inux的环境是： [root@localhost ~]# lsb_release -a LSB Version:     :core-4.1-amd64:core-4.1-noarch Distributor ID: CentOS Description:     CentOS…

    Linux干货 2016-12-21

  • Linux运维之磁盘管理

    ※磁盘管理 计算机是由五大组成部件：输入设备，输出设备，存储器，运算器，控制器，其中输入设备我们称之为I/O设备，如键盘，扫描器，运算器和控制称之为cpu，存储器如，内存，硬盘，优盘。计算机其实只需要内存和cpu其实已经可以完成工作了，但是内存属于随机存储设备，断电之后就丢失数据，所以我们就需要通过其他的辅助存储设备来弥补内存的不足，这些辅存我们称之为外存。…

    Linux干货 2016-08-27

  • httpd——事务

    http协议： http/0.9 ，http/1.0 ，http/1.1 ，http/2.0 http协议：stateless          服务器无法持续追踪访问者来源          &nb…

    Linux干货 2016-10-12
  • lvs集群搭建 Linux干货

    lvs集群搭建

    Linux Cluster： Cluster：计算机集合，为解决某个特定问题组合起来形成的单个系统； Linux Cluster类型：LB：Load Balancing，负载均衡；HA：High Availiablity，高可用；A=MTBF/（MTBF+MTTR）(0,1)：90%, 95%, 99%, 99.5%,  99.9%, 99.99%…

    2017-05-09

  • Linux进程及作业管理

    process 运行中的程序的一个副本，是被载入内存的一个指令集合 进程ID（Process ID，PID）号码被用来标记各个进程 UID、GID、和SELinux语境决定对文件系统的存取和访问权限， 通常从执行进程的用户来继承 存在生命周期 task struct：Linux内核存储进程信息的数据结构格式 task list：…

    Linux干货 2016-09-11

评论列表（1条）

  • stanley
    stanley 2015-08-26 09:21

    输出的代码段可以格式化，格式化后效果会好很多

电话咨询
在线咨询