Mysql基础

Mysql

Mysql的介绍#

SQL ( Structure query language ) 结构化查询语言

SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）

1、DDL语句数据库定义语言：数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2、DCL语句数据库控制语言：例如控制用户的访问权限GRANT、REVOKE
3、DML语句数据库操纵语言：插入数据INSERT、删除数据DELETE、更新数据UPDATE
4、DQL语句数据库操纵语言：查询数据SELECT

mysql数据库管理软件,记录事物一些数据特征:
由库,表,记录组成.
库相当于一个文件夹
表相当于一个文件
记录就是文件里面一条一条的内容
表中的成员属性就是一个一个字段
可以为每个项目建立一个数据库

关系型数据库:表与表之间有联系
比如:mysql,oracle,db2,sqlserver

非关系型数据库: key-value 键值对形式没有表的概念
比如:redis,mongodb,memcache

windows下Mysql的安装#

### windows安装mysql5.7
(1) 在D:\MySQL5.7\mysql-5.7.25-winx64文件下创建一个my.ini文件
以下部分可以黏贴: 但是要注意路径,除非你和我的一模一样.

# my.ini 文件
[mysql]  # 对服务端进行配置
# 设置mysql客户端默认字符集
default-character-set=utf8

[mysqld] # 对客户端进行配置

# 设置3306端口
port = 3306

# 设置mysql的安装目录
basedir=D:\MySQL5.7\mysql-5.7.25-winx64

# 设置mysql数据库的数据的存放目录
datadir=D:\MySQL5.7\mysql-5.7.25-winx64\data

# 允许最大连接数(并发)
max_connections=200

# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
----------------------------------------------------------------------------

以管理员身份运行cmd，进入bin目录，执行:
(2)初始化,创建mysql默认的root账户
mysqld --initialize-insecure --user=mysql 命令。不进行这一步，安装完成之后无法启动服务。
(3)依然在管理员cmd窗口的bin目录下，执行 mysqld install  命令安装。完成后会提示安装成功。
(4)依然在管理员cmd窗口的bin目录下，执行 net start mysql 命令启动MySQL服务。
(5)修改环境变量，添加"D:\MySQL5.7\mysql-5.7.25-winx64\bin"。
(6)cmd窗口中,执行 mysql -uroot -p 命令，默认没有密码，回车进入
(7)若要卸载,需要先停止服务,再删除即可

# 启动mysql服务
net start mysql
# 停止mysql服务
net stop mysql 
# 重启:先停止在启动

mysql在Linux中更改编码#

sudo find / -name my.cnf

#配置linux下的编码集
!includedir  /etc/mysql/conf.d/       客户端的修改
vim my.cnf
# 设置mysql客户端默认字符集
default-character-set=utf8


!includedir  /etc/mysql/mysql.conf.d/ 服务端的修改
vim my.cnf
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8

# 重启服务
service mysql restart

Mysql的快捷键#

\G 格式化输出（文本式，竖立显示）

\s 查看服务器端信息

\c 结束命令输入操作

\q 退出当前sql命令行模式

\h 查看帮助

数据库的状态操作#

登录数据库#

mysql -u用户 -p密码 -hip地址
mysql -uroot -p -h默认本地ip  
localhost => 127.0.0.1

退出数据库#

exit  或者  \q

查询当前登录用户#

select user()


+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

设置密码#

set password = password("123456")

去除密码#

set password = password("");

新建用户#

# 给具体某个ip设置一个账户连接linux
create user "ceshi100"@"192.168.126.1" identified by "111";

# 给具体192.168.126.% 这个网段下的所有ip设置账户
create user "ceshi101"@"192.168.126.%" identified by "222";

# 给所有ip下的主机设置账户
create user "ceshi102"@"%" identified by "333";

保存在mysql本地 的 user 用户表中

授权语法#

"""
select 查询数据的权限
insert 添加数据的权限
update 更改数据的权限
delete 删除数据的权限

* 所有权限

"""

# USAGE 没有任何权限
# 查看具体某个ip下的用户权限
show grants for "ceshi102"@"%";

# ON *.*  表示  数据库.表

+--------------------------------------+
| Grants for ceshi102@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO 'ceshi102'@'%' |
+--------------------------------------+

# 授权语法 [identified by 密码是可选参数]
grant 权限 on 数据库.表 to "用户名"@"ip地址" identified by "密码";

# 实例
    # 授予查询权限
    grant select,insert on *.* to "ceshi102"@"%" identified by "333";

    # 授予所有权限
 	grant all on *.* to "ceshi102"@"%" identified by "333";


# 移除
    # 移除删除权限(删除数据库/表)
    revoke drop on *.* from "ceshi102"@"%"
    # 移除所有权限
    revoke all on *.* from "ceshi102"@"%"
    
# 刷新权限,立刻生效
flush privileges

数据库内部方法#

select user()

select concat() # 内部链接字符串

select database() # 显示当前的数据库

select now() # 显示当前的时间

