mysql学习笔记

一.mysql概述。
1.mysql。MySQL是最流行的关系型数据库管理系统，在web应用方面MySQL是最好的RDBMS应用软件之一。

2.什么是数据库？
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或者多个不同的API用于创建，访问，管理，搜索和复制索堡村的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以现在我们使用关系型数据库管理系统（RDBMS）
来存储和管理大数据量。

3.关系型数据库管，是建立在关系型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
特点：

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

4.RDBMS术语
数据库：一些关联表的集合。
数据表：数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
行：一行（元组，或记录）是一组相关的数据。
列：一列包含了相同类型的数据，例如邮政编码的数据。
冗余：存储两倍数据，冗余降低了性能，但是提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。可以使用主键来查询数据。
外键：用于关联两个表。
复合键：复合键将多个列作为一个索引键，一般用于符合索引。
索引：使用索引可以快速访问数据库表中的特定信息。索引是对数据库表中一列或者多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性：参照完整性要求关系中不允许引用不存在的实体。
Mysql为关系型数据库，这种关系型可以理解为表格的概念，一个关系型数据库由一个或者多个表格组成。
表头（header）：每一列的名称。
列（col）：具有相同数据类型的数据的集合。
行（row）：每一行用来描述某条记录的相同信息。
值（value）：行的具体信息，每个值必须与该列的数据类型相同。
键（key）：键的值在当前列中具有唯一性。

5.启动musql：net start mysql

6.登录mysql：mysql -h 主机名 -u 用户名 -p
-h,指定客户端所要登录的MySQL主机名，登录本机（localhost或127.0.0.1）该参数可以省略；
-u：登录的用户名；
-p：登录密码，如果为空，可以省略。

7.添加MySQL用户，只需在名为MySQL的数据库中的user表中添加一条用户记录即可。
mysql -u root -p

use MYSQL;
INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES (‘localhost’, ‘guest’,
PASSWORD(‘guest123’), ‘Y’, ‘Y’, ‘Y’);
FLUSH PRIVILEGES;

另外一种添加用户的方法为通过SQL的 GRANT 命令，以下命令会给指定数据库TUTORIALS添加用户 zara ，密码为 zara123 。
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO ‘zara’@‘localhost’
-> IDENTIFIED BY ‘zara123’;

8.管理MySQL的命令。

use 数据库名：选择要操作的数据库名。
show DATABASES;列出当前数据库管理系统的所有数据库。
show tables;列出当前数据库的所有表；
desc 表名：显示表结构；
show columns from 表名;
show index from 表名；显示数据表的详细索引信息，包括主键。
show table status[from db_name][like ‘pattern’]\G;输出数据库管理系统的性能和统计信息。\G结果按列打印。

9.创建数据库。create database db_name;
10.删除数据库。

drop database db_name;

11.选择数据库。

use db_name;

12.mysql中所有的database，table，列，字段名都是区分大小写。

13.MySQL数据类型。
大致分为三种：数值，日期/时间，字符串。

1. 数值类型
   关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
   BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
   

2. 日期和时间类型
   

3. 字符串类型
   

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

14.创建数据表。

create table tableName(colName colType(size)[NOT NULL])

以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析：

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。

15.删除数据表。
格式：

drop table tableName;

16.插入数据。
格式：

insert into tableName (colName,colName2,...)values(val1,val2,...);

如果数据是字符串，必须用引号。

17.查询

以下为在MySQL数据库中查询数据通用的 SELECT 语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

• 可以查询多张表，用逗号隔开
• where查询条件
• limit设置返回记录数
• offset设置偏移量，默认为0

17.where子句

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• 使用主键查询速度较快
• 可以用在查询，删除，修改语句中
• 条件之间使用and，or连接

18.更新

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause];

19.删除

DELETE FROM table_name [WHERE Clause];

20.like语句

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• 百分号 %字符来表示任意字符
• 可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件

21.union操作符
讲多个结果集拼接到一个里面。

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

• 默认distinct，去重
• 所有结果拼接，使用all

22.排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [默认 ASC],...

• 默认asc，升序
• 可设置多个排序字段，用逗号隔开

23.GROUP BY 语句

根据一个或多个列对结果分组。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

24.连接join

可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

25.null值处理

• IS NULL
• IS NOT NULL
• <=> 相等或者都为null，返回true
• 不能使用=null，！=null查找null值
• 任何值和null=判断，返回都是null

