MYSQL基础阶段笔记

最新推荐文章于 2024-09-09 14:47:35 发布
最新推荐文章于 2024-09-09 14:47:35 发布
阅读量282 收藏
点赞数
分类专栏： mysql 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45142130/article/details/126841533
版权

MySQL数据库基本操作

一、初识SQL语句

SQL（Structured Query Language 即结构化查询语言）
SQL语句主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。SQL语言分为3种类型：
DDL语句 数据库定义语言：数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
DML语句 数据库操纵语言：插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
DCL语句 数据库控制语言：例如控制用户的访问权限GRANT、REVOKE

二、系数据库

**information_schema:虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等performance_schema：**主要存储数据库服务器的性能参数
mysql： 授权库，主要存储系统用户的权限信息
sys: 主要存储数据库服务器的性能参数
创建需要的业务主库…

三、创建业务数据库

语法：
CREATE DATABASE 数据库名；
数据库命名规则
区分大小写
唯一性
不能使用关键字如：create select
不能单独使用数字

查看数据库
SHOW DATABASES;

选择数据库
SELECT databases
USE 数据库名

删除数据库
DROP DATABASE 数据库名

MySQL数据库类型

一、MySQL常见的数据类型

在MySQL数据库管理系统中，可以通过存储引擎来决定表的类型。同时，MySQL数据库管理系统也提供了数据类型决定表存储数据的类型。MySQL数据库管理系统提供的数据类型：
数值类型：
整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT
浮点数类型 FLOAT DOUBLE
定点数类型 DEC
位类型 BIT

字符串类型：
CHAR系列 CHAR VARCHAR
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚举类型： ENUM
集合类型： SET

时间和日期类型： DATE TIME DATETIME TIMESTAMP YEAR

二、数据类型测试

整型类型测试：tinyint,int
作用：用于存储用户的年龄、游戏的level、经验值等。
LAB1：

mysql> create table test1(
	 ->tinyint_test tinyint,
	 ->int_test int
	 ->);

tinyint默认数值是4，int默认数值是11，默认有符号，超过数值范围会出错

mysql> insert into test1values (111,111);
Query OK, 1 row affected (0.09 sec)

mysql> insert into test1(tinyint_test) values(128);
ERROR 1264 (22003):Out of range value for column 'tinyint_test' at row 1

mysql> insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.05 sec)

mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1

测试结果，默认有符号，超过存储范围出错

测试整型类型的显示宽度

mysql> create table test2(
	 ->id1 int(8),
	 ->id2 int(4)
	 ->);

mysql> insert into test2 values(3,2147483647);
Query OK, 1 row affected (0.05 sec)

当插入大于宽度限制的值，仍然可以存储
整型宽度仅为显示宽度，不是限制，因此建议整型无需指定宽度

浮点数类型测试

作用：用于存储用户的身高、体重、薪水等
浮点数和定点数都可以用类型名称后加(M，D)的方式来表示，(M，D)表示一共显示M位数字(整数位+小数位)，其中D位于小数点后面，M和D又称为精度和标度。

float和double在不指定精度时，默认会按照实际的精度(由实际的硬件和操作系统决定)来显示，而decimal在不指定精度时，默认的整数位为10，默认的小数位为0

定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。

mysql> create table test4(float_test float(5,2))；//一共5位，小数占2位
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(10.2),(70.125),(70.246);
Query OK, 3 rows affected (0.00 sec)
Records:3 Duplicates:0 Warnings:0

mysql> desc test4;
+--------- - +
| float_test | 
+ ---------- +
| 		10.20| 
|		70.12|
|		70.25|
+ ---------- +
3 rows in set (0.00 sec)

整数位超过了规定的数值会报错，无法添加，小数位超过了规定的数值会通过五舍六入自动计算，不会报错。

位类型测试：BIT

BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位对于位字段可以使用函数读取：
bin()显示为二进制
hex()显示为十六进制

mysql> create table test_bit (id bit(4));
Query OK, 0 rows affected (0.35 sec)

mysql> insert into test_bit values(4);

mysql> select * from test_bit;
+ ------- +
|		id|
+ ------- +
|		  |
+ ------- +
1 row in set (0.00 sec)

mysql> select bin(id),hex(id) from test_bit;
+ ------- + ------- +
| bin(id) |	hex(id) |
+ ------- + ------- +
|	100   | 4       |
+ ------- + ------- +
1 row in set (0.00 sec)

插入的数值不能超过指定宽度的数值，不然会显示警告或者是报错。

时间和日期类型测试： year、date、time、datetime、timestamp
作用： 用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等

mysql> create table test_time(
		-> d date,
		-> t time,
		-> dt datetime);
Query OK, 0 rows affected (0.35 sec)

