day08

一个胖小白

已于 2022-03-26 15:10:11 修改

阅读量828

点赞数

分类专栏：笔记自己文章标签： sql

于 2022-03-24 20:52:31 首次发布

本文链接：https://blog.csdn.net/weixin_46002359/article/details/123721126

版权

笔记同时被 2 个专栏收录

75 篇文章 0 订阅

订阅专栏

自己

42 篇文章 0 订阅

订阅专栏

一、DDL-操作表

1.1 数据库与表之间的关系

 一对多的关系，一个数据库可以存储多张表

1.2 MySQL里面的常见的数据类型

1.2.1 字符类型

类型	大小	用途
CHAR(String)	0-255字符	不可变的长度的char类型的字符串
CARCHAR(StingBuffer)	0-65535字符	可变长度的varchar类型的字符串
BLOB(音视频)	0-65535字符	二进制形式的长文本数据类型
TEXT(文章)	0-65535字符	长文本数据

1.2.2 数值类型

类型	大小	用途
INT	4字节	大整数值
DOUBLE	8字节	双精度浮点数值
DOUBLE(M.D)	8字节，M表示长度，D表示小数位数	双精度浮点数值
DECIMAL(M.D)(大数据)	DECIMAL(M.D)(M的最大值为65)	小数值

1.2.3 时间类型

类型	格式	用途
DATE	YYYY-MM-DD	日期值
TIME	HH:MM:SS	时间值或持续时间
YEAR	YYYY	年份值
DATETIME	YYYY-MM-DD HH-MM-SS	混合日期和时间值
TIMESTAMP(相当于system.curr的时间戳)	YYYYMMDD HHMMSS	混合日期和时间，时间戳

二 DDL对表的操作

2.1 创建表

 1.语法：create table 表名(
         列名 数据类型(长度),
         列名 数据类型(长度),
         列名 数据类型(长度),
         列名 数据类型(长度)
         ........
     );
 2.创建一张学生表
     create table student(
         sid int(11),
         sname varchar(5),
         sage int(3),
         sex char(2)
     );

注意点：1.在创建表时候，在括号后面必须加上英文分号

2.在创建表的最后一个列的时候，不需要逗号来进行分割，其他的必须加上逗号来进行分割

2.2 修改表名

 语法：alter table 旧表名 rename  to 新表名

2.3 查看数据库所有的表

 语法：show tables

2.3 查看表结构

 语法：desc 表名

2.4 对列进行操作

2.4.1 在末尾增加列

 语法：alter table 表名 add 列名 数据类型(长度)

2.4.2 在指定的列后面面添加列

 语法：alter table 表名 add 列名 数据类型(长度) after 指定列名

2.4.3 修改列名

 语法：alter table 表名 change 原列名  新列名 数据类型(长度)

2.4.4 删除列

 语法:alter table 表名 drop 列名

2.5 可视化工具

 1.常用的可视化工具:navicte(收费) sqlyarn(免费)
 2.安装:里面2有登录码直接点击exe文件即可

三、DML

3.1 素材

 #创建数据库
 CREATE DATABASE day08db;
 #切换数据库
 USE day08db;
 #创建数据库表
 CREATE TABLE student(
     ipd INT(11),
     pname VARCHAR(20),
     sage INT(3)
 );

3.2 增加数据

3.2.1 第一种方式

 1.语法：insert into 表名 values(数据1，数据2，数据3....)
 2.例子：
         INSERT INTO student VALUES(1,"张三",25);

注意点：必须插入所有列的数据，少一列没有插入都不行

3.2.2 第二种方式

 1.语法：insert into 表名(列名，列名...) values(数据1，数据2....)
 2.例子：
     INSERT INTO student(pid,pname) VALUES(2,"法外狂徒")

3.2.3 插入多条数据

 1.语法：insert into 表名(列名，列名...) values(数据1，数据2....)，(数据1，数据2....)....
 2.例子：INSERT INTO student(pid,pname,sage) VALUES
         (3,"李四",25),
         (4,"王五",35),
         (5,"找刘",20),
         (6,"李刚",25);

