数据库基本管理

数据库基本管理

一 MySQL启动关闭流程

mysql是一个典型的C/S服务结构

1.mysql自带的客户端程序(/service/mysql/bin)
    mysql
    mysqladmin
    mysqldump

2.mysqld一个二进制程序,后台的守护进程
    单进程
    多线程

1.1 启动数据库

1、/etc/init.d/mysqld start ---> mysql.server ---> mysqld_safe ---> mysqld
2、systemctl start mysql ---> mysqld_safe ---> mysqld
3、mysqld_safe --defaults-file=/etc/my.cnf ---> mysqld_safe ---> mysqld

1.2 停止数据库

/etc/init.d/mysqld stop
systemctl stop mysqld

# 如果是自己用命令如mysqld_safe启动的mysql,可以用mysqladmin来关闭
mysqladmin -uroot -p123 shutdown  

# 无论如何都不建议使用
kill -9 pid
killall mysqld
pkill mysqld

# 直接kill数据库可能会出现问题:
1.如果在业务繁忙的情况下,数据库不会释放pid和sock文件
2.号称可以达到和Oracle一样的安全性,但是并不能100%达到
3.在业务繁忙的情况下,丢数据(补救措施,高可用)

二 MySQL设置密码

初始状态下,管理员root,密码为空,默认只允许从本机登录localhost

#  设置初始密码 由于原密码为空,因此-p可以不用
[root@egon ~]# mysqladmin -uroot password "123"    

# 修改mysql密码,因为已经有密码了,所以必须输入原密码才能设置新密码
[root@egon ~]# mysqladmin -uroot -p"123" password "456"        

三 mysql连接工具与方式

3.1 mysql自带的连接命令 mysql

mysql命令介绍

mysql
#常见的特定于客户机的连接选项:
-u:             指定用户  mysql -uroot
-p:             指定密码  mysql -uroot -p567
-h:             指定主机域  mysql -uroot -p567 -h127.0.0.1
-P:             指定端口    mysql -uroot -p567 -h127.0.0.1 -P3307
-S:             指定socket文件 mysql -uroot -p567 -S /tmp/mysql.sock
-e:             指定SQL语句(库外执行SQL语句) mysql -uroot -p567 -e "show databases;"
#--protocol:      指定连接方式 mysql --protocol=TCP  --protocol=socket

使用密码登录

#1.正确登录命令
[root@db01 scripts]# mysql -uroot -p123
[root@db01 scripts]# mysql -u root -p123
[root@egon ~]# mysql  # 以root用户登录本机,密码为空

#2.错误登录命令
[root@db01 scripts]# mysql -u root -p 123  # -p选项与密码之间不要有空格

两种连接方式

1.TCP/IP的连接方式
2.套接字连接方式,socket连接

#查看连接方式
mysql> status;
--------------
Connection:     Localhost via UNIX socket

3.举例:
    3.1.TCP/IP连接,通常带有-h选项的都是TCP/IP链接
    mysql -uroot -p -h127.0.0.1 -P 端口号
    mysql -uroot -p -h127.0.0.1 -S /tmp/mysql.sock

    3.2.socket连接
    mysql -uroot -p123(默认连接方式,socket)

4.注意:
    4.1.因为使用TCP/IP连接,需要建立三次握手
    4.2.不一定-h都是tcp,-hlocalhost是socket连接
        mysql -uroot -p -hlocalhost

3.2 第三方的连接工具

1.sqlyog
2.navicat
#注意:数据库是没有外网的,想连接可以使用通道
3.应用程序连接MySQL
#注意:需要加载对应语言程序的API

四 配置文件

4.1 mysql配置文件的作用

1.影响服务端的启动(mysqld)
2.影响客户端的连接

4.2 配置文件介绍

强调:配置文件中的注释可以有中文,但是配置项中不能出现中文

1. 在执行mysqld命令时,下列配置会生效,即mysql服务启动时生效
[mysqld]
;skip-grant-tables
port=3306
character_set_server=utf8
default-storage-engine=innodb
innodb_file_per_table=1