mysql> insert into test_time values(now(),now(),now());
Query OK, 1 rows affected,1 warning (0.00 sec)

mysql> select * from test_time;
+ ---------- + -------- + ------------------ +
| d			 |	t		|	dt				 |
+ ---------- + -------- + ------------------ +
| 2022-09-12 | 11:45:12 |2022-09-12 11:45:12 |
+ ---------- + -------- + ------------------ +
1 row in set (0.00 sec)

timestamp类型插入的是null值时，显示的是当前时间

注意事项：
其他时间，按要求插入
=插入年份是，尽量使用四位数
=插入两位数年份是，<=69的，以20开头，结果是2069
>=70的，以19开头，结果是1970

字符串类型测试： CHAR、VARCHAR
作用： 用于存储用户的姓名、爱好、发布的文章等
CHAR 列的长度固定为创建表时声明的长度：0~255
VARCHAR 列中的值为可变长字符串，长度：0~65535
注：在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格

mysql> create table vc (
	-> v varchar(4),
	-> c char(4)
	->);
Query OK, 0 rows affected (0.03 sec)

查看空格可以使用

mysql> select concat(v,'='),concat(c,'=') from vc;

或者使用length长度查看

mysql> select length(v),lengtht(c) from vc;

字符串类型
ENUM类型即枚举类型、集合类型SET测试
字段的值只能在给定范围中选择
常见的是单选按钮和复选框
enum 单选 只能在给定的范围内选一个值，如性别sex男male/女femaleset 多选 在给定的范围内可以选择一个或一个以上的值（爱好1，爱好2，爱好3…)表schopl.student3
姓名 name varchar(50)
性别 sex enum(‘m’,'f)
爱好 hobbyset(‘music’, ‘book’,‘game’,‘disc’)

mysql> use school

mysql> create table student3(
	-> name varchar(50),
	->sex enum('m','f),
	->hobby set('music','book','game','disc')
	->);
Ouerv OK.0 rows affected (0.31Sec)

创建出来的内容在添加上新的数据时只能从hobby中所填写的内容选择，若是添加了不属于hobby中的数据，会显示报错。

mysql> insert into student3 values('josk','m','box')；
ERROR 1265 (01000): Data truncated for column  'hobby' at row 1

MySQL表操作 DDL

表是数据库存储数据的基本单位，由若干个字段组成，主要用来存储数据记录。表的操作包括创建表、查看表、修改表和删除表。
这些操作都是数据库管理中最基本，也是最重要的操作。本节内容包括：
创建表 create table
查看表结构 desc table,show create table
表完整性约束
修改表 alter table
复制表 create table…
删除表drop table

一、创建表(表的基本操作)

表school.student1

字段 字段 字段
id name sex age
1 tom male 23 记录
2 jack male 21 记录
3 alice female 19 记录

语法：

create table 表名(
	字段名1 类型[（宽度）约束条件],
	字段名2 类型[（宽度）约束条件],
	字段名3 类型[（宽度）约束条件]
)[存储引擎，字符集];

在同一张表中，字段名是不能相同的
宽度和约束条件可以选择是否添加上
字段名和类型是必须要有的

mysql>create database school;

mysql>use school;

mysql>create table student1(
		->id int,
		->name varchar(50),
		->sex enum('m','f'),
		->age int);
Query OK,0 rows affected (0.09 sec)
  1. 向表中插入内容
    语法：
insert  into 表名（字段1，字段2……）values（字段值列表……）;
  1. 查询表中内容
    语法：
 select * from 表名;  // 查询表中所有字段的值

 select 字段1,字段2…… from 表名;  //查询表中指定字段的值

二、查看表结构

DESCRIBE查看表结构
DESCRIBE 表名;
DESC 表名;
SHOW CREATE TABLE 查看表详细结构
SHOW CREATE TABLE 表名;

三、表完整性约束

作用：用于保证数据的完整性和一致性

约束条件说明
PRIMARY KEY (PK)标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE+NOT NULL
FOREIGN KEY (FK)标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联
NOT NULL标识该字段不能为空（空是没有插入值，并不是空白的意思）
UNIQUE KEY (UK)标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT为该字段设置默认值
UNSIGNED无符号，正数
ZEROFILL使用0填充I，例如0000001

说明：
1.是否允许为空，默认NULL，可设置NOTNULL，字段不允许为空，必须赋值
2.字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

sex enum('male','female') not null default 'male'   //两个默认值，非空和默认male
age int unsigned NOT NULL default 20 //必须为正值（无符号）不允许为空 默认是20

3.是否是key
主键 primary key 区分唯一性的标识
外键forengn key 关联表与表之间的联系，外键是主键的子表
索引（index，unique ……）

not mull 非空
default 设置默认值
添加数据时，若是没有插入数据，会默认插入默认值

mysql>create table student2(
	->id int not null,
	->name varchar(50) not null,
	->sex enum('m','f') default 'm' not null,
	->age int unsigned default 18 not null,
	->hobby set('disc','book','music') default 'disc,book' not null);
Query OK,0 row affected (0.06 sec)

unique 为约束唯一，作为约束值时，插入的表内的数据必须唯一，否则添加不进去，数据报错。

mysql>create table test1(
     ->t_id int,
     ->t_name varchar(50) unique,
     ->t_comment varchar(100));
 Query OK,0 row affected (0.08 sec)
 
 mysql>create table test2(
     ->t_id int,
     ->t_name varchar(50),
     ->t_comment varchar(100)
     ->unique (t_name));
 Query OK,0 row affected (0.06 sec)

设置主键，字段的值是不允许重复，且不允许为空（unique+not null）
单列做主键
多列做主键（复合主键）

mysql>create table test3(
     ->t_id int primary key not null auto_increment,
     ->t_name varchar(50) unique,
     ->t_sex enum('male','female') not null default 'male',
     ->t_age int not null default 20 );
 Query OK,0 row affected (0.03 sec)
 
mysql>create table test4(
     ->t_id int not null auto_increment,
     ->t_name varchar(50) unique,
     ->t_sex enum('male','female') not null default 'male',
     ->t_age int not null default 20,
     ->primary key(t_id));
 Query OK,0 row affected (0.01 sec)

复合主键
表school.service

host_ip存储主机IP
service name服务名
port服务对应的端口
allow(Y,N)服务是否允许访问

主键：host_ip + port= primary key
一个管不了事，只选一个会出现重复的现象，而主键只能是唯一值，不能出现重复现象，所以这个时候需要另外一个进行组合，这样主键才不会出现重复现在,而这时的主键也是唯一值，这便是复合主键。

mysql> create table service(
	->host_ip varchar(15) not null,
	->service_name varchar(10) not null, 
	->port varchar(5) not null,
	->allow enum('Y','N') default 'N',
	->primary key(host_ip,port));
Query OK, 0 rows affected (0.00 sec)

设置外键约束
设置外键约束 FOREIGN KEY

//父表company.employees
mysql> create table employees(
	-> name varchar(50) not null,
	->mail varchar(20),  //主键，主键和外键的类型和约束必须是一样的
	-> primary key(name)
	->)engine=innodb;
Query OK,0 row affected (0.03 sec)
//子表company.payroll
mysql> create table payroll( I
	-> id int not null auto_increment,
	-> name varchar(50) not null,   //外键，主键和外键的类型和约束必须是一样的
	->payroll float(10,2) not null,
	->primary key(id),
	->foreign key(name) references employees(name) on update cascade on delete cascade )engine=innodb;
Query OK,0 row affected (0.10 sec)

子表name外键，关联父表(employees主键name)，同步更新 同步删除
在创建父表和子表时要注意顺序，只有创建父表了才能创建子表，因为子表要关联父表的主键。
结论：
当父表中某个员工的记录修改时，子表也会同步修改；
当父表中删除某个员工的记录时，子表也会同步删除。

四、修改表 ALTER TABLE

语法：
1.修改表名
ALTER TABLE 表名
RENAME 新表名；
2.增加字段
ALTER TABLE 表名
ADD 字段名 数据类型[完整性约束条件…],
ADD 字段名数据类型[完整性约束条件…];
ALTER TABLE 表名
ADD 字段名数据类型 [完整性约束条件…]FIRST;
ALTER TABLE 表名
ADD 字段名数据类型 [完整性约束条件…]AFTER 字段名；
3.删除字段
ALTER TABLE 表名
DROP 字段名；
4.修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件……]
ALTER TABLE 表名
CHANGE 旧字段名 型字段名 旧字段类型 [完整性约束条件……]
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件……]

