Mysql数据库精深

1.Mysql基础常识

1.1无符号与有符号

       unsigned与signed代表无符号和有符号两种状态。当定义一个字段类型的时候，默认状态下是有符号的，比如一个有符号类型变量他的长度范围可以是：-128 ~ 127；但是你定义的时候可以特别指出他是一个无符号类型的变量，声明他为unsigned，那么他的表示范围就就变成了：0 ~ 255。

1.2字段类型

char：代表固定长度字符串，如name字段定义成char(10)，但实际只存了6个，那也占10个字节。好处是查询会快，缺点是空间占得大。

varchar：代表可变长度字符串，如name字段定义成 varchar(10) ,但实际存了6个字节，那么将会占据6字节而不是10字节。好处是占空间小，但查询效率没有char好。varchar适合存储备注信息、地址这类字段。

text：用于存储大文本文件，如新闻稿件、文章之类的内容。

       DECIMAL(M,D)：decimal，小数的；M表示数据的总长度，D表示小数位；例如：decimal(5,2) 123.45；存入数据的时候按照四舍五入计算。

       关于二进制类型数据，一般用来存储图片信息、音频等。例如一些需要加密保存的图片。

1.3数据库的创建

       可以使用命令行创建，但一般都使用图形界面来创建数据库，这样可以加快开发效率，而且更加直观。方式截图如下，很简单。

2.数据库表基本操作

2.1约束条件

       PRIMARY KEY：primary key，一个表的主键，唯一标识一条对应的记录；

       FOREIGN KEY：foreign key，外键，与另一个表的主键进行关联；

       NOT NULL：非空约束，标识该属性不能为空；

       UNIQUE：unique，标识该属性是唯一的；

       AUTO_INCREMENT：auto_increment，标识该属性自动增加；

       DEFAULT：default，为该属性设置默认值。

2.2创建表

2.3主外键关联

在架构设计器中查看主外键关联情况，把关联的两个表拖进去就可以了：

2.4工具创建主外键关联

       首先创建出相应的两个表，在从表中创建一个对应的外键，等待去关联主表的主键；

完成后将两个表拖入架构设计器：

在这里t_book表作为从表，他的外键是bookTypeId；t_booktype作为主表，其ID为主键，下一步用鼠标左键将bookTypeId拖入到t_booktype表的主键上。

点击对话框的【创建】按钮，则关联创建如下：

在查看这个关联的时候，与手动创建唯一不同的是他的关联名字：`FK_t_book`

2.5删除表

DROP TABLE t_book;

刷新后，t_book表被删除。

3.查询数据 – 单表查询

3.1查询所有字段

SELECT * FROM t_student;  

3.2查询指定字段

SELECT id,stuName,age FROM t_student

3.3where条件查询

where条件查询后面跟随一个条件表达式。

SELECT 字段1，字段2，字段3… FROM 表名 WHERE 条件表达式；

如下两个例子所示：

SELECT * FROM t_student WHERE gradeName = '一年级' ;

SELECT * FROM t_student WHERE age>23;

3.4in条件关键字查询

       in关键字是条件表达式的一种，后面跟随一个条件范围集合，基本语法如下：

SELECT 字段1，字段2… FROM 表名 WHERE 字段 [NOT]IN(元素1，元素2 …)；

如下两个例子所示：

SELECT * FROM t_student WHERE age IN (21,23); 

SELECT * FROM t_student WHERE age NOT IN (21,23); 

3.5BETWEEN AND 范围查询

       between and 将查询结果控制在一个范围，比如查询年龄在22 ~ 24岁之间的所有人信息。他的基本语法格式如下：

SELECT 字段1，字段2，字段3… FROM 表名 WHERE 字段 [NOT]BETWEEN 取值1 AND 取值2；如下两个例子所示：

SELECT * FROM t_student WHERE age  BETWEEN 22 AND 24;  

SELECT * FROM t_student WHERE age NOT BETWEEN 22 AND 24;  

3.6LIKE关键字与模糊查询

       like关键字用于模糊查询，比如查询出姓名中所有带“张三”两个字的所有记录信息。他的基本语法如下所示：

SELECT 字段 1，字段2，字段3…FROM 表名 WHERE 字段 [NOT]LIKE‘字符串’；“%”代表任意字符；“_” 代表单个字符；如：'%张三%';、'张三__';、'张三%';、'张三'; 等。如下几个例子所示：

SELECT * FROM t_student WHERE stuName LIKE '张三%'; 

SELECT * FROM t_student WHERE stuName LIKE '%张三%'; 

SELECT * FROM t_student WHERE stuName LIKE '%张三'; 

