MySQL简明教程-下

程序员文章站 2022-05-28 16:27:03

...

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

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

todo

完善游标
触发器
函数
引擎
数据库设置与安全
范式
更多的实战

事务

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. 数值类型

类型	大小	备注
BIT[(M)]
TINYINT[(M)]	1字节	(-128，127)
SMALLINT[(M)]	2字节	(-32 768，32 767)
MEDIUMINT[(M)]	3字节	(-8 388 608，8 388 607)
INT或INTEGER[(M)]	4字节	(-2 147 483 648，2 147 483 647)
BIGINT[(M)]	8字节	极大整数值
DECIMAL[(M[,D])]		M是数字的总数，D是小数点后的数字
FLOAT[(M,D)]	4字节	单精度浮点数值
DOUBLE[(M,D)]	8字节	双精度浮点数值
FLOAT[(p)]		0-24单精度，25-53双精度

bool值等于tinyint(1)。0是False，非0是True；但是False是0，True是1。

个人看法是尽量不要使用UNSIGNED，因为可能会带来一些意想不到的的效果，对于INT类型可能存放不了的数据，INT UNSIGNED同样可能存放不了，与其如此，还不如在数据库设计阶段将INT类型提升为BIGINT类型来源

[(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.

[UNSIGNED]无符号 [ZEROFILL]自动补零（自动unsigned）只有BIT没有这两个选项

BIGINT FLOAT DOUBLE DECIMAL

类型	大小	范围（有符号）	范围（无符号）
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)
FLOAT	4 字节	(-3.402 823 466 E+38，3.402 823 466 351 E+38)	(0，3.402 823 466 E+38)
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，1.797 693 134 862 315 7 E+308)	(0，1.797 693 134 862 315 7 E+308)

2. 日期和时间类型

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

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/’838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	日期时间值
TIMESTAMP	4	1970-01-01 00:00:00/2037 年某时	YYYYMMDD HHMMSS	日期时间值，自动更新时间戳

3. 字符串类型

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

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.

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.

BINARY的不同是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

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.

VARCHAR(M) M represents the maximum column length in characters. The range of M is 0 to 65,535.

还有ENUM（枚举）和SET两种字符串形式。

文本文档的导入导出

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

[mysqld]
secure-file-priv = C:\Users\Administrator\Desktop

导出为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 '\r\n';

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.****.net/fuchunyuan0/article/details/51883889?locationNum=3&fps=1

初始化

C:\Users\Administrator>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

数据预览

MySQL简明教程-下

题目

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

参考答案

将数据表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

将数据表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

将数据表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

查询孙杨的存款信息（显示信息：客户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)

查询日期为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)

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

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   |
+--------+
| 孙杨   |
| 郭惠   |
+--------+

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

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)

创建存储过程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)

上一篇：《C语言及程序设计》实践参考——我的加班费

下一篇： Ubuntu中安装JDK简明教程

MySQL简明教程-下

todo

目录

事务

示例代码

1. 数值类型

2. 日期和时间类型

3. 字符串类型

文本文档的导入导出

批处理

实战1

初始化

数据预览

题目

参考答案

PHP MYSQL乱码问题,使用SET NAMES utf8校正_PHP教程

LEFT JOIN 与 RIGHT JOIN 简明教程(ORACLE & MYSQL)

清空MySQL下的bin.00000mysql日志文件

利用crontab定时备份MySQL教程_MySQL

Centos 下用yum 命令部署lamp环境_MySQL

通过Access从Web获取MySQL数据_PHP教程

Redhat Linux AS4下的LAMP与Discuz安装_PHP教程

windows下安装php5.5的redis扩展，php5.5redis_PHP教程

mysql用户修改密码，mysql修改密码_PHP教程

CentOS下使用mysqlhotcopy定时备份MySQL数据库（MyISAM存储引擎_MySQL