26.正则表达式

使用REGEXP操作符模糊匹配。

例子：
查找name字段中以’st’为开头的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以’ok’为结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含’mar’字符串的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

27.MySQL事务

1.事务用于处理操作数量大，复杂度高的数据。级联删除就是个事务场景。

• 使用Innodb的数据库才可以处理事务
• 用来维护数据完整性，成批sql要么都执行，要么都不执行。

2.事务必须满足的条件ACID：原子性（atomicity,不可分割性），一致性（consistency），隔离性（isolation），持久性（durability）。

• 原子性：一个事务中的所有操作要么都完成，要么没开始。一旦有一个没完成，回滚到开始状态。
• 一致性：事务开始前和结束后，数据库完整性没有破坏。
• 隔离性：允许多个并发事务同时对数据进行读写和修改的能力，保证数据一致性。隔离级别：读未提交（read uncommitted），读提交（read committed），可重复读（repeatable read），串行化（serialized）。
• 持久性：对数据的修改是永久的。

3.在MySQL命令行默认开启自动提交，即执行完sql语句后，自动commit。可以通过begin /start transaction开启事务，SET AUTOCOMMIT=0,关闭自动提交。
事务控制语句：

• BEGIN/START TRANSACTION 显式开始事务；

• COMMIT[WORK],使对数据库的修改成为永久；

• ROLLBACK[WORK],回滚会结束事务，撤销未提交的修改；

• SAVEPOINT id，允许在事务中创建保存点；

• RELEASE SAVEPOINT id，删除保存点，当不存在时报异常。

• ROLLBACK TO id，返回到保存点；

• SET TRANSACTION设置事务级别。
  4.事务处理的办法：

• beigin，commit,rollback；

• set autocommit=0,禁止自动提交；set autocommit=1,允许自动提交。

28.MySQL alter命令：修改表名，表字段。

1. 删除，添加，修改表字段

create table testalter_tbl
    -> (
    -> i INT,
    -> c CHAR(1)
    -> );
ALTER TABLE testalter_tbl  DROP i;
ALTER TABLE testalter_tbl ADD i INT;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

• 关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）
• drop删除字段，add添加字段

2. 修改字段类型和名称（change、modify子句）

ALTER TABLE testalter_tbl MODIFY c CHAR(10);
ALTER TABLE testalter_tbl CHANGE i j BIGINT;
ALTER TABLE testalter_tbl CHANGE j j INT;

3. alter对null值和默认值的影响：当你修改字段时，你可以指定是否包含值或者是否设置默认值。

指定字段 j 为 NOT NULL 且默认值为100

ALTER TABLE testalter_tbl
MODIFY j BIGINT NOT NULL DEFAULT 100;

4. 操作字段默认值。

 ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;//修改默认值
 ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;//使用 ALTER 命令及 DROP子句来删除字段的默认值
 ALTER TABLE testalter_tbl ENGINE = MYISAM;//修改表类型
 SHOW TABLE STATUS LIKE 'testalter_tbl'\G;//查看数据表类型可以使用 SHOW TABLE STATUS 语句

5. 修改表名。rename子句

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

29.索引

1. 提高查询速度；
2. 单列索引和多列索引；
3. 创建索引时确保它是查询条件；
4. 索引也是一张表，保存了主键和索引字段；
5. 使用索引会降低更新表的速度，因为更新表同时添加索引。

知识点

1. 普通索引

CREATE INDEX indexName ON mytable(username(length));//直接创建索引

ALTER table tableName ADD INDEX indexName(columnName);//修改表结构的方式添加索引

CREATE TABLE mytable(  
ID INT NOT NULL,    
username VARCHAR(16) NOT NULL,   
INDEX [indexName] (username(length))   
); //创建表时添加索引

DROP INDEX [indexName] ON mytable;//删除索引

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

2. 唯一索引

   索引列的值必须唯一，但允许有空值。

CREATE UNIQUE INDEX indexName ON mytable(username(length));

ALTER table mytable ADD UNIQUE [indexName] (username(length));

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);  

3. 使用ALTER 命令添加和删除索引

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);//该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);//这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
ALTER TABLE tbl_name ADD INDEX index_name (column_list);//添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);//该语句指定了索引为 FULLTEXT ，用于全文索引

ALTER TABLE testalter_tbl ADD INDEX (c);
ALTER TABLE testalter_tbl DROP INDEX c;

