首页 > 数据库 > MySQL > Mysql数据库精髓
2014
07-11

Mysql数据库精髓

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创建表

  1. CREATE TABLE t_bookType(  
  2.     id int primary key auto_increment,  
  3.     bookTypeName varchar(20),  
  4.     bookTypeDesc varchar(200)  
  5. ); 

2.3主外键关联

  1. CREATE TABLE t_book(  
  2.     id int primary key auto_increment,  
  3.     bookName varchar(20),  
  4.     author varchar(10),  
  5.     price decimal(6,2),  
  6.     bookTypeId int,  
  7.     constraint `fk` foreign key (`bookTypeId`references `t_bookType`(`id`)
  8. );

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

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);