搭建数据库服务器
安装软件、启动服务
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
命令行连接数据库服务
命令行连接
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~127 | 0~255 |
| smallint | 小整数 | - 32768~32767 | 0~65535 |
| mediumint | 中整数 | -223 ~ 223-1 | 0 ~ 2^24-1 |
| int | 大整数 | ^31 | ^32 |
| bigint | 极大整数 | ^63 | ^64 |
| unsigned | 使用无符号存储范围 |
浮点型
# 采用无符号存储年龄，圆周率使用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)
时间函数
# 获取当前操作系统的时间
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
访问
http://116.63.133.222/phpmyadmin/
# 登录时使用数据库的用户名和密码
授权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>
评论区