本教程来自我的github nightttt7/MySQL-tutorial, 使用python脚本定时更新在此

本教程用于:
1. 入门学习
2. 快速查询
- 初稿完成于2018/3/13 by nightttt7 and lotus3333

todo

1. 完善 游标
2. 触发器
3. 函数
4. 引擎
5. 数据库设置与安全
6. 范式
7. 更多的实战

目录

事务

• TRANSACTION
• 管理必须成批执行的SQL语句,要么全部成功,要么全部失败
• 事务开启

START TRANSACTION;
# 或者
BEGIN;

• 事务提交

COMMIT;

• 事务回滚

ROLLBACK;

• 设置保存点

SAVEPOINT 保存点名称;

• 回滚到保存点

ROLLBACK TO SAVEPOINT 保存点名称;

• 删除保存点

RELEASE SAVEPOINT 保存点名称;

• 创建/删除/更改结构等语句不能被回滚
• 事务不能嵌套

示例代码

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test value('英文');
Query OK, 1 row affected (0.05 sec)
mysql>
insert into test value('挪威语');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from test;
+-----------+
| name
|
+-----------+
| 中文
|
| 英文
|
| 挪威语
|
+-----------+
3 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test value('法语');
Query OK, 1 row affected (0.05 sec)
mysql> rollback;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from test;
+-----------+
| name
|
+-----------+
| 中文
|
| 英文
|
| 挪威语
|
+-----------+
3 rows in set (0.00 sec)
mysql> begin ;
Query OK, 0 rows affected (4.01 sec)
mysql> insert into test value('俄罗斯语');
Query OK, 1 row affected (0.01 sec)
mysql> savepoint yaou;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test value('西班牙语');
Query OK, 1 row affected (0.00 sec)
mysql> savepoint xiou;
Query OK, 0 rows affected (0.02 sec)
mysql>
insert into test value('阿拉伯语');
Query OK, 1 row affected (0.00 sec)
mysql> savepoint dongou;
Query OK, 0 rows affected (0.00 sec)
mysql> rollback to savepoint xiou;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+--------------+
| name
|
+--------------+
| 中文
|
| 英文
|
| 挪威语
|
| 法语
|
| 粤语
|
| 瑞典语
|
| 芬兰语
|
| 俄罗斯语
|
| 西班牙语
|
+--------------+
9 rows in set (0.00 sec)

详细docs地址

1. 数值类型

1. bool值等于tinyint(1)。0是False，非0是True；但是False是0，True是1。
2. 个人看法是尽量不要使用UNSIGNED，因为可能会带来一些意想不到的的效果，对于INT类型可能存放不了的数据，INT UNSIGNED同样可能存放不了，与其如此，还不如在数据库设计阶段将INT类型提升为BIGINT类型 来源
3. [(M)] 对于bit指bit数，最大63，默认1。M indicates the maximum display width for integer types. For floating-point and fixed-point types, M is the total number of digits that can be stored (the precision). For string types, M is the maximum length. The maximum permissible value of M depends on the data type.
4. [UNSIGNED]无符号 [ZEROFILL]自动补零（自动unsigned）只有BIT没有这两个选项

BIGINT FLOAT DOUBLE DECIMAL

2. 日期和时间类型

当指定不合法的MySQL不能表示的值时使用”零”值。TIMESTAMP类型有专有的自动更新特性。

3. 字符串类型

1. The CHARACTER SET attribute specifies the character set, and the COLLATE attribute specifies a collation for the character set.CHARSET is a synonym for CHARACTER SET.
2. CHARACTER SET binary attribute for a character string data type causes the column to be created as the corresponding binary string data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB.
3. BINARY的不同是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
4. CHAR[(M)] M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1.
5. VARCHAR(M) M represents the maximum column length in characters. The range of M is 0 to 65,535.
6. 还有ENUM（枚举）和SET两种字符串形式。

文本文档的导入导出

• my.ini文件设置可访问路径

[mysqld]
secure-file-priv = C:UsersAdministratorDesktop

• 导出为TXT文档

select * into outfile '文件路径' from 表名;
# windows下用\
# linux下用/

• 导入TXT文档

load data local infile '文件路径' into table 表名;

• 分隔符号
  默认按制表符和换行符分隔

FIELDS TERMINATED BY 't';
LINES TERMINATED BY 'n';

windows下

LINES TERMINATED BY 'rn';

mac下

LINES TERMINATED BY 'r';

批处理

