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  )