mysql操作详解

时间：2024-10-18 16:29:25

mysql数据库基础

数据库介绍

  1. 存储数据的仓库(DataBase)
  2. 作用:永久性存储数据
  3. 为啥要学数据库
    a. 学过数组和集合,都可以存储数据,但是数组和集合都是临时存储,程序运行完毕,数据消失了
    b. 所以我们可以将数据放到数据库的表中,用数据库特有的sql语句,快速定位到对应的单元格中,对此单元格中的数据执行进行修改,也方便添加,删除,查询

sql语句

  1. sql语言:操作数据库的语言,所有关系型数据库的语句标准,是一种语法规则
  2. 但是不同的关系型数据库语法在遵守sql语言的前提下,也有个别不同的语法
    这一类不同的语法,叫做数据库方言

通用语法

  • SQL语句可以单行或多行书写，以分号结尾
  • 可使用空格和缩进来增强语句的可读性:基本上一个单词就一个空格
  • MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
  • 同样可以使用/**/的方式完成注释
    例子 
/*
     我是一个注释
    */
    #我也是一个注释
   -- 我也是一个注释

sql中的数据类型

类型名称 说明
int（整数长度） 整数类型
double 小数类型
decimal（m,d） 指定整数位与小数位长度的小数类型
date 日期类型，格式为yyyy-MM-dd，包含年月日，不包含时分秒 2020-01-01
datetime 日期类型，格式为 YYYY-MM-DD HH:MM:SS，包含年月日时分秒 到9999年
timestamp 日期类型，时间戳 从1970年到2038年
varchar（字符串长度） 文本类型， M为0~65535之间的整数

Navicat创建数据库

新建数据库

  1. 在localhost上右键选择创建数据库
    在这里插入图片描述
  2. 填写数据库名和字符集和排序规则
    a. 数据库名：随便起一个，
    b. 字符集：选择utf8的。（mb3和mb4区别就是mb4能表示更多的字符，如果用不到扩容去选择utf8mb4会有点浪费空间）
    c. 排序规则: 选择general_ci结尾的（ci：大小写不敏感、cs：区分大小写、bin：以二进制存储并且区分大小写）
    在这里插入图片描述

创建表

方法一 写sql语句
/*
	关键字：create TABLE
	语句：
	CREATE TABLE 表名(
		列名 数据类型(长度)[约束],
		列名 数据类型(长度)[约束]
	)
	注意：
		a. []代表可写可不写
		b. varchar 类型必须指定长度
		c. 每个列写完之后用, 隔开
		d. 如果所有要定义的列都定义完了，后面就不用加,
*/

CREATE TABLE category(
	cid INT,
	cname VARCHAR(10)
);
方法2 可视化操作

点击表，右键—>新建表
在这里插入图片描述
在这里插入图片描述

查看表

/*
查看所有表
*/
SHOW TABLES;

# 查看表结构
DESC 表名;

删除表

--  删除表
DROP TABLE 表名
DROP TABLE category

也可以可视化删除
点击表 ---->右键
在这里插入图片描述

修改表

添加列
alter table 表名 add 列名 类型(长度) [约束];
作用：添加列. 
ALTER TABLE category ADD `desc` VARCHAR(20)
修改列的类型,长度及约束.
alter table 表名 modify 列名 类型(长度) [约束];
  作用：修改列的类型,长度及约束.
  ALTER TABLE category MODIFY `desc`VARCHAR(40)
修改列名
 alter table 表名 change 旧列名 新列名 类型(长度) [约束]; 
  作用：修改列名.
  
  ALTER TABLE category CHANGE `desc` `xiaoming` VARCHAR(20)
修改表_删除列.
 alter table 表名 drop 列名; 
  作用：修改表_删除列.
  
ALTER TABLE category DROP `xiaoming`
修改表名
 rename table 表名 to 新表名;
RENAME TABLE category TO kind;

注意：以上操作都可以使用可视化操作

插入数据

  1. 关键字: insert into values
  2. 语法:
    a.insert into 表名 (列名,列名) values (值,值) -> into可以省略
    b.insert into 表名 (列名,列名) values (值,值),(值,值),(值,值)->一次添加多条数据
    c.insert into 表名 values (值,值) -> 不指定列名,后面添加的值要覆盖所有列
