DBA (day1)
冬练三九，夏练三伏！
什么是数据库
数据库基础
数据库就是存储数据的仓库。
生活中的数据
视频、音频、图片、文本文件(微信、QQ、美团、饿了么、绝地求生)
常见软件
主流的操作系统：
Unix 、Linux、Windows
专业术语
DB(DataBase)
数据库
依照某种数据模型进行组织并存放到存储器的数据集合
DBMS(DataBase Managemrent System)
数据库管理系统
用来操作和管理数据库的服务软件
DBS(DataBase System)
数据库系统:即 DB+DBMS
指带有数据库并整合了数据库管理软件的计算机系统
mysql介绍
起源于发展
应用最广泛的开源数据库软件
最早隶属于瑞典的MySQL AB公司
2008年1月，MySQL AB被Sun收购
2009年4月，SUN被Oracle收购
崭新的开源分支MariaDB
为应付Myslq可能会闭源的风险而诞生
由Myslq原作者widenius主导开发
与Mysql保持最大程度兼容
主要的特点和应用
适用于中小规模、关系型数据库系统
支持Linux 、Unix、Windows等多种操作系统
支持Python、Java、Perl、PHP等编程语言
典型应用环境
LAMP平台，与Apache HTTP Server组合
LNMP平台，与Nginx组合
构建MySQL服务器
MySQL安装
环境准备：
1.创建CentOS系统虚拟机1台
2.配置IP地址192.168.4.50
3.关闭firewalld
4.禁用SElinux
5.拷贝软件 myslq-5.7.17.tar
6.配置yum源
安装软件
官网下载地址：
https://dev.mysql.com/downloads/mysql/
把下载好的包解压完后有下列rpm包
mysql-community-client-5.7.17-1.el7.x86_64.rpm ##客户端应用程序
mysql-community-common-5.7.17-1.el7.x86_64.rpm ##数据库和客户端共享文件
mysql-community-devel-5.7.17-1.el7.x86_64.rpm ##客户端应用程序的库和头文件
mysql-community-embedded-5.7.17-1.el7.x86_64.rpm ##嵌入式函数库
mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm ##嵌入式兼容数据库
mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm ##头文件和库文件作为Mysql的嵌入式
mysql-community-libs-5.7.17-1.el7.x86_64.rpm ##mysql数据库客户端应用程序的共享库
mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm ##客户端应用程序的共享兼容库
mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
mysql-community-server-5.7.17-1.el7.x86_64.rpm ##服务端应用程序
mysql-community-test-5.7.17-1.el7.x86_64.rpm ##测试应用程序
解压软件包并用yum安装，自动解决软件依赖。
] # tar -xvf mysql-5.7.17.tar
] # yum -y install mysql-community-*.rpm
] # rpm -qa | grep -i mysql
相关参数
软件安装后自动创建相关目录与文件
/etc/my.cnf 主配置文件
/var/lib/myslq 数据库目录
默认端口号 3306
进程名 mysqld
传输协议 TCP
进程者所有者 mysql
进程着所属主 mysql
错误日志文件 /var/log/mysql.log
首次启动服务，会执行数据初始化。速度会比较慢。
] # systemctl start mysqld //启动服务
] # systemctl enable mysqld //开机自启
] # systemctl status mysqld //查看状态
] # netstat -utnip | grep :3306 //查看服务信息
tcp6 0 0 :::3306 :::* LISTEN 854/mysqld
] # ps -C mysql //查看进程
PID TTY TIME CMD
854 ? 00:00:00 mysqld
初始密码登录
数据库管理员名为 root
默认仅允许root本机连接
首次登录密码在安装软件是随机生成
随机密码存储目录为 /var/log/mysql.log
查询命令： grep password /var/log/mysql.log
连接命令： mysql -h数据库地址 -u用户 -p密码
] # msyql -hlocalhost -uroot -p'生成的随机密码'
进入数据库操作界面后输入命令查看库
] # show databases；
结果显示：
修改root密码
具体操作如下：
使用alter user 命令修改登录密码
新密码必须满足密码策略
管理员root 使用新密码连接服务
修改的登录密码命令
myslq> alter user root@"localhost" identified by "123qqq...A"；
重新登录验证结果
] # mysql -hlocalhost -uroot -p123qqq...A
修改密码
mysql> show variables like "%password%"; //查看变量
mysql> set global validate_password_policy=0; //修改密码策略
mysql>set global validate_password_length=6; //修改密码长度
永久修改配置
vim /etc/my.cof
[mysqld]
validate_password_policy=0
validate_password_length=6
重启服务
根据修改的策略来修改密码
alter user root@"localhost" identified by "123456";
验证修改后的密码
1 在50主机上部署mysql数据库服务 2 解包 3 安装软件包 4 启动mysqld服务 5 查看服务进程和端口号 6 查看初始化密码登录 7 修改登录密码 8 断开连接 9 使用修改厚的密码登录 10 修改密码策略 11 根据新密码策略设置密码 12 使用新密码登录
1.数据库基本管理
1.1连接mysql服务
客户端连接mysql服务的方法
1.命令行
2.web连接
3.安装图形软件
4.编写脚本(php、java、python......)
使用mysql命令
mysql -h服务器IP -u用户名 -p密码 [数据库名]
quit 或 exit 退出
1 ]#mysql -uroot-p123456 mysql 2 mysql> select database(); 3 +------------+ 4 | database() | 5 +------------+ 6 | mysql | 7 +------------+ 8 ]#mysql -uroot-p123456 9 mysql> select database(); 10 mysql> select database(); 11 +------------+ 12 | database() | 13 +------------+ 14 | NULL | 15 +------------+
mysql管理环境
sql命令使用规则(结构化查询语言)
1.SQL命令不区分字母大小写(密码，变量值除外)
2.每条SQL命令以;结束
3.默认命令不支持Tab键自动补全
4.\c 终止sql命令
1 mysql> SHOW DATABASES; 2 +--------------------+ 3 | Database | 4 +--------------------+ 5 | information_schema | 6 | mysql | 7 | performance_schema | 8 | sys | 9 +--------------------+ 10 4 rows in set (0.00 sec) 11 12 mysql> shOW dataBaSES; 13 +--------------------+ 14 | Database | 15 +--------------------+ 16 | information_schema | 17 | mysql | 18 | performance_schema | 19 | sys | 20 +--------------------+ 21 4 rows in set (0.00 sec) 22 23 24 mysql> alter table^C 25 mysql> show databases \c 26 mysql>
常用的SQL命令分类
1.DDL 数据定义语言 如：create、alter、drop
2.DML 数据操作语言 如：insert、update、delete
3.DCL 数据控制语言 如：grant、revoke
4.DTL 数据事物语言 如：commit、rollback、savepoint
1.2 MySQL基本操作
库管理命令--类似于文件夹，用来存储表，可以创建多个库
show databases; //显示已有的库
select user(); //显示连接用户
use 库名; //切换库
select databases(); //显示当前所在的库
create databases 库名;//创建新库
show dables； //显示已有的表
drop database 库名; //删除库
1 mysql> show databases; 2 +--------------------+ 3 | Database | 4 +--------------------+ 5 | information_schema | 6 | mysql | 7 | performance_schema | 8 | sys | 9 +--------------------+ 10 mysql> select user(); 11 +----------------+ 12 | user() | 13 +----------------+ 14 | root@localhost | 15 +----------------+ 16 17 mysql> use mysql; 18 Reading table information for completion of table and column names 19 You can turn off this feature to get a quicker startup with -A 20 21 Database changed 22 mysql> select database(); 23 +------------+ 24 | database() | 25 +------------+ 26 | mysql | 27 +------------+ 28 1 row in set (0.00 sec) 29 30 mysql> create database t1; 31 Query OK, 1 row affected (0.00 sec) 32 33 mysql> show databases; 34 +--------------------+ 35 | Database | 36 +--------------------+ 37 | information_schema | 38 | mysql | 39 | performance_schema | 40 | sys | 41 | t1 | 42 +--------------------+ 43 5 rows in set (0.00 sec) 44 45 mysql> show tables; 46 +---------------------------+ 47 | Tables_in_mysql | 48 +---------------------------+ 49 | columns_priv | 50 | db | 51 | engine_cost | 52 | event | 53 | func | 54 | general_log | 55 | gtid_executed | 56 | help_category | 57 | help_keyword | 58 ...... 59 60 mysql> drop database t1; 61 Query OK, 0 rows affected (0.00 sec) 62 63 mysql> show databases; 64 +--------------------+ 65 | Database | 66 +--------------------+ 67 | information_schema | 68 | mysql | 69 | performance_schema | 70 | sys | 71 +--------------------+ 72 4 rows in set (0.00 sec)
库管理命令
仅可以使用数字、字母、下划线、不能纯数字
区分字母大小写，具有唯一性
不可使用指令关键字、特殊字符
mysql> create database DB1; Query OK, 1 row affected (0.00 sec) mysql> create database db1; Query OK, 1 row affected (0.00 sec)
表管理命令
建表
表存储数据的文件
mysql>create table 库名.表名（
字段名1 类型(宽度)，
字段名2 类型(宽度)，
.....
）DEFAULT CHARSET=utf8；//指定中文字符集，可以赋值中文。
mysql> create table db1.t1( -> name char(15), -> homeaddr char(20) -> )DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec)
表类似于文件
desc 库名.表名； //查看表结构
drop table 库名.表名; //删除表
mysql> desc db1.t1; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | name | char(15) | YES | | NULL | | | homeaddr | char(20) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> drop table db1.t2; Query OK, 0 rows affected (0.01 sec)
记录管理命令
记录类似文件里的行
select * from 库名.表名； //查看表记录
insert into 库名.表名 values(值列表)；//插入表记录
update 库名.表名 set 字段=值; //修改表记录
delete from 表名; //删除表记录
mysql> insert into t1 values("lisi","beijing"); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+----------+ | name | homeaddr | +------+----------+ | lisi | beijing | +------+----------+ 1 row in set (0.00 sec) mysql> update t1 set homeaddr="shanxi"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t1; +------+----------+ | name | homeaddr | +------+----------+ | lisi | shanxi | +------+----------+ 1 row in set (0.00 sec) mysql> delete from t1; Query OK, 1 row affected (0.00 sec) mysql> show tables; +---------------+ | Tables_in_db1 | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> select * from t1; Empty set (0.00 sec)
练习题：
1 mysql> create table db1.学生表(学号 int ,姓名 char(15),性别 char(15),手机号 int,通讯地址 char(50))DEFAULT CHARSET=utf8; 2 Query OK, 0 rows affected (0.01 sec) 3 4 mysql> desc 学生表; 5 +--------------+----------+------+-----+---------+-------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +--------------+----------+------+-----+---------+-------+ 8 | 学号 | int(11) | YES | | NULL | | 9 | 姓名 | char(15) | YES | | NULL | | 10 | 性别 | char(15) | YES | | NULL | | 11 | 手机号 | int(11) | YES | | NULL | | 12 | 通讯地址 | char(50) | YES | | NULL | | 13 +--------------+----------+------+-----+---------+-------+ 14 5 rows in set (0.00 sec) 15 16 mysql> show create table 学生表; 17 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 18 | Table | Create Table | 19 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 20 | 学生表 | CREATE TABLE `学生表` ( 21 `学号` int(11) DEFAULT NULL, 22 `姓名` char(15) DEFAULT NULL, 23 `性别` char(15) DEFAULT NULL, 24 `手机号` int(11) DEFAULT NULL, 25 `通讯地址` char(50) DEFAULT NULL 26 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 27 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 28 1 row in set (0.00 sec)
MySQL数据类型
信息种类
数值型:体重、身高、成绩、工资
字符型:姓名、工作单位、通讯地址
枚举型:兴趣爱好、性别、专业
日期时间型:出生日期、注册时间
字符类型
定长char(字符个数)
最大字符个数255
不够指定字符个数时在右边用空格不全
字符个数超出时，无法写入数据
--------------
变长varchar(字符个数)
最大字符个数65532
按数据实际大小分配存储空间
字符个数超出是，无法写入数据
大文本类型：test/blob
字符数大于65535存储使用
mysql> create table db1.t2( name char(5), email varchar(30) ); Query OK, 0 rows affected (0.02 sec) mysql> desc t2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | char(5) | YES | | NULL | | | email | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into t2 values("bob","qq@tedu.cn"); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +------+------------+ | name | email | +------+------------+ | bob | qq@tedu.cn | +------+------------+ 1 row in set (0.00 sec)
数值类型
整数型
只能存储整数
223:8388608
224:16777216
231:2147483648
232:4294967296
263:9223372036854775808
264:18446744073709551616
1 mysql> create table t4(name char(10),level tinyint); 2 Query OK, 0 rows affected (0.01 sec) 3 4 mysql> desc t4; 5 +-------+------------+------+-----+---------+-------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +-------+------------+------+-----+---------+-------+ 8 | name | char(10) | YES | | NULL | | 9 | level | tinyint(4) | YES | | NULL | | 10 +-------+------------+------+-----+---------+-------+ 11 2 rows in set (0.00 sec) 12 mysql> insert into t4 values("aaa",-1); 13 Query OK, 1 row affected (0.00 sec) 14 15 mysql> select * from t4; 16 +------+-------+ 17 | name | level | 18 +------+-------+ 19 | aaa | -1 | 20 +------+-------+ 21 1 row in set (0.00 sec) 22 23 24 mysql> create table t5 (name char(10),level tinyint unsigned); 25 Query OK, 0 rows affected (0.01 sec) 26 27 mysql> insert into t5 values("bbb",-1); 28 ERROR 1264 (22003): Out of range value for column 'level' at row 1 29 30 mysql> insert into t5 values("bbb",1); 31 Query OK, 1 row affected (0.01 sec) 32 33 ysql> insert into t5 values("bbb",255); 34 Query OK, 1 row affected (0.00 sec) 35 36 mysql> insert into t5 values("bbb",256); 37 ERROR 1264 (22003): Out of range value for column 'level' at row 1 38 39 mysql> select * from t5; 40 +------+-------+ 41 | name | level | 42 +------+-------+ 43 | bbb | 1 | 44 | bbb | 0 | 45 | bbb | 255 | 46 +------+-------+ 47 3 rows in set (0.00 sec)
浮点型
格式:字段名 类型；
格式:字段名 类型(总宽度，小数位数)
mysql> create table t1(pay float,id double); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(999.23,9999.45); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(999,9999); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(999999.99,1111.9999); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +---------+-----------+ | pay | id | +---------+-----------+ | 999.23 | 9999.45 | | 999 | 9999 | | 1000000 | 1111.9999 | +---------+-----------+ 3 rows in set (0.00 sec) mysql> create table t2(bay float(5,3),id double); Query OK, 0 rows affected (0.02 sec) mysql> insert into t2 values (23.4,12324); Query OK, 1 row affected (0.01 sec) mysql> insert into t2 values (222222,1234); ERROR 1264 (22003): Out of range value for column 'bay' at row 1 mysql> select * from t2; +--------+-------+ | bay | id | +--------+-------+ | 23.400 | 12324 | +--------+-------+ 1 row in set (0.00 sec)
练习：
按照如下结构建表：
mysql> create table t2( stu_num int(11), name char(5), age tinyint(4), pay float, money float(5,2) ); Query OK, 0 rows affected (0.01 sec) mysql> desc db1.t2; +---------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------+ | stu_num | int(11) | YES | | NULL | | | name | char(5) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | pay | float | YES | | NULL | | | money | float(5,2) | YES | | NULL | | +---------+------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
日期时间类型
类型
日期时间 datetime
范围：1000-01-01 00:00:00~9999-12-31 23:59:59
格式: YYYYMMDDHHMMSS
日期时间 timestamp
范围：1970-01-01 00:00:00~2038-1-19 00:00:00
格式： YYYYMMDDHHMMSS
日期 date
范围: 0001-01-01 ~ 9999-12-31
格式: YYYYMMDD
年 year
范围：1901 ~ 2155
格式: XXXX
时间 time
格式: HH:MM:SS
mysql> create table t3 (time datetime,stamp timestamp,riqi date,nian year,shijian time); Query OK, 0 rows affected (0.01 sec) mysql> desc t3; +---------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------+-----------+------+-----+-------------------+-----------------------------+ | time | datetime | YES | | NULL | | | stamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | riqi | date | YES | | NULL | | | nian | year(4) | YES | | NULL | | | shijian | time | YES | | NULL | | +---------+-----------+------+-----+-------------------+-----------------------------+ 5 rows in set (0.00 sec) ysql> insert into t3 values(19221208235900,19990512234500,19981231,2025,103500); Query OK, 1 row affected (0.00 sec) mysql> select * from t3; +---------------------+---------------------+------------+------+----------+ | time | stamp | riqi | nian | shijian | +---------------------+---------------------+------------+------+----------+ | 1922-12-08 23:59:00 | 1999-05-12 23:45:00 | 1998-12-31 | 2025 | 10:35:00 | +---------------------+---------------------+------------+------+----------+ 1 row in set (0.00 sec)
关于日期时间字段
当未给timestamp字段赋值是，自动以当前系统时间赋值，而datetime值为NULL(空)。
mysql> create table t4( meetting datetime, party timestamp ); mysql> insert into db1.t4 values(20190804125600,20190617170000); mysql> insert into db1.t4(meetting) values(20190804120000); mysql> insert into db1.t4(party) values(20190804120055); mysql> select * from db1.t4; +---------------------+---------------------+ | meetting | party | +---------------------+---------------------+ | 2019-08-04 12:56:00 | 2019-06-17 17:00:00 | | 2019-08-04 12:00:00 | 2019-06-17 16:25:47 | | NULL | 2019-08-04 12:00:55 | +---------------------+---------------------+ 3 rows in set (0.00 sec)
[root@mysql50 ~]# date
2019年 06月 17日 星期一 16:26:03 CST
year类型
-要求使用4位数赋值
-当使用2位数赋值时：
01~69视为 2001~2069
70~99视为 1970~1999
mysql> create table t5( -> year_a year, -> year_b year -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into t5 values(13,85); Query OK, 1 row affected (0.00 sec) mysql> select * from t5; +--------+--------+ | year_a | year_b | +--------+--------+ | 2013 | 1985 | +--------+--------+ 1 row in set (0.00 sec)
时间函数
myslq服务内置命令
可以使用时间函数给字段赋值
mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:07:57 | +-----------+ 1 row in set (0.00 sec) mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-06-17 | +------------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-06-17 17:08:38 | +---------------------+ 1 row in set (0.00 sec) mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2019 | +-------------+ 1 row in set (0.00 sec) mysql> select month(curdate()); +------------------+ | month(curdate()) | +------------------+ | 6 | +------------------+
1 row in set (0.00 sec)
mysql> insert into t3 values(curtime(),curdate(),date(now()),year(now()),time(now())); Query OK, 1 row affected (0.00 sec) mysql> select * from t3; +---------------------+---------------------+------------+------+----------+ | time | stamp | riqi | nian | shijian | +---------------------+---------------------+------------+------+----------+ | 1922-12-08 23:59:00 | 1999-05-12 23:45:00 | 1998-12-31 | 2025 | 10:35:00 | | 2019-06-17 17:15:35 | 2019-06-17 00:00:00 | 2019-06-17 | 2019 | 17:15:35 | +---------------------+---------------------+------------+------+----------+ 2 rows in set (0.00 sec)
枚举类型
enum单选：
格式:字段名 enum（值1，值2，值3）
仅能选择一个值
字段值必须在列表里选择
mysql> create table t6( -> name char(5), -> sex enum("boy","girl","no") -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into t6 values("bob","boy"); Query OK, 1 row affected (0.00 sec) mysql> insert into t6 values("bob","man"); ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> select * from t6; +------+------+ | name | sex | +------+------+ | bob | boy | +------+------+ 1 row in set (0.00 sec)
set 多选
格式:字段名 set(值1，值2，值3)
选择一个或多个值
字段值必须在列表里选择
mysql> create table t7( -> name char(5), -> likes set("eat","game","music","money")); Query OK, 0 rows affected (0.01 sec) mysql> insert into t7 values("jerry","eat,game,music"); Query OK, 1 row affected (0.00 sec) mysql> insert into t7 values("jerry","eat,game,film"); ERROR 1265 (01000): Data truncated for column 'likes' at row 1
mysql> select * from t7; +-------+----------------+ | name | likes | +-------+----------------+ | jerry | eat,game,music | +-------+----------------+ 1 row in set (0.00 sec)
浙公网安备 33010602011771号