Mysql基础增删改查,主外键关联

ゝ一纸荒年。 2023-01-03 04:51 140阅读 0赞

## Mysql基础入门 ##

### 章节导航 ###

*   *  Mysql基础入门
     *  创建
     *  新增
     *  删除
     *  修改
     *  查询
     *  数据类型
     *  主键
     *  外键
     *  数值计算
     *  关联查询

1.登录mysql：mysql -uroot -p
     2.使用数据库： use 数据库名;
     3.导入sql文件: souse 目录;
     4.各个关键字的顺序:select 字段信息 from 表名 where 条件 group by 字段 having 聚合函数条件 order by 字段 limit ;
     5.关联查询关键字顺序: select * from A join B on 关联关系 where 普通字段条件 group by 字段名....

## 创建 ##

1.创建数据库名: create database 数据库名;
    2.创建表: create table 表名 (字段);
    3.创建指定字符集的表: create table 表名(name varchar(10),score int) charset=gbk;
    4.创建指定字符集的表: create table 表名(name varchar(10),score int) default charset=utf8;//默认字符集

## 新增 ##

1.添加表字段(末尾添加):alter table 表名 add 字段 类型;   
    eg: alter table student add gender varchar(5);
    
    2.添加表字段(首字段添加): alter table 表名 add 字段 类型 first;   
    eg:alter table student add id int first;
    
    3.在某个字段的后面添加:alter table 表名 add 字段 类型 atter 字段;   
    eg: alter table student add money int after name;
    
    二.数据新增
    1.全表插入(要求值的数量和顺序和表字段要保持一致):insert into 表名 values(值1,值2...); 
    eg: insert into person values("Tom",18);
    
    2.指定字段插入:insert into 表名(字段名1,字段名2) values(值1,值2);  
    eg: insert into person(name,age) values("jerry",18);
    
    3.批量插入:
     3.1.insert into person values('Lucy',20),('Lily',21);
     3.2.insert into person(name)values('Lilei'),('Hanmeimei');

## 删除 ##

1.删除数据库: drop database 表名;
    2.删除表: drop table 表名;
    3.删除表字段: alter table 表名 drop 字段名; eg: alter table student drop money;
    4.删除指定字段的信息: delete from 表名 where 条件; eg:delete from person where age=0;

## 修改 ##

1.修改表名:rename table 原名 to 新名;
    2.修改表字段:alter table 表名 change 原字段名 新字段名 新类型;
    eg: alter table student change gender money int;
    3.修改数据库密码: mysqladmin -u用户名 -p旧密码 password 新密码
    eg:mysqladmin -uroot -p123456 password root  //将密码123456修改为root
    4.修改指定字段的值:update 表名 set 字段名=xxx,where条件;
      4.1 修改Tom的年龄为8岁, update person set age=8 where name='Tom';
      4.2修改年龄为空的年龄为10岁: update person set age=10 where age is null;

## 查询 ##

1.查看所有表：show tables;
    2.查询表所有内容：select * from 表名;
    3.查看表结构：desc 表名;
    4.查看数据库的字符集: show create database 数据库名;
    5.模糊查询并排序:select * from 表名 where encode like '字段' order by 字段(此处+ desc 为降序);
    eg:select * from emp where job='销售' order by sal desc;
    
    说明: 1._: 代表1个未知符号 2. %: 代表0或多个未知符号
    
    1. 以x开头: x% 2. 以x结尾: %x 3. 包含x: %x% 4. 第二个字符x: _x% 5. 倒数第二个是x: %x_ 6. 以x开头倒数第二个是y: x%y_ ;
    
    eg: 1.1 以x开头 x% : select * from emp where ename like '孙%';
    
    6.查询所有:select * from 表名;
    7.查看表详情:show create table 表名;
    8.查询所有数据库：show databases;
    9.查询表: show tables;
    10.查询指定条件的信息: select 字段 from 表名 where 字段条件
    eg: select name,age from person where age>20;
    
    11.去重查询:select distinct 字段 from 表名;
    12.查询某个字段不为空的信息: select * from 表名 where 字段 is not null;
    eg:select * from emp where MGR IS NOT NULL;
    
    13.查询满足多个字段的值的信息: select 字段 from表名 where 字段 or 字段;
    eg: select ename,job,sal from emp where job='人事' or sal>3000;
    
    14.查询某个字段的值为多个值的信息: select * from 表名 where 字段 in(值1,值2,值3);
    eg: select * from emp where sal in(1000,3000,5000);
    
    15.查询某个字段的值在某个字段两个值之间的信息: select 字段 from 表名 where 字段 between 值1 and值2;
    eg: select ename,sal from emp where sal between 1000 and 2000;
    
    16.排序: 
      16.1 升序 order by 字段: select * from 表名 order by 字段;
      16.2降序: order by 字段 desc: select * from 表名 order by 字段 desc;
      
      16.3多个字段排序:select * from 表名 order by 字段,字段2,字段三;
       eg:select ename,sal,deptno from emp order by deptno desc,sal;
    
    17.分页查询limit: limit 跳过条数(跳过页数*每页条数),请求条数(每页条数);
    eg: select * from emp limit 0,3;
    
    18.分组查询(以某个字段作为条件): group by ;
    eg:查询每个部门的平均工资:select deptno,avg(sal) from emp group by deptno;
    
    19.次数相关: count(*): select 字段,count(*) from 表名;
    eg:查询每种工作的人数:select job,count(*) from emp group by job;

