【最详细】Mysql数据库 DDL DML DQL

最新推荐文章于 2024-06-29 13:09:29 发布

杨加一

最新推荐文章于 2024-06-29 13:09:29 发布

阅读量683

点赞数 11

文章标签：数据库 mysql 开发语言

本文链接：https://blog.csdn.net/2201_75401433/article/details/138995360

版权

数据库概述

● 为什么学习数据库：

● 实现数据持久化到本地；

● 使用完整的管理系统统一管理，可以实现结构化查询，方便管理；

● 数据库（ DataBase ）为了方便数据的存储和管理，它将数据按照特定的

规则存储在磁盘上 , 就是一个存储数据的仓库。

● 数据库的相关概念

DB ：数据库（ DataBase ）

存储数据的容器，它保存了一系列有组织的数据。

DBMS ：数据库管理系统（ DataBase Management System ）

又称为数据库软件或数据库产品，用于创建或管理 DB 。

● 常见的数据库产品：

国外

MySQL 快捷、可靠开源、免费

Oracle: 功能强大 , 收费 .

SQL Server( 微软 ): 只能安装在 Windows 操作系统

DB2 (IBM): 适合处理海量数据 , 收费 .

国内

南大通用 GBASE: 天津南大通用数据技术股份有限公司

达梦 : 武汉达梦数据库股份有限公司

人大金仓 : 北京人大金仓信息技术股份有限公司

神通 : 神舟通用公司

一、DDL

数据(结构)定义语言DDL(Data Definition Language)，是用于创建和修改数据库表结构的语言。 ● 常用的语句：create ,alter,drop,rename

1.创建数据库并设置编码格式:
CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8 ]

2.删除数据库
DROP DATABASE 数据库名 / [IF EXISTS 数据库名];

3.修改字符集
ALTER DATABASE 数据库名 CHARSET gbk;

在对数据库进行操作时,比如删除操作时,不能只是将语句删除,然后重新运行,这样是删不掉的,要进行删除操作,就必须要执行操作语句进行删除.修改字符集的操作也是同理,如果只是将CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8 ]里面的utf8改为gbk然后运行是行不通的,必须要进行修改字符集的语句操作.

-- 创建数据库
CREATE DATABASE IF NOT EXISTS schooldb CHARSET utf8
-- 删除数据库
 DROP DATABASE IF EXISTS schooldb
 
-- MySQL中数据库一旦创建,名字不能修改
 
-- 修改数据库字符集
ALTER DATABASE schooldb CHARSET utf8

4.数据库表的基本概念

1、数据表
表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。表的最简单形式是由行和列组成，分别都包含着数据。每个表都有一个表头和表体，表头定义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。

2、字段
字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单 ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。数据表的一列包含了特定字段的全部信息。

3、记录
记录也被称为一行数据，是表里的一行数据。

4.设计表
对于具体的某一个表，在创建之前，需要确定表的下列特征：

● 表名(表信息)

● 表中的字段

● 字段的数据类型和长度

● 哪些约束

数据类型:
char（n）长度为n的定长字符串,最大长度255个字符

varchar(n）最大长度为n的可变长字符串

date 日期，包含年月日

datetime 年月日时分秒

decimal 数据类型(M,D) M：精度，数据的总长度； D：标度，小数点后的长度

主键:

在一张表中代表唯一的一条记录,不能为空,不能重复

一般的,表的第一列为主键,命名为id,再有题目要求的情况下,根据题目进行修改

约束:

PRIMARY KEY 设置主键约束

NOT NULL 不能为空约束

UNIQUE 唯一性约束检查约束设置条件

外键约束

主键自动增长,设置为自动增长时,只能为整数类型 AUTO_INCREMENT

字段注释: comment '注释'

创建表语法:
CREATE TABLE 表名(列名数据类型 [约束] [默认值] [ 注释],......)

CREATE TABLE student(
    number INT,
    NAME VARCHAR(6),
    gender CHAR(1),
    birthday DATE,
    phone VARCHAR(11),
    height DECIMAL(3,2),
    reg_time DATETIME
)

这是简单的表创建,没有加任何的约束和注释,主要是方便第一次了解语法

CREATE TABLE student(
    number INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号主键',
    NAME VARCHAR(6) NOT NULL COMMENT '姓名',
    gender CHAR(1) COMMENT '性别',
    birthday DATE COMMENT '生日',
    phone VARCHAR(11) NOT NULL UNIQUE COMMENT '电话',
    height DECIMAL(3,2) CHECK(height<3) COMMENT '身高',
    reg_time DATETIME
)

这段代码则加了主键的约束,对其他的列也添加了约束和注释

对表的删除修改操作同对项目的操作,都需要有专门的语句进行修改

删除表 DROP TABLE [if exists]表名

修改表名 RENAME TABLE 旧表名 TO 新表名

复制表结构 CREATE TABLE 新表名 LIKE 被复制表名

-- 删除表结构
DROP TABLE student
 
-- 修改表名
RENAME TABLE student TO stu
 
-- 复制表结构
CREATE TABLE student LIKE stu

二、DML
数据操纵语言DML（Data Manipulation Language）

● 常用语句： insert,delete,updat

插入数据
方式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 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)