#解压的目录
basedir=E:\mysql-5.7.19-winx64
#data目录
datadir=E:\my_data #在mysqld --initialize时,就会将初始数据存入此处指定的目录,在初始化之后,启动mysql时,就会去这个目录里找数据

#2. 针对客户端命令的全局配置,当mysql客户端命令执行时,下列配置生效
[client]
port=3306
default-character-set=utf8
user=root
password=123

#3. 只针对mysql这个客户端的配置,2中的是全局配置,而此处的则是只针对mysql这个命令的局部配置
[mysql]
;port=3306
;default-character-set=utf8
user=egon
password=4573

#!!!如果没有[mysql],则用户在执行mysql命令时的配置以[client]为准

4.3 统一字符编码

xshell —> linux —> mysql数据库

系统编码

#1.命令行临时修改 : 
LANG=us_EN.UTF-8
LANG=zh_CN.UTF-8

临时修改报错命令为应文 : LANG=us_EN.UTF-8
#2.修改系统字符集
Centos6 永久修改:[root@db03 ~]# vim /etc/sysconfig/i18n
Centos7 永久修改:[root@db03 ~]# vim /etc/locale.conf

修改mysql数据库默认字符集

  • 方法1:cmake时候指定

    cmake .
    -DDEFAULT_CHARSET=UTF8 \
    -DDEFAULT_COLLATION=UTF8_GENERAL_CI
  • 方法2:配置文件指定

    #1.修改配置文件
    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
    
    [client]
    default-character-set=utf8
    [mysql]
    default-character-set=utf8
    
    #2. 重启服务
    #3. 查看修改结果:
    \s
    show variables like '%char%';

    注解:

    校验规则: utf8_general_ci
    1)ci:大小写不敏感
    2)cs或bin:大小写敏感
    
    #一个表里面不可能出现同名不同大小写的字段
    
    #查看校验规则
    mysql> show collation;

    注意:最好在[mysqld]下也加上下述配置,规范一些

    # 反向解析
    skip-name-resolve
    
    # 安装目录和数据目录
    basedir= 安装目录
    datadir= 数据目录

    mysql数据库怎么存入emoji表情

    # 第一步:设置数据库字符编码为utf8mb4_general_ci  设置相应表字段字符编码为utf8mb4_general_ci
    
    # 第二步:设置my.cnf增加以下配置信息
    [client]
    default-character-set = utf8mb4
    
    [mysql]
    default-character-set = utf8mb4
    
    [mysqld]
    character-set-client-handshake = FALSE
    character-set-server = utf8mb4
    collation-server = utf8mb4_unicode_ci
    init_connect='SET NAMES utf8mb4'
    
    # 第三步:重启MySQL 保存表情数据查看数据库就可以看到数据库中有表情字符的存在。

如果没有配置默认编码,也可以在建库指定,

#1、创建数据库指定字符集和校验规则
create database if not exists db1 charset utf8 collate utf8_general_ci;

#2、修改
alter database db1 charset utf8mb4 collate utf8mb4_general_ci;

创建的新表默认继承其所在库的字符编码,除非你自己指定

# 建表时指定编码
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT comment 'id',
`name` char(20) NOT NULL comment '名字',
PRIMARY KEY (`id`) comment '主键'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 改表的字符集:
alter table test charset utf8;

批量修改库和表的字符编码

#1、批量修改字符集,30个库,300个表
1.我们先创建10个库
for n in `seq 10`;do mysql -uroot -p123 -e "create database db$n charset gbk;";done

#2、库里创建表
for n in `seq 10`;do mysql -uroot -p123 -e "use db$n;create table user$n(id int,name varchar(10)) charset gbk";done

for n in `seq 10`;do mysql -S /data/3309/mysql.sock -uroot -p3309 -e "use db$n;create table user$n(id int,name varchar(10)) charset gbk";done

#3、怎么批量修改数据库的字符集????
对于新创建的表会继承库的字符编码,但对于已经存在的老表,修改其所在库的字符编码,表的字符编码是不会修改的
alter database db1 charset utf8;
show create database db1;
show create table user1;