操纵数据库#

创建数据库#

create database db001 charset utf8;

查看数据库#

show databases;

查看数据库建库语句#

show create database db001;

+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| wbc      | CREATE DATABASE `wbc` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

修改数据库#

alter database db002 charset gbk;

删除数据库#

drop database db001

操作数据表#

选择数据库#

use db001

创建表#

create table t1(id int , name char);

修改表名#

alter table t1 rename t1111111;
alter table 原表名 rename 新表名;

删表#

DROP TABLE table_name;

查看所有表#

show tables;

查看建表语句#

 show create table t1;
 
 
 CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

查看表结构#

desc t1;

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改表的字段属性#

modify#

alter table t1 modify name char(5);
alter table 表名 modify 字段 类型;

change#

alter table t1 change name  name123 char(4);
alter table 表名 change 原字段  新的字段 类型;

add#

alter table t1 add age int;
alter table t1 add 字段名 类型;

# 增加约束
ALTER TABLE t1 ADD UNIQUE (age);

drop#

alter table t1 drop age;
alter table t1 drop 字段;

alter table t1 drop index 字段; # 删除普通索引

操纵记录#

插入数据#

# 一次插入一条数据
insert into t1(id,name) values(1,'abcd');

# 一次插入多条数据
insert into t1(id,name) values(2,"王文"),(3,"刘文波"),(4,"康裕康"),(5,"张保障");

# 不指定具体字段,默认把字段全部插一遍
insert into t1 values(6,"沈思雨");

# 可以具体指定某个字段进行插入
insert into t1(name) values("张宇");

查询数据#

# * 所有
select * from t1;

# 查询单个字段
select id from t1;

# 查询多个字段
select id,name from t1;

修改数据#

# update 表名 set 字段=值 where 条件
update t1 set name="王伟" where id = 2;

# 不加条件有风险,一改全改,一定加where
update t1 set name="王伟" ;

删除数据#

# 删除的时候,必须加上where
delete from t1 where id = 1;

# 删除所有数据,一删全删,一定加where
delete from t1;

# 删除所有 (数据+重置id)
truncate table t1;

"""
	重置ID后 auto_increment 会重置 ID
"""

#

数据类型#

[!IMPORTANT]

MySQL会根据实际情况来确定存储的精度和范围。

整形#

tinyint#

1个字节有符号范围(-128~127) 无符号(0~255) unsigned 小整型值

int#

4个字节有符号范围(-21亿 ~ 21亿左右) 无符号(0~42亿) 大整型值

浮点型（位数，小数位数）#

float(255,30)#

单精度

double(255,30)#

双精度

decimal(60,30)#

金钱类型 (用字符串的形式来存储小数)

字符串 (字符个数/字符长度)#

char(255)#

定长:固定开辟11个字符长度的空间(手机号,身份证号),开辟空间的速度上来说比较快,从数据结构上来说,需谨慎,可能存在空间浪费. max = 255

varchar(21845)#

变长:动态最多开辟11个字符长度的空间(评论,广告),开辟空间的速度上来说相对慢,从数据结构上来说,推荐使用,不存在空间浪费 max = 21845

text#

本类型:针对于文章,论文,小说. max > varchar

create table t7(c char(11), v varchar(11) , t text);
insert into t7 values("11111","11111","11111");
insert into t7 values("你好啊你好啊你好啊你好","你好啊你好啊你好啊你好","你好啊你好啊你好啊你好");
# concat  可以把各个字段拼接在一起
select concat(c,"<=>",v,"<=>",t) from t7;

枚举和集合#

enum#

枚举 : 从列出来的数据当中选一个 (性别)

set#

集合 : 从列出来的数据当中选多个 (爱好)

create table t8( 
id int , 
name varchar(10) ,
sex enum("男性","兽性","人妖") , 
money float(5,3) , 
hobby set("吃肉","抽烟","喝酒","打麻将","嫖赌")  
);

# 正常写法
insert into t8(id,name,sex , money , hobby) values(1,"张保障","兽性",2.6,"打麻将,吃肉,嫖赌");

# 自动去重
insert into t8(id,name,sex , money , hobby) values(1,"张保障","兽性",2.6,"打麻将,吃肉,嫖赌,嫖赌,嫖赌,嫖赌,嫖赌,嫖赌");

# 异常写法 : 不能选择除了列出来的数据之外的其他值 error 报错
insert into t8(id,name,sex , money , hobby) values(1,"张保障","人妖12",2.6,"打麻将,吃肉,嫖赌12");

时间类型#

date#

time#

year#

datetime#

timestamp#

时间戳自动更新时间

约束#

unsigned 无符号#

create table t3(id int unsigned)

not null 不为空#

create table t4(id int not null)
insert into t4 values(null,"kk") # error

default 默认值#

create table t4(id int default "默认")
# 不写的时候才可以 给 默认值
insert into t4 values()

unique 唯一值加入唯一索引（为了加快速度，不可多加）#

