mysql(基础语法)

准备一张员工表

/*Navicat Premium Data TransferSource Server         : localhost_3306Source Server Type    : MySQLSource Server Version : 80037 (8.0.37)Source Host           : localhost:3306Source Schema         : studymysqlTarget Server Type    : MySQLTarget Server Version : 80037 (8.0.37)File Encoding         : 65001Date: 24/12/2024 11:32:08
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (`empno` int NOT NULL AUTO_INCREMENT COMMENT '雇员编号',`ename` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '雇员姓名',`job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '表示工作职位',`mgr` int NULL DEFAULT NULL COMMENT '表示一个雇员的领导编号',`hiredate` datetime NULL DEFAULT NULL COMMENT '表示雇佣日期',`sal` double NULL DEFAULT NULL COMMENT '表示月薪，工资',`comm` double NULL DEFAULT NULL COMMENT '表示奖金或佣金',`deptno` int NULL DEFAULT NULL,PRIMARY KEY (`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of emp
-- ----------------------------SET FOREIGN_KEY_CHECKS = 1;

1.向表中添加一条数据

insert into emp(ename,job,mgr,hiredate,sal,comm,deptno)
values("孙悟空","保镖",4,'2025-01-15 08:30:00',4000,243,0);

2.删除emp表中数据根据条件

2.1

delete from emp where ename='李白';

2.2批量删除员工信息

DELETE from emp where empno in(7901,7902,7903,7904);

3.修改表中数据根据条件

update emp set job='战神' where ename='哪吒';

4.查询表中所有数据

4.1

select *from emp;

 4.2批量查询

SELECT *from emp where empno in (1,2,3,4);

4.3模糊查询

SELECT*from emp WHERE emp.ename like 'A%'

SELECT*from emp WHERE emp.ename like '%A%'

SELECT*from emp WHERE emp.ename like '%A'

 5.排序

SELECT*from emp
ORDER BY sal ASC;

SELECT*from emp
ORDER BY sal desc;