MySQL--数据库--基础知识

目录

1、 数据库作用

2、sql认识

1、DDL

 整数类型

 浮点

主键

约束:

2、DML

插入数据

修改数据

删除数据

3、DQL-基础查询

 字符函数：

逻辑处理：

数学函数：

日期函数：

分组函数：

条件查询:

模糊查询 LIKE：

UNION：

排序：

数量限制 :

分组查询：

---

1、 数据库作用

1、实现数据持久性地存储在本地

2、使用完整的系统统一管理数据,实现结构化查询,方便管理

2、sql认识

SQL(structed query language)是一种带有特殊目的的编程语言,是一种数据库查询和设计语言,用于存储数据以及查询,更新和管理关系数据库系统。

优点:

不是某个产品的专用语言，几乎所有的DBMS都支持SQL语言

简单易学,灵活使用,可以进行非常复杂的和高级的数据库操作。

 根据其功能，主要分为四类：DDL,DML,DQL,DCL。

分类	说明
DDL	（Data Definition Language） 数据定义语言，用来定义数据库对象（数据库，表，字段）
DML	（Data Manipulation Language） 数据操作语言，用来对数据库表中的数据进行增删改
DQL	（Data Query Language） 数据查询语言，用来查询数据库中表的记录
DCL	（Data Control Language） 数据控制语言，用来创建数据用户，控制数据库的访问权限

1、DDL

是用来创建和修改数据库结构的语言。

常见的DDL关键字:create,drop,alter,rename。

创建数据库

删除数据库

修改字符集

-- DDL结构创建
-- 创建数据库
CREATE DATABASE schooldb

CREATE TABLE Student(
   num INT,
   NAME VARCHAR(10),
   gender CHAR(1),
   birthday DATE,
   phone CHAR(11),
   address VARCHAR(30),
   heigth DECIMAL(3,2),
   reg_time DATETIME
   )


/*
PRIMARY 设置列为主键
AUTO_INCREMENT  设置主键列自动增长，只能修饰主键列，而且主键列类型为整数
unique 唯一约束  可以添加到多个普通列
CHECK(height<2.6) 检查约束

列的数据类型：
   字符串型：
   char(n):长度为n的定长字符串，例如n=5
           如果我们只存储了2个字符进去,长度依然是5，会补空格
      char (n)：一般用来存储长度固定的数据，例如性别，电话
   varchar (n)：最大长度为n的变长字符串，
                例如n=10,如果我们只存储了3个字符,实际长度为3
  日期时间类型：
         date:日期--年月日
     datetime:时间--年月日 时分秒
   数值
     整数
      TINYINT 1
      SMALLINT 2
      mediumint 3
      int 4
      bigint 8
     浮点
       decimal(m,d)
       3.2
       1.73
       
    列的约束：列的规则
     
*/
DROP TABLE Student
DROP TABLE stu
CREATE TABLE stu(
   num INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号主键',
   NAME VARCHAR(10)NOT NULL COMMENT '姓名',
   gender CHAR(1)NOT NULL DEFAULT '男' COMMENT '性别',
   birthday DATE,
   phone CHAR(11)NOT NULL  UNIQUE,
   address VARCHAR(30),
   height DECIMAL(3,2) CHECK(height<2.6),
   reg_time DATETIME
   )
   
-- 修改表名
RENAME TABLE Student TO stu

-- 复制表结构
DROP TABLE student;
ALTER 
CREATE TABLE student LIKE stu;

 整数类型

 浮点

数据类型(M,D)

M：精度，数据的总长度；

D：标度，小数点后的长度

  decimal(m,d)
  3.2
  1.73

主键

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

约束:

• PRIMARY KEY 设置主键约束
• NOT NULL 不能为空约束
• UNIQUE 唯一性约束
• 检查约束 chack 设置条件
• 外键约束 foreign key
• 主键自动增长,设置为自动增长时,只能为整数类型AUTO_INCREMENT
• 默认值 DEFAULT default_value

2、DML

DML(data management language):是用来对数据库进行操纵的语言

DML语言常见的关键字:update,delete,insert。

插入数据

1、给指定字段添加数据：

insert into 表名 (字段名1,字段名2,..) values (值1，值2，...);

2、给全部字段添加数据

insert into 表名 values(值1，值2，...);

3、批量添加数据

insert into 表名 (字段名1,字段名2,...) values (值1,值2,...),(值1,值2,...);
或者
insert into 表名 values (值1,值2,...),(值1,值2,...);

