mysql数据库开发重点知识总结

一、存储引擎

1.什么是存储引擎?

库就是文件夹,表就是文件,文件都会有文件的格式,存储引擎就是文件格式.

存储引擎就是文件的类型（格式），文本文件有TXT类型、图片有JPG类型、视频有MP4格式.

mysql中最常用的是InnoDB存储引擎(格式)

2.使用存储引擎

# 创建表是指定存储引擎
create table t1(
    id int,
    name char
)engine=innodb;

# 查看表达存储引擎
show create table t1;

二、数据类型

1.数字

1.1整型：tinyinit, int, bigint

存储年龄,等级,ID,各种号码等

1.2小数(浮点型):float, double, decimal

存储薪资,身高,体重,体质参数等

2.字符串

2.1char(10)

简单粗暴,浪费空间,存取速度快

2.2varchare

精准,节省空间,存取速度慢

2.3sql对字符串的优化

创建表时,定长(比如性别)的类型往前放,变长(比如地址)的往后放

超过255个字符,就把文件路径存放到数据库中,比如图片,视频等找一个文件服务器,数据库中只存路径或URL.

3.时间类型:datetime

date, time, datetime, temestamp, year

now()

4.枚举类型与集合类型

enum单选

set多选

三、库

# 1. 新增库
create database db10 charset utf8;

# 2. 删除库
drop database db10;

# 3. 修改数据库
alter database db10 charset utf8;

# 4. 查看数据库
show databases;
show create database db10;
select database();  # 查看当前已经选择的数据库

# 5. 选择数据库
use db10;

 

四、表

1.增删改查

# 1. 创建表
create table t1(
    id int,
    name varchar(50),
    sex enum('male', 'female'),
    age int(3)
);

# 2. 删除表
drop table t1;

# 3. 修改表
# 3.1 修改存储引擎
alter table t1
    engine = innodb;
# 3.2 添加字段
alter table t1
    add name varchar(20) not null,
    add age int(3) not null default 22;
alter table student10
    add stu_num varchar(10) not null after name;  //添加name字段之后
alter table student10
    add sex enum('male', 'female') default 'male' first;  //添加到最前面
# 3.3 删除字段
alter table student10
    drop sex;
# 3.4 修改字段类型
alter table student10
    modify age int(3);
alter table student10
    modify id int(11) not null primary key auto_increment;  //修改为主键
# 3.5 增加约束(针对已有的主键增加auto_increment)
alter table student10 
    modify id int(11) not null primary key auto_increment;
alter table student10
    modify id int(11) not null auto_increment;
# 3.6 对已经存在的表增加复合主键
alter table service2
    add primary key (host_ip, port);
# 3.7 增加主键
alter table student1
    modify name varchar(10) not null primary key ;
# 3.8 增加主键和自动增长
alter table student1
    modify id int not null primary key auto_increment;
# 3.9 删除主键
alter table student10 
    modify id int(10) not null ;  //删除自增约束
alter table student10
    drop primary key ;  //删除主键

# 4. 查看表结构
describe t1;
desc t1;  //简写形式
show create table t1\G;  //查看表详细结构,可加\G

# 5. 复制表
create table new_service 
    select * from service;  //复制表结构+记录(key不会复制:主键,外键,和索引)
create table new_service
    select * from service where 1=2;  //只复制表结构,因为条件为假,查不到任何记录.

2.完整性约束条件

约束性条件和数据类型的宽度一样,都是可选参数

作用:用于保证数据的完整性和一致性

主要分为:

　　primary key (pk)  标识该字段为该表的主键,可以唯一的标识记录

　　foreign key (fk) 标识该字段为该表的外键

　　not null  标识该字段不能为空

　　unique key (uk)  标识该字段的值是唯一的

　　auto_increment  标识该字段的值自动增长(数据类型,而且为主键)

　　default  为该字段设置默认值

 

　　unsigned 无符号

　　zerofill  使用0填充

说明:

　　1.是否允许为空,默认null,可设置为not null,字段不允许为空,必须赋值

　　2.字段是否有默认值,缺省的默认值是null,如果插入记录时不给字段赋值,此字段使用默认值

　　　　sex enmu('male', 'female') not null default 'male'

　　　　age int usigned not null default 20 必须为正值(无符号),不允许为空,默认20

　　3.是否是KEY

　　　　主键 primary key

　　　　外键 foreign key

　　　　索引 (index, unique...)

3.完整性约束条件操作实例

3.1unique设置唯一约束

# 方法一:
create table t2(
    id int,
    name varchar(20) unique,
    comment varchar(100)
);
# 方法二:
create table t3(
    id int,
    name varchar(20),
    comment varchar(100),
    constraint uk_name unique (name)
);

# 联合唯一
create table service(
    id int primary key auto_increment,
    name varchar(20),
    host varchar(15) not null,
    port int not null ,
    unique (host, port)
);

3.2primary key字段的值不为空且唯一

一个表中可以:

