mysql基础

最新推荐文章于 2023-10-17 18:16:51 发布

Couldrush

最新推荐文章于 2023-10-17 18:16:51 发布

阅读量110

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 数据库 postgresql

本文链接：https://blog.youkuaiyun.com/Couldrush/article/details/125982905

mysql基础

关系型数据库介绍

数据结构模型

数据结构模型主要有：

层次模型
网状结构
关系模型

关系模型：
二维关系：row，column

数据库管理系统：DBMS
关系：Relational，RDBMS

RDBMS专业名词

常见的关系型数据库管理系统：

MySQL：MySQL，MariaDB，Percona-Server
PostgreSQL：简称为pgsql
Oracle
MSSQL

**SQL：**Structure Query Language，结构化查询语言

**约束：**constraint，向数据表提供的数据要遵守的限制

主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
- 一个表只能存在一个
惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
- 一个表可以存在多个
外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
检查性约束

**索引：**将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

关系型数据库的常见组件

关系型数据库的常见组件有：

数据库：database
表：table，由行（row）和列（column）组成
索引：index
视图：view
用户：user
权限：privilege
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler

SQL语句

SQL语句有三种类型：

DDL：Data Defination Language，数据定义语言
DML：Data Manipulation Language，数据操纵语言
DCL：Data Control Language，数据控制语言

SQL语句类型	对应操作
DDL	CREATE：创建 DROP：删除 ALTER：修改
DML	INSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据
DCL	GRANT：授权 REVOKE：移除授权

mysql安装与配置

mysql安装

mysql安装方式有三种：

源代码：编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
程序包管理器管理的程序包：
- rpm：有两种
  - OS Vendor：操作系统发行商提供的
  - 项目官方提供的
- deb

//配置mysql的yum源
[root@192 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
...
[root@192 ~]#  rpm -Uvh mysql57-community-release-el7-10.noarch.rpm		//更新yum源
...
[root@192 ~]# yum module disable mysql		//禁用mysql
...
[root@192 ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel	--nogpgcheck	//安装mysql服务包，客户包，命令行，工具包，并不检查密钥合法性
...

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]#

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)]>

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 ~]#

DDL操作

数据库DDL操作

//创建数据库，名字为778
MariaDB [(none)]> create database if not exists 778;
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 778;
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)]>

表操作

//创建数据库778
MariaDB [(none)]> create database 778;
Query OK, 1 row affected (0.001 sec)

//进入lishuai数据库
MariaDB [(none)]> use 778;
Database changed
//创建lishuai表
MariaDB [778]> create table 778 (id int not null,name varchar(10) not null,age tinyint);
Query OK, 0 rows affected (0.002 sec)

//查看当前数据库中的表
MariaDB [778]> show tables;
+-------------------+
| Tables_in_778     |
+-------------------+
| 778               |
+-------------------+
1 row in set (0.000 sec)

//查看表结构
MariaDB [778]> desc 778;
+-------+-------------+------+-----+---------+-------+
| 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)

//删除表778
MariaDB [778]> drop table 778;
Query OK, 0 rows affected (0.002 sec)

MariaDB [778]> show tables;
Empty set (0.000 sec)

MariaDB [778]>

用户操作

//创建数据库用户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)

查看命令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 778;
+-------------------+
| Tables_in_778     |
+-------------------+
| george            |
+-------------------+
1 row in set (0.000 sec)

//查看表结构
MariaDB [(none)]> desc 778.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 778;
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 [778]> 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 [778]>

获得帮助

//获取创建表的帮助
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/