示例：
1.修改存储引擎

mysql> alter table service
	-> engine=innodb; //engine=myisam|memoryll....

2.添加字段

mysql> create table student10 (id int);

mysql> alter table student10
	-> add name varchar(20) not null,
	-> add age int not null default 22;

mysql> alter table student10
	-> add stu_num int not null after name; //添加name字段之后

mysql> alter table student10
	-> add sex enum('male','female') default 'male' first; //添加到最前面

3.删除字段

mysql> alter table student10
	-> drop sex;

4.修改字段类型modify

mysql> alter table student10
	-> modify age tinyint not null default 22; //注意保留原有的约束条件

mysql> alter table student10
	-> modify id int not null primary key; I //修改字段类型、约束、主键

5.增加约束(针对已有的主键增加auto_increment)

mysql> alter table student10 modify id int not null ~~primary key~~   auto_increment; //错误，该字段已经是primary key ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records:0 Duplicates:0 Warnings:0

6.增加复合主键

mysql> alter table service2
	-> add primary key(host_ip,port);

7.增加主键

mysql> alter table student1
	-> add primary key(id);

8.增加主键和自动增长

mysql> alter table student1
	-> modify id int not null primary key auto_increment;

9.删除主键[primary key auto_increment]
a.删除自增约束

mysql> alter table student10 modify id int not null;