4. 使用 ALTER 命令添加和删除主键

 ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
 ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

 ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

5. 显示索引信息

SHOW INDEX FROM table_name[\G];

30.临时表

1. 临时表只在当前连接可见，断开数据库连接是自动销毁；
2. show tables不会出现在列表中；
3. 手动删除临时表，drop table tableName。

31.复制表
步骤

1. show create table tableName /G;得到建表语句
2. 修改表名，创建表；
3. 复制表内容，insert into …select
   
   
   

其他方式：

1. 只复制表结构到新表
   create table 新表 select * from 旧表 where 1=2或者
   create table 新表 like 旧表 ；
2. 复制表结构及数据到新表
   create table新表 select * from 旧表

CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;//完整复制表

CREATE TABLE newadmin AS(
    SELECT username, password FROM admin
);//拷贝部分字段

CREATE TABLE newadmin AS(  
    SELECT id, username AS uname, password AS pass FROM admin
);//新表字段改名

CREATE TABLE newadmin AS(
    SELECT * FROM admin WHERE LEFT(username,1) = 's'
)//拷贝一部分数据

CREATE TABLE newadmin(
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)AS(SELECT * FROM admin)  //创建表的同时定义表中的字段信息

32.元数据

33.序列

1. 用途。
   一个表只允许有一个自增字段，要想多个字段自增，就得使用序列。序列一一列整数：1,2,3…

   MySQL 每张表只能有1个自动增长字段，这个自动增长字段即可作为主键，也可以用作非主键使用，但是请注意将自动增长字段当做非主键使用时必须必须为其添加唯一索引，否则系统将会报错。

1)将自动增长字段设置为主键

create table t1 (id int auto_increment Primary key,sid int);

2)将自动增长字段设置为非主键，注意必须显式添加Unique键.

create table t2 (sid int primary key,id int auto_increment Unique);

3)将自动增长字段设置为非主键如果未添加唯一索引将会报错，如下面语句

create table t3 (sid int primary key,id int auto_increment);

2. 使用auto_increment,就使用序列了；
3. 获取auto_increment，LAST_INSERT_ID( ) 函数；
4. 重置序列；
   如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：

ALTER TABLE insect DROP id;
ALTER TABLE insect
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

5. 设置序列初始值。

CREATE TABLE insect(//建表的时候设置
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, 
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

ALTER TABLE t AUTO_INCREMENT = 100;//建表完成之后设置

34.处理重复数据

1. 防止表中出现重复数据
   1.1 可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
   表中没有主键也没有索引，那么表就允许数据重复。
   1.2 insert into 和insert ignore into区别：后者在发现数据重复的情况下，跳过；前者会在数据库不允许重复数据情况下报错。
   replace into：如果有主键或者索引，插入数据重复的时候，先删除原来的，再插入后来的。
2. 统计重复数据
   2.1 删除主键的步骤：
   1)删除auto_increment
   2)删除pk

alter table user change id id int;//如果主键是自增，首先	auto_INCREMENT去掉
alter table user drop primary key;//主键去掉

2.2 删除索引

alter table user drop index indexName;

drop index[indexName] on tableName;

3. 过滤重复数据
   

select distinct id,username from user;//distinct去重
select id,username from user group by id,username;//分组去重

4. 删除重复数据

//分组的方式找到不重复的数据，放到临时表里；原表删除；临时表改名为原表名。
create table tmp select * from user group by id,username;
drop table user;
alter table tmp rename to user;
//增加主键的方式删除不了重复数据，会报错。

35.SQL注入

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

36.导出数据

在cmd输入下面格式命令

导出整个数据库，格式：mysqldump -u root -p databaseName>sqlFilename.sql

导出一张表，格式：mysqldump -u 数据库账号 -p databaseName tableName > fileName.sql；

导出数据库结构，格式：mysqldump -u root -p -d --add-drop-table databaseName>destinationPath+sqlFileName.sql;

37.导入数据
进入mysql控制台，mysql -u root -p;
use databaseName;
source path+sqlFile.sql;
mysql -u root -D databaseName;//导入数据库
mysql -u root -D databaseName tableName;导入数据库中的表
38.函数

39.运算符

1. 算数运算符
   

2. 逻辑运算符
   

3. 比较运算符
   
   <=>:两边都是null，结果为1；否则为0；
   正则式。

4. 位运算符