我的MySQL技术笔记

2011年07月16日数据库技术_综合

MySQL

一、MySQL Connector for J （MySQL Java JDBC驱动）

1、Java数据类型和 MySQL数据类型的对应关系：

参见：https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html

2、MySQL 的 JDBC API 实现

参见：https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html

3、JDBC URL 格式、语法和参数

参见：https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

在使用数据库连接池的情况下，最好设置如下两个参数：

autoReconnect=true&failOverReadOnly=false

对应中文环境，通常MySQL连接URL可以设置为：

jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false

MySQL编码和Java编码对应表：

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-charsets.html

二、MySQL官方文档内容

1、MySQL自带的系统表

参见：

https://dev.mysql.com/doc/refman/5.7/en/system-database.html

2、数据类型

参见：

https://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html

3、字符编码相关

参见：

https://dev.mysql.com/doc/refman/5.7/en/charset.html

创建表时明确指定：

CREATE DATABASE mydb

DEFAULT CHARACTER SET utf8

  DEFAULT COLLATE utf8_general_ci;

备注：

COLLATE（collate）：用于指定数据集如何排序，以及字符串的比对规则。

（A collation is a set of rules that defines how to compare and sort character strings.）

collation 名字的规则可以归纳为这两类：

1. characterset_language/other_ci/cs

2. characterset_bin（二进制）

ci 是 case insensitive （大小写不敏感） 的缩写， cs 是 case sensitive 的缩写。即，指定大小写是否敏感。

具体参见：

https://dev.mysql.com/doc/refman/5.7/en/adding-collation.html

在my.ini配置文件中指定默认值：

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

查看当前连接（Connection）的编码

SHOW VARIABLES LIKE 'character_set%';

SHOW VARIABLES LIKE 'collation%

备注：Character set issues affect not only data storage, but also communication between client programs and the MySQL server. If you want the client program to communicate with the server using a character set different from the default, you'll need to indicate which one. For example, to use the utf8 Unicode character set, issue this statement after connecting to the server:

SET NAMES 'utf8';

SET NAMES 'utf8' COLLATE 'utf8_general_ci';

或者使用：

charset utf8;

这个设置应该是一次连接，如果再次连接又需要重新设置。如果想设置默认值，在my.ini配置文件中设置如下：

[mysql]

default-character-set=utf8

4、SQL查询优化

参见：

https://dev.mysql.com/doc/refman/5.7/en/select-optimization.html

5、保留关键字

参见：

https://dev.mysql.com/doc/refman/5.7/en/keywords.html

启动MySQL服务：net start mysql

注：我们以XXX代表数据库名(database)，aaa代表数据表名(table), id和name为列名(column)

DOS下连接数据库：mysql -u用户名 -hIP -p密码

备份（前提是以断开数据库连接）：mysqldump -u用户名 -p密码数据库名 >F:\XXX.txt

恢复：mysql -u用户名 -p密码数据库名 <F:\XXX.txt

数据库操作：
create database XXX;

show databases;

use XXX;

drop database XXX;

表操作：
1.创建create
——————————————————————
create table aaa(
id int auto_increment primary key,
name varchar(30) not null,
);
——————————————————————
2.查看show
——————————————————————
show columns from aaa;
show columns from aaa from XXX;
describe aaa id;（只显示一列）简写为：desc aaa id;
——————————————————————
3.修改alert
———————————————————————————————————————————
alter table aaa add email varchar(30) not null,modify name varchar(40) not null;
注意修改的最小单位为一列，不能单独修改一列中的某一个属性。例如修改name varchar(10) not null成varchar(30),如果用modify name varchar(30);就会出错，因为默认把not null也修改成null了。
———————————————————————————————————————————
4.插入insert
—————————————————————————————————————
insert into aaa(user,password,email) values('zollty','123456','zollty@qq.com');
—————————————————————————————————————
5.#######表查询语句#####
————————————————————————————————

select * from aaa
where ……
group by ……
order by ……
having ……
limit ……
————————————————————————————————
6.修改update
—————————————————————————————
update aaa set password='741951' where user='zollty';
—————————————————————————————
7.删除delete
———————————————————
delete from aaa where user='tiger';
———————————————————

特别案例分析：

1.联合查询

SELECT a . * , b.name, b.email, b.qq, c.revert, c.revert_time  
FROM post a  
LEFT JOIN revert c ON ( a.id = c.post_id ) , guest b WHERE a.guest_id = b.id  
ORDER BY a.id DESC  

这是一个实际的案例，要求是：在guest(id,name,email)，post(id,guest_id,post,post_time)，

revert(id,post_id,revert,revert_time) 三个表中找到guest中的name，email，qq以及revert中的revert，revert_time还有post中的所有信息。三个表的外键关系是guest.id = post.guest_id，post.id = revert.post_id。查询的入口是post表，根据post的id主键找到guest和revert表中的相应信息，然后删除。

第三行代码是关键：