注意点：

插入的数据的值必须与数据库里面的列的数据类型一致

插入的数据的长度必须小于列定义的长度

字符串可以使用单引号或者双引号来括起，时间类只能使用单引号

3.3 修改数据

 1.语法：update 表名 set 列名=值，列名=值 where 条件
 2.例子：
     UPDATE student SET sage=32 WHERE pid=2;

注意点：修改的时候，注意给定修改的条件，如果不给条件就会把表里面的数据全部修改了

3.4 删除数据

3.4.1 第一种方法

 1.语法：delete  from 表名 where 条件
 2.例子：
     DELETE FROM student WHERE pid=5;

3.4.2 第二种方法

 1.语法：truncate  table 表名
 2.例子：
     TRUNCATE   TABLE   student;

3.4.3 两种方法的比较

 1.truncate：删除数据，也删除数据结构(从1开始)，  delete只删除数据
 2.truncate是删除整个表的数据   delete是一行一行的删除数据，效率慢

四、单表查询

4.1 素材

 #创建商品表：
 CREATE TABLE product(
     pid INT PRIMARY KEY,#主键ID
     pname VARCHAR(20),#商品名称
     price DOUBLE,#商品价格
     category_name VARCHAR(32)#商品分类名称
 );
 #插入数据
 INSERT INTO product(pid,pname,price,category_name) VALUES(1,'联想电脑',5000,'电脑办公');
 INSERT INTO product(pid,pname,price,category_name) VALUES(2,'海尔电脑',3000,'电脑办公');
 INSERT INTO product(pid,pname,price,category_name) VALUES(3,'雷神电脑',5000,'电脑办公');
 
 INSERT INTO product(pid,pname,price,category_name) VALUES(4,'JACK JONES',800,'服装');
 INSERT INTO product(pid,pname,price,category_name) VALUES(5,'真维斯',200,'服装');
 INSERT INTO product(pid,pname,price,category_name) VALUES(6,'花花公子',440,'服装');
 INSERT INTO product(pid,pname,price,category_name) VALUES(7,'劲霸',2000,'服装');
 
 INSERT INTO product(pid,pname,price,category_name) VALUES(8,'香奈儿',800,'女士用品');
 INSERT INTO product(pid,pname,price,category_name) VALUES(9,'相宜本草',200,'女士用品');
 INSERT INTO product(pid,pname,price,category_name) VALUES(10,'面霸',5,'女士用品');
 
 INSERT INTO product(pid,pname,price,category_name) VALUES(11,'雪碧',56,'饮料饮品');
 INSERT INTO product(pid,pname,price,category_name) VALUES(12,'香飘飘奶茶',1,'饮料饮品');
 
 INSERT INTO product(pid,pname,price,category_name) VALUES(13,'iPhone9',8000,NULL);

4.2 表结构

4.3 全查(查询表里面的所有数据)

 1.语法：select * from 表名
 2.例子：
     #查询product表中所有记录
     SELECT * FROM product ;

4.3 查询表里面指定的列数据

 1.语法：select 列名，列名 from 表名
 2.例子：
     #查询product表中pid和pname字段
     SELECT pid,pname FROM product;

4.4 简单的运算查询

 1.语法：select 运算 from 表名
 2.运算：+ - * /
 3.例子：
     #查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
     #语法：select 运算  from 表名  as是设置别名
     SELECT pname,price+10 AS "增加后的价格" FROM product;

4.5 根据条件查询

符号	说明
not	非（！）
between and	并且（&&）
or	或者（ \|\| ）

 1.语法：select * from 表名 where 条件 
 2.例子：   
     #查询价格不是800的所有商品
     SELECT * FROM product WHERE price !=800;
     
     #查询商品价格在200到1000之间所有商品
     SELECT * FROM product WHERE price>200 AND price<1000;
     SELECT * FROM product WHERE price >200 && price < 1000;
     SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
     
     #查询商品价格是200或800或者2000的所有商品
     SELECT * FROM product WHERE price =200 OR price =800 OR price = 2000;
     SELECT *FROM product WHERE price =200 || price =800 || price =2000;
     SELECT * FROM product WHERE price IN(200,800,2000);