INSERT INTO category(cid,cname) VALUES(1,'香菜');
--  可以省略into
INSERT category(cid,cname) VALUES(2,'芹菜')

-- 批量增加
INSERT INTO category(cid,cname) VALUES(3,'白菜'),(4,'青菜'),(5,'辣椒')

--  可以不写列名
INSERT INTO category VALUES(6,'豌豆'),(7,'地瓜')

删除表数据

  1. 关键字: delete from
  2. 语法:
    a.delete from 表名 -> 全部删除
    b.delete from 表名 where 条件
DELETE FROM category;
-- 删除cid为1的记录
DELETE FROM category WHERE cid = 1;

-- 删除cid>=5的记录
DELETE FROM category WHERE cid >=5;
-- 删除cid不等于3的记录
DELETE FROM category WHERE cid!=3;

DELETE FROM category WHERE cid<>3;

DELETE FROM category WHERE (cid=3);

修改数据

1.关键字:update set
2.语法:
a.update 表名 set 列名 = 新值 -> 将所有数据全部改成新值
b.update 表名 set 列名 = 新值 where 条件

/*
修改数据
*/

-- 将表中的香菜改成蔬菜
UPDATE category SET cname = '蔬菜' WHERE cname = '香菜';

-- 将cid为6的数据改成肉
UPDATE category SET cname = '肉' WHERE cid=6;

-- 将cid不等于1的canme都改成面粉
UPDATE category SET cname = '面粉' WHERE cid!=1;

约束

约束指的是对指定列的数据进行约束

主键约束

1.关键字: primary key
2.特点:
a.每个表都应该有一个主键
b.主键列的数据不能重复,唯一
c.主键列的数据不能为空(不能为NULL)

示例：

CREATE TABLE product(
	pid INT PRIMARY KEY,
	pname VARCHAR(30),
	price INT 
);

联合主键

概述：多个列合称为一个主键
特点： 多个列中不能有完全重复的数据；
不能为null

示例：

CREATE TABLE persons(
  firstname VARCHAR(10), -- 名
  lastname VARCHAR(10), -- 姓
  address VARCHAR(10), -- 地址
  city VARCHAR(10), -- 城市
  PRIMARY KEY (firstname,lastname)
);

INSERT INTO persons(firstname,lastname,address,city) VALUES('小明','王','北京市朝阳区','北京市')

INSERT INTO persons(firstname,lastname,address,city) VALUES('小明','刘','北京市朝阳区','北京市')
--  主键不能为null
INSERT INTO persons(firstname,lastname,address,city) VALUES('小明',null,'北京市朝阳区','北京市')

简单查询

前：创建表
create table product_one(
	pid int primary key,
	pname varchar(20),
	price double
);
INSERT INTO product_one(pid,pname,price) VALUES(1,'联想',5000);
INSERT INTO product_one(pid,pname,price) VALUES(2,'海尔',3000);
INSERT INTO product_one(pid,pname,price) VALUES(3,'雷神',5000);
INSERT INTO product_one(pid,pname,price) VALUES(4,'JACK JONES',800);
INSERT INTO product_one(pid,pname,price) VALUES(5,'真维斯',200);
INSERT INTO product_one(pid,pname,price) VALUES(6,'花花公子',440);
INSERT INTO product_one(pid,pname,price) VALUES(7,'劲霸',2000);
INSERT INTO product_one(pid,pname,price) VALUES(8,'香奈儿',800);
INSERT INTO product_one(pid,pname,price) VALUES(9,'相宜本草',200);
INSERT INTO product_one(pid,pname,price) VALUES(10,'面霸',5);
INSERT INTO product_one(pid,pname,price) VALUES(11,'好想你枣',56);
INSERT INTO product_one(pid,pname,price) VALUES(12,'香飘飘奶茶',1);
INSERT INTO product_one(pid,pname,price) VALUES(13,'果9',1);
– 查询product_one所有数据
SELECT * FROM product_one
– 查询product_one 所有数据,展示pname和pid
SELECT pname,pid FROM product_one
去重复值
 关键字: distinct(列名)*/
	SELECT DISTINCT(price) FROM product_one
给列中的数据做计算
查询所有数据,给price列中所有的数据+100
SELECT pid,pname,price+100 FROM product_one
给列和表取别名
/*
  给列和表取别名
  
  as 别名
  
  as可以省略
*/
SELECT pid,pname,price+100 `newprice` FROM product_one

