MySQL8.0（ddl.dml和dql 函数&&条件查询&&分组查询&&子查询）

最新推荐文章于 2024-03-12 21:35:32 发布

crraxx

最新推荐文章于 2024-03-12 21:35:32 发布

阅读量249

点赞数 2

分类专栏： MySQL java 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/crraxx/article/details/113269755

版权

java 同时被 2 个专栏收录

35 篇文章 0 订阅

订阅专栏

MySQL

10 篇文章 0 订阅

订阅专栏

mySQL 8.0

注释

#单行注释

-- 单行注释（注意用--做单行注释时需要加空格）

/*
多行注释
*/

DDL 数据（结构）定义语言

/*DDL 数据（结构）定义语言（Date Definition Language）
DDL 也属于SQL语言，只是按功能划分
执行时选中 点击执行查询 选中左侧树形目录root 点击刷新对象浏览器即可
数据库名一旦给定不能更改*/

数据库

#创建数据库
CREATE DATABASE IF NOT EXISTS school_db CHARSET utf8
#删除数据库
drop database school_db
#修改数据库字符编码
ALTER DATABASE school_db CHARSET utf8

表

/*
数据库中数据以表为单位来存储
设计表：表名（存储的信息，一个表存储一类信息）
字段：一列
完整记录：一行
约束：
主键约束：一个表中只有一个主键，不能为空，不可以重复
唯一约束：一个表中可以有多个，可以为空
*/

#创建表（不带约束条件）
CREATE TABLE t_student (
num INT,
NAME VARCHAR(10),
sex  CHAR(1),
birthday DATE,
grade INT,
score DOUBLE (4,1),
mobile VARCHAR(11),
reg_time DATETIME
);
#删除表
DROP  TABLE t_student


#创建表（带约束条件）
CREATE TABLE t_student (
num INT PRIMARY KEY COMMENT '主键',
NAME VARCHAR(10) NOT NULL COMMENT '姓名',
sex  CHAR(1) DEFAULT '男' COMMENT '性别',
birthday DATE COMMENT '出生日期',
grade INT COMMENT '年级',
score DOUBLE (4,1) CHECK (score >=0 AND score <= 100) COMMENT '成绩',
mobile VARCHAR(11) UNIQUE COMMENT '手机号',
reg_time DATETIME COMMENT '注册时间'
);
#删除表
DROP TABLE t_student

先创建不带约束条件的表，在需要时按需添加。

添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(列名)

删除主键约束

ALTER TABLE 表名 DROP PRIMARY KEY 设置自动增长
ALTER TABLE 表名 MODIFY 列名类型 AUTO_INCREMENT; 删除自动增长
ALTER TABLE users MODIFY 列名类型 ; 设置不能为空
ALTER TABLE 表名 MODIFY 列名类型 NOT NULL;

设置为空

ALTER TABLE 表名 MODIFY 列名类型 NULL; 添加唯一约束 ALTER TABLE
表名 ADD CONSTRAINT 约束名 UNIQUE (列名)

删除唯一约束

ALTER TABLE 表名 DROP INDEX 约束名; 添加检查约束 ALTER TABLE 表名 ADD
CONSTRAINT 约束名 CHECK(条件)

删除检查约束

ALTER TABLE 表名 DROP CHECK 约束名;

添加列 ALTER TABLE 表名 ADD 列名数据类型 ALTER TABLE 表名 ADD 列名数据类型
FIRST ALTER TABLE 表名 ADD 列名数据类型 AFTER 列名

删除列 ALTER TABLE 表名 DROP 列名

修改列名 ALTER TABLE 表名 CHANGE 旧列名新列名数据类型

修改列的数据类型 ALTER TABLE 表名 MODIFY 列名新数据类型

