MySql数据库第二课

最新推荐文章于 2024-09-29 16:19:38 发布

Bubble～

最新推荐文章于 2024-09-29 16:19:38 发布

阅读量785

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_53334593/article/details/121272219

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

– 第二课
/*
数据类型一致必须为主键
*/
– 创建库
create database informat;
– 使用库
use informat;
– 创建表
create table person(
p_id int primary key auto_increment,
p_name varchar(10) not null,
p_sex char(3) default ‘男’,
p_number int(11) not null
);
– 创建表
create table idcard(
id_id int primary key,
id_card int not null,
id_gov varchar(50) not null,
id_btime date not null,
id_etime date not null
);
– 查看当前库下的表
show tables;
– 添加外键
alter table idcard add constraint fk_idcard_person foreign key(id_id) references person(p_id);

– 创建表
create table cities(
ci_id int(10) primary key,
ci_name varchar(5) not null unique,
ci_pid int(10) not null unique
);
– 创建自关联表内创建外键
alter table cities add constraint fk_cid_cpid foreign key(ci_pid) references cities(ci_id);

– 自我整理笔记
/*
一、外键 1.一对一关系 2. 一对多关系 3. 多对多关系 4. 自关联
二、CRUD操作：数据增、删、改、简单查询

外键：一个表可以有一个或多个外键，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
外键可以不为本表的主键，但要对应另一个表的主键。

主表(父表)：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表
从表(子表)：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表

一对一
人对身份证
1 对 1
create table person(
per_id int primary key auto_increment,
per_name varchar(50),
per_phone varchar(50)
);
create table idcard(
idc_id int primary key,
idc_number varchar(50),
idc_start date,
idc_end date,
idc_publish varchar(50)
);
alter table idcard add constraint fk_card_person foreign key(idc_id) references person(per_id);
从表约束名外键主表(主键)

一对多
部门对员工
1 对 n
create table department(
dep_id int primary key auto_increment,
dep_name varchar(50),
dep_number int default 0,
dep_desc varchar(200)
);
create table employee(
emp_id int primary key auto_increment,
emp_name varchar(50),
emp_sex char(2) default ‘男’,
emp_birth date,
emp_phone varchar(50),
emp_depid int
);
alter table employee add constratin fk_emp_dep foreign key(emp_id) references department(dep_id);
从表约束名外键字段主表(主键)

多对多需要创建一个中间表
用户对角色
1 对 n
n 对 1
n 对 n
create table users(
use_id int primary key auto_increment,
use_name varchar(50) not null unique,
use_pwd varchar(50) not null,
use_status int
);
create table roles(
rol_id int primary key auto_increment,
rol_name varchar(50) not null
);
create table userroles(
use_id int,
rol_id int,
primary key(u_id,r_id) --复合主键
);
alter table userroles add constraint fk_ur_user foreign key(use_id) references users(use_id);
中间表约束名外键主表(主键)
alter table userroles add constraint fk_ur_role foreign key(rol_id) references roles(rol_id);
中间表约束名外键主表(主键)

自关联 (省市)
create table cities(
cit_id int primary key auto_increment,
cit_name varchar(50),
cit_pid int,
constraint fk_pid_cid foreign key(cit_pid) references cities(cit_id)
约束名外键主键
);

查看表结构：describe employee; 简写：desc employee
查看表详细结构：show create table employee;

null:表示该列可以存储null值
key：表示该列是否已编制索引。
(pri:表示该列是主键的一部分)
(uni:表示该列是unique的一部分)
(mul:表示在列中某个给定值允许多次出现)
default：表示该列是否有默认值
extra:表示可以获取的与给定列有关的附加信息，例如auto_increment等

CRUD操作

插入数据
*单条记录
insert into employee values(字段值1,字段值2…); – 向表中所有的列插入一条记录
表名
或者 insert into employee(col1,col2…) values(字段值1,字段值2…)
表名字段1 字段1
*多条记录
insert into employee(col1,col2…) values(值1,值2,…),(值1,值2,…),(值1,值2…);
表名字段1 字段1 第一列数据第二列数据第三列数据
备份表数据
*表不存在
create table emp_bak as select * from emp;
新创建表原表
*表存在且备份表与原表表结构一致
insert into emp_bak select * from emp;
备份表原表
希望将原表中对应记录保存到emp_bak
insert into emp_bak select emp_id,emp_name,emp_sex from emp;
备份表字段1 字段2 字段3 原表
改更新数据
update emp set empname=‘jerry’, empaddr=‘延安’, empphone=‘118’ where empid=1;
表名需更改字段新值需更改字段新值需更改字段新值条件
删除数据
delete from employee where empid=2
表名条件
如果要删除主表记录，必须先解除关联关系。
如果删除语句没有where，会把数据表中记录全部删除，类似truncate table
truncate 将直接删除原来的表，并重新创建一个表，其语法结构为：truncate table 表名
truncate 直接删除表而不是记录，因此执行速度比delete快，而且不能用在有主外键的主表中。

5.查询数据 (select语句)
查询所有字段：select * from employee;
表名
查询指定字段：select emp_id from employee;
字段名表名
查询多个字段：select emp_id,emp_phone,emp_number from employee;
字段1 字段2 字段3 表名
查询时起别名：select emp_id (as) ‘员工编号’,emp_name (as) ‘员工名字’ from employee;
字段1 可有可无别名1 字段2 可有可无别名2 表名

查询指定记录：select emp_sex,emp_id from employee where emp_sex=‘女’，emp_id=1;
字段1 字段2 表名？(查询条件)
带关键字的查询：select * from employee where emp_id=1 or emp_id=4 or emp_id=8 or emp_id=10;
或 select * from employee where emp_id in (1,4,8,10);
select * from employee where emp_id not in (1,4,8,10);
范围查询：select * from employee where emp_id>=6 and emp_id<=10;
表名小的值范围大的值范围
select * from employee where emp_id between 6 and 10;
表名字段
字符匹配查询：通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符。
通配符‘ % ’ ：匹配任意长度的字符，甚至包括零字符
通配符‘ _ ’ ：一次只能匹配任意一个字符
% select * from employee where emp_name like ‘a%’; – 以a开头
select * from employee where emp_name like ‘%a’; – 以a结尾
select * from employee where emp_name like ‘%a%’; – 包含a
_ select * from employee where emp_name like ‘a_’;
select * from employee where emp_name like ‘_a’;
select * from employee where emp_name like ‘a’;
查询空值:
查询空：select * from employee where emp_addr is null; 查询null
select * from employee where emp_addr =’’; 查询为空的值
select * from employee where emp_addr is null or emp_addr=’’;
非空查询：select * from employee where emp_addr is not null; 查询非空值
select * from employee where emp_addr !=’’; 查询不是空的值
select * from employee where emp_addr is not null or emp_addr !=’’;
多条件查询：
and select * from employee where emp_sex=‘男’ and emp_addr =‘西安’；–查询住在西安的男
or select * from employee where emp_sex=‘男’ or emp_addr = ‘西安’; --查询住在西安或者所有的男
查询结果不重复：select distinct emp_id,emo_name from employee;
查询结果排序：desc：倒叙 ;asc:默认值升序
单列排序：select * from employee order by emp_id desc;
多列排序：select * from employee order by emp_id,emp_phone desc;
聚合函数：MySQL提供一些查询功能，可以对获取的数据进行分析和报告。忽略空值返回单行单列
函数作用
AVG() ：返回某列的平均值
COUNT() ：返回某列的行数
MAX() ：返回某列的最大值
MIN() ：返回某列的最小值
SUM() ：返回某列值的和
count(): count(*) :计算表中总的行数，不管某列是否有数值或者为空值。
count(字段名) :计算指定列下总的行数，计算时将忽略空值的行。
sum(): 求总和的函数，返回指定列值的总和。 SUM()函数在计算时，忽略列值为NULL的行。
avg(): 计算返回的行数和每一行数据的和，求得指定列数据的平均值。
max(): 返回指定列中的最大值。
(max()函数除了用来找出最大的列值或日期值之外，还可以返回任意列中的最大值，包括返回字符类型的最大值。在对字符类型数据进行比较时，
按照字符的ASCII码值大小进行比较，从a~z，a的ASCII码最小，z的最大。在比较时，先比较第一个字母，如果相等，继续比较下一个字符，
一直到两个字符不相等或者字符结束为止。)min与此类似

分组查询：group by 通常和集合函数一起使用
select dep_id,count(1) from employee group by dep_id; – 查询每个部门的人数
字段？表名？
select dep_id,emp_sex,count(1) from employee group by dep_id,emp_sex; --查询每个部门的男女人数
字段1 字段2 ？表名
分组后排序：select dep_id,emp_sex,count(1) from employee group by dep_id,emp_sex order by dep_id desc,count(1) desc;
过滤分组：group having
select dep_id,emp_sex,count(1) from employee group by dep_id,emp_sex having count(1)>=3 order by count(1) desc,dep_id desc;
limit: mysql特有
select返回所有匹配的行，有可能是表中所有的行，若仅仅需要返回第一行或者前几行，可使用LIMIT
关键字。
select * from employee limit 0,5; 等价 select * from employee limit 5;
select * from employee limit 5,5;
select * from employee limit 10,5;
第n次： (n-1)*5,5

create table emp_bak as select * from idcard;
select * from employees where job_id=IT_PROG or job_id=AD_VP or job_id= or emp_id=10;
– 查看工资最高的员工信息
select salary from employees w
select * from employees order by salary desc limit 1;