Mysql知识点总结

一、Mysql数据库介绍

1、MySQL 是最流行的RDBMS(Relational Database Management System：关系数据库管理系统)

2、数据库：数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

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

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

3、数据库的三大范式

1）第一范式1NF：数据表中的所有字段都是不可分割的原子值

2）第二范式2NF：满足第一范式，除主键外每一列完全依赖于主键

3）第三范式3NF：满足第二范式，除主键外其他列之间不能有传递依赖关系

二、数据库的相关操作

1、创建数据库

1）使用create
[root@host]# mysql -u root -p   
Enter password:******  # 登录后进入终端
mysql> create DATABASE 数据库名;

2）使用mysqladmin 

[root@host]# mysqladmin -u root -p create 数据库名
Enter password:******

2、删除数据库

1）drop

drop database 数据库名；

2）mysqladmin

[root@host]# mysqladmin -u root -p drop 数据库名
Enter password:******

3、选择数据库

[root@host]# mysql -u root -p
Enter password:******
mysql> use 数据库名;
Database changed

三、数据类型

1、数值类型

类型	大小	范围（有符号）	用途
TINYINT	1 byte	(-128，127)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	小数值

2、日期和时间类型

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性

类型	大小 ( bytes)	格式	用途
DATE	3	YYYY-MM-DD	日期值
TIME	3	HH:MM:SS	时间值或持续时间
YEAR	1	YYYY	年份值
DATETIME	8	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

3、字符串类型

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

四、数据表相关的操作

1、创建数据表

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;

• 设置字段的属性为 NOT NULL，为空则报错。
• AUTO_INCREMENT定义列为自增的属性，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 
• ENGINE 设置存储引擎，CHARSET 设置编码。

   

 mysql的键表约束：

1）主键约束：primary key

   值不能为空且唯一

   语法：primary key（字段名1，字段名2）

2）自增约束：auto_increment

  语法：字段名  字段类型  auto_increment

3)唯一约束：unique

约束修饰的字段值不可以重复

4）非空约束：not null

约束修饰的字段不能为空

5）外键约束：foreign key

涉及两个表，父表（主）和子（副）表

父表中的数值被子表引用，不能被删除

语法：foreign key（子表字段） references（父表字段）

2、删除数据表

DROP TABLE 数据表 ;

3、插入数据

INSERT INTO 表名( 字段名1, 字段名2,...字段名n)
                       VALUES
                       ( 值1, 值2,...值n);

4、查询数据

SELECT 字段名1，字段名2
FROM 表名
WHERE Clause
LIMIT （起始位置0，查询个数）；

 1）执行顺序：from、where、group by、having（对where和group by查询出来的分组进行过滤）、select、distinct、union（连接两个以上的表组合到一个结果集中）、order by、limitand offset

2） Like代替=

通常与%一同使用

3） ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。

4）GROUP BY：根据一个或多个列对结果集进行分组

5）连接：①内连接：inner join或join

     语法：select * from 表1 inner  join 表2 on 表1字段=表2字段

②外连接：左连接：left join 或 left outer join  左表所有数据取出，而右表中若无就补null

                  右连级：right join 后 right outer join  反之

 5、更新数据

UPDATE 表名 SET 字段名=字段值, 字段名=字段值
WHERE Clause

 alert命令：

⑴删除：ALTER TABLE 表名 DROP 字段名 ;

⑵添加：ALTER TABLE 表名 ADD 字段名 ;

⑶修改：ALTER TABLE 表名 MODIFT 字段名 ;

⑷使用CHANGE子句： ALTER TABLE 表名 CHANGE 修改字段名  修改后字段名 修改后字段类型;

⑸修改表名：ALTER TABLE 表名 RENAME TO 修改后表名;

⑹删除、添加索引

⑺删除、添加主键

6、删除数据

1）delete
DELETE FROM 表名 WHERE Clause

2）runcate

删除表中所有行，而不记录单个行删除操作。在这个指令执行后，表中的资料会完全消失，可表格本身还存在

语法：runcate table 表名；

五、Mysql事务

1、数据库操作语句就构成一个事务：

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

2、事务必须满足的4个条件：

• 原子性（Atomicity，或称不可分割性）：要么全部完成，要么全部失败
• 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。写入的资料必须完全符合所有的预设规则
• 隔离性（Isolation，又称独立性）：事务和事务之间具有隔离，防止事务并行执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别：读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

3、MYSQL 事务处理主要有两种方法：

1）用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2）直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交（默认的）
• SET AUTOCOMMIT=1 开启自动提交

六、Mysql索引

• 索引：单列索引、组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；组合索引，即一个索引包含多个列。

• 创建索引时，应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

• 索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

1、普通索引

⑴创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON table_name (column_name)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 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; 

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))  

);  

七、正则表达式

用于REGEXP操作

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。
[...]	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]	负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
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 次。

语法：

SELECT name FROM 表名 WHERE name REGEXP '^st';