MySQL_Step1

发表于   |   分类于   |     |   阅读次数

hardwork
熟练掌握使用MySQL

前面的话

本人使用的MySQL 5.1版本，图形化界面用的SQLyog，本文主要介绍有如下内容：

  • MySQL数据类型：整数类型、浮点数类型和定点数类型；日期和时间类型；字符串类型；二进制类型；
  • 数据库基本操作
  • 数据库表的基本操作
  • 数据库表的基本操作（SQL）

MySQL数据类型

一、整数类型、浮点数类型和定点数类型

1). 整数类型

整数类型 字节数 unsigned范围 signed范围（默认）
tinyint 1 0~2^8 -2^7~2^7-1
smallint 2 0~2^16 -2^15~2^15-1
mediumint 3 0~2^24 -2^23~2^23-1
int 4 0~2^32 -2^31~2^31-1
bigint 8 0~2^64 -2^63~2^63-1

2).浮点数类型和定点数类型

类型 字节数 unsigned范围 signed范围（默认）
float 4 1.1E-38~3.4E+38 -3.4E+38~3.4E+38
double 8 2.2E-308~1.7E+308 -1.7E+308~1.7E+308
decimal(m,d) m+2 同double 同double

decimal(m,d)：m表示数据的总长度（不包括小数点），d表示小数位
例如：decimal(5.2) 123.45
存入数据的时候，按四舍五入计算

二、日期与时间类型

类型 字节数 取值范围 零值
year 1 1910~2155 0000
date 4 1000-01-01~9999-12-31 0000-00-00
time 3 -838:59:59~838:59:59 00:00:00
datetime 8 1000-01-01 00:00:00
~9999-12-31 23:59:59		 0000-00-00 00:00:00
timestamp 4 19700101080001
~20380119111407		 0000-00-00 000000

三、字符串类型

MySQL字符串类型

四、二进制类型

MySQL二进制类型

数据库基本操作

一、数据库概念

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库

二、显示所有数据库

show databases;

三、创建数据库

create database 数据库名;

四、删除数据库

drop database 数据库名；

数据库表基本操作

一、创建表

1）表的概念

表是数据库存储数据的基本单位，一张表包含若干条字段或记录。

2）创建表的语法

create table 表名（
    属性名 数据类型 [完整性约束条件],
    属性名 数据类型 [完整性约束条件],
    ...
    属性名 数据类型 [完整性约束条件]
);

3）约束条件

MySQL表约束条件

  • 创建图书类别表：
create table t_bookType(
    id int primary key auto_increment,
    bookTypeName varchar(20),
    bookTypeDesc varchar(200)
);
  • 创建图书表：
create table t_book(
    id int primary key auto_increment,
    bookName varchar(20),
    author varchar(200),
    price decimal(6,2),
    bookTypeId int,
    constraint `fk` foreign key (`bookTypeId`) references `t_bookType`(id)
);

二、查看表结构

1） 查看基本表结构

describe 表名；
或者：desc 表名

2） 查看表详细结构

show create table 表名

三、修改表

  • 修改表名
alter table 旧表名 rename 新表名
  • 修改字段
alter table 表名 change 旧属性名 新属性名 新数据类型
  • 增加字段
alter table 表名 add 属性1 数据类型 [完整性约束条件] [first|after 属性名2]
first； 指添加的字段在第一个位置
after 属性名2； 指添加的字段在已有属性名2之后
  • 删除字段
alter table 表名 drop 属性名

四、删除表

drop table 表名

查询表数据

单表查询

1） 查询所有字段

select * from 表名;

2） 查询指定字段

select 字段1,字段2,... from 表名;

3） where条件查询

select 字段1,字段2,... from 表名 where 条件表达式;

4） 带in关键字查询

select 字段1,字段2,... from 表名 where 字段 [not] in (元素1， 元素2， ...);

5） 带between and的范围查询

select 字段1,字段2,... from 表名 where 字段 [not] between 取值1 and 取值2;

6） 带like的模糊查询

select 字段1,字段2,... from 表名 where 字段 [not] like ‘字符串’;
% 代表任意字符
_ 代表单个字符

7） 空值查询

select 字段1,字段2,... from 表名 where 字段 is [not] null;

8） 带and的多条件查询

select 字段1,字段2,... from 表名 where 条件表达式1 and 条件表达式2 [...and 条件表达式n];

9） 带or的多条件查询

select 字段1,字段2,... from 表名 where 条件表达式1 or 条件表达式2 [...or 条件表达式n];

10） distinct去重复查询

select distinct 字段名 from 表名;

11） 对查询结果排序

select 字段1,字段2... from 表名 order by 属性名 [asc|desc];

12） group by分组查询

group by 属性名 [having 条件表达式][with rollup]
1.单独使用毫无意义；
2.与group_concat()函数一起使用；
3.与聚合函数一起使用
4.与having一起使用（限制输出结果）；
5.与with rollup一起使用(最后加入一个总和行)；

举例：

select gradeName,group_concat(stuName) from t_student group by gradeName;

select gradeName,count(stuName) from t_student group by gradeName;

select gradeName,count(stuName) from t_student group by gradeName having count(stuName)>3;

select gradeName,count(stuName) from t_student group by gradeName with rollup;

