MySQL数据库基本操作

Mysql

目录

• Mysql
  • 特点
  • 数据定义语言DDL
    • 对数据库的常用操作
    • 对表的常用操作-创建表
      • 创建表的格式
      • 数据类型
        • 字符串类型
        • 数值类型
          • 整型
          • 浮点型
        • 日期和时间类型
    • 对表的其他操作
    • 修改表结构
      • 增加列
        • 语法格式
        • 修改表结构实例
      • 修改列名和类型
        • 语法格式
        • 实例
      • 修改表删除列
        • 语法格式
        • 实例
        • //删除student表的department列
      • 修改表名
        • 语法格式
        • 实例
  • 数据操作语言DML
    • 数据插入insert
      • 语法格式
        • 向表中插入指定列（一一对应）
        • 向表中插入所有列
        • 举例
    • 数据修改update
      • 语法格式
      • 实例
    • 数据删除delete
      • 语法格式
      • 实例
    • 数据删除truncate
      • 语法格式
      • 实例
    • delete和truncate区别
  • Mysql约束
    • 约束简介
      • 概念
      • 作用
      • 分类
    • 主键约束
      • 概念
      • 主键作用
        • 主键约束列唯一
        • 主键约束列不能为空
      • 操作
        • 添加单列主键
          • 定义字段的同时指定主键
          • 定义字段后再指定主键
        • 添加多列主键（联合主键）
          • 语法格式
          • 实现
        • 通过修改表结构添加主键
          • 语法格式
          • 实现
        • 删除主键约束
          • 语法格式
          • 实现
    • 自增长约束
      • 概念
      • 语法格式
      • 实例
        • 在user1表中为id 设置自增长约束
        • 不指定自增字段初值
        • 创建表时指定自增字段初值
        • 创建表后指定自增字段初值
        • delete和truncate在删除后自增列的变化
          • delete数据后自动增长从断点开始
          • truncate数据后自动增长从默认起始值1开始
      • 特点
    • 非空约束
      • 概念
      • 语法格式
      • 实例
        • 创建表时指定非空约束
        • 创建表后指定非空约束
        • 不正确的指定方式
        • 正确的指定方式(插入内容要与定义数据类型匹配)
        • 删除非空约束
          • 语法格式
          • 实例
    • 唯一约束
      • 概念
      • 语法格式
      • 实例
        • 新建user6表并为id添加唯一约束
        • 为已有表user1的id添加唯一约束
        • 删除唯一约束
    • 默认约束
      • 概念
      • 语法格式
      • 实例
        • 建表时添加约束
        • 建表后添加约束
    • 零填充约束
      • 概念
      • 操作
  • 数据查询语言DDL
    • 语法格式
    • 简化版语法格式
    • 实际操作
      • 数据准备
      • 简单查询
        • 1.查询所有商品
        • 2.查询商品名和商品价格
        • 3.别名查询（起别名）
          • 为表起别名
          • 为列其别名
        • 去掉重复值
        • 运算查询
    • 运算符
      • 算术运算符
      • 比较运算符
      • 逻辑运算符
      • 运算符使用实例
    • 排序查询
      • 语法格式
      • 特点
      • 操作实例
    • 聚合函数查询
      • 常用聚合函数
      • 实例
        • 查询商品的总条数
        • 查询价格大于200商品的总条数
        • 查询分类为c001的所有商品的总和
        • 查询商品的最大/最小价格
        • 查询分类为'c002'的所有商品的平均价格
      • 聚合函数对null的处理
        • 测试
    • 分组查询group by
      • 语法格式
      • 实例
      • having
        • 实例
      • 注意
    • 分页查询limit
      • 语法格式
        • 方式1 显示前n条
        • 方式2 分页显示（从m到n）
      • 实例
        • 查询produce的前五条数据
        • 从product查询数据并从第四条开始显示，显示三条
    • 数据导入语句
      • 语法格式
      • 实例

特点

• 用C/C++语言编写，保证了源码的可移植性

• 支持多个操作系统（如Windows、Linux、Mac OS等等）

