Growing - mysql - 《Clear Note》

问题报错
SQL

mysql -u root
use mysql;
show tables;
mysql> select host, user, plugin from user;

查询出的用户，如图:

mysql8.0 引入了新特性 caching_sha2_password；这种密码加密方式Navicat 12以下客户端不支持；

问题报错

**2003 - Can’t connect to MySQL server on ‘121.89.167.190’ (61 “Connection refused”)

用Navicat客户端连接有如上报错
mysql容器启动用的是

docker run —name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

端口不知道启动到哪里了，3306没有被占用，所以改用指定端口号启动容器

2007 - Protocol mismatch; server version = 11, client version = 10

解决报错1的问题使用的容器启动命令也有问题

docker run -p 3307:3306 —name mysql8.0 -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0

SQL

utf8mb4
utf8mb4_general_ci

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS province;
CREATE TABLE province (
  id            int,
  name           varchar(64),
  province_id      varchar(12),
  PRIMARY KEY (id)
);


DROP TABLE IF EXISTS city;
CREATE TABLE city (
  id           int,
  name          varchar(64),
  city_id       varchar(12),
  province_id   varchar(12),
  PRIMARY KEY (id)
);


DROP TABLE IF EXISTS county;
CREATE TABLE county (
  id  int,
  name varchar(64),
  county_id varchar(12),
  city_id varchar(12),
  PRIMARY KEY (id)
);

条件查询

# and
SELECT * FROM county WHERE (NAME NOT LIKE "%区") AND (NAME NOT LIKE "%县");
# or
SELECT * FROM province WHERE NAME = "安徽省" OR NAME = "北京市";
SELECT * FROM city WHERE province_id = "110000000000" OR province_id = "340000000000";

# order
SELECT * FROM city WHERE province_id = "340000000000" ORDER BY id DESC;

# limit
SELECT * FROM city WHERE province_id = "340000000000" ORDER BY id ASC LIMIT 5;

# count
SELECT province_id , count(id) FROM city GROUP BY province_id;

一对一

从表持有外键

UPDATE clazz set masterid = (
    SELECT id from teacher WHERE name="robin"
) WHERE name = "丐帮";

-- 根据班主任找班级
SELECT * FROM teacher WHERE id = (
    SELECT masterid FROM clazz WHERE NAME = "丐帮"
);

-- 根据班主任查询班级
SELECT * FROM clazz WHERE masterid = (
    SELECT id FROM teacher WHERE name ="robin"
);

一对多

多方持有一方的外键

- 查询一个班级有多少学生
SELECT * FROM student WHERE classid = (
    SELECT id FROM class WHERE name = ""
);

- 查询某个学生是哪个班的
SELECT * FROM class WHERE id =  (
    SELECT id FROM student WHERE name = ""
);

多对多

建一张中间表

create table student_course(
  sid integer not null,
  cid integer not null,
  primary key (sid,cid)
);

-- 查询学生选了几门课
SELECT * FROM course WHERE id in (
    SELECT cid FROM student_course WHERE sid =( 
        SELECT id FROM student WHERE name = "小明"
    )
);

-- 查询一门课被几个学生选
SELECT * FROM student WHERE id in (
    SELECT sid FROM student_course WHERE cid =( 
        SELECT id FROM course WHERE name = "PHP"
    )
);

联合查询

union

查询中国各省id, 省名字
河北所有地级市id, 名字 ```sql SELECT id, name FROM province

UNION

SELECT id, name FROM city WHERE province_id = ( SELECT province_id FROM province WHERE name = “河北省” );


<a name="e5uoi"></a>
#### join


<a name="9OKG7"></a>
#### Mysql
```sql
CREATE TABLE tb_emp1 (
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(25),
  deptId INT(11),
  salary FLOAT
)

主键约束，要求主键数据唯一，且不允许为空, 可以加快数据库查询速度
联合主键

CREATE TABLE tb_emp1 (
  name VARCHAR(25),
  deptId INT(11),
  salary FLOAT,
  PRIMARY KEY(name, deptId)
)

外建:
主表(父表): 对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表

CREATE TABLE tb_dept1 (
    id INT(11) PRIMARY KEY,
  name VARCHAR(22) NOT NULL,
  location VARCHAR(50)
)

查询

CREATE TABLE fruits (
    f_id char(10)                         NOT NULL,
  s_id INT                            NOT NULL,
  f_name char(255)                     NOT NULL,
  f_price decimal(8, 2)         NOT NULL,
  PRIMARY KEY(f_id)
);

INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES 
('a1', 101, 'apple', 5.2),
('a2', 102, 'blackberry', 2.2),
('b1', 103, 'orange', 10.2),
('b2', 104, 'melon', 8.2),
('b5', 105, 'banner', 9.2),
('bs1',106, 'grape', 6.2),
('bs2', 107, 'coconut', 3.2),
('t1', 108, 'cherry', 19.2),
('t2', 109, 'apricot', 18.2),
('o2', 110, 'lemon', 80.2),
('c0', 111, 'xbabay', 16.2),
('m1', 112, 'xbababa', 14.2),
('m2', 113, 'xxxtt', 15.2),
('t4', 114, 'water', 25.2),
('m3', 115, 'xxxx', 35.2),
('12', 116, 'beer', 45.2);

SELECT f_id, f_name FROM fruits;

SELECT f_name,f_price FROM fruits WHERE f_price=10.2;
SELECT f_name,f_price FROM fruits WHERE f_price<10;

-- 查询价格在2.00到10.00之外的水果名称和价格
SELECT f_name,f_price FROM fruits WHERE f_price NOT BETWEEN 2.0 AND 10.2;

LIKE：
%匹配任意长度字符、甚至包括零字符
_一次只能匹配任意一个字符

通过连接符可以实现多个表查询，
插入演示数据

CREATE TABLE suppliers (
    s_id         int             auto_increment,
  s_name  char(50)     NOT NULL,
  s_city  char(50)     NULL,
  s_zip   char(10)  NULL,
  s_call  char(50)     NOT NULL,
  PRIMARY KEY(s_id)
);

INSERT INTO suppliers (s_id, s_name, s_city, s_zip, s_call) VALUES
(101, 'FastFruit Inc1', 'Tianjin', '300000', '48075'),
(102, 'FastFruit Inc2', 'Beijing', '400000', '44333'),
(103, 'FastFruit Inc3', 'Shanghai', '500000', '45678'),
(104, 'FastFruit Inc4', 'Hangzhou', '600000', '11111'),
(105, 'FastFruit Inc5', 'Qingdao', '700000', '22222'),
(106, 'FastFruit Inc6', 'Hefei', '800000', '3333332'),
(107, 'FastFruit Inc7', 'Shamen', '900000', '90046');

内连接(INNER JOIN):使用这种语法的时候，连接的条件使用ON，而不是WHER

SELECT suppliers.s_id, s_name, s_city, f_name, f_price 
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id;

自连接: 两张表是同一张表，指相互连接的表在物理上为同一个表，但可以在逻辑上分两个表

SELECT f1.f_id, f1.f_name
FROM fruits AS f1, fruits AS f2
WHERE f1.s_id = f2.s_id AND f2.f_id='a1';

外连接查询:
LEFT JOIN(左连接): 返回左表中的所有记录，而右表中只匹配返回匹配的记录值
RIGHT JOIN(右连接)
插入演示数据

CREATE TABLE orders (
    o_id int AUTO_INCREMENT,
  o_date datetime NOT NULL,
  c_id int NOT NULL,
  primary KEY(o_id)
)