MySQL数据库-知识笔记

本文详细介绍了SQL的四大类别:DDL(定义)、DML(操作)、DQL(查询)和DCL(控制)。主要内容包括数据库设计、表操作(如创建、修改、删除和约束),以及数据插入、修改、删除和查询的方法,涵盖了内连接、外连接和子查询等高级查询技巧。
摘要由CSDN通过智能技术生成

镇楼

命名规则:字母, 数字, 下划线 (命名不区分大小写)

 dir :查询当前磁盘目录  ;exit : 结束sql服务

SQL语句根据其功能被分为四大类:DDL(定义)、DML(操作,即增删改查)、DQL(查询)、DCL(控制)

一.数据库设计-DDL(定义)

1. 数据库操作

1.1 MySQL 登陆

 mysql -u root -p 

1.2 查询数据库

 show databases;

1.3创建数据库

 create database 数据库名;

 create database if not exists 数据库名;
 [if not exists] : 数据库不存在,则创建该数据库; 如果存在则不创建

1.4 使用数据库

use 数据库名;

1.5 删除数据库

 drop database 数据库名;

 drop database if exists 数据库名;
 [if exists] : 数据库存在,则执行删除;如果不存在,不执行

2. 表操作

表结构的操作也是包含四个部分:创建表、查询表、修改表、删除表

2.1建表

create table 表名 (
   字段1 字段1类型 [约束] [comment 注释],
   字段2 字段2类型 [约束] [comment 注释],
   ...
   字段n 字段n类型 [约束] [comment 注释]
 );
 [comment] : 后边跟字段注释


示例:

  create table xx (
      id int comment '编号',
      name varchar(20) comment '姓名',
      age int comment '年龄'
     );

2.2 约束

约束描述关键字
非空约束限制该字段值不能为nullnot null
唯一约束保证字段的所有数据都是唯一、不重复的unique
主键约束主键是一行数据的唯一标识,要求非空且唯一primary key
默认约束保存数据时,如果未指定该字段值,则采用默认值default
外键约束让两张表的数据建立连接,保证数据的一致性和完整性foreign key

示例:

primary key
-- 1.主键不能重复
 insert into t1 values (1,'许一');
 报错: [23000][1062] Duplicate entry '1' for key 'PRIMARY' 
   
-- 2.主键不能为空
 insert into t1(name) values ('许影');
 报错: [HY000][1364] Field 'id' doesn't have a default value  

-- 3.主键字符类型不能为字符串类型
 报错: [42000][1063] Incorrect column specifier for column 'id' 

 auto_increment(主键自增,需要搭配主键约束使用)

 1.指定字段进行插入
 insert into t2(name) values ('许影');

 2.插入整条记录, 给主键一个null
 insert into t2 values (null,'张三')

 unique 唯一约束
 报错: [23000][1062] Duplicate entry '许一' for key 'name'

删除表数据 (delete from 和 truncate 的区别 )

 1.delete from 表名; (删除数据后新建表主键id依旧按照上一张表的最大值往下排序)
 ​
 2.truncate 表名; (清空数据,新建表主键id重新开始, 相当于进行了 drop + create)

2.3 数据类型

常用数据类型
tinyint
int
decimal(M,D)示例:`height decimal(5,2) comment '身高',即最大可显示999.99`
datetime :  年月日时分秒   示例:`1000-01-01 00:00:00 至 9999-12-31 23:59:59`
char : 固定长度字符串
varchar : 变长类型字符串
text : 变长类型字符串
enum : 枚举类型,只能有一个值   示例: `gender enum('男','女','未知') comment '性别'`

注: char 与 varchar 都可以描述字符串,char是定长字符串,指定长度多长,就占用多少个字符,和字段值的长度无关 。而varchar是变长字符串,指定的长度为最大占用长度 。相对来说,char的性能会更高些。

示例:

 用户名 username ---长度不定, 最长不会超过50
 username varchar(50)
   
 手机号 phone ---固定长度为11
 phone char(11)

大致分为三类:数值类型 , 日期/时间类型 , 字符串类型

数值类型

