Mysql

MySQL数据库知识总结
一。数据库基础知识
1.1数据库的基本概念
数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来可以被各种用户或应用共享的数据集合。

1.2数据库管理系统
是专门用于管理数据库的计算机软件，数据库管理系统能够为数据库提供数据的定义，建立，维护，查询和系统等操作功能，并完成对数据完整性，安全性进行控制的功能。

1.3常见的数据库管理系统
Oracle：目前比较成功的关系型数据库管理系统，运行稳定，功能齐全，性能超群，技术领先。主要用于在大型的企业数据库领域。

DB2：IBM的产品。

SQL Server：Microsoft的产品，软件界面友好，易学易用，在操作性和交互性方面独树一帜。

PostgreSQL：加州大学伯克利分校以教学目的开发的数据库系统，支持关系和面向对象的数据库，属于自由数据库管理系统。

MySQL：免费的数据库系统。被广泛用于中小型应用系统。体积小，速度快，总体拥有成本低，开放源代码。2008年被SUN收购，2009年SUN被Oracle收购。

1.4常见的关系数据库
数据库系统

所属公司

Oracle

Oracle

DB2

IBM

SQL Server

MS

MySQL

AB——>SUN——>Oracle

Oracle：运行稳定，可移植性高，功能齐全，性能超群！适用于大型企业领域

DB2：速度快，可靠性好，适于海量数据，恢复性极强。适用于大中型企业领域。

SQLServer：全面，效率高，界面友好，操作容易，但是不跨平台，适用于中小型企业领域。

MySQL：开源，体积小，速度快，适用于中小型企业领域。

二。SQL概述
2.1 SQL是什么
SQL:结构化查询语言（Structured Query Language）。是关系型数据库标准语言。

特点：简单，灵活，功能强大。

2.2 SQL的分类
数据查询语言（DQL）：
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有的SQL）用的最多的动词，其他DQL常用的保留字有WHERE, ORDE BY,

GROUP BY和HAVING.这些DQL保留字常与其他类型的SQL语句一起使用。

数据操作语言（DML）：
其语句包括动词 INSERT , UPDATE 和 DELETE。它们分别用于添加，修改和删除表中的行，也称为动作查询语言。

事务处理语言（TPL）：
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION,

COMMIT 和 ROLLBACK.

数据控制语言（DCL）：
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT 或 REVOKE 控制对表单个列的访问。

数据定义语言（DDL）：
某些语句包括动词CREATE和DROP。在数据库中创建新表或删除表(CREAT TABLE或DROP TABLE)；

为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字，它也是动作查询的一部分。

指针控制语言（CCL）：
它的语句，像 DECLARE CURSOR , FETCH INTO 和 UPDATE WHERE CURRENT 用于对一个或多个表单独行的操作。

三。表
我们说MySQL是一种关系型数据库。关系数据库最重要的概念就是表。表具有固定的列数和任意的行数，在数学上称为“关系”。二维表是同类实体的各种属性的集合，每个实体对应于表中的一行，在关系中称为元组，相当于通常的一条记录；表中的列表示属性，称为Field，相当于通常记录中的一个数据

项，也叫列，字段。

例如：（使用一张表存储学生的信息student表）

学号

姓名

年龄

班级

专业

户籍地

1001

张三

18

计科1

计算机科技学院

江苏南京

1002

李四

19

计科2

计算机科技学院

河南开封

1003

王五

20

网络1

网络工程大学

上海

1004

赵六

21

网络2

网络工程大学

深圳

1005

莫七

22

软件1

计算机科技学院

北京

3.1数据库服务器，数据库和表的关系
所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。

四。数据库操作
4.1书写规则：
在数据库中，SQL语句大小写不敏感，SELECT/select/SeleCTt

SQL语句可单行或多行书写

在SQL语句中，关键字不能跨多行或缩写

为了提高可读性，一般关键字大写，其他小写。

空格和缩进使程序易读

4.2启动MySQL服务：
打开数据库连接之前：一定要保证MySQL服务以及开启了.net start

命令名字：开启一个服务，如：start MySQL net stop

命令名字：关闭一个服务器，如：net stop MySQL

连接MySQL数据库：

方式1：进入MySQL命令行，在命令行中输入密码；

方式2：在运行敞口中：

格式：mysql -u 账户名 -p密码 -h数据库服务器安装的主机 -p数据库端口

mysql -uroot -padmin -h127.0.01 -P3306

若连接的数据库服务器在本机上，并且端口号是3306.

则可以简写：mysql -uroot -padmin

如何开启远程连接：

需更改mysql数据库里的user表里的host项

把localhost改成%

登录mysql服务器，执行以下命令

