MySQL基本操作

已于 2022-02-17 23:35:51 修改
阅读量443 收藏
点赞数
分类专栏： 数据库 文章标签： mysql sql 数据库
于 2022-01-06 16:18:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_44730086/article/details/122346913
版权

SQL语句:

  • 入门操作

    -- 如果存在名为school的数据库就删除它
drop database if exists `school`;

-- 创建名为school的数据库并设置默认的字符集和排序方式
create database `school` default character set utf8mb4 collate utf8mb4_general_ci;

-- 切换到school数据库上下文环境
use `school`;


-- 创建student表
create table student 
(
id int unsigned primary key comment '编号',
name varchar(20) not null comment "姓名",
sex char(1) not null comment "性别",
birthday date not null comment "生日",
tel char(11) not null comment "电话",
remark varchar(200)
)comment "学生student";

-- 创建学院表
create table `tb_college`
(
`col_id` int unsigned auto_increment comment '编号',
`col_name` varchar(50) not null comment '名称',
`col_intro` varchar(500) default '' comment '介绍',
primary key (`col_id`)
) engine=innodb auto_increment=1 comment '学院表';

-- 创建学生表
create table `tb_student`
(
`stu_id` int unsigned not null comment '学号',
`stu_name` varchar(20) not null comment '姓名',
`stu_sex` boolean default 1 not null comment '性别',
`stu_birth` date not null comment '出生日期',
`stu_addr` varchar(255) default '' comment '籍贯',
`col_id` int unsigned not null comment '所属学院',
primary key (`stu_id`),
constraint `fk_student_col_id` foreign key (`col_id`) references `tb_college` (`col_id`)
) engine=innodb comment '学生表';

-- 创建教师表
create table `tb_teacher`
(
`tea_id` int unsigned not null comment '工号',
`tea_name` varchar(20) not null comment '姓名',
`tea_title` varchar(10) default '助教' comment '职称',
`col_id` int unsigned not null comment '所属学院',
primary key (`tea_id`),
constraint `fk_teacher_col_id` foreign key (`col_id`) references `tb_college` (`col_id`)
) engine=innodb comment '老师表';

-- 创建课程表
create table `tb_course`
(
`cou_id` int unsigned not null comment '编号',
`cou_name` varchar(50) not null comment '名称',
`cou_credit` int not null comment '学分',
`tea_id` int unsigned not null comment '授课老师',
primary key (`cou_id`),
constraint `fk_course_tea_id` foreign key (`tea_id`) references `tb_teacher` (`tea_id`)
) engine=innodb comment '课程表';

-- 创建选课记录表
create table `tb_record`
(
`rec_id` bigint unsigned auto_increment comment '选课记录号',
`stu_id` int unsigned not null comment '学号',
`cou_id` int unsigned not null comment '课程编号',
`sel_date` date not null comment '选课日期',
`score` decimal(4,1) comment '考试成绩',
primary key (`rec_id`),
constraint `fk_record_stu_id` foreign key (`stu_id`) references `tb_student` (`stu_id`),
constraint `fk_record_cou_id` foreign key (`cou_id`) references `tb_course` (`cou_id`),
constraint `uk_record_stu_cou` unique (`stu_id`, `cou_id`)
) engine=innodb comment '选课记录表';

# 插入表
insert into student values(1,"张三","男","1996-03-20","15907876160",NULL);

# 显示所有表
show tables;

# 查看单个表信息
desc student;

# 显示建表代码 
show create table student;

# 删除表
drop table student;

  • 字符串存储，char，varchar、text的区别，分别合适什么场景。

    char长度固定，即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。
    varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
    text不设置长度， 当不知道属性的最大长度时，适合用text。
    按照查询速度： char最快， varchar次之，text最慢。

数据表的操作

  • 相关常规操作

    # 向表中添加字段
/* 格式：
ALTER TABLE 表名称
add 列1 数据类型 [约束] [COMMIT 注释],
add 列2 数据类型 [约束] [COMMIT 注释];  
*/
ALTER TABLE student
ADD address VARCHAR(200) NOT NULL,
ADD home_tel char(11) NOT NULL;

# 修改表中字段类型和约束
/* 格式：
ALTER TABLE 表名称
MODIFY 列1 数据类型 [约束] [COMMIT 注释],
MODIFY 列2 数据类型 [约束] [COMMIT 注释];  
*/
ALTER TABLE student
MODIFY home_tel VARCHAR(20) NOT NULL;

# 查看表的结构
DESC student;