• 批处理-shell https://dev.mysql.com/doc/refman/5.7/en/batch-mode.html

shell> mysql -h host -u user -p < batch-file
Enter password: ********

• 如果您希望脚本继续，即使其中的某些语句产生错误，则应使用 –force命令行选项。
• 如果要以批处理方式获取交互式输出格式，请使用mysql -t。要输出执行的语句输出，请使用mysql -v
• 您还可以使用命令或命令从mysql提示符中使用脚本:source\.

实战1

• 原文地址:http://blog.csdn.net/fuchunyuan0/article/details/51883889?locationNum=3&fps=1

初始化

C:UsersAdministrator>mysql -uroot -pSqq123456!
mysql> create database if not exists customdb;
Query OK, 1 row affected (0.05 sec)
mysql> use customdb;
Database changed
mysql> create table if not exists customer(
->
c_id char(6) primary key,

->
name varchar(30)not null,

->
location varchar(30),

->
salary decimal(8,2)

-> );
Query OK, 0 rows affected (0.47 sec)
mysql> create table if not exists bank(
->
b_id char(5) primary key,

->
bank_name char(30) not null

-> );
Query OK, 0 rows affected (0.34 sec)
mysql> create table if not exists deposite(
->
d_id int(10) auto_increment primary key,

->
c_id char(6),

->
b_id char(5),

->
dep_date date,

->
amount decimal(8,2),

->
constraint FK_c_id foreign key(c_id) references customer(c_id)

-> );
Query OK, 0 rows affected (0.32 sec)
mysql> insert into customer
-> values('101001','孙杨','广州',1234),

-> ('101002','郭海','南京',3526),

-> ('101003','卢江','苏州',6892),

-> ('101004','郭惠','济南',3492);
Query OK, 4 rows affected (0.15 sec)
Records: 4
Duplicates: 0
Warnings: 0
mysql> insert into bank
-> values('B0001','工商银行'),

-> ('B0002','建设银行'),

-> ('B0003','中国银行');
Query OK, 3 rows affected (0.07 sec)
Records: 3
Duplicates: 0
Warnings: 0
mysql> insert into bank
-> values('B0004','农业银行');
Query OK, 1 row affected (0.04 sec)
mysql> insert into deposite
-> values(1,'101001','B0001','2011-04-05',42526),

-> (2,'101002','B0003','2012-07-15',66500),

-> (3,'101003','B0002','2010-11-24',42366),

-> (4,'101004','B0004','2008-03-31',62362),

-> (5,'101001','B0003','2002-02-07',56346),

-> (6,'101002','B0001','2004-09-23',353626),

-> (7,'101003','B0004','2003-12-14',36236),

-> (8,'101004','B0002','2007-04-21',26267),

-> (9,'101001','B0002','2011-02-11',435456),

-> (10,'101002','B0004','2012-05-13',234626),

-> (11,'101003','B0003','2001-01-24',26243),

-> (12,'101004','B0001','2009-08-23',45671);
Query OK, 12 rows affected (0.09 sec)
Records: 12
Duplicates: 0
Warnings: 0

数据预览

题目

1. 将数据表deposite中孙杨的所有存款金额加10000
2. 将数据表deposite中所属账户为工商银行并且存入日期为2011-04-05的人员的存款金额加1000
3. 将数据表deposite中郭海的银行标识改为建设银行
4. 查询孙杨的存款信息（显示信息：客户ID，客户姓名，银行名称，存款金额）
5. 查询日期为2011-04-05这一天进行过存款的客户ID，客户姓名，银行名称，存款金额
6. 查询工商银行存款大于等于一万的客户姓名（使用表链接和子查询两种方式实现）
7. 查询在农业银行存款前五名的客户存款信息（显示信息：客户姓名，银行名称，存款金额）
8. 创建存储过程update_salary，更新customer表的salary属性，将salary低于5000的客户的salary变为原来的2倍

参考答案

1. 将数据表deposite中孙杨的所有存款金额加10000

mysql> update deposite set amount = amount + 10000
-> where c_id = (select c_id from customer where name = '孙杨');
Query OK, 3 rows affected (0.12 sec)
Rows matched: 3
Changed: 3
Warnings: 0

1. 将数据表deposite中所属账户为工商银行并且存入日期为2011-04-05的人员的存款金额加1000

mysql> update deposite set amount = amount + 1000
-> where dep_date = '2011-04-05' and
-> b_id = ( select b_id from bank where bank_name = '工商银行');
Query OK, 1 row affected (0.11 sec)
Rows matched: 1
Changed: 1
Warnings: 0