b.删除主键

mysql> alter table student10
	-> drop primary key;

先删除自增再删除主键，自增依赖于主键，若是直接删除主键自增那边会抗议。

五、复制表

复制表结构+记录(key不会复制：主键、外键和索引)

mysql> create table new_service select * from service;

只复制表结构

mysql> create table new1_service select *from service where 1=2;   //条件为假，查不到任何记录复制表结构，包括Key

mysql> create table t4 like employees;

六、删除表

DROP TABLE 表名；

MySQL数据操作：DML

在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括使用INSERT实现数据的插入、DELETE实现数据的删除以及UPDATE实现数据的更新。
更新数据insert
更新数据update
删除数据delete

一、插入数据INSERT

1.插入完整数据(顺序插入)
语法—：
INSERT INTO-表名(字段1，字段2，字段3…字段A)VALUES(值1，值2，值3…值n)
语法二：
INSERT INTO 表名VALUES(值1，值2，值3…值n)；
2.指定字段插入数据
语法：
INSERT INTO 表名(字段2，字段3…) VALUES(值2，值3…)；
3.插入多条数据
语法：
INSERT INTO 表名(字段2，字段3…) VALUES
(值1，值2，值3…值n),
(值1，值2，值3…值n),
(值1，值2，值3…值n)；
4.插入查询结果
语法：
INSERT INTO 表1 VALUES(字段1，字段2，字段3……字段n)
SELECT (字段1，字段2，字段3……字段n) from 表名2
WHERE ……；

mysql> insert into test5
	->select * from test6 where age>6;
Query OK, 3 row affected (0.01 sec)
Records:3 Duplicates:0 Warnings:0

二、更新数据UPDATE

语法：
UPDATE 表名 SET
字段1=值1,
字段2=值2，
WHERE CONDITION;
示例：

mysql>select user,host,authentication_string from mysql.user;
+ -------- + -------- + --------------------------------------- +
| user     | host     | authentication_string				    |
+ -------- + --------- + -------------------------------------- +
| root     |localhost |*26194D7EF5057F123665C1AF7D9EBEDB0015E4EA|
| mysql.sys|localhosl |*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE|
+ ---------+ -------- + --------------------------------------- +
2 rows in set (0.00 sec)

mysql> update mysql.user set
	-> authentication_string=password('gkgfjhfh520^&*')
	-> where user='root'and host='localhost';
Query0K, 0 rowsaffected, 1 warning (0.00 sec)
Rows matched:1 Changed:0 Warnings:1

mysql> flush privileges;
Query 0K, 0 rows affected(0.00 sec)

三、删除数据DELECT

语法：
DELECT FROM 表名
WHERE CONITION;
示例：

mysql>delect from mysql.user where authentication_string=' ';

MySQL单表查询SELECT ：DQL

简单查询
通过条件查询
查询排序
限制查询记录
使用集合函数查询
分组查询
使用正则表达式查询

mysql>CREATE TABLE company.employee5(
	->id int primary key AUTO_INCREMENT not null, 
	->name varchar(30) not null,
	->sex enum('male','female') default 'male' not null,
	->hire_date date not null,
	->post varchar(50) not null,
	->job_description varchar(100),
	->salary double(15,2) not null,
	->office int,
	->dep_id int);

一、简单查询

简单查询
语法：

SELECT * FROM 表名；
SELECT （字段1，字段2，……字段n）FROM 表名;

避免重复DISTINCT

SELECT post FROM 表名;
SELECT DISTINCT 字段1 FROM 表名

注：不能部分使用DISTINCT，通常仅用于某一字段。

通过四则运算查询

SELECT name, salary, salary*14 FROM employee5;
SELECT name,salary,salary*14 AS Annual_salary FROM employee5;//AS Annual_salary 给salary*14起一个别名，年度薪水
SELECT name, salary, salary*14 Annual_salary FROM employee5;

字段后面空格或是空格加上AS可以给字段起一个别名来代替字段显示在数据表中。

定义显示格式

CONCAT() //函数用于连接字符串
SELECT CONCAT(name,' annual salary:' salary*14)AS Annual_salary FROM empl

二、单条件查询

单条件查询

mysql>SELECT name,post FROM employee5 WHERE post='hr';

多条件查询

mysql>select name,salary from employee5 where post="hr' and salary>10000;

关键字BETWEEN AND