# 修改表中字段名称
/* 格式：
ALTER TABLE 表名称
CHANGE 旧列1 新列1 数据类型 [约束] [COMMIT 注释],
CHANGE 旧列2 新列2 数据类型 [约束] [COMMIT 注释];  
*/
ALTER TABLE student
CHANGE address home_address VARCHAR(200) NOT NULL;

# 删除表中字段名称
/* 格式：
ALTER TABLE 表名称
DROP 列1,
DROP 列2;
*/
ALTER TABLE student
DROP home_address,
DROP home_tel;

  • 约束

    参考：https://www.cnblogs.com/fanqisoft/p/10697866.html

    CREATE TABLE t_teacher(
	id INT UNSIGNED PRIMARY KEY auto_increment,  # auto_increment表示自然增长
 	name VARCHAR(20) NOT NULL,
 	tel CHAR(11) NOT NULL UNIQUE,		# UNIQUE表示唯一，即电话号码不同人应该不一样
 	married BOOLEAN NOT NULL DEFAULT FALSE	# DEFAULT如果没有数据，则默认为false。表中显示0
);
  
  
# 父表，部门表
CREATE TABLE t_dept(
	deptno INT UNSIGNED PRIMARY KEY auto_increment,  # 部门编号
 	dname VARCHAR(20) NOT NULL UNIQUE,
  tel char(4) UNIQUE
);
  
# 子表，员工表
CREATE TABLE t_emp(
	empno INT UNSIGNED PRIMARY KEY auto_increment,
	ename VARCHAR(20) NOT NULL,
	sex ENUM("男","女") NOT NULL,		# 可以设定枚举，选择男或女
	deptno INT UNSIGNED NOT NULL,		# 员工部门编号
	hiredate DATE NOT NULL,		# DATE要注意格式2021-12-01
  # 外键约束 t_emp表中的deptno关联自t_dept中的deptno，但是不建议用外键约束，容易形成闭环，互相牵制
	FOREIGN KEY (deptno) REFERENCES t_dept(deptno)	
);

  • 索引

    DROP TABLE IF EXISTS t_message;

CREATE TABLE t_message(
	id INT UNSIGNED PRIMARY KEY,
	content VARCHAR(200) NOT NULL,
	type ENUM("公告","通报","个人通知") NOT NULL,
	create_time TIMESTAMP NOT NULL,
	INDEX idx_type(type)		# 创建表的时候直接指定索引，INDEX 素引名(字段)
);

/* 删除/添加/显示索引:
删除：DROP INDEX 素引名 ON 表名
添加：CREATE INDEX 素引名 ON 表名(字段);
添加：ALTER TABLE 表名 ADD INDEX 索引名(字段);
显示：SHOW INDEX FROM 表名;
*/
DROP INDEX idx_type on t_message;
CREATE INDEX idx_type on t_message(type);
ALTER TABLE t_message ADD INDEX idx_test(content);
SHOW INDEX FROM t_message;
# 显示结果
# table 		Non_unique		Key_name	index		Column_name
# t_message	0(表示唯一的)	 PRIMARY		 1		 id			
# t_message	1(表示非唯一)	 idx_type		 1		 type		
# t_message	1(表示非唯一)	 idx_test		 1		 content
数据库的基本查询

  • 记录查询

    # 各种句子执行顺序
# FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
# FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT
# 查看表的列数据，每列或者指定列
USE demo;
SELECT * FROM t_emp;
SELECT empno, ename, sal FROM t_emp;

# 使用列别名，对于不雅观的列名，可以选择重命名的方式展示输出结果,不会对底层数据进行修改
SELECT 
	ename,
	sal*12 AS income
FROM t_emp;

# 数据分页查询格式：
# SELECT ... FROM ... LIMIT 起始位置,偏移量;如果不写起始默认为0
# 取从起始位置开始，往后的偏移量个数据
SELECT ename,job,sal FROM t_emp LIMIT 5,5;

# 结果集排序：
# SELECT ... FROM ... ORDER BY 列名 [ASC|DESC];  
# ASC为生序，DESC为降序，不写默认为生序，数字和字符串都可以排序
SELECT ename,empno,sal FROM t_emp ORDER BY empno DESC;
# 定义多个排序条件，万一第一条件相同，则比较第二条
SELECT ename,empno,sal 
FROM t_emp 
ORDER BY sal DESC,ename ASC;

# 排出工资最高的5个人
SELECT ename,empno,sal 
FROM t_emp 
ORDER BY sal DESC LIMIT 0,5;