4.6 模糊查询

 1.模糊查询的关键字：like
 2.匹配字符：%表示匹配多个字符或者一个和零个
             _ 匹配一个
 3.语法：select *from 表名 where 查询条件
 4.例子：
     #查询含有'霸'字的所有商品
     SELECT * FROM product WHERE pname LIKE "%霸%";
     #查询以'香'开头的所有商品
     SELECT * FROM product WHERE  pname LIKE "香%";
     #查询第二个字为'想'的所有商品
     SELECT * FROM product WHERE pname LIKE "_想%";

4.7 非空查询

 1.关键字：is null,  is not null 
 2.例子：   
     #商品没有分类的商品
     SELECT * FROM product WHERE category_name IS NULL;
     #查询有分类的商品product
     SELECT * FROM product WHERE category_name IS NOT NULL;

注意点：在数据库里面 null 不等于 “” ，null表示是没有插入数据

4.8 排序

 1.关键字：order by 升序：asc 降序：desc
 2.语法：select  * from 表名  order by  列名 排序规则
 3.例子：   
     #1.使用价格排序(降序)
     SELECT * FROM product ORDER BY price DESC;
     #2.在价格排序(降序)的基础上
     #若价格相同，相同价格的数据以pid降序排序
     SELECT * FROM product ORDER BY price DESC,pid DESC;

4.9 分页查询

 1.关键字：limit ？,？  第一个问号表示第几条数据，第二个表示取这个几条数据
 2.语法：select * from 表名 limit ?,?
 3.案例：
     #分页查询
     #查询第一条和第二条数据
     SELECT * FROM product LIMIT 0,2;
 
     #查询第三条和第四条数据
     SELECT * FROM product LIMIT 2,2;

注意点：在分页查询时候，转换规则：（当前页 - 1 ）*一页的页量

4.10 分组查询

 1.关键字：group by
 2.语法：select * from 表名 group by 列名
 3.例子：
     # 统计各个分类下商品的个数
     SELECT  category_name,COUNT(1) FROM product GROUP BY category_name;

4.11 过滤查询

 1.关键字：having
 2.语法：select * from 表名 group by 列名 having 条件
 3.例子：
     #统计各个分类商品的个数，有且 只显示分类名称不为空值的数据（过滤）
     SELECT category_name,COUNT(1) FROM product GROUP BY category_name HAVING  category_name IS NOT NULL;

4.12 去重

 1.关键字：distinct
 2.语法：select distinct * from 表名
 3.例子：
     #查看价格
     SELECT DISTINCT price FROM product;

4.13 查询的顺序

 1.查询的语句的顺序：
     select * from 表名 where 条件 group by 条件 having 条件 order by 条件 排序条件 limit (分页)

五、mysql里面的函数

5.1 聚合函数

函数名	说明
count(1)(效率最高)或者count(*)或者count(列名)	总记录数
sum(列名)	总和
max(列名)	最大值
min(列名)	最小值
avg(列名)	平均值

 #查看商品总价格、最大价格、最小价格、价格的平均值
 SELECT SUM(price) AS "总价格" FROM product;
 SELECT MAX(price) AS "最大值" FROM  product;
 SELECT MIN(price) AS "最小值" FROM product;
 SELECT AVG(price) AS "平均值" FROM product;
 #平均值设置小数
 SELECT ROUND(AVG(price),2) FROM product;
 
 
 #2 查询价格大于200商品的总条数
 SELECT COUNT(1) AS "总条数" FROM product WHERE price >200;
 
 #3 查询分类为'电脑办公'的所有商品的总记录
 SELECT * FROM product WHERE category_name="电脑办公";
 #4 查询分类为'服装'所有商品的平均价格
 SELECT AVG(price) AS "所有服装的平均价格" FROM product WHERE category_name="服装";