类型大小有符号(SIGNED)范围无符号(UNSIGNED)范围描述
tinyint1byte(-128,127)(0,255)小整数值
DECIMAL依赖于M(精度)和D(标度)的值依赖于M(精度)和D(标度)的值小数值(精确定点数)
int/integer4bytes(-2147483648,2147483647)(0,4294967295)大整数值
bigint8bytes(-2^63,2^63-1)(0,2^64-1)极大整数值
float4bytes(-3.402823466 E+38,3.402823466351 E+38)0 和 (1.175494351 E-38,3.402823466 E+38)单精度浮点数值
double8bytes(-1.7976931348623157 E+308,1.7976931348623157 E+308)0 和 (2.2250738585072014 E-308,1.7976931348623157 E+308)双精度浮点数值

日期/时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

类型大小范围格式描述
date31000-01-01 至 9999-12-31YYYY-MM-DD日期值
time3-838:59:59 至 838:59:59HH:MM:SS时间值或持续时间
year11901 至 2155YYYY年份值
datetime81000-01-01 00:00:00 至 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
timestamp41970-01-01 00:00:01 至 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值,时间戳

示例 :

 生日字段 birthday ----年月日  
 birthday date

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型大小用途
char0-255 bytes定长字符串
varchar0-65535 bytes变长字符串
text0-65 535 bytes长文本数据
tinytext0-255 bytes短文本字符串

2.4 删除数据表

 drop table 表名;

2.5 修改数据表

2.5.1 修改数据类型
 alter table 表名 modify  字段名  新数据类型(长度);
 alter table 表名 change  旧字段名  新字段名  类型(长度)  [comment 注释]  [约束];

案例:修改qq字段的字段类型,将其长度由11修改为13

 alter table tb_emp modify qq varchar(13) comment 'QQ号码';

案例:修改qq字段名为 qq_num,字段类型varchar(13)

 alter table tb_emp change qq qq_num varchar(13) comment 'QQ号码';
2.5.2 删除字段
 alter table 表名 drop 字段名;

案例:删除tb_emp表中的qq_num字段

 alter table tb_emp drop qq_num;
2.5.3 修改表名
 rename table 表名 to  新表名;

案例:将当前的tb_emp表的表名修改为emp

 rename table tb_emp to emp;

2.6 查看数据表

查看表结构 :

 desc 表名;

查看建表语句 :

  show create table 表名;

取消当前命令

 \c
 ctrl + c 

非负,无符号

 unsigned ,防止出现负数
 示例: id int unsigned

二.数据库操作-DML(操作)

1.增加操作

向指定字段添加数据

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

全部字段添加数据

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

批量添加数据(指定字段)

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

批量添加数据(全部字段)

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

示例:

 -- 1.添加整条数据
 insert into t values (2,'李四',20,'女','2001-01-01',175);
 -- 2.添加指定字段
 insert into t(id, name) values (3,'王五');
 -- 3.添加多条数据
 insert into t(id,name,age) values (4,'马六',20),(5,'田七',21),(6,'许一',23);

2.修改操作

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

示例:

 -- 1.修改单个字段
 update t set age = 30 where id = 1;
 -- 2.修改多个字段
 update t set age = 18, gender = '男',birthday = '2002-02-02', height = 180 where id = 3;

3.删除操作

 delete from 表名  [where  条件] ;

示例:

 删除单条信息(注意添加条件)
 delete from t where id = 4;

三.数据库操作-DQL(查询)

1.基本查询

查询多个字段

 select 字段1, 字段2, 字段3  from  表名;
 示例:
 select id,name from t;

查询所有字段(通配符)

 select *  from  表名;

设置别名

 select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ]  from  表名;
 示例:
 select id as '编号',name as '姓名' from t;

去除重复记录

 select distinct 字段列表 from  表名;
 示例:
 select distinct * from t;

2.条件查询

 select  字段列表  from   表名   where   条件列表 ; -- 条件列表:意味着可以有多个条件

常用的比较运算符

比较运算符功能
>, >=, <, <=, =比较大小
<> 或 !不等于
between ... and ...在某个范围之内(含最小、最大值)
in(...)在in之后的列表中的值,多选一
like 占位符模糊匹配(_匹配单个字符, %匹配任意个字符)
is null是null