修改方式
for n in `seq 10`;do mysql -uroot -p123 -e "alter database db$n charset utf8;use db$n;alter table user$n charset utf8";done

4.4 设置不区分大小写

注意:开发时,数据库的库名和表名都要小写,因为很多研发使用第三方工具连接数据库,而windows不区分大小写,linux区分大小写,很容易读写错数据库;

mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)

可以修改配置文件来让数据库不区分大小写:

[root@egon ~]# vim /etc/my.cnf
[mysqld]
lower_case_table_names=1
[root@egon ~]# systemctl restart mysql

五 MySQL实例初始化配置(了解)

5.1 初始化配置文件的作用

1.预编译:cmake去指定,硬编码到程序当中去

2.在命令行设定启动初始化配置
--skip-grant-tables 
--skip-networking
--datadir=/application/mysql/data
--basedir=/application/mysql
--defaults-file=/etc/my,cnf
--pid-file=/application/mysql/data/db01.pid
--socket=/application/mysql/data/mysql.sock
--user=mysql
--port=3306
--log-error=/application/mysql/data/db01.err

3.初始化配置文件(/etc/my.cnf)

5.2 配置文件读取顺序

5.2.1 读取顺序

/etc/my.cnf
/etc/mysql/my.cnf
$basedir/my.cnf(前提是在环境变量中定义了MYSQL_HOME变量)
defaults-extra-file (类似include)
~/.my.cnf

5.2.2 生效顺序

~/.my.cnf
defaults-extra-file (类似include)
$basedir/my.cnf(前提是在环境变量中定义了MYSQL_HOME变量)
/etc/mysql/my.cnf
/etc/my.cnf

5.2.3 生效顺序验证

#配置/etc/my.cnf
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1

#配置/etc/mysql/my.cnf
[root@db01 ~]# mkdir /etc/mysql
[root@db01 ~]# vim /etc/mysql/my.cnf
[mysqld]
server_id=2

#配置$basedir/my.cnf
[root@db01 ~]# vim /service/mysql/my.cnf 
[mysqld]
server_id=3

#配置~/my.cnf
[root@db01 ~]# vim ~/.my.cnf
[mysqld]
server_id=4

#重启数据库(注意这里不能使用systemctl restart mysql重启)
[root@db01 ~]# systemctl stop mysql
[root@db01 ~]# /etc/init.d/mysqld start

#查看server_id
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 4     |
+---------------+-------+
1 row in set (0.00 sec)

5.3 思考参数执行优先级

#cmake:
socket=/service/mysql/tmp/mysql.sock

#命令行:
--socket=/tmp/mysql.sock

#配置文件:
vim /etc/my.cnf
[mysqld]
socket=/opt/mysql.sock

#default参数:
--defaults-file=/tmp/a.txt
vim /tmp/a.txt
[mysqld]
socket=/tmp/test.sock

#启动测试
mysql_safe --defaults-file=/tmp/a.txt --socket=/tmp/mysql.sock
优先级测试结论:
1、命令行
2、defaults-file
3、配置文件
4、预编译

命令行 >> defaults-file >> ~/.my.cnf >> defaults-extra-file >> $basedir/my.cnf >> /etc/mysql/my.cnf >> /etc/my.cnf >> cmake

六 mysql相关命令

6.1 mysql连接后命令行快捷命令

\c:中断当前输入语句
    \r:重新连接到服务器
    \d:设置语句sql结束符    mysql> \d ]
    \e:编辑命令    输出输入的内容 echo
    \p:打印当前命令并执行
\G:垂直显示结果
\q:退出mysql    等于 quit exit
    \g:表示结束    等于 ; 的作用
\h:显示此帮助    等于 help    #help还可以查看命令语法 help create database;
    \t:不写入outfile
\T:将所有内容附加到给定的输出文件中(只支持本次会话)        \T /tmp/a.log
    #\n:禁用寻呼机,打印到标准输出。
    #\P:设置寻呼机[到寻呼机]。通过寻呼机打印查询结果。