5.2 时间函数

函数名	说明
SYSDATE()	当前系统时间（年月日时分秒）
CURDATE()	获取当前日期
CURTIME()	获取当前时间
NOW()	查询当前的时间

 #sysdate()
 select sysdate();
 #curdate() 获取当前日期
 select curdate();
 #curtime() 获取当前时间
 select curtime();
 #获取当前的日期和时间
 select now()

T CONCAT("法外狂徒","张三"); #替换 SELECT INSERT("罗志祥玩完了",1,3,"吴一凡"); #小写 SELECT LOWER("dFDSGfv"); #大写 SELECT UPPER("LKJlkmndfgsF"); #截取 SELECT SUBSTRING("你哈哈还是很牛逼的",7,8);

5.3 字符串函数

函数名	说明
concat(str1,str2....)	将多个字符串拼接
insert(str,pos,len,newstr)	将str中指定的pos位置开始的len长度的内容替换成newstr
LOWER(str)	将指定的字符串转换成小写
UPPER(str)	将指定的字符串转换成大写
substring(str,num,len)	将str字符串的指定位置num的开始截取长度为len的内容
length(str)	获取字符串的长度
char_length(str)	获取字符串的长度

 #字符串函数
 SELECT CONCAT("法外狂徒","张三");
 #替换
 SELECT INSERT("罗志祥玩完了",1,3,"吴一凡");
 #小写
 SELECT LOWER("dFDSGfv");
 #大写
 SELECT UPPER("LKJlkmndfgsF");
 #截取
 SELECT SUBSTRING("你哈哈还是很牛逼的",7,8);
 #取长度
 select char_length("你觉得你又行了呀");

MySQL里面的length()和char_length的比较：

length()：是MySQL里面获取字符串长度的一个内置函数

char_length()：是mysql里面内置函数查看字符串的长度的函数

不同的：

length()的单位是字节，字utf-8之下，一个汉字等于三个字节，一个字母或数字等于一个字节，在gbk编码下，一个汉字等于两个字节

char_length()单位为字符，不管汉字、字母或数字都是一个字符

小技巧：length() >或者< char_length() 可以来判断是否含有中文字符

utf-8的编码格式下，判断某个字段为英文， length(字段) = char_length(字段)

六、约束

6.1 概念

 1.在设计表中的指定列的约束条件，可以指定列里面的数据，保证数据的完整性和正确性，避免数据的重复
 2.五大约束：
     A.主键约束 primary key
     B.唯一约束 unique
     C.外键约束 foreign key
     D.非空约束 not null
     E.默认约束  default

6.2 主键约束

 1.关键字：primary key
 2.特点：唯一，不为空，一张表只能有一个主键
 3.主键约束有两种方式：A.创建表的时设置主键  B.创建表之后设置主键

第一种方式创建表时候设置主键

 CREATE TABLE p1(
     pid INT(11) PRIMARY KEY,
     pname VARCHAR(5)
 );

第二种方式创建表之后设置主键

 CREATE TABLE p2(
     pid INT(11) ,
     pname VARCHAR(5)
 );
 
 #设置主键
 ALTER TABLE p2 ADD PRIMARY KEY(pid);

6.3 自增长策略

 1.关键字：auto_increment
 2.特点：
     A.只能针对整数
     B.自增长的波长为1
     C.一般与主键进行搭配
     D.设置自增长之后，改列之后不需要插入数据

 #创建表
 create table p3(
     pid primery key auto_increment,
     pname varchar(5)
 );

 #设置主键
 ALTER TABLE p2 ADD PRIMARY KEY(pid);
 
 CREATE TABLE p3(
     pid INT(11) PRIMARY KEY AUTO_INCREMENT,
     pname VARCHAR(5)
 );
 #添加数据
 INSERT INTO  p3(pname) VALUES("张三");