LEFT JOIN revert c ON ( a.id = c.post_id ) , guest b WHERE a.guest_id = b.id

分析：左连接到revert表，revert化名为c，连接条件是a.id = c.post_id（即revert中的post_id要和post表中的id相同）；左连接到guest表，guest化名为b，连接条件是a.guest_id = b.id（即guest表中的id要和post表中的guest_id相同）。显然这两个连接都是利用了上面的外键关系。

实例：

注意：/*……*/ 为注释内容。

/*连接数据库：-u后接用户名，-p后接密码*/
C:\Users\Administrator>mysql -uroot -hlocalhost -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.

/*选择数据库，USE后加数据库的名称*/
mysql> USE zollty;
Database changed

/*查看student表的所有列（columns）*/
mysql> SHOW COLUMNS FROM student;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| Sno   | char(9)              | NO   | PRI | NULL    |       |
| Sname | char(20)             | NO   |     | NULL    |       |
| Ssex | char(4)              | NO   |     | NULL    |       |
| Sage | smallint(6) unsigned | YES |     | NULL    |       |
| Sdept | varchar(20)          | YES |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

/*向student表中插入数据*/
mysql> INSERT INTO student(Sno,Sname,Ssex,Sage,Sdept) values('20215121','李

勇','男','20','CS');
Query OK, 1 row affected (0.03 sec)

/*以同样的方法插入"王敏""张立"等，（略）………*/

/*向course表中插入数据，此时还不能插入Cpno列，因为我设置了外键约束*/
mysql> INSERT INTO course(Cno,Cname) VALUES ('1','数据库');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO course(Cno,Cname) VALUES ('2','数学');
Query OK, 1 row affected (0.04 sec)

（略）………
mysql> INSERT INTO course(Cno,Cname) VALUES ('7','PASCAL语言');
Query OK, 1 row affected (0.02 sec)

/*更新数据，因为上面还没有指定列Cpno和Ccredit的值，此时可以设置了*/
mysql> UPDATE course
-> SET Cpno='5', Ccredit=4
-> WHERE Cno='1';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE course
-> SET Cpno='1', Ccredit=4
-> WHERE Cno='3';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

（略）………

/*最基本的查询语句：查询course表中所有列的数据*/
mysql> SELECT *
    -> FROM course;
+-----+------------+------+---------+
| Cno | Cname      | Cpno | Ccredit |
+-----+------------+------+---------+
| 1   | 数据库     | 5    |       4 |
| 2   | 数学       | NULL |       2 |
| 3   | 信息系统   | 1    |       4 |
| 4   | 操作系统   | 6    |       3 |
| 5   | 数据结构   | 7    |       4 |
| 6   | 数据处理   | NULL |       2 |
| 7   | PASCAL语言 | 6    |       4 |
+-----+------------+------+---------+
7 rows in set (0.00 sec)

/*退出MySQL*/
mysql> exit
Bye

/*备份数据库：将名为mysql01的数据库备份到E:\mysql.txt*/
C:\Users\Administrator>mysqldump -uroot -pmymima mysql01 >E:\mysql.txt

查看编码设置

show variables like 'character_set_%';

修改编码

alter database mydb character set utf-8;

1 DATE 格式为yyyy-MM-dd

2 TIME 格式为hh:mm:ss

3 DATETIME 格式为yyyy-MM-dd hh:mm:ss

4 TEMESTAMP 格式为yyyyMMddhhmmss（用于时间戳设置）

mysql数据分页

Select * From tests limit 200,100

MySQL中tinytext、text、mediumtext和longtext详解

Char(N): N=1~255 个字节分辨大小写固定长度

VarChar(N) N=1~255 个字节分辨大小写可变长度

TinyBlob 最大长度255个字节(2^8-1)

TinyText 最大长度255个字节(2^8-1)

Blob 最大长度65535个字节(2^16-1)

Text 最大长度65535个字节(2^16-1)

MediumBlob 最大长度 16777215 个字节(2^24-1)

MediumText 最大长度 16777215 个字节(2^24-1)

LongBlob 最大长度4294967295个字节 (2^32-1)

LongText 最大长度4294967295个字节 (2^32-1)

注：Blob (Binary large objects)储存二进位资料，且有分大小写

mysql bigint问题

比如mysql的bigint，后端long型的订单号，"orderId":1606291143573740096

但前端转换成javascript的值，精度问题，就变成了1606291143573740000。有何好的解决办法？

百度一下，网上很多人都遇到了这个问题。

方法1：最笨的办法是，后端就用String类型。

参考资料：https://www.zhihu.com/question/34564427?sort=created

方法2：或者，mysql的bigint只用16位，不用太长。

后端订单号，主键，用String类型，确实可以解决这个问题。

但是，其它业务就想用bigint作为主键，怎么办呢？

方法3：后端用long，异步给前端时，用string。（后端手动转换）

最好的办法就是，设计的时候就不用这个bigint型的主键。（方法2）