MySQL数据库学习四——Linux中的MySQL基本操作2

超、凢脫俗 2021-12-12 09:51 294阅读 0赞

#### mysql字段类型 ####

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

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

##### 1 数字数据类型 #####

> 设置无符号:alter table student modify column age int unsigned

*  INT - 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295。 可以指定多达**11位**的宽度。 2\*\*32-1 ,4个字节
 *  TINYINT - 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，可以指定多达**4位**数的宽度。 2\*\*8 -1 ，1个字节
 *  SMALLINT - 一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，可以指定最多**5位**的宽度。2\*\*16-1，2个字节
 *  MEDIUMINT - 一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。 如果无符号，允许的范围是从0到16777215，可以指定最多**9位**的宽度。2\*\*24，3个字节
 *  BIGINT - 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 可以指定最多**20位**的宽度。2\*\*64，8个字节
 *  FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。其中D是小数的位数，M是数字(包括小数)的总数。小数精度可以到**24个**浮点。
    
    float(5,2) 整数位(5-2=3)只能<3，小数位>2。可以 显示五位222.33,222.339(四舍五入222.34)
    
     显示四位22.333,22.3339(四舍五入22.34)
 *  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的长度。 12345

#### 2.Mysql操作表 ####

表的创建命令需要：

*  表的名称
 *  字段名称
 *  定义每个字段(类型、长度等)

##### 2.1 创建表 #####

**语法:**

CREATE TABLE table_name (column_name column_type);

例如：

create table student(
        id int not null auto_increment primary key,
        sex bit,
        age int,
        name char(20),
        info varchar(100),
        birthday date
    );

注意：

1.  字段使用NOT NULL属性，是因为我们不希望这个字段的值为NULL。 因此，如果用户将尝试创建具有NULL值的记录，那么MySQL会产生错误。
2.  字段的AUTO\_INCREMENT属性告诉MySQL自动增加id字段下一个可用编号。
3.  关键字PRIMARY KEY用于定义此列作为主键。可以使用逗号分隔多个列来定义主键。

查看表结构：

>describe student;
    或者
    >desc student;

##### 2.3 插入数据 #####

**语法**

#单行插入
    INSERT INTO table_name ( field1, field2,...fieldN )
                           	VALUES
                           ( value1, value2,...valueN );
    #多行插入
    INSERT INTO table_name ( field1, field2,...fieldN )
                          	 VALUES
                           ( value11, value21,...valueN1 ),
                           ( value12, value22,...valueNN )...;

插入数据到数据库

insert into student(age,name,info,birthday) values(22,'hule','laiyifa','2018-7-5');

一次性插入多行数据

insert into student(age,name,info,birthday) 
    					values
    					(22,'wangjianling','laiyifa','2018-7-5'),
    					(33,'mayun','i have money','2011-11-11');

##### 2.4 修改表 #####

>alter table student modify name char(20) unique;
    
    #如果想在一个已经建好的表中添加一列，可以用以下代码：
    
    alter table 表名 add column 列名 varchar(20) not null;
    
    >alter table student add column info2 varchar(100);
    
    #改列名
    >alter table student change column name stuname varchar(20);
    #改表名
    alter table stu rename stu1
    
    #增加普通索引
    alter table test add index(t_name)
    
    #查看索引
    show index from user;

删除列名为’sex’的列

>alter table student drop column sex;

增加外键约束

>alter table stu add column class_id int;
    #添加外键
    >alter table 从表 add constraint 外键名称 foreign key 从表(外键) references 主表(主键);
    >alter table stu add foreign key stu(class_id) references grade(id);  #stu_ibfk_1
    #加约束名称
    >alter table stu add constraint stu_grade foreign key stu(class_id) references grade(id);
    #删除外键
    >alter table stu drop foreign key stu_ibfk_1

加表时加外键

>create table stu( 
        id int not null auto_increment primary key, 
        name char(20) unique, 
        age int, 
        class_id int, 
        foreign key(class_id) references grade(id) 
    );
    
    #查看外键约束
    show create table student;

#### 3、数据操作 ####

###### 3-1、基本语法 ######

> 格式：select \* from 表名;  
> 说明：  
>  a、from关键字后面是表名，表示数据来源于这张表  
>  b、select后面写表中的列名，如果是\*表示在结果集中显示表中的所有列  
>  c、在select后面的列名部分，可以使用as为烈面起别名，这个别名显示在结果集中  
>  d、如果要查询多个列，之间使用逗号分隔

实例：

select * from student;
     select name, age from student;
     select name as a, age from student;

#### 4.查 ####

##### 4-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 gender=0;

d、模糊查询

> like: %表示任意多个任意字符、 \_表示一个任意字符

例：查询姓王的同学

insert into student values(0,"王一二",65,1,"北京",0);
     insert into student values(0,"王一",66,1,"北京",0);
     
     select * from student where name 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 id between 6 and 8;

f、空判断

> 注意：null与" "是不同的  
> 判断空：is null  
> 判断非空： is not null

例：查询没有地址的同学

insert into student(name,age) values("特朗普",70);
    
    select * from student where address is null;

例：查询有地址的同学

select * from student where address is not null;

g、优先级

> 小括号，not 比较运算符，逻辑运算符  
> and比or优先级高，如果同时出现并希望先选or,需要结合()来使用

例：求名字以li结尾的或者年龄为18且地址为空的同学

select * from student where address is null and (name like "%li" or age = 18);

##### 4-2、聚合 #####

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

例：查询学生总数

select count(*) from student;

例：查询女生的编号最大值

select max(id) from student where gender=0;

例:查询女生的编号最小值

select min(id) from student where gender=0;

例:查询所有学生的年龄和

select sum(age) from student;

例:查询所有学生的年龄平均值

select avg(age) from student;

##### 4-3、分组 #####

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

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

例:查询男女生总数

select gender,count(*) from student group by gender;
    select name,gender,count(*) from student group by gender,age;
    select max(age),sex from stu group by sex;

分组后的数据筛选：

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

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

例：

select max(age),sex from stu group by sex having max(age)>26;

3、排序

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

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

例：将没有被删除的数据按年龄排序

select * from student where isDelete=0 order by age desc;
    select * from student where isDelete=0 order by age desc, id desc;

##### 4-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;

##### 5、关联 #####

建表语句：

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)
    );

#插入一些数据：
    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填充