mysql 5.1 concat min,MySQL

数据类型: 字符型、整型、浮点型、日期时间型

操作符: 算术运算符、比较运算符、逻辑运算符、位运算符

属性: 自动编号

约束: 主键、外键、表关连、索引

库操作: 创建库、修改库、删除库

表操作: 创建表、删除表、查看表结构、查看库中所有表

列操作: 添加列、删除列、修改列

记录操作: 插入记录、更新记录(单表、多表更新)、删除记录、查询记录(多列查询、分组、指定查询范围、使用别名)

子查询语句、例约束

运算符与函数: 字符函数、数值运算符、比较运算符、日期时间函数、信息函数、聚合函数、加密函数、自定义函数

存储过程: 创建(参数:IN、OUT、INOUT)、调用

其它概念: 存储引擎、并发控制(锁、锁颗粒)、事务

MySQL基础一、MySQL介绍

1、MySQL是关系型数据库

2、安装后的有4个默认库 nformation_schem、performance_schema、test、mysql

3、Mysql 端口号 3306

4、mac下mySQL的配置文件 /usr/local/mysql/my.cnf

二、MySQL 目录结构

mysql安装后的路径 /usr/local/mysql

1、bin: 存储可执行文件

2、data: 存储数据文件

3、docs: 文档

4、include: 存储包含头文件

5、lib: 存储库文件

6、share: 错误消息和字符集文件

三、停止和启动mysql服务

关闭mysql服务 查看进程：ps -ef|grep mysql 杀进程和子进程：sudo kill -9 239 746

启动mysql服务 sudo /usr/local/mysql/bin/mysqld_safe

系统偏好设置 -> mysql 启动

四、登录、退出mysql

1、登录

mysql -uroot -pssssss -h127.0.0.1

-u 用户名 -p 密码 -P 端口号 -h 数据库地址

隐藏密码输入 mysql -uroot -h127.0.0.1 -p 回车后在输入密码

2、退出: exit;

五、语法规范

1、关键字和函数名全部大写

2、数据库名称、表名称、字段字都小写

3、SQL语句必须以";"分号结束

修改结束符 DELIMITER // 以 // 为结束

六、数据库概念

1、库: 一些关联表的集合

2、表: 行和列组成, 行一条数据

3、主键: 可以唯一标识不会有重复, 可以通过主键来查询数据

4、外键: 外键可以引用主键数据，用于关联

七、安装和卸载mysql

1、homebrew安装mysql

http://brew.sh/

$ brew install mysql // 安装mysql

$ mysql.server start // 启动

$ mysql -V // 查看版本

2、卸载mysql

sudo rm /usr/local/mysql

sudo rm -rf /usr/local/var/mysql

sudo rm -rf /usr/local/mysql*

sudo rm -rf /Library/StartupItems/MySQLCOM

sudo rm -rf /Library/PreferencePanes/My*

vim /etc/hostconfig and removed the line MYSQLCOM=-YES-

rm -rf ~/Library/PreferencePanes/My*

sudo rm -rf /Library/Receipts/mysql*

sudo rm -rf /Library/Receipts/MySQL*

sudo rm -rf /var/db/receipts/com.mysql.*

库服务命令1、启动和停止MySQL服务

service stop mysql

service start mysql

2、登录: mysql -uroot -p123456 -h127.0.0.1

3、退出: \q 、 exit、 quit

库操作语句1、创建数据库 CREATE DATABASE 库名;

创建一个指定字符集的数据库 CREATE DATABASE 库名 CHARACTER SET utf8;

2、修改数据库 ALTER DATABASE 库名 CHARACTER SET = uft-8; // 将库编码改成utf8

3、删除数据库 DROP database 库名;

4、查看所有数据库 SHOW databases;

5、打开数据库 USE 库名;

6、查看当前所在的数据库 SELECT database();

7、查看库的编码: SHOW CREATE DATABASE 库名;

