MySQL使用文档

MySQL使用手册

MySQL简介

在目前阶段，如果要存储和管理数据，则离不开数据库，当数据存储到数据库后，就会通过数据库管理系统对这些数据进行组织和管理，所谓的数据管理系统，是指对各种数据进行分类、组织、编码、存储、检索和维护。发展到现在，数据管理技术经历来三个阶段，分别为人工管理阶段、文件系统阶段和数据库系统阶段。

数据库管理系统提供了许多功能，可以通过SQL(结构化查询语言)来定义和操作数据，维护数据的完整性和安全性，以及进行各种数据库的管理等。

SQL是Structure Query Language(结构化查询语言)。该语言由IBM在20世纪70年代开发出来，实现关系数据库中信息的检索。由于SQL简单易学、功能丰富和使用灵活，因此受到很多人的追捧。经过不断的发展、完善和扩充，SQL语言被国际标准化组织(ISO)采纳为关系数据库语言的国际标准。

MySQL是一款免费开源、小型、关系型数据库管理系统。随着该数据库功能的不断完善、性能的不断提高，可靠性不断增强，2000年4月，MySQL对旧的存储引擎进行了整理，命名为MyISAM, 2001年支持事物处理和行级锁，存储引擎InnoDB被集成到MySQL发行版中，2005年10月，发布了里程碑的一个版本，MySQL5.0,在5.0中加入来游标，存储过程，触发器，视图和事务的支持。在5.0之后的版本里，MySQL明确的表现出迈向高性能数据库的发展步伐，MySQL公司于2008年1月16日被SUN公司收购，而在2009年SUN又被Oracle公司收购。

MySQL由于其体积小、速度快、总体拥有成本低，尤其是开发源码的这一特点，因此许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。MySQL：MySQL是最流行的开放源码SQL数据库管理系统，它是由MySQL AB公司开发、发布并支持的。MySQL AB是由多名MySQL开发人创办的一家商业公司。它是一家第二代开放源码公司，结合了开放源码价值取向、方法和成功的商业模型。在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

MySQL安装与配置

基于客服端**-服务器(C/S)**的数据库管理系统：到目前为止，市场上几乎所有的数据库管理系统都是基于客服端-服务器模式，服务器软件是负责所有数据访问和处理的一个软件，而关于数据添加，删除等所有请求都来自于客服端软件。

**安装MySQL：**在官网上提供来两种模式的软件：压缩版(ZIP Archive)和安装版(MSI Installer)。在这里主要讲解安装版。

什么是数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

1. 数据以表格的形式出现
2. 每行为各种记录名称
3. 每列为记录名称所对应的数据域
4. 许多的行和列组成一张表单
5. 若干的表单组成database

数据库操作：

​ 创建： create database 数据库名； 使用： use 数据库名; 删除：drop 数据库名; 查看：show databases;

数据类型：

• 整数类型： tinyint* smallint mediumint int/integer* bigint
• 小数类型： float double*
• 日期和时间类型： date* datetime timestamp(时间戳->当前系统时间)* time year
• 字符串类型： char varchar* tinytext text* mediuetext longtext
• 大数据类型：binary* varbinary tinyblob blob* mediumblob longblob

命名规则：

1. 字母,数字,下划线，@ , # , $
2. 不能以数据库中关键字和保留字命名
3. 不能以数字和$开头
4. 长度小于128 byte
5. 在数据库中不区分大小写,日期 和字符串除外

MySQL DDL操作

DDL(Data Definition Language) : 允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

创建数据表

创建MySQL数据表需要以下信息：

• 表名
• 表字段名
• 定义每个表字段
• 约束 ,常用的约束有以下几种:

约束类型:	主键	外键	唯一	非空	自增	默认值
关键字:	primary key	foreign key	unique	not null	auto_increment	default

常用命令:

• show table status [from 数据库]; 查看数据表详细信息
• show tables; 显示当前数据库中所有表
• desc <table_name>; 查看表结构信息

创建数据表语法:

CREATE TABLE [IF NOT EXISTS] `tbl_emp`(
   `column1` INT [NOT NULL] [PRIMARY KEY] AUTO_INCREMENT,
   `column2` VARCHAR(100) NOT NULL,
   `column3` timestamp default current_timestamp,
   `column4` INT,
   PRIMARY KEY  (`column1`,`column2`),  --复合主键
   UNIQUE KEY `uk_index` (`emp_title`),  --外键
   CONSTRAINT fk_table_id FOREIGN KEY(fk_name) REFERENCES tbl_table_name(pk_name);
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

修改表语法(了解):

1.  向表中添加新的字段：alter table <table_name> add (字段1 类型 [not null],字段2 类型 [not null].... );
2.  修改表中字段：alter table <table_name> modify 字段1 类型;
3. 删除表中字段：alter table <table_name> drop 字段1;
4. 修改表的名称：alter table <table_name> rename [to] <new table_name>;
5. 已存在的表添加约束：alter table <table_name> add constraint <constraint_name> 约束类型 (针对的字段名);

复制表

	CREATE TABLE table_name [AS] SELECT 语句 [WHERE 条件];
	CREATE TABLE tb_dept2 LIKE tb_dept

复制数据

	 INSERT INTO table_name(field1, field2,...fieldN ) SELECT 语句

删除数据表

MySQL中删除数据表是非常容易操作的， 但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

DROP TABLE table_name ;

MySQL DML操作

DML：Data Manipulation Language
DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

插入数据

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

插入数据的语法:

INSERT INTO table_name [(field1, field2,...fieldN )] VALUES( value1, value2,...valueN );   -- 单条
INSERT INTO table_name (field1, field2) VALUES ( value1,value2), ( value1,value2), .... ;  -- 批量

更新数据

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。.

修改数据的语法:

UPDATE table_name SET field1=val1, field2=val2 [WHERE 条件]

删除数据

你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

修改数据的语法:

DELETE FROM table_name [WHERE 条件]

MySQL DQL操作

DQL：Data Query Language
DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

使用SELECT语句从表或视图获取数据。表由行和列组成，如电子表格。 通常，我们只希望看到子集行，列的子集或两者的组合。SELECT语句的结果称为结果集，它是行列表，每行由相同数量的列组成。

SELECT [DISTINCT] < column_1 [as new name], column_2, ... >
FROM <table1>
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
[WHERE <条件>]
[GROUP BY <column_list>]
[HAVING <条件>]
[ORDER BY <column_list> [ASC|DESC]]
[LIMIT offset, length]

DISTINCT --表示隐藏重复的行
WHERE --按照一定的条件查找记录
GROUP BY --分组查找(需要汇总时使用)
HAVING --分组的条件
ORDER BY --对查询结果排序
LIMIT  --分页
-- 要显示全部的列可以用*表示
-- 并集连接： UNION 会去掉重复项   UNION  ALL 不会去掉重复项

WHERE 语句的运算符

• where <条件１>AND<条件２> --两个条件都满足
• where <条件１>OR<条件２> --两个条件中有一个满足即可
• where NOT <条件> --不满足条件的
• where [NOT] IN(条件列表) --所有满足在条件列表中的记录
• where [NOT] BETWEEN … AND　…　 --按范围查找
• where 字段 LIKE --主要用与字符类型的字段 ‘-’ 表示任意字符 ‘%’ 表示多字符的序列
• where 字段 IS [NOT] NULL --查找该字段是[不是]空的记录

子查询

可以把查询条件当做表,条件,字段来使用,这种操作对于复杂SQL非常常用.
SELECT <字段列表> from <table_name> where 字段 运算符(<SELECT 语句>);

-- 把查询语句当做条件
SELECT s.sid,sname FROM sc JOIN student s ON sc.sid = s.sid 
WHERE cid IN ( SELECT cid FROM sc WHERE sid = 1001 ) AND s.sid !=1001;

-- 把查询语句当做表
SELECT t100.sid ,t100.score score100,t200.score score200 
FROM (SELECT score,sid FROM sc WHERE cid=100 )t100 
JOIN(SELECT sid,score FROM sc WHERE cid=200 )t200 
ON t100.sid = t200.sid WHERE t100.score>t200.score;

-- 把查询语句当做字段显示
SELECT COUNT(sid) 男生人数,(SELECT COUNT(sid) FROM student WHERE sgender=1) 女生人数 
FROM student WHERE sgender=0;

常用的函数

数学函数

函数名	描述
ABS(x)	返回x的绝对值
CEILING(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x/y的模（余数）
RAND()	返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
ROUND(x[,y])	返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y)	返回数字x截短为y位小数的结果
FORMAT(x,y)	财务数字

聚合函数

函数名	描述
AVG(col)	返回指定列的平均值
COUNT(col)	返回指定列中非NULL值的个数
MIN(col)	返回指定列的最小值
MAX(col)	返回指定列的最大值
SUM(col)	返回指定列的所有值之和
GROUP_CONCAT(col)	返回由属于一组的列值连接组合而成的结果

字符函数

函数名	描述
CONCAT(s1,s2…,sn)	将s1,s2…,sn连接成字符串
CONCAT_WS(sep,s1,s2…,sn)	将s1,s2…,sn连接成字符串，并用sep字符间隔
FIND_IN_SET(str,list)	分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LOWER(str) / UPPER(str)	返回将字符串str中所有字符改变为小写/大写后的结果
LEFT(str,x) / RIGHT(str,x)	返回字符串str中最左边/最右边的x个字符
LENGTH(s)	获取字符串字节长度
CHAR_LENGTH(str)	获取字符个数
LTRIM(str) / RTRIM(str) / TRIM(str)	从字符串str中切掉开头/结尾/首尾的空格
POSITION(substr,str)	返回子串substr在字符串str中第一次出现的位置
QUOTE(str)	用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)	返回字符串str重复x次的结果
REVERSE(str)	返回颠倒字符串str的结果
REPLACE(str,from_str,to_str)	用字符串to_str替换字符串str中的子串from_str并返回
SUBSTRING(str,pos,len)/MID(str,pos,len)	返回字符串str的位置pos起len个字符
SBUSTRING_INDEX(str,delim,count)	返回从字符串str的第count个出现的分隔符delim之后的子串(count为正数时返回左端,否则返回右端子串)
RPAD/LPAD(str,len,padstr)	用字符串padstr填补str右端直到字串长度为len并返回

