MySQL Tutorial

MySQL is a relational database management system developed by MySQL AB of Sweden and currently owned by Oracle. MySQL is one of the most popular relational database management systems. MySQL is one of the best RDBMS (Relational Database Management System) applications for WEB applications.

MariaDB is a branch of the MySQL source code, which is used in the same way as Mysql, and mariadb outperforms mysql in some respects.

Next, explain how to use mariab.

Install

MySQL/MariaDB 的安装比较简单，下面主要介绍以下 Mac/Linux 上的安装方法，以及基础设置。

MAC

1. 安装 Homebrew
   brew 是 Mac 下的一个包管理工具
   /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
2. 检查 Homebrew
   brew doctor
3. 更新 Homebrew
   brew update
4. 确认 MariaDB 版本
   brew info mariadb
5. 安装 MariaDB
   brew install mariadb
6. 运行 MariaDB 的安装程序
   unset TMPDIR
cd /usr/local/opt/mariadb/bin/
mysql_install_db
7. 运行 MariaDB
   后台启动
   brew services start mariaDB
   前台启动
   mysql.server start
8. 安全的完成安装,重设root用户的密码、移除匿名用户、移除默认的test数据等
   mysql_secure_installation
9. 添加环境变量
   vi ~/.bash_profile
export PATH=$PATH:/usr/local/opt/mariadb/bin:/usr/local/sbin/
   运行生效
   source ~/.bash_profile
10. 修改 root 密码
    直接运行 mysql,然后输入alter user 'root'@'localhost' identified by 'new_password';修改
11. 登录之后修改 root 密码
    set password for root@localhost=password('new_password');
    或者
    update user set password=password('888') where user='root';
12. 创建用户
    create user 'USERNAME'@'HOST' identified by 'PASSWORD';
13. 删除用户
    drop user 'USERNAME'@'HOST';
14. 用户授权
    grant privilege(PRIVILEGE1,PRIVILEGE2) on DATABASE.TABLE to ‘USERNAME'@'HOST' [identified by 'PASSWORD'];
    授予所有权限
    grant all on *.* to 'USERNAME'@'HOST';
    刷新权限
    flush privileges;
    查看用户获得的授权
    show grants for 'USERNAME'@'HOST';
15. 显示表结构
    desc TABLE_NAME

RedHat

1. 安装
   sudo yum install mariadb-server
2. 开启 mariadb 服务
   systemctl start mariadb
3. 设置 mariadb 服务为开机自启
   systemctl enable mariadb
4. 数据库配置
   mysql_secure_installation
5. 设置 MariaDB 字符集为 utf-8
   编辑 /etc/my.cnf, 输入

   1 2 3 4 5

   init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-character-set-client-handshake

   编辑 /etc/my.cnf.d/client.cnf, 在 [client] 下面输入

   1	default-character-set=utf8

   编辑 /etc/my.cnf.d/mysql-clients.cnf, 在 [mysql] 下面输入

   1	default-character-set=utf8

   注意新版本的 MariaDB 默认字符集的设置方法为
   编辑 /etc/my.cnf.d/server.cnf,在 [mysqld] 下面输入

   1 2 3 4 5 6

   [mysqld] default-storage-engine = innodb innodb_file_per_table max_connections = 4096 collation-server = utf8_general_ci character-set-server = utf8

   编辑 /etc/my.cnf.d/client.cnf,在 [client] 下面输入

   1	default-character-set=utf8

   重启 MariaDB 服务

   1	systemctl restart mariadb

   其他的数据库操作方法与 MAC 一致，数据库的使用方法见下面。

远程连接

数据库有许多远程连接的软件，如 navicat 等，下面主要介绍命令行中连接数据库的方法
mysql -u USER_NAME -h ADDRESS -P PORT -p PASSWORD -D DATABASE_NAME
端口一般为 3306

使用方法

