mysql：初识

# 创建数据库
CREATE DATABASE IF NOT EXISTS huya;

# 删除数据库
DROP DATABASE IF EXISTS huya;

# 展示所有的数据库
SHOW DATABASES;

# 查看数据表
SHOW TABLES;

# 使用一个数据库
USE huya;

# 查看正在使用的数据库
SELECT DATABASE();

# 查看如何创建的数据库
SHOW CREATE DATABASE huya;

# 创建数据表
CREATE TABLE IF NOT EXISTS student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    age INT DEFAULT 0,
    phone VARCHAR(20) DEFAULT '' UNIQUE NOT NULL
);

# 删除表
DROP TABLE IF EXISTS student;

# 更新表名
ALTER TABLE student RENAME TO t_student;

# 添加一个新列
ALTER TABLE t_student ADD addr VARCHAR(100);
# 添加一个表示创建时间的新列 
ALTER TABLE t_student ADD createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
# 添加一个跟随更新时间的新列 
ALTER TABLE t_student ADD updateTime TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

# 删除一列
ALTER TABLE t_student DROP addr;

# 修改列的名字
ALTER TABLE t_student CHANGE addr address VARCHAR(100);

# 修改列的数据类型 
ALTER TABLE t_student MODIFY phone INT;

# 往表里插入数据
INSERT INTO t_student (name, age, phone,  address) VALUES ('lynn1', 18, '13800138001', '深圳市宝安区');
INSERT INTO t_student (name, age, phone,  address) VALUES ('lynn2', 19, '13800138002', '深圳市福田区');
INSERT INTO t_student (name, age, phone,  address) VALUES ('lynn3', 20, '13800138003', '深圳市南山区');
INSERT INTO t_student (name, age, phone,  address) VALUES ('lynn4', 21, '13800138004', '深圳市南山区');
INSERT INTO t_student (name, age, phone,  address) VALUES ('lynn5', 22, '13800138005', '深圳市南山区');
INSERT INTO t_student (name, age, phone,  address) VALUES ('lynn6', 22, '13800138006', '深圳市龙岗区');
INSERT INTO t_student (name, age, phone,  address) VALUES ('lynn7', 23, '13800138007', '深圳市光明区');
INSERT INTO t_student (name, age, phone,  address) VALUES ('lynn8', 23, '13800138008', '深圳市龙岗区');
INSERT INTO t_student (name, age, phone,  address) VALUES ('lynn9', 22, '13800138009', '深圳市南山区');

UPDATE t_student SET age = 18 WHERE name = 'lynn9';

# 展示创建表的方式
SHOW CREATE TABLE t_student;

# 删除表的所有数据 
DELETE FROM t_student;

# 按条件删除数据 
DELETE FROM t_student WHERE name = 'lynn';

# 更新数据 
UPDATE t_student SET name = 'lily1', phone = '13800138001' WHERE phone = '18665865199';

# 查询
SELECT * FROM t_student;
SELECT name AS s_name, age AS s_age, phone as s_phone, address AS s_address from t_student;

# 双条件查询 
SELECT * FROM t_student WHERE age > 19 AND age < 22;

# 模糊查询: %表示任意个任意字符; _表示任意的一个字符
SELECT * FROM t_student WHERE address LIKE '___宝%';

# 查询结果排序 
SELECT * FROM t_student WHERE age >= 20 ORDER BY age DESC;

# 查询表的行数 
SELECT COUNT(*) FROM t_student;

# 分页查询 
SELECT * FROM t_student WHERE age >= 20 ORDER BY age ASC LIMIT 2 OFFSET 0;
SELECT * FROM t_student LIMIT 2 OFFSET 0; -- 限定的条数 偏移
SELECT * FROM t_student LIMIT 2 OFFSET 2;
SELECT * FROM t_student LIMIT 2 OFFSET 4;
SELECT * FROM t_student LIMIT 2 OFFSET 6;

