Tagged: MySql

Mysql workbench 数据库设计

  • PK:primary key 主键
  • NN:not null 非空
  • UQ:unique 唯一索引
  • BIN:binary 二进制数据(比text更大)
  • UN:unsigned 无符号(非负数)
  • ZF:zero fill 填充0 例如字段内容是1 int(4), 则内容显示为0001
  • AI:auto increment 自增

外键约束

MySQL中定义外键的表,双方必须都是Innodb

子表中外键字段和其对应父表中的字段必须都设为索引,主键自动为索引

E-R图中几种关系

  • Identifying Relationship
    需要提供外键给另外一张表作联合主键
  • Non-identifying Mandatory Relationship
    强制外键
  • Non-identifying Optional Relationship
    可选外键
  • One-to-one Relationship Non-specific
    无特殊关系

判别一个关系是Non-Identifying还是Identifying只要区分子实体的主键,看是否需要父实体的外键来共同作为主键(类似联合主键?),需要则为Identifying,如果子实体自己的主键就可唯一标识则它为Non-Identifying!

  • 虚线1:1关联是添加外键的
  • 实线1:1是添加外键为主键的
-- 多个外键 个人理解 要把该字段设置为索引,再foreign key
CREATE TABLE product (
    category INT NOT NULL,
    id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY (category , id)
);
 
CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
);
 
CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    PRIMARY KEY (no),
    INDEX (product_category , product_id),
    FOREIGN KEY (product_category , product_id)
        REFERENCES product (category , id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    INDEX (customer_id),
    FOREIGN KEY (customer_id)
        REFERENCES customer (id)
);

-- 先给父表,子表中涉及到外键的字段设置索引
ALTER TABLE parts ADD INDEX idx_model (model);
ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel);
 
-- 在给pc表设置外键
ALTER TABLE pc ADD CONSTRAINT fk_cpu_model FOREIGN KEY (cpumodel) REFERENCES parts(model) ON UPDATE CASCADE;
 
-- 删除外键
alter table child drop foreign key 外键名

JAVA JDBC 报错一则!

Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

MYSQL在高版本的连接中需要指定SSL开启状态

String url = "jdbc:mysql://localhost:3306/bbc?"	+ "user=root&password=privacy&useUnicode=true&characterEncoding=UTF8&useSSL=false";

 

MySql 开启外部访问

# 改表法 
mysql -u root -ppassword use mysql; 
mysql> update user set host = '%' where user = 'root'; 
mysql> flush privileges; 
mysql> select host, user from user;
# 授权法 
mysql>  grant all privileges on *.* to root@'%' identified by "PASSWORD"; 
mysql>  FLUSH PRIVILEGES;

  • 第一个*为MySQL中的数据库名。
  • 第二个*为MySQL中的指定的数据库的表名。
  • root为授权用户的用户名。
  • %为任意主机,也可以写指定的ip地址或者主机名。
  • PASSWORD为授权改用户在指定的数据访问MySQL时的密码,如果该密码和本地用户同名用户的密码不一致,远程访问时务必使用该授权密码。
# 防火墙授权
iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
service iptables save
service iptables restart

Archives