# 结果集去重：用distinct关键字，
# 一句最多使用一个distinct，使用多了会报错
# 一个distinct只能查询一组数据，查询多了会失效
SELECT DISTINCT job FROM t_emp;

  • 条件查询

    # 条件查询
# SELECT ... FROM ... WHERE 条件 [AND|OR] 条件 ...;
SELECT empno,ename,sal FROM t_emp
WHERE (deptno=20 OR deptno=10) AND sal >= 2000;

# 查询年收入大于15000美金以及工龄大于20年
SELECT empno,ename,sal,hiredate
FROM t_emp
# IFNULL(expr1,expr2)假如参数1的值为null，则返回参数2
WHERE deptno=10 AND (sal+IFNULL(comm,0))*12 >= 15000	
# DATEDIFF(expr1,expr2)参数1的日期和参数2的日期间隔天数
AND DATEDIFF(NOW(),hiredate)/365 >= 20;

# 比较运算符
SELECT empno,ename,sal,deptno,hiredate
FROM t_emp
WHERE deptno IN(10,20,30) AND job!="CLERK"
AND hiredate<"1985-01-01";

# 高级比较运算符
SELECT ename,sal,deptno,hiredate,comm
FROM t_emp
WHERE comm IS NOT NULL
AND sal NOT BETWEEN 1000 AND 8000
# REGEXP为正则匹配
AND ename REGEXP "^*$";

  • 逻辑运算

    # AND,OR,NOT,XOR(异或)
SELECT ename,sal,deptno,hiredate,comm
FROM t_emp
# XOR异或，只有一真一假的时候才算成立True，否则都是False
WHERE NOT deptno IN(10,20) XOR sal>=2000;
数据库的高级查询

  • 聚合函数(聚合函数不能写在where里面，严重的错误，只能写在select里面)

    # 收入求均值AVG：
SELECT AVG(sal+IFNULL(comm,0)) AS avg FROM t_emp;
# 收入求和SUM：
SELECT SUM(sal+IFNULL(comm,0)) FROM t_emp WHERE deptno IN(10,20);
# 收入求最大MAX：
SELECT MAX(sal+IFNULL(comm,0)) FROM t_emp WHERE deptno IN(30,20);
# 收入求最小MIN：
SELECT MIN(sal+IFNULL(comm,0)) AS min FROM t_emp WHERE deptno IN(30,20);
# 姓名求最长MAX：
SELECT MAX(LENGTH(ename)) FROM t_emp;
# 统计数量count,如果参数是*，则统计所有，如果是指定列，则统计非null列
SELECT COUNT(*) FROM t_emp;
SELECT COUNT(comm) FROM t_emp;

# 查询10和20部门中，底薪超过2000元并且工龄超过15年的员工人数
SELECT COUNT(*) FROM t_emp
WHERE deptno IN(10,20) AND sal>=2000
AND DATEDIFF(NOW(),hiredate)/365>=15;

  • 分组查询

    # 如果含有group by子句时，select子句中的内容只能含有聚合函数，或者group by子句的分组列
# ROUND(X)对X进行四舍五入，GROUP BY按照部门和工作进行分组，每组的总数和平均工资：
SELECT deptno,job,COUNT(*),AVG(sal)
FROM t_emp 
GROUP BY deptno,job;

# WITH ROLLUP对分组结果集再次做汇总计算：
SELECT deptno,COUNT(*),SUM(sal),AVG(sal),MAX(sal),MIN(sal)
FROM t_emp 
GROUP BY deptno WITH ROLLUP;

# GROUP_CONCAT(ename)把符合下面条件的用户的姓名列表打印出来
SELECT deptno,COUNT(*),GROUP_CONCAT(ename)
FROM t_emp
WHERE sal>=2000
GROUP BY deptno;

  • HAVING子句

    # HAVING和where的效果差不多，都是用作条件筛选，只是HAVING语句必须依赖于GROUP BY之后执行
SELECT deptno
FROM t_emp
GROUP BY deptno HAVING AVG(sal)>=2000;


# HAVING和WHERE的区别
# where和having都可以使用的场景：
select goods_price,goods_name from sw_goods where goods_price>100
select goods_price,goods_name from sw_goods having goods_price>100
# 原因：goods_price作为条件也出现在了查询字段中。


# 只可以使用where，不可以使用having的情况：
select goods_name,goods_number from sw_goods where goods_price>100
select goods_name,goods_number from sw_goods having goods_price>100		# (报错)
# 原因：goods_price作为筛选条件没有出现在查询字段中，所以就会报错。
# having的原理是先select 然后从select出来的进行筛选。而where是先筛选再select。