日期和时间函数

函数名	描述
CURRENT_TIMESTAMP	返回当前系统详细日期时间
SYSDATE() / NOW()	返回当前系统日期时间
CURDATE() / CURRENT_DATE	返回当前日期
CURTIME() / CURRENT_TIME	返回当前时间
UNIX_TIMESTAMP(DATA)	返回当前日期时间的时间戳(毫秒数)
FROM_UNIXTIME(毫秒数 [,format])	把时间戳转为对应格式的字符串
FORMAT(日期格式化,不是函数)	%Y/y-%M/m-%D/d %H/h : %I : %S %F %W/w
YEAR(date)	获取年份
QUARTER(date)	获取季度
MONTH(date)	获取月份
DAYOFMONTH(date)	获取天数
HOUR(date)	获取小时
MINUTE(date)	获取分钟
SECOND(date)	获取秒
WEEKDAY(date)	获取数字星期(0-6)
DAYNAME(date)	获取英文星期
STR_TO_DATE(DATE_STR,FORMAT)	把字符串日期转为日期格式的Date
DATE_FORMAT(DATE,FORMAT)	把日期转为指定格式的字符串
ADDDATE(date,num)	在指定的日期上添加对应的天数,num可以是正数或负数
ADDDATE(date,INTERVAL EXPR TYPE)	返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);SELECT ADDDATE(CURDATE(),INTERVAL ‘2,3’ YEAR_MONTH)
SUBDATE(DATE,NUM / INTERVAL EXPR TYPE)	返回日期date减去间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DATEDIFF(DATE1,DATE2)	两个日期相差的天数

格式化函数

函数名	描述
DATE_FORMAT(date,fmt)	依照字符串fmt格式化日期date值
FORMAT(x,y)	把x格式化为以逗号隔开的数字序列，y是结果的小数位数
INET_ATON(ip)	返回IP地址的数字表示
INET_NTOA(num)	返回数字所代表的IP地址
TIME_FORMAT(time,fmt)	依照字符串fmt格式化时间time值

SELECT FORMAT(34234.34323432,3);
SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
SELECT DATE_FORMAT(19990330,'%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%h:%i %p');
SELECT INET_ATON('10.122.89.47');
SELECT INET_NTOA(175790383);

控制流函数

MySQL有4个函数是用来进行条件操作的，这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。

函数名	描述
CASE WHEN[test1] THEN [result1]…ELSE [default] END	如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]…ELSE [default]END	如果test和valN相等，则返回resultN，否则返回default
IF(test,t,f)	如果test是真，返回t；否则返回f
IFNULL(arg1,arg2)	如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2)	如果arg1=arg2返回NULL；否则返回arg1

SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');
SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);
SELECT IF(1<10,2,3),IF(56>100,'true','false');
SELECT CASE 'green' WHEN 'red' THEN 'stop' WHEN 'green' THEN 'go' END;
SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END;