msql 数据库基本操作命令和事务
1. DDL-数据库定义语言
1.1. 概念及作用:
database defination language 数据库定义语言
用于定义数据库,用于定义表结构
1.2. 表中字段基本数据类型
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型
1、一个汉字占多少长度与编码有关:
UTF-8:一个汉字=3个字节
GBK:一个汉字=2个字节
2、varchar(n) 表示 n 个字符,无论汉字和英文,Mysql 都能存入 n 个字符,仅是实际字节长度有所区别
1、整型
类型 |
大小 |
范围(有符号) |
范围(无符号) |
用途 |
TINYINT |
1 字节 |
(-128,127) |
(0,255) |
小整数值 |
SMALLINT |
2 字节 |
(-32 768,32 767) |
(0,65 535) |
大整数值 |
MEDIUMINT |
3 字节 |
(-8 388 608,8 388 607) |
(0,16 777 215) |
大整数值 |
INT或INTEGER |
4 字节 |
(-2 147 483 648,2 147 483 647) |
(0,4 294 967 295) |
大整数值 |
BIGINT |
8 字节 |
(-9 233 372 036 854 775 808,9 223 372 036 854 775 807) |
(0,18 446 744 073 709 551 615) |
极大整数值 |
FLOAT |
4 字节 |
(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) |
0,(1.175 494 351 E-38,3.402 823 466 E+38) |
单精度 |
DOUBLE |
8 字节 |
(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) |
0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) |
双精度 |
DECIMAL |
对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 |
依赖于M和D的值 |
依赖于M和D的值 |
小数值 |
取值范围如果加了 unsigned,则最大值翻倍,如 tinyint unsigned 的取值范围为(0~256)。
int(m) 里的 m 是表示 SELECT 查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个 m 有什么用。
2、浮点型(float 和 double)
MySQL数据类型 |
含义 |
float(m,d) |
单精度浮点型 8位精度(4字节) m总个数,d小数位 |
double(m,d) |
双精度浮点型 16位精度(8字节) m总个数,d小数位 |
设一个字段定义为 float(5,3),如果插入一个数 123.45678,实际数据库里存的是 123.457,但总个数还以实际为准,即 6 位。
3、定点数
浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数 m<65 是总个数,d<30 且 d<m 是小数位。
4、字符串(char,varchar,_text)
MySQL数据类型 |
含义 |
char(n) |
固定长度,最多255个字符 |
varchar(n) |
固定长度,最多65535个字符 |
tinytext |
可变长度,最多255个字符 |
text |
可变长度,最多65535个字符 |
mediumtext |
可变长度,最多2的24次方-1个字符 |
longtext |
可变长度,最多2的32次方-1个字符 |
char 和 varchar:
- 1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格,varchar 不限于此。
- 2.char(n) 固定长度,char(4) 不管是存入几个字符,都将占用 4 个字节,varchar 是存入的实际字符数 +1 个字节(n<=255)或2个字节(n>255),所以 varchar(4),存入 3 个字符将占用 4 个字节。
- 3.char 类型的字符串检索速度要比 varchar 类型的快。
varchar 和 text:
- 1.varchar 可指定 n,text 不能指定,内部存储 varchar 是存入的实际字符数 +1 个字节(n<=255)或 2 个字节(n>255),text 是实际字符数 +2 个字节。
- 2.text 类型不能有默认值。
- 3.varchar 可直接创建索引,text 创建索引要指定前多少个字符。varchar 查询速度快于 text, 在都创建索引的情况下,text 的索引似乎不起作用。
5.二进制数据(_Blob)
- 1._BLOB和_text存储方式不同,_TEXT以文本方式存储,英文存储区分大小写,而_Blob是以二进制方式存储,不分大小写。
- 2._BLOB存储的数据只能整体读出。
- 3._TEXT可以指定字符集,_BLO不用指定字符集。
6.日期时间类型
MySQL数据类型 |
含义 |
date |
日期 '2008-12-2' |
time |
时间 '12:25:36' |
datetime |
日期时间 '2008-12-2 22:06:44' |
timestamp |
自动存储记录修改时间 |
若定义一个字段为timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。
数据类型的属性
MySQL关键字 |
含义 |
NULL |
数据列可包含NULL值 |
NOT NULL |
数据列不允许包含NULL值 |
DEFAULT |
默认值 |
PRIMARY KEY |
主键 |
AUTO_INCREMENT |
自动递增,适用于整数类型 |
UNSIGNED |
无符号 |
CHARACTER SET name |
指定一个字符集 |
1.3. 数据库语句操作
我们可以在登陆 MySQL 服务后,使用 create 命令创建数据库,语法如下:
CREATE DATABASE 数据库名;
创建第一个数据库 mydb1
create database mydb1
在删除数据库过程中,务必要十分谨慎,因为在执行删除命令后,所有数据将会消失
drop 命令删除数据库
删除创建的数据库
drop database mydb1;
在你连接到 MySQL 数据库后,可能有多个可以操作的数据库,所以你需要选择你要操作的数据库,
选择数据库
user mydb1
注意:所有的数据库名,表名,表字段都是区分大小写的。所以你在使用SQL命令时需要输入正确的名称。
查看数据库创建细节
show create database mydb1
创建一个使用gbk字符集的数据库
create database mydb2 character set gbk
1.4. 表结构语句操作
选择数据库
user mydb1
创建MySQL数据表需要以下信息:
表名
表字段名
定义每个表字段
创建表
create table student(id int,name varchar(20),sex varchar(20),age int,salery float(6,2),birthday date)
删除表
drop table student;
查看所有表
show tables
查看表的创建细节
show create table student;
展示表结构
desc student
在原有的学生基础上添加address列
alter table student add address varchar(20)
在原有的学生基础上删除address列
alter table student drop address
1.5. 定义表的约束
create table student(id int primary key auto_increment,name varchar(20) unique not null,sex varchar(20),age int,salery float(6,2),birthday date)
l 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
l AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
l PRIMARY KEY关键字用于定义列为主键。为了标识数据库记录唯一性,不允许记录重复,且键值不能为空,主键也是一个特殊索引。 您可以使用多列来定义主键,列间以逗号分隔。
l UNIQUE KEY的用途:主要是用来防止数据插入的时候重复的
l ENGINE 设置存储引擎,CHARSET 设置编码
2. DML-数据库操作语言
2.1. 概念及作用
DML data manipulation language 数据库操作语言,用以操作数据库
2.2. 插入数据
insert into student values(1,’zhangsan’,’nan’,19,389.10,’1999-10-10’);
查询
select * from student
insert into student values(2,’李四’,’男’,19,389.10,’1999-10-10’);
插入中文会报错
通知服务器客户端使用的编码是gbk
set character_set_client=gbk;
通知服务器客户端查看结果集使用的编码是 gbk
set character_set_results=gbk;
insert into student(id,name,sex,age) values(3,’王五’,’男’,19);
2.3. 删除数据
删除单条数据
delete from student where id=1;
删除所有数据
delete from student;
摧毁表
truncate table student;
2.4. 修改数据
设置所有人的年龄加10岁
update student set age=age+10
修改zhangsan 为张三
update student set name=’张三’ where name=’zhangsan’
修改王五的salery和出生日期
update student set salery=100.01,birthday=’1999-10-10’ where id=3;
3. DQL-数据库查询语言
3.1. 概念及作用
Data Query Language 数据库查询语言
3.2. 数据查询
删除student
drop table student
创建数据库表-学生成绩表
create table student(id int primary key auto_increment,name varchar(20) unique not null,chinese float,english float,math float);
添加几条数据
insert into student values(1,’张三’,90,80,80);
insert into student values(2,’李四’,90,87,60);
insert into student values(3,’王五’,70,60,69);
insert into student values(4,’赵六’,99,90,87);
3.2.1. 普通条件查询
查询所有学生信息
select * from student;
查询id为1的学生信息
select * from student where id=1;
查询id为1的学生姓名
select name from student where id=1;
查询数学成绩大于80的同学成绩
select * from student where math>80
查询所有学生成绩,并输出效果为 姓名 语文 英语 数学 效果
select name as 姓名,chinese as 语文,english as 英语,math as 数学 from student
查询所有成绩及数学分+10分
select *,(math+10)from student
统计每个学生的总分
select name,(math+english+chinese) as 总分 from student
查询总分大于230分的同学
select * from student where (math+english+chinese)>230
查询数学成绩在80-90之间的同学
select * from student where math between 80 and 90
查询数学语文英语都大于80的同学成绩
select * from student where math>80 and english>80 and chinese >80;
查询数学成绩在 80 60 90内的同学
select * from student where math in(80,60,90);
3.2.2. 模糊查询
查询所有姓名中包含张的同学
select * from student where name like ‘%张%’
3.2.3. 排序查询
按照数学成绩从小到大查询
select * from student order by math;
按照数学成绩从大到小查询
select * from student order by math desc;
3.2.4. 分页查询
limit是mysql的语法
select * from table limit m,n
其中m是指记录从m+1开始
,N代表取n条记录。
select * from student limit 2,4
即取出第3条至第6条,4条记录
查询出数学成绩由高到低前两名
select * from student order by math desc limit 0,2;
3.2.5. 分组查询
创建一个订单表
create table employee(id int,name varchar(20),sex varchar(20),age int);
insert into employee values(1,'sunsan','男',18);
insert into employee values(2,'lisi','男',18);
insert into employee values(3,'wangwu','女',19);
insert into employee values(4,'zhaoliu','男',15);
分组查询
select * from employee group by sex;
分组查询加条件
select * from employee group by sex having age>18;
(1) having 条件表达式:用来分组查询后指定一些条件来输出查询结果
(2) having作用和where一样,但having只能用于group by
3.3. 报表查询
count 个数
sum 总数
avg 平均数
max 最大值
min 最小值
统计班级里边有多少学生
select count(*)from student;
统计总成绩大于250分的人数
select count(*)from student where (math+english+chinese)>250;
统计班级里边各科总成绩
select sum(math),sum(english),sum(chinese) from student
统计所有科目的总成绩
select sum(math+english+chinese) from student;
统计一下语文平均成绩
select sum(chinese)/count(*) from student;
select avg(chinese) from student;
统计一下班级语文最高分和最低分
select max(chinese) from student;
select min(chinese) from student;
报表查询订单根据名称合并后,总价格>10000的商品
select * from orders group by product having sum(price) >7000
交叉查询
交叉查询,又叫笛卡尔积查询,会将左表和右表的信息,做一个乘积将所有信息查询出来,会产生临时表,比较占用内存,生成的记录数=表1 X表2
select * from customer,orders;
select * from customer cross join orders;
内连接查询
内连接,inner join on 查询两张表,设定条件,将两张表中对应的数据查询出来
不会产生笛卡尔积,不会产生临时表,性能高
select * from customer c inner join orders o on c.id=o.customer_id;
select * from customer,orders where customer.id=orders.customer_id;
select * from customer c,orders o where c.id=o.customer_id;
左外连接
左外连接 left join on 设定条件,将两张表对应的数据查询出来,同时将左表自己没有关联的数据也查询出来
注意:join前面是左,后面是右
select * from customer c left join orders o on c.id=o.customer_id;
右外连接
右外连接 right join on 设定条件,将两张表对应的数据查询出来,同时将右表自己没有关联的所有数据查询出来
select * from customer c right join orders o on c.id=o.customer_id;
联合查询
select * from customer left join orders on customer.id=orders.customer_id
having price>20;
事务四大特征(ACID)
- 原子性(A):事务是最小单位,不可再分
- 一致性©:事务要求所有的DML语句操作的时候,必须保证同时成功或者同时失败
- 隔离性(I):事务A和事务B之间具有隔离性
- 持久性(D):是事务的保证,事务终结的标志(内存的数据持久到硬盘文件中)
关于事务的一些术语
- 开启事务:Start Transaction
- 事务结束:End Transaction
- 提交事务:Commit Transaction
- 回滚事务:Rollback Transaction
和事务相关的两条重要的SQL语句(TCL)
- commit:提交
- rollback:回滚