1. 将数据表deposite中郭海的银行标识改为建设银行

mysql> update deposite set b_id =
-> (select b_id from bank where bank_name = '建设银行')
-> where c_id =
-> (select c_id from customer where name = '郭海')
-> ;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3
Changed: 3
Warnings: 0

1. 查询孙杨的存款信息（显示信息：客户ID，客户姓名，银行名称，存款金额）

mysql> select deposite.c_id,name,bank_name,amount from deposite,customer,bank

-> where name = '孙杨' and deposite.c_id=customer.c_id and bank.b_id=deposite.b_id;
+--------+--------+--------------+-----------+
| c_id
| name
| bank_name
| amount
|
+--------+--------+--------------+-----------+
| 101001 | 孙杨
| 工商银行
|
42526.00 |
| 101001 | 孙杨
| 建设银行
| 435456.00 |
| 101001 | 孙杨
| 中国银行
|
56346.00 |
+--------+--------+--------------+-----------+
3 rows in set (0.01 sec)

或者

mysql> select customer.c_id,name,bank_name,amount from

-> deposite inner join customer inner join bank

-> on deposite.c_id = customer.c_id and deposite.b_id = bank.b_id

-> where name = '孙杨';
+--------+--------+--------------+-----------+
| c_id
| name
| bank_name
| amount
|
+--------+--------+--------------+-----------+
| 101001 | 孙杨
| 工商银行
|
42526.00 |
| 101001 | 孙杨
| 建设银行
| 435456.00 |
| 101001 | 孙杨
| 中国银行
|
56346.00 |
+--------+--------+--------------+-----------+
3 rows in set (0.00 sec)

1. 查询日期为2011-04-05这一天进行过存款的客户ID，客户姓名，银行名称，存款金额

mysql> select deposite.c_id,name,bank_name,amount from

-> deposite inner join customer inner join bank

-> on deposite.c_id = customer.c_id and deposite.b_id = bank.b_id

-> where dep_date = '2011-04-05';
+--------+--------+--------------+----------+
| c_id
| name
| bank_name
| amount
|
+--------+--------+--------------+----------+
| 101001 | 孙杨
| 工商银行
| 42526.00 |
+--------+--------+--------------+----------+
1 row in set (0.00 sec)

1. 查询工商银行存款大于等于十万的客户姓名(每个客户在每个银行只有一个账户)

mysql> select name from

-> deposite inner join customer inner join bank

-> on deposite.c_id = customer.c_id and deposite.b_id = bank.b_id

-> where bank_name = '工商银行' and amount >= 10000;
+--------+
| name
|
+--------+
| 孙杨
|
| 郭惠
|
+--------+

1. 查询在农业银行存款前两名的客户存款信息（显示信息：客户姓名，银行名称，存款金额）

mysql> select name from

-> deposite inner join customer inner join bank

-> on deposite.c_id = customer.c_id and deposite.b_id = bank.b_id

-> where bank_name='农业银行'

-> order by amount limit 2

-> ;
+--------+
| name
|
+--------+
| 卢江
|
| 郭惠
|
+--------+
2 rows in set (0.06 sec)

1. 创建存储过程update_salary，更新customer表的salary属性，将salary低于5000的客户的salary变为原来的2倍

mysql> CREATE PROCEDURE update_salary()
-> UPDATE customer set salary = salary * 2
-> WHERE salary < 5000;
Query OK, 0 rows affected (0.00 sec)
mysql> call update_salary();
Query OK, 3 rows affected (0.09 sec)
mysql> select * from customer;
+--------+--------+----------+---------+
| c_id
| name
| location | salary
|
+--------+--------+----------+---------+
| 101001 | 孙杨
| 广州
| 2468.00 |
| 101002 | 郭海
| 南京
| 7052.00 |
| 101003 | 卢江
| 苏州
| 6892.00 |
| 101004 | 郭惠
| 济南
| 6984.00 |
+--------+--------+----------+---------+
4 rows in set (0.00 sec)

最后

以上就是陶醉狗为你收集整理的MySQL简明教程-下todo目录事务1. 数值类型2. 日期和时间类型3. 字符串类型文本文档的导入导出批处理实战1的全部内容，希望文章能够帮你解决MySQL简明教程-下todo目录事务1. 数值类型2. 日期和时间类型3. 字符串类型文本文档的导入导出批处理实战1所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。