# 只可以使用having，不可以使用where的情况：
select goods_category_id,avg(good_price) as ag from sw_goods group by goods_category having ag>1000
select goods_category_id,avg(good_price) as ag from sw_goods where ag>1000 group by goods_category # (X)报错，这个表里没有这个ag这个字段。
# where子句中一般不使用聚合函数那种情况，只包含表中的字段

/* 
区别1：
	where是从数据表中的字段直接进行的筛选的。
	having是从前面筛选的字段再筛选
区别2：
	having子句中可以使用字段别名，而where不能使用
区别3:
	having能够使用统计函数，先分组，再判断(having)，但是where不能使用

如果你对何时应该使用WHERE，何时使用HAVING仍旧很迷惑，请遵照下面的说明：   
　　WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。   
　　HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。
*/

# COUNT参数用法：
# 如果数据表没有主键，那么count(1)的效率>count(*)
# 如果有主键，那么count(主键字段）的效率>count(*)
# 如果只有一个字段，那么count(*)最快

  • 表的内连接

    内外连接区别：https://www.cnblogs.com/smallVampire/p/12017880.html

    /* 内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现 
内连接3种格式：
#1. SELECT ...FROM 表1 JOIN 表2  ON 	连接条件;  	# 推荐用1，有ON比较标志性
#2.	SELECT ...FROM 表1 JOIN 表2 WHERE 连接条件;
#3. SELECT ...FROM 表1	  ,	 表2 WHERE 连接条件;
*/
# 1
SELECT e.empno,e.ename,d.dname,e.deptno,d.deptno
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;

# 2
SELECT e.empno,e.ename,d.dname,e.deptno,d.deptno
FROM t_emp e JOIN t_dept d WHERE e.deptno=d.deptno;

# 3
SELECT e.empno,e.ename,d.dname,e.deptno,d.deptno
FROM t_emp e,t_dept d WHERE e.deptno=d.deptno;

# example1：查询每个员工的工号，姓名，部门名称，底薪，职位，工资等级
# !!!!!用where的时候必须是最后一个表的，比如：
# 在这里如果t_dept就用where，会报错，where要在最后一个表，因此最好用ON
SELECT e.empno,e.ename,d.dname,e.sal,e.job,s.grade
FROM t_emp e
JOIN t_dept d ON e.deptno=d.deptno
JOIN t_salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

# example2: 查询与SCOTT相同部门的员工都有谁
# 1.效率低，因为有子查询：先找出SCOTT的部门编号，然后去找和他同部门的
SELECT ename
FROM t_emp
WHERE deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT")
AND ename!="SCOTT"

# 2.效率高，没有子查询，使用join，e1是SCOTT，相同数据表也可以做表连接
SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT";

# example3: 查询底薪超过公司平均底薪的员工信息
SELECT e.empno,e.ename,e.sal
FROM t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t
ON e.sal>=t.avg;

# example4: 查询RESEARCH部门的人数，最高底薪，最低底薪，平均底薪，平均工龄
# 1.先设好表2的条件，再插入表，FLOOR是去除小数点后取整
SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),FLOOR(AVG(DATEDIFF(NOW(),e.hiredate)/365))
FROM t_emp e JOIN (SELECT deptno FROM t_dept WHERE dname="RESEARCH") t
ON e.deptno=t.deptno;

# 2.先插好表2，再设置条件
SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),FLOOR(AVG(DATEDIFF(NOW(),e.hiredate)/365))
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="RESEARCH";

# example5: 查询每种职业的最高，最低，平均工资和最高，最低工资等级
SELECT
e.job,
MAX(e.sal+IFNULL(e.comm,0)),
MIN(e.sal+IFNULL(e.comm,0)),
AVG(e.sal+IFNULL(e.comm,0)),
MAX(s.grade),
MIN(s.grade)
FROM t_emp e JOIN t_salgrade s
ON (e.sal+IFNULL(e.comm,0)) BETWEEN s.losal AND s.hisal
GROUP BY e.job;

# example6: 查询每个底薪超过部门平均底薪的员工信息
SELECT	e1.ename,e1.deptno,e1.sal
FROM t_emp e1 JOIN (SELECT deptno,AVG(sal) avg FROM t_emp GROUP BY deptno) e2
ON e1.sal>=e2.avg AND e1.deptno=e2.deptno;

  • 表的外连接

    /*外连接: 包括 
	（1）左外连接(左边的表不加限制) 
	（2）右外连接(右边的表不加限制) 
	（3）全外连接(左右两表都不加限制) 
总之：
	左连接显示左边全部的和右边与左边相同的 
	右连接显示右边全部的和左边与右边相同的 
	内连接是只显示满足条件的!

*/