SELECT * FROM t_student LIMIT 0, 2; -- 偏移, 限定的条数 
SELECT * FROM t_student LIMIT 2, 2;
SELECT * FROM t_student LIMIT 4, 2;


# 聚合函数 
SELECT ROUND(AVG(age),1) AS avgAge FROM t_student;
SELECT AVG(age) FROM t_student WHERE address LIKE '%宝安%';
SELECT MAX(age) FROM t_student WHERE age < 20;
SELECT SUM(age) FROM t_student;
SELECT COUNT(*) AS count, MAX(age) AS maxAge, address FROM t_student GROUP BY address;

# 可以用HAVING对筛选结果做一些约束
SELECT COUNT(*) AS count, MAX(age) AS maxAge, address FROM t_student GROUP BY address HAVING count > 2;

# 查询组装json 
SELECT address AS s_address, name AS s_name, age AS s_age, phone AS s_phone,
    JSON_OBJECT( 'name', name, 'age', age, 'phone', phone, 'address', address) as s_info
    FROM t_student WHERE age <= 18;
    
# 测试手机 
CREATE TABLE IF NOT EXISTS products(
    id INT PRIMARY KEY AUTO_INCREMENT,
    brand VARCHAR(20),
    title VARCHAR(100),
    price INT,
    score DOUBLE,
    voteCnt INT,
    url VARCHAR(100),
    pid VARCHAR(30),
    brand_id INT
)

# 将相同品牌的组装起来 
-- GROUP BY: 表示根据什么分组 
SELECT brand, 
    JSON_ARRAYAGG(JSON_OBJECT('brand', brand, 'title', title, 'price', price, 'score', score, 'url', url)) AS phones
    FROM products GROUP BY brand;

SELECT brand, COUNT(*) AS num, ROUND(AVG(price),2) AS avgPrice FROM products GROUP BY brand;

# 添加另一张表, 单独列出手机的名字/网址/世界排名: 用于关联手机
CREATE TABLE IF NOT EXISTS brand(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    website VARCHAR(100),
    worldRank INT
)


INSERT INTO brand (name, website, worldRank) VALUES ('华为', 'www.huawei.com', 1);
INSERT INTO brand (name, website, worldRank) VALUES ('小米', 'www.mi.com', 10);
INSERT INTO brand (name, website, worldRank) VALUES ('苹果', 'www.apple.com', 5);
INSERT INTO brand (name, website, worldRank) VALUES ('OPPO', 'www.oppo.com', 15);
INSERT INTO brand (name, website, worldRank) VALUES ('京东', 'www.jd.com', 3);
INSERT INTO brand (name, website, worldRank) VALUES ('Google', 'www.google.com', 8);


# 添加一个brand_id字段
ALTER TABLE products ADD brand_id INT;

# 将products中的brand_id关联到brand中的id的值
UPDATE products SET brand_id = 1 WHERE brand = '华为';
UPDATE products SET brand_id = 2 WHERE brand = '小米';
UPDATE products SET brand_id = 3 WHERE brand = '苹果';
UPDATE products SET brand_id = 4 WHERE brand = 'OPPO';

ALTER TABLE products DROP brand_id;
DROP TABLE IF EXISTS brand;

# 创建products的外键
ALTER TABLE products ADD FOREIGN KEY (brand_id) REFERENCES brand(id);
ALTER TABLE products ADD FOREIGN KEY (brand_id) REFERENCES brand (id)
    ON UPDATE CASCADE 
    ON DELETE RESTRICT;

# 可以在设计表中查看外键的名字 
ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;

# 查看创建表的语句 
SHOW CREATE TABLE products;
SHOW CREATE TABLE brand;

SELECT COUNT(*) FROM products WHERE brand = 'vivo' OR brand = '锤子科技';

# 多表查询 
SELECT * FROM products, brand;
SELECT * FROM products, brand WHERE products.brand_id = brand.id;

