mysql基础

mysql通用语法:
SQL分类
SHOW CREATE DATABASE test;
MySQL会返回一个结果集，其中包含创建test数据库时使用的SQL语句。在这个语句中，你可以找到字符集的定义，通常类似于CHARACTER SET utf8mb4。
数据类型
表操作
添加字段:

修改数据类型和修改字段名和字段类型:
删除表字段
修改表名
删除表和删除表并重新创建该表
阶段总结
表内添加数据

其中批量添加数据为一次可以添加多条数据,比如:
修改数据

where可以指定修改条件,如下:

 update tb_user set `password`=1234 WHERE username= 'df';
update tb_user SET username="李连杰" WHERE `password`=1234;

如果不加where条件,那么将修改表中该字段所有的值.

删除数据

比如:删除username为李连杰的数据行
delete FROM tb_user where username='李连杰';
查询多个字段
设置别名

比如,表中id字段显示为编号的别名
去除重复记录

比如:将answer字段中答案重复的去除.
select DISTINCT answer from tb_user;
DQL条件查询
查询question字段为null的行,也就是该字段没有填写问题
select * from tb_user WHERE question is null;
当字段内某个值被"设置为NULL",查询用is null,不能用
select * from tb_user WHERE question == null;
查询question字段不为null的信息
select * from tb_user where question is not null;
!=和<>表示不等于,查询id不等于1的行
查询id在10到13之间的行信息

还可以用between and表示:
SELECT * from tb_user where id between 10 and 13;
查询id小于15并且性别为女的信息
select * from tb_user where sex = '女' and ID < 15;
或查询

还可以用in关键字
select * from tb_user where id in (2,5);
Like占位符,_匹配单个字符,查询username为两个字的
select * from tb_user where username like '__';
select * from tb_user where username like '__';
Like占位符,%匹配任意个字符
查询username后面带x的
聚合函数
统计表中有多少行
select count(*) from tb_user;
在question(字段)列中有null值,因为count函数不包括null值,查询question字段有多少行:select count(question) from tb_user;
avg计算平均年龄
select avg(age) from emp;
max计算最大值,比如求表中的最大值.相反,min()是求最小值
SELECT max(id) from tb_user;
计算sex为男的id之和
select sum(id) from tb_user WHERE sex='男';
分组查询
根据性别分组,分别统计男和女的数量:
根据性别性别分组,分别统计男和女的平均年龄.
查询ID小于10并且email字段(列)众相同值大于等于3的值
SELECT email, count(*) from tb_user where id < 10 GROUP BY email HAVING count(*)>=3;
asc升序,desc降序

分页查询

比如:从索引1开始,每页查1行数据.
select * from tb_user limit 1,1;
补充:
公式：起始索引 = (查询页码 - 1) * 每页显示记录数
对于上面的例子，查询页码是3，每页显示记录数是10，所以：
起始索引 = (3 - 1) * 10 = 20
这意味着你想从第20条记录开始获取数据，并获取10条记录。
在MySQL查询中，这可以写作：
sql
SELECT * FROM your_table_name
LIMIT 20, 10;
这里，20是起始索引，10是每页要显示的记录数。
DQL练习

如果有多个and条件,可以加括号(),比如:
练习2
练习3
练习4

感谢黑马程序员的演示.
DQL执行顺序

测试案例

其中emp别名为e,name字段别名为ename, age字段别名为eage
DQL小结

DCL(Data Control Language)数据控制语言,管理数据库用户、控制访问权限等.

