MySQL基础(二)

Union

select ename from emp;
union
select dname from dept;

Limit

MySQL特有的，分页查询就靠它了

limit是sql语句执行的最后一个环节

例：找出工资排名在第四到第九的员工？

select ename,sal from emp order by sal desc limit 3,6;

⚠️  第一个数字是起始位置，第二数字表示取几个数

Java示例

int pageNo = 2;
int pageSize = 10;
limit (page - 1) * pagesize, pagesize

创建表

create table 表名{

        字段名1,数据类型;

        字段名2,数据类型;

        字段名3,数据类型;

}

数据类型：

int整数型 对应Java的int

bigint长整型 对应Java的long

float浮点型 对应Java非float, double

char定长字符串 对应Java的String

varchar可变长字符串 对应Java的StringBuffer和StringBuilder

date日期类型 对应Java的java.sql.Date

实际开发中，某个字段的数据长度不发生改变时，是定长的，如性别，生日都是采用char，当字段长度不确定时，例如简介、姓名等都是采用varchar

例 创建学生表

create table student{

        no bigint,

        name varchar(255),

        sex char(1),

        classno varchar(255),

        birth char(10) 

}

Insert插入数据

insert into 表名(字段名1, 字段名2, 字段名3, ...) values(值1, 值2, 值3,...)

insert into t_student(name, sex, classno, birth, no) values('lisi', '1', 'gaosanyiban', 1997-07-01, 2)

一旦插入后就没办用Insert再次补充了，只能用update进行更新

⚠️  可以不写t_student后面的字段名，但是values后面的数据要严格按照字段进行排列

一次插入多行数据

insert into t_student(name, sex, classno, birth, no) values('lisi', '1', 'gaosanyiban', 1997-07-01, 2), ('zhangsan', '1', 'gaosanerban', 1997-06-01, 2);

将查询结果插入一张表中 

insert into dept1 select * from dept;

Drop删表

Drop table if exists t_table 

表的复制

create table emp2 as select * from emp1;

update修改数据

update 表名 set 字段名1 = 值1 ,字段名2 = 值2.... where 条件;

update dept1 set loc = 'SHANGHAI',dname = 'RENSHIBU' where deptno = 10;

Delete删除数据

delete from 表名 where 条件;

delete from dept where deptno = 10;
delete from dept;

Truncate删除数据

truncate table 表名

 

⚠️ 总结Drop，Truncate，和Delete

Delete是数据操作语言DML，Delete通过where支持条件删除，并且支持事物回滚

Truncate是数据定义语言DDL，Truncate是删除表中所有内容，不支持回滚，永久丢失，对于外键约束引用的表，不能使用 truncate table

Drop是对整个表的结构和数据进行删除

• 在速度上，一般来说，truncate > delete。
• 如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；如果和事务有关，或者想触发trigger，还是用delete；

• 随着不断地进行表记录的DML操作，会不断提高表的高水位线（HWM），DELETE操作之后虽然表的数据删除了，但是并没有降低表的高水位，随着DML操作数据库容量也只会上升，不会下降。所以如果使用DELETE，就算将表中的数据减少了很多，在查询时还是很和DELETE操作前速度一样。而TRUNCATE操作会重置高水位线，数据库容量也会被重置，之后再进行DML操作速度也会有提升。

 

约束

在创建表的时候，可以给表的字段添加相应的约束，目的是为了保证表中数据的合法性，有效性，完整性

常见约束有：

• 非空约束 not null只有列级约束，没有表级约束
• 唯一约束 unique 修饰的字段具有唯一性，但是可以为Null，可以多字段联合约束，表级约束
• 主键约束 primary key 字段数据不能为Null，也不能重复，主键值是这行记录在表中的唯一标识，主键值最好是个和业务无关的自然数。
• 外键约束 foreign key 外键字段引用表的字段一般是主键，但不是一定为主键，但是至少为unique约束

表级约束方式定义主键 

drop table if exists student;
create table student{
        id int,
        name varchar(255),
        primary key(id)
}

主键自增

drop table if exists student;
create table student{
        id int primary key auto_increment,
        name varchar(255),
}

Student中的classNo字段引用t_class表中的cno字段，此时Student表叫做子表，t_class表叫做父表

顺序要求：

• 删除数据时先删除子表后删除父表
• 添加数据的时候先添加父表再添加子表
• 创建表的时候先创建父表再创建子表
• 删除表的时候先删除子表后父表

drop table if exists Student;
drop table if exists Class;

create table Class{
        cno int,
        name varchar(255),
        primary key(cno)
}
create table Student{
        sno int,
        name varchar(255),
        classno int,
        primary key(sno),
        foreign key(classno) references Class(cno)
}

insert into Class values(101,'xxxxx');
insert into Class values(102,'xxxxy');

insert into Student values(1,'zz',101);
insert into Student values(2,'zy',102);

 

存储引擎

MySQL默认使用的存储引擎是InnoDB方式，默认采用的字符集是UTF8

MyISAM 是MySQL最常用的存储引擎，但是不支持事务，且不是默认

InnoDB 支持事务，行级锁，外键，这样安全得到了保障，MySQL数据库奔溃后提供自动恢复机制，支持级联删除和级廉更新

Memory 不支持事务，数据容易丢失，数据和索引都在内存中，断电就消失，但是查询速度最快