create table t4(id int unique)  # 标记为uni
insert into t4 values(1) 
insert into t4 values(1) # error

# 可以是null  插入多个
insert into t4 values(null) 
insert into t4 values(null)  # id变成多个null

primary key 主键 [唯一 + 不为null]#

create table t4(id int primary key)  # 标记为uni

insert into t4 values(1) 
insert into t4 values(1) # error

insert into t4 values(null) # error

[!IMPORTANT]

unique + not null ==》 primary key

在有primary key 和 unique + not null 时候优先primary 为主键 unique + not null 降级为unique

primary key 只能有一个多个会报错

auto_increment 自增加一（配和主键或者 unique 使用）#

create table t1(id int primary key auto_increment  )

insert into t1 values(1);
insert into t1 values(null); # 自动增加为 2 3 4 5

zerofill 零填充（配合int 使用，作为零填充）#

create table t1(id int(5) zerofill)
insert into t1 values(12);  # 会变成 00012

foreign key 外键#

把多张表通过一个关联字段聚合作用可以联级更新或删除

在将一个大型的多键的数据表为了速度会进行切割

在切割的时候会寻找一个关联的字段进行联级

注意被关联的字段必须具有唯一性

[!IMPORTANT]

关联的字段一般叫做外键

被关联的字段一般叫做约束索引

"""
	student1 为关联
	class1  为被关联
"""

# 流程代码

# class1     

create table class1(id int unique, classname varchar(255))
create table class1(id int, classname varchar(255));
or
alter table class1 add unique(id);


# student1
create table student1(
	id int primary key auto_increment,
    name varchar(255),
    age int,
    classid int,
    foreign key(classid) references class1(id)
);

------------------------------------------------------------------------
mysql> desc student1
    -> ;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | YES  |     | NULL    |                |
| age     | int(11)      | YES  |     | NULL    |                |
| classid | int(11)      | YES  | MUL | NULL    |                |
+---------+--------------+------+-----+---------+----------------+


Table: student1
Create Table: CREATE TABLE `student1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `classid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `classid` (`classid`),
  CONSTRAINT `student1_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class1` (`id`) # 看这里
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> desc class1;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | YES  | UNI | NULL    |       |
| classname | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+


insert into class1 values(1,"python32");
insert into class1 values(2,"python33");
insert into class1 values(3,"python34");

insert into student1 values(null,"wbc",30,1);
insert into student1 values(null,"qqq",32,1);
insert into student1 values(null,"ccc",20,2);

# 没有关联的数据可以直接删除
delete from class1 where id = 1;

# 有关联的数据不能直接删除 需要先把关联的数据删除

# 先删除约束的会
delete from class1 where id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`wbc`.`student1`, CONSTRAINT `student1_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class1` (`id`))


# 先删除外键记录
delete from student1 where id = 3;
delete from class1 where id = 2;

约束索引没有资格被删除#

[!WARNING]

ERROR 1553 (HY000): Cannot drop index 'id': needed in a foreign key constraint

外键被删除时候#

ALTER TABLE student1 DROP FOREIGN KEY student1_ibfk_1;
# 需要通过外键生成的名称进行删除

# 不然会报
ERROR 1091 (42000): Can't DROP 'classid'; check that column/key exists

联级更新和删除#

[!IMPORTANT]

在删除主表或者副表都会产生联动反应

# class1     create table class1(id int unique, classname varchar(255))
create table class1(id int, classname varchar(255));
alter table class1 add unique(id);


# student1
CREATE TABLE student1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    age INT,
    classid INT,
    FOREIGN KEY (classid) REFERENCES class1(id) ON DELETE CASCADE ON UPDATE CASCADE
);




insert into class1 values(1,"python32");
insert into class1 values(2,"python33");
insert into class1 values(3,"python34");

insert into student1 values(null,"wbc",30,1);
insert into student1 values(null,"qqq",32,1);
insert into student1 values(null,"ccc",20,2);



+----+------+------+---------+
| id | name | age  | classid |
+----+------+------+---------+
|  1 | wbc  |   30 |       1 |
|  2 | qqq  |   32 |       1 |
|  3 | ccc  |   20 |       2 |
+----+------+------+---------+

+------+-----------+
| id   | classname |
+------+-----------+
|    1 | python32  |
|    2 | python33  |
|    3 | python34  |
+------+-----------+

# 二表删除会联动反应
------------------------------------------------
mysql> delete from class1 where id = 2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from  student1;
+----+------+------+---------+
| id | name | age  | classid |
+----+------+------+---------+
|  1 | wbc  |   30 |       1 |
|  2 | qqq  |   32 |       1 |
+----+------+------+---------+
2 rows in set (0.00 sec)

mysql> select * from  class1;
+------+-----------+
| id   | classname |
+------+-----------+
|    1 | python32  |
|    3 | python34  |
+------+-----------+

联合唯一索引#

