mysql学习（一）

一、MySQL简介

什么是数据库？

首先需要了解一下什么叫做数据库, 什么叫做MySQL? 数据库简单来说, 就是一个数据存放的大仓库, 比方说, 你的身份证信息, 需要存在数据库中, 如果光靠一个文字版的, 那全国的身份证信息早乱套了, 这个大仓库, 可以取走数据(删除), 可以拿进来数据(插入), 也可以更改一些没有用数据的状态(更新), 当然, 一个仓库肯定是可以查到你数据都是有什么的(查询). 所以, 在数据库中, 一样也有<新增><修改><删除><查找>。

数据库分类：

Oracle ：收费的大型数据库， Oracle 公司的产品

MySQL ： 开源免费的中小型数据库。后来 Sun 公司收购了 MySQL ，而 Sun 公司又被 Oracle 收购

SQL Server ： MicroSoft 公司收费的中型的数据库。 C# 、 .net 等语言常使用

PostgreSQL ：开源免费中小型的数据库

DB2 ： IBM 公司的大型收费数据库产品

SQLite ：嵌入式的微型数据库。如：作为 Android 内置数据库

MariaDB ：开源免费中小型的数据库

什么是sql？

SQL：Structure Query Language。（结构化查询语言）

各数据库厂商都支持ISO的SQL标准。普通话

  各数据库厂商在标准的基础上做了自己的扩展。方言

SQL分类：

DQL(数据查询语言)：查询语句，凡是select的都是DQL
DML(数据操作语言)：增删改查，insert、delete、update
DDL(数据定义语言)：新修改表的结构 create、drop、alter
TCL(事务控制语言)：commit 提交事务；rollback回滚事务；
DCL(数据控制语言)：grant授权、revoke撤销权限
 

 表：

表是数据库的基本组成单元，是一个结构化文件。其包括

• 行：数据/记录
• 列：字段。每个字段都可以有：字段名、字段数据类型、字段约束、字段长度。

二、MySQL安装

三、MYSQL数据类型

MySQL支持多种类型，大致可以分为四类：数值型（整数型和小数型）、日期/时间和字符串(字符)类型。

注意：定义字段的时候，它是字段在前，数据类型在后。跟定义变量的时候是相反的

​​​​​（一）、数值型

1.整数类型

整数类型又称数值型数据，数值型数据类型主要用来存储数字。

MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。下表中列出了 MySQL 中的数值类型。

eg ： age int

从表中我们可以看到，占用字节数最小的是 TINYINT 类型，占用字节最大的是 BIGINT 类型。

2.小数类型

MySQL 中使用浮点数和定点数来表示小数。

浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。

浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，M表示该值的总共长度；D称为标度，D表示小数点后面的长度。

 

使用格式： 字段名 double ( 总长度 , 小数点后保留的位数 )

 eg ： score double(5,2)   总长度是5，小数点后长度是2

​​​​​（二）、日期和时间类型

MySQL 中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息的时候，可以只使用 YEAR 类型。

YEAR 类型

YEAR 类型是一个单字节类型，用于表示年，在存储时只需要 1 个字节。

TIME 类型

TIME 类型用于只需要时间信息的值，在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒。

DATE 类型

DATE 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 'YYYY-MM-DD'，其中 YYYY 表示年，MM 表示月，DD 表示日。

DATETIME 类型

DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH：MM：SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

TIMESTAMP 类型（时间戳）

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC。在插入数据时，要保证在合法的取值范围内。

注意 ：时间要从1970-01-01开始算起，1970年1月1日算UNIX和C语言诞生日

（三）、字符串类型（char和varchar类型）

CHAR(M) 为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。M 表示列的长度，范围是 0～255 个字符。

VARCHAR(M) 是长度可变的字符串，M 表示最大列的长度，M 的范围是 0～65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。

四、MySQL基本操作（数据库-->表-->数据）

DDL（包括：CREATE,ALTER,DROP关键字）

DDL:对数据库的操作

操作数据库主要就是对数据库的增删查操作。

连接数据库

mysql -u root -p

然后输入密码即可进入数据库，这里用root用户登录到mysql服务器。

查询数据库

show databases

创建数据库

CREATE DATABASE 数据库名;

删除数据库

drop database 数据库名;

使用数据库

数据库创建好了，要在数据库中创建表，得先明确在哪儿个数据库中操作，此时就需要使用数据库。

use 数据库名字;

查看当前使用的数据库

select database （）

DDL:对表的操作

操作表也就是对表进行增（ Create ）删（drop） 改（ alter ） 。

• 创建表

 create table 表名(
字段名 类型(长度) [约束],
字段名 类型(长度) [约束],
字段名 类型(长度) [约束]
);

注意：最后一行末尾，不能加逗号

• 查看数据库表

创建完成后，我们可以查看数据库表

show tables;

• 查看表的结构

 desc 表名