CREATE TABLE t_student (
num INT,
NAME VARCHAR(10),
sex  CHAR(1),
birthday DATE,
grade INT,
score DOUBLE (4,1),
moblie VARCHAR(11),
reg_time DATETIME
)
	-- 添加删除主键约束       
    ALTER TABLE  t_student  ADD  PRIMARY KEY(num) 
    ALTER TABLE  t_student  DROP  PRIMARY KEY
    
    -- 设置/删除自动增长 
    ALTER TABLE  t_student  MODIFY  num INT  AUTO_INCREMENT;
    ALTER TABLE  t_student  MODIFY    num  INT ;
   
    -- 设置不能/能为空
    ALTER TABLE   t_student  MODIFY  sex  CHAR  NOT NULL;
    ALTER TABLE t_student MODIFY   sex    CHAR   NULL;

    -- 添加删除唯一约束
    ALTER TABLE t_student  ADD CONSTRAINT phone_number UNIQUE(moblie)
    ALTER TABLE t_student DROP INDEX phone_number
    
   
    -- 添加/取消检查约束
    ALTER TABLE  t_student ADD CONSTRAINT score_check CHECK(score>=0 AND score<=100)
    ALTER TABLE  t_student  DROP CHECK  score_check;
   
 
   -- 添加列
   ALTER  TABLE  t_student  ADD   address VARCHAR(15)
    -- 添加到第一列
   ALTER  TABLE  t_student  ADD   address VARCHAR(15) FIRST
   -- 添加到某列之后
   ALTER  TABLE  t_student  ADD   address VARCHAR(15) AFTER moblie
   
   -- 删除列
   ALTER TABLE  t_student DROP address
   
   
   -- 修改列名
   ALTER TABLE    t_student   CHANGE    moblie   phone   VARCHAR(11)
   
   -- 修改列的数据类型
   ALTER TABLE   t_student  MODIFY  SCORE  INT
   
    -- 只是复制表结构,不包含数据
    CREATE TABLE stu LIKE t_student

DML数据操纵语言

/*
数据操纵语言DML（Data Manipulation Language）
常用语句： insert,delete,update 
*/

/*
插入数据
 方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n);
 方式2: INSERT INTO 表名 set 列名1=值1,..列名n=值n;
 方式3: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n);
 方式4:INSERT INTO`t_student` 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)
*/
INSERT INTO stu (num,NAME,sex,birthday,grade,score,phone,address,reg_time) 
VALUES(001,'张三','女','2005-1-1',1,96,'18200000','陕西宝鸡',NOW());

INSERT INTO stu(num,NAME,sex,birthday,grade,score,phone,reg_time) 
VALUES(002,'tom',"男",'2000-10-2',1,82,'1222222',NOW())

INSERT INTO stu 
SET num=003,NAME='李四',sex='男',birthday='2000-10-3',grade=2,score=90,reg_time=NOW()

INSERT INTO stu (num,NAME,sex,birthday,grade,score,phone,reg_time)
VALUES(004,'Jery',"男",'2000-10-2',1,82,'1424522',NOW()),
(005,'Tom',"男",'2000-10-2',1,82,'1522222',NOW()),
(006,'tom',"男",'2000-10-2',1,82,'1522222',NOW());

--  表名后不描述列名,表示向表中所有的列插入数据值的数量需要与列的数量相匹配
INSERT INTO stu  SELECT * FROM t_student    -- 备份表，只是复制过来表结构，但数据并未复制过来

ALTER TABLE t_student MODIFY num INT AUTO_INCREMENT -- 将主键设置为自增

-- 修改表数据
UPDATE stu SET NAME = '李四' sex = '女'
UPDATE stu SET NAME='李四' sex='女' WHERE num=3
-- 修改时要注意添加条件，条件不当时会导致数据修改出现问题
-- 一般主键作为条件，主键在数据库中会添加索引，查找方便

-- 删除语句
DELETE
FROM stu  
WHERE num=6

-- 清空表数据
TRUNCATE TABLE stu

DQL 数据查询语言

/*
查询是使用频率最高的语句
查询操作不会改变表中的数据
对查询结果的处理1.关键字2. 函数（单行函数，字符，数字，日期，分组函数）
select 需要查询的结果 from 表 where 条件 [分组] [分组后条件] 排序 数量限制
*/

关键字