unique + not null#

 create table t8 (id int ,ip varchar(15) not null , port int not null, unique(ip,port))
 
 # 短暂升级为 联合唯一主键
 +-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| ip    | varchar(15) | NO   | PRI | NULL    |       |
| port  | int(11)     | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

unique#

 create table t9 (id int ,ip varchar(15)  , port int , unique(ip,port))
 
-----+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| ip    | varchar(15) | YES  | MUL | NULL    |       |
| port  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

"""
尽管你可能会在创建联合索引时使用UNIQUE关键字，但在MySQL中，这个索引仍然被认为是普通索引，只是具有唯一性约束而已。
"""

联合唯一主键#

 create table t8 (id int ,ip varchar(15)  , port int , primary key(ip,port))
 
 +-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| ip    | varchar(15) | NO   | PRI | NULL    |       |
| port  | int(11)     | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

表关系#

一对一#

CREATE TABLE Books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    author_id INT UNIQUE, # 独特的 只有一对一的关系
    description TEXT,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

CREATE TABLE Authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    bio TEXT
);

+-----------+------------+--------------------------------------+
| author_id | name       | bio                                  |
+-----------+------------+--------------------------------------+
| 1         | John Doe   | John Doe is a prolific author...     |
| 2         | Jane Smith | Jane Smith is an accomplished...     |
+-----------+------------+--------------------------------------+

+---------+--------+-----------+------------------------+
| book_id | title  | author_id | description            |
+---------+--------+-----------+------------------------+
| 1       | Book 1 | 1         | Description of Book 1  |
| 2       | Book 2 | 2         | Description of Book 2  |
+---------+--------+-----------+------------------------+




"""
	每个作者只有一个作者详情，而每本书只属于一个作者。因此，我们在 Books 表中创建了一个唯一的外键 author_id，指向 Authors 表中的 author_id 主键。
"""

这种关系意味着一个实体的一个实例只能关联到另一个实体的一个实例

一对多#

CREATE TABLE Books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    description TEXT,
    category_id INT,    # 不是unique 可以一对多
    FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

CREATE TABLE Categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

#  副表 1 
+-------------+--------------+
| category_id | name         |
+-------------+--------------+
| 1           | Fiction      |
| 2           | Non-Fiction  |
+-------------+--------------+

#  主表 多 
+---------+--------------------+-------------+---------------------------+
| book_id | title              | category_id | description               |
+---------+--------------------+-------------+---------------------------+
| 1       | Fiction Book       | 1           | Description of Fiction... |
| 2       | Non-Fiction Book   | 2           | Description of Non-Fic... |
| 3       | Another Fiction... | 1           | Description of Another... |
+---------+--------------------+-------------+---------------------------+




"""
	在这个例子中，每个类别可以包含多本书，但每本书只属于一个类别。因此，我们在 Books 表中创建了一个 category_id 外键，指向 Categories 表中的 category_id 主键。
"""

多对多#

CREATE TABLE Authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    bio TEXT
);

CREATE TABLE Books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    description TEXT
);

# 中间表做介质 实现多对多

CREATE TABLE AuthorsBooks (
    author_id INT,
    book_id INT,
    PRIMARY KEY (author_id, book_id),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id),
    FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

"""
    在这个例子中，多个作者可以共同创作一本书，一本书也可以有多个作者。为了实现这种多对多的关系，我们创建了一个中间表 AuthorsBooks，用于记录每个作者和每本书之间的关系。这个中间表包含两个外键，分别指向 Authors 和 Books 表中的主键。同时，我们将 (author_id, book_id) 设为主键，以确保每个作者和每本书的组合是唯一的。
"""

存储引擎#

show engnes

MyISAM

表级锁 5.5版本之前默认的存储引擎

文件为

.frm 表结构

.myd 表数据

.myi 表索引

InnoDB#

事务处理行级锁外键

.frm 表结构

.myd 表数据 + 表索引

Memory#

把数据放在内存中，临时缓存

.frm 表结构内存在数据中

Blackhole

一般用于同步主从数据库放在主数据库和从数据库的一台服务器

同步 bin-log 日志

.frm 表结构

表级锁#

只有一个线程执行修改表中的相关操作，就会上锁

行级锁#

针对当前表中的这条记录，这一行进行上锁，其他数据仍然可以被线程修改

事务处理#

执行sql语句时，必须所有的操作全部成功，才会提交数据，有一条失败，可以直接回滚

begin 开启事务#

commit 提交数据#

rollback 回滚数据#

单表查询#

[!NOTE]

""" select ... from ... where ... group by ... having ... order by ... limit ... """

#

# 单表练习

#创建表
create table employee(
    id int not null unique auto_increment,
    emp_name varchar(20) not null,
    sex enum('male','female') not null default 'male', #大部分是男的
    age int(3) unsigned not null default 28,
    hire_date date not null,
    post varchar(50),
    post_comment varchar(100),
    salary double(15,2),
    office int, #一个部门一个屋子
    depart_id int
);


#三个部门：教学，销售，运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

where#