# 查询每个部门的名称和部门的人数,把左边的表加到右边，没有的数据左边用null替代
SELECT d.dname,COUNT(e.deptno)
FROM t_emp e RIGHT JOIN t_dept d ON e.deptno=d.deptno
GROUP BY d.deptno 

# Example1: 查询每个部门的名称和部门的人数,并且没有部门的人要加一个null部门
# UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
# 请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同
(SELECT d.dname,COUNT(*)
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
GROUP BY d.deptno)
UNION 
(SELECT d.dname,COUNT(e.deptno)
FROM t_emp e RIGHT JOIN t_dept d ON e.deptno=d.deptno
GROUP BY d.deptno) 

# Example2: 查询每名员工的编号，姓名，部门，月薪，工资等级，工龄，上司编号，上司名字，上司部门
SELECT	
	e.empno,e.ename,d.dname,
	e.sal+IFNULL(e.comm,0) AS sal,s.grade,
	FLOOR(DATEDIFF(NOW(),e.hiredate)/365) AS avg,
	t.mgrno,t.mgrname,t.mgrdname
FROM t_emp e 
LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN (SELECT e1.empno AS mgrno,e1.ename AS mgrname,d1.dname AS mgrdname 
FROM t_emp e1 JOIN t_dept d1 ON e1.deptno=d1.deptno) t ON t.mgrno=e.mgr

# 注意：内连接只保留符合条件的记录，因此ON和WHERE在内连接的效果是一样的，但是在外连接不同
# 1.条件写在ON子句里，会保留所有数据，包含null的
SELECT e.ename,d.dname,d.deptno
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno=d.deptno 
AND e.deptno=10

# 2.条件写在WHERE子句里，不符合条件的会被过滤
SELECT e.ename,d.dname,d.deptno
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno=d.deptno 
WHERE e.deptno=10

# 3.内连接，两个效果都和2的一样
SELECT e.ename,d.dname,d.deptno
FROM t_emp e
JOIN t_dept d ON e.deptno=d.deptno 
AND(WHERE) e.deptno=10

  • 子查询

    /* ****推荐使用FROM里的子查询****
子查询可以写在三个地方：WHERE子句，FROM子句，SELECT子句，但是在WHERE和SELECT里的效率特别低，每次都要执行
但是在FROM里可取，因为FROM里是用于表的连接，只需要连一次，效率没问题*/

# 查询工资高于平均工资的
# 1.WHERE子查询，有多少条数据，就执行多少次
SELECT e.empno,e.ename,e.sal
FROM t_emp e
WHERE e.sal>=(SELECT AVG(sal) FROM t_emp)

# 2.FROM子句查询，只有在连接表执行一次，效率大大的高
SELECT e.empno,e.ename,e.sal,t.avg
FROM t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t 
ON(WHERE) e.sal>=t.avg
MySQL的基本操作

  • 数据插入

    /* 写入数据，INSERT语句
1.写入单条数据到表
INSERT INTO 表名(字段1,字段2...)
VALUES (值1,值2...);
2.写入多条数据到表
INSERT INTO 表名(字段1,字段2...)
VALUES (值1,值2...), (值1,值2...), (值1,值2...);
*/

# 1. 向部门表插入数据
INSERT INTO t_dept(deptno,dname,loc)
VALUES (60,"技术部","北京"),(50,"财务部","上海");

# 2. 向技术部添加一条员工记录,可以在VALUES里用子查询(返回到数据必须是单行单列的)
INSERT INTO t_emp
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (7999,"李娜","CLERK","7369","1982-02-12",2400,NULL,
(SELECT deptno FROM t_dept WHERE dname="财务部"));

# MySQL的INSERT语句的方言语法，只支持mysql,很方便但是不推荐
INSERT INTO t_emp
SET empno=8002,ename="jack",job="salesman",mgr=8001,
hiredate="1995-02-03",sal=3000,comm=NULL,deptno=30

# IGNORE关键字会让INSERT只插入数据库不存在的记录，下面的40已经存在，则跳过40
INSERT IGNORE INTO t_dept (deptno,dname,loc)
VALUES(40,"hhh","sad"),(80,"软件部","深圳");

  • 数据更改

    /*UPDATE [IGGNORE] 表名
SET 字段1=值1,字段2=值2,...
[WHERE 条件1 ...] 		 # 要修改的范围
[ORDER BY ...]				# 对记录先排序，再修改
[LIMIT ...];					# LIMIT 几就是前几条数据，只能写一个参数
*/

