我的MySQL技术笔记
2011年07月16日


一、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自带的系统表
参见:
2、数据类型
参见:
3、字符编码相关
参见:
创建表时明确指定:
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 的缩写。即,指定大小写是否敏感。
具体参见:
在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查询优化
参见:
5、保留关键字
参见:


启动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.联合查询

  1. SELECT a . * , b.name, b.email, b.qq, c.revert, c.revert_time  

  2. FROM post a  

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

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

/*以同样的方法插入"王敏""张立"等,(略)………*/


mysql> SHOW COLUMNS FROM course;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Cno     | char(4)     | NO   | PRI | NULL    |       |
| Cname   | char(40)    | NO   |     | NULL    |       |
| Cpno    | char(4)     | YES  | MUL | NULL    |       |
| Ccredit | smallint(6) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 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)