MySQL

-- 查询数据库列表
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE coderhub;

-- 选中数据库
USE coderhub;

CREATE TABLE users(
`name` VARCHAR(20),
age INT,
height DOUBLE
);

--插入数据
INSERT INTO users VALUES('chen',24,172);
INSERT INTO users VALUES('gao',23,162);

--查询记录
SELECT * FROM users;

--使用数据库
USE coderhub;

--查看当前正在使用的数据库
SELECT DATABASE();

--创建数据库
CREATE DATABASE bilbil;

--如果不存在就创建
CREATE DATABASE IF NOT EXISTS bilbil;

--删除数据库表
CREATE TABLE IF NOT EXISTS `user`(
`name` VARCHAR(10),
age INT,
height DOUBLE
);

--查询解构
DESC `user`;

--创建一个完整的表
CREATE TABLE IF NOT EXISTS`goods`(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(10) UNIQUE,
price DOUBLE NOT NULL,
-- 创建时间 默认值 是当前的时间
createAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--插入记录
INSERT INTO `goods`VALUES(null,'荣耀',3999,null);
INSERT INTO `goods`(`name`,price)VALUES('小米',4999);

--删除数据库表
DROP TABLE `user`;
DROP TABLE IF EXISTS `user`;

--修改表

--修改表名称
ALTER TABLE `goods`RENAME TO `products`;
-- 添加一个新的字段
ALTER TABLE `products`ADD updateAt TIMESTAMP;
ALTER TABLE `products`ADD `prblishTime`DATETIME;

--删除字段
ALTER TABLE `products`DROP `prblishTime`;

--修改字段的名称（类型也可以修改）
ALTER TABLE `products` CHANGE updateAt updateTime DATETIME;

-- 修改字段的数据类型
ALTER TABLE `products`  MODIFY updateTime TIMESTAMP;
DESC `products`;

#选中数据库
use coderhub;

#查询表
SHOW TABLES;

#查询表结构
DESC products;

#新增数据
INSERT INTO products VALUES(null,'Opple',3999,null,'2022-06-18 19:40:03');
INSERT INTO products (`name`,price)VALUES('Vivo',3999);
INSERT INTO products SET`name` = '苹果',price = 6999;
INSERT INTO products SET`name` = '红米',price = 1999;
INSERT INTO products SET`name` = '三星',price = 4999;

#删除数据
DELETE FROM products;
#精确删除
DELETE FROM products WHERE id = 13;

#修改数据
UPDATE products SET `name` = 'hauwei'WHERE id = 11;
UPDATE products SET price = '9999'WHERE id = 11;

#查询表中所有数据的所有字段 *所有字段
SELECT *FROM products;

#查询指定字段
SELECT `name`,price FROM products;

#查询指定的字段 别名
SELECT `name` as '名称',price '价格'FROM products;

# 查询价格小于1000的手机
SELECT *FROM `products`WHERE price < 1000;
# 查询价格大于等于2000的手机
SELECT *FROM `products`WHERE price > 3000;

# 价格等于3399的手机
SELECT *FROM `products`WHERE price = 3999;
# 价格不等于3399的手机
SELECT *FROM `products`WHERE price != 3999;

# && AND
SELECT *FROM  `products`WHERE `name` = '红米' && `price`<2000;
SELECT *FROM  `products`WHERE `name` = '红米' AND `price`<2000;

-- || OR
SELECT *FROM  `products`WHERE `name` = '红米' || `price`>2000;
SELECT *FROM  `products`WHERE `name` = '红米' OR`price`>2000;

SELECT * FROM `products` WHERE `name` IN('苹果','vivo');

-- 模糊查询
SELECT *FROM `products` WHERE `name`LIKE '%i%';

-- ORDER BY 排序 ASC：升序排字段；DESC：降序排字段；
SELECT *FROM `products` ORDER BY id DESC;
SELECT *FROM `products` ORDER BY id ASC;

-- 分页查询 (page-1) * num    LIMIT 数量 OFFSET 偏移值
SELECT * FROM `products` LIMIT 2 OFFSET 0;
SELECT * FROM `products` LIMIT 2 OFFSET 2;
SELECT * FROM `products` LIMIT 2 OFFSET 4;
-- LIMIT 偏移值,数量
SELECT * FROM `products` LIMIT 0,2;
SELECT * FROM `products` LIMIT 2,2;
SELECT * FROM `products` LIMIT 4,2;

# 聚合函数
# 平均数
SELECT AVG(price) FROM `products`;

-- 最大值
SELECT Max(price) FROM `products`;

-- 最小值
SELECT Min(price) FROM `products`;

-- 求和
SELECT SUM(price) FROM `products`;

-- 记录数
SELECT COUNT(*) FROM `products`;

CREATE TABLE IF NOT EXISTS `user`(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(10),
age INT,
sex CHAR(2) DEFAULT '男',
height DOUBLE
);

INSERT INTO `user` VALUES(null,'张三',18,'男',188);
INSERT INTO `user` VALUES(null,'李四',18,'女',177);
INSERT INTO `user` VALUES(null,'王五',18,'男',168);
INSERT INTO `user` VALUES(null,'赵六',18,'女',170);
INSERT INTO `user` VALUES(null,'马奇',18,'男',155);
INSERT INTO `user` VALUES(null,'周扒皮',18,'女',160);
INSERT INTO `user` VALUES(null,'王麻子',18,'男',170);

# Group By 分组
SELECT sex, COUNT(*) FROM `user` GROUP BY `sex`;

SELECT sex '性别', COUNT(*) '数量' , AVG( height ) avgHeight,MAX( height) '最大身高',MIN( height)'最小身高' FROM`user` GROUP BY`sex`;
    
# HAVING 对分组的结果再进行筛选 
    SELECT sex '性别',COUNT(*) '数量',AVG( height ) avgHeight,MAX( height ) '最大身高',MIN( height ) '最小身高' FROM `user`GROUP BY
    `sex` HAVING avgHeight > 170;