//查询employee5表中薪水在5000和15000之间的数据
mysql>SELECT name,salary FROM employee5 WHERE salary between 5000 and 15000;
//查询employee5表中薪水不在5000和15000之间的数据
mysql>SELECT name,salary FROM employee5 WHERE salary not between 5000 and 15000;

关键字IS NULL

//查询employee5表中name,job_description字段中job_description是否设置的空值
mysql>SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
//查询employee5表中name,job_description字段中job_description有职位的
mysql>SELECT name,job_description FROM employee5
WHERE job_description IS NOT NULL;

关键字IN集合查询

//查询employee5表中name,salary字段中的salary薪水在4000或5000或6000或9000的
mysql>SELECT name,salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;
//查询employee5表中name,salary字段中的salary薪水在4000，5000，6000，9000范围内的
mysql>SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;
//查询employee5表中name,salary字段中的salary薪水不在4000，5000，6000，9000范围内的
mysql>SELECT name, salary FROM employee WHERE salary NOT IN (4000,5000,6000,9000);

关键字LIKE模糊查询
通配符‘%’
SELECT* FROM employee5
WHERE name LIKE ‘al%’;
%在这里表示任意多个字符。
通配符’_’
SELECT* FROM employee5
WHERE name LIKE ‘al__’;
明确知道一个字符的时候，用’ _ ‘，这表示一个数据，两个’ __'表示两个数据。

三、查询排序

按单列排序

//ORDER BY排序
mysql>SELECT * FROM employee5 ORDER BY salary;
//升序排序
mysql>SELECT name, salary FROM employee5 ORDER BY salary ASC;
//降序排序
mysql>SELECT name,salary FROM employee5 ORDER BY salary DESC;

按多列排序

//查询employee5表入职降序排序，薪水升序排序
mysql>SELECT* FROM employee5 ORDER BY 
	->hire_date DESC salary ASC;

四、限制查询的记录数据

示例：

//从一开始选择5个数据，开始数值为0
mysql>SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0

//从一开始选择5个数据，开始数值为0
mysql>SELECT*FROM employee5 ORDER BY salary DESC LIMIT 0,5;

mysql>SELECT*FROM employee5 ORDER BY salary DESC LIMIT 3,5; //从第4条开始，共显示5条

五、使用集合函数查询

示例：

//数量值
mysql>SELECT COUNT(*) FROM employee5;
//dep_id=101的数值
mysql>SELECT COUNT(*) FROM employee5 WHERE dep_id=101;
//最大值
mysql>SELECT MAX(salary) FROM employee5;
//最小值
mysql>SELECT MIN(salary) FROM employee5;
//平均值
mysql>SELECT AVG(salary) FROM employee5;
//总数值
mysql>SELECT SUM(salary) FROM employee5;
// dep_id=101的总数值
mysql>SELECT SUM(salary) FROM emnlovee5 WHERE dep_id=101.

六、分组查询
分组 GROUP BY
拼在一起 GROUP_CONCAT()
GROUP BY和GROUP_CONCAT()函数一起使用

//只要部门id相同的把他们的名字拼在一起
mysql>SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
//只要部门id相同的把他们的名字都拼在一起，name字段起一个别名叫emp_members
mysql>SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;

GROUP BY也可以和集合函数一起使用

mysql>SELECT dep_id,AVG(salary) FROM employee5 GROUP BY dep_id;

七、使用正则表达式查询
正则表达式：REGEXP

//查询name字段中有ali的数据
mysql>SELECT*FROM employee5 WHERE name REGEXP '^ali'; 
//查询name字段中以yun结尾的数据
mysql>SELECT*FROM employee5 WHERE name REGEXP 'yun$';
//查询name字段中出现2次m的数据
mysql>SELECT*FROM employee5 WHERE name REGEXP'm(2)';

MySQL多表查询 DQL

多表连接查询
复合条件连接查询
子查询

一、多表连接查询

交叉连接： 生成笛卡尔积，它不使用任何匹配条件
内连接： 只连接匹配的行
外连接之左连接： 会显示左边表内所有的值，不论在右边表内匹不
匹配外连接之右连接： 会显示右边表内所有的值，不论在左边表内
匹不匹配全外连接： 包含左、右两个表的全部行

交叉连接 两个表关联起来，没有任何的匹配条件

mysql>select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6;

内连接：只找出有部门的员工

mysql>select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6 
	->where employee6.dept_id=department6.dept_id;

外连接语法：
select 字段列表 from 表1 left|right join 表2 on 表1.字段 = 表2.字段;
left join 左边连接，主要以左边为主
right join 右连接，主要以右边为主

//找出所有的员工，以及所属部门，包括没有部门的员工
mysql>select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;

全外连接

mysql>select * from employee6 full join department6;