• 支持多线程，充分利用CPU资源（同时进行多任务）

• 为多种编程语言提供API（可通过JAVA、PHP、Python操作mysql）

• 优化sql算法，提高查询速度

• 源代码开放、自主性强、使用成本低

• 历史悠久、用户群体大，可及时获取帮助

数据定义语言DDL

DDL(Data Definition Language)

对数据库的常用操作

功能	SQL语句
查看所有数据库	show databases;
创建数据库	create database [数据库名];
使用/切换数据库	use [数据库名];
删除数据库	drop [存在的数据库名称]；
修改数据库编码	alter database [数据库名] character set utf8;

对表的常用操作-创建表

创建表的格式

create table [表名] 
（
//Mysql中一般以分号作为语句的结束，期间换行无影响
字段名1 类型（宽度） [约束条件] [内容 '备注/说明'],
字段名2 类型（宽度） [约束条件] [内容 '备注/说明'],
.
.
.
)[对表的设置]；

创建表是构建一张空表，指定表名、表的列数、表的列名以及每一列的存储类型

实例

use test；//使用名为test的数据库
create table student (
sname varchar(20),
snum int,
sgender varchar(2)
);

数据类型

一般遵循够用就行的原则

字符串类型

类型	大小	用途
blob	0-65535 bytes	二进制形式的长文本数据
char	0-255 bytes	定长字符串
varchar	0-65535 bytes	变长字符串
text	0-65535 bytes	长文本数据
tinytext	0-255 bytes	短文本字符串
mediumtext	\	中等长度文本数据
longblog	\	二进制形式的极大文本数据
longtext	\	极大文本数据
tinyblob	0-255 bytes	不超过255个字符的二进制字符串
midiumblob	\	二进制形式的中等长度文本数据

数值类型

整型

//一般直接用int

类型	范围（有符号）	范围（无符号）	用途
tinyint	(-128，127)	（0,255）	小整数值
smallint	(-32768,32767)	(0,65535)	大整数值
int/integer	(-2147483648,2147483647)	(0,188446744073709551615)	大整数值
bigint	\	\	极大整数值

浮点型

类型	用途
float	单精度浮点数
double	双精度浮点数
decimal	小数值

日期和时间类型

类型	格式	用途
date	YYYY-MM-DD	日期值
time	HH:MM:SS	时间值或持续时间
year	YYYY	年份值
datetime	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

对表的其他操作

功能	SQL
查看当前数据库所有表的名称	show tables;
查看指定某个表的创建语句	show create table [表名]；
查看表结构	desc 表名；
删除表	drop table 表名；

修改表结构

增加列

语法格式

alter table [表名] add [列名] 类型（长度） [约束]；

修改表结构实例

//为student表添加一个新的字段为：系别dept 类型为 varchar(20)

alter table student add dept varchar(20);

修改列名和类型

语法格式

alter table [表名] change [旧名][新名] 类型（长度） 约束；

实例

//为student表的dept字段更换为department varchar(30)

alter table student change dept department varchar(30);

修改表删除列

语法格式

alter table [表名] drop [列名]；

实例

//删除student表的department列

alter table student drop department;

修改表名

语法格式

rename table [原表名] to [新表名]； 

实例

//把student表改成stu

rename table student to stu;

数据操作语言DML

DML(Data Manipulation Language),用来对数据库中表的数据记录进行更新

数据插入insert

语法格式

向表中插入指定列（一一对应）

inser into [表名](列名1，列名2，列名3...) values (数据1，数据2，数据3....)；

向表中插入所有列

insert into [表名] values (数值1，数值2，数值3.....)；

举例

格式1

insert into student(snum,sname,sgender,sage)
values (001,'张三','男',18);
insert into student(sname,snum,sgender,sage)
values ('吴三桂',005,'男',18);
//只要一一对应，也可插入指定列，其他列默认为空值
如 insert into student (sname) values('王五');

格式2

insert into student 
insert into student values (003,'王四','男',23);
insert into student values (003,'男','赵四',20);