13） limit分页查询

select 字段1,字段2... from 表名 limit 起始位置,记录数;

使用聚合函数查询

1）count() 函数

1.count() 函数是用来统计记录条数的函数
2.与group by 关键字一起使用

2）sum() 函数

1.sum() 函数是用来求和的函数
2.与group by 关键字一起使用

3）avg() 函数

1.avg() 函数是用来求平均值的函数
2.与group by 关键字一起使用

4）max() 函数

1.max() 函数是用来求最大值的函数
2.与group by 关键字一起使用

5）min() 函数

1.min() 函数是用来求最小值的函数
2.与group by 关键字一起使用

应用举例

MySQLt_grade

/* 查询成绩表中共有多少条记录 */
SELECT COUNT(*) FROM t_grade;

/* 查询成绩表中共有多少条记录，并取别名为total */
SELECT COUNT(*) AS total FROM t_grade;

/* 查询每个学生有几门课程 */
SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;

/* 查询张三的总分 */
SELECT stuName,SUM(score) FROM t_grade WHERE stuName="张三";

/* 查询每个学生的平均分 */
SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName;

/* 查询张三的最高分并取得最高分的课程 */
SELECT stuName,course,MAX(score) FROM t_grade WHERE stuName = '张三';

/* 这样的查询是错误的，不可以带课程 */
SELECT stuName,course,MAX(score) FROM t_grade GROUP BY stuName;

/* 查询每个学生的所有课程得分中的最低分 */
SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName;

连接查询

基本概念

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据

内连接查询

内连接查询是一种最常用的连接查询，内连接查询可以查询两张或两张以上的表

/* 查询t_book和t_booktype表中的数据的三种方式
 * 条件是t_book.bookTypeId=t_booktype.id 
 */
/* 1 */
SELECT * FROM t_book,t_booktype WHERE t_book.bookTypeId=t_booktype.id;

/* 2 */
SELECT bookName,author,bookTypeName
FROM t_book,t_booktype
WHERE t_book.bookTypeId=t_booktype.id

/* 3 */
SELECT tb.bookName,tb.author,tbt.bookTypeName
FROM t_book AS tb,t_booktype AS tbt
WHERE tb.bookTypeId=tbt.id

外连接查询

外连接可以查询某一张表的所有信息：
select 属性名列表 from 表名 t1 left|right join 表名 t2 on t1.属性名=t2.属性名;

左外连接查询

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

/* 查询出t_book的所有记录
 * 并查询出t_bookType匹配的记录
 * 不匹配的用null填充 */
SELECT *
FROM t_book AS tb LEFT JOIN t_booktype AS tbt
ON tb.bookTypeId=tbt.id;
右外连接查询

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

/* 查询出t_bookType的所有记录
 * 并查询出t_book匹配的记录
 * 不匹配的用null填充 */
SELECT *
FROM t_book AS tb RIGHT JOIN t_booktype AS tbt
ON tb.bookTypeId=tbt.id;

多条件连接查询

/* 查询出t_bookType和t_book的所有数据
 * 条件是tb.bookTypeId=tbt.id AND tb.price>70 */
SELECT bookName,author,bookTypeName
FROM t_book AS tb,t_booktype tbt
WHERE tb.bookTypeId=tbt.id
AND tb.price>70

子查询

1） 带in关键字

一个查询语句的条件可能在另一个select语句的查询语句中

/* 查询图书所有信息，条件是bookTypeId在t_bookType中 */
SELECT * FROM t_book
WHERE bookTypeId IN (
    SELECT id FROM t_booktype
)

2） 带比较运算符的子查询

子查询可以使用比较运算符

/* 查询图书所有信息
 * 条件是price>=（对应于t_priceLevel中priceLevel=1的price） */
SELECT * FROM t_book
WHERE price>=(
    SELECT price FROM t_pricelevel
    WHERE priceLevel=1
)

3） 带exists关键字的子查询

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

/* 查询图书所有信息
 * 条件是t_booktype存在记录 */
SELECT * FROM t_book
WHERE EXISTS (
    SELECT * FROM t_booktype
)

4） 带any关键字的子查询

any 表示满足其中任一条件

/* 查询图书所有信息
 * 条件是price大于表t_priceLevel的price中的任何一个price */
SELECT * FROM t_book
WHERE price>=ANY(
    SELECT price FROM t_pricelevel
)

5） 带all关键字的子查询

all 表示满足其中所有条件

/* 查询图书所有信息
 * 条件是price大于表t_priceLevel的price中的所有price */
SELECT * FROM t_book
WHERE price>=ALL(
    SELECT price FROM t_pricelevel
)

合并查询结果

union

使用union关键字，数据库系统会将所有的查询结果合并到一起，然后除掉相同的记录

select id from t_book union
select id from t_bookType;

union all

使用union all，不会去除掉系统的记录

select id from t_book union all 
select id from t_bookType;

为表和字段取别名

/* way 1 */
表名 别名
属性名 别名

/* way 2 */
表名 as 表的别名
属性名 as 别名

结束语

建表和查询表是很重要的也是很繁杂的，需要多加练习，熟练掌握。

坚持原创技术分享，您的支持将鼓励我继续创作