二、复合条件连接查询

示例1：以内连接的方式查询employee6和department6表，并且employee6表中的age字段值必须大于25
找出公司所有部门中年龄大于25岁的员工

mysql>SELECT emp_id,emp_name,age,dept_name FROM employee6,department6
	->WHERE employee6.dept_id = department6.dept_id AND age >25;

示例2：以内连接的 方式查询employee6和department6表，并且以age字段的升序方式显示

mysql>SELECT emp_id,emp_name,age,dept_name 
	->FROM employee6,department6
	->WHERE employee6.dept_id = department6.dept_id
	->order by age asc;

三、子查询

子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS和 NOT EXISTS等关键字还可以包含比较运算符：=、！=、>、<等

1.带IN关键字的子查询
查询employee表，但dept_id必须在department表中出现过

mysql>select * from employee6
	->where dept_id in(select dept_id from department6);

2.带比较运算符的子查询
=、！=、>、>=、<、<=、<>
查询年龄大于等于25岁员工所在的部门

mysql>select dept_id,dept_name from department6
	->where dept_id in
	->(select distinct dept_id from employee6 where age>=25);

3.带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。
Ture或False,当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

//department表中存在dept_id=203，Ture
mysql>select * from employee6
	->where exists (select * from department6 where dept_id=203);

MySQL索引

创建索引
创建表时创建索引
CREATE在已存在的表上创建索引
ALTER TABLE在已存在的表上创建索引
查看并测试索引
删除索引

一、索引简介

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

二、索引的分类

普通索引
唯一索引 干锋
全文索引
单列索引
多列索引
空间索引

三、创建索引

创建表时
语法：
CREATE TABLE 表名(
字段名1 数据类型 [完整性约束条件…]
字段名2 数据类型[完整性约束条件…],
[UNIQUE|FULLTEXT|SPATIAL ] INDEX| KEY
[索引名](字段名[(长度)] [ASC|DESC])
);
创建普通索引示例：
创建索引必须要有index或者是key
可以是unique index/key 唯一索引
fulltext index/key 全文索引
spatial index/key
也可以index/key (字段名)
多列索引：index（字段名1，字段名2……）

创建索引：
mysql>create TABLE department10(
	->dept_id INT,
	->dept_name VARCHAR(30),
	->comment VARCHAR(50),
	->index (dept_name));//单列索引

在已存在的表中创建索引
语法：
create [unique|fulltext|spatial] index 索引名 on 表名 （字段名[(长度)] [asc|desc]);
创建普通索引名称
这个时候索引名是必须要添加上的

mysql>create index index_dept_name ondepartment (dept_name);

创建唯一索引示例：

mysql>create unique index index_dept_name ondepartment (dept_name);

创建全文索引示例：

mysql>create fulltext index index_dept_name ondepartment (dept_name);

创建多列索引示例：

mysql>create index index_dept_name ondepartment (dept_name，comment);

四、管理索引

查看索引
show create table 表名\G
explain：命令的作用是查看查询优化器如何决定执行查询

删除索引
show create table employee6;
drop index 索引名 on 表名；

MySQL视图 VIEW

视图简介
创建视图
查看视图
修改视图
通过视图操作基表
删除视图

一、视图简介

MySQL视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，MySQL视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
视图是存储在数据库中的SQL查询语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：一些敏感的信息，另一原因是可以使复杂的查询易于理解和使用。

二、创建视图

语法一：
CREATE [ALGORITHM=(UNDEFINED| MERGE | TEMPTABLE]
VIEW 视图名[(字段1,字段2…)]
AS SELECT语句
[WITH [CASCADED| LOCAL] CHECK OPTION ];

语法二：
CREATE VIEW视图名
ASSELECT语句

示例1：

mysql>create view u
	->as select user,host,authentication_string from mysql.user;

示例2：创建多个表

mysql> create database shop;
Query OK, 1 row affected (0.21 sec)

mysql> use shop
Database changed

mysql> create table product(
	->id int unsigned auto_increment primary key not null, 
	->name varchar(60)not null,
	->price double not null);

mysql> insert into product(name,price) values
	->('pear',4.3),
	->('orange',6.5),
	->('apple',5.0));

mysql> create table purchase(
	->id int unsigned auto_increment primary key not null, 
	->name varchar(60)not null,
	->quantity int not null default 0,
	->gen_time datetime not null);

mysql> insert into purchase(name,quantity,gen_time) values
	->('apple',7,now()),
	->('pear',4,now());
//产品名字，产品价格，产品数量
mysql> create view purchase_detail
	->as select 
	->product.name, product.price,purchase.quantity,
	->product.price*purchase.quantity as total_value from product,purchase
	->where product.name =purchase.name;

三、查看视图

