MySQL数据库记录总结

MySQL数据库记录总结

SQL分类

  • DDL数据定义语言:如:操作数据库,操作表
  • DML数据操纵语言:如:对表中的记录操作增删改查
  • DQL数据查询语言:如:对表中数据的查询操作
  • DCL数据控制语言:如:对用户权限的设置

DDL数据操作数据库

创建数据库

create database 数据库名

创建day01数据库

CREATE DATABASE day01;

查看所有数据库

SHOW DATABASES;

删除数据库

DROP DATABASE day01;

DDL操作表

创建表

create table 表名(
   列名  类型  [约束],
   列名  类型  [约束]
   ...
);

数值类型

  • 整型系列:xxxInt
    MySQL数据库记录总结_第1张图片
  • 浮点型系列:float,double
  • 位类型:bit

日期时间类型

日期时间类型:year, date, datetime, timestamp
MySQL数据库记录总结_第2张图片

字符串类型

MySQL中提供了多种对字符数据的存储类型,不同的版本可能有所差异。常见的有:
char,varchar,xxtext,binary,varbinary,xxblob,enum,set等等
MySQL数据库记录总结_第3张图片

约束

MySQL数据库记录总结_第4张图片
例子:
创建一张学生表,字段id,姓名,性别,id自动增长,姓名不能重复,性别不能为空默认值是男

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    ‘name’ VARCHAR(50) UNIQUE,
    gender VARCHAR(10) NOT NULL DEFAULT '男'
);

查看表

查看所有的表

show tables;

查看表结构

desc 表名;

修改表

alter table 表名称 addcolumn】 字段名 数据类型;

删除表

drop table 表名;

DML操作表记录-增删改

创建一张商品表product

insert插入操作

  • 插入表中指定列
insert into 表名(,...) values(,...);
INSERT INTO product (pname,price) VALUES ("iphone12",4099);
INSERT INTO product (pname,price) VALUES ("iphone13",5999);
  • 插入所有列,如果某一列不想插入值需赋值null
insert into 表名 values(,...);
INSERT INTO product VALUES(NULL,"红米K40",4099,10);
INSERT INTO product VALUES(NULL,"华为P50",5999,20);

update更新操作

update 表名 set=,=[where 条件]
UPDATE product SET price=1999 WHERE pname='iphone12'

delete删除操作

delete from 表名 [where 条件]
DELETE FROM product WHERE pname='iphone12'

truncate 把表直接DROP掉,然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快
删除分为物理删除(删除后数据不可恢复),逻辑删除(修改字段的状态 isDel 0已删除 1未删除)

DQL数据查询

简单查询

select 字段名 from 表名 [where 条件]
select * from product;

去重查询 distinct

select distinct 字段名 from 表名;
select distinct pname from product;

别名查询

select 列名 as 别名 fromas 别名
select pname as '商品名称',price as '商品价格' from product

运算查询(+,-,*,/,%等)

把商品名,和商品价格+10查询出来

SELECT pname AS '商品名',price+10 AS '商品价格' FROM product

条件查询

  • 比较运算符
大于 >
小于 <
大于等于 >=
小于等于 <=
等于 =不能用于null判断
不等于 !=或<>
安全等于 <=>可以用于null值判断
  • 逻辑运算符
逻辑与 && 或and
逻辑或 在这里插入图片描述或or
逻辑与 !或not
  • 范围
    区间范围:between … and …
    not between … and …
    集合范围:in(x,y,z)
    not in (x,y,z)

练习

查询商品价格大于3000的商品

SELECT * FROM product WHERE price>3000

查询pid不等于1的商品

SELECT * FROM product WHERE pid<>1

查询价格在3000到5000之间的商品(包括3000和5000)

SELECT * FROM product WHERE price BETWEEN 3000 AND 5000

查询pid在1,5,7,15范围内的商品

SELECT * FROM product WHERE pid IN(1,5,7,15)

查询商品名以iPho开头的商品(iPhone系列)

SELECT * FROM product WHERE pname LIKE 'iPho%'

排序查询

关键字Order By 升序ASC 降序DESC
数据准备

# 创建学生表(有sid,学生姓名,学生性别,学生年龄,分数列,其中sid为主键自动增长)
CREATE TABLE student(
	sid INT PRIMARY KEY auto_increment,
	sname VARCHAR(40),
	sex VARCHAR(10),
	age INT,
    score DOUBLE
);

