目 录CONTENT

文章目录

四、MySQL操作详细用法总结

ZOUNAN
2021-12-20 / 0 评论 / 0 点赞 / 592 阅读 / 28,470 字
温馨提示:
本文最后更新于 2022-03-05,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

搭建数据库服务器

mysql

安装软件、启动服务

tar -xf mysql-5.7.17.tar
yum -y install mysql-community-*.rpm
systemctl start mysqld
systemctl enable mysqld
netstat -utnlp | grep 3306
ps -C mysqld
# 注意,与系统自带的mariadb软件冲突,不能同时安装

初始配置

  • 默认仅允许root本机连接
  • 首次登录密码在安装软件时随机生成
  • 随机密码在日志文件/var/log.mysql.log里
  • mysql -h 数据库地址 -uroot -p密码
grep 'password' /var/log/mysqld.log
2021-09-26T03:13:28.754966Z 1 [Note] A temporary password is generated for root@localhost: n>VyttrLX3#l

mysql -uroot -p'n>VyttrLX3#l'

# 修改密码
alter user root@"localhost" identified by "密码";
mysql -uroot -p密码;

# eg
alter user root@"localhost" identified by "Passw0rd!";

修改密码策略

  • 默认密码策略是1。 8位、数字、大小写与特殊字符
# 查看变量
show variables like "%password%";
## 临时有效(命令行)
# 修改策略为0
set global validate_password_policy=0;
# 修改密码长度
set global validate_password_length=6;


## 永久生效
vim /etc/my.cnf
[mysqld]
validate_password_policy=0
validate_password_length=6

image-20210926111911533

命令行连接数据库服务

命令行连接

mysql -hlocalhost -uroot -ptedu.cn sys
# 最后一行为系统数据库

查看当前所在的库

select databases();

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql>
1. 使用\c取消结束
show databases
\c

2.命令不区分大小写

3.默认不支持tab不齐

4.查看已连接的用户
select user();

SQL分类

  • 常用的sql命令分类
    • DDL
    • DML
    • DCL
    • DTL

库名要求

  • 仅可以使用数字、字母、下划线、不能纯数字
  • 区分字母大小写、具有唯一性
  • 不可以使用指令关键字、特殊字符
create database DB1;
create database db1;

表管理命令

create table 库名.表名(字段名 宽度, 字段名 宽度) default charset=utf8;  //指定中文字符,可以给字段赋值中文。

# 创建表后,会在mysql下会创建对应的文件
create table db1.stuinfo(姓名 char(10), age int) default charset=utf8;
[root@hecs-268354 db1]# ls
@5b66@751f@8868.frm  @5b66@751f@8868.ibd  db.opt  stuinfo.frm  stuinfo.ibd
[root@hecs-268354 db1]#
  • 查看表结构

    desc命令

    desc stuinfo;
    # 如果是绝对路径
    # desc db1.stuinfo
    
    mysql> desc stuinfo;
    +--------+----------+------+-----+---------+-------+
    | Field  | Type     | Null | Key | Default | Extra |
    +--------+----------+------+-----+---------+-------+
    | 姓名   | char(10) | YES  |     | NULL    |       |
    | age    | int(11)  | YES  |     | NULL    |       |
    +--------+----------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    mysql>
    
  • 管理记录

# 查看
select * from stuinfo;

# 插入
insert into db1.stuinfo values("bob", 21);
insert into db1.stuinfo values("lucy", 18);
mysql> select * from stuinfo;
+--------+------+
| 姓名   | age  |
+--------+------+
| bob    |   21 |
| lucy   |   18 |
+--------+------+
2 rows in set (0.00 sec)

# 修改
updata db1.stuinfo set age=29 where 姓名="bob";
mysql> select * from stuinfo;
+--------+------+
| 姓名   | age  |
+--------+------+
| bob    |   29 |
| lucy   |   18 |
+--------+------+
2 rows in set (0.00 sec)

mysql>

# 删除
# 删所有行
delete from db1.stuinfo;
mysql> delete from db1.stuinfo;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from stuinfo;
Empty set (0.00 sec)

mysql>

数据类型

  • 数值型:体重、身高、成绩、工资
  • 字符型 :姓名、工作单位、通信住址
  • 枚举型: 兴趣爱好、性别、专业
  • 日期时间型: 出生时间、注册时间

定长char

  • 最大字符个数据255
  • 不够指定字符个数时在右边用空格补全
  • 字符个数超出时,无法写入数据
mysql> create table t1 (name char(3), homeaddr char(10));
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| 学生表        |
| stuinfo       |
| t1            |
+---------------+
3 rows in set (0.00 sec)

