黑马数据库学习笔记

课程地址

（基础篇）MySQL的启动

mysql 默认使用 3306 端口

在 centos下，启动 mysql 数据库：service mysqld start;

查看状态/启动/停止/重启：systemctl status/start/stop/restart mysqld;

登录到mysql数据库：mysql -uroot -psyc13140;

MySQL的层次结构为：用户，数据库，表

关系型数据库：RDBMS，Relational Database Management System。建立在关系模型理论的基础上，由多张连接的二维表组成的数据库

查看数据库和表：

show databases;

use db1;
select database();	# 查询当前数据库
show tables;

MySQL数据类型

SQL语言分类

CRUD：cerate retrive update delete

DDL

数据定义语言：数据库的定义，数据库表的定义，字段的定义

show databases;		-- 查看所有数据库
select database();	-- 显示当前使用的数据库

create database [if not exists] mydb1 [default charset utf8mb4] [collate utf8mb4_general_ci];
alter database mydb1 character set utf8;
drop database [if exists] mydb1;

show tables;
create table emp(
	id int [comment ""],
	name varchar(20) [comment ""],
	sex int [comment ""],
	birthday date [comment ""],
	salary double [comment ""],
	hiredate date [comment ""],
	resume text [comment ""]
) [comment ""];

desc emp;	# describe emp
show create table emp;	# 查看建表过程
drop table emp;

创建表

create table tb_user(
	id int comment "id",
	name varchar(20) comment "name",
	age int comment "age",
	gender varchar(1) comment "gender"
)comment "user table";

执行sql脚本：

source /home/daniel/scott.sql;

创建表练习：

create table emp(
	id int,
	work_no varchar(10),
	name varchar(10),
	gender char(1),
	age tinyint unsigned,
	id_card char(18),
	entry_date date
);

修改表

alter table emp add nickname varchar(20) comment "昵称";
alter table emp modify nickname varchar(25);
alter table emp change nickname username varchar(30);
alter table emp drop username;
alter table emp rename to worker;

删除表

drop table worker;
truncate table worker;	# 删除表，并重新创建该表

小结

# 数据库操作
show databases;
create database db_name;
user db_name;
select database();
drop database db_name;

# 表操作
show tables;
create table tb_name(field1, field2, ...);
desc tb_name;
show create table tb_name;
alter table tb_name add/modify/change/drop/rename to ...;
drop table tb_name;

DML

insert into tb_name(field1, field2, ...) values (value1, value2...);
insert into tb_name values(v1, v2);
# 批量添加数据
insert into tb_name (f1, f2, ...) values (value1, value2...), (value1, value2...), (value1, value2...);
insert into tb_name values (value1, value2...), (value1, value2...), (value1, value2...);

对于这样一张表：

create table emp(
	id int,
	name varchar(20),
	sex int,
	birthday date,
	salary double,
	hiredate date,
	resume text
);

# insert into tb(field1, field2, field3) values(value1, value2, value3);
insert into emp values(1,'daniel',1,'1999-10-19',50000,'2023-10-10','i hope so');
update emp set salary=salary+5000;
delete from emp where name='daniel';
delete from emp;	# 删除所有数据！

如果插入的数据已存在，则使用 ignore 修饰 insert

insert ignore into actor values(3, "ED", "CHASE", "2006-02-15 12:34:33");

DQL

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	字段1 排序方式1, 字段2 排序方式2
LIMIT	# 分页
	[<offset>,] <row count>]

select deptno, dname, loc from dept;
select empno, ename, sal, sal*12 as package from emp;	# as起别名
select distinct city from user_profile;		# 去重

where

select * from emp where sal=800 and deptno=20;	# 查找部门编号为20，薪水为800的员工
select * from emp where deptno in (20, 30);		# 查找部门编号为20或30的员工
select name from emp where idcard is null;

在解析where条件时， 是从右向左解析的，所以应该将容易假的值放在右边，利用逻辑短路特性

like

%：匹配任意多个字符
_：匹配任意一个字符

select * from emp where ename like 'S%';	# 查询名字以S开头的员工
select * from emp where ename like '____';	# 查询名字中有四个字母的员工

group by

# 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
select gender, count(*) from emp group by gender;
select gender, avg(age) from emp group by gender;

# 查询年龄小于45岁的员工，并根据工作地分组，获取员工数量>=3的地址
select workaddress, count(*)
from emp
where age < 45
group by workaddress
having count(*) >= 3;

order by

select * from emp order by age asc;
select * from emp order by entrydate desc;
select * from emp order by age asc, entrydate desc;

limit

select * from article limit 1,3		# 取第2 3 4条数据

DQL练习：

select * from emp where age <= 23 and age >= 20;
select * from emp where gender = "男" and age between 20 and 40 and name like "___";
select gender, count(*) from emp where age < 60 group by gender;
select name, age from emp where age <= 35 order by age, entrydate desc;
select * from emp where gender = "男" and (age between 20 and 40) order by age, entrydate limit 5;

DQL的执行顺序

函数

聚合函数

将一列数据作为一个整体，进行纵向计算。包括：count max min avg sum等

主要应用于 group by 分组操作

select count(*) from emp;	# null不参与聚合函数计算
select avg(age) from emp;

字符串函数

select concat("hello", "mysql");
select lower("Hello");
select upper("Hello");
select lpad("1", 5, "0");
select rpad("1", 5, "0");
select trim("   hello world   ");
select substring("hello, mysql", 1, 5);

-- 使用逗号分隔结果
select dept_no, group_concat(emp_no) as employees
from dept_emp
group by dept_no;

update emp set workno = lpad(workno, 5, "0");

数值函数

select ceil(1.4);
select floor(1.9);
select mod(7, 4);
select rand();	// 生成 (0, 1) 之间的验证码
select round(3.1415926, 3);

通过数据库的函数，生成一个6位数的随机验证码：

select lpad(round(rand() * 1000000, 0), 6, "0");

日期函数

取日期中的年月日：

select curdate();
select curtime();
select now();

select year(now());
select month(now());
select day(now());

select date_add(now, interval 70 day);
select datediff("2021-12-01", "2021-11-01");

查询所有员工的入职天数，并根据入职天数倒序排序：

select name, datediff(now(), entrydate) as days from emp order by days desc;

求时间差：

timestampdiff(MINUTE, start_time, submit_time)

流程控制函数

select