//按输入的顺序往表中输入数据，只要数据类型致就能实现，如此处 赵四 同 男 都是varchar类型的，按键入的顺序进行录入即可，但赵四不可和003换位置。

数据修改update

语法格式

update [表名] set [字段名/列名]=值...;//对所有行进行修改
update [表名] set [列名]=值...where [条件]；//对符合条件的数据进行修改

实例

将编号为5的学生年龄改为26

update student set sage=26 where snum=005;

将编号为4的学生性别改为女，姓名改为李娜

update student set sgender='女',sname='李娜' where snum=005;

数据删除delete

语法格式

delete from [表名] where [条件]；

实例

删除年龄为99岁学生的数据

delete from student where sage=99;

清空表数据

delete from student；

数据删除truncate

语法格式

truncate table [表名] ；//table可省略

实例

删除teacher表所有数据

truncate teacher;

delete和truncate区别

原理不同，delete只删除内容，而truncate类似于drop table(将整个表删除、再创建该表)

Mysql约束

约束简介

概念

约束（constraint）实际上就是表中数据的限制条件

作用

保证表中数据的记录完整性和有效性(如用户表中身份证列的值不能重复、手机号不能为空等)

分类

约束名	关键字
主键约束	primary key（PK）
自增长约束	auto_increment
非空约束	not full
唯一性约束	unique
默认约束	default
零填充约束	zerofill
外键约束	foreign key

主键约束

概念

• 主键约束是一个或多个列的组合，其值能唯一的标识表中的每一行，方便在数据库管理系统（RDBMS）中尽快的找到某一行
• 关键字为primary key
• 每个表最多只允许一个主键
• 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值
• 当创建主键的约束时，系统默认会在所在的列和列的组合上建立对应的唯一索引

主键作用

主键约束列唯一

//只能有一门课的编号为001

//联合主键不能相同，但仅当组成联合主键的两个字段均相同时，才会触发约束

主键约束列不能为空

操作

添加单列主键

创建单列主键有两种方式，一种是再定义字段的时候同时指定主键，另一种是定义完字段后指定主键

定义字段的同时指定主键

语法格式

create table [表名](
...
[字段名][数据类型]primary key
...
);

实现

//指定薪水表中的员工编号为主键

create table xinshuihuizong(
	id int primary key,
	name varchar(4),
	salary double
);

定义字段后再指定主键

语法格式

create table [表名](
...
constraint [约束名] primary key (字段名)
);

实现

//指定课程中的课程编号为主键

create table course(
cnum int,
cname varchar(20),
cscore float,
constraint pk1 primary key(cnum) //constraint pk1 部分可省略
);

添加多列主键（联合主键）

联合主键是指主键是由一张表中的多个字段组成

• 联合主键是指主键是由一张表中的多个字段组成

• 由多个字段组成的主键，不能在字段名后面声明主键约束

  一张表只有一个主键，联合主键也是一个主键

语法格式

create table [表名](
...
primary key(字段1，字段2，...,字段n)
);

实现

create table teacher(
tame varchar(20),
tid int,
salary double,
primary key(tname,tid)
);

通过修改表结构添加主键

语法格式

alter table [表名] add primary key [字段名]；

实现

//添加单列主键

create table test1(
id int,
name varchar(20),
salary double,
);//此处新建表是为了和之前的表做区分
alter table test1 add primary key(id);

//添加联合主键

（此处仍使用test1表，但记得将test1表中的id取消主键）

alter table test1 add primary key (id,name);

删除主键约束

语法格式

alter table [表名] drop primary key;

实现

//删除test1中的联合主键

alter table test1 drop primary key;

自增长约束

概念

主键定义为自增长后，这个主键的值就不再需要用户输入数据了，由数据库系统根据定义自动赋值

每增加一条记录，主键会自动以相同的步长进行增长

语法格式

[字段名] 数据类型 auto_increment

实例

在user1表中为id 设置自增长约束

create table user1(
id int primary key auto_increment,
name varchar(20)
);

不指定自增字段初值

//按默认值1