mysql>user mysql;

mysql>update user set host = ‘%’ where user = ‘root’;

mysql>flush privileges;

mysql>quit

备注：云服务器设置远程访问别忘了开发3306端口，否则连接不上

常用的操作命令：

查看数据库服务器存放在哪些数据库：

SHOW DATABASE;

使用指定的数据库：

USE database_name；

查看指定的数据库中有哪些数据表：

SHOW TABLES;

创建指定名称的数据库：

CREATE DATABASE database_name；

删除数据库：

DROP DATABASE database_name;

五。MySQL的常用数据类型
类型

大小

范围（有符号）

用途

TINYINT

1字节

（0,225）

小整数值

SMALLINT

2字节

（0,65535）

大整数值

MEDIUMINT

3字节

（0,16777215）

大整数值

INT或INTEGER

4字节

（0,4294967295）

大整数值

BIGINT

8字节

（0,1844674407370955165）

极大整数值

FLOAT

4字节

0，（1.175494351E38,

3.402823466E+38）

单精度浮点数值

DOUBLE

8字节

0,(2.2250738585072014E-308,1.7976931348623157+E+308)

双精度浮点数值

DECIMAL

对DECIMAL(M,D),如果M>D，为M+2否则为D+2

依赖于M和D的值

小数值

FLOAT[(s，p)]和DOUBLE[(s，p)]:小数类型，可存放实型和整形，精度（p）和范围（s）

如：money double(5,2):整数和小数一共占5位，其中小数占2为，最大值：999.99，最小-999.99都不够精确。

定点数据类型：DECIMAL，高精度类型，金额货币优先选择。

类型

允许长度

存储空间

CHAR

M字节

M为0~255之间的整数

VARCHAR

M字节

M为0~65535之间的整数

TINYTEXT

0~255字节

值得长度+2个字节

TEXT

0~65535字节

值得长度+3个字节

MEDIUMTEXT

0~167772150字节

值得长度+3个字节

LONGTEXT

0~4294967295字节

值得长度+4个字节

char（size）：订长字符，0~255字节，size值N个字符数，若插入字符数超过设定长度，会被截取并警告。

varchar（size）：变长字符，0~255字节，从MySQL5开始支持65535个字节，若插入字符数超过设定长度，会被截取并警告。

一般存储大量的字符串，比如文章的纯文本，可以选用TEXT系列类型。

注意：在MySQL中，字符使用单引号引起来。相当于Java中字符串（StringStringBuilder/StringBuffer）

日期与时间的类型

整数类型

字节数

取值范围

零值

YEAR

1

1901~2115

0000

dATE

4

100-01-01~9999-12-31

0000:00：00

TIME

3

-838:59:59~836:59:59

00:00:00

dATETIME

8

1000-01-01 ~9999-12-31
23:59:59

0000-00-00 00:00:00

TIMESTAMP

4

19700101080001~20380119111407

0000000000000

时期和时间类型为DATETIME,DATE,TIMESTAMP,TIME和YEAR.

注意：在MySQL中，日期时间值使用单引号引起来，相当于Java中Date，Calende

六。应用总结
6.1创建数据库：
CREATE DATABASE database_name;

6.2创建数据库表格：
CREATE TABLE table_name(column_name column_type);
例如：

CRETE TABLE t_student(
id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, —主键自增
class_id INT (11) NOT NULL, —班级ID
student_no VARCHAR (10) NOT NULL, —学号 不能为空
student_name VARCHAR (10) NOT NULL, ----姓名 不能为空
sex CHAR (2) DEFAULT NULL, —性别
birthday datetime DEFAULT NULL ----生日 日期格式
);

6.3删除数据库：
DROP DATABASE database_name;

6.4删除数据库表格：
DROP TABLE table_name;

6.5插入数据到数据库表格：
INSERT INTO table_name values();
例如：

INSERT INTO t_student
VALUES(‘1’,‘1’,‘2016001001’,‘张艺兴’,‘男’,‘1991-10-07 00:00:00’);

6.6修改表
删除列

ALTER TABLE table_name DROP column_name;
添加列

ALTER TABLE table_name ADD column_name INT;
修改列类型

ALTER TABLE table_name MODIFY column_name CHAR(10);
修改列名称

ALTER TABLE table_name CHANGE c1 c2 BIGINT;
修改字段默认值

ALTER TABLE table_name ALTER column_name SET DEFAULT 1;

6.7表中内容的修改
删除表中数据：

DELETE FROM table_name where ----条件语句
例如：

DELETE FROM t_student WHERE student_name = ‘王宝强’;
如果不使用where子句，将删除表中所有数据。

Delete语句不能删除某一列的值（可使用update）

