MYSQL语句

查询数据库列表

SHOW DATABASES;

– 创建数据库

CREATE DATABASE corderhub;

– 选中数据库

USE corderhub;

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

– 插入数据

INSERT INTO users VALUES('admin',18,1.88);
INSERT INTO users VALUES('admin123',20,1.78);

– 查询记录

SELECT * FROM users;

– 查询所有的数据库

SHOW DATABASES;

– 使用数据库

USE corderhub;

– 查看当前正在使用的数据库

SELECT DATABASE();

– 创建数据库

CREATE DATABASE bilibili; 

– 如果不存在就创建

CREATE DATABASE IF NOT EXISTS bilibili;

–删除数据库

DROP DATABASE corderhub;

–如果存在就删除

DROP DATABASE IF EXISTS corderhub;

– 选中bilibili

USE bilibili;

– 查询选中数据库下的所有表

SHOW TABLES;

–创建数据库表

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,'华为',4999.5,null);
INSERT INTO `goods`(`name`,price) VALUES('小米',4999.5);

– 删除数据库表

DROP TABLE `user`;
DROP TABLE IF EXISTS `user`;

– 修改表名称

ALTER TABLE `goods` RENAME TO `products`;
-- 添加一个新的字段
ALTER TABLE `products` ADD updateAt TIMESTAMP;
ALTER TABLE `products` ADD `publishTime` DATETIME;

–删除字段

ALTER TABLE `products` DROP `publishTime`;
-- 修改字段的名称(类型也可以修改)
ALTER TABLE `products` CHANGE updateAt updateTime DATETIME;
-- 修改字段的数据类型
ALTER TABLE `products`  MODIFY updateTime TIMESTAMP;
DESC `products`;

–选中数据库

USE bilibili;

–查询表

SHOW TABLES;

–新增数据

INSERT INTO products VALUES(null,'Opple',3999,null,'2022-06-16 09:07:22');
INSERT INTO products(`name`,price) VALUES('vivo',3999);
INSERT INTO products SET `name` = '苹果',price=4999;
INSERT INTO products SET `name` = 'huawei',price=4999;
INSERT INTO products SET `name` = 'sanxing',price=4999;

–删除数据

DELETE FROM products;

–精确删除

DELETE FROM products WHERE id = 9;

–修改数据

UPDATE products SET `name` = '苹果' WHERE id = 7;

–查询表中所有数据的所有字段

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 >= 2000;
# 价格等于3399的手机
SELECT * FROM `products` WHERE price = 3399;
# 价格不等于3399的手机
SELECT * FROM `products` WHERE price != 3399;

– && 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 ) '平均身高',
	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;