insert into user1 values
(null,'mimi'),
(56,'iiji'),
(null,'milly'),
(11,'ihpq'),
(null,'pijsk');

创建表时指定自增字段初值

若第一条数据指定了字段的初值，新增加的记录按照设置的初值开始自增

create table user2(
id int primary key auto_increment,
name varchar(20)
) auto_increment=10;

insert into user2 values
(null,'lisa'),
(null,'bobby'),
(null,'blues')；

创建表后指定自增字段初值

//对user3表进行操作

create table user3(
id int primary key auto_increment,
name varchar(20)
);
alter table user3 auto_increment=20;
insert into user3 values
(null,'marry'),
(null,'hellen'),
(null,'bill');

delete和truncate在删除后自增列的变化

delete数据后自动增长从断点开始

delete from user3;
insert into user3 values
(null,'marry'),
(null,'hellen'),
(null,'bill');

//删除前最后一个序号（断点）为22，再次添加数据时默认从23开始算

truncate数据后自动增长从默认起始值1开始

truncate user3;
insert into user3 values
(null,'marry'),
(null,'hellen'),
(null,'bill');

特点

• 默认情况下初始值为1（不指定第一行自增长约束型主键数据），每增加一条数据，字段值自动加一

• 一个表中只能有一个字段使用自增长（autu_increment）约束,且该字段必须有唯一索引，以免序号重复

  //即自增长约束只能使用单列主键

• auto_increment 约束的字段必须具有非空属性（not null）

• auto_increment 约束的字段必须是整数类型（int、smallint、bigint等）

• auto_increment 约束字段的最大值受到数据类型的约束 //达到上限后该类型失效

非空约束

概念

Mysql非空约束(not null)指字段的值不能为空。对于使用了非空约束的字段，若用户在添加数据时没有指定值，数据库系统就会报错

语法格式

指定方式一:     [字段名][数据类型] not null;  //创建表时指定
指定方式二：     alter table [表名] modify [字段名/列名] [类型] not null；//创建表后指定非空约束
删除非空约束：

实例

创建表时指定非空约束

create table t1(
id int not null,
name varchar(20) not null
);

创建表后指定非空约束

create table t2(
id int,
name varchar(20),
address varchar(20)
);
alter table t2 modify id int not null;
alter table t2 modify name varchar(20) not null;

不正确的指定方式

insert into t2(id,name) values(1000,null);

正确的指定方式(插入内容要与定义数据类型匹配)

insert into t2(id,name) values(1000,'null');
insert into t2(id,name) values(1000,'');

删除非空约束

语法格式

alter table [表名] modify 字段 类型

实例

alter table t2 modify id int;
alter table t2 modify name varchar(20);

唯一约束

概念

唯一约束（unique key）是指所有记录字段的值不能重复出现

如为id字段添加上唯一性约束后，每条记录的id值都是唯一的，不会出现重复的情况

语法格式

[字段名][数据类型] unique //定义时添加约束
alter table [表名] add constraint [约束名] unique(列)；//为此约束起名字以方便删除约束，可以省略
alter table [表名] drop index [唯一约束的名称]；

实例

新建user6表并为id添加唯一约束

create table user6(
id int unique,
name varchar(20),
salary double
); 

为已有表user1的id添加唯一约束

alter table test2 add constraint unique_1 unique(id); 

删除唯一约束

alter table test2 drop index unique_1;
insert into test2 values 
(null,78),
(null,88);

//Mysql中的null和任何值都不相同（包括自己）

默认约束

概念

Mysql默认值约束（default）用来指定某列的默认值

语法格式

[列名][数据类型] default[默认值]；//建表时指定默认约束
alter table [表名] modify[列名][类型] default[默认值]；//建表之后添加默认约束

实例

建表时添加约束

//在test1表中指定默认地址为nanjing,若人为指定为其他城市则以指定为准

create table test1
(id int ,
name varchar(20),
address varchar(20) default 'nanjing');

建表后添加约束

alter table test2 modify address varchar(20) default 'qingdao'; 

