MySQL 总结

文章目录MySQL 总结概述软件下载连接MySQL数据库数据库操作表操作数据类型查看所有表查看表结构表约束创建表删除表修改表插入数据删除数据修改数据记录更新时间单表查询基本查询 SELECT别名 AS查询条件 WHERE模糊查询 LIKE排序 ORDER BY分页查询 LIMIT聚合函数分组 GROUP BY过滤 HAVING外键 FOREIGN KEY父子表关系外键约束行为多表查询笛卡尔乘积查询所有数据左连接右连接内连接全连接多对多关系转对象转数组MySQL 总结概述数据库分类关系型数据库MySQL、Oracle、SQL Server、Postgre SQL等非关系型数据库MongoDB、Redis、Memcached等关系型数据库通常需要创建多个二维数据表数据表之间相互关联形成一对一、一对多、多对多等关系非关系型数据库非关系型数据相对简单一些存储数据也更自由基于key-value的对应关系查询过程中不需要经过SQL解析SQL(Structured Query Language结构化查询语言)用于对数据库进行操作SQL语言关键字、函数名、数据库名、表名不区分大小写表中的字段名区分大小写但是推荐关键字大写如CREATE、TABLE、SHOW等如果遇到表名或字段名使用关键字可以使用包裹SQL语句可分4类DDLData Definition Language数据定义语言用于对数据库或表进行增删改查操作DMLData Manipulation Language数据操作语言用于对表中数据进行增删改查操作DQLData Query Language数据查询语言用于从数据库中查询和检索数据DCLData Control Language数据控制语言用于控制数据库的访问权限和安全级别管理用户对数据库对象的操作权限软件下载https://dev.mysql.com/downloads/mysql/连接MySQL数据库需要配置 MySQL 的环境变量。查看MySQL版本 mysql --version 连接数据库 方式一直接输入mysql -uroot -p123456 方式二另一行密文输入mysql -uroot -p数据库操作MySQL默认的数据库information_schema信息数据库包含MySQL再维护的其他数据库、表、列、访问权限等信息。performance_schema性能数据库记录MySQL Server数据引擎在运行过程中的一些资源消耗相关的信息。mysql用于存储数据库管理者的用户信息、权限信息以及一些日志信息等。sys相当于简易版的performance_schema将数据汇总成更容易理解的形式。查看所有数据库 SHOW DATABASES; 使用数据库 USE mydb; 查看使用的数据库 SELECT DATABASE(); 创建新的数据库 CREATE DATABASE mydb; CREATE DATABASE IF NOT EXISTS mydb; 删除数据库 DROP DATABASE mydb; DROP DATABASE IF EXISTS mydb; 修改数据库的字符集和排序规则 ALTER DATABASE mydb CHARACTER SET utf8 COLLATE utf8_unicode_ci;表操作数据类型MySQL支持的数据类型数值类型整数类型TINYINT1字节SMALLINT2字节INT/INTEGER4字节BIGINT8字节这些类型都可以加上 UNSIGNED 属性来表示非负数浮点类型FLOAT(M,D)4字节M 是总位数D 是小数位数。DOUBLE(M,D)8字节双精度浮点数比 FLOAT 精度更高。定点类型DECIMAL(M,D)M 是数字总位数D 是小数点后位数。占用空间取决于 M 和 D 的值。NUMERIC(M,D)作为 DECIMAL 的同义词。用于精确存储小数常用于货币等需要精确计算的场景DECIMAL的计算速度可能比FLOAT慢但现代 CPU 和数据库优化使得这种差距在很多应用中并不显著选择时应优先考虑精度需求。位类型BIT(M)存储 M 位二进制值M 的范围是 1 到 64默认为 1。字符串类型定长字符串CHAR(M)存储固定长度的字符串M 表示字符的最大长度1 到 255。变长字符串VARCHAR(M)存储可变长度的字符串M 表示字符的最大长度最大有效长度取决于行大小限制通常远大于 255。只占用实际存储数据所需的字节数加一些额外开销。文本类型TINYTEXT最大长度约为 255 个字符。TEXT最大长度约为 65,535 个字符。MEDIUMTEXT最大长度约为 16,777,215 个字符。LONGTEXT最大长度约为 4,294,967,295 个字符。二进制类型BINARY(M)类似于CHAR(M)但存储二进制字节。VARBINARY(M)类似于VARCHAR(M)但存储二进制字节。日期和时间类型DATE格式为 ‘YYYY-MM-DD’范围从 ‘1000-01-01’ 到 ‘9999-12-31’。TIME格式为 ‘HH:MM:SS’范围从 ‘-838:59:59’ 到 ‘838:59:59’。DATETIME格式为 ‘YYYY-MM-DD HH:MM:SS’范围从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。TIMESTAMP格式为 ‘YYYY-MM-DD HH:MM:SS’范围从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC。通常用于自动记录行的创建或修改时间。UTC 是国际通用的时间标准它的值与时区有关。YEAR4 位数字格式的年份 (YYYY)范围从 1901 到 2155或者 0000。查看所有表SHOW TABLES;查看表结构DESC user;表约束PRIMARY KEY主键不会重复不会为空UNIQUE唯一值不会重复NOT NULL不能为空DEFAULT默认值AUTO_INCREMENT自动递增创建表CREATE TABLE student( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10) NOT NULL, age TINYINT UNSIGNED, gender ENUM(Male,Female) NOT NULL, height DECIMAL(5,2), weight DECIMAL(5,2), address TEXT, email VARCHAR(255) UNIQUE, hobbies VARCHAR(500) )CREATE TABLE IF NOT EXISTS product( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(20) NOT NULL, description VARCHAR(200), price DOUBLE, publishTime DATETIME )删除表DROP TABLE student; DROP TABLE IF EXISTS student;修改表修改表名 ALTER TABLE student RENAME TO students; 新增列 ALTER TABLE student ADD publishTime DATETIME; ALTER TABLE student ADD updateTime DATETIME; 删除列 ALTER TABLE student DROP hobbies; 修改列名 ALTER TABLE student CHANGE publishTime publishDate DATE; 修改列的数据类型 ALTER TABLE student MODIFY age INT;插入数据插入单条数据 INSERT INTO product(title,description,price,publishTime) VALUES(iphone,苹果手机,123.123,2008-8-8); 插入多条数据 INSERT INTO product(title,description,price,publishTime) VALUES (huawei,华为手机,456.456,2008-8-9), (xiaomi,小米手机,789.789,2008-8-10), (vivo,vivo手机,123.123,2008-8-11);删除数据删除表中所有数据 DELETE FROM product; 删除指定条件的数据 DELETE FROM product WHERE id5; DELETE FROM product WHERE titlexiaomi;修改数据修改表中所有数据 UPDATE product SET titlehello,price666; 修改指定条件的数据 UPDATE product SET titlehello123 WHERE id12;记录更新时间ALTER TABLE product ADD updateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;修改数据后可以直接显示最新的修改数据时间。单表查询基本查询 SELECT查询所有并显示所有字段 SELECT * FROM products; 查询指定字段 SELECT title,brand,price FROM products;别名 AS使用AS关键字 SELECT title as t,brand as b,price as p FROM products; 省略AS关键字 SELECT title t,brand b,price p FROM products;查询条件 WHERE查询price小于1000 SELECT * FROM products WHERE price 1000; 查询price大于等于1000 SELECT * FROM products WHERE price 1000; 查询brand是华为 SELECT * FROM products WHERE brand华为; 查询brand不是华为 SELECT * FROM products WHERE brand!华为; 查询brand是华为且price小于1000 SELECT * FROM products WHERE brand华为 and price 1000; 查询brand是华为或price小于1000 SELECT * FROM products WHERE brand华为 or price 1000; 查询brand是华为并且price大于1000且小于2000 SELECT * FROM products WHERE brand华为 and price 1000 and price 2000; 查询brand是华为并且price大于等于1000且小于等于2000 SELECT * FROM products WHERE brand华为 and price BETWEEN 1000 AND 2000; 查询brand是华为或小米 SELECT * FROM products WHERE brand IN (华为,小米);模糊查询 LIKE%表示匹配若干个任意字符_表示匹配一个任意字符查询brand是O开头的 SELECT * FROM products WHERE brand LIKE O%; 查询中间2个字符是PP的 SELECT * FROM products WHERE brand LIKE _PP_;排序 ORDER BYASC升序DESC降序查询brand是华为按价格降序排序 SELECT * FROM products WHERE brand华为 ORDER BY price DESC;分页查询 LIMITlimit查询几条数据offset偏移几条数据查询20条数据 SELECT * FROM products LIMIT 20; 等价于上面 SELECT * FROM products LIMIT 20 OFFSET 0; 偏移10条数据查询20条数据 SELECT * FROM products LIMIT 10,20; SELECT * FROM products LIMIT 20 OFFSET 10;聚合函数avg平均值max最大值min最小值sum总和值count数量所有手机的均价 SELECT AVG(price) FROM products; 华为手机的均价 SELECT AVG(price) FROM products WHERE brand华为; 最便宜的手机 SELECT MIN(price) FROM products; 最贵的手机 SELECT MAX(price) FROM products; 总投票人数 SELECT SUM(voteCnt) FROM products; 手机总数 SELECT count(*) FROM products; 华为手机总数 SELECT count(*) FROM products WHERE brand华为;分组 GROUP BY按品牌分组 SELECT brand, COUNT(*) as count, ROUND(AVG(price),2) as avgPrice, MAX(price) as maxPrice, MIN(price) as minPrice FROM products GROUP BY brand;过滤 HAVING对分组后的数据进行过滤操作。对分组后数据进行过滤均价小于4000 SELECT brand, COUNT(*) as count, ROUND(AVG(price),2) as avgPrice, MAX(price) as maxPrice, MIN(price) as minPrice FROM products GROUP BY brand HAVING avgPrice 4000;外键 FOREIGN KEY定义品牌表CREATE TABLE IF NOT EXISTS products( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(20), price DOUBLE NOT NULL, score DECIMAL(2,1), voteCnt INT, url VARCHAR(100), pid INT, brand_id INT DEFAULT NULL, FOREIGN KEY(brand_id) REFERENCES brand(id) );定义产品表CREATE TABLE IF NOT EXISTS products( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(20), price DOUBLE NOT NULL, score DECIMAL(2,1), voteCnt INT, url VARCHAR(100), pid INT, brand_id INT DEFAULT NULL, FOREIGN KEY(brand_id) REFERENCES brand(id) // 添加外键 );单独添加外键先添加列 ALTER TABLE products ADD brand_id INT; 再添加外键 ALTER TABLE products ADD FOREIGN KEY (brand_id) REFERENCES brand (id);父子表关系部门表 CREATE TABLE department ( id INT PRIMARY KEY, name VARCHAR(255) ); 员工表 CREATE TABLE employee ( employeeId INT PRIMARY KEY, name VARCHAR(255), departmentId INT, FOREIGN KEY (departmentId) REFERENCES department(id) -- 外键关联 );员工表的departmentId依赖于部门表的id因此部门表是父表员工表是子表。外键约束行为外键约束行为当修改或删除父表的记录时数据库系统的应对策略。RESTRICT限制当更新或删除父表的记录时有的话会报错不允许更新或删除。NO ACTION无动作与RESTRICT一致是SQL标准中定义的。CASCADE级联当更新或删除父表的记录时更新那么会更新对应的记录删除那么关联的记录会被一起删除掉SET NULL置空当更新或删除父表的记录时会将对应的值设置为NULL。查看表的SQL语句 SHOW CREATE TABLE products; 删除外键 ALTER TABLE products DROP FOREIGN KEY products_ibfk_1; 添加外键和约束行为 ALTER TABLE products ADD FOREIGN KEY (brand_id) REFERENCES brand (id) ON UPDATE CASCADE ON DELETE CASCADE;多表查询笛卡尔乘积如果第一张表有100条数据第二张表有6条数据则会查询出100*6600条数据也就是笛卡尔乘积。查询所有数据笛卡尔乘积查询的数据很多是没有意义的例如苹果手机与华为品牌结合是没有意义的。这时候可以使用 where 进行条件查询有意义的数据。SELECT * FROM products,brand WHERE products.brand_idbrand.id;左连接关键字LEFT JOIN … ON …以左表为主左表的数据都会被查询出来即使右表没有匹配。SELECT * FROM products LEFT JOIN brand ON products.brand_id brand.id;右连接关键字RIGHT JOIN … ON …以右表为主右表的数据会被全部查询出来。SELECT * FROM products RIGHT JOIN brand ON products.brand_id brand.id;内连接关键字INNER JOIN … ON … 或 JOIN … ON …左右两边都有关联的数据。SELECT * FROM products INNER JOIN brand ON products.brand_id brand.id;效果和下面一样SELECT * FROM products,brand WHERE products.brand_id brand.id;inner join在2张表连接时会约束数据之间的关系决定查询的结果。where先计算笛卡尔乘积再进行where条件筛选。全连接SQL规范中全连接是使用 FULL JOIN但是MySQL中并没有对它的支持可以使用 UNION 来实现。(SELECT * FROM products LEFT JOIN brand ON products.brand_id brand.id) UNION (SELECT * FROM products RIGHT JOIN brand ON products.brand_id brand.id);多对多关系创建表学生表 CREATE TABLE IF NOT EXISTS student( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, age INT ); 课程表 CREATE TABLE IF NOT EXISTS course( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, price DOUBLE NOT NULL ); 关系表 CREATE TABLE IF NOT EXISTS student_select_course( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, FOREIGN KEY (student_id) REFERENCES student(id) ON UPDATE CASCADE, FOREIGN KEY (course_id) REFERENCES course(id) ON UPDATE CASCADE );插入数据INSERT INTO student_select_course (student_id, course_id) VALUES (1, 1); INSERT INTO student_select_course (student_id, course_id) VALUES (1, 3); INSERT INTO student_select_course (student_id, course_id) VALUES (3, 2); INSERT INTO student_select_course (student_id, course_id) VALUES (3, 3); INSERT INTO student_select_course (student_id, course_id) VALUES (3, 4);查询多条数据查询所有学生的选课情况 SELECT stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice FROM student stu LEFT JOIN student_select_course ssc ON stu.id ssc.student_id LEFT JOIN course cs ON ssc.course_id cs.id; 查询已选课的学生的选课情况 SELECT stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice FROM student stu JOIN student_select_course ssc ON stu.id ssc.student_id JOIN course cs ON ssc.course_id cs.id; 查询没有选课的学生 SELECT stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice FROM student stu LEFT JOIN student_select_course ssc ON stu.id ssc.student_id LEFT JOIN course cs ON ssc.course_id cs.id WHERE cs.id IS NULL; 查询没有学生选择的课程 SELECT stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice FROM student stu RIGHT JOIN student_select_course ssc ON stu.id ssc.student_id RIGHT JOIN course cs ON ssc.course_id cs.id WHERE stu.id IS NULL;查询单个学生课程查询指定学生的选课情况 SELECT stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice FROM student stu LEFT JOIN student_select_course ssc ON stu.id ssc.student_id LEFT JOIN course cs ON ssc.course_id cs.id WHERE stu.id 1; 使用子查询性能更好 SELECT stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice FROM (SELECT id,name FROM student WHERE id 1) stu LEFT JOIN student_select_course ssc ON stu.id ssc.student_id LEFT JOIN course cs ON ssc.course_id cs.id;说明当 student 表和 student_select_course 表很大时连接操作会消耗大量资源转对象SELECT products.id productId, products.title productTitle, products.price productPrice, products.score productScore, JSON_OBJECT(id,brand.id,name,brand.name,rank,brand.worldRank,website,brand.website) AS brand FROM products LEFT JOIN brand ON products.brand_id brand.id;转数组SELECT student.id,student.name,student.age, JSON_ARRAYAGG(JSON_OBJECT(id,course.id,name,course.name)) AS courses FROM student LEFT JOIN student_select_course ssc ON student.id ssc.student_id LEFT JOIN course ON ssc.course_id course.id GROUP BY student.id;