# 左连接 
SELECT * FROM products LEFT JOIN brand ON products.brand_id = brand.id;
SELECT * FROM products LEFT JOIN brand ON products.brand_id = brand.id WHERE products.brand_id IS NULL;

# 右连接 
SELECT * FROM products RIGHT JOIN brand ON products.brand_id = brand.id;

# 内连接 
SELECT * FROM products INNER JOIN brand;
SELECT * FROM products INNER JOIN brand ON products.brand_id = brand.id;


-- # 测试多对多查询 
# 创建学生表 
CREATE TABLE IF NOT EXISTS students(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    age INT
)

# 创建课程表 
CREATE TABLE IF NOT EXISTS courses(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    price DOUBLE NOT NULL
)

INSERT INTO students (name, age) VALUES('lynn', 18); 
INSERT INTO students (name, age) VALUES('tom', 22); 
INSERT INTO students (name, age) VALUES('lilei', 25); 
INSERT INTO students (name, age) VALUES('lucy', 16); 
INSERT INTO students (name, age) VALUES('lily', 20);

INSERT INTO courses (name, price) VALUES ('英语', 100); 
INSERT INTO courses (name, price) VALUES ('语文', 666); 
INSERT INTO courses (name, price) VALUES ('数学', 888); 
INSERT INTO courses (name, price) VALUES ('历史', 80);
 
# 创建关系表 
CREATE TABLE IF NOT EXISTS students_select_courses(
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    FOREIGN key (student_id) REFERENCES students(id) ON UPDATE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE
)

# lynn 选修了 英文和数学
INSERT INTO students_select_courses (student_id, course_id) VALUES (1, 1); 
INSERT INTO students_select_courses (student_id, course_id) VALUES (1, 3);
# lilei选修了 语文和数学和历史
INSERT INTO students_select_courses (student_id, course_id) VALUES (3, 2); 
INSERT INTO students_select_courses (student_id, course_id) VALUES (3, 3); 
INSERT INTO students_select_courses (student_id, course_id) VALUES (3, 4);


# 查询lynn选择的课程 
SELECT stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice 
    FROM students stu 
    JOIN students_select_courses ssc ON stu.id = ssc.student_id 
    JOIN courses cs ON ssc.id = cs.id
    WHERE stu.id = 1;

SELECT stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
    FROM students stu 
    LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id 
    LEFT JOIN courses cs ON ssc.id = cs.id
    WHERE stu.name = 'lynn';
    
# 组装成对象: 如果为null, 就转化一下 
SELECT stu.id, stu.name, stu.age,
    JSON_ARRAYAGG(JSON_OBJECT('csId', IFNULL(cs.id, -1), 'csName', IFNULL(cs.name, ''))) as courses
    FROM students stu
    LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id 
    LEFT JOIN courses cs ON ssc.course_id = cs.id
    GROUP BY stu.id;
    

# 组装成对象 
SELECT stu.id, stu.name, stu.age,
    JSON_ARRAYAGG(JSON_OBJECT('csId', cs.id, 'csName', cs.name)) as courses
    FROM students stu
    LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id 
    LEFT JOIN courses cs ON ssc.course_id = cs.id
    WHERE cs.id IS NOT NULL
    GROUP BY stu.id;
    
# 查询哪些学生没有选课 
SELECT stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
    FROM students stu 
    LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id 
    LEFT JOIN courses cs ON ssc.id = cs.id
    WHERE cs.id IS NOT NULL;
    
# 查询哪些课没有被学生选择 
SELECT stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
    FROM students stu 
    RIGHT JOIN students_select_courses ssc ON stu.id = ssc.student_id 
    RIGHT JOIN courses cs ON ssc.id = cs.id
    WHERE stu.id IS NULL;

UPDATE students_select_courses SET course_id = 1 WHERE id = 3;

SELECT * FROM students_select_courses;
SELECT * FROM courses;