MYSQL基础知识总结(一)

一、初识MYSQL

1.1、启动\关闭MySQL服务：net start/stop MySQL

1.2、MYSQL常用命令：

     SELECT VERSION();//显示当前服务器版本

     SELECT NOW();//显示当前日期时间

     SELECT USER();//显示当前用户

     CREATE DATABASE dbName;//创建数据库

     DROP DATABASE dbName;//删除当前数据库

     ALLTER DATABASE dbName;//修改数据库名字

     USE dbName;//使用数据库

二、常用的数据类型

数据类型描述integer(size),smallint(size)
int(size),tinyint(size)容纳整数，
在括号内规定数字的最大位数decimal(size,d),numeric(size,d)容纳带有小数的数字
‘size’规定数字的最大位数，‘d’规定小数点的最大位数char(size)容纳固定长度的字符串(可容纳字母、数字及特殊符号)varchar(size)容纳可变长度的字符串(可容纳字母、数字及特殊的字符)
在括号中规定字符串的最大长度date(yyyymmdd)容纳日期三、数据表操作及约束

3.1、创建数据表

       CREATE TABLE <表名> (<列名><数据类型>[列级完整性约束条件]

                                                    [,<列名><数据类型>[列级完整性约束条件]

                                                  .........

                                                    [,<表级完整性约束条件>]);

3.2、修改基本表

        ALERT  TABLE  <表名>

                                [ RENAME[TO|AS]<新表名>]

                                 [ADD <新列名> <数据类型> [完整性约束][first|after]]

                                 [DROP <字段名>]

                                 [CHANGE <旧字段名><新字段名><数据类型>];

                                 [MODIFY <字段名><数据类型>];

         ADD: 用于向表中增加新列及新列相关的完整性约束条件，新的列增加成功后不带任何数据                  

         例如：ALTER TABLE Student ADD age VARCHAR(20) NOT NULL first;//放置在第一行

                   ALTER TABLE Student  ADD age VARCHAR(20) NOT NULL after name;//在name行下增加age行

                   ALTER TABLE Student ADD age VARCHAR(20) NOT NULL;//置于表的最后一行                           

                  ALTER TABLE Student ADD age VARCHAR(20) NOT NULL,ADD age1 VARCHAR(20) NOT NULL,…//(添加多列时，不能指定位置关系)

          DROP:用于删除指定的完整性约束条件

          MODIFY:用于修改原来的列定义，包括修改列名和数据类型，列数据类型修改后可能会丢失原有的数据

3.3、删除基本表

         DROP TABLE 表名;//删除整个表

         TRUNCATE TABLE 表名;//清空表中数据，但保留表的定义

3.4、基本的数据操作

1、INSERT

         首先，创建一个数据表：

         CREATE  TABLE user(

                       id smallint NOT NULL PRIMARY KEY AUTO_INCREMENT,

                       name  varchar(20) NOT NULL,

                       password varchar(20) NOT NULL,

                       age smallint  NOT NULL,

                       number smallint NOT NULL DEFAULT 15); 

        i、INSERT  表名  [ 字段名1，字段名2...] VALUES (值1，值2...)

                   1)插入时，如果不写字段名时，必须依次给每一个字段赋值，否则要写上字段名

                       例如：INSERT user VALUES (NULL,'lily','123456',20,18)或者INSERT user (name,password) VALUES('lucy', '123456')

                    2)INSERT为自动编号的字段赋值时，可以赋值为NULL或者DEFAULT

                        例如：INSERT user VALUES (NULL,'lily','1123456',20,18)或者INSERT user VALUES(DEFAULT,'lily','1123456',20,18)

                     3)如果字段设置为DEFAULT，可以不用赋值，直接写成DEFAULT即可

                         例如：INSERT user VALUES(NULL,'LILY','123456',20,DEFAULT)

       ii、INSERT 表名 SET age=’kcy’,number=10;

                      这种方式可以使用子查询,但只能单条插入

       iii、INSERT 表名 SELECT 列名 FROM 表名1  [WHERE条件]

2、UPDATE

      UPDATE  表名  SET 列名称=新值 WHERE 列名称=某值

                     1)省略where语句的话，所以的记录都被更新

                     2)更新多列时，逗号隔开即可

3、DELETE

      DELETE FROM 表名 WHERE 列名=值

                      注意：当我们删除一条记录后，在重新插入一条数据，id号为最大id号加1，而并非补充删除的id号