-- 查询结果的处理

-- 查询结果是常量
SELECT 100;

-- 查询结果是表达式
SELECT 10+10;
SELECT grade +1 FROM stu
 
-- 函数
SELECT VERSION()-- 查看数据库版本号
SELECT NOW()-- 查看当前时间 年月日时分秒

-- 查询表数据
SELECT*
FROM stu

-- 查询特定列,减少代码冗余
SELECT  num,NAME
FROM stu 

-- 去除重复数据（针对查询出来的结果去查） 重复是指多行数据的所有列相同 distince
SELECT sex FROM stu
SELECT DISTINCT sex FROM stu

-- 算术运算符+，-，*，/ （mysql中加号是指加法运算，没有字符串连接功能）
SELECT NAME + sex FROM stu
SELECT grade+1 FROM stu

函数

-- 单行函数（对n行数据分别进行操作，操作完成后还是n行）

-- 字符函数
-- length（列名）在utf-8编码下，以字节为单位，返回字符字节数量长度
SELECT LENGTH (NAME) FROM stu 

-- char_length(列名) 以字符为单位返回
SELECT CHAR_LENGTH(NAME) FROM stu 

-- 拼接字符串 concat（Str1，str2...）
SELECT CONCAT ('a','b','c')

-- 字符串大小写转化
-- upper()
SELECT UPPER(NAME) FROM stu
-- lower()
SELECT LOWER('ABCD')

-- 字符串截取 
-- subString(操作的字符串，开始的位置，长度)  注意：索引位置从1开始
SELECT SUBSTRING("hello",2,5)

-- 查找指定字符首次出现的位置 instr(str,指定字符)
SELECT INSTR (NAME,"e") FROM stu

-- trim(str) 去掉字符串前后的空格或子串
SELECT CHAR_LENGTH(TRIM(NAME)) FROM stu
SELECT TRIM('tom' FROM NAME) FROM stu

-- 左填充&&右填充
-- 用指定的字符实现左填充,将str填充为指定长度
SELECT LPAD(NAME,5,"1") FROM stu

-- 用指定的字符实现右填充,将str填充为指定长度
SELECT RPAD(NAME,5,"1") FROM stu

-- 替换所有的子串 replace(str,old,new)
SELECT REPLACE(NAME,"t","T") FROM stu

-- 逻辑处理
-- case then 条件 then 成立的结果 else 不成立 end
SELECT 
CASE WHEN SCORE>=90 THEN "A" ELSE "B" END FROM stu

SELECT 
num,NAME,
(CASE WHEN SCORE>=90 THEN "A"
      WHEN SCORE>=80 AND SCORE<=89 THEN 'B'
      ELSE 'C'
      END)class --  当条件很长时可以写别名进行代替
      FROM stu
      
-- ifnull（判断的列，指定的默认值）
SELECT IFNULL(phone ,'暂未登记手机号')phone
FROM stu

SELECT IFNULL (phone ,NAME)phone FROM stu

-- if (条件，结果1，结果2)
SELECT IF(score>=60,'及格','不及格') score FROM stu

-- 数学函数
SELECT ROUND(5.5)-- 四舍五入
SELECT cell(5.1) -- 向上取整
SELECT FLOOR (3.9) -- 向下取整
SELECT TRUNCATE(3.141592653,2) -- 保留指定的小数位数，直接截取
SELECT MOD(10,3)  -- 取余
SELECT RAND() -- 在0-1之间产生随机数

-- 日期函数
SELECT NOW() -- 返回系统当前时间，日期加时间
SELECT CURDATE()-- 返回系统当前日期，不包含时间
SELECT CURTIME()-- 返回当前时间，不包含日期
SELECT YEAR(reg_time) FROM stu-- 传入reg_time,自动转为year
SELECT MONTH(reg_time) FROM stu-- 传入reg_time,自动转为month
SELECT DAY(reg_time) FROM stu -- 传入reg_time,自动转为day


-- 字符串转日期
SELECT STR_TO_DATE("2021-1-26","%Y-%m-%d")

