1. MySQL数据类型
支持多种数据类型,大致分为3类:
- 数值
- 日期/时间
- 字符串(字符)
1.1 数值类型
主要包含:
- 严格的数值数据类型:integer,smallint、decimal、numberic;
- 近似数值数据类型:float、real、double precision;
关键字INT是INTEGER的同义词;
关键字DEC是DECIMAL的同义词。
作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。
1.2 日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
1.3 字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
Note
- char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
- CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
- BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
- BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
- 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
2. 表的增删查改操作
2.1 表操作
2.1.1 创建表
1.基本语法:
CREATE TABLE table_name (column_name column_type);
2.实例:
CREATE TABLE `people` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3;
3.说明:
- 如果字段为非空,可以设置字段的属性为
NOT NULL
, 在操作数据库时如果输入该字段的数据为NULL
,就会报错。 AUTO_INCREMENT
定义列为自增的属性,一般用于主键,数值会自动加1。PRIMARY KEY
关键字用于定义列为主键。 可以使用多列来定义主键,列间以逗号分隔。- ENGINE 设置存储引擎。
- CHARSET 设置编码。
4.查看表:
-
先进入表所在的数据库:
use 数据库名
-
显示表:
show 表名;
-
查看表的结构:
desc 表名;
2.1.2 删除表
1.基本语法:
DROP TABLE table_name ;
2.举例:
2.1.3 修改数据表
1.修改表名
如果需要修改数据表的名称,可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
2.修改字段类型及名称
-
在ALTER命令中使用 MODIFY 或 CHANGE 子句 :
ALTER TABLE test MODIFY name CHAR(100);
-
在 CHANGE 关键字之后,紧跟着的是你要修改的字段名,然后指定新字段名及类型:
ALTER TABLE test CHANGE name NewName VARCHAR(255);
3.删除,添加或修改表字段
- 使用了 ALTER 命令及 DROP 子句来删除创建表的字段:
ALTER TABLE test DROP NewName;
- 使用 ADD 子句来向数据表中添加列:
ALTER TABLE test ADD i INT;
3.修改字段默认值
-
使用 ALTER 来修改字段的默认值:
ALTER TABLE test ALTER name SET DEFAULT "默认值";
-
使用 ALTER 命令及 DROP子句来删除字段的默认值:
ALTER TABLE test ALTER name DROP DEFAULT ;
-
修改存储引擎:
mysql> SHOW TABLE STATUS ; +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | people | InnoDB | 10 | Dynamic | 8 | 2048 | 16384 | 0 | 0 | 0 | 8 | 2021-05-21 16:52:57 | NULL | NULL | utf8_general_ci | NULL | | | | test | MyISAM | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2021-06-02 22:13:14 | NULL | NULL | utf8_general_ci | NULL | | | +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE test ENGINE = MYISAM; Query OK, 6 rows affected (0.05 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SHOW TABLE STATUS ; +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | people | InnoDB | 10 | Dynamic | 8 | 2048 | 16384 | 0 | 0 | 0 | 8 | 2021-05-21 16:52:57 | NULL | NULL | utf8_general_ci | NULL | | | | test | MyISAM | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2021-06-02 22:13:37 | NULL | NULL | utf8_general_ci | NULL | | | +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 2 rows in set (0.00 sec)
-
删除外键约束:keyName是外键别名
alter table test drop foreign key id;
2.2 表数据
2.2.1 增
1.基本语法:
INSERT INTO table_name (field1, field2,...fieldN )
VALUES
(value1, value2,...valueN );
插入多条数据:
INSERT INTO table_name(field1, field2,...fieldN)
VALUES
(valueA1,valueA2,...valueAN),
(valueB1,valueB2,...valueBN),
(valueC1,valueC2,...valueCN)
......;
如果所有的列都要添加数据可以不规定列进行添加数据:
INSERT INTO table_name
VALUES
(valueA1,valueA2,...valueAN),
(valueC1,valueC2,...valueCN)
.....;
2.举例:
INSERT INTO people (name,sex,phone)
VALUES
("gsd","女","12345678974");
INSERT INTO people (name,sex,phone)
VALUES
("sdeas","女","12345678974"),
("ggs","女","12345678974");
3.说明:
- 如果数据是字符型,必须使用单引号或者双引号。
- 若设置了主键自增,则添加数据的时候可以不在设置主键,主键会自增。
- 若设置了主键自增,添加数据的时候同样也可以设置主键的数据。
- 如果添加过主键自增(PRINARY KEY AUTO_INCREMENT)第一列在增加数据的时候,可以写为0或者null,这样添加数据可以自增。
2.2.2 删
1.基本语法:
DELETE FROM table_name [WHERE Clause];
2.举例:
DELETE FROM test WHERE id=2;
3.说明:
- 如果没有指定 WHERE 子句,MySQL 表中的所有记录将被删除。
- 可以在 WHERE 子句中指定任何条件。
- 可以在单个表中一次性删除记录。
2.2.3 查
基本语法:
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
2.2.3.1 where关键字
WHERE 子句中操作符列表:
- 查看表中所有数据:
select * from table_name;
2.2.3.2 BINARY关键字
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
-
区分大小写:
select * from people WHERE BINARY name="YJ" GROUP BY id;
-
不区分大小写:
select * from people WHERE name="YJ" GROUP BY id;
2.2.3.3 LIMIT关键字
-
如果只给定一个参数n,表示检索前n条记录(1-n)。
select * from people LIMIT 6;
-
两个参数,第一个参数表示offset, 第二个参数为查询的总记录条数。
select * from people LIMIT 2,6;
2.2.3.4 LIKE
使用LIKE子句代替等号 =
。LIKE 通常与 % 一同使用,类似于一个元字符的搜索。可以使用 AND 或者 OR 指定一个或多个条件。
like 匹配/模糊匹配,会与 %
和 _
结合使用。
‘
%a
’ //以a结尾的数据
‘a%
’ //以a开头的数据
‘%a%
’ //含有a的数据
‘_a_
’ //三位且中间字母是a的
’_a'
//两位且结尾字母是a的
‘a_
’ //两位且开头字母是a的
在 where like 的条件查询中,SQL 提供了四种匹配方式。
%
:表示任意 0 个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。
_
:表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。
[]
:表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。
[^]
:表示不在括号所列之内的单个字符。其取值和 [] 相同,但它要求所匹配对象为指定字符以外的任一个字符。
查询内容包含通配符时,由于通配符的缘故,导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现,而把特殊字符用 “[ ]” 括起便可正常查询。
select * from people where name like "%g%";
2.2.3.5 UNION 操作符
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
UNION 语句:用于将不同表中相同列中查询的数据展示出来;(不包括重复数据)
UNION ALL 语句:用于将不同表中相同列中查询的数据展示出来;(包括重复数据)
使用形式如下:
SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称;
SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称;
举例:
select name from people
UNION
SELECT name from test;
people表格:
test表格:
查询结果:
2.2.3.6 ORDER BY
使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果。
可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
- 按增序排列查询
select * from people ORDER BY id asc;//默认为增序查询
- 按降序排列查询
select * from people ORDER BY id desc;
2.2.3.7 GROUP BY
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
SELECT name ,COUNT(*) FROM people GROUP BY name;
people原表:
查询结果:
2.2.3.8 JOIN
JOIN 按照功能大致分为如下三类:
-
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
SELECT a.id, a.name ,b.name FROM people a INNER JOIN test b WHERE a.id= b.id;
-
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
SELECT a.id, a.name ,b.name FROM people a LEFT JOIN test b ON a.id= b.id;
-
RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
SELECT a.id, a.name ,b.name FROM people a RIGHT JOIN test b ON a.id= b.id;
2.2.4 改
1.SQL 语法:
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
2.举例:
UPDATE test SET name="hace" WHERE id=3;
3. 事务
MySQL 事务主要用于处理操作量大,复杂度高的数据。
- 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
- 事务用来管理 insert,update,delete 语句
3.1 事务目的
事务在数据库系统中主要有两个目的:
-
提供一个可靠的工做单元,可以从失败中恢复到正确的状态。若是发生系统错误,执行操做语句中断(所有中断或部分中断)或者是许多操做没有完成或不知道其状态下,依旧保持数据库正常运行。
-
在程序访问数据库之间提供一个隔离。若是不提供这种隔离,程序可能返回不许确的结果。
3.2 事务特性
事务是必须满足4个条件(ACID):
- 原子性(Atomicity,或称不可分割性)
一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
- 一致性(Consistency)
在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性(Isolation,又称独立性)
数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
- 持久性(Durability)
事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
3.3 事务类型
-
自动提交的事务 (隐式开启,隐式提交)
每条单独的SQL语句都是一个事务, 语句执行完以后会自动执行commit
操做, 若是出现错误则进行事务回滚至以前的状态数。set session autocommit=0; # 0 是关闭, 1 是开启
-
显示声明的事务 (显式开启,显式提交)
明显的以BEGIN TRAN
开始,而且以ROLLBACK TRAN
或者是COMMIT TRAN
结尾的语句。start transaction 或者 begin 语句开始事务, commit 或者 rollback 语句结束事务
-
隐含事务 (隐式开启,显示提交)
在隐式事务中, 不须要使用begin
或者start transaction
来开启事务, 每一个 SQL 语句第一次执行会自动开启事务, 但须要使用commit
提交或者rollback
回滚来结束事务并发。mysql 中经过关闭自动提交事务, 从而来达到隐式开启事务, 显示提交事务的目的。
3.4 完整的事务生命周期
- 事务开始
- 执行数据库操做语句(一个或多个)
- 若是没有错误发生,提交事务而且事务结束
- 若是出现了错误,回滚到事务开始以前而且事务结束
3.5 事务控制语句
-
开启事务
begin; # 或者下面的语句 start transaction;
-
事务回滚(回滚到以前的状态,并关闭事务)
rollback; # 回滚 + 关闭
-
事务提交(将修改提交,并关闭事务)
commit; # 提交 + 关闭
举例:
-
事务提交
mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | ghd | | 2 | hh | | 3 | 66 | | 4 | ggs | | 5 | BDB | +----+------+ 5 rows in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.03 sec) mysql> insert into test(id,name) values(6,'Baby'); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.03 sec) mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | ghd | | 2 | hh | | 3 | 66 | | 4 | ggs | | 5 | BDB | | 6 | Baby | +----+------+ 6 rows in set (0.00 sec)
-
事务回滚
mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | ghd | | 2 | hh | | 3 | 66 | | 4 | ggs | | 5 | BDB | | 6 | Baby | +----+------+ 6 rows in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into test(id,name) values(7,'Sad'); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | ghd | | 2 | hh | | 3 | 66 | | 4 | ggs | | 5 | BDB | | 6 | Baby | | 7 | Sad | +----+------+ 7 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.03 sec) mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | ghd | | 2 | hh | | 3 | 66 | | 4 | ggs | | 5 | BDB | | 6 | Baby | +----+------+ 6 rows in set (0.00 sec)
3.6 事务保存点 (savepoint)
- 事务的保存点:savepoint 相似于虚拟机中的快照, 运用于事务中,每设置一个 savepoint 就是一个保存点,当事务结束时会自动删除定义的全部保存点,在事务没有结束以前能够回退到任意保存点。
- 设置和回滚保存点语法:
savepoint [保存点名字]; # 设置保存点 rollback to [某个保存点名字]; # 回滚到某个保存点,该保存点以后的操做都无效包括保存点 rollback; # 回滚开启事务后的全部操做,并删除全部保存点,并结束事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | ghd |
| 2 | hh |
| 3 | 66 |
| 4 | ggs |
| 5 | BDB |
| 6 | Baby |
+----+------+
6 rows in set (0.00 sec)
mysql> savepoint one;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test(id,name) values(7,'Sad');
Query OK, 1 row affected (0.00 sec)
mysql> savepoint two;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | ghd |
| 2 | hh |
| 3 | 66 |
| 4 | ggs |
| 5 | BDB |
| 6 | Baby |
| 7 | Sad |
+----+------+
7 rows in set (0.00 sec)
mysql> insert into test(id,name) values(8,'Sadag');
Query OK, 1 row affected (0.00 sec)
mysql> savepoint three;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | ghd |
| 2 | hh |
| 3 | 66 |
| 4 | ggs |
| 5 | BDB |
| 6 | Baby |
| 7 | Sad |
| 8 | Sadag |
+----+-------+
8 rows in set (0.00 sec)
mysql> rollback to two;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | ghd |
| 2 | hh |
| 3 | 66 |
| 4 | ggs |
| 5 | BDB |
| 6 | Baby |
| 7 | Sad |
+----+------+
7 rows in set (0.00 sec)
mysql> rollback to one;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | ghd |
| 2 | hh |
| 3 | 66 |
| 4 | ggs |
| 5 | BDB |
| 6 | Baby |
+----+------+
6 rows in set (0.00 sec)
4. MySQL 函数
MySQL 有很多内置的函数,以下列出了这些函数的说明。
详见:MySQL 函数
总结:
MySQL导入数据
MYSQL数据库的事务(Transaction)
数据库事务(Transaction)详解
MySQL事务(TRANSACTION)