SELECT * FROM t_student WHERE stuName LIKE '张三_ _'; 

SELECT * FROM t_student WHERE stuName LIKE '张三_';

SELECT * FROM t_student WHERE stuName LIKE '_张三';

SELECT * FROM t_student WHERE stuName LIKE '张三'; 

SELECT * FROM t_student WHERE stuName NOT LIKE '%张三%'; 

查询出所有stuName字段中不包含张三的信息会用到 NOT LIKE。

3.7空值查询 IS NULL

       用于查询某一个字段是否为空的情况。其语法格式如下：

SELECT 字段1，字段2，字段3… FROM 表名 WHERE 字段 IS [NOT] NULL；示例如下：

SELECT * FROM t_student WHERE sex IS NOT NULL;  

3.8多条件查询AND / OR

       即多条件表达式查询。其语法格式如下：

SELECT 字段 1，字段2… FROM 表名 WHERE 条件表达式1 AND 条件表达式2[…AND 条件表达式n] 。或“AND”换成“OR”。示例如下：

SELECT * FROM t_student WHERE gradeName='一年级' AND age=23

SELECT * FROM t_student WHERE gradeName='一年级' OR age=23

3.9DISTINCT 去重复查询

       distinct 释义：不同的|清楚的|明显的；用于去除重复信息。其语法格式如下：

SELECT DISTINCT 字段名 FROM 表名；示例如下。

当我们不使用distinct关键字的时候，会出现重复字段：

SELECT gradeName  FROM t_student

当我们 使用distinct关键字的时候，可以去除重复字段：

SELECT DISTINCT gradeName  FROM t_student

3.10GROUP BY 分组查询

       group by 分组，Select一个字段可以跟一个函数，但是不能再加入其他字段，因为Group By分组的依据是那个被Select的字段，group by可以单独使用。语法格式如下：

GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]

    1，单独使用(毫无意义)；

    2，与GROUP_CONCAT()函数一起使用；concat，n，合并多个字符串或数组。

    3，与聚合函数一起使用；如COUNT()函数

    4，与HAVING 一起使用(限制输出的结果)； having；

    5，与WITH ROLLUP一起使用(最后加入一个总和行)；with rollup：与汇总。

使用举例如下：

SELECT * FROM t_student GROUP BY gradeName; 

可以看到，数据丢失了,是因为 * 代表多条件，导致。即只能有一个字段被查询。

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student 

GROUP BY gradeName; 

中间以逗号分隔开，在Java或C#中用split()函数就可以很容易的取出这些数据。针对聚合函数的结合使用，举例如下：查询每个年级的学生总数。

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;

利用HAVING筛选查询结果。

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY 

gradeName HAVING COUNT(stuName)>3;

WITH ROLLUP动态的在最后一行加入一个总和的计算或字符叠加，不是很常用。

SELECT gradeName,COUNT(stuName) FROM t_student 

GROUP BY gradeName WITH ROLLUP; 

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student 

GROUP BY gradeName WITH ROLLUP; 

3.11LIMIT 分页查询

       limit 限制，界限。分页查询，其语法格式如下：

SELECT 字段 1，字段2… FROM 表名 LIMIT 初始位置，记录数；

使用举例如下：

SELECT * FROM t_student LIMIT 0,5; 

SELECT * FROM t_student LIMIT 5,5; 

SELECT * FROM t_student LIMIT 10,5; 

3.12查询结果排序 ASC / DESC

       对查询结果按照升序或降序进行排序。其中ASC代表升序排列，默认情况下是按照升序排列的；DESC代表降序排列，需要指明。其语法格式如下：

SELECT 字段1，字段2… FROM 表名 ORDER BY 属性名 [ASC|DESC]

使用举例如下：

SELECT * FROM t_student ORDER BY age ASC; 

SELECT * FROM t_student ORDER BY age DESC; 

4.聚合函数查询

新建一个t_grade表，表数据如下：

4.1COUNT() 函数

COUNT()函数用来统计记录的条数；与GOUPE BY关键字一起使用。示例如下：

SELECT COUNT(*) FROM t_grade;

SELECT COUNT(*) AS total FROM t_grade; 为他取一个名字：total。

       AS total是为这个数据列取一个名字。

SELECT stuName FROM t_grade

       如果不使用GROUP BY，会出现查询错误：

SELECT stuName,COUNT(*) AS total FROM t_grade ;

SELECT stuName,COUNT(*) AS total FROM t_grade GROUP BY stuName; 

4.2SUN() 函数

SUM()函数是求和函数；与GOUPE BY关键字一起使用。如下所示：

SELECT stuName,SUM(score) FROM t_grade WHERE stuName="张三"; 

SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName; 

4.3AVG() 函数

AVG()函数是求平均值的函数；与GOUPE BY关键字一起使用。如下所示：

SELECT stuName,AVG(score) AS avgsssFROM t_grade 

WHERE stuName="张三";  

SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName; 

4.4MAX()函数 和 MIN()函数

       MAX()函数是求最大值的函数；MIN()函数是求最小值的函数。与GOUPE BY关键字一起使用。示例如下：

SELECT stuName, MAX(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName; 

5.连接查询

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据；内连接查询用的比较多，但外连接也会用，只是并不是非常多。创建示例数据表2个，如下：

t_book 和 t_booktype

5.1内连接查询 – 广义笛卡尔积

广义迪卡儿积没有限定语句，结果会产生N*M条记录。示例如下：

SELECT * FROM t_book , t_bookType;  

5.2内连接查询 – 条件限制

SELECT tb.id, tb.bookName,tb.price,tb.author,tby.bookTypeName FROM 

t_book tb, t_bookType tby WHERE tb.bookTypeId=tby.id;   

5.3外连接查询

外连接可以查出某一张表的所有信息； 语法格式如下：

SELECT 属性名列表 FROM 表名 1LEFT|RIGHT JOIN 表名2 ON 表名 1.属性名1=表名2.属性名2；

5.4外连接查询-左连接查询

可以查询出“表名1”的所有记录，而“表名2”中，只能查询出匹配的记录；

SELECT * FROM t_book tb LEFT JOIN t_bookType tby ON tb.bookTypeId=tby.id; 

从结果可以看出，t_bookType表中不符合查询条件的记录会被NULL所代替。

5.5外连接查询-右连接查询

可以查询出“表名2”的所有记录，而“表名 1”中，只能查询出匹配的记录；

SELECT * FROM t_book tb RIGHT JOIN t_bookType tby 

ON tb.bookTypeId=tby.id;  

5.6多条件查询 AND

       使用AND作为连接条件。示例如下：

SELECT tb.id, tb.bookName,tb.price,tb.author,tby.bookTypeName FROM 

t_book tb, t_bookType tby WHERE tb.bookTypeId=tby.id AND tb.price>70;   

6.子查询

创建示例数据表3个，如下：

t_book 、 t_booktype和t_pricelevel

6.1子查询 – 关键字In

一个查询语句的条件可能落在另一个SELECT语句的查询结果中。表示一个集合数据。

SELECT * FROM t_book tb

       WHERE tb.booktypeId IN (SELECT id FROM t_booktype);

这个查询语句的意思是：查询t_book表中的所有字段，其限定条件是t_book表中的booktypeId字段需要在 IN这个范围内；而IN的范围是由一个查询条件给出的集合数据。如果我们单独执行这个句话：SELECT id FROM t_booktype；那么会得到如下结果：

也就是说IN的范围是{1,2,3}，也就是说这个查询语句还可以表述成如下：

SELECT * FROM t_book tb WHERE tb.booktypeId IN (1，2，3);

ELECT * FROM t_book tb WHERE tb.booktypeId NOT IN (1，2，3);

6.2子查询 – 比较运算符

子查询可以使用比较运算符。使用比较运算符进行子查询操作，一般其子语句的结果会是一个查询条件，你不要放一个集合数据进去，这样的逻辑上就有问题，自然也会报错。示例如下所示：查询t_book中的所有信息，限定条件是price >= 80。

SELECT * FROM t_book WHERE price>= (

SELECT price FROM t_pricelevel WHERE priceLevel=1);

6.3子查询 – 关键字Exists

假如子查询查询到记录，则进行外层查询，否则，不执行外层查询；

SELECT * FROM t_book WHERE EXISTS (SELECT * FROM 

t_booktype WHERE id <3);

将查询条件改变，子查询中没有结果，则不会执行外层查询：

SELECT * FROM t_book WHERE EXISTS (SELECT * FROM 

t_booktype WHERE id >3);

6.4子查询 – 关键字Any

ANY关键字表示满足其中任一条件；

SELECT * FROM t_book

SELECT * FROM t_book WHERE price>= ANY (

SELECT price FROM t_pricelevel); 

注意！这里不能等同于：SELECT * FROM t_book WHERE price>= ANY (40,60,80);

这样的语法是错误的！

6.5子查询 – All关键字

ALL关键字表示满足所有条件；

SELECT price FROM t_pricelevel;

SELECT * FROM t_book;

当使用ALL关键字时，表示需要满足price != 40/60/80三个值。

SELECT * FROM t_book WHERE price != ALL (

SELECT price FROM t_pricelevel);