Mysql数据库基础操作

Mysql数据库

基本概念

内核的作用：调用硬件资源

数据库的作用

使用数据库可以高效且条理分明地存储数据，使人们能够更加迅速、方便的管理数据。

数据、表、数据库

数据

描述事物的符号记录，包括数字，文字，图形，图像，声音等

表

将不同的记录组织在一起用来存储数据

数据库

表的集合，是存储数据的仓库

数据库管理系统DBMS

DBMS是一个实现数据库资源有效组织、管理和存取的系统软件。

数据库的建立和维护功能：包括建立数据库的结构和数据的录入与转换、数据库的 转储与恢复、数据库的重组与性能监视等功能。

数据定义功能：包括定义全局数据结构、局部逻辑数据结构、存储结构、保密模式 及信息格式等功能。保证存储在数据库中的数据正确、有效和相容，以防止不合语 义的错误数据被输入或输出。

数据操纵功能：包括数据查询统计和数据更新两个方面。

数据库的运行管理功能：这是数据库管理系统的核心部分，包括并发控制、存取控 制、数据库内部维护等功能。

通信功能：DBMS 与其他软件系统之间的通信,如 Access 能与其他 Office 组件进行 数据交换。

数据库的分类

数据库分为两大类分别是关系型数据库和非关系型数据库。

关系数据库

操作命令：SQL语句

存储结构：二维表格

存储的数据：结构化数据

每一行称为一条记录，用来描述一个对象的信息；

每一列称为一个字段，用来描述对象的一个属性。

非关系数据库NoSQL

存储结构：键值对 文档 索引 时间序列
缓存型 Redis Memcached
文档型 MongoDB
索引型 ElasticSearch
时序型 Prometheus InfluxDB

主键

主键（Primary Key）是一个数据库表中的一列或一组列，用于确保数据的唯一性和数据行的唯一标识。
在表中，主键的值是唯一的。

主键具有以下特点：
1）唯一性：主键列中的值必须是唯一的，每一行的主键值都不相同。
2）非空性：主键列的值不能为空，也就是不允许为空值或NULL值。
3）唯一标识性：主键用于识别和区分表中的不同行数据，通过主键可以快速定位和访问表中的数据。

主键还可以用于建立表与表之间的关系，实现数据的关联和连接操作。

Mysql数据库简介

Mysql的特点

• 性能卓越、服务稳定

• 开源、无版权限制、成本低

• 多线程、多用户

• 基于C/S（客户端/服务器）架构

• 安全可靠

Mysql常用数据类型

int ：整型 无符号[0,232-1]，有符号[-231,2^31-1]
float ：单精度浮点 4字节32位
double ：双精度浮点 8字节64位
char ：固定长度的字符类型
varchar ：可变长度的字符类型
text ：文本
image ：图片
decimal(5,2) ：5个有效长度数字，小数点后面有2位

SQL语句

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

SQL语言分类：
DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
DML：数据操纵语言，用于对表中的数据进行管理
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限

MySQL的约束特性

primary key（主键约束） 字段的值不能重复，且不能为null，一个表只能有一个唯一键

uniq key（唯一性约束） 字段的值不能重复，可以为null,一个表可以有多个唯一键

not null (非空约束) 字段的值不能为null

default （默认值约束） 字段的值如果没有设置，则使用默认值自动填充

auto_increment (自增约束) 字段的值如果没有设置，默认会从1开始，每次自动递增1，要求自增字段必须设置主键

int(N) zerofill (零填充)

安装MySQL

yum安装

[root@localhost ~]#yum install mariadb-server -y
[root@localhost ~]#systemctl start mariadb.service
[root@localhost ~]#mysql
[root@localhost ~]#mysql_secure_installation 
#初始化设置  先输入密码 一路回车     




[root@centos7 ~]#tee /etc/yum.repos.d/mysql.repo <<EOF
[mysql]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-communitcy-el7-x86_64/
gpgcheck=0
EOF

清华大学  没有初始密码   5.7.41


[root@centos7 ~]#tee /etc/yum.repos.d/mysql.repo <<EOF
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/
enabled=1
gpgcheck=0
EOF

官方源   5.7.3.

[root@centos7 ~]#cat >/etc/yum.repos.d/mysql.repo <<EOF
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/
enabled=1
gpgcheck=0
EOF


http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/

[root@centos7 ~]#yum -y install mysql-community-server
[root@localhost yum.repos.d]# systemctl start mysqld
[root@localhost yum.repos.d]# ss -ntap |grep 3306
[root@localhost yum.repos.d]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@centos7 ~]#grep password /var/log/mysqld.log
[root@localhost yum.repos.d]# mysql -u root -p'+8saq%5+ljzG'
#特殊符号用单引号引起来



myslq    
客户端软件
mysaladmin 




mysql> status
#必须改密码
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