4、SELECT

       1)SELECT 列名 FROM 表名  [WHERE 条件]                    (AND和OR可在WHERE子语句中把两个或多个条件结合起来)

       2)SELECT * FROM 表名  [WHERE 条件] 

       3)SELECT 列名 AS 新列名 FROM 表名   [WHERE  条件]           (使用AS来指定别名新列名为输出显示的列名)

       4)SELECT DISTINCT 列名 FROM 表名                                         (返回唯一不同的值，不会包括重复的列名)

       5)group by函数。group by语句用于结合合计函数，根据一个或多个列对结果集进行分组

          例如：SELECT Customer,SUM(orderPrice) FROM Orders GROUP BY Customer, 查找每个客户的消费的总金额，并根据客户进行组合

       6)Having 函数，与group by结合，列举满足having 函数的情况。出现having函数是因为where语句无法与合计函数一块使用

          例如：SELECT Customer,SUM(orderPrice) FROM Orders GROUP BY Customer HAVING SUM(orderPrice）<2000,  查找订单金额少于2000的客户，并根据客户进行组合

       7)order by语句，用于根据指定的列对结果集进行排序，默认是升序排序[ASC];如果希望降序排序，则使用[DESC]关键字

           例如：SELECT Company,OrderNumber FROM Orders ORDER BY Company,依字母顺序显示

                      SELECT Company,OrderNumber FROM Orders ORDER BY Company,以逆字母顺序显示

                      SELECT Company,OrderNumber FROM Orders ORDER BY Company,OrderNumber,以字母顺序显示,若出现相同的公司名,则按照    OrderNumber的顺序排序

                      SELECT Company,OrderNumber FROM Orders ORDER BY Company DESC,OrderNumber ASC,以逆字母顺序显示,若出现相同的公 司名,则按照OrderNumber的顺序排序

       8)limit语句限制查询结果

3.5、约束

        按功能划分：NOT NULL(非空约束)、PRIMARY KEY(主键约束)、UNIQUE KEY(唯一约束)、 DEFAULT(默认约束)、FOREIGN  KEY(外键约束)

        按数据列的数目划分：表级约束和列级约束。其中表级约束就是对多个数据列建立约束，列级约束是对一个数据列建立约束。                                                                          列级约束即可以在列定义是声明，也可以在列定义后声明，表级约束只能在列定义后声明。

        其中，默认约束和非空约束不存在表级约束，其他三种两种约束都存在。

         NOT NULL:约束强制列不接受NULL值，如果不向字段添加值，就无法插入新纪录或者更新记录；

         UNIQUE:约束唯一标识数据库表中的每条记录，为列提供唯一性保证。

                        创建：1）在定义表时定义：id int NOT NULL UNIQUE  2)ALTER TABLE 表名 ADD UNIQUE (列名)

                        删除：ALTER TABLE 表名 DROP INDEX  (列名)

         DEFAULT:约束用于向列中插入默认值，如果没有规定其他的值，那么会将默认值添加到所有的新纪录。

                         创建：1）在定义表时定义：id int NOT NULL DEFAULT  2)ALTER TABLE 表名 ALTER 列名 set DEFAULT 固定值

                         删除：ALTER TABLE 表名 ALTER 列名 DROP DEFAULT

         PRIMARY KEY:约束唯一标识数据库表中的每一条记录，主键必须包含唯一的值，主键列不能包含NULL值，每个表都应该有一个主键，并且每一个表只能有一个主键

                         创建：1）在定义表时定义：id int NOT NULL PRIMARY KEY 2)ALTER TABLE 表名 ADD PRIMARY KEY (列名) 

                         删除：ALTER TABLE 表名  DROP PRIMARY KEY

        FOREIGN KEY:指向另外一个表的主键，约束永固预防破坏表之间连接的动作，防止非法数据插入外键列

                        创建:1)在定义表时定义 2）ALTER TABLE 表名 ADD FOREIGN KEY (列名) REFERENCES 表名1 (列名)

                        删除：ALTER TABLE DROP FOREIGN KEY (列名)

        外键约束的参照操作：

             CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行    

                              ALTER TABLE 列名 ADD FOREIGN KEY (列名) REFERENCES 表名1 (列名) ON DELETE CASCADE

            SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

            RESTRICT:拒绝对父表的删除或更新操作

            NO ACTION:在MYSQL中与RESTRICT等价.

四、子查询和连接

