表中字段基本数据类型
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型
1、一个汉字占多少长度与编码有关:
UTF-8:一个汉字=3个字节
GBK:一个汉字=2个字节
- varchar(n) 表示 n 个字符,无论汉字和英文,Mysql 都能存入 n 个字符,仅是实际字节长度有所区别
- 整型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
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) | 双精度 |
取值范围如果加了 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个字符 |
text | 可变长度,最多65535个字符 |
char 和 varchar:
CHAR数据类型与VARCHAR数据类型不同,其采用的是固定长度的存储方式。简单的说,就是系统总为其分配最大的存储空间。当数据保存时,即使 其没有达到最大的长度,系统也会为其分配这么多的存储空间。显然,这种存储方式会造成磁盘空间的浪费。
VARCHAR与CHAR两种字符型数据类型相比,最大的差异就是前者是可变长度,而后者则是固定长度。在存储时,前者会根据实际存储的数据 来分配最终的存储空间。而后者则不管实际存储数据的长度,都是根据CHAR规定的长度来分配存储空间
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 | 指定一个字符集 |
数据库语句操作
我们可以在登陆 MySQL 服务后,使用 create 命令创建数据库,语法如下:
CREATE DATABASE 数据库名;
创建第一个数据库 mydb1
create database mydb1
在删除数据库过程中,务必要十分谨慎,因为在执行删除命令后,所有数据将会消失
drop 命令删除数据库
删除创建的数据库
drop database mydb1;
在你连接到 MySQL 数据库后,可能有多个可以操作的数据库,所以你需要选择你要操作的数据库,
选择数据库
user mydb1
注意:所有的数据库名,表名,表字段都是区分大小写的。所以你在使用SQL命令时需要输入正确的名称。
查看数据库创建细节
show create database mydb1
创建一个使用utf8字符集的数据库
create database mydb2 character set utf8
表结构语句操作
选择数据库
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
定义表的约束
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)
- 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
- AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
- PRIMARY KEY关键字用于定义列为主键。为了标识数据库记录唯一性,不允许记录重复,且键值不能为空,主键也是一个特殊索引。 您可以使用多列来定义主键,列间以逗号分隔。
- UNIQUE KEY的用途:主要是用来防止数据插入的时候重复的
- ENGINE 设置存储引擎,CHARSET 设置编码
DML-数据库操作语言概念及作用
DML data manipulation language 数据库操作语言,用以操作数据库
插入数据
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);
删除数据
删除单条数据
delete from student where id=1;
删除所有数据
delete from student;
摧毁表
truncate table student;
修改数据
设置所有人的年龄加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
DQL-数据库查询语言概念及作用
Data Query Language 数据库查询语言
数据查询
删除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);
普通条件查询
查询所有学生信息
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);
模糊查询
查询所有姓名中包含张的同学
select * from student where name like ‘%张%’
排序查询
按照数学成绩从小到大查询
select * from student order by math;
按照数学成绩从大到小查询
select * from student order by math desc;
分页查询
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;
分组查询
创建一个订单表
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
报表查询
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
多表设计一对一
一张表的一条记录一定只能与另外一张表的一条记录进行对应,反之亦然。
有时候,为了业务,或者避免一张表中数据量过大,过复杂,在开发中会进行一对一方式来设计表。
7.2一对多(1方建主表(id为主键字段), 多方建外键字段)
指的是一个实体的某个数据与另外一个实体的多个数据有关联关系, 一对多的关系在设计的时候,需要设计表的外键
班级表和学生表设计
部门表和员工表设计
创建数据库表
constraint 约束
foreign key就是表与表之间的某种约定的关系,由于这种关系的存在,能够让表与表之间的数据,更加的完整,关连性更强。
foreign key语句的式例:FOREIGN KEY(Sno) REFERENCES Student(Sno)
附:表的外键必须是另一张表的主键
create table class(id int primary key auto_increment,name varchar(20));
create table student(id int primary key auto_increment,name varchar(20),sex varchar(20),class_id int,constraint foreign key(class_id) references class(id));
insert into class values(1,'ceshiban');
insert into class values(2,'kaifa');
insert into student values(1,'zhangsan','nan',1);
insert into student values(2,'lisi','nan',2);
insert into student values(3,'jingjing','nan',2);
select * from student where class_id=(select id from class where id=2);
补一个外键的概念(默认是约束): 删除主键信息时,当该主键字段值在外键表中存在时,该记录是不能删除的。---要把外键表是的相关信息删除之后,才能删除。
7.3、多对多( 3个表= 2个实体表 + 1个关系表 )
一个实体的数据对应另外一个实体的多个数据,另外实体的数据也同样对应当前实体的多个数据。
一个学生可以有多个老师,一个老师可以教多个学生
解决方案:创建一个中间表,专门用来维护多表之间的对应关系,通常是能够唯一标识出数据的字段(主键)
create table teacher(id int primary key,name varchar(100));
create table student (id int primary key,name varchar(100));
create table teacher_student(teacher_id int,student_id int,constraint foreign key(teacher_id) references teacher(id),constraint foreign key(student_id) references student(id));
insert into teacher values(1,'梁老师');
insert into teacher values(2,'李老师');
insert into student values(1,”张三”);
insert into student values(2,”李四”);
insert into teacher_student values(1,1);
insert into teacher_student values(1,2);
insert into teacher_student values(2,1);
insert into teacher_student values(2,2);
查询李老师所教的学生
select id from teacher where name=’李老师’
select student_id from teacher_student where teacher_id=id
select * from student where id in(select student_id from teacher_student where teacher_id =(select id from teacher where name='李老师'));
查询张三的所有老师
select * from teacher where id in(select teacher_id from teacher_student where student_id=(select id from student where name='张三'));
连接查询初始定义表结构
create table customer(id int primary key auto_increment,name varchar(20),city varchar(20));
create table orders(id int primary key auto_increment,good_name varchar(20),price float(8,2),customer_id int);
insert into customer (name,city) values('李老师','东北');
insert into customer (name,city) values('崔老师','山西');
insert into customer (name,city) values('张老师','内蒙');
insert into customer (name,city) values('闫老师','天津');
insert into orders(good_name,price,customer_id) values('电脑',59,1);
insert into orders(good_name,price,customer_id) values('笔记本',88,2);
insert into orders(good_name,price,customer_id) values('吹风机',99,1);
insert into orders(good_name,price,customer_id) values('香水',300,3);
insert into orders(good_name,price,customer_id) values('牛奶',100,6);
交叉查询
交叉查询,又叫笛卡尔积查询,会将左表和右表的信息,做一个乘积将所有信息查询出来,会产生临时表,比较占用内存,生成的记录数=表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;