MySQL数据库介绍
最为著名、应用最为广泛的开源数据库软件
-最早隶属于瑞典的MySQL AB公司
-2008年1月,MySQL AB被SUN收购
-2009年4月,SUN被Oracle收购
崭新的开源分支MariaDB
-为应付MySQL可能会闭源的风险而诞生
- 由MySQL原作者Widenius主导开发
- 与MySQL保持最大程度兼容
MySQL的特点及应用
主要特点
-适用于中小规模、关系型数据库系统
-支持Linux/Unix、Windows等多种操作系统
-使用C和C++编写,可移植性强
-通过API支持Python/JAVA/Perl/PHP等语言
典型应用环境
-LAMP平台,与Apache HTTP Server组合
-LNMP平台,与Nginx组合
Mysql安装
准备工作
-停止mariadb服务
-删除文件 /etc/my.cnf
-删除数据
-卸载软件包
[root@proxy ~]# systemctl stop mariadb [root@proxy ~]# rm -rf /etc/my.cnf [root@proxy ~]# rm -rf /var/lib/mysql/* [root@proxy ~]# rpm -e --nodeps mariadb-server mariadb 警告:/var/log/mariadb/mariadb.log 已另存为 /var/log/mariadb/mariadb.log.rpmsave
至少安装server、client、share*包
-采用-U升级安装,可替换冲突文件
- 推荐将devel安装,用于支持其他软件
[root@proxy ~]# yum -y install perl-Data-Dumper perl-JSON perl-Time-HiRes [root@proxy ~]# tar -xf mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar [root@proxy ~]# rm -f mysql-community-server-minimal-5.7.17-1.el7.x86_64.rpm [root@proxy ~]# rpm -Uvh mysql-community-*.rpm
启动MySQL数据库服务
-服务脚本为/usr/lib/systemd/system/mysqld.service
[root@localhost ~]# systemctl start mysqld [root@localhost ~]# systemctl enable mysqld [root@localhost ~]# systemctl status mysqld
MySQL初始配置
默认的数据库管理账号
-root,允许从localhost访问
-首次登录密码在安装时随机生成
-存储在错误日志文件中
[root@proxy ~]# grep 'temporary password' /var/log/mysqld.log 2019-06-24T15:19:18.303935Z 1 [Note] A temporary password is generated for root@localhost: zzXdihIzU4-_ [root@proxy ~]# mysql -uroot -p'zzXdihIzU4-_'
mysql> set global validate_password_policy=0; //只验证长度 Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_length=6; //修改密码长度,默认值是8个字符 Query OK, 0 rows affected (0.00 sec) mysql> alter user user() identified by "123456"; //修改登录密码 Query OK, 0 rows affected (0.00 sec)
使用客户端命令连接服务器
[root@proxy ~]# 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 3Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
MySQL服务相关参数
文件 | 说明 |
/etc/my.cnf | 主配置文件 |
/var/lib/mysql | 数据库目录 |
默认端口号 | 3306 |
进程号 | mysqld |
传输协议 | TCP |
进程所有者 | mysql |
进程所属组 | mysql |
数据库基本管理
常用SQL操作指令
-DDL数据定义语言(create,alter,drop)
-DML数据定义语言(insert,update,delete)
-DCL数据定义语言(grant,revoke)
-DTL数据定义语言(commit,rollback,savepoint)
库管理命令
-show databases; //显示已有的库
-Use 库名; //切换库
-Select database(); //显示当前所在的库
-Create database 库名; //创建新库
-Show tables; //显示已有的库
-Drop database 库名; //删除库
表管理命令
-Desc 表名; //查看表结构
-Select * from 表名; // 查看表记录
-Drop table 表名; //删除表
记录管理命令
-Select * from 表名; //查看表记录
-Insert into 表名 values(值列表); //插入表记录
-Update 表名 set 字段=值; //修改表记录
-Delete from 表名; //删除表记录
修改表结构
-添加新字段
ALTER TABLE 表名
ADD 字段名 类型(宽度) 约束条件;
可加 AFTER 字段名;
或者FIRST;
mysql> desc tt1; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | varchar(5) | NO | | NULL | | | age | int(3) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> alter table tt1 add interest varchar(40); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tt1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | varchar(5) | NO | | NULL | | | age | int(3) | NO | | NULL | | | interest | varchar(40) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
-修改字段类型
alter table 表名
modify 字段名 类型(宽度)约束条件;
可加 after 字段名;
或者 first ;
mysql> desc tt1; +----------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | varchar(5) | NO | | NULL | | | age | int(3) | NO | | NULL | | | gender | enum('boy','girl') | NO | | NULL | | | interest | varchar(40) | YES | | NULL | | +----------+--------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table tt1 modify name char(6) not null; Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tt1; +----------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | char(6) | NO | | NULL | | | age | int(3) | NO | | NULL | | | gender | enum('boy','girl') | NO | | NULL | | | interest | varchar(40) | YES | | NULL | | +----------+--------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
-修改字段名
alter table 表名
change 源字段名 新字段名 类型(宽度) 约束条件;
mysql> desc tt1; +----------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | varchar(5) | NO | | NULL | | | age | int(3) | NO | | NULL | | | sex | enum('boy','girl') | YES | | NULL | | | interest | varchar(40) | YES | | NULL | | +----------+--------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table tt1 change sex gender enum('boy','girl') not null; Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tt1; +----------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | varchar(5) | NO | | NULL | | | age | int(3) | NO | | NULL | | | gender | enum('boy','girl') | NO | | NULL | | | interest | varchar(40) | YES | | NULL | | +----------+--------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
-删除字段
alter table 表名
drop 字段名;
mysql> desc tt1; +----------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | char(6) | NO | | NULL | | | age | int(3) | NO | | NULL | | | gender | enum('boy','girl') | NO | | NULL | | | interest | varchar(40) | YES | | NULL | | +----------+--------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table tt1 drop gender; Query OK, 0 rows affected (0.35 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tt1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | char(6) | NO | | NULL | | | age | int(3) | NO | | NULL | | | interest | varchar(40) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
-修改表名
alter table 表名
rename 新表名;
mysql> alter table tt1 rename tt2; Query OK, 0 rows affected (0.31 sec) mysql> desc tt1; ERROR 1146 (42S02): Table 'studb.tt1' doesn't exist mysql> desc tt2; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | char(6) | NO | | NULL | | | age | int(3) | NO | | NULL | | | interest | varchar(40) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
时间函数
类型 | 用途 |
now() | 获取系统当前日期和时间 |
year() | 执行时动态获得系统日期时间 |
sleep() | 休眠N秒 |
curdate() | 获取当前的系统日期 |
curtime() | 获取当前的系统时刻 |
month() | 获取指定时间中的月份 |
date() | 获取指定时间中的日期 |
time() | 获取指定时间中的时刻 |
无需库、表,可直接调用
-使用SELECT指令输出函数结果
mysql> select now(),sysdate(),curdate(); +---------------------+---------------------+------------+ | now() | sysdate() | curdate() | +---------------------+---------------------+------------+ | 2019-06-25 22:10:45 | 2019-06-25 22:10:45 | 2019-06-25 | +---------------------+---------------------+------------+ 1 row in set (0.00 sec)
mysql> select date(now()),time(now()); +-------------+-------------+ | date(now()) | time(now()) | +-------------+-------------+ | 2019-06-25 | 22:11:41 | +-------------+-------------+ 1 row in set (0.00 sec)
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。