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创建表
- CREATE TABLE t_bookType(
- id int primary key auto_increment,
- bookTypeName varchar(20),
- bookTypeDesc varchar(200)
- );
2.3主外键关联
- CREATE TABLE t_book(
- id int primary key auto_increment,
- bookName varchar(20),
- author varchar(10),
- price decimal(6,2),
- bookTypeId int,
- constraint `fk` foreign key (`bookTypeId`) references `t_bookType`(`id`)
- );
在架构设计器中查看主外键关联情况,把关联的两个表拖进去就可以了:
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);