零填充约束

概念

插入数据时，字段长度小于定义长度时，会在该值的前面补上相应的0

零填充(zerofill)约束默认为int(10)

使用zerofill时，默认自动设置无符号（unsigned）属性，使用unsigned属性后，数值范围是原来的两倍

//如有符号为-128~+127，无符号为0-126

操作

create table test3(
id int zerofill,
name varchar(20)
);//设置零填充约束
alter table test3 modify id int;//删除零填充约束

数据查询语言DDL

语法格式

select [all/distinct]
<列名>[别名]，
<列名>[别名]，....
from <表名或视图名> [别名]，<表名或视图名> [别名]...，
[where <条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc/desc]]
[limit <数字或列表>];//筛选结果

简化版语法格式

select 
*/<列名>
from <表名>
where <条件>

实际操作

数据准备

//对数据库test2中的商品表进行操作

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');
insert into product values (null,'啄木鸟村衣',300,'c002');
insert into product values (null,'恒源祥西裤',800,'c002')；
insert into product values (null,'花花公子夹克',440,'c002');
insert into product values (null,'劲霸休闲裤',266,'c002');
insert into product values (null,'海澜之家卫衣',180,'c002');
insert into product values (null,'杰克琼斯运动裤',430,'c0o');
insert into product values (null,'兰蔻面霜',300,'c003');
insert into product values (null,'雅诗兰黛精华水',200,'c003');
insert into product values (null,'香奈儿香水',350,'c003');
insert into product values (null,'SK-II神仙水',350,'c003');
insert into product values (null,'资生堂粉底液',180,'c003')；
insert into product values (null,'老北京方便面',56,'c004');
insert into product values (null,'良品铺子海带丝',17,'c004');
insert into product values (null,'三只松鼠坚果',88,null);

简单查询

1.查询所有商品

select * from product;

2.查询商品名和商品价格

select pname,price from product;

3.别名查询（起别名）

为表起别名

select * from product as p;//as可省略

为列其别名

select pname as '商品名'，price '商品价格' from product；

去掉重复值

select distinct price from product;

运算查询

将所有商品加价十元展示

select pname, price+10 newprice from product; //为price+10起新名newprice

运算符

算术运算符

算术运算符	说明
+	加法运算
-	减法运算
*	乘法运算
/或DIV	除法运算，返回商
%或MOD	求余运算，返回余数

比较运算符

比较运算符	说明
=	\
<和<=	\
>和>=	\
<=>	安全的等于，两个操作码均为NULL时，其所得值为1；而仅一个操作码为NLLL时，其所得值为0
<>或!=	不等于
ISNULL 或IS NULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
between and	判断一个值是否落在两个值之间
in	判断一个值是in列表中的任意一个值
not in	判断一个值不是in列表中的任意一个值
like	通配符匹配
regexp	正则表达式匹配

逻辑运算符

逻辑运算符	说明
not或者 ！	逻辑非
and或者&&	逻辑与
or 或者 ||	逻辑或
XOR	逻辑异或

运算符使用实例

将每件商品的价格上调10%(算术)

select pname, price*1.1 newprice 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 price<=1000&&price>=200;
select * from product where price between 200 and(&&) 1000;

查询价格是200或者800的所有商品

select * from product where price in(200,800);
select * from product where price =200 or price=800;

查询含有‘裤’的所有商品

select * from product where pname like '%裤%';

查询以‘海’开头的所有商品

select * from product where pname like '海%';

查询第二个字为‘蔻’的所有商品

select * from product where pname like '_蔻%';//下划线匹配单个字符

查询categery_id为null的商品

select * from product where category_id IS NULL;

查询categery_id不为null的商品

select * from product where category_id IS NOT NULL;

使用least求最小值

//求值的数中有null，值为null

select least(10,5,20) as smallnum;

使用greatest求最大值

select greatest(10,20,30) as bignum;

排序查询

语法格式

select 
<字段名1>，<字段名2>，.....
from [表名]
order by <字段名1> [asc/desc],<字段名2>[asc/desc]....;

