MySQL基础（二）---MySQL服务基础与使用 MySQL 数据库

---

前言

---

一、MySQL 服务基础

1.简介

• MySQL 是一个真正的多线程、多用户的 SQL 数据库服务，凭借其高性能、高可靠和易于使用的特点，成为服务器领域中最受欢迎的开源数据库系统
• 在 2008 年以前，MySQL 项目由 MySQL AB 公司进行开发、发布和支持，之后历经 Sun 公司收购 MySQL，Oracle 公司收购 Sun 公司的过程，目前 MySQL 项目由 Oracle 公司负责运营和维护

2.商业版与社区版

• MySQL 商业版是由 MySQL AB 公司负责开发与维护，需要付费才能使用
• MySQL 社区版是由分散在世界各地的 MySQL 开发者、爱好者一起开发与维护，可以免费使用
• 两者区别
  商业版组织管理与测试环节更加严格，会比社区版更稳定
  商业版不遵守 GPL，社区版遵从 GPL 可以免费使用
• 商业版可获得 7*24 小时的服务，社区版则没有

3.产品阵营

• 第一阵营：5.0-5.1 阵营，可说是早期产品的延续
• 第二阵营：5.4-5.7 阵营，整合了 MySQL AB 公司、社区和第三方公司开发的存储引擎，从而提高性能
• 第三阵营：6.0-7.1 阵营，就是 MySQL Cluster 版本，为适应新时代对数据库的集群需求而开发

4.编译安装

安装包链接
提取码：ntkq

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake
#安装 MySQL 环境依赖包

useradd -M -s /sbin/nologin mysql
#创建运行用户

cd /opt
#使用Xshell，将加载好的软件包拖至此目录下
mysql-boost-5.7.20.tar.gz
tar zxvf mysql-boost-5.7.20.tar.gz

cd mysql-5.7.20/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1
#配置软件模块

make -j 4 && make install
#编译安装，这里所用时间会较长

#修改 mysql 配置文件
vim /etc/my.cnf
#删除原配置项，再重新添加下面内容

[client]
port = 3306
socket=/usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character-set-server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket=/usr/local/mysql/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
max_connections=2048
default-storage-engine=INNODB
max_allowed_packet=16M
server-id = 1
   
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES


#更改mysql安装目录和配置文件的属主属组
chown -R mysql.mysql /usr/local/mysql/
chown mysql.mysql /etc/my.cnf

#设置路径环境变量
echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
source /etc/profile

#初始化数据库
cd /usr/local/mysql/bin/
./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

#添加mysql系统服务
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/

systemctl daemon-reload
systemctl start mysqld.service
systemctl enable mysqld

netstat -anpt | grep 3306

#修改mysql登录密码
mysqladmin -u root -p password "123123" 	

#授权远程登录
mysql -u root -p

grant all privileges on *.* to 'root'@'%' identified by '123123';

show databases;

quit

ln -s /usr/local/mysql/bin/mysql /usr/local/sbin

• 登陆mysql

mysql -u用户 -p密码

二、使用MySQL

1.查看数据库信息

SHOW DATABASES

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |               
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

#大部分SQL操作命令必须以 ；结尾

2.查看数据库中的表信息

USE 数据库名       //切换数据库
SHOW TABLES

mysql> use mysql
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
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
......

3.显示数据表的结构（字段）

DESCRIBE [数据库名] 表名

mysql> describe user;			//也可以使用 desc user
+------------------------+-----------------------------------+------+-----+---------------------
--+-------+| Field                  | Type                              | Null | Key | Default             
  | Extra |+------------------------+-----------------------------------+------+-----+---------------------
--+-------+| Host                   | char(60)                          | NO   | PRI |                     
  |       || User                   | char(32)                          | NO   | PRI |                     
  |       || Select_priv            | enum('N','Y')                     | NO   |     | N                   
  |       || Insert_priv            | enum('N','Y')                     | NO   |     | N    



Field:字段名称
type:数据类型
Null :是否允许为空
Key :主键
Type:数据类型
Null :是否允许为空key :主键
Default :默认值
Extra :扩展属性，例如:标志符列（标识了种子，增量/步长）1 2
id：1 3 5 7

4.常用的数据类型

int：整型						用于定义整数类型的数据
float：单精度浮点4字节32位		准确表示到小数点后六位		
double：双精度浮点8字节64位
char：固定长度的字符类型		用于定义字符类型数据。
varchar：可变长度的字符类型		
text：文本
image：图片
decimal(5,2)：5个有效长度数字，小数点后面有2位		指定长度数组
#Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，
#如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错

三、SQL语句概述

1.SQL语言

• Structured Query Language的缩写，即结构化查询语言
• 关系型数据库的标准语言
• 用于维护管理数据库
  包括数据查询、数据更新、访问控制、对象管理等功能

2.SQL分类

• DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
• DML：数据操纵语言，用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据
• DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
• DCL：数据控制语言，用于设置或者更改数据库用户或角色权限（数据控制语句，用于控制不通数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别,如COMMIT、ROLLBACK、GRANT、REVOKE）

四、创建及删除库和表

• DDL语句可用于创建数据库对象，如库、表、索引等

1.使用DDL语句新建库、表

####创建数据库####
CREATE DATABASE 数据库名