set global validate_password_policy=0;
set global validate_password_length=1;
#修改密码策略

mysql> alter user root@'localhost' identified by 'Admin@123';
alter user root@'localhost' identified by 'abc123';

编译安装Mysql

#建议内存4G以上 硬盘空间大于20G
#安装相关依赖包
yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel   ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel
#下载并解压缩源码包
tar xf  mysql-boost-5.7.20.tar.gz

[root@localhost opt]#cd mysql-5.7.20/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1

#############模块解释#####################
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
#指定mysql的安装路径
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
#指定mysql进程监听套接字文件（数据库连接文件）的存储路径
-DSYSCONFDIR=/etc \
#指定配置文件的存储路径
-DSYSTEMD_PID_DIR=/usr/local/mysql \
#指定进程文件的存储路径
-DDEFAULT_CHARSET=utf8  \
#指定默认使用的字符集编码，如utf8
-DDEFAULT_COLLATION=utf8_general_ci \
#指定默认使用的字符集校对规则
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
#安装INNOBASE存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
#安装ARCHIVE存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
#安装BLACKHOLE存储引擎
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
#安装FEDERATED存储引擎
-DMYSQL_DATADIR=/usr/local/mysql/data \
#指定数据库文件的存储路径
-DWITH_BOOST=boost \
#指定boost的路径，
-DWITH_SYSTEMD=1
#生成便于systemctl管理的文件
make -j2
make install

#数据库目录权限调整
[root@localhost mysql-5.7.20]#chown -R mysql:mysql /usr/local/mysql/

#调整配置文件
[root@localhost mysql-5.7.20]#vi /etc/my.cnf
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

#设置环境变量
echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
echo 'export PATH' >> /etc/profile
source /etc/profile

#初始化数据库
cd /usr/local/mysql/

bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/

#设置Mysql密码，初始密码为空回车即可然后重新输入新密码
update mysql.user set authentication_string=password('123123') where user='root' and host='127.0.0.1' or host='localhost';
update mysql.user set authentication_string=password('newpassword') where user='root' and host='127.0.0.1' or host='localhost';
#登录数据库
mysql -u root -p 

#如果出错，执行rm -f CMakeCache.txt

MySQL数据库基本操作

登录mysql数据库

表名和库命区分大小写

命令不区分大小写

mysqladmin -u root -p password "123123" #设置密码

mysql -u root -p #登录

查看数据库结构

1.查看当前服务器中的数据库

show databases;						#大小写不区分，分号“;”表示结束

2.查看数据库中包含的表

USE 数据库名;
show tables;

show tables from 数据库名;

3.查看表的结构

use 数据库名;
describe 表名; 
或者
desc 表名;

#不切换数据库
desc 库名.表名;

创建及删除数据库和数据表

创建新的数据库

create database <数据库名>;

创建新的表

create table 表名 (字段1 数据类型,字段2 数据类型[,...][,primary key (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

删除指定的数据表

drop table [数据库名.]表名;				#如不用USE进入库中，则需加上数据库名

删除指定的数据库

drop database 数据库名;

管理数据表中的数据记录

向数据表中插入新的数据记录

insert into表名(字段1,字段2[,...]) values(字段1的值,字段2的值,...);

查询数据记录

select 字段名1,字段名2[,...] from 表名 [where 条件表达式];

修改，更新表中的数据

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

#解释
insert  表名[（字段）]  值（值1，值2....）,(值1，值2)......；
使用 insert 语句时 如果不在表后加上字段就要一一对应填写上信息（注意 字符串用引号引起来）

update l1 set passwd='123456789' where name='zhangsan';
#修改

在数据表中删除指定的数据记录

delete from 表名 [where 条件表达式];

修改表名和表结构

alter table 旧表名 rename 新表名;

扩展表结构

alter table 表名 add 新字段 数据类型 [字段属性];

删除字段

alter table 表名 drop 字段名;

数据表的高级操作

克隆表

create table <新表> like <旧表>; #克隆表结构

insert into <新表> select * from <旧表>; #克隆表数据

#此方法能保证 新表的表结构、表数据 跟旧表都是一致的

清空表

delete from 表名;        
#一条一条的删除记录，效率较慢；自增字段仍然会按照清空前的最大记录继续自增

数据库用户管理

新建用户并查看用户信息

create user '用户名'@'源地址'   identified by '密码';

#创建后的用户保存在 mysql 数据库的 user 表里
use mysql;
select user,authentication_string,Host from user;

查看当前登录用户

#查看当前登录用户
select user();

重命名用户

rename user 'zhangsan'@'localhost' to 'lisi'@'localhost';

删除用户

drop user 'ctrl'@'localhost';

修改当前登录用户密码

set password = password('abc123');

当前用户为test

修改其他用户密码

set password for 'test'@'%' = PASSWORD('abc123');