Mysql基础（一）
1. 常见的数据库有哪些呢？
- 关系型数据库：数据存放在硬盘中
- MySQL（5.7之前都是开源的、8.0开始商业化）
- mariadb（从10.0版本开始）
- MSsql
- oracle（商业软件）
- sqlserver
- nosql非关系型数据库：数据存放在内存中、变量形式、key=value
- mongodb
- redis
- memcache
- 存放在文件中（数据文件，只能用sql语句来进行管理）
- sqlite
字段：每一列的第一行就是字段
记录：每一行代表一条记录
2. 数据库管理系统：
DBMS DateBase Management System
Relationship 关系
RDBMS 关系型数据库系统
3. 关系型数据库的常见组件：
| database | 数据库 |
|---|---|
| table，由行（row）和列（column）组成 | 表 |
| index | 索引 |
| view | 试图 |
| user | 用户 |
| privilege | 权限 |
| procedure | 存储过程 |
| function | 存储函数 |
| trigger | 触发器 |
| event scheduler | 事件调度器 |
4. SQL语句三种类型：
- DDL：数据定义语言
- CREATE 创建
- DROP 删除
- ALTER 修改
- DML：数据操纵语言
- INSERT 向表中插入数据
- DELETE 删除表中数据
- UPDATE 更新表中数据
- SELECT 查询表中数据
- DCL：数据控制语言
- GRANT 授权
- REVOKE 移除授权
5. MySQL数据类型
MySQL中定义数据字段的类型对数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串（字符）类型。
数值类型
MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型（INTEGER、SMALLINT、DECIMAL和NUMERIC），以及近似数值数据类型（FLOAT、REAL和DOUBLE PRECISION）。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
6. 日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。
| 类型 | 大小( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
7. 字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。
8. yum安装mysql
8.1 mysql安装
//配置mysql的yum源
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
--2022-07-25 14:05:39-- http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
······
Saving to: 'mysql57-community-release-el7-10.noarch.rpm'
mysql57-community-r 100%[===================>] 24.95K 37.5KB/s in 0.7s
2022-07-25 14:05:46 (37.5 KB/s) - 'mysql57-community-release-el7-10.noarch.rpm' saved [25548/25548]
[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-10.noarch.rpm
warning: mysql57-community-release-el7-10.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying... ################################# [100%]
Preparing... ################################# [100%]
Updating / installing...
1:mysql57-community-release-el7-10 ################################# [100%]
//禁用mysql
[root@localhost ~]# yum module disable mysql
Failed to set locale, defaulting to C.UTF-8
MySQL Connectors Community 8.3 kB/s | 49 kB 00:05
MySQL Tools Community 237 kB/s | 651 kB 00:02
MySQL 5.7 Community Server 279 kB/s | 2.6 MB 00:09
Last metadata expiration check: 0:00:02 ago on Mon Jul 25 14:09:12 2022.
Dependencies resolved.
================================================================================
Package Architecture Version Repository Size
================================================================================
Disabling modules:
mysql
Transaction Summary
================================================================================
Is this ok [y/N]: y
Complete!
//安装mysql并且不检查来源
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:00:50 ago on Mon Jul 25 14:09:12 2022.
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
······
pkgconf-1.4.2-1.el8.x86_64
pkgconf-m4-1.4.2-1.el8.noarch
pkgconf-pkg-config-1.4.2-1.el8.x86_64
Complete!
8.2 mysql配置
//启动mysql并设置开机自启
[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor pres>
Active: active (running) since Mon 2022-07-25 14:14:51 CST; 16s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 110458 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/my>
Process: 110314 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status>
Main PID: 110461 (mysqld)
Tasks: 27 (limit: 5748)
Memory: 314.7M
CGroup: /system.slice/mysqld.service
└─110461 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mys>
Jul 25 14:14:48 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 25 14:14:51 localhost.localdomain systemd[1]: Started MySQL Server.
//查看端口3306是否监听
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
[root@localhost ~]#
//过滤日志文件找到临时密码
[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-07-25T06:14:49.241724Z 1 [Note] A temporary password is generated for root@localhost: i2sdOxozkl+g
//复制密码登录mysql
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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密码
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'lishuai123!';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
//避免mysql自动升级，卸载刚安装的yum源
[root@localhost ~]# rpm -e mysql57-community-release
[root@localhost ~]# cd /etc/yum.repos.d/
[root@localhost yum.repos.d]# ls
ls.repo
[root@localhost yum.repos.d]#
9. yum安装mariadb
[root@localhost ~]# yum -y install mariadb*
Failed to set locale, defaulting to C.UTF-8
appstream 4.3 MB/s | 4.4 kB 00:00
BaseOS 1.3 MB/s | 3.9 kB 00:00
Dependencies resolved.
================================================================================
Package Arch Version Repo Size
================================================================================
Installing:
······
tzdata-java-2021e-1.el8.noarch
unixODBC-2.3.7-1.el8.x86_64
zlib-devel-1.2.11-17.el8.x86_64
Complete!
[root@localhost ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> set password=password('lishuai123!')
-> ;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> exit
Bye
[root@localhost ~]# mysql -uroot -plishuai123!
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
10. mysql工具使用
//查看当前使用的mysql版本
[root@localhost ~]# mysql -V
mysql Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1
//登录mysql指定用户名root、指定密码lishuai123！、指定服务器主机，默认为localhost，推荐使用IP地址
[root@localhost ~]# mysql -uroot -plishuai123! -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> exit
Bye
//不登录mysql执行查看数据库sql语句后退出，常用于脚本
[root@localhost ~]# mysql -uroot -p -h 127.0.0.1 -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@localhost ~]#
11. DDL操作
11.1 数据库操作
//创建数据库，名字为lishuai
MariaDB [(none)]> create database if not exists lishuai;
Query OK, 1 row affected (0.000 sec)
//查看系统当前数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lishuai |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
//删除数据库
MariaDB [(none)]> drop database if exists lishuai;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]>
11.2 表操作
//创建数据库lishuai
MariaDB [(none)]> create database lishuai;
Query OK, 1 row affected (0.001 sec)
//进入lishuai数据库
MariaDB [(none)]> use lishuai;
Database changed
//创建lishuai表
MariaDB [lishuai]> create table lishuai (id int not null,name varchar(10) not null,age tinyint);
Query OK, 0 rows affected (0.002 sec)
//查看当前数据库中的表
MariaDB [lishuai]> show tables;
+-------------------+
| Tables_in_lishuai |
+-------------------+
| lishuai |
+-------------------+
1 row in set (0.000 sec)
//查看表结构
MariaDB [lishuai]> desc lishuai;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
//删除表lishuai
MariaDB [lishuai]> drop table lishuai;
Query OK, 0 rows affected (0.002 sec)
MariaDB [lishuai]> show tables;
Empty set (0.000 sec)
MariaDB [lishuai]>
11.3 用户操作
//创建数据库用户george
MariaDB [(none)]> create user 'george@127.0.0.1' identified by 'lishuai123!'
-> ;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> exit
Bye
//使用新创建的用户和密码登录
[root@localhost ~]# mysql -u'george@127.0.0.1' -plishuai123! -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
//删除数据库用户
MariaDB [(none)]> drop user 'george@127.0.0.1';
Query OK, 0 rows affected (0.000 sec)
11.4 查看命令show
//查看支持的所有字符集
MariaDB [(none)]> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
······
//查看当前数据库支持的所有存储引擎
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
······
//查看数据库信息
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lishuai |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
//不进入某数据库而列出其包含的所有表
MariaDB [(none)]> show tables from lishuai;
+-------------------+
| Tables_in_lishuai |
+-------------------+
| george |
+-------------------+
1 row in set (0.000 sec)
//查看表结构
MariaDB [(none)]> desc lishuai.george;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
//查看某表创建的命令
MariaDB [(none)]> show create table lishuai.george;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| george | CREATE TABLE `george` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
//查看某表的状态
MariaDB [(none)]> use lishuai;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [lishuai]> show table status like 'george'\G
*************************** 1. row ***************************
Name: george
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2022-07-25 14:59:49
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.001 sec)
MariaDB [lishuai]>
11.5 获取帮助
//获取创建表的帮助
MariaDB [(none)]> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
······
Rules for permissible table names are given in
https://mariadb.com/kb/en/identifier-names/. By default,
the table is created in the default database, using the InnoDB storage
engine. An error occurs if the table exists, if there is no default
database, or if the database does not exist.
URL: https://mariadb.com/kb/en/create-table/
扫一扫
382
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