#新创建后的数据库是空的，其中不包含任何表
#在 /usr/local/mysql/data 目录下会自动生成一个与新建的库名相同的文件夹

####创建数据表####
CREATE TABLE 表名(字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键

####例：
CREATE DATABASE test;		//创建test数据库
Query OK, 1 row affected (0.00 sec)

USE test;					//切换到test数据库
Database changed

mysql> CREATE TABLE test1 (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT'', PRIMARY KEY (id));
Query OK, 0 rows affected (0.04 sec)
//创建test1 数据表 	
		
mysql> desc test1;		//显示数据表的结构
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | NULL    |       |
| name   | char(10)     | NO   |     | NULL    |       |
| score  | decimal(5,2) | YES  |     | NULL    |       |
| passwd | char(48)     | YES  |     |         |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#id int NOT NULL：id 不能为空值
#name char(10) NOT NULL：name 字符串长度为10字节，且不为空值
#score decimal(5,2)：“2”表示小数部分的位数，5”指的是整数部分加小数部分的总长度
#passwd char(48) DEFAULT''：定义密码48个字节，默认为空	
#PRIMARY KEY (id)：指定主键为id

2.使用DDL语句删除库、表

####删除指定的数据表####
DROP TABLE 表名
#例：
mysql> drop table test1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables 			//查看表

####如不用USE进入库中，则需加上数据库名####
DROP TABLE [数据库名.] 表名;
#例：
mysql> drop tables test.test1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables

####删除指定的数据库####
DROP DATABASE 数据库名;
#例：
mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;		//查看库

五、管理表中的数据记录

1.向数据表中插入新的数据记录

#格式：
INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值,...);

#例：
mysql> create database zone;		//先创建一个库 zone
mysql> use zone						//切换到zone
create table dsj (id int not null,name char(10) not null,sex char(1),primary key (id)); 
Query OK, 0 rows affected (0.04 sec)
//创建 dsj 表

mysql> INSERT INTO dsj (id,name,score,passwd) values(1,'zhangsan',70.5,PASSWORD('123456'));
Query OK, 1 row affected, 1 warning (0.00 sec
//向dsj中插入新的数据记录

2.查询数据记录

#格式：
SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];

例:
#查询 表dsj 中数据记录
mysql> select * from dsj;	
+----+----------+-------+-------------------------------------------+
| id | name     | score | passwd                                    |
+----+----------+-------+-------------------------------------------+
|  1 | zhangsan | 70.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+----------+-------+-------------------------------------------+
1 row in set (0.00 sec)

#查询dsj中 id=1 的数据		
mysql> select name from dsj where id=1;	
+----------+
| name     |
+----------+
| zhangsan |
+----------+
1 row in set (0.01 sec)

select * from dsj limit 2;     #只显示头2行
select * from dsj limit 2,3;   #显示第2行后的前3行

3.修改、更新数据表中的数据记录

#格式：
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

例：
insert into dsj (id,name) values(2,'lisi');		//插入新的数据
insert into dsj (id,name) values(3,'zhangsan');
select * from dsj;		//查询dsj中所有数据

UPDATE dsj SET passwd=PASSWORD('') WHERE name='zhangsan';	//更新，修改数据
UPDATE dsj SET name='lisi',passwd='123' WHERE id=2;
mysql> select *from dsj;
+----+----------+-------+--------+
| id | name     | score | passwd |
+----+----------+-------+--------+
|  1 | zhangsan | 70.50 |        |
|  2 | lisi     |  NULL | 123    |
+----+----------+-------+--------+
2 rows in set (0.00 sec)

4.在数据表中删除指定的数据记录

#格式
DELETE FROM 表名 [WHERE 条件表达式];

例：
mysql> delete from dsj where id=1;
mysql> select * from dsj;
+----+------+-------+--------+
| id | name | score | passwd |
+----+------+-------+--------+
|  2 | lisi |  NULL | 123    |
+----+------+-------+--------+
1 row in set (0.00 sec)

5.修改表名和表结构

1. 修改表名

#格式
ALTER TABLE 旧表名 RENAME 新表名;

例：
alter table dsj rename dlh;		//修改表名dsj为dlh
show tables;
select * from dlh;

2. 扩展表结构(增加字段)

#格式：
ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default ‘地址不详’：表示此字段设置默认值 地址不详；可与 NOT NULL 配合使用

例：
alter table dlh add address varchar(50) default '地址不详';

3. 修改字段(列)名，添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];

#unique key:唯一键（特性:唯一，但可以为空，空值只允许出现一次)
#Primary key:唯一且非空(主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。)

例：
alter table dlh change name student_name varchar(20) unique key;
select * from dlh;

4. 删除字段

ALTER TABLE 表名 DROP 字段名;

例：
alter table dlh drop address;

6.案例拓展

例：
use zone;
create table if not exists info (
id int(4) zerofill primary key auto_increment,     #指定主键的第二种方式
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));

-----------------------------------------------------------------------------------------------
#if not exists: 表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill: 表示若数值不满4位数，则前面用“0"填充，例0001
#auto_increment: 表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key: 表示此字段唯一键约束，此字段数据不可以重复;一张表中只能有一个主键，但是一张表中可以有多个唯一键
#not null:表示此字段不允许为NULL

---