使用delete语句仅删除记录，不删除表本身。

修改表中数据：

IPDATE table_name SET
例如：

UPDATE t_student SET student_name = ‘王宝强’，sex=‘男’ WHERE id=1
UPDATE语法可以用新值更新原有表行中的各列。

SET子句指示要修改哪些列和要给与哪些值，

WHERE子句指定应更新哪些行，如果没有WHERE子句，则更新所有的行

6.8查询数据：
查询分类：1.多行多列 2.多行单列 3.单行多列 4.单行单列

查询语句1：

SELECT [DISTINCT] *| {column1,column2,coumn3…} FROM table;
例如：

SELECT DISTINCT(job) FROM EMP WHERE com > 0;
SELECT指定查询哪些列的数据。

column指定列名。

*号代表查询所有列。

FROM指定查询哪张表。

DISTINCT可选，指显示结果时，是否剔除重复数据

查询语句2（函数查询）

在SELECT语句中可使用表达式对查询的列进行运算

SELECT * | {column1|expression,column2|expression,…} FORM table;
在SELECT语句中可使用as语句

SELECT column as 别名 from 表名；
例如：

合计函数：sum avg min max count

1.查询平均工资

select avg(sal) a,deptno from emp(工资平均值)
2.查询总人数

select count(ename)‘名字’ from emp(查询总人数)
3.查询工资总和

select sum(sal) ‘总工资’ from emp(所有人工资总数)
4.查询工资最高的人

select max(sal) from emp (最高工资是多少)
5.查询公司中最晚入职的时间

SELECT MAX(HIREDATE) FROM EMP;（入职时间最晚的人，入职越晚，日期数越大）
日期函数：year month day （dei） now DATE DATEDIFF

Select year(hiredate) from emp (显示入职的年，如1981)

Select month(hiredate) from emp (显示入职的月，如3)

Select day(hiredate) from emp (显示入职的日期，如28)