-- 日期转为指定格式的字符串
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')m 
SELECT  DATE_FORMAT(reg_time,'%Y-%m-%d')m FROM stu
SELECT COUNT(*),DATE_FORMAT(reg_time,'%Y-%m-%d')m FROM stu GROUP BY m

-- 计算两个日期之间的差
SELECT DATEDIFF(NOW(),reg_time) FROM stu

-- 组合函数，聚合函数，统计函数

/*
分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）
1.sum，avg一般用于处理数值型max，min，count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算
4.count函数的一般使用count（*）用作统计行数
*/

SELECT SUM(SCORE) FROM stu -- 总和
SELECT SUM(DISTINCT SCORE) FROM stu -- 求和之前去掉重复值

SELECT SUM(SCORE),AVG(DISTINCT SCORE) FROM stu -- 求平均值

SELECT  MAX(SCORE)  FROM stu-- 求最大值
SELECT  MIN(SCORE)  FROM stu -- 求最小值

SELECT  COUNT(*)  FROM stu -- 统计数量
SELECT  COUNT(num)  FROM stu

条件查询

-- select 结果 from 表名 where 条件

-- 条件查询：使用where字句，将不满足条件的行过滤
-- = != <> <= >= < > 注意在where语句中 = 表示相等
SELECT* FROM stu WHERE SCORE>60
SELECT* FROM stu WHERE SCORE<>60
SELECT* FROM stu WHERE SCORE!=60
SELECT* FROM stu WHERE SCORE=82

-- 逻辑运算 and or not
SELECT* FROM stu WHERE SCORE>60 AND SCORE<90
SELECT* FROM stu WHERE SCORE BETWEEN 60 AND 90

SELECT* FROM stu WHERE SCORE <0 OR score >100
SELECT* FROM stu WHERE NOT SCORE <=80


-- 模糊查询 like
-- % 代表若干个字符 _一个下划线表示一个字符 %四%:表示匹配中间有四字的
SELECT* FROM stu WHERE NAME LIKE '张%'
SELECT* FROM stu WHERE NAME LIKE '张_'
SELECT* FROM stu WHERE NAME LIKE '%四%'
SELECT* FROM stu WHERE NAME LIKE '_四_'

SELECT* FROM stu WHERE num = 1 OR num= 6 OR num = 3

-- 判断某字段的值是否属于in列表中的某一项
SELECT* FROM stu WHERE num IN (1,6,3)
SELECT* FROM stu WHERE num NOT IN (1,6,3)

-- is null 为空  is not null 不为空
SELECT* FROM stu WHERE address IS NULL
SELECT* FROM stu WHERE address IS NOT NULL


-- union && union all 将多个查询结果合并，要求：多个SQL的结果列数一致

SELECT* FROM stu WHERE sex='男'
UNION 
SELECT* FROM stu WHERE sex= '女'


/*
当列数不一致时会报错
SELECT num,NAME FROM stu WHERE sex='男'
UNION
SELECT num,NAME,sex FROM stu WHERE  score >60
*/

-- UNION 会去除重复
SELECT num,NAME,sex FROM stu WHERE sex='男'
UNION
SELECT num,NAME,sex FROM stu WHERE  score >60
-- UNION ALL 直接将查询到的数据连接，不会去除重复，因此效率较高
SELECT num,NAME,sex FROM stu WHERE sex='男'
UNION ALL
SELECT num,NAME,sex FROM stu WHERE  score >60

分组查询

-- select 结果 from 表名[where 条件][group by][order by][limit]

-- 排序  asc 升序 desc 降序
-- 查询语言默认使用主键排序
SELECT* FROM stu ORDER BY SCORE DESC -- 按成绩降序排列
SELECT* FROM stu ORDER BY SCORE ASC -- 按成绩升序排列

-- 使用两列排序规则。先用第一个排序，出现相同值使用第二个排序
SELECT* FROM stu ORDER BY SCORE DESC ,num ASC -- 按成绩降序，学号升序排列

----------------------------------------------------------------------------------------------------------------