"""
		功能: 对表中的数据进行筛选和过滤
		语法:
			1.判断的符号
			= (!= <>不等于) > >= < <=
			2.拼接不同的条件的关键字
			and or not 
			3.查询对应的区间值
			between 小值 and 大值 [小值,大值]   查询两者之间的范围值
			4.查询具体在哪个范围中
			in(1,21,333,444) 指定范围
			5.模糊查询 like % 通配符  _ 通配符
				like "%b"  匹配以b结尾的任意长度的字符串
				like "b%"  匹配以b开头的任意长度的字符串
				like "%b%" 匹配字符串中含有b的任意长度的内容
				like "__b" 匹配总长度为3个字符,任意内容的字符串,并且以b结尾
				like "b_"  匹配总长度为2个字符,任意内容的字符串,并且以b开头
"""

group by ... having#

# having 在数据分类分组之后,对数据进行二次过滤,一般配合group by来使用的;
# 在group by 时候 select 只能 查询group by 依据的字段 但是 可以利用聚合函数来突破限制 group_concat

order by#

order by # 排序 , 按照某字段排序
order by age asc (升序)  # 默认
order by age desc (降序)

limit#

""" limit m,n m代表从第几条数据开始查, n 代表查几条  m=0 代表的是第一条数据"""
""" limit + num  num => 搜索的条数据 """

	# 1. 查询部门是sale的所有员工姓名:
	select emp_name from employee where post="sale";

	# 2. 部门是teacher , 收入大于10000的所有数据
	select * from employee where post = "teacher" and salary > 10000;
	
	# 3. 收入在1万到2万之间的所有员工姓名和收入
	select emp_name,salary from employee where salary between 10000 and 20000;
	
	# 4. 收入不在1万到2万之间的所有员工姓名和收入
	select emp_name,salary from employee where salary not between 10000 and 20000;
	
	# 5. 查看岗位描述为NULL的员工信息
	select emp_name from employee where post_comment = null;
	select emp_name from employee where post_comment = '';
	select emp_name from employee where post_comment is null;
	
	# 6. 查看岗位描述不为NULL的员工信息
	select emp_name from employee where post_comment is not null;
	
	# 7. 查询收入是3000 ,4000 ,5000,8300 所有员工的姓名和收入
	select emp_name,salary from employee where salary in(3000,4000,5000,8300);
	select emp_name,salary from employee where salary = 3000 or salary=4000 or salary=5000 or salary=8300;
	
	# 8. 查询收入不是3000 ,4000 ,5000,8300 所有员工的姓名和收入
	select emp_name,salary from employee where salary not in(3000,4000,5000,8300);

	# 9. 以on结尾的员工名搜一下
	select emp_name from employee where emp_name like "%on";
	select emp_name from employee where emp_name like "ji%";
	select emp_name from employee where emp_name like "_le_";
	
	# 10. 统计员工一年的年薪
	select concat(" 姓名: ",emp_name,"  收入:  ",salary) from employee;
	
	# 计算年薪,可以在mysql中使用四则运算符 + - * / 
	select concat(" 姓名: ",emp_name,"  收入:  ",salary * 12) from employee;
	select concat_ws("  :  ",emp_name,salary*12 ) from employee;
	
	# 11. 查询部门的种类
	# distinct  返回唯一不同的值
	select distinct(post)  from employee;
	
	
# 二.group by 子句 分组分类
	"""group by 字段,对数据进行分类, by后面接什么字段,select后面就搜什么字段"""
	select sex from  employee group by sex;
	# group_concat 按照分组把对应字段拼在一起;
	select group_concat(emp_name),post from  employee group by post;
	
	# 聚合函数
		# count 统计总数 *所有
		select count(*) from employee;
		# max  统计最大值
		select max(salary) from employee;
		# min  统计最小值
		select min(salary) from employee;
		# avg  统计平均值
		select avg(salary) from employee;
		# sum  统计总和
		select sum(salary) from employee;
		
	# 1. 查询部门名以及各部门的平均薪资
	select avg(salary),post from employee group by post;
	# 2. 查询部门名以及各部门的最高薪资
	select max(salary),post from employee group by post;
	# 3. 查询部门名以及各部门的最低薪资
	select min(salary),post from employee group by post;
	# 4. 查询公司内男员工和女员工的个数
	select count(*),sex from employee group by sex;
	# 5. 查询部门名以及部门包含的所有员工名字
	select group_concat(emp_name),post from employee group by post;
	# 6 可以group by 两个字段,就可以同时搜索两个字段
	select emp_name,post from employee group by post ,emp_name;
	

# 三.having 在数据分类分组之后,对数据进行二次过滤,一般配合group by来使用的;
	# 找出各部门平均薪资,并且大于10000
	select post , avg(salary) from  employee group by post having avg(salary) > 10000

	# 1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
	# 对于没有group的字段 应用 group_concat
	select post , group_concat(emp_name), count(*) from employee group by post having count(*) < 2;
	# 2.查询各岗位平均薪资小于10000的岗位名、平均工资
	select post , avg(salary) from employee group by post having avg(salary) < 10000
	# 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
	select post, avg(salary) from employee group by post having avg(salary) between 10000 and 20000
	select post, avg(salary) from employee group by post having avg(salary) > 10000 and  avg(salary) < 20000;
	
	
