数据库基础
程序员文章站
2022-05-07 14:35:50
...
什么是数据库
- 存储数据的仓库叫做数据库
- 数据存储在硬盘
数据是什么
- 通过聊天软件(QQ,微信,淘宝,美团…)传输数据: 视频,音频,图片,文本,这些叫做数据
提供数据库服务的软件有哪些
常见的软件:
主流的操作系统:Unix.Linux.Windows
软件名 开源 跨平台 厂商
Oracle 否 是 甲骨文
MySQL 是 是 甲骨文
SQL Server 否 否 微软
DB2 否 是 IBM
Redis 是 是 开源软件
Memcached 是 是 开源软件
MongDB 是 是 开源软件
如何决定使用哪种软件搭建数据库服务?
- 是否要花钱买? 是否跨平台?
数据库的专业术语
DB(DataBase)
数据库
依照某种数据模型进行组织并存放到存储器的数据集合
DBMS(DataBase Management System)
数据库管理系统
用来操纵和管理数据库的服务软件
DBS(DataBase System)
数据库系统: 即DB+DBMS
指带有数据库并整合了数据库管理软件的计算机系统
MySQL软件介绍
起源与发展
应用最广泛的开源数据库软件
最早隶属于瑞典的MySQL AB公司
2008年1月, MySQL AB被Sun收购
2009年4月,Sun被Oracle收购
崭新的开源分支 MariaDB
为应付MySQL可能会闭源的风险而诞生
由MySQL原作者 Windenius主导开发
与MySQL保持最大兼容程度
特点及应用
主要特点
适用于中小规模,关系型数据库系统
支持Linux,Unix,Windows等多种操作系统
支持Python,Java,Perl,PHP
典型应用环境
LAMP平台,与Apache HTTP Server组合
LNMP平台,与Nginx组合
搭建MySQL数据库服务安装软件
准备环境: (基本需求)
准备一台Centos虚拟机一台或者VMware虚拟机一台
配置IP地址为:192.168.4.51
配置主机名为:host51
配置yum源
关闭firewalld
禁用selinux
软件包:mysql-5.7.17.tar放到/opt目录中
mysql链接提取码: em6w
mysql-5.7.17.tar内容:
mysql-community-client ##客户端应用程序
mysql-community-common ##数据库和客户端库共享文件
mysql-community-devel ##客户端应用程序的库和头文件
mysql-community-embedded ##嵌入式函数库
mysql-community-embedded-compat ##嵌入式兼容函数库
mysql-community-embedded-devel ##头文件和库文件作为Mysql的嵌入式库文件
mysql-community-libs ##MySQL数据库客户端应用程序的共享库
mysql-community-libs-compat ##客户端应用程序的共享兼容库
##可以从官网下载RPM包
http://dev.mysql.com/downloads/mysql
1.安装MySQL软件包
[aaa@qq.com opt]# cd /opt
[aaa@qq.com opt]# tar -xvf mysql.tar.gz ##解包
[aaa@qq.com opt]# ls
mysql-community-server-5.7.17-1.el7.x86_64
mysql-community-embedded-compat-5.7.17-1.el7.x86_64
mysql-community-common-5.7.17-1.el7.x86_64
mysql-community-client-5.7.17-1.el7.x86_64
mysql-community-devel-5.7.17-1.el7.x86_64
mysql-community-test-5.7.17-1.el7.x86_64
mysql-community-libs-compat-5.7.17-1.el7.x86_64
mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64
mysql-community-libs-5.7.17-1.el7.x86_64
mysql-community-embedded-5.7.17-1.el7.x86_64
mysql-community-embedded-devel-5.7.17-1.el7.x86_64
[aaa@qq.com opt]# yum -y install mysql-community-*.rpm
[aaa@qq.com opt]# rpm -qa | grep mysql
11
软件安装后自动创建相关目录与文件
文件 说明
/etc/my.cnf 主配置文件
/var/lib/mysql 数据库目录 ##启动前默认这个路径没有文件
默认端口号 3306
进程名 mysqld
传输协议 TCP
进程所有者 mysql
进程所属组 mysql
错误日志文件 /var/log/mysql.log
2.启动MySQL数据库服务
[aaa@qq.com opt]# ls /var/lib/mysql
[aaa@qq.com opt]# systemctl start mysqld ##启动数据库服务,首次启动,需要初始化数据,会比较慢
[aaa@qq.com opt]# ls /var/lib/mysql ##启动Mysql数据库服务后产生的文档
auto.cnf ib_buffer_pool mysql public_key.pem
ca-key.pem ibdata1 mysql.sock server-cert.pem
ca.pem ib_logfile0 mysql.sock.lock server-key.pem
client-cert.pem ib_logfile1 performance_schema sys
client-key.pem ibtmp1 private_key.pem
[aaa@qq.com opt]# systemctl enable mysqld ##设置开机自启
查看mysql服务的3种启动状态:
[aaa@qq.com ~]# systemctl status mysqld.service ##查看服务
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-02-17 17:57:42 CST; 4h 26min ago
Docs: man:mysqld(8)
[aaa@qq.com ~]# ps -C mysqld ##查看进程
PID TTY TIME CMD
1934 ? 00:00:11 mysqld
[aaa@qq.com ~]# ss -nutlp | grep 3306 ##查看端口
tcp LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=1934,fd=42))
再次查看MySQL的安装路径
[aaa@qq.com opt]# ls -l /var/lib/mysql
总用量 122952
-rw-r----- 1 mysql mysql 56 2月 13 14:25 auto.cnf
-rw------- 1 mysql mysql 1675 2月 13 14:26 ca-key.pem
-rw-r--r-- 1 mysql mysql 1074 2月 13 14:26 ca.pem
-rw-r--r-- 1 mysql mysql 1078 2月 13 14:26 client-cert.pem
-rw------- 1 mysql mysql 1675 2月 13 14:26 client-key.pem
-rw-r----- 1 mysql mysql 413 2月 13 14:26 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 2月 13 14:26 ibdata1
-rw-r----- 1 mysql mysql 50331648 2月 13 14:26 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 2月 13 14:25 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 2月 13 14:26 ibtmp1
drwxr-x--- 2 mysql mysql 4096 2月 13 14:26 mysql
srwxrwxrwx 1 mysql mysql 0 2月 13 14:26 mysql.sock
-rw------- 1 mysql mysql 5 2月 13 14:26 mysql.sock.lock
drwxr-x--- 2 mysql mysql 8192 2月 13 14:26 performance_schema
-rw------- 1 mysql mysql 1679 2月 13 14:26 private_key.pem
-rw-r--r-- 1 mysql mysql 451 2月 13 14:26 public_key.pem
-rw-r--r-- 1 mysql mysql 1078 2月 13 14:26 server-cert.pem
-rw------- 1 mysql mysql 1679 2月 13 14:26 server-key.pem
drwxr-x--- 2 mysql mysql 8192 2月 13 14:26 sys
[aaa@qq.com opt]# ls -ld /var/lib/mysql
drwxr-x--x 5 mysql mysql 4096 2月 13 14:26 /var/lib/mysql
##要保证MySQL的所属组和所属主的文件是MySQL,否则数据保存不了,就进行不了下一步操作,因为mysql没有w和x权限就执行不了
3.连接MySQL数据库
- 初始密码登录
- 数据库管理员名为root
- 默认仅允许root本机连接
- 首次登录密码在安装软件时随机生成
- 随机密码存储在日志文件 /var/log/mysql.log里
- 连接命令: mysql -h数据库地址 -u用户 -p密码
##查看Mysql初始密码连接数据库,每个人的初始密码都是不一样的
[aaa@qq.com opt]# grep "password" /var/log/mysqld.log ##过滤初始密码
2020-02-13T06:25:51.681618Z 1 [Note] A temporary password is generated for aaa@qq.com: ,gtruI,fw78h
[aaa@qq.com opt]# mysql -uroot -p",gtruI,fw78h" ##连接数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases; ##查看已有的库,发现报错
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
##这句话的意思是 在执行这条命令之前,必须使用ALTER USER修改密码
修改root密码
具体操作如下:
使用ALTER USER 命令修改登录密码
密码如果是 ! $ & ( ) < > ` ; | 属于特殊符号
\ "" 反斜线和双引号不能用作密码
'' 单引号需要用双引号""才能连接数据库
新密码必须满足密码策略
管理员root 使用新密码连接服务
##修改root密码,要符合密码策略,不能太简单,否则报错.
错误演示:
mysql> alter user aaa@qq.com"localhost" identified by "123456";
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
##这里提示密码过于简单,不符合密码策略
正确操作:
mysql> alter user aaa@qq.com"localhost" identified by "123qqq...A";
Query OK, 0 rows affected (0.00 sec)
测试是否能登录:
使用exit或quit可以退出数据库
mysql> exit
Bye
[aaa@qq.com opt]# mysql -uroot -p123qqq...A ##进入数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases; ##查看数据库,成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
##information_schema为虚拟库,所有数据都存在内存,默认这个库无法删除.
修改密码策略
修改密码策略
策略名称 验证方式
0 or LOW 长度
1 or MEDIUM(默认) 长度;数字,大小写字母,特殊符号
2 or STRONG 长度;数字,大小写字母,特殊符号,字典文件
查看变量:
mysql> show variables like "%password%";
+---------------------------------------+--------+
| Variable_name | Value |
+---------------------------------------+--------+
| default_password_lifetime | 0 |
| disconnect_on_expired_password | ON |
| log_builtin_as_identified_by_password | OFF |
| mysql_native_password_proxy_users | OFF |
| old_passwords | 0 |
| report_password | |
| sha256_password_proxy_users | OFF |
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+---------------------------------------+--------+
14 rows in set (0.00 sec)
修改密码策略:以下操作属于临时!!!
mysql> set global validate_password_policy=0; ##设置策略名称
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "validate_password_policy"; ##查看验证策略
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| validate_password_policy | LOW | ##(这里从MEDIUM变成了LOW)
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> set global validate_password_length=6; ##修改密码长度,默认值是8个字符
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "validate_password_length"; ##查看密码长度
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| validate_password_length | 6 |(这里从8变成了6)
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> alter user aaa@qq.com"localhost" identified by "123456"; ##修改登录密码为123456
Query OK, 0 rows affected (0.00 sec)
测试是否能登录成功:
mysql> exit
Bye
[aaa@qq.com opt]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
永久修改密码策略
[aaa@qq.com opt]# vim /etc/my.cnf
4 [mysqld] ##一定要在这一行下面写
5 validate_password_policy=0
6 validate_password_length=6
[aaa@qq.com opt]# systemctl restart mysqld ##重启服务
[aaa@qq.com opt]# mysql -uroot -p123456 ##进入数据库
mysql> show variables like "%password%"; ##查看密码策略
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| default_password_lifetime | 0 |
| disconnect_on_expired_password | ON |
| log_builtin_as_identified_by_password | OFF |
| mysql_native_password_proxy_users | OFF |
| old_passwords | 0 |
| report_password | |
| sha256_password_proxy_users | OFF |
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 6 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+---------------------------------------+-------+
14 rows in set (0.00 sec)
注意事项:
1.如果之前有mariadb,则需要先卸载,并删除对应的配置与数据:
[aaa@qq.com opt]# systemctl stop mariadb
2.删除/etc/my.cnf配置文件
此配置文件由RHEL自带的mariadb-libs库提供:
[aaa@qq.com opt]# rm -rf /etc/my.cnf
3.删除数据
[aaa@qq.com opt]# rm -rf /var/lib/mysql
4.删除软件包(没有会显示未安装软件包)
[aaa@qq.com opt]# rpm -e --nodeps mariadb-server maridb
数据库的基本管理和基本使用
1.连接数据库的方式
客户端连接MySQL服务的方法
①命令行
②web页面
③安装图形软件
④编写脚本(php,java,pyton...)
使用mysql命令
mysql -h服务器ip -u用户名 -p密码 [数据库名]
quit或exit 退出
2.把数据存储到数据服务器上的过程
客户端把数据存到数据库服务器上的步骤
①连接数据库服务器
②建库 ##类似于文件夹
③建表 ##类似于文件
④插入记录 ##类似于文件内容
⑤断开连接
3.1 SQL命令使用规则
SQL命令不区分大小写字母(密码,变量值除外)
每条SQL命令以;结束
默认命令不支持Tab键自动补齐(可以安装插件使数据库有Tab的功能)
\c 或者ctrl+c 终止sql命令
4.1 常用的sql命令分类
管理数据库使用SQL(结构化查询语言)
① DDL 数据定义语言 如: create,alter,drop
② DML 数据操作语言 如: insert,update,delete
③ DCL 数据控制语言 如: grant,revoke
④ DTL 数据事物语言 如: commit,rollback,savepoint
MySQL基本操作
库管理命令
- 库 类似于文件夹,用来存储表
- 可以创建多个库,通过库名区分
- 库名 命名规则
- 仅可以使用数字,字母,下划线,不能纯数字
- 区分字母大小写,具有唯一性
- 不可以使用指令关键字,特殊字符
show databases; ##显示已有的库
select use(); ##显示连接用户
use 库名; ##切换库
select database(); ##显示当前所在的库
create database 库名; ##创建新库
drop database 库名; ##删除库
为什么说库相当于文件夹?
mysql> create database db1; ##在数据库中创建一个名叫db1的库
Query OK, 1 row affected (0.00 sec)
[aaa@qq.com ~]# ls /var/lib/mysql ##查看数据库存放的路径下会多出一个db1的目录
auto.cnf db1 ibtmp1 private_key.pem
库管理命令的练习:
mysql> create database db1 ; ##创建库,名叫db1
mysql> exit ##退出
Bye
[aaa@qq.com opt]# mysql -uroot -p123456 db1 ##连接mysql进入db1库里
mysql>
mysql> select database(); ##查看当前所在库
+------------+
| database() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
mysql> show databases; ##查看已有库
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use sys; ##进入sys库中
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> select database();
+------------+
| database() |
+------------+
| sys |
+------------+
1 row in set (0.00 sec)
mysql> use mysql; ##切换到mysql库中
mysql> select database(); ##查看当前所在库
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
mysql> show tables; ##查看mysql表格
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> create database DB1; ##创建一个库叫DB1
Query OK, 1 row affected (0.00 sec)
mysql> drop database DB1; ##删除一个库名叫DB1
Query OK, 0 rows affected (0.00 sec)
表管理命令
建表
表存储数据的文件
mysql>create table 库名.表名(
>字段1 类型(宽度),
>字段2 类型(宽度),
......
>) default charset=utf8; ##指定中文字符集,可以给字段赋值中文
表 类似于文件
desc 库名.表名; ##查看表结构
第二种查看表结构:
desc 表名; ##需要切换到库里执行
drop table 库名.表名; ##删除表
为什么说表类似于文件?
mysql> create table db1.stuinfo( name char(15), homeaddr char(20); ##创建stuinfo表
[aaa@qq.com ~]# ls /var/lib/mysql/db1/ ##db1目录下会多出这两文件,只能以数据库格式打开
db.opt stuinfo.frm stuinfo.ibd
表管理命令练习
mysql> create database mydb; ##创建mydb库
mysql> create table mydb.student( ##创建表,不在mydb库里时要写库.表
-> 学号 char(9) NOT NULL,
-> 姓名 varchar(4) NOT NULL,
-> 性别 enum('男','女') NOT NULL,
-> 手机号 char(11) DEFAULT '',
-> 通信地址 varchar(64),
-> PRIMARY KEY(学号)
-> ) DEFAULT CHARSET=utf8; ##手工指定字符集，采用utf8
mysql> create table sutinfo(name char(10),homeaddr char(20)); ##创建表,第二种写法,在库里可省略库
Query OK, 0 rows affected (0.57 sec)
mysql> use db1; ##切换到db1库里
mysql> show tables; ##查看db1库所创建的表
+---------------+
| Tables_in_db1 |
+---------------+
| sutinfo |
+---------------+
1 row in set (0.00 sec)
mysql> desc sutinfo; ##查看表结构
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| homeaddr | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
记录管理命令
记录 类似于文件里的行
select * from 库名.表名; ##查看表记录
insert into 库名.表名 values(值列表); ##插入表记录
update from 表名; ##修改表记录
delete from 表名; ##删除表记录
where ##代表条件,条件写在where后面
记录管理命令
根据上面的练习而延续:
mysql> insert into db1.sutinfo values("bob","usa"),("lilei","china"); ##插入表记录
Query OK, 2 rows affected (0.31 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into db1.sutinfo values("jerry","js"); ##插入表记录
Query OK, 1 row affected (0.31 sec)
mysql> select * from db1.sutinfo; ##查看表内容
+-------+----------+
| name | homeaddr |
+-------+----------+
| bob | usa |
| lilei | china |
| jerry | js |
+-------+----------+
3 rows in set (0.00 sec)
mysql> update db1.sutinfo set homeaddr="bj"; ##修改表记录
Query OK, 3 rows affected (0.16 sec)
mysql> select * from db1.sutinfo; ##再次查看表内容,没有指定条件时默认是修改全部
+-------+----------+
| name | homeaddr |
+-------+----------+
| bob | bj |
| lilei | bj |
| jerry | bj |
+-------+----------+
3 rows in set (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> update db1.sutinfo set homeaddr="sh" where name="lilei"; ##指定当name等于lilei时,修改homeaddr为sh
mysql> select * from db1.sutinfo; ##查看表记录
+-------+----------+
| name | homeaddr |
+-------+----------+
| bob | bj |
| lilei | sh |
| jerry | bj |
+-------+----------+
3 rows in set (0.00 sec)
mysql> delete from db1.sutinfo where name="lilei"; 删除db1库下的sutinfo表格内容,条件当name=lilei时
Query OK, 1 row affected (0.05 sec)
mysql> select * from db1.sutinfo; ##查看表记录
+-------+----------+
| name | homeaddr |
+-------+----------+
| bob | bj |
| jerry | bj |
+-------+----------+
2 rows in set (0.00 sec)
mysql> delete from db1.sutinfo; ##删除db1库下sutinfo所有记录,delete只能删除表记录
Query OK, 2 rows affected (0.05 sec)
mysql> select * from db1.sutinfo; ##查看表记录,提示没有记录
Empty set (0.00 sec)
mysql> show tables; ##查看表,还在
+---------------+
| Tables_in_db1 |
+---------------+
| sutinfo |
+---------------+
1 row in set (0.00 sec)
mysql> drop table db1.sutinfo; ##删除表
Query OK, 0 rows affected (0.10 sec)
mysql> show tables; ##表已删除
Empty set (0.00 sec)
建表时指定表的字符集,使其可以存储中文
##字符集: 就是所有汉子的集合,类似于字典
没有指定字符集的时候不能打中文,否则报错,例如:
mysql> show create table db1.user; ##在.db1库下建user表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| user |
+---------------+
1 row in set (0.00 sec)
mysql> insert into db1.user values("张三"); ##插入表记录报错
ERROR 1146 (42S02): Table 'db1.user' doesn't exist
mysql> show create table db1.user\G; ##查看表的详细信息,\G代表以列的方式查看
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 (字符集不是utf8)
1 row in set (0.00 sec)
mysql> create table db1.user2(name char(3))default charset=utf8; 指定中文字符集;
mysql> show create table db1.user2\G; ##
*************************** 1. row ***************************
Table: user2
Create Table: CREATE TABLE `user2` (
`name` char(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 (字符集为utf8,代表可以插入中文字符集)
1 row in set (0.00 sec)
mysql> insert into db1.user2 values("张三疯"); ##插入中文字符集,成功
Query OK, 1 row affected (0.03 sec)
mysql> select * from db1.user2;
+-----------+
| name |
+-----------+
| 张三疯 |
+-----------+
1 row in set (0.00 sec)
修改MySQL服务的默认字符集，可以更改服务器的my.cnf配置文件，添加character_set_server=utf8 配置，然后重启数据库服务。这样就永久生效了.
更改前查看字符变量:
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 ##默认识别不了中文 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
mysql> quit;
Bye
[aaa@qq.com ~]# vim /etc/my.cnf
[mysqld]
5 character_set_server=utf8
[aaa@qq.com ~]# systemctl restart mysqld
[aaa@qq.com ~]# mysql –u root -p123456
mysql> show variables like '%character%'; ##确认更改结果
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 ##变成了了utf8字符集 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
MySQL数据类型
常见信息种类
1.数值型: 体重,身高,成绩,工资
2.字符型: 姓名,工作单位.通信地址
3.枚举型: 兴趣爱好,性别,专业
4.日期时间型: 出生日期,注册时间
数值类型
- 整数型
- 只能存整数
类型 名称 有符号范围 无符号范围
tinyint 微小整数 -128~127 0~255
smalint 小整数 -32768~32767 0~65535
mediumint 中整数 -2^23~2^23-1 0~2^24-1
int 大整数 -2^31~2^31-1 0~2^32-1
bigint 极大整数 -2^63~2^63-1 0~2^64-1
unsigned 使用无符号存储范围
练习演示:
##设置整数类型为微小整数的无符号范围
mysql> create database db1;
mysql> create table db1.t2(name char(10),age tinyint unsigned);
mysql> insert into db1.t2 values("bob",25);
mysql> insert into db1.t2 values("tom",1);
mysql> insert into db1.t2 values("jerry",19.5); ##四舍五入
mysql> insert into db1.t2 values("jack",19.4); ##同理,四舍五入
mysql> select * from db1.t2;
+--------------+
| name | age |
+--------------+
| bob | 25 |
| tom | 1 |
| jerry | 20 |
| jack | 19 |
+--------------+
4 rows in set (0.00 sec)
注意事项:
##数值不在0~255范围内就会报错
mysql> insert into db1.t2 values("bob",256);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
mysql> insert into db1.t2 values("bob",-1);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
- 浮点型
- 格式1: 字段名 类型;
- 格式2: 字段名 类型(总宽度(整数位+小数位),小数位)
- float(n,m)
- double(n,m)
两者的区别:
单精度,也就是float,在32位机器上用4个字节来存储的,也就是2的32次方;
而双精度double是用8个字节来存储的,也就是2的8次方.双精度比单精度占用的空间大.
单精度和双精度在计算机的表示格式虽然一样,但由于位数存储位不同,他们能表示的数值的范围就不同,也就是说能精确表示的位数不同.
练习演示:
##设置t3表为单精度表
mysql> create table db1.t3(name char(10), gz float(7,2));
mysql> desc db1.t3; ##查看表结构
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| gz | float(7,2) | YES | | NULL | |
+- -----+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into db1.t3 values("jack",6888); ##插入表记录
mysql> select * from db1.t3; ##查看表记录
+------+---------+
| name | gz |
+------+---------+
| jack | 6888.00 |
+------+---------+
1 row in set (0.00 sec)
mysql> insert into db1.t3 values("jack",18888.23); ##插入表记录
mysql> select * from db1.t3;
+------+----------+
| name | gz |
+------+----------+
| jack | 6888.00 |
| jack | 18888.23 |
+------+----------+
2 rows in set (0.01 sec)
注意事项:
总宽度大于7就报错
mysql> insert into db1.t3 values("jack",118888.23);
ERROR 1264 (22003): Out of range value for column 'gz' at row 1
字符类型
定长:char(字符个数)
最大字符个数255
不够指定字符个数时在右边用空格补全
字符个数超出时,无法写入数据
适合存姓名,工作单位,通信地址
变长: varchar(字符个数)
最大存储字符个数65535
按数据实际大小分配存储空间
字符个数超出时,无法写入数据.
适合存储邮箱
定长char与变长varchar的区别
char是指固定的长度,也就是说建表的时候规定的长度来计算存储空间
什么叫规定的长度,以下面练习为例,class和name用的是char类型,定长7个字符和20个字符,
当class或者name存储的字节不够7或者20时,后面会以空格补全,这样占用的空间就是实际定长的空间.
varchar是指可以变动的长度,也就是说建表时规定的长度按照实际存储的字节来计算存储空间
什么叫变动的长度,以下面练习为例,email用的是varchar类型,定长为20,当email存储的字节长度不够20时,按照它实际存储字节来定义存储空间
大文本类型: test/blob
字符个数65535存储时使用
适合存储视频文件,音频文件,图片
练习演示:
mysql> create table db1.ti(class char(7),name char(20),email varchar(50));
mysql> insert into db1.ti values("NSD1911","bob","aaa@qq.com");
mysql> insert into db1.ti values("NSD1911","jerry","aaa@qq.com");
mysql> insert into ti(class,name) values("102","tian");
mysql> select * from db1.ti;
+---------+-------+---------------+
| class | name | email |
+---------+-------+---------------+
| nsd1911 | bob | aaa@qq.com |
| nsd1911 | jerry | aaa@qq.com |
| 102 | tian | NULL |
+---------+-------+---------------+
3 rows in set (0.00 sec)
mysql> desc ti;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| class | char(7) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
日期时间类型
类型:
日期时间 datetime
范围: 1000-01-01 00:00:00~9999-12-31 23:59:59 24小时制
格式: YYYYMMDDHHMMSS
日期时间 timestamp
范围:1970-01-01 00：00：00～2038-01-19 00：00：00
格式：YYYYMMDDHHMMSS
datetime和timestamp两者的区别:
当未给timestamp字段赋值时,自动以当前系统时间赋值,而datetime值为null(代表为空)
日期 date
范围: 0001-01-01~9999-12-31
格式：YYYYMMDD
年 year
范围:1901~2155
格式:YYYY
类型:
要求使用4位数赋值
当使用2位数赋值时:
01~69视为2001~2069
70~99视为1970~1999
时间 time
格式:HH:MM:SS
获取日期和时间的命令
- 时间函数
- MySQL服务内置命令
- 可以使用时间函数给字段赋值
- 适合存储出生日期,注册时间
类型 用途
curtime() 获取当前的系统时间
curdate() 获取当前的系统年份和日期
now() 获取当前的日期和时间
year() 获取当前系统的年份
month() 获取当前系统的月份
day() 获取当前系统的
date() 获取当前的系统年份和日期
time() 获取当前系统的时间
select curtime() 查看当前系统的时间
select time(now()); 查看当前系统的时间,效果同上
select curdate() 查看当前系统的年份日期
select date(now()); 查看当前系统的年份日期,效果同上
练习演示
mysql> create table db1.t5 (name char(15),csnf year, birthday date,up_class time, party datetime );
mysql> insert into db1.t5 values ("bob",1990,20201120,093000,20200214180000);
mysql> select * from db1.t5;
+------+------+------------+----------+---------------------+
| name | csnf | birthday | up_class | party |
+------+----------+--------+----------+---------------------+
| bob | 1990 | 2020-11-20 | 09:30:00 | 2020-02-14 18:00:00 |
+------+----------+--------+----------+---------------------+
1 row in set (0.00 sec)
mysql> insert into db1.t5 values ("jerry", year(now()),date(now()),time(now()),now());
mysql> select * from db1.t5;
+-------+------+------------+----------+---------------------+
| name | csnf | birthday | up_class | party |
+-------+------+------------+----------+---------------------+
| bob | 1990 | 2020-11-20 | 09:30:00 | 2020-02-14 18:00:00 |
| jerry | 2020 | 2020-02-14 | 09:40:40 | 2020-02-14 09:40:40 |
+-------+------+------------+----------+---------------------+
2 rows in set (0.00 sec)
##使用命令的值给日期时间类型字段赋值
mysql> insert into db1.t5 values ("tom",81, date(now()),time(now()),now());
mysql> insert into db1.t5 values ("john",51, date(now()),time(now()),now());
mysql> select * from db1.t5;
+-------+------+------------+----------+---------------------+
| name | csnf | birthday | up_class | party |
+-------+------+------------+----------+---------------------+
| bob | 1990 | 2020-11-20 | 09:30:00 | 2020-02-14 18:00:00 |
| jerry | 2020 | 2020-02-14 | 09:40:40 | 2020-02-14 09:40:40 |
| tom | 1981 | 2020-02-14 | 09:53:21 | 2020-02-14 09:53:21 |
| john | 2051 | 2020-02-14 | 09:53:34 | 2020-02-14 09:53:34 |
+-------+------+------------+----------+---------------------+
4 rows in set (0.00 sec)
mysql> create table db1.t6 (name char(10),meetting datetime, pary timestamp);
mysql> insert into db1.t6 values ("dingmy",20200214130000,20200214183000);
mysql> select * from db1.t6;
+--------+---------------------+---------------------+
| name | meetting | pary |
+--------+---------------------+---------------------+
| dingmy | 2020-02-14 13:00:00 | 2020-02-14 18:30:00 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)
##以下为datetime与timestamp的区别:
mysql> insert into db1.t6 values("nb",20200214140000,20200214100418);
mysql> select * from db1.t6;
+--------+---------------------+---------------------+
| name | meetting | pary |
+--------+---------------------+---------------------+
| dingmy | 2020-02-14 13:00:00 | 2020-02-14 18:30:00 |
| nb | 2020-02-14 14:00:00 | 2020-02-14 10:04:18 |
+--------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> insert into db1.t6(name,pary) values("wk",20200214203000);
mysql> select * from db1.t6;
+--------+---------------------+---------------------+
| name | meetting | pary |
+--------+---------------------+---------------------+
| dingmy | 2020-02-14 13:00:00 | 2020-02-14 18:30:00 |
| nb | 2020-02-14 14:00:00 | 2020-02-14 10:04:18 |
| wk | NULL | 2020-02-14 20:30:00 |
+--------+---------------------+---------------------+
3 rows in set (0.00 sec)
##时间函数:
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 09:35:27 |
+-----------+
1 row in set (0.00 sec)
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-02-14 |
+------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-02-14 09:36:14 |
+---------------------+
1 row in set (0.00 sec)
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2020 |
+-------------+
1 row in set (0.00 sec)
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 14 |
+------------+
1 row in set (0.00 sec)
mysql> select time(now());
+-------------+
| time(now()) |
+-------------+
| 09:37:34 |
+-------------+
1 row in set (0.00 sec)
枚举类型
- 字段的值必须在类型列举的范围内选择
enum 单选:
格式: 字段名 enum(值列表)
仅能选择一个值
字段值必须在列表里选择
set 多选:
格式; 字段名 set(值列表)
选择一个或多个值
字段值必须在列表里选择
什么是枚举类型?
比如: 兴趣爱好,专业,性别
练习演示:
mysql> create table db1.t7 (name char(15),sex enum("boy","girl"),likes set("eat","game","money","it"));
mysql> desc t6;
+----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+-------------------+-----------------------------+
| name | char(10) | YES | | NULL | |
| meetting | datetime | YES | | NULL | |
| pary | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
mysql> desc db1.t7\G ##以列的形式查看表结构
*************************** 1. row ***************************
Field: name
Type: char(15)
Null: YES
Key:
Default: NULL
Extra:
*************************** 2. row ***************************
Field: sex
Type: enum('boy','girl')
Null: YES
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: likes
Type: set('eat','game','money','it')
Null: YES
Key:
Default: NULL
Extra:
3 rows in set (0.00 sec)
mysql> insert into db1.t7 values ("nb","boy","eat,it");
Query OK, 1 row affected (0.05 sec)
mysql> insert into db1.t7 values ("wk","girl","it");
Query OK, 1 row affected (0.05 sec)
mysql> select * from db1.t7;
+------+------+--------+
| name | sex | likes |
+------+------+--------+
| nb | boy | eat,it |
| wk | girl | it |
+------+------+--------+
2 rows in set (0.01 sec)
##注意事项:要严格遵循表的类型填写
##不知道字段类型可以用desc 库名.表名\G或者show create table 库名.表名; 查看
mysql> insert into db1.t7 values ("wk","man","film,book");
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
提示sex类型填错
mysql> insert into db1.t7 values ("wk","girl","film,book");
ERROR 1265 (01000): Data truncated for column 'likes' at row 1
提示likes类型的填错
上一篇： MongoDB复制(副本集)
下一篇： Docker Swarm集群