MySQL数据库系统学习
一，了解数据库
1.什么是数据库
英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。
顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。
2.什么是SQL
SQL：结构化查询语言
程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。
3.关于SQL语句的分类
SQL语句有很多，最好进行分门别类，这样更容易记忆。
分为：
DQL： 数据查询语言（凡是带有select关键字的都是查询语句） select... DML： 数据操作语言（凡是对表当中的数据进行增删改的都是DML） insert delete update insert 增 delete 删 update 改 这个主要是操作表中的数据data。 DDL： 数据定义语言 凡是带有create、drop、alter的都是DDL。 DDL主要操作的是表的结构。不是表中的数据。 create：新建，等同于增 drop：删除 alter：修改 这个增删改和DML不同，这个主要是对表结构进行操作。 TCL： 不是王牌电视。 是事务控制语言 包括： 事务提交：commit; 事务回滚：rollback; DCL： 是数据控制语言。 例如：授权grant、撤销权限revoke....
4.什么是MySQL
MySQL是数据库管理系统中的一种，是市面上最流行的数据库管理软件之一 MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。 MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。
5.SQL通用语法
- SQL语句可以单行或多行书写，以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
- 注释
- 单行注释：–注释内容或#注释内容（MySQL特有）
- 多行注释：/* 注释内容 */
二，基本环境使用命令
1.设置root的初始密码(四种方法)
mysql_secure_installation #使用MySQL自带的初始化密码命令进行初始化 mysqladmin -uroot -p password 123 #使用mysqladmin设置密码 登录数据库：grant all on *.* to root@localhost identified by '123'; #进入数据库来进行修改用户密码 登录数据库：alter user root@localhost identified by '123.com'; #进入数据库来进行修改用户密码
2.连接MySQL服务器（三种方式）
mysql -uroot -p123 -h 192.168.2.1 -P 3306 #mysql客户端工具进行连接 mysql -uroot -p123 -S /tmp/mysql.sock #仅可以本地登录 使用类似navicat工具来进行连接
MySQL常用选项： -u 用户 -p 密码 -h IP -P 端口 -S socket文件 -e 免交互执行命令 < 导入SQL脚本
3.MySQL的基本操作
mysql> create database wordpress default charset utf8mb4 collate utf8mb4_bin; # 创建库 Query OK, 1 row affected (0.00 sec) mysql> show create database wordpress; #查看创库语句 +-----------+-------------------------------------------------------------------------------------------+ | Database | Create Database | +-----------+-------------------------------------------------------------------------------------------+ | wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ | +-----------+-------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> use wordpress #进入库 Database changed mysql> create table t1 (id int primary key,name varchar(20)); #创建表 Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1,'zhangsan'),(2,'lisi'),(3,'wuwang'); #插入数据 Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1; #查看表内容 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wuwang | +----+----------+ 3 rows in set (0.00 sec) mysql> desc t1; #查看表架构 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> show create table t1; #查看创表语法 +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL, `name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
4.基本管理命令
1.管理用户
mysql> select user,host from mysql.user; #查看mysql中有那些用户 +---------------+-----------+ | user | host | +---------------+-----------+ | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 3 rows in set (0.00 sec) mysql> create user lisi@'192.168.2.3'; #创建lisi用户 Query OK, 0 rows affected (0.00 sec) mysql> select user,host,authentication_string from mysql.user; #查看 +---------------+-------------+-------------------------------------------+ | user | host | authentication_string | +---------------+-------------+-------------------------------------------+ | root | localhost | | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | lisi | 192.168.2.3 | | +---------------+-------------+-------------------------------------------+ 4 rows in set (0.01 sec) mysql> alter user lisi@'192.168.2.3' identified by '123.com'; #修改lisi用户密码 Query OK, 0 rows affected (0.00 sec) mysql> select user,host,authentication_string from mysql.user; #查看用户表 +---------------+-------------+-------------------------------------------+ | user | host | authentication_string | +---------------+-------------+-------------------------------------------+ | root | localhost | | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | lisi | 192.168.2.3 | *AC241830FFDDC8943AB31CBD47D758E79F7953EA | +---------------+-------------+-------------------------------------------+ 4 rows in set (0.00 sec) mysql> drop user lisi@'192.168.2.3'; #删除用户 Query OK, 0 rows affected (0.00 sec) mysql> select user,host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | +---------------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec)
2.管理用户权限
1.创建用户
mysql> grant all on wordpress.* to wordpress@'192.168.2.%' identified by '123.com'; Query OK, 0 rows affected, 1 warning (0.00 sec)
解释：
ALL:SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPAC ALL：以上所有权限，一般是普通管理员拥有 wordpress.*：wordpress库下的所有表 wordpress@'192.168.2.%'：wordpress用户名，只允许192.168.2网段来进行访问 %代表任意 identified by '123.com'：设置密码
2.查看用户授权
mysql> show grants for wordpress@'192.168.2.%'; +--------------------------------------------------------------------+ | Grants for wordpress@192.168.2.% | +--------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'wordpress'@'192.168.2.%' | | GRANT ALL PRIVILEGES ON `wordpress`.* TO 'wordpress'@'192.168.2.%' | +--------------------------------------------------------------------+ 2 rows in set (0.00 sec)
3.收回权限
mysql> revoke delete on wordpress.* from wordpress@'192.168.2.%'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for wordpress@'192.168.2.%'; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for wordpress@192.168.2.% | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'wordpress'@'192.168.2.%' | | GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `wordpress`.* TO 'wordpress'@'192.168.2.%' | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
5.多实例
1.安装二进制MySQL
rpm -e mariadb-libs-5.5.52-1.el7.x86_64 --nodeps tar zxf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ mv /usr/local/mysql-5.7.36-linux-glibc2.12-x86_64/ /usr/local/mysql mkdir /usr/local/mysql/data/ groupadd mysql && useradd -r -M -s /sbin/nologin -g mysql mysql chown mysql:mysql /usr/local/mysql/ -R ln -s /usr/local/mysql/bin/* /usr/local/bin/ yum install -y ncurses-devel libaio-devel autoconf mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ vi /etc/my.cnf [mysqld] user=mysql basedir=/usr/local/mysql/ datadir=/usr/local/mysql/data/ socket=/usr/local/mysql/mysql.sock log_error=/usr/local/mysql/data port=3306 server_id=1 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld /etc/init.d/mysqld start
2.创建多实例文件
mkdir -p /usr/local/mysql/330{7,8}/data
3.编写MySQL配置文件
[root@localhost system]# vi /usr/local/mysql/3307/my.cnf [mysqld] user=mysql basedir=/usr/local/mysql/ datadir=/usr/local/mysql/3307/data/ socket=/usr/local/mysql/3307/data/mysql.sock log_error=/usr/local/mysql/3307/data/mysql.log port=3307 server_id=2 [root@localhost system]# vi /usr/local/mysql/3308/my.cnf [mysqld] user=mysql basedir=/usr/local/mysql/ datadir=/usr/local/mysql/3308/data/ socket=/usr/local/mysql/3308/data/mysql.sock log_error=/usr/local/mysql/3308/data/mysql.log port=3308 server_id=3 [root@localhost system]# chown mysql:mysql /usr/local/mysql/ -R
4.初始化数据库
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/3308/data/ mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/3307/data/
5.常见系统启动mysql文件
[root@localhost system]# cd /etc/systemd/system [root@localhost system]# vi mysqld.service [Unit] Description=MYSQL server After=network.target [Install] WantedBy=multi-user.target [Service] Type=forking TimeoutSec=0 PermissionsStartOnly=true ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize $OPTIONS ExecReload=/bin/kill -HUP -$MAINPID #这里-HUP可以是改成-s HUP，就变成强制杀进程，有需要可以改，下面也一样 ExecStop=/bin/kill -QUIT $MAINPID #-s QUIT是强制杀进程 KillMode=process LimitNOFILE=65535 Restart=on-failure RestartSec=10 RestartPreventExitStatus=1 PrivateTmp=false [root@localhost system]# cp mysqld.service mysqld3307.service [root@localhost system]# vi mysqld3307.service #修改一下数据 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf [root@localhost system]# cp mysqld.service mysqld3308.service [root@localhost system]# vi mysqld3308.service #修改一下数据 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
6.启动mysql
systemctl daemon-reload systemctl start mysqld3307.service systemctl start mysqld3308.service
7.测试
[root@localhost system]# netstat -anptl | grep mysqld tcp6 0 0 :::3306 :::* LISTEN 18074/mysqld tcp6 0 0 :::3307 :::* LISTEN 28568/mysqld tcp6 0 0 :::3308 :::* LISTEN 29342/mysqld [root@localhost system]# mysql -S /usr/local/mysql/3307/data/mysql.sock #本地登录 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.36 MySQL Community Server (GPL) Copyright (c) 2000, 2021, 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> exit Bye
三，数据库定义语言（DDL）
1.DDL数据库操作
1.1.库操作
1.1.1.查询
查询所有数据库
show databases;
查询当前数据库
1.1.2.创建
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]
1.1.3.删除
drop database [is exists] 数据库名;
1.1.4.使用
use 数据库名;
1.2.表操作
1.2.1.查询当前数据库所有表
show table;
1.2.2.查询表结构
desc 表名;
1.2.3.查询指定表的建表语句
show create table 表名( 字段1 类型 [comment 注释], 字段2 类型 [comment 注释], 字段3 类型 [comment 注释] )[comment 表注释];
1.2.4.删除表
drop table 表名;
1.2.5.删除指定表，并重新创建表
truncate table 表明;
1.3.表管理
1.3.1.添加字段
alter table 表名 add 字段名 类型 [comment 注释] [约束];
1.3.2.修改数据类型
alter table 表名 modify 字段名 新类型;
1.3.3.修改字段名和字段类型
alter table 表名 change 旧字段 新字段 类型 [comment 注释] [约束];
1.3.4.删除字段
alter table 表名 drop 字段名;
1.3.5.修改表名
alter table 表名 rename to 新表名;
2.管理数据库
1.查看MySQL创建格式
mysql> help create database; Name: 'CREATE DATABASE' Description: Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ... create_option: [DEFAULT] { CHARACTER SET [=] charset_name | COLLATE [=] collation_name } 解释： create：创建语句 database：创建数据库 schema：同database if not exists：在创建数据库之前进⾏判断，只有该数据库⽬前尚不存在时才能执⾏操作。此选项可以⽤来避免数据库已经存在⽽重复创建的错误 db_name：自定义创建数据库名 create_option：字符编码选项 character set：字符编码 collate：校对排序规则 create database test default cartacter set=utf8 collate=utf8_bin；
2.查看系统支持的字符集
mysql> show charset; +----------+---------------------------------+---------------------+--------+ | 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 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.00 sec) 常用字符集解释： gb2312：简体中文汉字编码国家标准 gbk：可以说是对 GB2312 编码的扩充 gb18030：完全支持 Unicode，无需动用造字区即可支持中国国内少数民族文字、中日韩和繁体汉字以及 emoji 等字符，编码空间庞大，最多可定义 161 万个字符。 Unicode：国际标准字符集 utf-8：是一种对Unicode的可变长度字符编码。它可以用一至四个字节对Unicode字符集中的所有有效编码点进行编码，它逐渐成为网络传输中优先采用的编码方式。 utf-16：UTF-16 也是一种变长字符编码, 这种编码方式比较特殊, 它将字符编码成 2 字节 或者 4 字节
3.创建数据库
mysql> create database if not exists test default character set=gb18030 collate=gb18030_bin; Query OK, 1 row affected (0.00 sec) 解释： 创建一个字符集为gb18030排序规则gb18030_bin，名为test，如果存在将跳过创建，不出现报错信息，如果不存在进行创建
4.修改数据库字符集
mysql> alter database test default character set='utf8' ; #修改库的 Query OK, 1 row affected (0.00 sec) mysql> show create database test; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec)
5.修改表存储引擎
mysql> show create table test; +-------+-----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table test engine=MyISAM; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test; +-------+-----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
6.删除数据库
mysql> drop database test; #表内删除库 Query OK, 0 rows affected (0.00 sec) mysql> mysqladmin -u root -p drop test #表外删除库 password：