　　单列做主键,not null + unique 或 primary key

　　多列做主键(符合主键),

但一个表内只能有一个主键primary key

# 多列主键
create table t4(
    ip varchar(15),
    port char(5),
    service_name varchar(10) not null,
    primary key (ip, port)
);

3.3auto_increment自增长字段

create table student(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male', 'female') default 'male'
);

3.4foreign key关联其它表

先建父表,再建关联子表,删除父表记录,子表中对应的记录跟着删.

表之间的关系:一对一,多对一,多对多

 

# 先建立主表department部门表,然后让employee员工表关联该表.
# 表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一.
create table department(
    id int primary key ,
    name varchar(20) not null 
)engine = innodb;

# 建立多对一的关联表
# dept_id外键,关联父表(department主键id),同步更新,同步删除
create table employee(
    id int primary key,
    name varchar(20) not null,
    dpt_id int,
    constraint fk_name foreign key (dpt_id)
                     references department(id)
        on delete cascade 
        on update cascade 
)engine = innodb;

# 多对多关系,需要新建第三张表来描述两个表之间的关系.
create table author2book(
    id int not null unique auto_increment,
    author_id not null ,
    book_id int not null,
    foreign key (author_id) references author(id)  # 作者ID与作者表的id字段对应
                        on delete cascade 
                        on update cascade,
    foreign key (book_id) references book(id)  # 书籍ID与图书表的id字段对应
                        on delete cascade 
                        on update cascade ,
    primary key (author_id, book_id)
);

 

 

五、记录

在mysql管理软件中,可通过SQL语句中的DML语言来实现数据的操作,包括:

　　1.使用insert实现数据的插入

　　2.update实现数据的更新

　　3.使用delete实现数据的删除

　　4.使用select查询数据

1.增

# 1.插入完整书籍(顺序插入)
# 语法一:
insert into 表名(字段1,字段2,字段3,...字段n) values 
    (值1,值2,值3,...值n);
# 语法二:
insert into 表名 values 
    (值1,值2,值3,...值n);  # 值一定要按照表的字段顺序一个不漏

# 2.指定字段插入数据
insert into 表名(字段1,字段2,字段3,...) values 
    (值1,值2,值3,...);

# 3.插入多条记录
insert into 表名 values 
    (值1,值2,值3...值n),
    (值1,值2,值3...值n),
    (值1,值2,值3...值n);

# 4.插入查询结果
insert into 表名(字段1,字段2,字段3...字段n)
    select (字段1,字段2,字段3...字段n) from 表2
    where ...;

 

2.删

# 语法
delete from 表名
    where conition;
# 实例
delete from mysql.user
    where password='';

 

3.改

# 语法
update 表名 set
    字段1=值1,
    字段2=值2
    where condition;
# 实例
update from mysql.user set
    password=password('123')  # password('123')意思是采用密文方式存储
    where user='root' and host='localhost';

 

4.查

# 单表查询
select distinct 字段1,字段2,字段3 from 库.表
    where 条件
    group by 分组条件
    having 过滤
    order by 排序字段
    limit n;

#  查询的执行顺序
# 1. from 表
# 2. where 条件
# 3. group by 分组条件
# 4. having 过滤
# 5. deistinct 去重
# 6. order by 排序
# 7. limit n 取数据n条
# 8. select 输出结果

 

六、多表数据的查询操作

1.连表操作

连表后两个表组合成了一个表,可以按照一个表的查询来操作

# 内连接:只取两张表的共同部分
select * from employee inner join department on employee.dpt_id = department.id;

# 左连接:在内连接的基础上保留左表的记录(左表中没有对应关系的记录保留下来)
select * from employee left join department on employee.dpt_id = department.id;

# 右连接:在内连接的基础上保留右表的记录(右表中没有对应关系的记录保留下来)
select * from employee right join department on employee.dpt_id = department.id;

# 全外连接:在内连接的基础上保留两表没有对应关系的记录
select * from employee left join department on employee.dpt_id = department.id
union
select * from employee right join department on employee.dpt_id = department.id;

2.子查询

# 1. 带IN关键字的子查询
# 查询平均年龄在25岁以上的部门名
select name from department 
    where id in (
        select dep_id from employee
            group by dep_id
            having avg(age) >25
        );
# 查看技术部员工姓名
select name from employee
    where dpt_id = (
        select id from department
            where name='技术'
        );
# 查看不足1人的部门
select name from department
    where id not in (
        select distinct dep_id from employee
        );

# 2. 带比较运算符的子查询
# 查询大于所有人平均年龄的员工名与年龄
select name, age from employee
    where age >  (
        select avg(age) from employee
        );

3.连表与子查询组合使用

# 查询每个部门最新入职的那名员工
select * from employee as t1
    inner join (
        select post,max(hire_date) as max_hire_date from employee
            group by post) as t2  # 子表也出自员工信息表
        on t1.post = t2.post
    where t1.hire_date=t2.max_hire_date;

七、索引