4.1、子查询是指在另一个查询语句中的SELECT子句，必须始终出现在圆括号内。子查询可以包含多个关键字或条件，例如

         DISTINCT,GROUP BY,ORDER BY,LIMIT以及函数等，子查询的外层查询可以是SELECT,INSERT,UPDATA,SET或者DO。

         例如：SELECT * FROM 表1 WHERE column1=(SELECT column1 FROM 表2 )

                    SELECT * FROM 表1 WHERE (col1,col2) = (SELECT col3, col4 FROM 表2 WHERE id = 10);

        1、由比较运算符引发的子查询

              使用比较运算符的子查询：=、>、<、>=、<=、<>、!=、<=>,其返回结果为1个

              举例：创建一个数据表

              CREATE TABLE IF NOT EXISTS tdb_goods(

                              goods_id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

                              goods_name  VARCHAR(150) NOT NULL,

                              goods_cate  VARCHAR(40)  NOT NULL,

                              brand_name  VARCHAR(40)  NOT NULL,

                              goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,

                              is_show     BOOLEAN NOT NULL DEFAULT 1,

                              is_saleoff  BOOLEAN NOT NULL DEFAULT 0

                              );

            使用子查询来查询所有价格大于平均价格的商品，并且按价格降序排序：

            SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > (SELECTROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods) ORDER BY goods_price DESC;

        2、列子查询使用IN、ANY、SOME、ALL关键字

           列子查询是指子查询返回的结果集是N行一列而非一个结果，该结果通常来自对表的某个字段查询返回。其不能直接使用=、< 等这些比较标量 结果的操作符，所以要使用上述四个关键字。ANY与SOME是等价的。

           IN:在指定项内，同IN(项1，项2);

           ANY:与比较操作符联合使用，表示与子查询返回的任何值比较为TRUE，则返回TRUE;

           SOME:与ANY等价，较少使用;

           ALL:与比较操作符联合使用，表示与子查询返回的所有值比较为TRUE,则返回TRUE.

          

         举例:继续使用上述创建的tdb_goods表进行举例：

                 查询价格大于或等于“超极本”价格的商品，并且按价格降序排列

                 SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price=ANY(SELECT goods_price  FROM ydb_goods WHERE goods_cate='超极本') ORDER BY goods_price DESC;

                 由于‘=ANY’或‘=SOME’等价于'IN'，上述SQL语句可以表示为：

                 SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price IN(SELECT goods_price FROMydb_goods WHERE goods_cate='超极本') ORDER BY goods_price DESC;   

4.2、连接

         MYSQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。

         连接语句：A表 INNER JOIN|LEFT OUTER JOIN|RIGHT OUTER JOIN B表 ON 条件

         1、通常使用ON 关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤

          2、数据表可以使用tbl_name AS alias_name来赋予别民，如果在条件中出现两个表的某个字段的名字相同，则必须使用别名

          3、内连接INNER JOIN ==CROSS JOIN==JOIN ,显示左表和右表的公共部分，也就是符合连接条件的记录

             左外连接LEFT OUTER JOIN,显示左表的全部记录以及右表符合条件的记录。也就是说返回左表中的所有行，如果左表的某条记录符合条件，但是右表不存在符合连接条件的记录，则在右表中生成一个所有列为空的额外的行。

             右外连接RIGHT OUTER JOIN,显示右表的全部记录以及左表符合条件的记录。也就是说返回右表的所有行，如果右表的某条记录符合条件， 但是左表不存在符合连接条件的记录，则在左表中生成一个所有列为空的额外的行。

             全连接FULL JOIN,完整显示坐标和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。

             例如:

          .......................................................................................................
          a表     id   name     b表     id   job   parent_id   
                     1   张3                   1     23     1   
                     2   李四                  2     34     2   
                     3   王武                  3     34     4       
                     a.id同parent_id   存在关系  
          .......................................................................................................

           内连接：  select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id       

           结果是 ：   

                   1   张3                   1     23     1   

                   2   李四                  2     34     2   

           左连接   select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id       

           结果是     

                   1   张3                   1     23     1   

                   2   李四                  2     34     2    

                   3   王武                  null     

           右连接  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id       

           结果是      

                  1   张3                   1     23     1   

                  2   李四                  2     34     2    

                   null                       3     34     4   

          完全连接   select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id   

            结果是     

                  1   张3                  1     23     1   

                  2   李四                 2     34     2   

                   null               　　 3     34     4   

                  3   王武                 null

          举例：使用连接进行多表更新

                     1、创建数据表tdb_goods_cates：

                        CREATE TABLE IF NOT EXISTS tdb_goods_cates(

                                   cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

                                   cate_name VARCHAR(40) NOT NULL

                                    );

                     2、将分组结果写入到tdb_goods_cates数据表：

                           INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

                     3、通过tdb_goods_cates数据表来更新tdb_goods表：

                           UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id ;

                为了简化SQL语句，我们将创建数据表的过程以SELECT的过程结合：

                      通过CREATE...SELECT来创建数据表并且同时写入记录

                       1、CREATE TABLE tdb_goods_brands (

                                     brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

                                     brand_name VARCHAR(40) NOT NULL

                                     ) SELECT brand_name FROM tdb_goods GROUP BY brand_name;

                         2、通过tdb_goods_brands数据表来更新tdb_goods表：

                               UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS s ON g.brand_name=s.brand_name SET  g.brand_name=s.brand_id

                更新tdb_goods的brands_name以及goods_cate后，通过查看tdb_goods表，我们发现它们的类型并没有从VARCHAR类型变成SMALLINT,所以我们要自己修改，防止后面的操作出现错误。

                         ALTER TABLE tdb_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL，

                         CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL

              通过这一步，我们修改了两者的数据类型，同时将其名称变成了cate_id和brand_id

参考链接：1、SQL的四种连接参考   http://www.cnblogs.com/afirefly/archive/2010/10/08/1845906.html

                   2、http://www.w3school.com.cn/sql/index.asp

                   3、http://www.imooc.com/learn/122