操作表语句1、创建表 CREATE TABLE 表名(字段名 数据类型, 字段名 数据类型);

CREATE TABLE 表名(字段 数据类型 primary key, username VARCHAR(20)); // 加主键 primary key

2、查看库中所有表 SHOW TABLES [db_name] [where expr];

3、查看表结构 SHOW COLUMNS FROM 表名; // 与 desc 表名; 相同

4、删除表 DROP TABLE 表名;

5、ALTER 列操作:

1)添加列: ALTER TABLE 表名 ADD 列名 varchar(20);

2)删除列: ALTER TABLE 表名 DROP 列名;

3)修改列的类型信息

ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称(这里可以用和原来列同名即可)】 BIGINT NOT NULL COMMENT '注释说明'

4)重命名列

ALTER TABLE 【表名】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL COMMENT '注释说明'

5)重命名表

ALTER TABLE 【表名字】 RENAME 【表新名字】

6)删除表中主键

Alter TABLE 【表名字】 drop primary key

7)添加主键

ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)

8)添加索引

ALTER TABLE sj_resource_charges add index INDEX_NAME (name);

9)添加唯一限制条件索引

ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);

10)删除索引

ALTER TABLE [表名] index emp_name;

记录操作语句1、插入记录

1) INFO 插入指定字段值: INSERT INTO 表名(name, age) VALUES (name值, age值);

2) SET 插入指定字段值: INSERT 表名 SET name="sssss", age="lll";

3)插入所有字段值: INSERT 表名 VALUES (NULL, name值, age值)； // 1、没有INFO 2、如果id为主键、自增涨，需要给一个默认传入NULL 或 DEFAULT

4)INSERT ... SELECT 将查询结果写入数据表

INSERT [INTO] 表名 FROM 列名 SELECT

// 将查询出restaurant表中的type进行分组内容，插入到rest_type表中的type字段中

INSERT rest_type(type) SELECT type FROM restaurant GROUP BY type;

2、更新数据

单表更新: UPDATE 表名 SET 要更新字段名=新值, 要更新字段名=新值 WHERE 条件 // 如果省略where条件，将修改表中的所有记录

多表更新:

UPDATE 表1 [JOIN | CROSS JOIN | INNER JOIN] 表2

表连接类型，JOIN、CROSS JOIN、INNER JOIN

LEFT [OUTER] JOIN 左外连接

RIGHT [OUTER] JOIN 右外连接

3、删除记录

单表删除: DELETE FROM 表名 WHERE id=2

多表删除:

4、查询记录

查询所有列: SELECT * FROM 表名; // 查询所有列

查询多列: SELECT username, age, sex FROM 表名;

条件查询: SELECT * FROM 表名 WHERE 条件表达式(id<100); // 查询表中的id小于100的所有记录

查询结果分组 GROUP BY: 返回一个列中所有不相同的类别

SELECT * FROM 表名 GROUP BY 列名 [ASC | DESC]; // 分组以哪列进行返回 ASC | DESC 正、倒序

例: SELECT * FROM users GROUP BY sex; # 返回sex列的分组内容，结果为两类: '男' 和 '女'

HAVING 分组条件: SELECT * FROM 表名 GROUP BY 列名 HAVING version < 100; // 分组后进行过滤

LIMIT 指定查询范围: SELECT * FROM 表名 LIMIT 5, 10; // 从第5条数据开始，查找出10条数据，也就是查询出第5-15条数据

AS 给予别名: 当名子太长可以定义一个名，输出的时候就是定义的名字

SELECT person AS ps, username AS un FORM users;

--------------- select 其它 ---------------

SELECT COUNT(*) FROM 表名 WHERE (条件) // 返回记录的总数，如果不加条件返回整个表的总记录

SELECT AVG(price) FROM 表名; // AVG()来求一个字段的平均值

SELECT ROUND(AVG(price), 2) FROM 表名; // ROUND() 对数值进行四舍五入

小技巧:

1、SELECT * FROM users\G; // 加\G 以网格形势展示查询，格式化了数据方便来看

设置空值

例 UPDATE tag SET label='折' WHERE label=NULL; // 将label列中所有空值更新成'折'，失效不会被

MySQL: null值需要通过几种方式

1、is null、is not null: UPDATE tag SET label='折' WHERE label is null;

2、ISNULL(): 与上面一样 UPDATE tag SET label='折' WHERE ISNULL(label);

子查询语句子查询指嵌套在查询内部，且必须始终出现在圆括号内

子查询包含关键字或条件: DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等

子查询外层查询可以是: SELECT、INSERT、UPDATE、SET、DO

1、按返回结果集分类

http://www.cnblogs.com/herry52/p/5643986.html

分为4种：表子查询，行子查询，列子查询和标量子查询

1) 表子查询：返回的结果集是一个行的集合，N行N列(N>=1)。表子查询经常用于父查询的FROM子句中。

示例: 获取编号小于10的男性球员的号码

SELECT playerno FROM (SELECT playerno, sex FROM players WHERE playerno < 10) AS players10 WHERE sex='M';

分析:

// 查询players中playerno小于10的两个字段的记录，并起别名为player10

SELECT playerno, sex FROM players WHERE playerno < 10 AS players10;

// 显示playerno字段内容，条件是子表中的sex='M'

SELECT playerno FROM 查询出的内容 WHERE sex='M'

2) 行子查询：返回的结果集是一个列的集合，一行N列(N>=1)。行子查询可以用于福查询的FROM子句和WHERE子句中。

示例: players表中获取与100号球员性别相同并且居住在同一城市的球员号码。

SELECT playerno, sex, town FROM players WHERE (sex, town) = (select sex, town FROM players where playerno = 100);

3) 列子查询：返回的结果集是一个行的集合，N行一列(N>=1)

列子查询可以使用 IN、ANY、SOME、ALL

1、修饰的比较运算符: ANY、SOME、ALL

如果子查询返回：Subquery returns more than 1 row 多于一行记录，使用上面运算符

ANY和SOME是等价的意思只符合一个就可以

ALL 需要符合全部

2、IN、NOT IN 子查询

4) 标量子查询：可以指定一个标量表达式的任何地方，几乎都可以使用一个标量子查询

示例: 获取和27号球员出生在同一年的球员的号码

SELECT playerno, name, birth_date FROM players WHERE YEAR(birth_date) = (SELECT YEAR(birth_date) FROM players WHERE playerno=100) AND playerno != 100;

—————————– 运算符与函数 —————————–

字符函数1、CONCAT(): 字符连接 SELECT CONCAT('user', '-', 'name');

// 将tag表中value和label字段的值连接，并通过AS来返回一个别列名为tagName

SELECT CONCAT(value, '_', label) AS tagName FROM tag;

2、CONCAT_WS(): 使用指定的分隔符进行字符连接

SELECT CONCAT_WS('|', name, user, id); 以"|"进行分隔

3、FORMAT(): 数据格式化

SELECT FORMAT(132.34234, 2); // 132.34

4、LOWER(): 将字符转成小写

5、UPPER(): 将字符转成大写

6、LEFT()、RIGHT(): 获取从左、右侧字符的几位

SELECT LEFT('what are you doing.', 7); // what ar

7、LENGTH(): 获取字符串长度

8、LTRIM()、RTRIM()、TRIM(): 删除前空格、删除后空格、删除前后空格

9、SUBSTRING(): 字符串的截取 substring();

SELECT SUBSTRING('what are you doing.', 3, 7); // at are

10、RESPLACE(): 字符替换

SELECT REPLACE('what are you doing.', ' ', ''); // whatareyoudoing.

11、LIKE(): 模式匹配

%: 为任意字符 _: 任意一个字符

ESCAPE: 使用哪个字符后的百分号