-- limit数量限制（注意表中数据索引是从0开始）
SELECT* FROM stu LIMIT 5  -- 查询表中前5项数据
SELECT* FROM stu LIMIT 2,3  -- 查询表中从第2项开始的连续三条数据
SELECT* FROM stu LIMIT 3 OFFSET 2  -- 查询表中从第2项开始的连续三条数据
SELECT* FROM stu WHERE sex = '男' ORDER BY SCORE DESC LIMIT 1-- 取男性中成绩最高的记录

--------------------------------------------------------------------------------------------------------------------

-- group by 分组的条件
-- 把条件相同的数据分到一组中进行处理
SELECT sex,COUNT(*)数量
FROM stu
GROUP BY sex
-- myaql 8.0中count(*)前只可以是group by 后面的字段。
-- 即和分组函数一同查询的字段要求是group by 后面的字段。

-- 分组前进行数据过滤
-- 查询个年级的男生人数
SELECT grade,COUNT(*)各年级人数
FROM stu
WHERE sex = '男'
GROUP BY grade
ORDER BY grade ASC

-- 查询哪个性别人数大于2
SELECT COUNT(*)c,sex
GROUP BY sex
HAVING c>2
ORDER BY sex 
LIMIT 1

子查询

-- 子查询
-- 出现在其他语句（insert，Update，delete，select）中的select语句成为子查询或内查询
-- 外部的查询语句成为主查询或外查询
/*
子查询语句返回结果有四种情况
一行一列（标量子查询）
一列多行（列子查询）
一行多列（行子查询）
多行多列（表子查询）
*/

-- 在insert语句中使用子查询
INSERT INTO t_student SELECT* FROM stu

-- 在update语句中使用子查询，在mySQL中update语句中的子查询不能使用当前查询的表
UPDATE stu SET sex='男' WHERE num IN (SELECT num FROM t_student WHERE SCORE>90)

-- 在delete语句中使用子查询，在mySQL中delete语句中的子查询不能使用当前查询的表
DELETE FROM t_student WHERE num IN (SELECT num FROM stu WHERE SCORE>90)
------------------------------------------------------------------------------------------------------------------------

-- 在查询语句中使用子查询

-- select后面：仅仅支持标量子查询（一行一列）
SELECT (SELECT num FROM t_student WHERE num = 103),NAME FROM t_student

SELECT (SELECT ts.num FROM t_student ts WHERE ts.name=t.name),t.name FROM t_student t
---------------------------------------------------------------------------------------------------------------------

-- 在where后使用标量子查询,用=连接
SELECT* FROM t_student WHERE SCORE= (SELECT MAX(SCORE) FROM t_student) -- 查询成绩最高的学生的所有信息

-- 在where后使用列子查询 ，用in连接
SELECT* FROM t_student WHERE score IN (SELECT score FROM t_student WHERE SCORE>60)

-- 在where后使用行子查询,同时满足多个条件，用=连接
SELECT* FROM t_student WHERE (num,SCORE)= (SELECT MIN(num),MAX(SCORE)FROM t_student)
---------------------------------------------------------------------------------------------------------------------

-- 在from后面使用子查询，只支持表子查询（即多行多类）一个查询出来的结果作为一张临时表供外查询使用
SELECT* 
FROM (SELECT COUNT(*) c,sex
      FROM t_student
      GROUP BY sex)t
      WHERE t.c>3

crraxx

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL8.0（ddl.dml和dql 函数&&条件查询&&分组查询&&子查询）

mySQL 8.0注释#单行注释-- 单行注释（注意用--做单行注释时需要加空格）/*多行注释*/DDL 数据（结构）定义语言/*DDL 数据（结构）定义语言（Date Definition Language）DDL 也属于SQL语言，只是按功能划分执行时选中点击执行查询选中左侧树形目录root 点击刷新对象浏览器即可数据库名一旦给定不能更改*/数据库#创建数据库CREATE DATABASE IF NOT EXISTS school_db CHARSET utf8
复制链接

扫一扫

专栏目录