INSERT INTO student (NAME ,gender ,birthday,phone,height,reg_time)VALUES('詹姆斯','男','1985-2-3','15555555555',1.98,NOW())
INSERT INTO student SET NAME='科比',gender='男',birthday='1986-2-3',phone='13333333333'
INSERT INTO student(NAME,gender ,birthday,phone,height,reg_time)
            VALUES('库里','男','1998-2-2','22222222222',1.93,NOW()),
                  ('欧文','男','1998-2-2','11111111111',1.93,NOW())
                
INSERT INTO stu (number,NAME ,gender ,birthday,phone,height,reg_time)SELECT*FROM student

修改数据
UPDATE 表名 SET 列名 = ‘新值’WHERE 条件

-- 修改记录 注意条件
UPDATE stu SET NAME='梅西',height=1.70 WHERE number = 1

删除数据
DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名;清空整张表

-- 删除

DELETE FROM student WHERE number =2
INSERT INTO student (NAME ,gender ,birthday,phone,height,reg_time)VALUES('杜兰特','男','1985-12-3','15566666666',1.98,NOW())

注意:

删除,修改后就不可恢复了,所以要注意操作,避免不必要的法麻烦

三、DQL
DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作，可以从一个表中查询数据，也可以从多个表中查询数据。

基础查询

● 语法： select 查询列表 from 表名;

● 特点：查询列表可以是：表中的字段、常量、表达式、函数

查询的结果是一个虚拟的表格

查询结果处理：
特定列查询:select column1,column2 from table

全部列查询: select * from table

算数运算符:+ - * /

排除重复行: select distinct column1,column2 from table

查询函数：select 函数; / 例如version()

函数:类似于java中的方法，将一组逻辑语句事先在数据库中定义好,可以直接调用

分类：单行函数：如concat、length、ifnull等

分组函数：做统计使用，又称为统计函数、聚合函数、组函数

单行函数
字符函数
length()：获取参数值的字节个数

char_length()获取参数值的字符个数

concat(str1,str2,.....)：拼接字符串

upper()/lower()：将字符串变成大写/小写

substring(str,pos,length)：截取字符串位置从1开始

instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0

trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)

lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度

rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度

replace(str,old,new)：替换，替换所有的子串

-- leghth(列名) 以字节为单位
SELECT LENGTH(NAME),gender FROM student
 
-- CHAR_LENGTH(列名) 以字符为单位
SELECT CHAR_LENGTH(NAME),gender FROM student
 
-- concat(str1,str2...)链接多个字符串 as 别名
SELECT CONCAT(NAME,':',CHAR_LENGTH(NAME))AS NAME,gender FROM student
 
-- upper()转为大写 lower()转为小写
SELECT UPPER(NAME),LOWER(NAME)FROM student
 
-- substring(字符串,开始位置,截取长度) 开始位置从1开始
SELECT SUBSTRING(NAME,1,2)FROM student
 
-- instr(字符串,指定的字符) 返回指定字符首次出现的位置 找不到返回0
SELECT INSTR(NAME,'o')FROM student
 
-- trim(字符串) 默认是去掉字符串前后的空格
SELECT TRIM(NAME),NAME FROM student
 