• 删除表（删除一张表）

drop table 表名

修改表

修改表添加列

alter table 表名 add 列名 类型(长度) [约束]

修改表删除列

alter table 表名 drop 列名 类型(长度) [约束]

删除表

drop table 表名

修改列的类型(长度、约束)
alter table 表名 modify 要修改的字段名/列名 类型(长度) [约束]

修改表的列名

alter table表名 change  旧列名　新列名　数据类型

修改表名

rename table 原始表名/旧表名  to　要修改的表名/新表名;

修改表的字符集gbk

alter table 表名 charset gbk;

DML：对数据的操作

DML主要是对数据进行增（insert）删（delete）改（update）查（select）操作。

添加数据/新增数据

给指定列添加数据

INSERT INTO 表名 ( 列名 1, 列名 2,… ) VALUES ( 值 1, 值 2,… ) ;

给全部列添加数据

INSERT INTO 表名 VALUES ( 值 1, 值 2,… ) ;

批量添加数据

INSERT INTO 表名 ( 列名 1, 列名 2,… ) VALUES ( 值 1, 值 2,… ) , ( 值 1, 值 2,… ) , ( 值 1, 值 2,… ) …;

INSERT INTO 表名 VALUES ( 值 1, 值 2,… ) , ( 值 1, 值 2,… ) , ( 值 1, 值 2,… ) …;

删除数据

DELETE FROM 表名 （WHERE 列名=值）

修改数据

 UPDATE 表名 SET 列名1=列值1,列名2=列值2,... WHERE 列名=列值;

查询数据

1.基础查询

查询一个字段：select 字段名 from 表名;

注意: 所有的sql语句都要以;结尾。并且sql不区分大小写。

查询多个字段：

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

注意：要用逗号，进行隔开
查询全部字段：

select * from 表名;
给字段起别名：

select 字段名 as 别名 from 表名;
select 字段名 别名 from 表名; (以空格分隔其别名)
字段进行简单运算：

在工资表中查询工资并乘以20 select money*20 from wages;

注意：

1.“*”表示将所有的字段都显示出来

2.用逗号分割，列出需要显示的字段

 

2.条件查询

  where条件查询可以对表中的数据进行筛选，条件成立的记录会出现在结果集中。

在 MySQL 中，如果需要有条件的从数据表中查询数据，可以使用 WHERE 关键字来指定查询条件。

 where后面的筛选条件有以下5种:

1. 比较运算符
2. 逻辑运算符
3. 模糊查询
4. 范围查询
5. 空判断

1. 比较运算符查询（单一条件）

1. 等于: =
2. 大于: >
3. 大于等于: >=
4. 小于: <
5. 小于等于: <=
6. 不等于: != 或 <>

 1：查询编号不大于4的学生:

select * from students where id <= 4;

 2. 逻辑运算符查询 （多个条件）

  and或者&&    or或者||   not或！

• 逻辑运算符and表示多个条件同时成立则为真，or表示多个条件有一个成立则为真，not表示对条件取反
• 多条件使用and 关键字进行连接

注意：

where后面的条件可以用>、<、>=、<=、!=等多种比较运算符，多个条件之间可以用or、and等逻辑运算符

1：查询编号大于3的女同学:

select * from students where id > 3 and gender='女';

3.模糊查询(LIKE)

1. like是模糊查询关键字
2. %表示任意多个任意字符
3. _表示一个任意字符

1：查询姓张的学生:

select * from students where name like '张%';

 4. 范围查询

1. between .. and .. 表示在一个连续的范围内查询
2. in 表示在一个非连续的范围内查询    【例如：select * from students where id in (3, 5,7,9);】

1：查询编号为2至4的学生:

select * from students where id between 2 and 4;

 5. 空判断查询

1. 判断为空使用: is null
2. 判断非空使用: is not null

注意：null与空字符串''是不同的

1：查询没有填写身高的学生:

select * from students where height is null;

注意:

1. 不能使用 where height = null 判断为空
2. 不能使用 where height != null 判断非空
3. 当参与运算的字段为NULL时，计算的结果始终为NULL。

3.去除重复记录（DISTINCT）

对重复字段进行去重：使用distinct关键字

select dinstinct job from emp;

tips:

1、dinstinct只能放在所有字段的最前面。代表将select的字段看成一组再进行过滤。

2、distinct只能使用需要去重的字段进行操作。 ----也就是说我sidtinct了name,age两个字段，我后面想根据id进行排序，是不可以的，因为只能name,age两个字段进行操作.

3、distinct去重多个字段时，含义是：几个字段 同时重复 时才会被 过滤。

4、可以跟分组函数一起使用。

5、distinct和聚合函数使用时，要将distinct放在聚合函数里面,

     例如：count（distinct house_name）
     而不是distinct count（house_name）

   distinct一般都放在字段的最前面的。

 4.排序查询（ORDER BY）

