mysql的增删改查

库的增删改查 增

create database 库名称;
create database 数据库名称 charset 编码方式;

CREATE DATABASE company;

CREATE TABLE dep(
id INT PRIMARY KEY,
dnname VARCHAr(50)
)DEFAULT CHARSET = utf8;

删

drop database 库名称;

改

修改编码方式:alter database 库名称 charset 编码方式

查

查看所有库:show databases;
查看指定库:show database 库名称;

表的增删改查 增

create table 表名称；

删

drop table 表名称

改

• 修改表的名称：rename table 表名称 to 新名称
• 添加字段：alter table 表名称 add 字段 字段数据类型
• 修改字段数据类型：alter table 表名称 modify 字段 新的数据类型
• 删除字段：alter table 表名称 drop 字段

查

查看所有表：show tables
查看指定表：desc table 表名称

表里数据的增删改查 增

insert into 表名 values(…) 该方式必须保证 插入的数据个数与 表格字段一 一对应

INSERT INTO dep VALUES( 1,'财务部');

删

删除表的所有：delete from 表名;
删除指定的数据：delete from 表名 where 条件;

改

修改所有数据：update 表名 set 字段名称=新的值,字段名称2=值2;
修改指定数据：update 表名 set 字段名称=新的值,字段名称2=值2 where 条件

查

创建2个简单的表，进行实例演示

-- 创建库
CREATE DATABASE company;
-- 创建一个部门表  部门编号  部门名称
CREATE TABLE dep (
id INT  PRIMARY KEY , -- 主键 唯一 且不为空
dname VARCHAr(50)

)DEFAULT CHARSET = utf8;

-- 添加部门
INSERT INTO dep VALUES(1,'财务部');

INSERT INTO dep VALUES(2,'人事部');

INSERT INTO dep VALUES(3,'研发部');
-- 创建员工表  id ,age,sex, salary,hire_date,dept_id
CREATE TABLE employee (
id VARCHAr(50) PRIMARY KEY, -- primary key表示主键

age TINYINT(3) NOT NULL,   -- not null 不为空

sex ENUM('男','女')  DEFAULT '男',  -- enum 表示枚举 

salary DECIMAL(5,2),-- 总共5位 小数是2位

hire_date DATE,

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES dep(id)
)DEFAULT CHARSET = utf8;

-- 添加数据
INSERT INTO employee VALUES('xk001',18,DEFAULT,100,'2022-3-12',1);
-- sysdate() 函数 ，获取当前系统时间
INSERT INTO employee VALUES('xk002',10,'女',200,SYSDATE(),1);

INSERT INTO employee VALUES('xk003',30,'女',500,SYSDATE(),2);


INSERT INTO employee VALUES('xk008',20,'男',10,NULL,2,'黄中');

DELETE FROM employee WHERe id ='xk003'


-- 增加外键约束 
-- 外键约束 保证了 数据的完整性以及正确性

ALTER TABLE employee ADD CONSTRAINT fk_depid FOREIGN KEY (dept_id)

REFERENCES dep(id);

查找符合条件的数据

查找性别是男的

SELECt * FROM employee WHERe sex='男'

查询出部门编号是1 或者是2 或者是3部门的人员

SELECt * FROM employee WHERe dept_id= 1 OR dept_id =2 OR dept_id = 3;
SELECt * FROM employee WHERe dept_id  IN (1,2,3) 

查询出部门编号在1，2，3 以外的数据

SELECt * FROM employee WHERe dept_id NOT IN (1,2,3) 
SELECt * FROM employee WHERe dept_id!= 1 AND dept_id !=2 AND dept_id != 3;

查询部门编号是1并且年龄是10岁的

SELECt * FROM employee WHERe dept_id =1 AND age = 10;

查询入职时间是空的数据

SELECt * FROM employee WHERe hire_date IS NOT NULL;

模糊查询

查询出以张开头的姓名

SELECt * FROM employee WHERe ename LIKE '张%'

查询出有包含“张” 字的姓名

SELECt * FROM employee WHERe ename LIKE '%张%'

姓名第二个字符是“张” 总共三个字符

SELECt * FROM employee WHERe ename LIKE '_张_'

排序

order by 字段名 asc(升序)，desc(降序)

查询出所有员工并且按照薪资降序排列

SELECt * FROM  employee ORDER BY salary DESC

先按照薪资降序排列 如果薪资相同则按照年龄降序排列

SELECt * FROM  employee ORDER BY salary DESC,age DESC

分页查询

仅限于mysql 使用limit进行分页查询 limit 起始值 ,数据量

SELECt * FROM  employee LIMIT 0,2

聚合函数

聚合函数 max(字段) min(字段) count(字段)，avg(字段)，sum(字段)

SELECt MAX(salary) AS 薪资最大值 FROM employee WHERe dept_id = 1

SELECt MIN(salary) AS 薪资最小值 FROM employee WHERe dept_id = 1

SELECt COUNT(*) AS 人数 FROM employee;

SELECt SUM(salary) FROM employee 

SELECt AVG(salary) FROM employee

分组查询group by

统计出每个部门的人数

SELECt dept_id,COUNT(*) FROM employee GROUP BY dept_id

统计出部门人数少于三个的部门编号

SELECt dept_id FROM employee GROUP BY dept_id HAVINg COUNT(*)<3

统计出每个部门中男性的人数

SELECt dept_id ,COUNT(*) FROM employee WHERe sex = '男' GROUP BY dept_id

统计出每个部门中男性的人数并且部门人数大于1

SELECt dept_id ,COUNT(*) FROM employee WHERe sex = '男' GROUP BY dept_id HAVINg COUNT(*)>1

多表查询

SELECt * FROM employee;

SELECt * FROM dep

SELECt * FROM employee ,dep WHERe employee.dept_id = dep.id

SELECt * FROM employee e ,dep d WHERe e.dept_id = d.id

SELECt e.id,e.ename,d.dname FROM employee e ,dep d WHERe e.dept_id = d.id

内连接 ，左（外）连接，右（外）连接，全连接，交叉连接

内连接 inner join

SELECt * FROM employee e INNER JOIN dep d ON e.dept_id = d.id

左连接 left join 左表的数据 需要全部显示 如果对应的右表没有数据 则显示空

SELECt * FROM dep LEFT OUTER JOIN employee ON dep.id = employee.dept_id

右连接 right join

SELECt * FROM  employee RIGHT JOIN dep ON dep.id = employee.dept_id

全连接 full join

SELECt * FROM  employee FULL JOIN dep 

in子查询

仅仅返回一个数据列，这个列的数据将供外层查询语句进行比较操作

查询员工中是否存在年龄大于21岁的员工，如果存在则查询部门表中的所有记录

SELECt * FROM dep WHERe  id NOT IN(SELECt dept_id FROM employee WHERe age <=21)

SELECt * FROM dep WHERe  id IN(SELECt dept_id FROM employee WHERe age >21)

exists 关键字的查询

exists关键字后面的参数可以是任意一个子查询，这个子查询的作用相当于测试，它不产生任何数据，只返回true或false,当返回true时 外层查询才开始执行

查询员工中是否存在年龄大于21岁的员工，如果存在则查询部门表中的所有记录

SELECt * FROM dep WHERe EXISTS (SELECt dept_id FROM employee WHERe age>21 AND dep.id=employee.dept_id  )