MySQL笔记

最新推荐文章于 2023-02-26 14:31:03 发布

橘然`

最新推荐文章于 2023-02-26 14:31:03 发布

阅读量180

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_65286290/article/details/128397849

版权

# DDL

## 数据库操作

```sql
-- 查看所有数据库
show databases;
-- 创建数据库
create database mydb1;
create database if not exists mydb1;
-- 选择使用哪一个数据库
use mydb1;
-- 删除数据库
drop database mydb1;
drop database if exists mydb1;
-- 修改数据库编码
alter database mydb1 character set utf-8; -- alter：改变
```

## 数值类型

> 类型大小用途默认有符号的 tintint unsigned无符号
>
> 整形
> tinyint 1bytes 小整数值（-128，127）（0，255）
> smallint 2bytes 大整数值
> mediumint 3bytes 大整数值
> int或integer 4bytes 大整数值
> bigint 8bytes 极大整数值
>
> 浮点型
> float 4bytes 单精度浮点数值
> double 8bytes 双精度浮点数值
> decimal 依赖于M和D 小数值 ☞特殊 decimal（M,D) -- EG decimal（5，2）整数小数共五位小数保留2位
>
> 字符串类型
> char 定长字符串
> varchar 变长字符串 ☞EG name varchar（20）指最长20个字符
> tinyblob 不超过255个字符
> tinytext 短文本字符串
> blob 二进制形式的长文本数据
> text 长文本数据
> mediumtext 中等长度文本数据
> longblob 二进制形式的极大文本数据
> longtext 极大文本数据
>
> 日期类型
> date 年月日
> time 时分秒
> year 年
> datetime 年月日时分秒
> timestamp 混合日期和时间值，时间戳获取当前时区的时间

## 表及对表的其他操作

```sql
create table if not exists student(
sid int,-- 学号
name varchar(20),
gender varchar(10),-- 性别
age int,
birth date,
address varchar(20),-- 地址
score double
);

-- 1 查看当前数据库所有的表
show tables;
-- 2 查看指定表的创建语句
show create table student;
-- 3 查看表结构
desc student;-- desc 描述
-- 4 删除表
drop table student;

-- 修改表结构
-- 1 添加列：alter table 表名 add 列名类型（长度）【约束】；
-- 需求：为student表添加一个新的字符段为：系别 dept 类型为 varchar（20）
# alter table 表名 add 列名类型（长度）【约束】;//alter
alter table student add dept varchar(20);

-- 2 修改列名和类型： alter table 表名 change 旧列名新列名类型（长度）【约束】；
-- 需求：为student表的dept字段更换为department varchar（30）
# alter table 表名 change 旧列名新列名类型（长度）【约束】;
alter table student change dept department varchar(30);

-- 3 删除列：alter table 表名 drop 列名
-- 删除student表中的department;
# alter table 表名 drop 列名;
alter table student drop department;

-- 4 修改表名： rename table 表名 to 新表名
-- 需求：将student表的名字改为stu
#rename table 表名 to 新表名
rename table student to stu;
-- 查询表
select 列名 from 表名 where 条件 and 条件;
select * from 表名 where 条件 and 条件;#*代表所有
select * from 表名 where 某一列名 like '%华%'; # 查询表中某项有华字的行
-- 描述表
describe stuTable1
```

# DML
> 增删改查：insert delete update select
## 数据插入
```sql
-- 格式1：insert into 表（列名1，列名2，列名3...）value（值1，值2，值3...）；
insert into
student(sid,name,gender,age,birth,address,score)
values
(1001,'张三','男',18,'2001-12-23','北京',85.5);