# 将每个员工的编号和上司编号+1，用ORDER BY子句完成
UPDATE t_emp SET empno=empno+1,mgr=mgr+1
ORDER BY empno DESC;

# 将月收入前三名的员工底薪减100元，用LIMIT子句完成
UPDATE t_emp SET sal=sal-100
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 3;
 
# 把ALLEN调往RESEARCH部门，职位调整为ANALYST
# 可以表连接两个表，对两个表做操作，同时表连接可以不写条件
# update可以做表连接，格式和select一样，可以
# update 表1，表2 set... where
UPDATE t_emp e JOIN t_dept d
SET e.deptno=d.deptno,e.job="ANALYST",d.loc="北京"
WHERE e.ename="ALLEN" AND d.dname="RESEARCH";

# 把底薪低于公司平均底薪的员工，底薪增加150元
# 1.也是表连接
UPDATE t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t
SET	e.sal=e.sal-150
WHERE e.sal>=t.avg
# 2.也是表连接
UPDATE t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t
ON e.sal<=t.avg
SET	e.sal=e.sal+150

# 把没有部门的或者SALES部门里底薪低于2000的人分到部门20
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
SET e.deptno=20
WHERE e.deptno IS NULL OR (d.dname="SALES" AND e.sal<=2000);

  • 数据删除

    /*DELETE [IGGNORE] FROM 表名
[WHERE 条件1,条件2, ...]	 # 要删除的范围
[ORDER BY ...]						# 对记录先排序，再删除
[LIMIT ...];							# LIMIT 几就是前几条数据，只能写一个参数
*/
# 删除10部门中，工龄超过20年员工的记录
DELETE FROM t_emp
WHERE deptno=10 AND (DATEDIFF(NOW(),hiredate)/365)>=20

# 删除20部门中工资最高的员工记录
DELETE FROM t_emp
WHERE deptno=20
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 1

# delete 表连接
# delete 表1,表2... from 表1 join 表2 on 条件
# 删除SALES部门和该部门的全部员工记录
# 1
DELETE e, d FROM t_emp e, t_dept d 
WHERE d.dname="SALES" AND e.deptno=d.deptno
# 2
DELETE e, d FROM t_emp e JOIN t_dept d 
ON e.deptno=d.deptno
WHERE d.dname="SALES"
# 3
DELETE e, d FROM t_emp e JOIN t_dept d 
ON e.deptno=d.deptno AND d.dname="SALES"

# 删除每个低于部门平均底薪的员工记录
DELETE e FROM t_emp e 
JOIN (SELECT AVG(sal) avg, deptno FROM t_emp GROUP BY deptno) t 
ON e.sal <= t.avg AND e.deptno=t.deptno

# 删除员工KING和他直接下属的员工记录，用表连接实现,where/on都可以
DELETE e FROM t_emp e JOIN 
(SELECT empno FROM t_emp WHERE ename="KING") t
WHERE e.ename="KING" OR e.mgr=t.empno;
# ON e.empno=t.empno OR e.mgr=t.empno;

# 删除SALES部门的员工，以及没有部门的员工
DELETE e 
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="SALES" OR e.deptno IS NULL;

# TRUNCATE快速清空表，速度远超delete
# TRUNCATE TABLE 表名;
TRUNCATE TABLE t_emp;

  • MySQL删除语句小结

    # DROP语句	删除数据库和表
drop 数据库名;
drop 表名;

# DELETE语句	删除表中记录
delete from weibo_user where username="xiaomu";
# 注意：如果delete语句中没有where条件，将会把表中的所有记录全部删除

# update set语句	修改和更新语句，更新时也会覆盖（删除）原来的值
update set 与 where 搭配使用，变更某些记录
update 表1，表2 set ... where ...;

# alter语句	
删除字段： alter table 表名 drop 字段名;
删除主键： alter table 表名 drop primary key;
更新表名：	alter table 表名 rename to 新表名;
MySQL的基本函数
  • 基本函数：https://blog.youkuaiyun.com/qq_33730348/article/details/79865553
事务管理

  • START TRANSACTION启用事务管理

  • https://www.cnblogs.com/lebronqjh/p/12191117.html

    # 事务指令
START TRANSACTION;
SQL语句;
[COMMIT|ROLLBACK];

# 开启事务管理，此时相当于有个临时变量
START TRANSACTION;
DELETE FROM t_emp;
DELETE FROM t_dept;
# 此时去查，都是空，但是实际上两个表的内容都还在
SELECT * FROM t_emp;
SELECT * FROM t_dept;