INSERT INTO student VALUES(null,'zs','男',18,98.5);
INSERT INTO student VALUES(null,'ls','女',18,96.5);
INSERT INTO student VALUES(null,'ww','男',15,50.5);
INSERT INTO student VALUES(null,'zl','女',20,98.5);
INSERT INTO student VALUES(null,'tq','男',18,60.5);
INSERT INTO student VALUES(null,'wb','男',38,98.5);
INSERT INTO student VALUES(null,'小丽','男',18,100);
INSERT INTO student VALUES(null,'小红','女',28,28);
INSERT INTO student VALUES(null,'小强','男',21,95);

单列排序,按照某一个字段排序
以分数降序查询所有的学生

SELECT * FROM student ORDER BY score DESC

组合排序,按照多个字段排序
以分数降序查询所有的学生, 如果分数一致,再以age降序

SELECT * FROM student ORDER BY score DESC, age DESC

分组查询和聚合函数和Having

分组GROUP BY
聚合函数列表

聚合函数 作用
max(列名) 最大值
min(列名) 最小值
avg(列名) 平均值
count(列名) 统计有多少条记录
sum(列名) 总和

Having分组后筛选条件
根据性别分组, 统计每一组学生的总人数> 5的(分组后筛选)

SELECT sex, count(*) FROM student GROUP BY sex HAVING count(*) > 5

根据性别分组,只统计年龄大于等于18的,并且要求组里的人数大于4

SELECT sex '性别',COUNT(sid) '总人数' FROM student WHERE age >= 18 GROUP BY sex HAVING COUNT(sid) > 4

where和having的区别

子名 作用
where 子句 对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,即先过滤再分组。2) where后面不可以使用聚合函数
having字句 having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤。2) having后面可以使用聚合函数

分页查询

select * from student limit 1 ,2

1 表示的是跳过的数据条数,即跳过1条数据
2 表示的是要查询的数据条数,即查询2条数据

MySQL多表

准备数据

-- 创建一张分类表(类别id,类别名称.备注:类别id为主键并且自动增长)
CREATE TABLE t_category(
		cid INT PRIMARY KEY auto_increment,
		cname VARCHAR(40)
);
INSERT INTO t_category values(null,'手机数码');
INSERT INTO t_category values(null,'食物');
INSERT INTO t_category values(null,'鞋靴箱包');


-- 创建一张商品表(商品id,商品名称,商品价格,商品数量,类别.备注:商品id为主键并且自动增长)

CREATE TABLE t_product(
		pid INT PRIMARY KEY auto_increment,
		pname VARCHAR(40),
		price DOUBLE,
		num INT,
		cno INT
);

insert into t_product values(null,'苹果电脑',18000,10,1);
insert into t_product values(null,'iPhone8s',5500,100,1);
insert into t_product values(null,'iPhone7',5000,100,1);
insert into t_product values(null,'iPhone6s',4500,1000,1);
insert into t_product values(null,'iPhone6',3800,200,1);
insert into t_product values(null,'iPhone5s',2000,10,1);
insert into t_product values(null,'iPhone4s',18000,1,1);

insert into t_product values(null,'方便面',4.5,1000,2);
insert into t_product values(null,'咖啡',10,100,2);
insert into t_product values(null,'矿泉水',2.5,100,2);

insert into t_product values(null,'法拉利',3000000,50,null);

-- 给 商品表添加外键
ALTER TABLE t_product ADD FOREIGN KEY(cno) REFERENCES t_category(cid);

笛卡尔积

内连接

查询所有类别下的商品信息

SELECT * from t_category c INNER JOIN t_product p ON c.cid = p.cno

左外连接

查询所有类别下的商品信息,就算该类别下没有商品也需要将该类别的信息展示出来

SELECT * FROM t_category c LEFT JOIN t_product p ON c.cid = p.cno

右外连接

查询所有商品所对应的类别信息

SELECT * FROM t_category c RIGHT JOIN t_product p ON c.cid = p.cno

union全外连接

# 用左外的A union 右外的B
SELECT * FROM t_category c LEFT JOIN t_product p ON c.cid = p.cno
union
SELECT * FROM t_category c RIGHT JOIN t_product p ON c.cid = p.cno

你可能感兴趣的