条件查询

语法

1.语法:
select 列名,列名 from 表名 where 条件

1.语法:
  select 列名,列名 from 表名 where 条件
比较运算符 < <= >= = <> 大于、小于、大于(小于)等于、不等于
BETWEEN …AND… 显示在某一区间的值(含头含尾)
字段 IN(set) 显示在in列表中的值，例：price in(100,200) 查询id为1,3,7的商品: id in(1,3,7)
列名 LIKE ‘张pattern’ 模糊查询，Like语句中，% 代表零个或多个任意字符，_ 代表一个字符， 例如：first_name like '_a%';
比如:查询姓张的人:name like ‘张%’
查询商品名中带香的商品: pname like ‘%香%’
查询第二个字为想的商品: like ‘想%'
查询商品名为四个字的商品:like '___’
IS NULL 判断是否为空
逻辑运行符 and (与) 多个条件同时成立 全为true,整体才为true
or(或) 多个条件任一成立 有真则真
not(非) 不成立，例：where not(salary>100);
查询商品名为’联想’的商品所有信息
SELECT * FROM product_one WHERE pname='联想';
查询价格为800的商品
SELECT *FROM product_one WHERE price=800;
查询商品价格大于60元的所有商品信息
SELECT *FROM product_one WHERE price>60;
查询商品价格在200-1000之间的所有商品信息 大的在前，小的在后
SELECT *FROM product_one WHERE price BETWEEN 200 AND 1000;
查询商品价格是200或者800的商品
SELECT * FROM product_one WHERE price =200 OR price=800;
SELECT *FROM product_one WHERE price IN(200,800);
查询以’香’开头的商品
SELECT *FROM product_one WHERE pname LIKE '香%';
查询含有’霸’的商品
SELECT *FROM product_one WHERE pname LIKE '%霸%';
– 查询商品名为NULL的
SELECT *FROM product_one WHERE pname IS NULL;
查询商品名不为NULL的
SELECT *FROM product_one WHERE pname IS NOT NULL;

排序查询

1.关键字: order by
2.语法:
select 列名 from 表名 order by 排序字段 desc|asc
3.desc和asc
desc(降序)
asc(升序) -> order by 默认
4.问题:先查询,还是先排序
先查询,最后排序

书写sql语句关键字的顺序
select
from
where
group by
having
order by
执行顺序:
from
where
group by
having
select
order by
先定位到要查询哪个表,然后根据什么条件去查,表确定好了,条件也确定好了,开始利用select查询
查询得出一个结果,在针对这个结果进行一个排序

使用价格排序(降序)
SELECT pname,price FROM product_one ORDER BY price DESC;
SELECT pname,price FROM product_one ORDER BY price;
显示商品的价格(去重复),并排序(降序)
SELECT DISTINCT(price) FROM product_one ORDER BY price DESC;

聚合查询

1.注意:聚合查询都是针对列的,纵向查询;聚合查询需要用到聚合函数
2.语法:
select 聚合函数(列名) from 表名 where 条件
3.聚合函数:
sum(列名):对指定列进行求和
avg(列名):对指定列进行求平均值
max(列名):对指定列求最大值
min(列名):对指定列求最小值
count(列名):求总记录数
4.集合函数结果为:单值

--  查询所有表数
SELECT *FROM product_one;
-- 查询所有表总记录数
SELECT COUNT(*) FROM product_one
--  查询pid 列总记录数
SELECT COUNT(pid) FROM product_one;
--  可以统计待null的数据
SELECT COUNT(0) FROM product_one;

SELECT COUNT(1) FROM product_one;

--  查询所有商品价格总和
SELECT SUM(price) FROM product_one;
-- 查询商品的最高价格以及最低价格
SELECT MAX(price),MIN(price) FROM product_one;

--  查询pid为1,3,7 商品的价格平均值
SELECT AVG(price) FROM product_one WHERE pid IN(1,3,7);

分组查询

1.关键字:group by
2.语法:
select 聚合函数(列名) from 表名 group by 分组列 having 条件
3.注意:
a.分组查询都是和聚合函数一起使用
4.分组小技巧:
观察以哪一组分组展示
相同的合并为一组展示
不同的单独为一组展示
5.having和where的区别:
a.相同点:都是条件筛选
b.不同点:
where在分组之前执行
having在分组之后执行