特点

• asc代表升序，desc代表降序，默认升序
• order by 可以支持单个字段，也可以支持多个字段（表达式、函数、别名）
• order by 子句放在查询语句的最后面（limit子句除外）

操作实例

使用价格降序排序

select * from product order by price desc; 

在价格降序排列的基础上以分类降序

select * from product order by price desc,category_id desc; 

显示商品的价格（去重复），并降序排列

select distinct price from product order by price desc;

聚合函数查询

纵向查询和之前查询的区别在于聚合函数查询是对列的值进行计算返回一个单个的值，且聚合函数忽略空值

常用聚合函数

聚合函数	作用
count()	记录不为null的行数
sum()	计算列的数值和（若指定列类型非数值类型，计算结果为0）
max()	计算列的最大值（指定列若是字符串类型，那么使用字符串排序运算）
min()	计算列的最小值（指定列若是字符串类型，那么使用字符串排序运算）
avg()	计算列的数值求平均值（若指定列类型非数值类型，计算结果为0）

实例

查询商品的总条数

select count(*) from product;
select count(pid) from product;//此处由于pid为不重复的主键，所以相当于查询所有数据

查询价格大于200商品的总条数

select count(*)from product where price>200;

查询分类为c001的所有商品的总和

select sum(price) from product where category_id='c001';

查询商品的最大/最小价格

select max(price) maxprice from product;
select min(price) minprice from product;
select max(price) maxprice,min(price) minprice from product;

查询分类为'c002'的所有商品的平均价格

select avg(price) from product where category_id='c002';

聚合函数对null的处理

count、sum、avg、min、max函数忽略null值的存在

测试

create table test_null(
c1 varchar(20),
c2 int
);
insert into test_null values('aaa',3);
insert into test_null values('bbb',3);
insert into test_null values('ccc',null);
insert into test_null values('ddd',6);
select count(*),count(1),count(c2) from test_null; //count(*)和count(1)是一个意思
select sum(c2),max(c2),min(c2),avg(c2) from test_null;
//取平均值时null所在列忽略，所在行相当于不存在
若想取消此影响，建表时将参数指定默认值为0（c2 int default 0）

分组查询group by

使用group by字句对查询信息进行分组

语法格式

select <字段1><字段2>...from <表名> group by <字段分组> having <分组条件> ;
//分组字段可以为一个或多个，having可对分组结果

实例

统计各个分类商品的个数

select category_id,count(*) from product group by category_id;

统计来自同一个城市的学生信息

select  <省市名所在列名称>count(*) from student group by <省，市> 

having

分组之后的筛选

实例

统计各个分类商品的个数，但只显示个数大于4的信息

select 
	category_id, count(*) 
from 
	product 
group by 
	category_id having count(*)>4;

注意

• select 后面只能加分组字段和聚合函数

• having 语句用来从分组的结果中筛选行，分组之后统计结果只能用having，不能用where

分页查询limit

数据量较大时对数据采用分页显示

语法格式

方式1 显示前n条

select <字段1><字段2>...from <表名> limit n;//此处隐藏m=0，从第一条开始算

方式2 分页显示（从m到n）

select <字段1><字段2>...from <表名> limit m-1,n;//第一条数据为0

实例

查询produce的前五条数据

select * from product limit 5;

从product查询数据并从第四条开始显示，显示三条

select * from product limit 3,3;

数据导入语句

利用insert into 语句可以将一张表的数据导入到另一张表中

语法格式

方式1

insert into <表2>(<列1>,<列2>,...) select <列1>,<列2>...from<表1>//将表1的内容相应的插到表2中

方式2

insert into <表2> select * from <表1>

//以上的目标表 表2必须存在

实例

将product表中的pname和price内容复制到product2表中

create table product2(
pname varchar(20),
price double
);//创建product2表
insert into product2(pname,price) select pname,price from product;

查询product表中每种分类商品的计数，并复制到product3中

create table product3(
category_id varchar(20),
product_count int
);
insert into product3 select category_id,count(*)from product group by category_id;