MySQL 用连接软件连接或者使用 mysqladmin 管理的话，可以直接在 用户界面中操作，下面主要介绍在命令行中的使用方法

1. 创建数据库 create
create database DATABASE_NAME
2. 删除数据库 drop
drop database DATABASE_NAME
3. 选择数据库 use
use DATABASE_NAME

4. MySQL 数据类型

   • 数值类型

     | 类型 | 大小 | 用途 |
     |:—-:|:—-:|:—-:|
     |tinyint|1 字节|整数|
     |smallint|2 字节|整数|
     |mediumint|3 字节|整数|
     |int|4 字节|整数|
     |bigint|8 字节|整数|
     |float|4 字节|单精度浮点数|
     |double|8 字节|双精度浮点数|

   • 日期和时间类型

     | 类型 | 大小 | 格式 | 用途 |
     |:—-:|:—-:|:—-:|:—-:|
     |date|3 字节|YYYY-MM-DD|日期值|
     |time|3 字节|HH:MM:SS|时间值或持续时间|
     |year|1 字节|YYYY|年份值|
     |datetime|8 字节|YYYY-MM-DD HH:MM:SS|混合日期和时间值|
     |timestamp|4 字节|YYYYMMDDHHMMSS|混合日期和时间的时间戳|

   • 字符串类型

     | 类型 | 大小 | 用途 |
     |:—-:|:—-:|:—-:|
     |char|255 字节|定长字符串|
     |varchar|65535 字节|变长字符串|
     |tinytext|255 字节|短文本字符串|
     |text|65535 字节|长文本数据|
     |mediumtext|16777215 字节|中等长度文本数据|
     |longtext|4294967295 字节|极大文本数据|

5. 创建数据表 create
create table TABLE_NAME (COLUMN_NAME1 TYPE1,COLUMN_NAME2 TYPE2);
   具体使用如下

   1 2 3 4 5 6 7

   create table if not exists `TABLE_NAME`( `runoob_id` int unsigned auto_increment, `runoob_title` varchar(100) not null, `runoob_author` varchar(40) not null, `submission_date` date, primary key ( `runoob_id` ) )engine=InnoDB default charset=utf8;

• 如果字段设为 not null，则在操作数据库时如果输入的该字段为 null 就会报错。
• auto_increment 定义为自增属性，一般用于主键，数值会自动 +1
• primary key 关键字用于定义主键，主键可以定义多列，中间用逗号分割
• engine 设置存储引擎，charset 设置编码

1. 删除数据表 drop
drop table TABLE_NAME
2. 插入数据 insert
insert into TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2, COLUMN_NAME3) values (VALUE1, VALUE2, VALUE3);
   数据为字符串，必须使用 ‘ ’ 或 " "
3. 数据查询 select
select COLUMN_NAME1,COLUMN_NAME2 from TABLE_NAME where CONDITION limit N offset M;
limit 设置返回的记录数
   offset 指定 select 语句开始查询的数据偏移量，默认为 0
   where 子句可以使用 and 或者 or 指定一个或多个条件，where 也可以用于 delete 或 update
4. 数据更新 update
update TABLE_NAME set COLUMN_NAME1=VALUE1,COLUMN_NAME2=VALUE2 where CONDITION;
5. 删除数据 delete
delete from TABLE_NAME [where CONDITION];
6. 模糊匹配 like
like 后面是条件，条件可以使用正则，like 通常与 % 一起使用，% 表示任意字符，类似于正则表达式中的 *, 如果没有 %, like 相当于 =
select COLUMN1,COLUMN2 from TABLE_NAME where COLUMN like CONDITION1 [and[or]] CONDITION2=VALUE;
7. union 操作符
   SELECT_STATEMENT1 union [ all | distinct ] SELECT_STATEMENT2;
distinct 可选，删除结果集中重复的数据，union 操作符默认已经删除了重复数据，所以 distinct 对结果没有什么影响
   all 可选，返回所有结果，包含重复数据