SELECT * FROM sys_user WHERE username LIKE '%@renrendai.com%'\G; // sys_user表中username字段匹配出 带有@renrendai.com的字符

SELECT * FROM sys_user WHERE username LIKE '%renren%%' ESCAPE 'renren'\G; // 匹配出 renren%

数值运算符1、CEIL(): 向上取整 SELECT CEIL(4.9); // 5 SELECT CEIL(log) FROM office;

2、FLOOR(): 向下取整 SELECT FLOOR(4.2); // 4

3、DIV(): 整数除法 SELECT 20 DIV 3; // 6

4、MOD(): 取模

5、POWER(): 幂运算

6、ROUND(): 四舍五入 SELECT ROUND(2.3464, 2); // 2.35

7、TEUNCATE(): 数字截取

比较运算符1、[NOT] BETWEEN ... AND ... : 在不在一个范围内

// 查找出fruit表中fruit_price字段(价格)10元至30无之间的数据

SELECT fruit_id, fruit_desc, fruit_price FROM fruit WHERE fruit_price BETWEEN 10 AND 30;

2、[NOT] IN(): 在不在几个值的内

SELECT 10 IN (5, 20, 40); // 返回0

SELECT 10 IN (2, 10, 30, 49); // 返回1

3、IS [NOT] NULL: 返回为空， IS NOT NULL 返回不为空

// 返回 fruit_dim_three 内容为空的值

SELECT fruit_product_name, fruit_price,fruit_dim_three FROM fruit WHERE fruit_dim_three IS NULL;

函数一、日期时间函数

1、NOW(): 返回一个当前日期和时间

2、CURDATE(): 当前日期

3、CURTIME(): 当前时间

4、DATE_ADD(): 在一个时间内添加多少时间

SELECT DATE_ADD('2016-12-12', INTERVAL 365 DAY); // 返回 2017-12-12 , -365就是减365天

5、DATEDIFF(date1, date2): 两个日期的差值

SELECT DATEDIFF('2018-12-12', NOW()); // 2018-12-12与今天相差多少天

6、DATE_FORMAT(): 日期格式化

SELECT DATE_FORMAT('2016-12-10', '%Y/%m/%d'); // 转换成 2016/12/10

SELECT DATE_FORMAT(NOW(), '%Y年-%m月-%d日 %H时:%i分:%s秒');

二、信息函数

1、CONNECTION_ID(): 连接的ID(线程的ID)

2、DATABASE(): 查看当前数据库 SELECT DATABASE();

3、LAST_INSERT_ID(): 最后插入记录

4、USER(): 当前用户

5、VERSION(): 版本信息

三、聚合函数

1、AVG(): 平均值的计算

SELECT AVG(fruit_price) FROM fruit; // 将水果的价格字段平均值返回

2、COUNT(): 返回条件的行数

SELECT COUNT(列名) FROM 表名; // 返回数目(NULL 不计)

SELECT COUNT(*) FROM 表名; // 返回所有的记录数

3、MAX(): 最大值

4、MIN(): 最小值

5、SUM(): 返回和

四、加密函数

1、MD5(): 信息摘要算法

MD5('adf234sdf2=-a1%af123#$'); // 16f7709af2898dd3c9f116d56ea93c82

2、PASSWORD(): 密码算法

PASSWORD('adf234sdf2=-a1%af123#$'); // *6D3B0C079BFF7DAD315D9AF131F0868939A74A8D

自定义函数一、创建自定函数:

CREATE FUNCTION 函数名 RETURNS {STRING | INTERGER | REAL | DECIMAL} routine_body

创建 函数 名 返回 类型 函数体

函数体: 由合法的SQL语句构成，可以是简单的SELECT 或 INSERT语句

示例1 无参数:

// 创建一个返回当前格式化的日期

CREATE FUNCTION newDate() RETURNS VARCHAR(30)

RETURN DATE_FORMAT(NOW(), '%Y年-%m月-%d日 %H时:%i分:%s秒');