# 四.order by 排序 , 按照某字段排序
	order by age asc (升序) order by age desc (降序)
	# 按照年龄从小到大排序
	select * from employee order by age;
	# 按照年龄从大到小排序
	select * from employee order by age desc;
	
	# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
	select * from employee order by age asc ,  hire_date desc;
	
	# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
	select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc
	
	# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
	select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc
	
# 五.limit 限制查询条数 (应用在分页)
	""" limit m,n m代表从第几条数据开始查, n 代表查几条  m=0 代表的是第一条数据"""
	select * from employee limit 0,10   # 0代表的是第一条数据
	select * from employee limit 10,10  # 10代表的是第十一条数据
	select * from employee limit 20,10  # 20代表的是第二十一条数据
	
	# limit + num  num => 搜索的条数据
	select * from employee limit 1
	# 搜索这个表里面最后一条数据
	select * from employee order by id desc limit 1
	# 搜索这个表里面最后五条数据
	select * from employee order by id desc limit 5
	
	
# 六.mysql 当中可以使用正则表达式 (不推荐,效率低)
	select * from employee where  emp_name regexp ".*on$"; # mysql中无法识别?
	select * from employee where  emp_name regexp "^程.*";
	select * from employee where  emp_name regexp "^程.*金";

多表查询#

# 多表练习:
#建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

链接#

# 两表联查
    select 字段 from 表1 链接词 表2 on 必要的关联条件
# 多表联查
    select 字段 from 表1 链接词 表2 on 必要的关联条件1 inner join 表3 on 必要的关联条件2 
    
# 表后可以跟 as 取别名 也可以省略

内链接#

inner join  : 
-- 两表或者多表之间,把满足条件的所有数据查询出来 (多表之间共同拥有的数据会被查询出来)

where
# where 写法默写是内联接( 等同于inner join )
select * from employee,department where employee.dep_id = department.id;
select * from employee as e ,department as d where e.dep_id = d.id;

外链接#

left join 左联接   -- 以左表为主,右表为辅,完整查询左表所有数据,右表没有的数据补null
right join 右联接  -- 以右表为主,左表为辅,完整查询右表所有数据,左表没有的数据补null

全联接 : union#

union
# 左联接与右联接之间用一个union

例子#

# 1.内联接 :  inner join  :  两表或者多表之间,把满足条件的所有数据查询出来 (多表之间共同拥有的数据会被查询出来)
    # 两表联查
    select 字段 from 表1 inner join 表2 on 必要的关联条件
    # 多表联查
    select 字段 from 表1 inner join 表2 on 必要的关联条件1 inner join 表3 on 必要的关联条件2 

select * from employee inner join department on employee.dep_id = department.id;

# as 起别名
select * from employee as e inner join department as d on e.dep_id = d.id;

# 也可以省略as (不推荐)	
select * from employee e inner join department d on e.dep_id = d.id;


# 2.外联接 :  left join左联接  / right join 右联接
# (1)left  join左联接 : 以左表为主,右表为辅,完整查询左表所有数据,右表没有的数据补null
select * from employee left join department on employee.dep_id = department.id;

# (2)right join右联接 : 以右表为主,左表为辅,完整查询右表所有数据,左表没有的数据补null
select * from employee right join department on employee.dep_id = department.id;

# 3.全联接 :  union
select * from employee left join department on employee.dep_id = department.id

union

select * from employee right join department on employee.dep_id = department.id;

子查询#

# ### part3 子查询 
	"""
	子查询: 嵌套查询
		(1) sql语句当中又嵌套了另外一条sql,用括号()进行包裹,表达一个整体
		(2) 一般用在from子句,where子句... 身后,表达一个条件或者一个表
		(3) 速度快慢: 单表查询 > 联表查询 > 子查询;
	"""


	# 一.找出平均年龄大于25岁以上的部门
	# (1) where
	select 
		d.id,d.name
	from 
		employee as e ,department as d
	where
		e.dep_id = d.id
	group by 
		d.id,d.name
	having
		avg(e.age) > 25
	
	# (2) inner join 
	select 
		d.id,d.name
	from 
		employee as e inner join department as d on e.dep_id = d.id
	group by 
		d.id,d.name
	having
		avg(e.age) > 25
		
	# (3) 子查询
	# 1.先找出平均年龄大于25岁的部门id
	select dep_id from employee group by employee.dep_id having avg(age)>25; # 201 202
	# 2.通过部门的id找部门的名字
	select name from department where id in (201,202);
	# 3.综合拼接:
	select id , name from department where id in (select dep_id from employee group by employee.dep_id having avg(age)>25);

综合比较#