-- 一次插入多行
insert into
student(sid,name,gender,age,birth,address,score)
values
(1002,'王五','女',19,'2000-12-23','上海',82.5),
(1003,'李四','男',18,'2001-12-2','南京',80);
-- 只给某一列赋值
insert into student(sid)values(1005);
-- 格式2：insert into 表名 value（值1，值2，值3...);//向表中插入所有列
insert into student values(1006,'张华','女',21,'1999-01-08','广州',79);
-- 一次插入多行
insert into student values(1007,'钱博','男',21,'2005-05-08','武汉',79),
(1008,'李芳','女','24','1998-05-04','武汉',89);
```

## 数据修改

```sql
-- 格式1：update 表名 set 字段名=值，字段名=值...；
-- 格式2：update 表名 set 字段名=值，字段名=值...where 条件；
-- #需求1.将所有学生的地址修改为重庆
update student set address='重庆';
-- #需求2.将id为1003的学生的地址修改为北京
update student set address='北京' where sid=1003;
#需求2.1 将id大于1003的学生的地址修改为北京
update student set address='北京' where sid>1003;
-- #需求3.将id为1005的学生的地址修改为北京，成绩修改为100
update student set address='北京',score=100 where sid=1005;
```

## 数据删除

```sql
-- 3 数据的删除
-- 格式：delete from 表名【where条件】；
-- truncate table 表名或者 truncate 表名
-- #需求1.删除sid为1002的学生数据
delete from student where sid=1002；
-- #需求2.删除表所有数据//delete只删除内容
delete from student;
-- #需求3.清空表数据//truncate:截断将整个表删除然后创建新表（为空）
truncate table student;
truncate student;
```

## 约束

> **primary key:**用于唯一标识对应的记录不允许为空
>
> **foreign key:** 外键约束
>
> **not null:** 非空约束
>
> **unique:**唯一性约束允许空值
>
> **defaulit:**默认值约束，用于设置字段的默认值

### 主键约束

```sql
-- 1 主键约束
-- 方法一-语法：
-- create table 表名（...<字段名><数据类型>primary key...);
-- 主键约束
-- 单列主键
-- 方法一-实现：
create table emp1(
   id int primary key,#这一列数据不能为空且唯一唯一标识这一行
   name varchar(20),
   deptId int,
   salary DOUBLE
);

-- 方法2-语法：
-- create table 表名（...【constraint<约束名>】primary key 【字段名】);
-- 方法2-实现
create table emp2(
id int,
name varchar(20),
deptId int,
salary double,
constraint pk1 primary key(id)-- constraint pk1可以省略
);

-- 主键的作用
insert into emp2(id,name,deptId,salary)values(1001,'张三',10,5000);
insert into emp2(id,name,deptId,salary)values(1001,'李四',20,3000);-- 这个与上行语句不能同时执行原因为id相同 id不能为重复值

insert into emp2(id,name,deptId,salary)values(null,'李四',20,3000);-- 不能执行 id为key不能为空

-- 多列主键（联合主键）
-- 所谓的联合主键，就是这个主键是由一张表中多个字段组成的
-- primary key （字段1，字段2，字段3，...，）
create table emp3(
name VARCHAR(20),
deptId int,
salary DOUBLE,
constraint pk2 primary key(name,deptId)
);

insert into emp3 values('张三',10,5000);√
insert into emp3 values('张三',20,5000);√
insert into emp3 values('王五',10,5000);√
-- 联合主键每一个都不能为空
insert into emp3 values(null,30,5000);×
insert into emp3 values('赵六',null,5000);×
insert into emp3 values(null,null,5000);×

-- 联合主键每一个合起来不能重复
insert into emp3 values('张三',10,4000);×
insert into emp3 values('张三',10,5000);×

-- 添加单列主键
create table emp4(
eid int,
name varchar(20),
deptId int,
salary double
);
alter table emp4 add primary key(eid);

-- 添加多列主键
create table emp5(
eid int,
name varchar(20),
deptId int,
salary double
);

alter table emp5 add primary key(name,deptId);
-- 删除单列主键
alter table emp4 drop primary key;
-- 删除联合主键
alter table emp5 drop primary key;
```

### 自增长约束

```sql
-- auto_increment实现主键自增长所以自增长约束只能有一个
create table t_user1(
id int primary key auto_increment,
name varchar(20)
)；