常用的逻辑运算符

逻辑运算符功能
and 或 &&并且 (多个条件同时成立)
or 或 ||或者 (多个条件任意一个成立)
not 或 !非 , 不是
 
示例:
 -- 1.> 大于
 select * from t5 where id > 2 ;
 -- 2.>= 大于等于
 select * from t5 where id >= 2 ;
 -- 3.< 小于
 select * from t5 where id < 2 ;
 -- 4.<= 小于等于
 select * from t5 where id <= 2 ;
 -- 5.= 等于
 select * from t5 where id = 2 ;
 -- 6.<> or !=  不等于
 select * from t5 where id != 2 ;
 -- 7.between...and...  在某个范围内
 select * from t5  where id between 2 and 4 ;
 -- 8.in(...)
 select * from t5 where id in (2,3,5);
 -- 9.like 占位符 模糊匹配
 -- %不限位数
 select * from t5 where username like '%三';
 -- _限一位数
 select * from t5 where username like '_三_';
 -- 10. is null / is not null
 select  * from t5 where age is null;

3.聚合函数

 select  聚合函数(字段列表)  from  表名 ;
函数功能
count统计数量 : 按照列去统计有多少行数据。
max最大值 : 算指定的最大值
min最小值 :计算指定的最小值
avg平均值 : 计算指定的平均值
sum求和 : 计算指定的数值和,如果不是数值类型,那么计算结果为0

注意: avg( )函数计算平均值时,如果一个字段值为空 ,则这个字段不参与计算

 示例:
 alter table t5 add column asset int default 0  comment '资产(元)' after age;
 -- 1、统计函数 count()统计有多少行数据
 select count(*) from t5;
 -- 2、求和函数 sum() 计算指定列的数值和,如果不是数值类型,那么计算结果为0
 select sum(asset) as 总资产 from t5;
 -- 3、求平均值函数 avg() 计算指定列的平均值 但如果一个字段值为空 ,则这个字段不参与计算
 select avg(asset)  from t5;
 -- 4、最值函数 max() min() 最大值、最小值
 select max(age) as 最大年龄 from t5;
 select min(age) as 最小年龄 from t5;
 ​

4.分组查询

分组: 按照某一列或者某几列,把相同的数据进行合并输出。

 select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
 示例:
 select  gender 性别, max(age) 最大年龄 ,min(age) 最小年龄, avg(age) 平均年龄 from t5 group by gender;

where与having区别(面试题)

  1. 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。

  2. 判断条件不同:where不能对聚合函数进行判断,而having可以。

5.排序查询

 select  字段列表  
 from   表名   
 [where  条件列表] 
 [group by  分组字段 ] 
 order  by  字段1  ASC(升序) , 字段2  DESC(降序) … ;
 示例:
 select * from t5 order by age asc; 
 select * from t5 order by age desc;

6.分页查询

 -- 分页 limit start(省略表示从0开始),length;
 select * from student limit 开始,每页的条数;
 ​
 示例:select * from t5 limit 0,3; 后面只能跟明确数字

7.多表查询

7.1 内连接

 select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

7.2 外连接

外连接分为两种:左外连接 和 右外连接。

左外连接语法结构:

 select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。

右外连接语法结构:

 select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;

右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。

示例:
 create table goods(
     id int primary key auto_increment,
     name varchar(20),
     good_id tinyint unsigned
 );
 create table goods_fl(
     id int primary key auto_increment,
     good_name varchar(20)
 );
 -- 1.内连接
 select g.id , g.name, g.good_id ,g2.good_name from goods g inner join goods_fl g2 on g.good_id = g2.id;
 -- 2.左连接
 select g.id , g.name, g.good_id ,g2.good_name from goods g left join goods_fl g2 on g.good_id = g2.id;
 -- 3.右连接
 select g2.good_name, g.id , g.name, g.good_id from goods g right join goods_fl g2 on g.good_id = g2.id;
 ​

7.3 子查询

SQL语句中嵌套select语句,称为嵌套查询,又称子查询。

 SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );

未完待续。。。。

  • 25
    点赞
  • 51
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值