## 数据类型 ##

1.date:只能保存年月日
    2.Time:只能保存时分秒
    3.datetime:默认值为null,最大值9999-12-31;
    4.Timestamp:(时间戳:距1970年1月1号的毫秒数):默认值为当前系统时间,最大值2038-1-19
    5.浮点数: double(m,d) m代表总长度 d代表小数长度 53.234 double(5,3) ,超高精度浮点数 decimal(m,d)精度远高于double 只有涉及超高精度运算时使用

## 主键 ##

1.主键:表示数据唯一性的字段称为主键
    2.约束:创建表时给表字段添加的限制条件
    3.主键约束:限制主键的值,唯一且非空
    4.格式:primary key
    eg: create table t1(id int primary key, name varchar(10));
    
    5.自增:auto_increment(自增字段给null值会触发自增,从历史最大值+1)
    eg: create table t1(id int primary key auto_increment, name varchar(10));

## 外键 ##

外键约束作用:设置外键可以确保数据库完整性,模式分为:
    删除时
    1:RESTRICT(制约)或NO ACTION时(无动作):即:当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。
    2.Cascade时(级联:就是删除主键表的同时，外键表同时删除):即:则当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录。
    3.Set Null时(置空):即:则当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）。
    
    更新时
    1:RESTRICT或NO ACTION时:即:则当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新。
    2.Cascade时:即:则当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则也更新外键在子表（即包含外键的表）中的记录。
    3.Set Null时:即:则当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）

如何设置外键约束  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk4MDExNg_size_16_color_FFFFFF_t_70]

语句创建:alter table 子表的数据表名 add foreign key(子表的外键名称) references 父表的数据表名称（父表的主键名称）;
    eg: alter table book add foreign key(tid) references book_type(id) on delete restrict ON UPDATE CASCADE;
    
    删除外键:alter table 表名 drop foreign key 外键名称
    eg:alter table book drop foreign key book_ibfk_5

## 数值计算 ##

1.查询每个员工的姓名 工资及年终奖: select ename,sal,sal*5 from emp
    2.查询每个员工的姓名 工资及年终奖(别名形式): select ename 名字,sal 工资,sal*5 年终奖 from emp
    eg:select job,count(*) c from emp group by job having c=1;
    
    3.聚合函数: 1.平均值:avg(字段名) 2.最大值:max(字段名) 3.最小值:min(字段名) 4.求和:sum(字段名) 5.计数:count(*)
    
    4.having后面写聚合函数条件,需要结合分组查询使用: having 聚合函数条件
    eg:select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

## 关联查询 ##

创建表时: 表与表之间存在的业务关系 外键:用来建立关系的字段称为外键
    1.一对一:有AB两张表,A表的一条数据对应B表的一条,同时b表的一条也对应A表的一条
    2.一对多:有AB两张表,A表的一条数据对应B表的多条,同时b表的一条对应A表的一条
    3.多对多:有AB两张表,A表的一条数据对应B表的多条,同时b表的一条对应A表的多条
    二.
    2.1关联查询-等值连接: select * from 表1,表2 where 关联关系 and 其他条件 
    eg: 查询工资高于两千的员工的姓名和对应的部门名: select e.ename,d.dname from emp e, dept d where e.deptno=d.deptno and e.sal>2000;
    
    2.2 内连接: select * from 表1 join 表2 on 关联关系 where 其他条件
    eg: 查询工资高于两千的员工的姓名和对应的部门名: select e.ename,d.dname , e.sal from emp e join dept d on e.deptno=d.deptno where e.sal>2000;
    
    2.3 外连接: 查询一张表的全部数据和另外一张表的交集数据:select * from 表1 left/right join 表2 on 关联关系 where 条件
    eg:查询所有员工姓名和对应的部门名:select e.ename ,d.dname from emp e left join dept d on e.deptno=d.deptno;
    
    总结:如果查询的是多张表的数据,则使用关联查询,查询的是两张表的交集数据使用等值连接或者内连接(推荐),如果是查询的是一张表的全部和另外一张表的交集数据则使用外连接

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk4MDExNg_size_16_color_FFFFFF_t_70]: /images/20221119/1e646cf6eba243c8931346cfa2ad0735.png