\R:更改mysql提示符    \R mysql>>> 
\.:执行一个sql文件    等于 source
\s:从服务器获取状态信息    等于 status
\u:切换数据库    等于 use  #查看当前所在数据库select database();
    \C:切换到另一个字符集    一般不使用
    \W:在每个语句后显示警告
    \w:不在每个语句后显示警告

6.2 help命令

mysql> help
mysql> help contents    help 用法
mysql> help select
mysql> help create
mysql> help create user
mysql> help status
mysql> help show

6.3 mysqladmin命令

1.修改密码,设置密码:password
[root@db01 ~]# mysqladmin -uroot -p旧密码 password '新密码'

2.关闭MySQL服务:shutdown
[root@db01 ~]# mysqladmin -uroot -p密码 -S socket文件 shutdown

3.库外建库:create
[root@db01 ~]# mysqladmin -uroot -p密码 create egon
[root@db01 ~]# mysql -uroot -p123456 -e 'create database egon'

4.库外删除数据库:drop
[root@db01 ~]# mysqladmin -uroot -p123456 drop egon
Do you really want to drop the 'egon' database [y/N] y
Database "egon" dropped

5.查看配置文件所有的默认参数:variables
[root@db01 ~]# mysqladmin -uroot -p123456 variables
[root@db01 ~]# mysqladmin -uroot -p123456 variables | grep server_id

6.检测MySQL进程是否存活:ping
[root@db01 ~]# mysqladmin -uroot -p123456 ping

7.查看数据库 慢查询,负载信息:status
[root@db01 ~]# mysqladmin -uroot -p123456 status
Uptime                   MySQL服务器已经运行的秒数
Threads                  活跃线程(客户)的数量 
Questions                从mysqld启动起来自客户问题的数量   已经发送给服务器的查询的个数
Slow queries             已经超过long_query_time秒的查询数量 
Opens                    mysqld已经打开了多少表 
Flush tables             flush ..., refresh和reload命令数量 
Open tables              现在被打开的表数量
Queries per second avg: 0.046   负载

8.重载授权表,刷新缓存主机:reload,相当于flush privileges
[root@db01 ~]# mysqladmin -uroot -p123456 reload

9.刷新binlog日志
[root@db01 ~]# mysqladmin -uroot -p123456 flush-log

七 初始sql语句

有了mysql这个数据库软件,就可以将程序员从对数据的管理中解脱出来,专注于对程序逻辑的编写

mysql服务端软件即mysqld帮我们管理好文件夹以及文件,前提是作为使用者的我们,需要下载mysql的客户端,或者其他模块来连接到mysqld,然后使用mysql软件规定的语法格式去提交自己命令,实现对文件夹或文件的管理。该语法即sql(Structured Query Language 即结构化查询语言)

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。
SQL语言分为:
DDL:Data definition Language 数据定义语言(CREATE、DROP、ALTER)
DCL:Data control Language 数据控制语言(GRANT,REVOKE,ROLLBACK,COMMIT)
DML:Data Manipulation Language 数据操作语言(INSERT,UPDATE,DELETE)
DQL:Data Query Language 数据查询语言(SELECT)

库、表、记录的增删改查

#1. 操作文件夹
增:create database db1 charset utf8;
    数据库已存在会报错,使用IF NOT EXISTS
    create database if not exists db1 charset utf8;
查:show databases;
改:alter database db1 charset latin1;
删除: drop database db1;

#2. 操作文件
先切换到文件夹下:use db1
增:create table t1(id int,name char);
查:show tables
改:alter table t1 modify name char(3);
alter table t1 change name name1 char(2);
删:drop table t1;

#3. 操作文件中的内容/记录
增:insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
查:select * from t1;
改:update t1 set name='sb' where id=2;
删:delete from t1 where id=1;

清空表:
delete from t1; #如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大,删除速度比上一条快,且直接从零开始,

auto_increment 表示:自增
primary key 表示:约束(不能重复且不能为空);加速查找
上一篇
下一篇
Copyright © 2022 Egon的技术星球 egonlin.com 版权所有 帮助IT小伙伴学到真正的技术