-- 查询数据库列表
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;