修改数据

update 表名 set 字段名1=值1,字段名2=值2,...[where 条件];

删除数据

delete from 表名 [where 条件];

-- DML 增删改操作
INSERT INTO stu(NAME,gender,birthday,phone)
VALUES
('张三','男','2004-04-12','15566566544')

INSERT INTO stu(NAME,gender,birthday,phone,address,height,reg_time)
VALUES
('李四','男','2003-03-05','15233336677','汉中',1.75,NOW()),
('王五','女','2003-03-16','15233336699','汉中',1.85,NOW()),
('王五','男','2003-04-30','15233336609','汉中',1.95,NOW())

INSERT INTO stu SET NAME='赵六',gender='女',phone='15777778888'

-- INSERT INTO stu(NAME,gender,birthday,phone,address,height,reg_time)
     SELECT NAME,gender,birthday,phone,address,height,reg_time FROM stu
     
-- 修改语句  需要注意条件的准确性，否则修改所有的数据
UPDATE stu SET address='陕西'WHERE num=1

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

3、DQL-基础查询

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

基础语法：

select 查询列表 from 表名;

特点：

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

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

单行函数

分组函数

字符函数

 字符函数：

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)：替换，替换所有的子串

-- 单行函数-- 会对查询的每条记录进行操作
-- 分组函数-- sum() 也成为聚合函数，统计函数，  把多行数据最终处理为一行
-- 字符函数

-- length():获取参数值的字节个数
SELECT NAME,LENGTH(NAME) FROM stu

-- char length():获取参数值的字符个数
SELECT NAME,CHAR_LENGTH(NAME) AS NAME,gender FROM stu

-- concat(str1,str2,.....):拼接字符串 as 别名
SELECT num,CONCAT(NAME,':',gender)AS NAME FROM stu

-- upper()/lower():将字符串变成大写/小写
SELECT UPPER(NAME),LOWER(NAME)FROM stu

-- substring(str,pos,length):截取字符串 位置从1开始
SELECT SUBSTRING(NAME,2,3)FROM stu

-- instr(str,指定字符):返回子串第一次出现的索引(indexof())，如果找不到返回0
SELECT INSTR(NAME,'三')FROM stu

-- trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT CHAR_LENGTH(TRIM(NAME))FROM stu
SELECT TRIM('王'FROM NAME)FROM stu

-- lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度 ,RPAD()右填充
SELECT LPAD(NAME,5,'a'),RPAD(NAME,5,'b')FROM stu 
-- rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度

-- replace(str,old,new):替换，替换所有的字串
SELECT REPLACE(NAME,'王','I')FROM stu

逻辑处理：

case when 语句

/*
case when 条件 then 结果1 else 结果2 end; 可以有很多个when
ifnull （被检测值，默认值）函数检测是否为null, 如果为null,则返回指定的值,否则返回原本的值
if函数：if else的 效果 if(条件，结果1，结果2)
*/

SELECT NAME,
       (CASE WHEN height>=1.8 THEN '高个子' ELSE '正常身高' END)AS height,
        gender FROM stu
        
SELECT NAME,
        (CASE WHEN height>=1.8 THEN '高个子'WHEN height>=1.6 THEN '正常身高' ELSE '低个子'END)AS height,
        gender FROM stu
        
SELECT NAME,IFNULL(address,'空值')AS address FROM stu

SELECT NAME,IF(height>=1.80,'高个子','正常')FROM stu

数学函数：

round(数值)：四舍五入

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

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

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

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

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

-- round(数值)：四舍五入
SELECT NAME,ROUND(height) FROM stu
-- 指定保留小数位数
SELECT NAME,ROUND(height,1) FROM stu

-- ceil(数值)：向上取整，返回>=该参数的最小整数
-- floor(数值)：向下取整，返回=<该参数的最大整数
SELECT NAME,CEIL(height),FLOOR(height) FROM stu

-- truncate(数值，保留最小位数)：截断，小数点后截断到几位，不会四舍五入
SELECT NAME,TRUNCATE(height,1)FROM stu

-- mod(，取模)
SELECT NAME,MOD(num,3) FROM stu

-- rand(): 获取随机数，返回0-1之间的小数
SELECT NAME,RAND() FROM stu

日期函数：

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

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

curtime()：返回当前时间，不包含日期

-- 日期函数
-- now()

SELECT NAME,NOW(),CURDATE(),CURTIME() FROM stu