# 1.如果此时用commit，当commit之后，就是同步到了实际表中，这是就是真的删除了
COMMIT;
# 2.如果此时用rollback，当rollback之后，就还原之前到数据，不会有任何修改
ROLLBACK;

# READ UNCOMMITTED事务隔离级别，设置后可以查看其他事务未提交的改动
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT empno,ename,sal FROM t_emp;
COMMIT;

# READ COMMITTED事务隔离级别，设置后可以查看其他事务已提交的改动
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT empno,ename,sal FROM t_emp;
COMMIT;

# REPEATABLE READ事务隔离级别，同一个事务先后查询结果一样
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
# 另一个事务修改前
SELECT empno,ename,sal FROM t_emp;
# 另一个事务修改后
SELECT empno,ename,sal FROM t_emp;
COMMIT;

# SERIALIZABLE事务隔离级别，设置后可以查看其他事务已提交的改动
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
# SERIALIZABLE会阻塞，等待其他事务处理完再处理，相当于把并发变成了单发
SELECT empno,ename,sal FROM t_emp;
COMMIT;


# 两个函数的第二个参数都是加密的密钥，密钥不对也没法解密
# 加密，2进制结果为èZKaB§7^SÀT\­Hî
SELECT AES_ENCRYPT("你好世界","ABC123456");

# 加密，16进制结果为E85A104B6142A7375E53C0545CAD48EE
SELECT HEX(AES_ENCRYPT("你好世界","ABC123456"));