1. SHOW TABLES 查看视图名
SHOW TABLES;

2. SHOW TABLE STATUS
示例：查看数据库mysql中视图及所有表详细信息SHOW TABLE STATUS FROM mysql \G
示例：查看数据库mysql中视图名view_user详细信息
SHOW TABLE STATUS FROM mysql LIKE view_user’\G

3. SHOW CREATE VIEW
示例：查看视图定义信息
SHOW CREATE VIEW view_user\G

4. DESCRIBE
示例：查看视图结构 I
DESC view_user;

四、修改视图

方法一：删除后新创建
DROP VIEW view_userI
CREATE VIEW view_user
AS SELECT user,host FROM mysql.user; SELECT *FROM view_user;
方法二：ALTER修改视图
语法：
ALTER VIEW 视图名
AS SELECT语句；
示例：

ALTER VIEW view_user
AS SELECTuser,passwordFROM mysql.user;

五、通过视图操作基表

查询数据SELECT
SELECT * FROM view_user;

更新数据UPDATE
删除数据DELETE

六、删除视图

语法：
DROP VIEW view_name [,view_name]…;
示例：
USE mysql;
DROP VIEW view_user ;

MySQL 触发器 Triggers

触发器简介
创建触发器
查看触发器
删除触发器
触发器案例

一、触发器简介