-- 日期格式化
SELECT NAME,YEAR(reg_time),MONTH(reg_time),DAY(reg_time) FROM stu


-- 按年分类
SELECT COUNT(*) FROM stu GROUP BY YEAR(birthday)

-- 将字符串格式转化为日期类型
SELECT STR_TO_DATE('2001-2-2','%Y-%m-%d') FROM stu 

-- 将日期格式化为指定的字符串
SELECT DATE_FORMAT(reg_time,'%Y-%m')FROM stu

-- 计算两个日期之间相差的天数
SELECT DATEDIFF(CURDATE(),birthday)FROM stu

SELECT DATEDIFF(STR_TO_DATE('2001-1-1','%Y-%m-%d'),birthday)FROM stu

分组函数：

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

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

-- 分组函数/聚合函数，统计函数(用作统计使用)
-- sum求和，avg平均值，max最大值，min最小值，count计数
-- sum(),avg()只能用于数值类型
-- max(),min()可以适用于所有类型

SELECT SUM(height),AVG(height),MAX(height),MIN(height)FROM stu

SELECT MAX(birthday),MIN(birthday)FROM stu

-- 统计行数count(*) count(1) count(列名) 列值为空的话，不统计

SELECT COUNT(birthday) FROM stu

SELECT * FROM stu WHERE height =(SELECT MAX(height)FROM stu)

条件查询:

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

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

逻辑运算：and 与， or 或， not 非。

-- 条件查询条件select 结果列 from 表名where

SELECT *FROM stu WHERE num =2

-- and 所有条件都需要满足
 SELECT *FROM stu WHERE height>1.7 AND gender ='男' AND address='汉中'
 
-- or 满足一个条件即可!
 SELECT *FROM stu WHERE height>1.7 OR gender ='男'
 
-- 不等于
 SELECT *FROM stu WHERE gender!='男'
 SELECT *FROM stu WHERE gender<>'男'

模糊查询 LIKE：

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

通配符： % 任意多个字符

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

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

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

-- 模糊查询 like %字符% 通配符
SELECT * FROM stu WHERE NAME LIKE '王%'

SELECT * FROM stu WHERE NAME LIKE '%六%'

-- between a and b
SELECT * FROM stu WHERE height>=1.8 AND height<=2.0
SELECT * FROM stu WHERE height BETWEEN 1.7 AND 2.0

UNION：

合并多个査询的结果,可以去除重复的数据
UNION ALL：

不能去除重复的数据.

-- union 合并多个査询的结果,可以去除重复的数据
SELECT num,NAME,gender FROM stu WHERE gender='男'
UNION
SELECT num,NAME,gender FROM stu WHERE gender='女'

-- 不能去除重复的数据
SELECT num,NAME,gender,height FROM stu WHERE gender='男'
UNION ALL
SELECT num,NAME,gender,height FROM stu WHERE height=1.75

排序：

升序，降序

-- 排序  默认是升序
SELECT * FROM stu ORDER BY height
-- 升序
SELECT * FROM stu ORDER BY height ASC
-- 降序
SELECT * FROM stu ORDER BY height DESC

SELECT * FROM stu WHERE num>2 ORDER BY height DESC

SELECT * FROM stu WHERE num>2 ORDER BY height DESC,birthday DESC

-- 什么排序都没有,默认按主键 升序排列
SELECT * FROM stu

数量限制 :

limit 开始位置(开始位置为0),查询的数量
-- 实际使用场景，数据分页显示,一次只查询一部分数据，提高查询效率

-- 数量限制 1imit 开始位置(开始位置为0),查询的数量
-- 实际使用场景，数据分页显示,一次只查询一部分数据，提高查询效率
SELECT * FROM stu LIMIT 0,2 -- 1 当前页码n
SELECT * FROM stu LIMIT 2,2 -- 2
SELECT * FROM stu LIMIT 4,2 -- 3
-- limit(n-1)*2,2个

分组查询：

将某类数据分到一个组中进行处理，例如按性别查询

-- 分组查询
-- 将某类数据分到一个组中进行处理，例如按性别查询
-- 查询男生 女生各有多少人(统计数量) 例如按性别分组
-- GROUP BY 分组条件(列名)

SELECT COUNT(*),gender FROM stu GROUP BY gender
SELECT SUM(height),gender FROM stu GROUP BY gender
SELECT AVG(height),gender FROM stu GROUP BY gender

持续更新........