8. 结果排序 order by
select COLUMN1,COLUMN2 from TABLE_NAME1 TABLE_NAME2 order by COLUMN1 [ asc | desc ],COLUMN2 [ asc | desc ];
asc 设置查询结果是升序，默认结果
   desc 设置查询结果是降序
9. 结果分组 group by、with rollup
select COLUMN_NAME, from TABLE_NAME where CONDITION group by COLUMN_NAME;
group by 对结果进行分组
   with rollup 对结果字段进行相同的统计
   以上两个语句都可以使用 count、sum、avg等
   coalesce 用来设置字段的取代名称，如果一个字段为 null ，则用 coalesce 中设置的值代替
   select coalesce(COLUMN_NAME,SPARE_COLUMN,SPARE_NAME); 如果 COLUMN_NAME 字段的值为 null，则用 SPARE_COLUMN 字段的值代替，如果两个都为 null，则用 SPARE_NAME 代替。SPARE_COLUMN 和 SPARE_NAME 可以使用多个，从前往后依次检查，直到不为 null 为止
10. 连接查询 join
inner join 内连接或等值连接，获取两个表中字段匹配关系的记录
    left join 左连接，获取左表所有的记录，即使右表没有对应匹配的记录
    right join 右连接，获取右表所有的记录，即使左表没有对应匹配的记录
    full outer join 完全外连接，完整连接所有表中的数据
    以上三种连接中空字段全部用 null 填充
    select COLUMN1,COLUMN2,COLUMN3 from TABLE1 inner join TABLE2 on CONDITION;
11. null 值处理
    MySQL 中的 null 值处理方法有两个，分别为 is null 和 is not null

    1 2	select * from TABLE_NAME where COLUMN is null; select * from TABLE_NAME where COLUMN is not null;

12. 正则表达式 regexp

1. 修改表名或字段 alter

   1 2 3 4 5

   alter table TABLE_NAME drop COLUMN; # 删除 TABLE_NAME 中的 COLUMN 字段 alter table TABLE_NAME add COLUMN TYPE [ first [COLUMN] | after [COLUMN] ]; # 添加字段 alter table TABLE_NAME modify COLUMN NEW_TYPE; # 修改字段类型 alter table TABLE_NAME change OLD_COLUMN NEW_COLUMN NEW_TYPE; # 修改字段为新字段 alter table TABLE_NAME modify COLUMN INT NOT NULL DEFAULT 100; # 修改字段

2. 临时表 temporary table
   MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。

创建方法与普通表的创建基本类似，只是把 table 修改为 temporary table

1. 防止 SQL 注入
   所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

在程序设计中永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

1. 导出数据表
   导出到文件 into outfile
select * from TABLE_NAME into outfile 'FILE_PATH'
   导出作为原始数据到指定目录 mysqldump
mysqldump -u roop -p --no-create-info --tab=PATH runoob TABLE_NAME
   导出表为 SQL 格式数据到指定文件 mysqldump
mysqldump -u root -p runoob TABLE_NAME > FILE
   导出整个数据库到指定文件
   mysqldump -u root -p runoob > FILE
   备份所有数据
   mysqldump -u root -p --all-databases > FILE
   导入备份数据到 MySQL
   mysql -u root -p DATABASE_NAME < FILE
   使用管道直接将数据导入到远程的服务器上，
   mysqldump -u root -p DATABASE_NAME | mysql -h OTHER_HOST_ADDRESS DATABASE_NAME
2. 导入数据表
   mysql 命令导入
   mysql -u USER -p PASSWORD < file
   source 命令导入,需要先登录到数据库

   1 2 3 4

   create database DATABASE_NAME; use DATABASE_NAME; set names utf8; source FILE

3. 函数
   MySQL 有很多内置的函数，这里就不一一介绍了。

---

• ← Previous Post
• Next Post →

---