本文共 7211 字,大约阅读时间需要 24 分钟。
mysql 简介
MySQL是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。mysql 安装与配置mysql 安装方式有三种源代码:编译安装二进制格式的程序包:展开至特定路径,并经过简单配置后即可使用程序包管理器管理的程序包:rpm / deb// 配置mysql的yum源[root@yanyinglai3 ~]# cd /usr/src/[root@yanyinglai3 src]# lsdebug kernels[root@yanyinglai3 src]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm[root@yanyinglai3 src]# yum -y install mysql57-community-release-el7-10.noarch.rpm[root@yanyinglai3 src]# ls /etc/yum.repos.d/[root@yanyinglai3 ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-develmysql 配置// 启动mysql[root@yanyinglai3 ~]# systemctl start mysqld[root@yanyinglai3 ~]# systemctl status mysqld● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since 五 2018-08-17 16:35:41 CST; 14s ago Docs: man:mysqld(8)// 确保3306端口已经监听起来[root@yanyinglai3 ~]# ss -antlState Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 128 :::22 :::* LISTEN 0 100 ::1:25 :::* LISTEN 0 80 :::3306 :::* //在日志文件中找出临时密码 [root@yanyinglai3 ~]# grep "password" /var/log/mysqld.log2018-08-17T08:35:30.972217Z 1 [Note] A temporary password is generated for root@localhost: CYCIoXn;V1)o //此处的临时密码为CYCIoXn;V1)o //使用获取到的临时密码登录mysql [root@yanyinglai3 ~]# mysql -uroot -pEnter password: // 此处输入密码,可以直接复制粘贴。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 'wangqing123!';Query OK, 0 rows affected (0.00 sec)mysql> quitBye为避免mysql自动升级,这里需要卸载最开始安装的yum源 [root@yanyinglai3 ~]# rpm -qa|grep mysqlmysql-community-common-5.7.23-1.el7.x86_64mysql-community-libs-compat-5.7.23-1.el7.x86_64mysql-community-libs-5.7.23-1.el7.x86_64mysql-community-server-5.7.23-1.el7.x86_64mysql-community-devel-5.7.23-1.el7.x86_64mysql57-community-release-el7-10.noarchmysql-community-client-5.7.23-1.el7.x86_64[root@yanyinglai3 ~]# yum -y remove mysql57-community-release-el7-10.noarch
mysql 的程序组成
mysql:CLI 交互式客户端程序mysql_secure_installation: 安全初始化,强烈建议安装完以后执行此命令mysqldump:mysql 备份工具服务器mysqldmysql 工具使用// 语法:mysql [ OPTINS] [database]常用的OPTIONS-uUSERNAME // 指定用户名,默认为root-hHOST // 指定服务器主机,默认为localhost, 推荐使用ip地址-pPAS1SWORD // 指定用户的密码-P# //指定数据库监听的端口-V // 查看当前使用的mysql版本-e // 不登录mysql执行sql语句后退出,常用于脚本[root@yanyinglai3 ~]# mysql -uroot -pwangqing123! -h127.0.0.1
// 注意,不推荐直接在命令行里直接用。
[root@yanyinglai3 ~]# mysql -uroot -p -h127.0.0.1Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.服务器监听的两种socket地址socket 类型ip socket 默认监听在tcp的3306端口,支持远程通信Unix sock 监听在sock文件上(/tmp/mysql.sock, /var/lib/mysql/mysql.sock)server 地址只能是:localhost , 127.0.0.1DDL 操作
数据库操作
创建数据库wangqinggemysql> CREATE DATABASE IF NOT EXISTS wangqingge;Query OK, 1 row affected (0.00 sec)//查看当前实例有哪些数据库mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || wangqingge |+--------------------+5 rows in set (0.07 sec)//删除数据库wangqingge
mysql> DROP DATABASE IF EXISTS wangqingge;Query OK, 0 rows affected (0.08 sec)mysql> SHOW DATABASES;
+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.01 sec)表操作
在数据库wangqingge里创建表wangqingmysql> CREATE DATABASE wangqingge; //创建数据库wangqinggeQuery OK, 1 row affected (0.00 sec)mysql> use wangqingge; //进入wangqingge数据库
Database changedmysql> CREATE TABLE wangqing (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint); //创建wangqing表
Query OK, 0 rows affected (0.07 sec)查看当前数据库有哪些表
mysql> SHOW TABLES;+----------------------+| Tables_in_wangqingge |+----------------------+| wangqing |+----------------------+1 row in set (0.00 sec)删除表wangqing
mysql> DROP TABLE wangqing;Query OK, 0 rows affected (0.01 sec)查看某表的状态
//语法:SHOW TABLE STATUS LIKE 'table_name'\GDML操作
DML 操作包括增(INSERT),删(DELETE)改(UPDATE)查(SELECT),均属针对表的操作
作业练习
创建一个以你名字为名的数据库,并创建一张表student,该表包含三个字段[root@yanyinglai3 ~]# mysql -uroot -p -h127.0.0.1mysql> CREATE DATABASE yanyinglai;Query OK, 1 row affected (0.00 sec) 创建数据库mysql> SHOW databases; 查看当前数据库+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || yanyinglai |+--------------------+5 rows in set (0.00 sec)mysql> use yanyinglai; 进入yabyinglai数据库Database changedmysql> CREATE TABLE student (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);Query OK, 0 rows affected (0.02 sec) 创建表mysql> SHOW TABLES;+----------------------+| Tables_in_yanyinglai |+----------------------+| student |+----------------------+1 row in set (0.00 sec) 查看表mysql> DESC yanyinglai.student;+-------+--------------+------+-----+---------+-------+| 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.06 sec)查看下该新建的表有无内容(用select语句)查看表信息mysql> select * from student;往新建的student表中插入数据(用insert语句),结果应如下id | name | age |+----+-------------+------+| 1 | tom | 20 || 2 | jerry | 23 || 3 | wangqing | 25|| 4 | sean | 28 || 5 | zhangshan | 26 || 6 | zhangshan | 20 || 7 | lisi | NULL ||8 | chenshuo | 10 || 9 | wangwu | 3 || 10 | qiuyi | 15 || 11 | qiuxiaotian | 20 |向表中插入数据mysql> insert into student (id,name,age) values (1,'tom',20),(2,'jerry',23),(3,'hejiee',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL),(8,'ran',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'wangbadan',20);修改lisi的年龄为50mysql> update student set age=50 where name = 'lisi';以sge字段降序排序mysql> select * from student order by age desc;查询student表中年龄最小的3位同学mysql> select * from student order by age limit 3;查询student表中年龄最大的4位同学mysql> select * from student order by age desc limit 4;查询student表中名字叫zhangshan的记录mysql> select * from student where name='zhangshan';查询student表中名字叫zhangsan且年龄大于20的记录mysql> select * from student where name='zhangshan' and age>20;查询student表中年龄在23到30之间的记录mysql> select * from student where age between 23 and 30;修改wangwu的年龄为100mysql> update student set age=100 where name='wangwu';删除student中名字叫zhangsan且年龄小于等于20的记录mysql> delete from student where name='zhangshan' and age<=20;
转载于:https://blog.51cto.com/13910274/2161656