查询有超级权限的用户
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';
修改用户密码
alter user itcast@localhost IDENTIFIED with caching_sha2_password BY '*********';或
alter user itcast@localhost IDENTIFIED with mysql_native_password BY '*********';
在MySQL中，如果不使用 WITH 子句明确指定密码认证插件，那么通常会使用MySQL服务器配置文件中指定的默认认证插件。这个默认插件可以在MySQL配置文件（通常是my.cnf或my.ini）中的[mysqld]部分通过default_authentication_plugin选项来设置。
从MySQL 8.0开始，默认的认证插件是caching_sha2_password。这是MySQL推荐的最新和最安全的插件，它使用SHA-2散列算法来存储密码，并且支持密码缓存来提高性能。
因此，如果你在执行ALTER USER语句时不指定WITH子句和认证插件，例如：
sql
ALTER USER 'itcast'@'localhost' IDENTIFIED BY '新密码';
那么MySQL将会使用default_authentication_plugin配置选项所指定的认证插件来设置或更新用户的密码。如果配置文件中没有明确指定default_authentication_plugin，则MySQL将使用caching_sha2_password作为默认插件（在MySQL 8.0及更高版本中）。
请注意，如果你的客户端或连接库不支持caching_sha2_password，你可能需要在创建或修改用户时显式指定mysql_native_password插件，或者更新你的客户端和连接库以支持新的认证插件。此外，管理员也可以在服务器级别或全局级别更改默认认证插件，但这通常是在配置MySQL服务器时由系统管理员进行的操作。
删除用户
drop user 'itcast'@'localhost';
DCl常用权限

案例:给test数据库中的所有表授权所有权限,然后查看,然后再取消授予的所有权限.
grant all on test.* to 'heima'@'%';
show grants for 'heima'@'%';
revoke all on test.* from 'heima'@'%';

函数

函数是指一段可以直接被另一段程序调用的程序或代码.

concat演示:

lpad演示:

rpad演示:

trim演示:

subString演示
小练习:
update tb_user set password = lpad(password,3,'1');
修改password字段,原先密码10,执行上面命令后,password字段变为110,也就是显示3个字段,原先有2个字段,不够的用1填充.
数值函数

ceil向上取整:

floor向下取整:

mod(x,y)取模运算:

rand():

round()
总结案例:

因为RAND()是返回0~1内的随机数,如果返回的是0.050383837940528836,那么改数乘以1000000将会是5位数,要求生产一个六位数的随机验证码,所以需要使用lpad填充0。

日期函数

now()返回当前的日期和时间(时分秒).
year()返回指定date的年份:
month()返回指定日期的月份:
day()返回指定时间的具体日期是几号
date_add演示:
select date_add(now(), interval 70 day);从当前时间开始算,往后推算70天后的时间,70后面可以是day、month、year
datediff演示
select DATEDIFF('2025.12.1','2024.12.1')计算2025.12.1和2024.12.1间隔的天数,其中日期需要用引号括起来,日期内的点也可以用-替换.如果前面日期小,后面日期大,结果将为负数.
综合案例:

补充entrydate字段

流程函数

ifnull,只有第一个参数是null,才会返回字符串Error.
select ifnull(null,"Error");
case [expr] when演示
case when...演示:

约束

按照需求创建表

 create table user(
	id int primary key auto_increment COMMENT '主键',
	name VARCHAR(10) not null UNIQUE COMMENT '姓名',
	age int check(age>0 && age<=120) COMMENT '年龄',
	status char(1) DEFAULT '1' COMMENT '状态',
	gender char(1) COMMENT '性别'
) COMMENT '用户表';

其中unique约束作为索引使用.

添加外键
往emp表内插入数据:

 insert into emp(id, name, age, job, salary, entrydate, manager_id, dept_id)
