mysql第二天笔记

mysql字段类型

正确地定义的表中的字段在数据库的整体优化是非常重要的。我们应该只使用真正需要使用类型和字段的大小; 如果知道只使用2个字符，就不使用10个字符宽定义一个字段。这些类型的字段(或列)，也被称为数据类型，数据存储这些字段之中。

MySQL使用许多不同的数据类型，总体上分为三类：数字，日期时间和字符串类型。

1 数字数据类型

     unsigned 无符号

int ** 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295。 可以指定多达11位的宽度。 232-1 ,4个字节
tinyint ** 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，可以指定多达4位数的宽度。 28 -1 ，1个字节
smallint ** 一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，可以指定最多5位的宽度。216-1，2个字节
mediumint ** 一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。 如果无符号，允许的范围是从0到16777215，可以指定最多9位的宽度。224，3个字节
** bigint ** 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 可以指定最多20位的宽度。264，8个字节
float(M,D) * 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。其中D是小数的位数，M是数字(包括小数)的总数。小数精度可以到24个浮点。
double(M,D) ** 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。 其中D是小数的位数，M是数字(包括小数)的总数。。小数精度可以达到53位**的double。real是double同义词。
decimal(M,D) 非压缩浮点数不能是无符号的。在解包小数，每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 numeric是decimal的同义词。

2 日期和时间类型

MySQL的日期和时间数据类型包括：

- DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。 例如，1973年12月30日将被存储为1973-12-30。‘yyyy/mm/dd’
- DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1973年12月30日下午3:30，会被存储为1973-12-30 15:30:00。
- TIMESTAMP - 1970年1月1日午夜之间的时间戳，到2037的某个时候。这看起来像前面的DATETIME格式，无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。
- TIME - 存储时间在HH:MM:SS格式。
- YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70〜69)。如果长度指定为4，年份范围是1901-2155，默认长度为4。

3字符串类型

虽然数字和日期类型比较有意思，但存储大多数数据都可能是字符串格式。 下面列出了在MySQL中常见的字符串数据类型。

- CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。 限定长度不是必需的，它会默认为1。
- VARCHAR(M) - 可变长度的字符串是以长度为1到21533之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。

• BLOB or TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。
  - TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。
  - MEDIUMBLOB or MEDIUMTEXT - BLOB或TEXT列具有16777215字符的最大长度。不指定MEDIUMBLOB或MEDIUMTEXT的长度。
  - LONGBLOB 或 LONGTEXT - BLOB或TEXT列具有4294967295字符的最大长度。不指定LONGBLOB或LONGTEXT的长度。

修改表

#如果想在一个已经建好的表中添加一列，可以用以下代码：

alter table 表名 add column 列名 varchar(20) not null;
alter table student add column info varchar(100);

#修改列名

alter table student change column name stuname varchar(20);

#修改表名

alter table 表名 rename 新的表名;
alter table student rename stu;

#删除表

drop table 表名; //表的数据和结构全部删除
truncate table 表名; //删除数据，保留表的结构

#增加索引

1.添加主键索引
alter table 表名 add primary key (column);
2.添加唯一索引
alter table 表名 add unique(column);
3.添加普通索引
alter table 表名 add index index_name(column);
4.查看索引
show index from 表名;

#增加外键约束

学生表先创建一列class_id
alter table stu add column class_id int;
添加外键
alter table 从表 add constraint 外键名称 foreign key 从表(外键字段) references 主表(主表字段)；
alter table stu add foregin key stu(class_id) references grade(id);
加约束名称
alter table 表名 add constraint

#外键约束:例如有一个学生表和一个班级表
mysql>create table class(
id int auto_increment primary key,
name varchar(20) not null,
stuNum int not null);

mysql>create table students(
id int auto_increment primary key,
name varchar(20) not null,
gender bit default 1,
classid int not null,
foreign key(classid) references class(id)
);
这样学生表中的classid这一列中的值必须是class这个表中有的id，比如class表有1,2,3这3个id，那么学生表的classid只能填1,2,3这3个值

查

#####1.条件查询

a.语法
select * from 表名 where 条件

b.比较运算符
等于 =
​ 大于 >
​ 小于 <
​ 大于等于 >=
​ 小于等于 <=
​ 不等于 !=或<>
需求：查询id值大于8的所有数据
select * from student where id>8;

