MySQL基础

MySQL安装配置

文件压缩版本需要到bin目录下双击MySQLInstanceConfig.exe文件，进行安装配置。

安装完成配置PATH路径：MySQL安装的bin目录；

然后配置my-default.ini或者my.ini文件：

basedir=C:\Program Files\MySQL\MySQL Server 5.6（mysql所在目录） 
datadir=C:\Program Files\MySQL\MySQL Server 5.6\data （mysql所在目录\data） 如果需要改变数据存储路径，这个一定要配置

	my.ini文件还要注意以下几个配置：
		[client]
		port：端口号
		default-character-set=utf8 默认字符编码（MySQL没有utf-8，只有utf8）
		[mysqld]
		character-set-server=utf8
		注：MySQL5.5版本mysqld下应该添加：default-character-set=utf8
		
安装版本，数据存储位置默认是：ProgramData目录下，配置数据存储路径要在这个目录下配置

压缩版本要注册MySQL到服务中，配置完成到bin目录下打开DOS输入：
mysqld --install;
卸载服务：
mysqld --remove;

RDBMS 术语

数据库: 数据库是一些关联表的集合。

数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。

行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。

冗余：存储两倍数据，冗余可以使系统速度更快。

主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

外键：外键用于关联两个表。

复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL常用参数及功能：

-D database 打开指定数据库
-h host 服务器名称
-p（小写） password 密码
-P（大写） port 端口号
-u user 用户名
-V version 版本信息

修改root密码

mysqladmin -u root -p password 新密码

use mysql;

update user set authentication_string = password("*******") where user="root";
·修改用户密码只有这种方法可用，MySQL5.7之后密码加密到authentication字段中
查询user表：
select * from user\G；
其中\G按格式输出

退出MySQL三种方法：

exit;

quit;

\q;  

MySQL清屏

\c;

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;

CREATE DATABASE mysql_test CHARACTER SET gbk;
·创建时通过 character set gbk 将数据库字符编码指定为 gbk

IF NOT EXISTS 和IF EXTSTS

如果存在，语句就相当于执行insert into select；

如果不存在，则相当于create table … select。

选择所要操作的数据库

mysql -Dmysql_test -uroot -p
·注意：这里要大写的D
或者
mysql -uroot -p
进入数据管理系统以后：
USE mysql_test;
·USE命令可以不加分号

查看数据库的编码方式

SHOW CREATE DATABASE 数据库名;

修改数据库的编码方式

ALTER DATABASE 数据库名 CHARACTER SET utf8;
上面命令将数据库编码改为utf8

删除数据库

DROP DATABASE 数据库名;
删除已经删除了的数据库会报错，可以加上IF EXTSTS,就不会报错：
DROP DATABASE IF EXISTS 数据库名; 

MySQL常见的数据类型

大致分为四种：数值型、浮点型、日期/时间和字符串（字符）类型：
数值类型包括严格数值数据类型(整数型)(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(浮点型)(FLOAT、REAL和DOUBLE PRECISION)
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET
　	ENUM是枚举类型 ENUM {"value1", "value2" ...}
　　SET是集合类型不同于ENUM类型，它是一个排列组合。假如有abc，它可以选择a或b或c，也有选择是ab,ac,bc，也可以选择abc。 SET {"value1", "value2" ...}

打开及验证数据库

打开数据库：
USE 数据库名；

验证数据库：
SELECT DATABASE();

创建数据库表

CREATE TABLE 表名称(列声明);

CREATE TABLE [IF NOT EXISTS] students
	（  //注意这里是小括号
		id int unsigned not null auto_increment primary key, //auto_increment必须和主键一起使用，但是这并不代表主键一定要和auto_increment一起使用主键可以写成PRIMARY KEY，也可以写成KEY。
		name char(8) not null,
		sex char(4) not null,
		age tinyint unsigned not null,
		tel char(13) null default "-"
	);