mysql>

# 报错
mysql> insert into db1.t1 values("lucy", "china")
    -> ;
ERROR 1406 (22001): Data too long for column 'name' at row 1

变长varchar

  • 按数据实际大小分配存储空间
  • 65532
# 比如邮件地址,它不固定
varchar(50)


create table  db1.t2 (class char(7), email varchar(30));
mysql> insert into db1.t2 values("nsd2103", '3036@qq.com')
    -> ;
Query OK, 1 row affected (0.01 sec)

大文件类型:text/blob

  • 字符数大于65535存储时使用
  • 通常存储访问地址

数值类型

整数型(只能存整数)

类型名称有符号范围无符号范围
tinyint微小整数-128~1270~255
smallint小整数- 32768~327670~65535
mediumint中整数-223 ~ 223-10 ~ 2^24-1
int大整数^31^32
bigint极大整数^63^64
unsigned使用无符号存储范围

浮点型

image-20211002111034448

# 采用无符号存储年龄,圆周率使用double存储
create table t3(name char(10), age tinyint unsigned, level tinyint, yzl double);
mysql> desc t3;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | YES  |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
| level | tinyint(4)          | YES  |     | NULL    |       |
| yzl   | double              | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

# 插入一行数据
mysql> insert into t3 values('bob', 19, 125, 3.1415926);
Query OK, 1 row affected (0.00 sec)

如果为整数类型,存入小数时,会四舍五入

指定小数位的位数

# float(5, 2) 第一位是总位数,第二位是小数位数,最大能存储到99999.99
create table t5(name char(10), pay float(7,2));

insert into t5 values("jim", 3999);

mysql> insert into t5 values("jim", 3999);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t5 values("jim", 2.433);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+------+---------+
| name | pay     |
+------+---------+
| jim  | 3999.00 |
| jim  |    2.43 |
+------+---------+
2 rows in set (0.00 sec)

mysql>

如果小数位大于设定值,会四舍五入

日期

  • date

范围:0001-01-01~9999-12-31

格式:yyyymmdd 19491901

  • year

范围:1901~2155

格式:yyyy 1998

要求四位数

当使用两位时:01-99

01-69视为: 2001~2069

70-99视为:1970~1999

  • time

格式:HHMMSS 221828

  • 日期时间 datetime

范围:1000-01-01 00:00:00~9999-12-21 23:59:59

格式:yyyymmddhhmmss

  • 日期时间timestamp

范围: 1970-01-01 00:00:00 ~ 2038-01-19 00:00:00

格式:yyyymmddhhmmss

当未给timestamp字段赋值时,自动以当前系统时间赋值,而datetime值为NULL