Select datediff(now(),hiredate) from emp
(datediff 函数里面填写2个日期，返回的结果为第一个日期减去第二个日期的天数，now()函数是获取系统当前的时间）

1.字符串函数：CONCAT（string2[,…]）连接字符串

concat（）函数里面的参数是2个字符串，其函数可以将两个字符串拼接到一起

例如：

Select concat（‘姓名’.ename） from emp —会显示
2.UCASE(string2)转换大写 UCASE

Select ucase(ename) from emp —返回的所有ename会变成小写
3.LCASE(string2)转换小写LCASE

Select lcase(ename) from emp —返回所有ename会变成小写
4.LEFT（string2，length）从左边开始显示几个字符

Select left(ename,3) from emp 所有人的姓名只显示左边起前三位字母
时间相关函数总结

函数

用途

ADDTIME(date2,time_interval)

将time_interval加到date2

current_date ()

当前日期

current_time ()

当前时间

current_timestamp ()

当前时间戳

date (datetime)

返回datetime的日期部分

DATE_ADD (date2,INTERVAL d_value d_type)

在date2中加上日期或时间

DATE_SUB (date2,INTERCAL d_value d_type)

在date2中减去日期或时间

DATEDIFF (date1,date2)

两个日期差

NOW ( )

当前时间

YEAR | MONTH | DAY (datetime)

年月日

例如：

select addtime(‘02:30:30’,‘01:01:01’); 注意：字符串，时间日期的引号问题

select date_add(entry_date,INTERVAL 2 year) from student; 增加两年

select addtime(time,‘1 1-1 10:09:09’) from student; 时间戳上增加
字符串相关函数

函数

用途

CHARSET(str)

返回字符串字符集

CONCAT(string2[,…])

连接字符串

INSTR(string,substring)

返回substring中出现的位置，没有返回0

UCASE(string2)

转换成大写

LCASE(string2)

转换成小写

LEFT(string2,length)

从string2中的左边起取length个字符

LENGTH(string)

string长度

REPLACE(str,search_str,replace_str)

从str中用replace_str替换search_str

STRCMP(string1,string2)

逐字符比较两字符串大小

SUBSTRING(str,position[,length])

从str的position开始，取length个字符

LTRIM(string2) RTRIM(string2) trim

去除前端空格或后端空格

数学相关的函数：

header1

header2

ABS(number2)

绝对值

BIN(decimal_number)

十进制转二进制

CEILING(number2)

向上取整

CONV(number2,from_base,to_base)

进制转换（数字，原进制，目标进制）

FLOOR(number2)

向下取整

FORMAT(number,decimal_places)

保留小数位数

HEX(DecimalNumber)

转十六进制

LEAST(number,number2[,…])

求最小值

MOD(numerator,denominator)

求余

RAND ( )

随机数

查询语句3

使用order by子句排序查询结果：

SELECT column1,column2,column3… FROM table order by column asc | desc
Order by指定排序的列，排序的列即可是表中的列名，也可以使select语句后指定的列名。

Asc升序，Desc降序

ORDER BY 子句应位于SELECT语句的结尾。

例如:

1.查询所有员工的编号，姓名，部门编号，职位，薪水，按照薪水降序排列

select EMPNO,ENAME,DEPTNO,JOB,SAL from EMP ORDER BY SAL DESC;
2.查询所有员工信息，按照部门降序排列，部门内部按照薪水升序排列

select * from EMP ORDER BY DEPTNO DESC,SAL ASC;
3.查询姓名中包含‘A’员工的姓名，编号，薪水，按照薪水降序排列

select ENAME,EMPNO,SAL from EMP WHERE LIKE ‘%A%’ ORDER BY SAL DESC
4.查询年收入超过10000的员工的姓名，编号，薪水，年收入，按照年收入降序排列Ifnull()：任何值与空间值进行运算都会得到空，所以我们在对有可能带有空值的列进行计算时使用Ifnull()函数，Ifnull()函数里面第一个参数为我们需要运算的列或值，如果这个值是nunll就使用我们的第二个参数替代，所以第二个参数我们一般填写0，代表如果这个值是空就用0进行计算

SELECT ENAME,EMPND,SAL,(sal+ifnull(COMM,0))*12 AS YEARSAL FROM EMP WHERE
(SAL+ifnull(COMM,0))*12 > 10000 ORDER BY YEARSAL DESC;
5.查询年薪超过10000的员工的姓名，编号，薪水，年收入，按照年薪降序排列

SELECT ENAME,EMPNO,SAL,SAL12 AS YEARSAL FROM EMP WHERE SAL12 >
10000 ORDER BY YEARSAL DESC;
查询语句4（子查询）

一对多

多对多

一对一

连接查询：

交叉连接（cross join）：不带on子句，返回连接表中所有数据行的笛卡尔积。

交叉连接查询CUSTOMER表和ORDERS表

SELECT * FORM customer CROSS JOIN orders;

SELECT * FROM customer，orders;
内连接（inner join）：返回连接表中符合连接条件及查询条件的数据行。

显示内连接：使用inner join关键字，在on子句中设定连接条件

SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id;
例如：

select e.*,m.ename ‘领导’ from emp e INNER JOIN emp m ON e.empno
and e.ename = ‘smith’
隐式内连接：不包含inner join 关键字和on关键字，在where子句设定连接条件

SELECT * FROM customer c,orders o WHERE c.id=o.customer_id;
例如：

SELECT e.*,m.ename ‘领导’ from emp e, emp m where e.empno=m.empno
and e.deptno =20
外链接：分为左外链接（left out join），右外连接（right outer join），与内连接不同的是，外链接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外链接时）或右表（右外链接时）中仅符合查询条件但比符合连接条件的数据行。

使用left outer join关键字，在on子句中设定连接条件

SELECT * FORM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id;
不仅包含符合c.ic=o.customer_id连接条件的数据行，还包含customer左表中的其他数据行带查询条件的左外链接查询，在where子句中设定查询条件

SELECT * FROM customer c LEFT OUTER JOIN order o ON c.id=o.customer_id
WHERE o.price > 250;
子查询

子查询也叫嵌套查询，是指在slect子句或者where子句中又嵌入select查询语句，子查询的语句放在小括号只内。例如:查询"王宝强"的所有订单信息

SELECT ename,masx(sal),deptno from emp where sal in (select max(sal) s
from emp GROUP BY deptno) GROUP BY deptno
联合查询

联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。联合查询使用unioni 关键字

SELECT * FROM orders WHERE price >200 UNION SELECT * FROM orders
WHERE customer_id=1;
报表查询

报表查询对数据进行分组统计，其语法格式为：

[select…] from … [where…][group by …[having…]][order by …]
其中group by子句指定按照哪些字段分组，having子句设定分组查询条件，在报表查询中可以使用SQL含数。

查询语句5（group by）

使用group by子句对列进行行分组

SELECT column1，column2，column3… FROM table;
group by column
例如：

SELECT count(deptno)from emp GROUP BY deptno

select max(sal),deptno from emp GROUP BY deptno
子查询和group By联合使用：

SELECT ename,max(sal),deptno from emp where sal in (select max(sal) s from emp
GROUP BY deptno ) GROUP BY deptno
使用having子句过滤

SELECT column1，column2，column3… FROM table;
group by column having…
Having和where均可实现过滤，但在having可以使用合计函数，having通常跟在group by后，它作用于组。