在 MySQL 中，GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。

使用 GROUP BY 关键字的语法格式如下：

GROUP BY  <字段名>

其中，“字段名”表示需要分组的字段名称，多个字段时用逗号隔开。

GROUP BY 与 GROUP_CONCAT() 

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

GROUP BY 与聚合函数

在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。

聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。其中，COUNT() 用来统计记录的条数；SUM() 用来计算字段值的总和；AVG() 用来计算字段值的平均值；MAX() 用来查询字段的最大值；MIN() 用来查询字段的最小值。

asc：表示升序排序。desc表示降序排序。如不指定则默认升序排序

注意：

     1.如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

     2.上面格式的执行顺序为：from --> where --> select --> order by

根据工资进行排序：
select * from emp order by ewage; 升序
select * from emp order by ewage desc; 降序
在SALESMENT部门根据工资进行降序排序，工资相同时按名字升序排序：
select * from emp where job='SALESMENT' order by ewage desc,ename asc;
 

5.聚合函数/分组函数

分组函数又叫多行处理函数、聚合函数。

分组函数都是对某一组数据进行操作

 注意：

        聚合：将多行汇总成一行。

分组函数的特点：

自动忽略null
不可以出现在where后面
使用：

查询工资总和：select sum(ewage) from emp;
找出最高工资：select max(ewage) from emp;
找出总人数：select count(*) from emp;

思考1：count(*)和count(某个字段)的区别：

count(*) 是统计记录总条数
count(某个字段) 是统计这个字段不为null的数据总数

思考2：

如果要找出工资大于平均工资的员工：可否使用下面这个语句
select ename from emp where ewage > avg(ewage);

答案是不可以。因为分组函数不能用在where后面。为什么呢？在分组查询那里揭晓。

6.分组查询group by

SELECT 字段列表 FROM 表名 [ WHERE 分组前条件限定 ] GROUP BY 分组字段名 [ HAVING 分组后条件过滤 ] ;

注意：在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段， 查询其他字段无任何意义。

tips：

分组函数一定要跟group by一起使用，这也就是它为什么分组函数的原因。
分组函数都是在group by分完组之后执行的。这就是它为什么不能放在where之后的原因
当sql语句中没有group by时，整张表的数据会自动组成一组。
当sql语句有group by时，select后面只能跟分组函数或参与分组的字段。

7.分组过滤having

在 MySQL 中，可以使用 HAVING 关键字对分组后的数据进行过滤。

使用 HAVING 关键字的语法格式如下：

HAVING <查询条件>

HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。

但是 WHERE 和 HAVING 关键字也存在以下几点差异：

• 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
• WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
• WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
• WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
• WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

8.分页查询limit

如下图所示，大家在很多网站都见过类似的效果，如京东、百度、淘宝等。分页查询是将数据一页一页的展示给用户看，用 户也可以通过点击查看下一页的数据。

 SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;

注意： 上述语句中的起始索引是从 0 开始

起始索引 = ( 当前页码 - 1 ) * 每页显示的条数

select * from user limit 1,2
1表示从第几条数据开始查(默认索引是0,如果写1,从第二条开始查)
2,表示这页显示几条数据
 

需求：客户端通过传递 start(页码)， pageSize(每页显示的条数)两个参数去分页查询数据库表中的数据，那我们知道MySql数据库提供了分页的函数 limit m,n，但是该函数的用法和我们的需求不一样，所以就需要我们根据实际情况去改写适合我们自己的分页语句，具体的分析如下：

比如：

查询第1条到第10条的数据的sql是：select * from table limit 0,10;   ->对应我们的需求就是查询第一页的数据：select * from table limit (1-1)*10,10;

查询第10条到第20条的数据的sql是：select * from table limit 10,10;  ->对应我们的需求就是查询第二页的数据：select * from table limit (2-1)*10,10;

查询第20条到第30条的数据的sql是：select * from table limit 20,10;  ->对应我们的需求就是查询第三页的数据：select * from table limit (3-1)*10,10;

通过上面的分析，可以得出符合我们需求的分页sql格式是：select * from table limit (start-1)*pageSize,pageSize; 其中start是页码，pageSize是每页显示的条数。

 小结（sql语句定义顺序和执行顺序）

sql语句定义的顺序
(1) SELECT (2)DISTINCT<select_list>
(3) FROM <left_table>
(4) <join_type> JOIN <right_table>
(5)         ON <join_condition>
(6) WHERE <where_condition>
(7) GROUP BY <group_by_list>
(8) WITH {CUBE|ROLLUP}
(9) HAVING <having_condition>
(10) ORDER BY <order_by_condition>

————————————————

sql语句执行顺序
(8) SELECT (9)DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2)         ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

————————————————

定义顺序：select--from--where--group by--having--order by 

执行顺序：from--where--group by--having--select--order by