·"id" 为列的名称;
·"int" 指定该列的类型为 int(取值范围为 -8388608到8388607), 在后面我们又用 "unsigned" 加以修饰, 表示该类型为无符号型, 此时该列的取值范围为 0到16777215;
·"not null" 说明该列的值不能为空, 必须要填, 如果不指定该属性, 默认可为空;
·"auto_increment" 需在整数列中使用, 其作用是在插入数据时若该列为 NULL, MySQL将自动产生一个比现存值更大的唯一标识符值。在每张表中仅能有一个这样的值且所在列必须为索引列。
·"primary key" 表示该列是表的主键, 本列的值必须唯一, MySQL将自动索引该列。
·char(8) 表示存储的字符长度为8, tinyint的取值范围为 -127到128, default 属性指定当该列值为空时的默认值

查看数据表列表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

查看当前数据库表：
SHOW TABLES;

查看数据表结构的语法

SHOW COLUMNS FROM table_name;

查找记录

SELECT * FROM 数据库表; 

按特定条件查询

select * from students where age < 20 limit 1;
·查询age小于20的所有行，并打印第一行。
·查询名字中带有 "王" 字的所有人信息: select * from students where name like "%王%";
·查询id小于5且年龄大于20的所有人信息: select * from students where id<5 and age>20;

更新表中的数据

UPDATE 表名称 SET 列名称=新值 WHERE 更新条件;

UPDATE students SET name='hanmeimei' WHERE name='lilei'; 
UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...]  [WHERE where_definition] 

删除表中数据

delete from 表名称 where 删除条件;
	注意：如果删除了最后一行，最后一行的主键值是6，再插入一行数据，折行数据的主键值是7，不是6。

·删除表中所有数据：
delete from 表名称;

修改表

添加列：
alter table tb_name ADD [COLUMN] col_name cplumn_definition [FIRST | AFTER col_name];
alter table 表名 add 列名 列数据类型 [after 插入位置];
	添加多列：
	alter table tb_name ADD [COLUMN] (col_name column_definition, ...)//添加多列是，位置只能在所有列最后面
修改列：
alter table 表名 change 列名称 列新名称 新数据类型；
删除列：
alter table 表名 drop 列名称;
	删除多列：
	alter table 表名 drop 列1, drop 列2...;
	也可以在删除一行的同事添加一行，用逗号隔开：
	alter table 表名 drop 列1，ADD 列2;
重命名表： 
alter table 表名 rename 新表名；

删除整张表：

drop table 表名

删除整个数据库

drop database 数据库名；

CREATE TABLE IF NOT EXIST… SELECT的行为，先判断表是否存在

如果存在，语句就相当于执行insert into select；
如果不存在，则相当于create table … select。

唯一约束

1、唯一约束UNIQUE KEY
2、唯一约束可以保证记录的唯一性
3、唯一约束的字段可以为空值
4、每张数据表可以存在多个唯一约束

默认值

DEFAULT 默认值;

外键约束

要求：
	1、父表和子表必须使用相同的存储殷勤，而且禁止使用临时表；
	2、数据表的存储引擎只能为INNODB; 如果默认引擎不是INNODB，在mysql的my.ini配置文件中Default-storage-engine=INNODB
	3、外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同，而字符的长度则可以不同；
	4、外键列和参照列必须创建索引，如果外键列不存在索引的话，MySQL将自动创建索引。
FOREIGN KEY外键约束：
froeign key (pid) references provinces (id):外键表users，参照表province，即父表

外键约束的参照操作

CASCADE:从父表删除或更新且启动删除或更新子表中匹配的行
SET NULL:从父表删除或更新行，并设置子表中的外键为NULL，如果使用该选项，必须保证字表列没有指定NUT NULL
RESTRICT:拒绝对父表的删除或更新操作
NO ACTION:标准SQL关键字，在MySQL中RESTRICT相同。

添加约束

添加主键约束（只能一个）
ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name)
	CONSTRAINT是可以添加，也可以不添加。如果添加的话，表示为约束起名字的意思