insert into t_user1 values(null,'张三');
insert into t_user1(name) values('李四');
-- 指定自增长的初始值
-- 方式1：创建表时指定
create table t_user2(
id int primary key auto_increment,
name varchar(20)
)auto_increment=100;

insert into t_user2 values(null,'张三');

-- 方式2：创建表之后指定
create table t_user3(
id int primary key auto_increment,
name varchar(20)
);

alter table t_user3 auto_increment=200;

insert into t_user3 values(null,'张三');
insert into t_user3 values(null,'李四');

-- 删除表数据子增长情况
delete from t_user1;-- delete删除数据后，自增长在最后一个值的基础上加1

truncate t_user3;-- truncate删除数据无法保留上次自增的记录
```

### 非空约束

```sql
-- 非空约束not NULL -- modify：修改

-- 方式一：字段名数据类型 not null;#创建表时

-- 方式二：alter table 表名 modify 字段类型 not null；#创建表后 modify：修改

-- 去掉非空约束
-- alter table 表名 modify 字段类型；
```

### 唯一约束

```sql
-- 唯一约束
-- 方式一：字段名数据类型 unique #unique：独一无二的

-- 方式二：alter table 表名 add constraint 约束名 unique(列）；#constraint：约束
create table t_user4(
id int,
name varchar(20),
phone_number varchar(20) unique#方式一：指定唯一约束
);

#方式二：格式：alter table 表名 add constraint 约束名（列名）；
alter table t_user4 add constraint unique_pn unique(phone_number);# 方式二

-- 在MySQL中null和任何值都不同甚至是null
-- 删除唯一约束
-- 格式：alter table 表名 drop index 唯一约束名；
#当没有唯一约束名时当前列名就是唯一约束名
alter table t_user4 drop index unique_pn;
```

### 默认约束

```sql
-- 默认约束【default】不给值时默认
-- 方式一：创建表时直接加default
-- 方式二：alter table 表名 modify 列名类型 default 默认值；
create table t_user5 (
id int,
name varchar(20),
address varchar(20)-- default '深圳' 方法一
);

alter table t_user5 modify address varchar(20) default '深圳';-- 方式二

insert into t_user5(id,name)values(1001,'张三');-- 默认address为深圳
insert into t_user5(id,name)values(1002,'李四');
insert into t_user5(id,name,address) values(1002,'李四','上海');-- address为上海不用默认值
insert into t_user5(id,name,address) values(1002,'李四',null);-- 为null，不为默认值

-- 删除默认约束
-- 格式：alter table 表名 modify 列名类型 default null;
alter table t_user5 modify address varchar(20) default null;
```

### 零填充约束

```sql
-- 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
-- zerofill默认为int(10)
-- 当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128~+127，无符号为0~256。

-- 比如：现在设置某个字段的长度为5，那么真实数据是1，那么显示在你的数据库的是00001；

create table t_user12 (
id int zerofill , -- 零填充约束默认为int(10)
name varchar(20)
);
alter table t_user12 modify id int;
-- 1. 添加约束
create table t_user12 (
id int zerofill , -- 零填充约束
name varchar(20)
);

insert into t_user12 values(123, '张三');
insert into t_user12 values(1, '李四');
insert into t_user12 values(2, '王五');
```

![img](https://img-blog.csdnimg.cn/e1636e06b9ca48ac899f2c2bb7725f9e.png)

### 外键约束-唯一一个约束两个表的约束

```sql
create table user_info(
id char(36) primary key,
user_name varchar(30) not null,
password varchar(30) not null
)

create table address(
id char(36) primary key,
user_info_id char(36),
real_name varchar(8) not null,
mobile char(11) not null,
address varchar(150) not null,
#下面这条语句就是在user_info_id添加了外键，指向user_info表的主键
foreign key(user_info_id) references user_info(id)
)
```

# DQL

数据准备

```sql
create database mydb2;
use mydb2;
create table product(
pid int primary key auto_increment,
pname varchar(20) not null,
price double,
category_id varchar(20)
);

insert into product values(null,'海尔洗衣机',5000,'c001'),
(null,'美的冰箱',3000,'c001'),
   (null,'格力空调',5000,'c001'),
       (null,'九阳电饭煲',5000,'c001'),
       (null,'啄木鸟衬衣',300,'c002'),
(null,'恒源祥西裤',800,'c002'),
       (null,'花花公子夹克',440,'c002'),
       (null,'劲霸休闲裤',266,'c002'),
       (null,'海澜之家卫衣',180,'c002'),
       (null,'杰克琼斯运动裤',430,'c002'),
       (null,'兰蔻面霜',300,'c003'),
(null,'雅诗兰黛精华水',200,'c003'),
       (null,'香奈儿香水',350,'c003'),
   (null,'SK-II神仙水',350,'c003'),
   (null,'资生堂粉底液',180,'c003'),
       (null,'老北京方便面',56,'c004'),
       (null,'良品铺子海带丝',17,'c004'),
(null,'三只松鼠坚果',88,null);
```

## 基本查询

```sql
-- 1.查询所有商品【select】
select * from product;
-- 2.查询商品名和商品价格
select pname,price from product;
-- 3.别名查询，使用的关键字是as（as可以省略）
-- 3.1表别名
select * from product as p;#方式一
select * from product p;#方式二

-- 3.2列别名
select pname as '商品名',price '商品价格' from product;

-- 4.去掉重复值【distinct】
select distinct price from product;
select distinct * from product;

-- 5.查询结果是表达式（运算查询）：所有商品的加价10元进行显示。
select pname,price+10 new_price from product;
```

## 算术运算符及条件查询

```sql
select 6+2;
-- 将所有商品的价格加十元
select pname,price+10 as new_price from product;

-- 将所有的商品价格上调10%
select pname,price*1.1 as new_price from product;

-- 查询商品名称为“海尔洗衣机”的商品所有信息
select * from product where pname = '海尔洗衣机';

-- 查询价格为800的所有商品
select *from product where price = 800;

-- 查询商品不是800的所有商品
select *from product where price != 800;
select *from product where price <> 800;
select *from product where not(price = 800);

-- 查询价格大于60的所有商品
select *from product where price >= 60;

-- 查询价格在200-1000之间的所有商品
select *from product where between 200 and 1000;
select *from product where price >= 200 and price <= 1000;
select *from product where price > 200 && price <= 1000;

-- 价格是200或800的所有商品
select *from product where price in(200，800);
select *from product where price = 200 or price = 800;
select *from product where price = 200 || price = 800;

-- 查询含有“鞋”字的所有商品# %用于匹配任意字符
select *from product where pname =like'%鞋';#前面是什么不管，只要最后一个是鞋就可以
select *from product where pname like '%鞋%';

-- 查询以‘海’字开头的所有商品
select *from product where pname like '海%';

-- 查询第二个字为“寇”的所有商品
select *from product where pname like '_蔻%';-- 下划线匹配单个字符

-- 查询category_id为null的商品
select *from product where category_id is null;

-- 查询category_id不是null的商品
select *from product where category_id is not null;

-- 使用least求最小值#如果有一个null，则不会比较结果直接为null
select least(10,5,20) as small_number;
select least(10,null,20) as small_number;

-- 使用greatest求最大值#如果有一个null，则不会比较结果直接为null
select greatest(10,5,20) as small_number;
```

## 位运算符（了解）

```sql
select 3&5;-- 位与
/*两个都为一则是一否则为0
0011 一共32位，左面省略了
0101
---------
0001
*/

select 3|5；-- 位或
/*两个只要有一个为一结果为一
0011
0101
--------
0111
*/

select 3^5;-- 位异或
/*相同为零不同为一
0011
0101
-------
0110
*/

select 3>>1;-- 位右移，左边补零
/*
0011 >>1 ---->0001
*/