# 解密，先用UNHEX转回2进制，然后用AES_DECRYPT解密，结果为：你好世界
SELECT AES_DECRYPT(UNHEX("E85A104B6142A7375E53C0545CAD48EE"),"ABC123456");
MySQL数据库基本操作
Drw_Dcm的博客
09-09 5万+
MySQL数据库基本操作
linux操作mysql基本操作
qq_41480794的博客
03-31 2412
1.查看安装的MySQL ：rpm-qa|grepmysql 2.查看安装的mariadb：rpm-qa|grepmariadb 3.卸载：rpm-e--nodeps 【加上刚刚rpm-qa|grepmysql查询出来的结果或者rpm-qa|grepmariadb查询的结果】 4.查看磁盘安装在磁盘大的里面 ：df -Th 5.检查mysql用户组和用户是否存在，如果没有，则创建 cat /etc/group | grep mysql cat /etc/pa...
MySQL——操作
2303_78442132的博客
12-04 1973
执行删除之后的结果数据库内部看不到对应的数据库对应的数据库文件夹被删除，级联删除，里面的数据表全部被删。对数据库的修改主要指的是修改数据库的字符集，校验规则。查看创建数据库的详细信息能够查看当时创建该数据库时所编写的语句的信息。
mysql基本操作
lenkee的博客
04-11 2228
mysql基本操作 1、登录数据库：mysql -u root -p，然后输入密码 2、数据库退出：exit；    查看数据库版本：SELECT VERSION();    显示当前用户：SELECT USER(); 3、查看数据库列表：SHOW DATABASES;    删除数据库：DROP DATABASE 4、连接数据库：USE TB_NAME
超详细的MySQL基本操作
m0_67401228的博客
07-30 6269
数据库，就是一类专门负责管理（增删查改）数据的软件数据库大体可以分为关系型数据库和非关系型数据库关系型数据库（RDBMS）是指采用了关系模型来组织数据的数据库。简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。基于标准的SQL，只是内部一些实现有区别。非关系型数据库不规定基于SQL实现。现在更多是指NoSQL数据库。CPU存储器输入设备输出设备而存储器用来存储数据的设备，它分为内存和外存。...
MySQL基本操作和基于MySQL基本操作的综合实例项目
m0_47419053的博客
07-28 1772
本文主要介绍了数据库的创建和删除的SQL语句CREATEDATABASEdatabase_name和使读者可以很快掌握MySQL数据库中关于创建和删除数据库的基本使用方法。另外还介绍了分别使用cmd环境创建、删除数据库和Navicat环境中创建、删除数据库的使用方法。httpshttpshttpshttpshttps。......
mysql操作 begin 命令_MySQL基础操作
热门推荐
weixin_29505425的博客
02-08 1万+
MySQL1、连接数据量mysql -u username -p输入命令后回车，显示Enter password: 输入密码即可。username登录时使用的用户名2、创建数据库create database databaseNamedatabaseName:需要创建的数据库名。3、删除数据库drop database databaseNamedatabaseName:需要删除的数据库名。4...
MySQL基本操作（命令行方式）
lingtouyang的博客
01-09 3798
MySQL基本操作（命令行方式）1、登录MySQL2、MySQL 创建数据库3、MySQL 删除数据库4、MySQL选择数据库5、MySQL 创建数据表6、 MySQL 删除数据表7、MySQL 插入数据8、MySQL 查询数据9、MySQL WHERE 子句10、MySQL UPDATE 查询11、 MySQL DELETE 语句13、MySQL UNION 操作符15、MySQL GROUP ...
MySQL 基本操作命令
PILIpilipala的博客
02-06 4342
MySQL一、启动与退出二、数据库操作 一、启动与退出 1、启动 MySQL 服务 net start MySQL 服务名 2、关闭 MySQL 服务 net stop MySQL 服务名 3、登录 MySQL 方式一： 启动 MySQL Command Line Client（MySQL 的 DOS 界面），直接输入安装时 的密码即可。此时的提示符是：mysql> 方式二： 打开 DOS 窗口，然后进入目录 E:\MySQL\mysql-8.0.15-winx64\bin 输入如下命令： mysql
MySQL基础操作
12-14
文章目录配置操作连接`MySQL`添加新用户修改用户密码修改用户的权限查看用户的权限删除用户数据库操作创建数据库修改数据库删除数据库数据引擎查看`mysql`已经提供什么引擎查看`mysql`当前默认引擎修改数据库临时的...
mysql 基本操作
12-15
在window下，启动、停止mysql服务 启动mysql数据库 net start mysql 停止mysql数据库 net stop mysql 重新启动mysql数据库 net restart mysql 命令行形式，mysql基本命令的使用 1、命令的取消 \c 2、退出mysql窗口 ...
MySql 教程-mysql 基本操作.pdf
07-31
MySQL 基本操作 MySQL 是一种关系型数据库管理系统，广泛应用于 Web 应用程序和企业级应用系统中。本文将介绍 MySQL 的基本操作，包括连接数据库、基本命令语法、常用命令等。 一、连接数据库 连接 MySQL 数据库...
mysql基础操作
09-26
本文将详细介绍MySQL的基础操作，包括服务管理、数据库操作以及表操作。 一、MySQL服务管理 1. 启动服务：在Windows系统中，你可以通过命令行以管理员身份运行`net start 服务名称`来启动MySQL服务，例如`...
MySQL 派生表查询导致 Crash 的根源分析与解决方案
GreatSQL2021的博客
05-23 712
MySQL 8.0.32 派生表查询致 Crash？本文深度剖析根源，揭秘修复代码，复杂 SQL 问题应对有法，速来一探究竟！
MySQL强化关键_015_存储过程
lu_shao_feng的博客
05-23 1140
MySQL强化关键内容之存储过程解读。
canal实现mysql数据同步
那些乐趣的博客
05-23 1881
1、canal下载进入页面可以选择需要安装的版本下载canal.deployer-1.1.8.tar.gz和canal.admin-1.1.8.tar.gz2、mysql同步用户创建和授权在canal-admin解压文件的conf中有一个canal_manager.sql，导入到master数据库3、canal admin安装和启动把canal.admin-1.1.8.tar.gz上传到linux解压 tar -zvxf canal.admin-1.1.8.tar.gz。
浅谈Mysql的MVCC机制（RC与RR隔离级别）
最新发布
weixin_44826783的博客
05-27 850
继续向前找，100,100 不在活跃的事务ID列表里面，100也不等于创建当前ReadView的事务ID300，100比最小事务ID还小，可以访问，所以RC级别下读到的结果就是小红，那么如果是RR级别下读到的是什么呢？读已提交不会造成脏读，从名字来看就是已提交，就是解决读未提交产生的问题，它不会产生脏读，但是它会产生不可重复读，不可重复读，指的就是一个事务一直读取同一条记录，但是会被其他事务的更新操作影响到读取的结果，造成数据不一致。从第一个版本看，200，在活跃的事务ID列表里面，不可见，
K8S-statefulset-mysql-ha
demon7552003的小本本
05-24 564
本文介绍了在Kubernetes上部署MySQL高可用集群的方案
Linux环境下MySQL基础操作指南
"这篇文档介绍了在Linux环境下操作MySQL的基本命令，包括创建数据库管理用户、登录MySQL以及在数据库中执行命令的方法。" 在Linux系统中，MySQL数据库是常用的数据存储和管理工具。对于初学者，掌握一些基本的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值