添加唯一约束（可以多个）
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type](index_col_name,…)
添加外键约束（）
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]]FOREIGN KEY [index_name] (index_col_name,…)reference_definition
添加默认约束（）
ALTER TABLE tbl_name ALTER [COLUMN] col_name{SET DEFAULT literal | DROP DEFAULT}

删除约束

删除主键约束：
ALTER TABLE tbl_name DROP PRIMARY KEY
删除唯一约束：
ALTER TABLE tbl_name DROP ｛INDEX|KEY｝ index_name
删除外键约束：
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

修改列

修改列定义：
ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST | AFTER col_name]
修改列名称:
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_namecol_definition [FIRST | AFTER col_name]
修改数据表的名称:
	方法一：
	　　ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
	方法二：
	　　RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]…

插入记录

INSERT [INTO] tbl_name [(col_name,…)] {VALUES | VALUE}({expr | DEFAULT},…),(…),…

INSERT [into] 表名[(列名1， 列名2， 列名3...)] VALUES (值1， 值2， 值3...); 

如果是插入的数据包含表中所有的列：
INSERT 表名 VALUES(values, values,...);
也可以用default作为默认值，特别是主键自增：
INSERT 表名 VALUES(default,values, values,...);
注：插入字段以外，还可以插入表达式（数字表达式、字符表达式、函数表达式）

可以一次性插入多条记录，用逗号隔开：
INSERT [into] 表名[(列名1， 列名2， 列名3...)] VALUES (值1， 值2， 值3...)，(值1， 值2， 值3...)...; 

INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},… 
	insert student set id=..,name=..,...;

INSERT [INTO] tbl_name [(col_name,…)] SELECT…

更新表

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}]...[WHERE where_condition]
UPDATE table_reference(它指的是表的参照关系)但是这里指单个的某一张数据表，然后set设置某一列的值是表达式或默认值
	例如：把某个数值列所有行值加5--update user set age=age+5;
	也可以更新多列，列中间用逗号隔开：update user set age=age+5，sex=0,...;

删除表

DELETE FROM tab_name [WHERE where_condition]

查询

*星号表示所有列
使用as赋予别名

GROUP BY

查询结果分组:
[GROUP BY {col_name | position} [ASC | DESC],… ]
可以写字段名字之外，还可以写位置:
select sex from user group by 1;

分组条件

[HAVING where_condition]

对查询结果进行排序

[ORDER BY {col_name | expr | position} [ASC | DESC],…]
ASC升序 DESC降序 默认升序
如果对多个列分别进行排序，则需要在每个列后面指定升序或降序排列

限制查询结果返回的数量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

select记录从0开始编号，如果如果limit 3， 5 ，实际是找到第四行开始，找5行记录

插入查询的数据

insert test (username) select username from user where age>=30;

子查询

子查询是指在另一个查询语句中的SELECT子句
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
其中，SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement),SELECT column1 FROM t2 称为Sub Query[子查询]。
子查询是嵌套在外查询内部
行级子查询
　　SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
　　SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

SET NAMES gbk;意思是在客户端以gbk的方式显示我们的数据，只是影响客户端的数据，并不影响数据表里的真实数据。

常见的子查询

什么是子查询？
（1）子查询（Subquery）是指出现在其他SQL语句内的SELECT子句
　　　　例如:SELECT * FROM t1 WHERE col1=(SELECT col2 FROM
　　　　t2);其中SELECT * FROM t1,称为Outer Query/Outer
　　　　Statement ,SELECT col2 FROM t2,称之为SubQuery
（2）子查询指嵌套在查询内部，且必须始终出现在圆括号内。
（3）子查询可以包含多个关键字或条件，比如DISTINCT、GROUP BY、ORDER BY、LIMIT以及相关的函数等。
（4）子查询的外层查询可以是SELECT,INSERT,UPDATE,SET或DO.
（5）子查询的返回值：可以返回标量、一行、一列或子查询

常见的比较运算符

=、 >、 <、 >=、 <= 、 <>、 !=、 <=>