// 调用

SELECT newDate(); // 2016年-12月-12日 11时:31分:55秒

示例2 有参数:

CREATE FUNCTION fun2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)

RETURNS FLOAT(10,2) UNSIGNED

RETURN (num1+num2)/2;

SELECT fun2(20, 30); // 25.00

示例3 返回语句

# 需要修改结束符 “;”

// 定义一个向users表中的username字段插入值

DELIMITER //;

CREATE FUNCTION addUser(username VARCHAR(20))

RETURNS INT UNSIGNED

BEGIN

INSERT users(username) VALUES(username);

RETURN LAST_INSERT_ID();

END//

二、删除自定义函数: DROP FUNCTION 函数名;

ROW_COUNT(): 用于返回被 update, insert, delete 实际修改的行数

FOUND_ROWS(): 返回上一条执行语句，影响的行数

数据类型一、int: 整型

二、浮点

1、float: 单精度

2、double: 双精度浮点

三、日期

1、datetime: 日期

2、timestamp: 日期

3、year、month、date

四、字符型

1、char: 定长字符类型

2、varchar: 不定长字符

3、tinytext:

4、text:

5、medinmtext:

6、longtext:

7、enum:

8、set:

操作符一、算术运算符

+ 加 SET var1=2+2; 4

- 减 SET var2=3-2; 1

* 乘 SET var3=3*2; 6

/ 除 SET var4=10/3; 3.3333

DIV 整除 SET var5=10 DIV 3; 3

% 取模 SET var6=10%3 ; 1

二、比较运算符

> 大于 1>2 False

< 小于 2<1 False

<= 小于等于 2<=2 True

>= 大于等于 3>=2 True

BETWEEN 在两值之间 5 BETWEEN 1 AND 10 True

NOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 False

IN 在集合中 5 IN (1,2,3,4) False

NOT IN 不在集合中 5 NOT IN (1,2,3,4) True

= 等于 2=3 False

<>, != 不等于 2<>3 False

<=> 严格比较两个NULL值是否相等 NULL<=>NULL True

LIKE 简单模式匹配 "Guy Harrison" LIKE "Guy%" True

REGEXP 正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False

IS NULL 为空 0 IS NULL False

IS NOT NULL 不为空 0 IS NOT NULL True

三、逻辑运算符

与(AND) && 或(OR)|| 非(NOT)！

四、位运算符

| 位或

& 位与

<< 左移位

>> 右移位

~ 位非(单目运算，按位取反)

属性1、AUTO_INCREMENT: 自动编号，必须与主键组合使用，起始值为1，每次增加1

2、primary key 主键约束 每张表只能存在一个主键，主键保证记录的唯一性, 主键自动为 NOT NULL

3、foreign 外键约束 保持数据的一致性, 实现一对一或一对多的关系约束

约束2、PRIMARY KEY(主键约束): 每张表只能存在一个主键，保证记录的唯一性，主键自动为NOT NULL

3、UNIQUE KEY(唯一约束): 可以保证记录的唯一性，字段可以为空值(NULL)，每张数据表可以存在多个唯一约束

4、DEFAULT(默认约束): 默认值，插入记录时没有赋值，自动赋予默认值

创建表时的性别字段，有三个值1，2，3 => 男，女，不男不女 如果insert插入未写入值，默认加入3

sex ENUM('1', '2', '3') DEFAULT '3'

5、FOREIGN KEY(外键约束): 保证数据一致性和完整性，实现一对一 或 一对多关系

5、NOT NULL(非空约束) 不能为空，必须写值要不报错

6、NULL 可以为空

# 约束分为表级约束 和 列级约束

约束只针对一个字段来约束，叫列级约束

约束针对两个或以上字段来约束，叫表级约束

# 外键约束的要求

1、父表和子表必须使用相同的存储引擎，禁止使用临时表

2、数据表存储只能为InnoDB

3、外键列和参照必须有相似的数据类型，