mysql> create table t7 (name char(10), birthday date, csnf year, up_class time, party datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t7;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(10) | YES  |     | NULL    |       |
| birthday | date     | YES  |     | NULL    |       |
| csnf     | year(4)  | YES  |     | NULL    |       |
| up_class | time     | YES  |     | NULL    |       |
| party    | datetime | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>

# 插入对应的值
mysql> insert into t7 values("alices", 20211120, 1990, 193000, 20220101183000);
Query OK, 1 row affected (0.01 sec)

时间函数

img

# 获取当前操作系统的时间
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-10-03 |
+------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-10-03 10:16:19 |
+---------------------+
1 row in set (0.00 sec)

mysql>

# 获得单独的年份或月份
mysql> select time(now());
+-------------+
| time(now()) |
+-------------+
| 10:17:24    |
+-------------+
1 row in set (0.00 sec)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2021 |
+-------------+
1 row in set (0.00 sec)

mysql>

利用时间函数获得的值赋值

insert into t7 values("jerry", curdate(), year(now()), time(now()), now());

# 注意year的时间写为两位数时,要注意对应关系
mysql> insert into db1.t7 values("koko", curdate(), 99, time(now()), now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from db1.t7 where name='koko';
+------+------------+------+----------+---------------------+
| name | birthday   | csnf | up_class | party               |
+------+------------+------+----------+---------------------+
| koko | 2021-10-03 | 1999 | 10:23:41 | 2021-10-03 10:23:41 |
+------+------------+------+----------+---------------------+
1 row in set (0.00 sec)

mysql>

当未给timestamp字段赋值时,自动以当前系统时间赋值,而datetime值为NULL

create table t8(meeting datetime, party timestamp);

# 存第一行数据
insert into t8 values(19981120213058, 20231120154828);
# 存第二行数据
insert into t8(metting) values(20191120213058);
# 存储之后发现以当前系统的时间来赋值
mysql> select * from t8;
+---------------------+---------------------+
| meeting             | party               |
+---------------------+---------------------+
| 1998-11-20 21:30:58 | 2023-11-20 15:48:28 |
| 2019-11-20 21:30:58 | 2021-10-03 10:30:27 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql>

# 而datetime值为NULL
mysql> insert into t8(party) values(20201120213058);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
+---------------------+---------------------+
| meeting             | party               |
+---------------------+---------------------+
| 1998-11-20 21:30:58 | 2023-11-20 15:48:28 |
| 2019-11-20 21:30:58 | 2021-10-03 10:30:27 |
| NULL                | 2020-11-20 21:30:58 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

枚举类型

  • enum 单选:

    • 格式:字段名 enum(值1,值2,值n)
    • 仅能在列表里选择一个值
    mysql> create table db1.t5(name char(5), sex enum("boy", "girl", "no"));
    # 报错
    mysql> insert into db1.t5 values("bob", "man");
    
  • set 多选

    • 格式:字段名 set(值1, 值2, 值N)
    • 在列表里选择一个或多个
    create table db1.t6(name char(5), likes set("eat", "game", "music", "money"));
    # 报错
    mysql> insert into db1.t5 values("jerry", "eat,film");
    
    
    mysql> create table t9(name char(10), gender enum("boy", "girl", "no"),  likes set("eat","sleep","game","money"));
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> desc t9;
    +--------+-----------------------------------+------+-----+---------+-------+
    | Field  | Type                              | Null | Key | Default | Extra |
    +--------+-----------------------------------+------+-----+---------+-------+
    | name   | char(10)                          | YES  |     | NULL    |       |
    | gender | enum('boy','girl','no')           | YES  |     | NULL    |       |
    | likes  | set('eat','sleep','game','money') | YES  |     | NULL    |       |
    +--------+-----------------------------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    
    mysql> insert t9 values("aaa", "boy", "eat,sleep");
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from t9;
    +------+--------+-----------+
    | name | gender | likes     |
    +------+--------+-----------+
    | aaa  | boy    | eat,sleep |
    +------+--------+-----------+
    1 row in set (0.00 sec)
    
    mysql>
    

约束条件

  • 作用,限制字段赋值0
  • 设置约束条件
    • null 允许为空
    • not null 不允许为null(空)
    • key 键值类型
    • default 设置默认值,缺省为NULL
    • extra 额外设置
create table t10(name char(10) not null, gender enum("boy", "girl") default "boy", class char(7) not null default "nsd2112");

mysql> desc t10;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| name   | char(10)           | NO   |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
| class  | char(7)            | NO   |     | nsd2112 |       |
+--------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

# 插入行
mysql> insert into t10(name) values("bob");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t10;
+------+--------+---------+
| name | gender | class   |
+------+--------+---------+
| bob  | boy    | nsd2112 |
+------+--------+---------+
1 row in set (0.00 sec)

mysql>

修改表结构

  • 用法
  • mysql> alter table 库名.表名 执行动作
    • add 添加字段
    • modify 修改字段类型
    • change 修改字段名
    • drop 删除字段
    • rename 修改表名
mysql> desc t1;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(3)  | YES  |     | NULL    |       |
| homeaddr | char(10) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)


# 添加列
mysql> desc t1;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(3)  | YES  |     | NULL    |       |
| homeaddr | char(10) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table t1 add class char(7) not null default "nsd2023";
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(3)  | YES  |     | NULL    |       |
| homeaddr | char(10) | YES  |     | NULL    |       |
| class    | char(7)  | NO   |     | nsd2023 |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>


# 把列添加到第一列
alter table t1 add stu_num int first;
# 指定把列放在具体的位置
mysql> alter table t1 add age tinyint unsigned after name;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| stu_num  | int(11)             | YES  |     | NULL    |       |
| name     | char(3)             | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| homeaddr | char(10)            | YES  |     | NULL    |       |
| class    | char(7)             | NO   |     | nsd2023 |       |
+----------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>

修改字段

  • 修改的字段不能与已经存在数据冲突
    • 可以修改为有值
    • 删除此列,重新建立

调整字段的位置

# 把class列修改到stu_num的后面
alter table t1 modify class char(7) not null default "nsd2023" after stu_num;

删除字段

# 同时删除两列
alter table t1 drop stu_num, drop homeaddr;

修改字段名字

  • alter table 库名.表名 change 原字段名 新字段名 类型 约束条件
mysql> alter table t1 change name 姓名 char(3);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1
    -> ;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| 姓名   | char(3)             | YES  |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| class  | char(7)             | NO   |     | nsd2023 |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改表名

alter table t1 rename 学生信息表;

索引

  • index 普通索引
  • unique 唯一索引
  • fulltext 全文索引
  • primary key 主键
  • foreign key 外键

索引是什么?

  • 类似于书的目录
  • 对表中字段值进行排序
  • 索引算法:Btree / B + tree / hash

优缺点?

  • 可以加快数据的查询速度
  • 当表中的数据进行增加,删除和修改的时候,索引也要动态的调整,降低了数据的维护速度
  • 索引需要占物理空间

适用于

  • 适用于selelct的操作远大于修改插入等操作
  • 通常把做为查询条件的字段设置为index字段
  • index 字段 标志不MUL(在key那里)
  • 字段的值允许重复,且可以赋NULL值

创建索引

  • create index 索引名 on 表名(字段名)
  • drop index 索引名 on 表名
mysql> desc t9;
+--------+-----------------------------------+------+-----+---------+-------+
| Field  | Type                              | Null | Key | Default | Extra |
+--------+-----------------------------------+------+-----+---------+-------+
| name   | char(10)                          | YES  | MUL | NULL    |       |
| gender | enum('boy','girl','no')           | YES  |     | NULL    |       |
| likes  | set('eat','sleep','game','money') | YES  |     | NULL    |       |
+--------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

查看创建索引的详细信息

mysql> show index from t9 \G;
*************************** 1. row ***************************
        Table: t9
   Non_unique: 1
     Key_name: xxx
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified0

mysql>

建表的时候创建索引

mysql> create table t12( name char(10), class char(7), email varchar(30), age int, index(name), index(class));
Query OK, 0 rows affected (0.02 sec)

mysql>

删除索引

  • 要先查索引名
show index from t12 \G;  # 如果是在创建时建立的,那么索引名与字段名一样
drop index class on t12;


mysql> desc t12;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | char(10)    | YES  | MUL | NULL    |       |
| class | char(7)     | YES  |     | NULL    |       |
| email | varchar(30) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

主键primary key

  • 字段值不允许重复,且不允许赋NULL值 ,设置为主键后,它会自动设置为不允许空值
  • 一个表中只能有一个primary key字段
  • 多个字段都作为主键,称为复合主键,必须一起创建
  • 主键字段的标志是PRI
  • 主键通常与auto_increment连用
  • 通常把表中唯一标识记录的字段[记录编号字段]设置为主键
定义主键通常有两种方式
create table t8(name char(3) primary key, age int);
create table t8(name char(3) age int, primary key(name));

删除主键

  • alter table 表名 drop primary key;
  • 删除主键前,如果有自增属性,必须先去掉000
alter table t13 drop primary key;

# 添加主键
mysql> alter table t9 add primary key(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

与auto_increment连用

  • 字段名 类型 primary key auto_increment
  • 字段通过自加1方式赋值
  • 必须要是数值类型
create table t6(id int primary key auto_increment, name char(10), sex enum("man", "woman"));

mysql> desc t15;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(11)             | NO   | PRI | NULL    | auto_increment |
| name  | char(10)            | YES  |     | NULL    |                |
| sex   | enum('man','woman') | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

复合主键

  • 表中多个字段一起做主键
  • 插入记录时,不允许主键字段值同时重复,同时!!!
  • 创建时,需要一起指定
create table t17( cip varchar(15), s_port smallint, status enum("allow", "deny"), primary key(cip, s_port));

+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| cip    | varchar(15)          | NO   | PRI | NULL    |       |
| s_port | smallint(6)          | NO   | PRI | NULL    |       |
| status | enum('allow','deny') | YES  |     | NULL    |       |
+--------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>
mysql> insert into t17 values("1.1.1.1", 22, "allow");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t17 values("1.1.1.1", 23, "allow");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t17;
+---------+--------+--------+
| cip     | s_port | status |
+---------+--------+--------+
| 1.1.1.1 |     22 | allow  |
| 1.1.1.1 |     23 | allow  |
+---------+--------+--------+
2 rows in set (0.00 sec)

mysql> 

删除复合主键

alter table t17 drop primary key;

添加复合主键

alter table t17 add primary key(cip, s_port);

外键

功能:插入表记录时,字段值在另一个表字段范围内选择

目的:为了保证数据的一致性

  • 使用规则
    • 表存储引擎必须是innodb
    • 字段类型要一致
    • 被参照字段必须要是索引类型的一种(primary key)
create table 表名(字段名列表, foreign key(字段名) references 表名(字段名) //指定外键
                on update cascade //同步更新
                on delete cascade //同步删除
               )engine=innodb; //存储引擎

例子

1.员工表
2.工资表

create table yg(yg_id int primary key auto_increment, name varchar(4)) engine=innodb;
create table gz(gz_id int, gz float(7,2), foreign key(gz_id) references yg(yg_id) on update cascade on delete cascade)engine=innodb;
# show table status from db1;
# 查看存储引擎


# 插入数据
mysql> insert into yg(name) values("bob");
Query OK, 1 row affected (0.00 sec)

mysql> insert into yg(name) values("tom");
Query OK, 1 row affected (0.00 sec)

mysql> insert into yg(name) values("lucy");
Query OK, 1 row affected (0.00 sec)

mysql> select * from yg;
+-------+------+
| yg_id | name |
+-------+------+
|     1 | bob  |
|     2 | tom  |
|     3 | lucy |
+-------+------+
3 rows in set (0.00 sec)

mysql> insert into gz(gz_id, gz) values(1, 15000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into gz(gz_id, gz) values(4, 15000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql>

同步更新的限制(主表变,从表相应变)

mysql> select * from gz;
+-------+----------+
| gz_id | gz       |
+-------+----------+
|     1 | 15000.00 |
+-------+----------+
1 row in set (0.00 sec)

mysql> update yg set yg_id=8 where yg_id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from gz;
+-------+----------+
| gz_id | gz       |
+-------+----------+
|     8 | 15000.00 |
+-------+----------+
1 row in set (0.00 sec)

mysql>

同步删除

mysql> delete from yg where yg_id=8;
Query OK, 1 row affected (0.01 sec)

mysql> select * from gz;
Empty set (0.00 sec)

mysql>

处理外键不唯一一问题

mysql> insert into gz(gz_id, gz) values(2, 15000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into gz(gz_id, gz) values(2, 15000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from gz;
+-------+----------+
| gz_id | gz       |
+-------+----------+
|     2 | 15000.00 |
|     2 | 15000.00 |
+-------+----------+
2 rows in set (0.00 sec)

mysql> insert into gz(gz_id, gz) values(null, 15000);
Query OK, 1 row affected (0.00 sec)
# 修改为住建
alter table gz add gz_id int not null primary key;

删除外键

  • 查看外键 show create table 表名 \G
  • 命令格式 alter table 表名 drop foreign key 名称;
  • 添加外键 alter table 表名 add foreign key(字段名) on update cacsade on delete cascade;
mysql> show create table gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) NOT NULL,
  `gz` float(7,2) DEFAULT NULL,
  PRIMARY KEY (`gz_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

# 这里的gz_ibfk_1是外键名
alter table gz drop foreign key gz_ibfk_1;

# 再重新加上
alter table gz add foreign key(gz_id) references yg(yg_id) on update cascade on delete cascade; 

数据导入

查看默认设置

  • 安装mysql服务软件时,会自动创建检索目录
show variables like "secure_file_priv";

mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

mysql>


show variables like "%file%";

# 执行系统命令
mysql> system ls /var/lib

修改检索目录

mkdir /myload
chown mysql /myload
vim /etc/my.cnf
	[mysqld]
	secure_file_priv="/myload"
systemctl restart mysqld

mysql> show variables like "secure_file_priv";
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| secure_file_priv | /myload/ |
+------------------+----------+
1 row in set (0.00 sec)

mysql>

案例:

mysql> load data infile "/目录名/文件名"
into table 库名.表名
fields terminated by "符号"  # 列间隔符号
lines terminated by "\n"    # 行结束符号11
  • 将/etc/passwd文件导入db3库中user表里,并添加行号字段
create database db3;
create table db3.user(name char(30), password char(1), uid int, gid int, comment varchar(150), homeaddr char(100), shell char(50));

system cp /etc/passwd /myload/

# load data infile "/myload/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";
mysql> load data infile "/myload/passwd" into table db3.user
    -> fields terminated by ":" lines terminated by "\n";
Query OK, 22 rows affected (0.00 sec)
Records: 22  Deleted: 0  Skipped: 0  Warnings: 0

# 加上行号
mysql> alter table db3.user add id int primary key auto_increment first;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
  • 字段分割符要与文件一致
  • 表字段类型和字段个数要与文件内容匹配
  • 导入数据时指定文件的绝对路径

数据导出

  • 把表里的记录保存到系统文件里

    • 格式1,把查询的结果导出

      select 命令 into outfile "/目录名/文件名";
      # 如下,目录是系统指定的目录,文件名自定义
      mysql> select * from db3.user into outfile "/myload/user";
      Query OK, 22 rows affected (0.00 sec)
      
      mysql>
      
    • 格式2, 可以指定导出时以指定分隔符分隔

      select 命令 into outfile "/目录名/文件名" fields terminated by '分隔符';
      
      select * from db3.user into outfile "/myload/user" fields terminated by ":";
      
    • 格式3,指定每列的分隔符,默认为\n

      # select 命令 into outfile "/目录名/文件名" fields terminated by '分隔符' lines terminated by "分割符";
      select * from db3.user into outfile "/myload/user" fields terminated by ":" lines terminated by "###";
      

      导出的结构完全取决于前面的查询命令

插入表记录

  • 格式1:添加1条记录,给所有字段赋值

    insert into 表名 values(字段值列表);
    
  • 格式2:添加多条记录,给所有字段赋值

    insert into 表名 values(字段值列表),(字段值列表),(字段值列表);
    
  • 格式3:添加1条记录,给执行字段赋值

    insert into 表名(字段名列表) values (字段值列表);
    
  • 格式4:添加多条记录,给指定字段赋值

    insert into 表名(字段名列表) values (字段值列表),(字段值列表),(字段值列表)
    

修改表记录

  • 格式1,指量更新

    update 库名.表名
    	set 字段名=值, 字段名=值, 字段名=值,……;
    
  • 条件匹配更新

    update 库名.表名
    	set 字段名=值, 字段名=值, 字段名=值,…… 
    	where 条件表达式;
    

删除记录

  • 格式1,条件匹配删除

    delete from 库名.表名 where 条件表达示;
    
  • 格式2,删除所有记录(慎用)

    delete from 库名.表名
    

基本查询语句

范围匹配

  • 匹配范围名为人任意给我和值即可

    • in (值列表)
    mysql> select * from user where uid in (10, 20 ,30, 40);
    Empty set (0.00 sec)
    
    mysql> 
    
    • not in (值列表)
    mysql> select * from user where uid not in (10, 20 ,30, 40);
    Empty set (0.00 sec)
    
    mysql> 
    
    • between 数字 and 数字
    mysql> select * from user where uid between 1 and 4;
    +----+--------+----------+------+------+---------+----------------+---------------+
    | id | name   | password | uid  | gid  | comment | homeaddr       | shell         |
    +----+--------+----------+------+------+---------+----------------+---------------+
    |  2 | bin    | x        |    1 |    1 | bin     | /bin           | /sbin/nologin |
    |  3 | daemon | x        |    2 |    2 | daemon  | /sbin          | /sbin/nologin |
    |  4 | adm    | x        |    3 |    4 | adm     | /var/adm       | /sbin/nologin |
    |  5 | lp     | x        |    4 |    7 | lp      | /var/spool/lpd | /sbin/nologin |
    +----+--------+----------+------+------+---------+----------------+---------------+
    4 rows in set (0.00 sec)
    
    mysql> 
    

高级匹配

模糊查询

  • 用法(适用于select update delete操作)

where 字段名 like ‘通配符’

_ 表示一个字符

% 表示0~n个字符

  • 示例:例出name列,值是4个字符的记录
select name from user where name like "____";

正则表达式

  • 用法

where 字段名 regexp ‘正则表达示’

正则元字符 ^ $ . [] * |

^ 开头

$结尾

  • 示例
# 例出name值以J开头或以y结尾的记录
select * from db1.t2 where name regexp "^j | y$";

# 以范围内的任意一个开头
select * from db1.t2 where name regexp "^[abcd]";

# 任意一个开头,任意一个结尾,只包含三位数 
mysql> select name, uid from user where uid regexp '^...$';
+-----------------+------+
| name            | uid  |
+-----------------+------+
| systemd-network |  192 |
| polkitd         |  999 |
| chrony          |  998 |
+-----------------+------+
3 rows in set (0.00 sec)

mysql> 

# 以r开头 t结尾 中间有一个或多个字符
select * from name, uid from user where regexp '^r.*t$'; b   

运算符

mysql> select id, name, uid from user where uid < 5;
+----+--------+------+
| id | name   | uid  |
+----+--------+------+
|  1 | root   |    0 |
|  2 | bin    |    1 |
|  3 | daemon |    2 |
|  4 | adm    |    3 |
|  5 | lp     |    4 |
+----+--------+------+
5 rows in set (0.00 sec)

mysql> update user set uid = uid + 1 where uid < 5;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select id, name, uid from user where uid < 5;
+----+--------+------+
| id | name   | uid  |
+----+--------+------+
|  1 | root   |    1 |
|  2 | bin    |    2 |
|  3 | daemon |    3 |
|  4 | adm    |    4 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql> 


# 除法
mysql> select name, uid , (gid + uid) /2 as pjs from user  where uid < 5;
+--------+------+--------+
| name   | uid  | pjs    |
+--------+------+--------+
| root   |    1 | 0.5000 |
| bin    |    2 | 1.5000 |
| daemon |    3 | 2.5000 |
| adm    |    4 | 4.0000 |
+--------+------+--------+
4 rows in set (0.00 sec)

mysql> 


# 获得出生年份
mysql> select name, age, year(now()) - age as csnf from user where name="root";
+------+------+------+
| name | age  | csnf |
+------+------+------+
| root |   20 | 2001 |
+------+------+------+
1 row in set (0.00 sec)

mysql> 

操作查询结果

查最小值:

  • min
  • max
  • sum
  • avg

排序 分组 去重显示

# 排序
mysql> select name, uid from user where uid between 10 and 100 order by uid desc;
+----------+------+
| name     | uid  |
+----------+------+
| nobody   |   99 |
| postfix  |   89 |
| dbus     |   81 |
| sshd     |   74 |
| tcpdump  |   72 |
| ntp      |   38 |
| mysql    |   27 |
| ftp      |   14 |
| games    |   12 |
| operator |   11 |
+----------+------+
10 rows in set (0.01 sec)

mysql> 
# 分组显示
mysql> select shell from user where uid < 100 group by shell;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /bin/false     |
| /bin/sync      |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/shutdown |
+----------------+
6 rows in set (0.00 sec)

mysql> 

# 去重查询显示
mysql> select distinct  gid from user;
+------+
| gid  |
+------+
|    0 |
|    1 |
|    2 |
|    4 |
|    7 |
|   12 |
|  100 |
|   50 |
|   99 |
|  192 |
|   81 |
|  998 |
|   89 |
|   74 |
|  996 |
|   72 |
|   38 |
|   27 |
+------+
18 rows in set (0.00 sec)

mysql> 

# 过滤查询结果
# 对查询出来的结果再过滤一遍
mysql> select name from db3.user where uid >=1000 having name="bob";
Empty set (0.00 sec)

mysql> 

限制查询结果显示行数

  • 用法

sql 查询 limit 数字; //显示查询结果前多少条记录

sql查询 limit 数据1, 数字2 ;//显示指定范围内的查询结果;

# 显示查询结果的前三行
mysql> select name, uid, gid from user limit 3;
+--------+------+------+
| name   | uid  | gid  |
+--------+------+------+
| root   |    1 |    0 |
| bin    |    2 |    1 |
| daemon |    3 |    2 |
+--------+------+------+
3 rows in set (0.00 sec)

mysql> 

# 显示查询结果的范围,第一行为0 。第一个数字是起始行,第二个数字表示一共显示多少行
mysql> select name, uid, gid from user limit 4,7;
+----------+------+------+
| name     | uid  | gid  |
+----------+------+------+
| lp       |    5 |    7 |
| sync     |    5 |    0 |
| shutdown |    6 |    0 |
| halt     |    7 |    0 |
| mail     |    8 |   12 |
| operator |   11 |    0 |
| games    |   12 |  100 |
+----------+------+------+
7 rows in set (0.00 sec)

mysql> 

搭建PHP myadmin

yum -y install httpd php php-mysql php-devel php-fpm
systemctl start httpd
systemctl enable httpd

systemctl start  php-fpm 
systemctl enable  php-fpm 

# 安装phpMyAdmin软件包
wget https://files.phpmyadmin.net/phpMyAdmin/2.11.11.3/phpMyAdmin-2.11.11.3-all-languages.zip --no-check-certificate

unzip phpMyAdmin-2.11.11.3-all-languages.zip
放到/var/www/htmp/phpmyadmin文件里

# 创建主配置文件
[root@mx html]# cd phpmyadmin/
[root@mx phpmyadmin]# ls
browse_foreigners.php  db_export.php       Documentation.txt  LICENSE             pmd_display_field.php  readme.php              server_processlist.php  tbl_export.php      tbl_sql.php
calendar.php           db_import.php       error.php          license.php         pmd_general.php        RELEASE-DATE-2.11.11.3  server_sql.php          tbl_import.php      tbl_structure.php
ChangeLog              db_operations.php   export.php         main.php            pmd_help.php           scripts                 server_status.php       tbl_indexes.php     themes
changelog.php          db_printview.php    favicon.ico        navigation.php      pmd_pdf.php            server_binlog.php       server_variables.php    tbl_move_copy.php   themes.php
chk_rel.php            db_qbe.php          import.php         pdf_pages.php       pmd_relation_new.php   server_collations.php   show_config_errors.php  tbl_operations.php  TODO
config.sample.inc.php  db_search.php       index.php          pdf_schema.php      pmd_relation_upd.php   server_databases.php    sql.php                 tbl_printview.php   transformation_overview.php
contrib                db_sql.php          INSTALL            phpinfo.php         pmd_save_pos.php       server_engines.php      tbl_addfield.php        tbl_relation.php    transformation_wrapper.php
CREDITS                db_structure.php    js                 phpmyadmin.css.php  print.css              server_export.php       tbl_alter.php           tbl_replace.php     translators.html
db_create.php          docs.css            lang               pmd                 querywindow.php        server_import.php       tbl_change.php          tbl_row_action.php  user_password.php
db_datadict.php        Documentation.html  libraries          pmd_common.php      README                 server_privileges.php   tbl_create.php          tbl_select.php      view_create.php
[root@mx phpmyadmin]# cp config.sample.inc.php config.inc.php
[root@mx phpmyadmin]# 


vim config.inc.php

image-20211013113325589

访问

http://116.63.133.222/phpmyadmin/
# 登录时使用数据库的用户名和密码

image-20211013113712469

image-20211013113948105

授权grant

  • 授权: 添加用户并设置权限及连接密码

  • 命令格式

    grant 权限列表 on 库名 to 用户名@"客户端地址" identified by "密码" with grant option;// 有授权权限,可选项。也就是可以再增加人员
    

    权限列表

    • all // 所有权限
    • usage //无权限
    • select, update, insert //个别权限
    • select ,update(字段1,字段n) // 指定字段

    库名

    *.*    所有库所有表
    库名.*   //一个库
    库名.表名 //一张表
    

    客户端地址

    % //所有主机
    192.168.4.%  //网段内的所有主机
    192.168.4.1  //1台主机
    localhost    //数据库服务器本身
    

    追加权限

    #授权的话,要同时给这张表权限才行。因为添加权限时会往里面存
    grant insert on mysql.* to yaya66@"localhost";
    

    安装mysql-client 客户端

    yum install -y mariadb.x86_64 mariadb-libs.x86_64

    授权库

    授权库mysql

  • mysql库记录授权信息,主要表如下:

    • user 表 记录已有授权用户及全局权限
    mysql> select user,host  from user;
    +-----------+-----------+
    | user      | host      |
    +-----------+-----------+
    | mydba     | %         |
    | mysql.sys | localhost |
    | root      | localhost |
    | yaya      | localhost |
    +-----------+-----------+
    4 rows in set (0.00 sec)
    
    mysql> 
    
    # 改
    update mysql.user set 字段名="值" where 条件; flush privileges;
    
    • db 表 记录已有授权用户对数据库的访问权限
    • tables_priv表 记录已有授权用户对表的访问权限
    • columans_priv表 记录已有授权用户对字段的访问权限
  • 查看表记录可以获取用户权限,也可以通过更新记录,修改用户权限

# 查看对某库的访问权限
mysql> select user, db , host from mysql.db;
+-----------+-----+-----------+
| user      | db  | host      |
+-----------+-----+-----------+
| yaya      | db1 | localhost |
| mysql.sys | sys | localhost |
+-----------+-----+-----------+
2 rows in set (0.00 sec)

mysql> 



#  查询某个表对某个表字段的访问权限
mysql> grant select, update(name, uid) on db3.user to yaya88@"localhost" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> select * from mysql.columns_priv;
+-----------+-----+--------+------------+-------------+---------------------+-------------+
| Host      | Db  | User   | Table_name | Column_name | Timestamp           | Column_priv |
+-----------+-----+--------+------------+-------------+---------------------+-------------+
| localhost | db3 | yaya88 | user       | name        | 0000-00-00 00:00:00 | Update      |
| localhost | db3 | yaya88 | user       | uid         | 0000-00-00 00:00:00 | Update      |
+-----------+-----+--------+------------+-------------+---------------------+-------------+
2 rows in set (0.00 sec)

mysql> select * from mysql.tables_priv;
+-----------+-----+-----------+------------+----------------+---------------------+------------+-------------+
| Host      | Db  | User      | Table_name | Grantor        | Timestamp           | Table_priv | Column_priv |
+-----------+-----+-----------+------------+----------------+---------------------+------------+-------------+
| localhost | sys | mysql.sys | sys_config | root@localhost | 2021-09-26 11:55:24 | Select     |             |
| localhost | db3 | yaya88    | user       | root@localhost | 0000-00-00 00:00:00 | Select     | Update      |
+-----------+-----+-----------+------------+----------------+---------------------+------------+-------------+
2 rows in set (0.00 sec)

mysql> 

0

评论区