# 二.查看技术部门员工姓名
	# (1) 普通的where 查询
select 
	e.id,e.name
from
	employee as e,department as d
where
	e.dep_id = d.id
	and
	d.name = "技术"
	
	# (2) inner join 
select 
	e.id,e.name
from
	employee as e inner join department as d on e.dep_id = d.id 
where
	d.name = "技术"
	
	# (3)子查询
	# (1) 找技术部门对应的id
	select id from department where name = "技术";
	# (2) 通过id找员工姓名
	select name from employee where dep_id = 200;
	# (3) 综合拼接
	select id,name from employee where dep_id = (select id from department where name = "技术");
	
	# 三.查看哪个部门没员工
	
	# 联表写法
	select
		d.id,d.name
	from
		department as d left join employee as e on d.id = e.dep_id
	where
		e.dep_id is null	
	
	
	# 1.找员工在哪些部门 (200  201  202 204)
	select dep_id from employee  group by dep_id
	# 2.把不在该部门的员工找出来
	select  id  from department where id not in (200,201,202,204);
	# 3.综合拼接
	select  id,name  from department where id not in (select dep_id from employee  group by dep_id);
	
	department;
	+------+--------------+
	| id   | name         |
	+------+--------------+
	|  200 | 技术         |
	|  201 | 人力资源     |
	|  202 | 销售         |
	|  203 | 运营         |
	+------+--------------+
	employee;
	+----+------------+--------+------+--------+
	| id | name       | sex    | age  | dep_id |avg(age) 
	+----+------------+--------+------+--------+
	|  1 | egon       | male   |   18 |    200 |  18
	|  2 | alex       | female |   48 |    201 |  43
	|  3 | wupeiqi    | male   |   38 |    201 |  43
	|  4 | yuanhao    | female |   28 |    202 |  28
	|  5 | liwenzhou  | male   |   18 |    200 |  18
	|  6 | jingliyang | female |   18 |    204 |  18
	+----+------------+--------+------+--------+
	# 四.查询大于平均年龄的员工名与年龄
	# 假设已经知道了平均年龄;
	select name,age from employee where age > 30;
	# 计算平均年龄
	select avg(age) from employee;
	# 综合拼接
	select name,age from employee where age > (select avg(age) from employee);
	
	
	
	# 五.把大于其本部门平均年龄的员工名和姓名查出来
	# 1.先计算本部门的平均年龄是多少
	select dep_id , avg(age) from employee  group by dep_id;	
	+--------+----------+
	| dep_id | avg(age) |
	+--------+----------+
	|    200 |  18.0000 |
	|    201 |  43.0000 |
	|    202 |  28.0000 |
	|    204 |  18.0000 |
	+--------+----------+

	# 2.把查询的各部门平均年龄和employee进行联表,变成一张大表,最后做单表查询
	select 
		*
	from
		employee as t1 inner join (1号查询出来的数据) as t2 on t1.dep_id = t2.dep_id
	
	# 3.综合拼装
select 
	*
from
	employee as t1 inner join (select dep_id , avg(age) as avg_age from employee  group by dep_id) as t2 on t1.dep_id = t2.dep_id
	
	# 4.最后做一次单表查询,让age > 平均值	
select 
	*
from
	employee as t1 inner join (select dep_id , avg(age) as avg_age from employee  group by dep_id) as t2 on t1.dep_id = t2.dep_id
where 
	age >avg_age
	
	
	# 六.查询每个部门最新入职的那位员工  # 利用上一套数据表进行查询;
	employee
	+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
	| id | emp_name   | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |    max_date
	+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
	|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              |              |    7300.33 |    401 |         1 | 2017-03-01
	|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 | 2015-03-02
	|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 | 2015-03-02 
	|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 | 2015-03-02
	|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 | 2015-03-02
	|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 | 2015-03-02
	|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 | 2015-03-02
	|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 | 2015-03-02
	|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 | 2017-01-27
	| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 | 2017-01-27
	| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 | 2017-01-27
	| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 | 2017-01-27
	| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 | 2017-01-27
	| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 | 2016-03-11
	| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 | 2016-03-11
	| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 | 2016-03-11
	| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 | 2016-03-11
	| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 | 2016-03-11
	+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
	
	# 1.找各部门的最新入职的时间
	select post,max(hire_date) as max_date from employee group by post
	
	+-----------------------------------------+------------+
	| post                                    | max_date   |
	+-----------------------------------------+------------+
	| operation                               | 2016-03-11 |
	| sale                                    | 2017-01-27 |
	| teacher                                 | 2015-03-02 |
	| 老男孩驻沙河办事处外交大使             | 2017-03-01 |
	+-----------------------------------------+------------+
	
	# 2.把子查询搜索出来的结果作为一张表和employee这个表做联表,把max_date拼接在employee这个表中,变成一张大表,最后做一次单表查询
	select 
		*
	from
		employee as t1 inner join (1号数据) as t2 on t1.post = t2.post
	where
		t1.hire_date = t2.max_date
		
	# 3.综合拼装