-- trim(指定的字符串 from 字符串) 可以去掉前后指定的子串
SELECT TRIM('a' FROM NAME),NAME FROM student
 
-- 左填充 右填充到指定长度
SELECT LPAD(NAME,5,'a'),RPAD(NAME,5,'b')FROM student
 
-- replace(列,'old','new') 替换
SELECT REPLACE(NAME,'o','O')FROM student

逻辑处理

case when 条件 then 结果1 else 结果2 end; 可以有多个when

ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值

if函数：if else的效果 if(条件,结果1,结果2)

-- case when 条件 then 条件成立结果 else 不成立结果 end;
SELECT
  NAME,
  gender,
  (CASE WHEN height>=2 THEN '高个子'ELSE'正常身高'END)AS height
FROM
  student
 
SELECT
  NAME,
  gender,
  (CASE WHEN height>=2 THEN'A'
          WHEN height>=1.9 THEN'B'
          WHEN height>=1.8 THEN'C'
          ELSE 'D' END)AS height
FROM
  student
  
  -- ifnull(列名,'为空时的默认信息')
SELECT
  NAME,
  gender,
  IFNULL(height,'暂未录入身份信息')AS height
FROM
  student
 
-- if(条件,'结果1','结果2')  
SELECT
  NAME,
  gender,
  IF(height>=2,'高个子','正常')AS height
FROM
  student

数学函数

round(数值)：四舍五入

ceil(数值)：向上取整，返回>=该参数的最小整数

floor(数值)：向下取整，返回<=该参数的最大整数

truncate(数值,保留小数的位数)：截断，小数点后截断到几位

mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负

rand()：获取随机数，返回0-1之间的小数

-- 数学函数
SELECT NAME,gender,ROUND(height),CEIL(1.1),FLOOR(1.9)FROM student
 
SELECT NAME,gender,TRUNCATE(height,1),height,RAND() FROM student

日期函数

now()：返回当前系统日期+时间

curdate()：返回当前系统日期，不包含时间

curtime()：返回当前时间，不包含日期可以获取指定的部分，年、月、日、小时、分钟、秒

YEAR(日期列),MONTH(日期列),DAY(日期列) ,HOUR(日期列) ,MINUTE(日期列) SECOND(日期列)

str_to_date(字符串格式日期,格式)：将日期格式的字符转换成指定格式的日期

date_format(日期列,格式)：将日期转换成字符串

datediff(big,small)：返回两个日期相差的天数

-- 日期函数
-- NOW(),年月日 时分秒 系统当前时间
-- CURDATE(),年月日
-- CURTIME() 时分秒
SELECT NOW(),CURDATE(),CURTIME()FROM student
 
-- year(日期) 将日期格式化为年
SELECT YEAR(birthday),MONTH(birthday)FROM student
 
-- STR_TO_DATE('2004-3-3','%Y-%m-%d')将字符串日期 格式化为 日期类型
SELECT STR_TO_DATE('2004-3-3','%Y-%m-%d')FROM student
 
-- DATE FORMAT (birthday，'%Y-%m') 将日期格式化为指定的字符串
SELECT DATE_FORMAT(birthday,'%Y-%m')FROM student
 
-- DATEDIFE(CURDATE(),birthday) 计算两个日期之间的相差的天数
SELECT DATEDIFF(CURDATE(),birthday)FROM student

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）

1.sum，avg一般用于处理数值型max，min，count可以处理任何类型

2.以上分组函数都忽略null值

3.count函数的一般使用count（*）用作统计行数

4.和分组函数一同查询的字段要求是group by后的字

-- 分组函数 聚合函数(查询多行,返回一行) 统计函数
 
SELECT SUM(height) FROM student -- 求和
SELECT AVG(height) FROM student -- 平均值
SELECT MAX(height) FROM student -- 最大值
SELECT MIN(height) FROM student -- 最小值
SELECT COUNT(*) FROM student -- 统计个数

条件查询

使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。

语法:select from where <条件>

比较 =, != 或<>, >, =, <=

逻辑运算

and 与

or 或

not 非

模糊查询 LIKE ：是否匹配于一个模式一般和通配符搭配使用，可以判断字符型数值或数值型.

通配符： % 任意多个字符

