数据库(Mysql)
1.什么是数据库
2.关系型数据库
-
属性
-
属性值
-
实体
3.Mysql数据库的使用
(1)mysql
(2)输入密码
4 常用的命令
4.1数据库
-
显示数据库
show databases;
-
创建数据库
create database 数据库的名称;
注意:建议数据库的名称最好是项目的英文名字
-
删除数据库
drop database 数据库的名称;
-
选择要使用的数据库
use 数据库的名称;
4.2 表格的创建
-
创建表格
字段也就是属性
数据的类型不能自定义的
int 整数
char varchar都是字符串
长度可以根据需要自定义
-
第一个表格
create table 表格的名称( 字段的名字 数据的类型(长度), 字段的名字 数据的类型(长度), .......... )
-
主键
原则上每个表格都要有主键的 主键的值不能为空
mysql> create table cat( -> id int(11) primary key, -> name varchar(22) -> );
mysql> create table dog( -> id int(11), -> name char(50), -> price int(3), -> color varchar(30), -> primary key(id,name) -> );
主键自增 mysql> create table cat( -> id int(11) primary key auto_increment, -> name varchar(33), -> age int(2), -> sex varchar(22) -> );
-
非空 not null
mysql> create table emp( -> id int(11), -> name varchar(50), -> salary int(11), -> tel varchar(50) not null, -> primary key(id) -> );
-
默认default
mysql> create table emp( -> id int(11) primary key auto_increment, -> name varchar(50), -> salary int(11), -> sex int(1) default 0 -> );
练习 创建银行卡表格 有卡号 持卡人姓名 电话 密码(默认6个1)
mysql> create table bankCard( -> id int(19), -> name varchar(33), -> tel varchar(50), -> password int(6) default 111111, -> primary key(id) -> ); Query OK, 0 rows affected (0.11 sec)
-
唯一unique
mysql> create table detial( -> id int(11) primary key auto_increment, -> name varchar(33), -> tel varchar(11) unique, -> sex int(1) -> );
练习
创建一个员工表emp ,编号是主键自增的,名字不能为空,性别默认是男(数据库中0表示男1表示女),电话号码不能重复,工资,部门。
mysql> create table emp( -> id int(50) primary key auto_increment, -> name varchar(20) not null, -> sex int(1) default 0, -> tel varchar(11) unique , -> salary int(11), -> dept varchar(30) -> ); Query OK, 0 rows affected (0.10 sec) mysql> insert into emp(name,sex,tel,salary,dept)values('zs',1,'110',2222,'market'); Query OK, 1 row affected (0.30 sec)
-
表格外键
create table dept( id int(11) PRIMARY KEY auto_increment, name VARCHAR(20), address VARCHAR(50), tel VARCHAR(11) ) create table emp( id int(11) auto_increment, deptid int(11), name VARCHAR(50), salary int(11), tel VARCHAR(11), PRIMARY KEY(id,deptid), FOREIGN key(deptid) REFERENCES dept(id) )
-
表格的范式
第一范式
所谓第一范式(1NF)是指在关系模型中,对于添加的一个规范要求,所有的域都应该是原子性的,即数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等非原子数据项。
第二范式
在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或记录必须可以被唯一地区分。
第三范式
在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
4.3 给表格添加数据
-
给表格的全部字段赋值
insert into 表格的名称 values(字段的值,.......)
-
给指定的字段赋值 注意:指定几个要赋值的字段就要给几个值 赋值的顺序和你指定字段的顺序一致
insert into 表格的名称(字段名,....) values(字段的值,.....)
4.4 修改表格数据
-
修改表格的全部数据
update 表格的名称 set 字段的名称=字段的值 ,......
-
带着条件的修改 and是条件都满足修改, or 是满足其中一个就修改
update 表格的名称 set 字段的名称=字段的值 ,...... where 字段名=字段值 and ...... update 表格的名称 set 字段的名称=字段的值 ,...... where 字段名=字段值 or ......
create table detial( id int(50) PRIMARY KEY auto_increment, customer varchar(50), customer_tel varchar(50), customer_address varchar(50), price int(11), product_name varchar(50), saler_name varchar(50), saler_chief varchar(50), borther_name varchar(50), t int(11), status int(2) ) insert into detial(customer,customer_tel,customer_address,price,product_name) values("张三","13877779999","体育场路580号",20,"大米") insert into detial(customer,customer_tel,customer_address,price,product_name) values("李四","13977779999","体育场路580号",20,"大米") insert into detial(customer,customer_tel,customer_address,price,product_name) values("李四","13777779999","体育场路580号",29,"小吃") insert into detial(customer,customer_tel,customer_address,price,product_name) values("李四","13777779999","体育场路580号",39,"辣条") insert into detial(customer,customer_tel,customer_address,price,product_name) values("李四","13787779999","体育场路570号",39,"火锅") SELECT * from detial; UPDATE detial set borther_name="特工",t=37,status=2 UPDATE detial set saler_name="南京大排档",saler_chief="御厨",status=1 where id=3 update detial set price=10 where customer="李四" and customer_address="体育场路580号" update detial set price=0 where customer="李四" or customer_address="体育场路580号" update detial set status=0 where t>=38
-
练习题目
-- 创建一个市民表 有市民的编号 市民的名称 市民的地址 月收入 其他收入 年龄 -- 给表格添加一些市民的信息 -- 政府给70+的老人月收入加个100块 -- 给住在西湖区和滨江区的每月工资涨500块 create table city_p( id int(11) PRIMARY KEY auto_increment, name VARCHAR(50), address VARCHAR(50), month_salary int(11), other_salary int(11), age int(2) ) insert into city_p(name,address,month_salary,other_salary,age)VALUES("好听","西湖区古墩路",900,90000,20); insert into city_p(name,address,month_salary,other_salary,age)VALUES("不号","西湖区紫金港",9000,900,80); insert into city_p(name,address,month_salary,other_salary,age)VALUES("吴亦凡","下城区武林广场",90,9000000,28); insert into city_p(name,address,month_salary,other_salary,age)VALUES("马云","西湖区",1,900000,60); insert into city_p(name,address,month_salary,other_salary,age)VALUES("刘强东","江苏",90,19000,40); insert into city_p(name,address,month_salary,other_salary,age)VALUES("马化腾","深圳",90,190000,50); insert into city_p(name,address,month_salary,other_salary,age)VALUES("好听2","西湖区",1900,90000,70); insert into city_p(name,address,month_salary,other_salary,age)VALUES("好听3","西湖区",900,190,20); insert into city_p(name,address,month_salary,other_salary,age)VALUES("好听4","滨江区",900,390,70); update city_p set month_salary=month_salary+100 WHERE age>=70 update city_p set month_salary=month_salary+500 WHERE address="西湖区" or address="滨江区" update city_p set month_salary=month_salary+500 WHERE address like "%西湖区%" or address like "%滨江区%" select * from city_p
4.5 删除数据
-
清空表格
delete from 表格的名称
-
根据条件删除
delete from 表格的名称 where 字段的名字=字段的值 and /or 字段的名字=字段的值 ......
delete from city_p where id=11 or id=14 or id=17 delete from city_p where age>=20 and month_salary=90 delete from city_p where address like "___西湖区%"
注意like的%任意个 _一个任意字符
练习
-- 创建一个市民表 有市民的编号 市民的名称 市民的地址 月收入 其他收入 年龄 -- 给表格添加一些市民的信息 -- 政府给70+的老人月收入加个100块 -- 给住在西湖区和滨江区的每月工资涨500块 create table city_p( id int(11) PRIMARY KEY auto_increment, name VARCHAR(50), address VARCHAR(50), month_salary int(11), other_salary int(11), age int(2) ) insert into city_p(name,address,month_salary,other_salary,age)VALUES("好听","西湖区古墩路",900,90000,20); insert into city_p(name,address,month_salary,other_salary,age)VALUES("不号","杭州市西湖区紫金港",9000,900,80); insert into city_p(name,address,month_salary,other_salary,age)VALUES("吴亦凡","下城区武林广场",90,9000000,28); insert into city_p(name,address,month_salary,other_salary,age)VALUES("马云","浙江省杭州市西湖区",1,900000,60); insert into city_p(name,address,month_salary,other_salary,age)VALUES("刘强东","江苏",90,19000,40); insert into city_p(name,address,month_salary,other_salary,age)VALUES("马化腾","深圳",90,190000,50); insert into city_p(name,address,month_salary,other_salary,age)VALUES("好听2","杭州市西湖区",1900,90000,70); insert into city_p(name,address,month_salary,other_salary,age)VALUES("好听3","西湖区曲院风荷",900,190,20); insert into city_p(name,address,month_salary,other_salary,age)VALUES("好听4","滨江区",900,390,70); update city_p set month_salary=month_salary+100 WHERE age>=70 update city_p set month_salary=month_salary+500 WHERE address="西湖区" or address="滨江区" update city_p set month_salary=month_salary+500 WHERE address like "西湖区%" or address like "%滨江区%" select * from city_p delete from city_p where id=11 or id=14 or id=17 delete from city_p where age>=20 and month_salary=90 delete from city_p where address like "___西湖区%"
4.6 查询表格数据(单表)
-
查询全部(不建议的写法)
select * from 表格的名称
分页 起始条 查几条 SELECT id,name, age+age*0.2 age from student LIMIT 0,5
-
查询指定字段
select 字段名字,字段名字.... from 表格的名称 limit 0,5
3.带条件的查询
select id,name,age,tel from student where age>=40 select id,name,age,tel from student where name='刘胖子' select id,name,age,tel from student where name='刘胖子' and age>20 select id,name,age,tel from student where name='刘胖子' or age>20
4.模糊查询 like
select id,`name`,age,tel,createdate from student where name like '刘%' select id,`name`,age,tel,createdate from student where name like '%胖%' select id,`name`,age,tel,createdate from student where name like '__子%' limit 0,5
5.查询中使用运算符 + - * / %
SELECT id,name,age*2 age from student (别名) SELECT id,name, age+age*0.2 age from student limit 0,5
-
统计查询
查询数据的条数
select count(*) n from student select count(*),avg(age),max(age),min(age) from student
查询不为空的字段的条数
select count(字段名字) from 表格的名字 select count(address),count(*),count(name) from student
练习
SELECT count(*) FROM `student` where createdate LIKE '2021-06-27%'; select count(*),avg(age),max(age),min(age) from student
7.分组查询 group by
select sex,COUNT(*) FROM student GROUP BY sex select count(*),avg(age),max(age),min(age) from student
create table emp( id int(11) PRIMARY KEY auto_increment, name VARCHAR(50), deptid int(11), salary int(11), age int(11), tel varchar(11) ) INSERT into emp(name,deptid,salary,age,tel)VALUES('欧阳锋',1,10000,30,'111000'); INSERT into emp(name,deptid,salary,age,tel)VALUES('李白',1,13000,32,'1131000'); INSERT into emp(name,deptid,salary,age,tel)VALUES('小乔',2,30000,13,'131000'); INSERT into emp(name,deptid,salary,age,tel)VALUES('安其拉',2,13400,10,'231000'); INSERT into emp(name,deptid,salary,age,tel)VALUES('梦琪',3,16000,13,'311000'); INSERT into emp(name,deptid,salary,age,tel)VALUES('亚瑟',3,90000,130,'511000'); INSERT into emp(name,deptid,salary,age,tel)VALUES('大乔',4,1000,13,'191000'); -- 查询平均年纪大于30的部门的员工数 平均工资 最高工资 最低工资 最大年纪 最小年纪 平均年纪 select deptid,count(*),avg(salary),max(salary),min(salary),avg(age),max(age),min(age) from emp group by deptid HAVING AVG(age)>30
注意: group by后面加限制条件使用having
-
排序 order by 默认升序的 desc 降序
select name,deptid,salary from emp ORDER BY salary desc
-- 练习:查询工资最高的三个人 select name,deptid,salary from emp ORDER BY salary desc LIMIT 0,3
4.7 查询表格数据(多表)
1.基本的多表联合查询 要有表格数据-1个关联键
create table dept( dept_id int(11) primary key, dept_name varchar(50), dept_address VARCHAR(50), dept_tel VARCHAR(50) ) create table emp( id int(11) , name VARCHAR(50), dept_id int(11), salary int(11), age int(11), tel varchar(11), PRIMARY KEY(id,dept_id), FOREIGN KEY(dept_id) REFERENCES dept(dept_id) ) insert into dept values(1001,"采购部","L1001室","0571-90900909"); insert into dept values(1002,"销售部","L2003室","0571-90900709"); insert into dept values(1003,"仓管部","L3004室","0571-80900709"); insert into dept values(1004,"人事部","L9004室","0571-99998888"); insert into emp values(1,"马云",1002,1,50,'8888'); insert into emp values(2,"董明珠",1002,100000,60,'7888'); insert into emp values(3,"罗永浩",1002,6000,40,'68888'); insert into emp values(4,"刘强东",1002,1000,30,'98888'); insert into emp values(5,"小熊",1001,190000,20,'58888'); insert into emp values(6,"小王",1001,290000,20,'58888'); insert into emp values(7,"小李",1001,790000,20,'58888'); insert into emp values(8,"小张",1001,990000,20,'58888'); insert into emp values(9,"龟田",1003,1000,60,'7889'); insert into emp values(10,"松下",1003,900,70,'8688');
-- 查询 采购部的员工的名字和工资 以及采购部的办公地点 select dept.dept_name,dept.dept_address,emp.name,emp.salary from dept,emp where dept.dept_id=emp.dept_id -- 多表联合查询建议是给表格起别名 select d.dept_name,d.dept_address,e.name,e.salary from dept d,emp e where d.dept_id=e.dept_id and d.dept_name="采购部" -- 查询每个部门的员工人数 以及平均工资 并按照平均工资降序排序 select d.dept_name,count(*)n,avg(e.salary)avg_salary from dept d,emp e where d.dept_id=e.dept_id GROUP BY d.dept_name,d.dept_id ORDER BY avg(e.salary) desc
2.链接查询
(1)左连接
select d.dept_name,e.name,e.salary from dept d left join emp e on d.dept_id=e.dept_id
(2)
(3)