CURRENT_TIMESTAMP

—————————– 存储过程 —————————–

MySQL命令执行过程SQL命令 -> MySQL引擎 -> 语法正确 -> 可识别命令 -> 执行结果 -> 返回客户端

需要这一套流程才能跑完，如果使用存储过程会节省中间的一些步骤，可以节省性能.

什么是存储过程存储过程: 是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理

存储过程存储在数据库内，可以通过应用程序调用和输出

存储过程的优点:

1、增强SQL语句的功能和灵活性

2、实现较快的执行速度

存储过程是不需要语法解析等操作，预编译，所以速度上要快

3、减少网络流量

后端应用程序与数据库通信，需要传SQL语句，如果有存储过程就直接告诉执行哪个存储过程就可以了

存储过程的使用一、创建存储过程

CREATE

[DEFINER = {user | CURRENT_USER}] // 如果省略就是默认走的当前登录客户端的用户

PROCEDURE 存储过程名(参数)

参数：

1) IN: 表示该参数值必须在调用存储过程时指定，这个值不能返回只能进不能出

2)OUT: 表示该参数值可以审美观点存储过程改变，并且可以返回，

3)INOUT: 表示该参数调用时指定，并且可以被改变和返回

过程体

1)由合法的SQL语句构成

2)可以是做任意的SQL语句

3)复合结构则使用BEGIN...END语句，与函数相同，如果执行两个或两个以上语句需要使用BEGIN...END

4)复合结构可以包含声明、循环、控制结构

示例1: 无参数

// 定义一个返回客户端信息的存储过程

CREATE PROCEDURE sp1() SELEECT VERSION();

// 调用存储过程

CALL sp1(); // 如果没有参数可以去掉小括号 CALL sp1;

示例2: 带参数 IN

DELIMITER //

CREATE PROCEDURE removeUserId(IN uid INT UNSIGNED)

BEGIN

DELETE FROM users WHERE id=uid; // 通过传入的id来删除users表中的一条记录

END //

// 调用存储过程

CALL removeUserId(5);

示例3: OUT

DELIMITER ;;

CREATE PROCEDURE removeIdAndReturnUserCount(IN pid INT UNSIGNED, OUT userCount INT UNSIGNED) // OUT 定义输出的变量

BEGIN

DELETE FROM users WHERE id = pid; // 删除指定的记录

SELECT count(id) FROM users INTO userCount; // 查询id字段所有的记录数，并返回userCount变量中，注意结果返回使用INTO

END;;

// 调用

CALL removeIdAndReturnUserCount(3, @count); // 执行删除并返回记录数，存储在count变量中

SELECT @count; // 显示变量的值

二、删除存储过程: DROP PROCEDURE 存储过程名;

常用概念一、什么是存储引擎

MySQL可以将数据以不同的技术存储在文件(内存)中，这种技术就称为存储引擎。

每种存储引擎使用不同的存储机制、索引技术、锁定水平，最终提供广泛且不同的功能。

二、并发控制

当多个连接对记录进行修改时保证数据的一致性和完整性

例: 一个用户删除id=22的操作，另一个用户要读取id=22的操作，这样读取id=22就会报错

解决这个问题就是使用并发，当迸发读或写时系统会使用锁系统，来控制读写

1、锁

共享锁(读锁): 在同一时间段内，多个用户读取同一个资源，读取过程中数据不会发生任何变化.

排他锁(写锁): 在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其它的读锁或写锁操作.

2、锁颗粒

表锁: 开锁最小的锁策略

行锁: 开锁最大的锁策略

三、事务

事务的主要作用就是保证数据的完整性

四、外键

保证数据一致性的策略

五、索引

是对数据表中一列或多列进行排序的一种结构

Navicat工具一、导入、导出表数据

1、导出，选择要导出的表右键 Dump SQL File

2、导入，选中Table右键 import Wizard...

| 使用工具

| Navicat