c、逻辑运算符
​ and 并且
​ or 或者
​ not 非
需求：查询id值大于7的女同学
select * from student where id>7 and sex=“女”;

d、模糊查询
like
​ %表示任意多个任意字符
​ _表示一个任意字符
需求：查询姓刘的同学
select * from student where name like “刘%”;

e、范围查询
in 表示在一个非连续的范围内
between…and… 表示在一个连续的范围内
需求：查询编号为8、10、12的学生
select * from student where id in (8,10,12);
需求：查询编号为6到8的学生
select * from student where between 6 and 8;

f、空判断
注意：null与""是不同
判断空：is null
判断非空： is not null
需求：查询没有地址的同学
select * from student where adress is null;

g、优先级
​ 小括号，not 比较运算符，逻辑运算符
​ and比or优先级高，如果同时出现并希望先选or,需要结合()来使用
需求：求名字以li结尾的或者年龄为18且地址为空的同学
select * from student where address is null and (name like “%li” or age = 18);

2、聚合

为了快速等到统计数据，提供了5个聚合函数
​ a、count() 表示计算总行数，括号中可以写和列名
​ b、max(列) 表示求此列的最大值
​ c、min(列) 表示求此列的最小值
​ d、sum(列) 表示求此列的和
​ e、avg(列) 表示求此列的平均值

需求：查询学生总数
select count(*) from student;

需求：查询女生的编号最大值最小值
select max(id) from student where sex=“女”;
select min(id) from student where sex=“女”;

查询所有学生的年龄和
select sum(age) from student;

查询所有学生的年龄平均值
select avg(age) from student;

3、分组

​ 按照字段分组，表示此字段相同的数据会被放到一个集合中.分组后，只能查询出相同的数据列，对于有差异的数据列无法显示在结果集中,可以对分组后的数据进行统计，做聚合运算.

#语法

select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,……;

需求：查询男女生总数
select gender,count(*) from student group by gender;

分组后的数据筛选：
where与having的区别：
1、where是对from后面指定的表进行筛选，属于对原始数据的筛选
2、having是对group by的结果进行筛选
#语法

select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,…… having 列1,……聚合……;

#####4、排序

#语法：
select * from 表名 order by 列1 asc|desc，列2 asc|desc , ……;

​ 说明：
​ a、将数据按照列1进行排序，如果某些列1的值相同，则按照列2进行排序
​ b、默认按照从小到大的顺序排序
​ c、asc升序
​ d、desc降序

例如将所有人按年龄大小 升序排列
select * from student order by age asc;
将性别为男的的学生 按年龄大小升序排列
select * from student where sex=“男” order by age asc;

4、分页

#语法：

select * from 表名 limit start,count;

page = 2
page_num =3

start = (page - 1) * page_num
count = page_num

select * from student limit start,count
说明：start索引从0开始

例如：
select * from student limit 0,3;
select * from student limit 3,3;
select * from student where gender=1 limit 0,3;
limit 0,3 :值从第0行开始，取3条数据，第0行不算
limit 3,3:从第3行开始，取3条数据，第3行的数据不取，也就是取后面4,5,6的数据

5、关联

建表语句：

create table class(
id int auto_increment primary key,
name varchar(20) not null,
stuNum int not null);

create table students(
id int auto_increment primary key,
name varchar(20) not null,
gender bit default 1,
classid int not null,
foreign key(classid) references class(id)
);

#插入一些数据：

insert into class values(0, “python01”, 55),(0, “python02”, 50),(0, “python03”, 60),(0, “python04”, 80);

insert into students values(0, “tom”, 1, 1);
insert into students values(0, “lilei”, 1, 10);
insert into students values(0, “jack”, 1, 2);

select * from students;
关联查询：
select students.name,class.name from class inner join students on class.id=students.classid;

select students.name,class.name from class left join students on class.id=students.classid;

select students.name,class.name from students right join class on class.id=students.classid;

分类：
1、表A inner join 表B：
表A与表B匹配的行会出现在结果集中
2、表A left join 表B：
表A与表B匹配的行会出现在结果集中，外加表A中独有的数据，未对应的数据使用null填充
3、表A right join 表B：
表A与表B匹配的行会出现在结果集中，外加表B中独有的数据，未对应的数据使用null填充