AVG()聚合函数，只有一个返回值，作用是求平均值
round()四舍五入
由于列子查询返回的结果集是 N 行一列，因此不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。在列子查询中可以使用 IN、ANY、SOME 和 ALL 操作符：
    IN：在指定项内，同 IN(项1,项2,…)。
    ANY：与比较操作符联合使用，表示与子查询返回的任何值比较为 TRUE ，则返回 TRUE 。
    SOME：ANY 的别名，较少使用。
    ALL：与比较操作符联合使用，表示与子查询返回的所有值比较都为 TRUE ，则返回 TRUE 。

使用[NOT] IN 的子查询

operand comparison_operator [NOT] IN (subquery)
=ANY 运算符与IN等效
!=ALL或<>ALL运算与NOT IN 等效

使用insert...select语法插入记录

INSERT…SELECT
INSERT…VALUES

多表更新

UPDATE table_references
SET col_name1={expr1 | DEFAULT}
[,col_name2={expr2 | DEFAULT}] …
[WHERE where_condition]

表的参照关系的语法结构

table_reference
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

连接类型

INNER JOIN,内连接
在MySQL中，JOIN,CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN,左外连接
RIGHT [OUTER] JOIN,右外连接
	UPDATE   tdb_goods  INNER   JOIN  tdb_goods_ cates   ON   goods_cate =  cate_name   SET  goods_cate =  cate_id;

链接

MySQL的SELECT语句、多表更新、多表删除语句中支持JOIN操作

使用ON关键字来设定连接条件，也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件，
使用WHERE关键字来进行结果集记录的过滤。

多表链接

表的连接实际上就是外键约束的逆向操作

多表删除的语法结构

DELETE tbl_name[.*] [,tbl_name[.*]]…FROM table_references [WHERE where_condition]

字符函数


Concat_ws指的是使用指定的分割符进行字符的连接，至少需要三个参数，第一个参数指定分隔符，后面的参数表示要连接的字符，至少要两个，所以总共至少要有三个参数，我们发现abc三个字符之间用我们指定的@分隔符连接起来。





MySQL自定义函数
自定义函数条件：参数和返回值
自定义函数语法结构

CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body
*RETURNS后面跟上返回值类型，routine_body指函数体

关于函数体的几点说明
（1）函数体由合法的SQL语句构成；
（2）函数体可以是简单的SELECT或INSERT语句；
（3）函数体如果为复合结构则使用BEGIN....END语句；
（4）复合结构可以包含声明、循环、控制语句；
存储过程的优点

第一，增强了sql语句的功能和灵活性。因为在存储过程中可以写控制语句，那么就有很强的灵活性，也可以完成复杂的判断和运算。
第二，实现了较快的执行速度。如果某一个操作包含了大量的操作语句，那么这些语句都将被mysql引擎进行语法分析和编译，所以执行过程的效率相对较低，而存储过程是预编译的，当客户端第一次调用这个存储过程的时候，mysql引擎将对他进行语法分析和编译操作，然后把这个编译的结果存储到内存当中，所以说第一次是和之前的效率是相同的，但是以后客户端再次调用这个存储过程的时候，便直接在内存当中来执行，所以说相比效率比较高，速度比较快。
第三，减少网络流量.如果我们通过客户端单独发送sql语句让服务器来执行的话，那么通过http协议提交的数据量相对较大，而使用存储过程，我们只需要传递存储过程的名字，后面加上我们要操作的那个值就行了，所以说，他提交给服务器的数据量相对较少，那么也就减少了网络流量。

创建存储过程

CREATE
[DEFINER={user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[IN | OUT | INOUT] param_name type

IN ：表示该参数的值必须在调用存储过程时指定,在存储过程中这个值是不能返回的，就是说只能进，不能出。
OUT ：表示该参数的值可以被存储过程改变，并且可以返回
out指的是输出。
INOUT ：表示该参数在调用时指定，并且可以被改变和返回

调用存储过程

CALL sp_name([parameter[,...]])
CALL sp_name[( )]

MySQL正则表达式

p1|p2|p3  匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
* 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+ 	匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n} n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m} 	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

查找name字段中以'st'为开头的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'为结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找name字段中包含'mar'字符串的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';