触发器(trigger)是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作(insert，delete，update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。
例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。

二、创建触发器

语法：
CREATE TRIGGER 触发器名称 BEFORE|AFTER触发事件ON 表名 FOR EACH ROW
BEGIN
触发器程序体；
END
在这里插入图片描述

1创建表

mysql>create table student(
	-> id int unsigned auto_increment primary key not null,
	-> name varchar(50));
	
mysql> insert into student(name) values(jack);

mysql> create table student_total(total int);

mysql> insert into student_total values(1);

2.创建触发器student_insert_trigger

mysql> delimiter $$ //$$是结束符
mysql> create trigger student_insert_trigger after insert 
	->on student for each row 
	->BEGIN 
	->update student_total set total=total+1;
	->END$$
mysql> delimiter;//换回;是结束符

3.创建触发器student_delete_trigger

mysql> delimiter $$
mysql> create trigger student_delete_trigger after delete
	->on student for each row
	->BEGIN
	->update student_total set total=total-1;
	->END$$
mysql> delimiter;//换回;是结束符

触发器案例：

//创建表tab1
mysql>DROP TABLE IF EXISTS tab1;
mysql>CREATE TABLE tab1(
	->id int primary key auto_increment, 
	->name varchar(50),
	->sex enum('m',f),
	->age int);
//创建表tab2
mysql>DROP TABLE IF EXISTS tab2;
mysql>CREATE TABLE tab2(
	->id int primary key auto_increment, 
	->name varchar(50),
	->salary double(10,2));

触发器tab1_after_delete trigger
作用：tab1表删除记录后，自动将tab2表中对应记录删除

mysql> \d $$
mysql> create trigger tab1_after_delete_trigger
	->after delete on tab1
	->for each row
	->BEGIN
	->delete from tab2 where name=old.name;
	->END$$

触发器tab1_after update trigger作用：当tab1更新后，自动更新tab2

mysql> create trigger tab1_after_update_trigger
	->after update on tab1
	->for each row
	->BEGIN
	->insert into tab2(name,salary) values(new.name,5000);
	->END$$

更新和删除必须通过主键进行操作

星※星
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql高级篇学习笔记
生命不息，编程不止的博客
08-19 7541
MySQL高级篇学习借鉴笔记
Linux+MySQL+JAVA基础笔记适合萌新
12-17
学习编写简单的Java程序，熟悉IDE如Eclipse或IntelliJ IDEA的使用，以及掌握如何调试和测试代码，这些都是基础阶段必备的技能。 总的来说，这个笔记涵盖了从操作系统的基本操作到数据库管理，再到编程语言的学习，...
mysql数据类型
CHIK_HunPun的博客
05-17 233
数据类型
MySQL 基础笔记
qq_43645678的博客
09-25 1700
MySQL 基础笔记
MySQL 相关知识笔记
weixin_68929783的博客
08-23 3024
以上是根据 B 站黑马课程学习后的 MySQL 笔记，为了日后复习和给别人参考而整理，希望对大家有所帮助。同时，在实际应用中，还需要根据具体情况合理使用索引、约束等功能，以提高数据库的性能和数据的完整性。
【MySQL】 黑马 MySQL进阶 笔记
YA____的博客
08-23 1706
介绍索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引——可以类比二叉搜索树优缺点优势提高数据检索的效率，降低数据库的成本通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。劣势索引列也是要占用空间的（硬盘价格低，可忽略）
Mysql 学习笔记
m0_60693634的博客
03-10 1818
对于本人情况，着重学习第二个阶段。
黑马MySQL进阶篇笔记
温涛的博客
04-04 3093
包括存储引擎、索引、SQL优化、视图、存储过程、存储函数、触发器、锁、InnoDB引擎、MySQL管理
尚硅谷MySQL高级学习笔记
热门推荐
李英俊小朋友
08-18 2万+
数据库MySQL学习笔记高级篇 文章目录数据库MySQL学习笔记高级篇写在前面1. mysql的架构介绍mysql简介概述高级MysqlmysqlLinux版的安装mysql配置文件mysql逻辑架构介绍mysql存储引擎2. 索引优化分析性能下降SQL慢常见通用的Join查询索引简介性能分析索引优化3. 查询截取分析查询优化慢查询日志批量数据脚本Show Profile全局查询日志4. MySQ...
MySQL的知识点整理（学习笔记）
随心笔记
06-06 7698
MySQL是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。Mysql是开源的，所以你不需要支付额外的费用。Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
02 Go语言操作MySQL基础教程_20240729 课程笔记
Python私教
07-29 1483
如果您没有Golang的基础，应该学习如下前置课程。Golang零基础入门Golang面向对象编程Go Web 基础Go语言开发REST API接口_20240728基础不好的同学每节课的代码最好配合视频进行阅读和学习，如果基础比较扎实，则阅读本教程巩固一下相关知识点即可，遇到不会的知识点再看视频。
MySQL学习笔记
12-19
### MySQL学习笔记知识点详解 #### 一、数据库概述 数据库是指按照特定的数据结构来组织、存储和管理数据的集合体。随着信息技术的进步和社会需求的增长，数据库已经从最初的简单存储发展成为用户所需的各种复杂...
mysql运维工程师笔记
05-04
本笔记涵盖了 MySQL 的基础知识点，包括数据库的基本概念、 MySQL 的特点、库和表的操作、数据的操作、查询语句、数据完整性、事务的隔离级别等。 数据库的基本概念： 数据库是存储和管理数据的仓库，经历了人工...
mysql学习笔记
12-16
这份“mysql学习笔记”涵盖了MySQL的基础知识，包括但不限于数据库的概念、SQL语言的使用、以及更深入的多表操作。 在“day08入门笔记”中，你可能会学到以下内容： 1. **数据库基本概念**：了解什么是数据库，...
策略规划：在MySQL中实现数据恢复的全面指南
2401_85339615的博客
09-04 1096
数据恢复是数据库管理中至关重要的一环，它确保在发生数据丢失或损坏的情况下，能够迅速且准确地恢复数据。在MySQL中，实现有效的数据恢复策略规划需要综合考虑备份策略、备份类型、存储管理、故障转移机制以及恢复流程。本文将深入探讨如何在MySQL中进行数据恢复的策略规划，包括策略制定、技术实现和最佳实践。
MySQL 大量 IN 的查询优化
xchenhao 的博客
09-09 301
MySQL 大量 IN 的查询优化
三.海量数据实时分析-FlinkCDC实现Mysql数据同步到Doris
墨家巨子@俏如来
09-07 1163
通过上面的 route 配置，会将 app_db.orders 表的结构和数据同步到 ods_db.ods_orders 中。这样，就可以将诸如 app_db.order01、app_db.order02、app_db.order03 的表汇总到 ods_db.ods_orders 中。参考：https://nightlies.apache.org/flink/flink-cdc-docs-release-3.0/zh/docs/get-started/quickstart/mysql-to-doris/
【重学 MySQL】十四、显示表结构
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
09-06 657
以上就是在MySQL中显示表结构的几种常用方法。DESCRIBE或DESC命令和命令提 供了快速查看表列信息的简便方式，而查询数据库则提供了更详细、更灵活的信息获取方式。命令则适用于需要查看或复制表完整结构的情况。
MySQL MHA
最新发布
2201_75549363的博客
09-09 591
该参数代表忽略上次 MHA 触发切换产生的文件，默认情况下，MHA 发生切换后会在日志记录，也就是上面设置的日志app1.failover.complete文件，下次再次切换的时候如果发现该目录下存在该文件将不允许触发切换，除非在第一次切换后收到删除该文件，为了方便，这里设置为--ignore_last_failover。master_ip_failover_script=/usr/local/bin/master_ip_failover　　#设置自动failover时候的切换脚本，也就是上面的那个脚本。
MySQL5.1学习笔记：从基础到应用与管理
这份笔记覆盖了MySQL的基础知识和高级应用，旨在帮助读者深入了解和掌握MySQL数据库系统。 第1章介绍了MySQL的安装，包括下载安装MySQL服务器以及配置基本环境。对于初学者来说，这是入门的第一步，确保安装过程...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值