VALUES (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
       (2,'张无忌',20,'项目经理',12500,'2005-12-05',2,1),
       (3,'杨逍',33,'开发',8400,'2000-11-03',2,1),
       (4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
       (5,'常遇春',43,'开发',10500,'2004-09-07',3,1),
       (6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);

语法:

alter table emp add CONSTRAINT fk_emp_dept_id FOREIGN key (dept_id) REFERENCES dept(id);
其中fk_emp_dept_id为外键名称,也就是将emp表的dept_id设置为外键.

删除外键
alter table emp drop foreign key fk_emp_dept_id;

补充,在navicat中,在父表中删除了一行记录,但是没有提交时,执行上述删除外键命令后一直在等待,无法删除外键,只有点击父表中的提交按钮后才能删除外键成功.
外键删除/更新行为

cascade演示:
alter table emp add CONSTRAINT fk_emp_dept_id FOREIGN key (dept_id) REFERENCES dept(id) on UPDATE cascade on delete cascade;
补充:如果之前已经fk_emp_dept外键,现在想修改成cascade(默认为NO ACTION),需要删除外键fk_emp_dept,重新执行上述命令.
SET NULL演示:
alter table emp add CONSTRAINT fk_emp_dept_id FOREIGN key (dept_id) REFERENCES dept(id) on UPDATE set null on delete set NULL;

多表查询

一对多
多对多
一对一

select * from emp,dept where dept_id = dept.id;,其中,dept_id为外键.

多表查询分类

内连接查询语法:

select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
第二行为使用别名.

显示内连接演示:

select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
别名:
select e.name, d.name from emp e INNER JOIN dept d on dept_id=d.id;
其中INNER关键字是可以省略的.
外连接
左外链接:

不带别名的操作命令:select emp.*,dept.name from emp left JOIN dept on dept_id=dept.id
右外连接:
select e.*,d.* from emp e right JOIN dept d on e.dept_id=d.id

查出右表中的所有数据,截图中有nll行,是因为emp员工表中没有对应dept表中的id,但是由于是右外查询,所以还是显示出前面是null后面是dept表的信息.
当然上面的右外连接实现的效果,改成左外链接也能实现:
select e.*,d.* from dept d left JOIN emp e on e.dept_id=d.id
自连接

案例2:

操作结果图:

联合查询

案例演示:即查薪资大于10000的又查年龄大于50岁的.

子查询

标量子查询:
case1. 查询"销售部"的所有员工信息

case2. 查询在"周润发"入职之后的员工信息
列子查询

case1. 查询"销售部"和"市场部"的所有员工员工信息

case2. 查询在emp(员工)表中查询比财务部中任何人工资都高的员工信息.

case3. 查询比研发部其中任意一人工资高的员工信息.

行子查询

case1. 查询与"张无忌"的薪资及直属领导都相同的员工信息
表子查询
case1. 查询与"成龙"、"韦一笑"的职务和薪资一样的员工

select * from emp where (job,salary) in (select job,salary from emp where name in('成龙','韦一笑'));
通过第一个命令行查询,返回的是多行的数据,所以where (job,salary)后面需要用in关键字

case2. 查询入职日期是"2002-02-05"之后的员工信息及部门信息

SELECT * from (select * from emp where entrydate > '2002-02-05') e left join dept d on e.dept_id=d.id
练习:

查询员工的姓名、年龄、职位、部门信息
#隐式内连接 select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id = d.id;
#显式外连接 select e.name,e.age,e.job,d.name from emp e join dept d on e.dept_id = d.id;
查询年龄小于30岁的员工信息
select e.name,e.age,e.job,d.name from emp e join dept d on e.dept_id = d.id where e.age <30;;
查询有员工的部门ID和部门名称
select DISTINCT d.id,d.name from emp e,dept d where e.dept_id=d.id其中distinct是去重关键字.
查询所有年龄大于40岁的员工及归属部门名称;如果员工没有分配部门,也需要显示出来
select e.*,d.name from emp e left join dept d on e.dept_id=d.id where e.age>40;
查询所有员工的工资等级

select e.*,s.* from emp e,salgrade s where e.salary>=s.losal and e.salary <= s.hisal;
还以用between and表示:
select e.*,s.* from emp e,salgrade s where e.salary between s.losal and s.hisal;
查询"研发部"所有员工的信息及工资等级

 SELECT
	e.*,
	s.grade,
    d.name 
FROM
	emp e,
	dept d,
	salgrade s 
WHERE
	e.dept_id = d.id 
	AND ( e.salary BETWEEN s.losal AND s.hisal ) 
	AND ( d.NAME = '研发部' );

查询研发部的平均工资
select avg(e.salary) from emp e,dept d where e.dept_id = d.id and d.name="研发部";
查询比"杨逍"工资高的员工数量
查询比平均薪资高的员工信息

 select avg(salary) from emp ;
#利用标量子查询
select * from emp where salary > (select avg(salary) from emp )

查询低于本部门平均工资的员工信息

 #如果求已知的部门的ID,这里用2,此时查询的是大于部门2平均工资的员工信息
select * from emp where salary < (select avg(salary) from emp where dept_id = 2)
#(select avg(salary) from emp where dept_id = e2.dept_id)求的是平均工资,此处使用自连接别名查询
select *,(select avg(salary) from emp where dept_id = e2.dept_id) '平均' from emp e2 where e2.salary < (select avg(salary) from emp where dept_id = e2.dept_id)

查询所有部门信息并统计各部门的员工人数.

 #利用自连接别名查询
select d.id,d.name,(select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;

查询所有学生的选课情况,展示学生的名称,学号,课程情况

 SELECT
	s.NAME,
	s.NO,
	c.NAME 
FROM
	student s,
	student_course sc,
	course c 
WHERE
	s.id = sc.studentid 
	AND c.id = sc.courseid

补充创建student_course表命令行

 CREATE TABLE `student_course` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `studentid` int NOT NULL COMMENT '学生ID',
  `courseid` int NOT NULL COMMENT '课程ID',
  PRIMARY KEY (`id`),
  KEY `fk_courseid` (`courseid`),
  KEY `fk_studentid` (`studentid`),
  CONSTRAINT `fk_courseid` FOREIGN KEY (`courseid`) REFERENCES `course` (`id`),
  CONSTRAINT `fk_studentid` FOREIGN KEY (`studentid`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生课程中间表'

创建student表命令行

 CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `no` varchar(10) DEFAULT NULL COMMENT '学号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'

创建course表命令行

 CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(10) DEFAULT NULL COMMENT '课程名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='课程表'

补充:
创建dept表

 CREATE TABLE `dept` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) NOT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部门表'

创建emp表

 CREATE TABLE `emp` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `job` varchar(20) DEFAULT NULL COMMENT '职位',
  `salary` int DEFAULT NULL COMMENT '薪资',
  `entrydate` date DEFAULT NULL COMMENT '入职时间',
  `manager_id` int DEFAULT NULL COMMENT '直属领导ID',
  `dept_id` int DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept_id` (`dept_id`),
  CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工表'

创建salgrade表

 CREATE TABLE `salgrade` (
  `grade` int DEFAULT NULL,
  `losal` int DEFAULT NULL,
  `hisal` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='薪资等级表'

事务

Mysql默认是自动开启事务的,通过select @@autocommit;查看,通过set @@autocommit =0;手动关闭事务,此时是会话级关闭.
测试表:

选择全部执行以下命令

 select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
程序抛出异常 #故意添加报错代码
update account set money = money + 1000 where name = '李四';

此时,执行报错,如果不手动执行commit提交事务,那么将无法修改account表,可以选择执行rollback回滚.

如果数据库默认自动开启事务,那么我们可以自己通过命令手动开启事务
事务四大特性
并发事务问题,一定是在多个并发事务才发生的,不是一个事务导致的问题
事务隔离级别

posted @ 2024-04-26 01:09 文采杰出阅读(7) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Mysql运维

· Mysql数据库备份及恢复

· MySQL笔记汇总(1)基础篇

· MySQL

· mysql基础

阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了

公告

昵称：文采杰出
园龄： 4年2个月
粉丝： 0
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

文采杰出

mysql基础

DCL(Data Control Language)数据控制语言,管理数据库用户、控制访问权限等.

函数

日期函数

流程函数

约束

多表查询

多表查询分类

联合查询

子查询

事务

公告

搜索

常用链接

随笔分类

随笔档案

阅读排行榜

	update tb_user set `password`=1234 WHERE username= 'df';
	update tb_user SET username="李连杰" WHERE `password`=1234;

	create table user(
	id int primary key auto_increment COMMENT '主键',
	name VARCHAR(10) not null UNIQUE COMMENT '姓名',
	age int check(age>0 && age<=120) COMMENT '年龄',
	status char(1) DEFAULT '1' COMMENT '状态',
	gender char(1) COMMENT '性别'
	) COMMENT '用户表';

	insert into emp(id, name, age, job, salary, entrydate, manager_id, dept_id)
	VALUES (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
	(2,'张无忌',20,'项目经理',12500,'2005-12-05',2,1),
	(3,'杨逍',33,'开发',8400,'2000-11-03',2,1),
	(4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
	(5,'常遇春',43,'开发',10500,'2004-09-07',3,1),
	(6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);

	SELECT
	e.*,
	s.grade,
	d.name
	FROM
	emp e,
	dept d,
	salgrade s
	WHERE
	e.dept_id = d.id
	AND ( e.salary BETWEEN s.losal AND s.hisal )
	AND ( d.NAME = '研发部' );

	select avg(salary) from emp ;
	#利用标量子查询
	select * from emp where salary > (select avg(salary) from emp )

	#如果求已知的部门的ID,这里用2,此时查询的是大于部门2平均工资的员工信息
	select * from emp where salary < (select avg(salary) from emp where dept_id = 2)
	#(select avg(salary) from emp where dept_id = e2.dept_id)求的是平均工资,此处使用自连接别名查询
	select *,(select avg(salary) from emp where dept_id = e2.dept_id) '平均' from emp e2 where e2.salary < (select avg(salary) from emp where dept_id = e2.dept_id)

	#利用自连接别名查询
	select d.id,d.name,(select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;

	SELECT
	s.NAME,
	s.NO,
	c.NAME
	FROM
	student s,
	student_course sc,
	course c
	WHERE
	s.id = sc.studentid
	AND c.id = sc.courseid

	CREATE TABLE `student_course` (
	`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
	`studentid` int NOT NULL COMMENT '学生ID',
	`courseid` int NOT NULL COMMENT '课程ID',
	PRIMARY KEY (`id`),
	KEY `fk_courseid` (`courseid`),
	KEY `fk_studentid` (`studentid`),
	CONSTRAINT `fk_courseid` FOREIGN KEY (`courseid`) REFERENCES `course` (`id`),
	CONSTRAINT `fk_studentid` FOREIGN KEY (`studentid`) REFERENCES `student` (`id`)
	) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生课程中间表'

	CREATE TABLE `student` (
	`id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	`name` varchar(10) DEFAULT NULL COMMENT '姓名',
	`no` varchar(10) DEFAULT NULL COMMENT '学号',
	PRIMARY KEY (`id`)
	) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'

	CREATE TABLE `course` (
	`id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	`name` varchar(10) DEFAULT NULL COMMENT '课程名称',
	PRIMARY KEY (`id`)
	) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='课程表'

	CREATE TABLE `dept` (
	`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`name` varchar(50) NOT NULL COMMENT '部门名称',
	PRIMARY KEY (`id`)
	) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部门表'

	CREATE TABLE `emp` (
	`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`name` varchar(50) NOT NULL COMMENT '姓名',
	`age` int DEFAULT NULL COMMENT '年龄',
	`job` varchar(20) DEFAULT NULL COMMENT '职位',
	`salary` int DEFAULT NULL COMMENT '薪资',
	`entrydate` date DEFAULT NULL COMMENT '入职时间',
	`manager_id` int DEFAULT NULL COMMENT '直属领导ID',
	`dept_id` int DEFAULT NULL COMMENT '部门ID',
	PRIMARY KEY (`id`),
	KEY `fk_emp_dept_id` (`dept_id`),
	CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
	) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工表'

	CREATE TABLE `salgrade` (
	`grade` int DEFAULT NULL,
	`losal` int DEFAULT NULL,
	`hisal` int DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='薪资等级表'

	select * from account where name = '张三';
	update account set money = money - 1000 where name = '张三';
	程序抛出异常 #故意添加报错代码
	update account set money = money + 1000 where name = '李四';