SELECT pname,SUM(price) FROM product_one GROUP BY pname;


--  查询相同商品的价格总和,再展示出价格总和大于等于2000的商品

SELECT pname,SUM(price) FROM product_one WHERE price >= 2000 GROUP BY pname;

分页查询

1.语法:
select * from 表名 limit m,n
2.字母代表啥:
m:每页的起始位置
n:每页显示条数
3.小技巧:
我们将整个表的每一条数据进行编号,从0开始
4.每页的起始位置快速算法:
(当前页-1)*每页显示条数
5.其他分页参数:
a.每页的起始位置:
(当前页-1)每页显示条数
b.int curPage = 2; – 当前页数
c.int pageSize = 5; – 每页显示数量
d.int startRow = (curPage - 1) * pageSize; – 当前页, 记录开始的位置(行数)计算
e.int totalSize = select count() from products; – 记录总数量
f.int totalPage = Math.ceil(totalSize * 1.0 / pageSize); – 总页数
总页数 = (总记录数/每页显示条数)向上取整

-- 第一页
SELECT * FROM product LIMIT 0,5;

-- 第二页
SELECT * FROM product LIMIT 5,5;

-- 第三页
SELECT * FROM product LIMIT 10,5;

多表之间的关系

一对多的表创建外键约束

分析：假设有两张表 商品分类表和商品信息表

表关系：

  • 从分类表往信息表看，一个分类对应多个商品—> 一对多
  • 从信息表往分类表看，多个商品对应一个分类----> 多对一
    谁是主表，谁是从表
    分类表的数据牵制商品表的数据–>分类表为主。商品表为从表
CREATE TABLE category (
	cid VARCHAR(32) PRIMARY KEY ,
	cname VARCHAR(50)
);
CREATE TABLE products(
	pid VARCHAR(32) PRIMARY KEY,
	pname VARCHAR(50),
	price DOUBLE,
	category_id VARCHAR(32) -- 外键  存储的是主表的主键内容
);

外键：表之间关联

格式:alter table 从表 add [constraint 外键名称(自定义)] foreign key 从表(外键列名) references 主表(主键列名)   

ALTER TABLE products ADD CONSTRAINT cp FOREIGN KEY products(category_id) REFERENCES category(cid)

添加数据

--  给主表添加数据
INSERT INTO category(cid,cname) VALUES (1,'蔬菜'),(2,'水果'),(3,'服装'),(4,'肉蛋');
--  给从表添加数据
INSERT INTO products(pid,pname,price,category_id) VALUES(1,'黄瓜',120,1)
INSERT INTO products (pid,pname,price,category_id) VALUES (2,'西红柿',4,1);
INSERT INTO products (pid,pname,price,category_id) VALUES (3,'苹果',8,2);
INSERT INTO products (pid,pname,price,category_id) VALUES (4,'牛仔裤',99,3);
INSERT INTO products (pid,pname,price,category_id) VALUES (5,'鸡蛋',2,4);
多对多的表创建外键约束

表关系

  • 从商品表往订单看，一个商品对应多个订单–> 一对多
  • 从订单表往商品表看，一个订单对应多个商品–>一对多
  • 结论：多对多
    问题：谁是主表谁是从表
    结论：中间表
 # 订单表 -> 主表
  CREATE TABLE `orders`(
  `oid` VARCHAR(32) PRIMARY KEY ,
  `totalprice` DOUBLE 	#总计
  );
   
#订单项表->中间表->从表
CREATE TABLE orderitem(
  pid VARCHAR(50),-- 商品id->外键
  oid VARCHAR(50)-- 订单id ->外键
);

外键sql规范

alter table 从表 add [constraint 外键名称(自定义)] foreign key 从表(外键列名) references 主表(主键列名)

/*
  先给products和orderitem建立外键约束
  主表:products
  从表:orderitem
 
*/
ALTER TABLE orderitem ADD CONSTRAINT op FOREIGN KEY orderitem(pid) REFERENCES products(pid);

/*
  给orders和orderitem建立外键约束
  主表:orders
  从表:orderitem
*/
ALTER TABLE orderitem ADD CONSTRAINT oo1 FOREIGN KEY orderitem(oid) REFERENCES orders(oid);

相关文章