select 
	emp_name , max_date
from
	employee as t1 inner join (select post,max(hire_date) as max_date from employee group by post) as t2 on t1.post = t2.post
where
	t1.hire_date = t2.max_date

EXISTS#

# 七.带EXISTS关键字的子查询
	"""
	exists 关键字 , 表达存在 , 应用在子查询中
		如果内层sql , 能够查到数据, 返回True ,  外层sql执行相应的sql语句
		如果内层sql , 不能查到数据, 返回False , 外层sql不执行sql语句
	"""
	select * from employee where exists (select * from employee where id = 1);
	select * from employee where exists (select * from employee where id = 100000);
	
	
	"""
	总结: 
		子查询可以单独作为临时数据,作为一张表或者一个字段,通过()进行包裹,表达一个整体;
		一般用在from,where,select.子句的后面
		可以通过查询出来的数据和另外的表做联表变成更大一张表,
		最后做单表查询,达到目的;
	"""

数据库的导入和导出#

导入#

"""
    导出数据库
    1.退出mysql
    2.选择要导出的默认路径
    3.mysqldump -uroot -p db001 > db001.sql
    mysqldump -uroot -p 数据库名 > 文件形式  导出到当前的cmd文件
    mysqldump -uroot -p 数据库名 表1 表2 > 文件形式  导出到当前的cmd文件
"""

导出#

 ""
    导入数据库
    1.登录到mysql之后
    2.创建新的数据库
    4.use 需要导入的数据库
    3.source 路径+文件  # 不要加分号
 """

数据库崩了时候#

myisam#

# myisam 直接拷贝文件即可

Innodb#

# innodb 在只有frm和ibd文件的情况下,如何恢复数据;

安装 MySQL Utilities
https://downloads.mysql.com/archives/utilities/ 

cmd中找到frm那个文件,执行如下命令:
切换到对应目录,执行下面语句,不要加分号
mysqlfrm --diagnostic ./文件目录/t1.frm

查出建表语句,复制查询出来的建表语句在mysql中创建的新数据中使用

CREATE TABLE `innodb1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB;

-------------------------------------------------------

#对已创建的表进行表空间卸载 删除ibd文件  卸载新建的表的idb文件
mysql> alter table innodb1 discard tablespace;


把要恢复的idb文件替换进去


#对已创建的表进行空间装载			对恢复的idb文件进行替换
mysql> alter table innodb1 import tablespace;

posted @ 2024-04-10 23:45 江寒雨阅读(26) 评论(0) 编辑收藏举报

刷新页面返回顶部

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

相关博文：

· pymysql模块

· Python基础

· MySQL 基础操作

· Mysql基本部分笔记

· 2022-08-15 吉林化工学院第五组韩嘉宁（MySQL基础）

阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 如何调用 DeepSeek 的自然语言处理 API 接口并集成到在线客服系统
· 【译】Visual Studio 中新的强大生产力特性
· 2025年我用 Compose 写了一个 Todo App

公告

昵称：江寒雨
园龄： 1年1个月
粉丝： 0
关注： 3

+加关注

2025年3月

日

一

二

三

四

五

六

加载中...

七情六欲

想观测这个世界

Mysql基础

Mysql

Mysql的介绍#

windows下Mysql的安装#

mysql在Linux中更改编码#

Mysql的快捷键#

数据库的状态操作#

登录数据库#

退出数据库#

查询当前登录用户#

设置密码#

去除密码#

新建用户#

授权语法#

数据库内部方法#

操纵数据库#

创建数据库#

查看数据库#

查看数据库建库语句#

修改数据库#

删除数据库#

操作数据表#

选择数据库#

创建表#

修改表名#

删表#

查看所有表#

查看建表语句#

查看表结构#

修改表的字段属性#

modify#

change#

add#

drop#

操纵记录#

插入数据#

查询数据#

修改数据#

删除数据#

#

数据类型#

整形#

tinyint#

int#

浮点型（位数，小数位数）#

float(255,30)#

double(255,30)#

decimal(60,30)#

字符串 (字符个数/字符长度)#

char(255)#

varchar(21845)#

text#

枚举和集合#

enum#

set#

时间类型#

date#

time#

year#

datetime#

timestamp#

约束#

unsigned 无符号#

not null 不为空#

default 默认值#

unique 唯一值 加入唯一索引（为了加快速度，不可多加）#

primary key 主键 [唯一 + 不为null]#

auto_increment 自增加一（配和 主键 或者 unique 使用）#

zerofill 零填充（配合int 使用 ，作为零填充）#

foreign key 外键#

约束索引没有资格被删除#

外键被删除时候#

联级更新和删除#

联合唯一索引#

unique + not null#

unique#

联合唯一主键#

unique 唯一值加入唯一索引（为了加快速度，不可多加）#

auto_increment 自增加一（配和主键或者 unique 使用）#

zerofill 零填充（配合int 使用，作为零填充）#