between and 两者之间,包含临界值；

in 判断某字段的值是否属于in列表中的某一项

IS NULL（为空的）或 IS NOT NULL（不为空的)

-- 条件查询
-- select * from 表名 where 条件
 
-- and 并且
SELECT * FROM student WHERE gender = '男'AND height>1.80
 
-- or 多个条件只需要满足一个即可
SELECT * FROM student WHERE gender = '男'OR height>1.80
 
SELECT * FROM student WHERE gender != '男'
SELECT * FROM student WHERE gender <> '女'
 
-- like'库%' 模糊匹配
SELECT * FROM student WHERE NAME LIKE '库%'
 
SELECT * FROM student WHERE height>=1.65 AND height<=1.98
 
-- BETWEEN 1.65 AND 1.98 两者之间
SELECT * FROM student WHERE height BETWEEN 1.65 AND 1.98
 
-- in(值1,值2,值n) 在给定的值中
SELECT * FROM student WHERE height IN(1.78,1.88,1.98)
 
SELECT * FROM student WHERE height NOT IN(1.78,1.88,1.98)
 
-- 查询身高为null值的数据
SELECT * FROM student WHERE height IS NULL
SELECT * FROM student WHERE height IS NOT NULL

1、UNION 的语法如下：
[SQL 语句 1] UNION [SQL 语句 2]

2、UNION ALL 的语法如下：
[SQL 语句 1] UNION ALL [SQL 语句 2]

当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ， mysql 会把所有的记录返回，且效率高于union

-- union 将多个查询结果合并,合并时,列数必须一致,去除重复数据
SELECT * FROM student WHERE gender='男'
UNION
SELECT * FROM student WHERE gender='女'
 
-- union all 只是将查询的多个结果合并,不会去除重复数据
SELECT * FROM student WHERE gender='男'
UNION ALL
SELECT * FROM student WHERE gender='女'

排序

查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC

asc代表的是升序，desc代表的是降序，如果不写，默认是升序

order by子句中可以支持单个字段、多个字段

-- 排序 order by 列 asc(升序) desc(降序)
SELECT * FROM student WHERE number>0 ORDER BY number ASC
 
SELECT * FROM student WHERE number>0 ORDER BY number DESC
 
SELECT * FROM student WHERE number>0 ORDER BY height ASC
 
SELECT * FROM student WHERE number>0 ORDER BY reg_time DESC

数量限制

limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)

SELECT * FROM table LIMIT offset rows;

SELECT * from table LIMIT 0,5

-- 数量限制 实现分页查询 limit 开始位置,每次查询数量
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 0,3
 
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 3,3
 
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 6,3

分组查询

语法：

select 分组函数，列（要求出现在group by的后面）

from 表

[where 筛选条件]

group by 分组的列表

[having 分组后的筛选]

[order by 子句]

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

-- 分组查询
-- 分组--在一些统计场景中,按照某一个条件进行区分(分组)
-- 统计时,按照性别,班级,年级,部门,年份,月份
-- 把满足某个条件的数据,分到同一个组中,每个组单独进行处理
 
-- 查询 男生,女生各有多少人 每个年级各有多少人 每个部门各有多少人
-- 分组统计 男生组统计一次次,女生组统计一次
SELECT COUNT(*),gender FROM student GROUP BY gender
 
SELECT COUNT(*),DATE_FORMAT(reg_time,'%Y-%m')FROM student GROUP BY DATE_FORMAT(reg_time,'%Y-%m')
 
-- 统计男生女生各多少人
SELECT COUNT(*),gender FROM student GROUP BY gender
 
-- 男生和女生谁的人数大于2
-- HAVING COUNT(*)>2 having 条件 是对分组后的结果进行条件筛选
SELECT COUNT(*),gender FROM student GROUP BY gender HAVING COUNT(*)>2
 
-- 作业 请取出student表中日期(reg_time字段)为当天的所有记录
SELECT *
FROM student
WHERE DATE_FORMAT(reg_time,'%Y-%m-%d') = CURDATE();
 
-- 作业 使用sql查询出学生表哪些学生的姓名重复了
SELECT COUNT(*),NAME FROM student GROUP BY NAME HAVING COUNT(*)>1