MySQL数据库

1、数据库的概念

数据（Data）

• 描述事物的符号记录

• 包括数字，文字、图形、图像、声音、档案记录等

• 以”记录“形式按统一的格式进行存储

表

• 将不同的记录组织在一起

• 用来存储具体数据

数据库（DB）

• 表的集合，是存储数据的仓库

• 以一定的组织方式存储的相互有关的数据集合

• 是按照数据结构来组织、存储和管理数据的仓库‘

数据库管理系统（DBMS）

• 是实现对数据库资源有效组织、管理和存取的系统软件

• 数据库的建立和维护功能、数据定义功能、数据操纵功能、数据库的运行管理功能、通信功能

数据库系统

• 是一个人机系统。由硬件、OS、数据库、DBMS、应用软件和数据库用户组成

• 用户可以通过DBMS或应用程序操作数据库

 

2、关系数据库

• 关系数据库系统是基于关系模型的数据库系统

• 关系模型的数据结构使用简单易懂的二维数据表

• 关系模型可用简单的”实体-关系“（E-R）图来表示

• E-R图中包含了实体（数据对象）、关系和属性三个要素

3、MySQL安装方法

【安装 MySQL 服务】
1、安装Mysql环境依赖包
yum -y install \
ncurses \
ncurses-devel \
bison \
cmake
---------------------------------------------
2、创建运行用户
useradd -M -s /sbin/nologin  mysql
---------------------------------------------
3、编译安装
cd /opt
tar zxvf mysql-boost-5.7.20.tar.gz
---------------------------------------------
cd /opt/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 -j4 && make install
------------------------------------------
4、修改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
------------------------------------------
5、更改mysql安装目录和配置文件的属主属组
chown -R mysql:mysql /usr/local/mysql/
chown mysql:mysql /etc/my.cnf
-------------------------------------------
6、设置路径环境变量
echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile  
source /etc/profile
--------------------------------------------
7、初始化数据库
cd /usr/local/mysql/bin/
./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
--------------------------------------------
8、添加mysqld系统服务
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
systemctl daemon-reload
systemctl start mysqld.service
systemctl enable mysqld
---------------------------------------------
9、修改mysql 的登录密码
mysqladmin -u root -p password  回车设置密码
mysql -u root -p    登录数据库
---------------------------------------------

4、数据库基本操作

4.1、常见的数据类型

• int：整型

• float：单精度浮点、4字节32位

• double：双精度浮点、8字节64位

• char：固定长度的字符类型

• varchar：可变长度的字符类型

• text：文本

• image：图片

• decimal（5，2）：5个有效长度数字、小数点后面有2位

4.2、对数据库操作

4.2.1、进

-------------------进入mysql系统------------------------
[root@localhost ~]# mysql -u root -p 
Enter password: 
​
--------------------进入数据库----------------------------
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

4.2.2、增

----------------------创建新的数据库-----------------------------
mysql> create database kyc;

 4.2.3、删

--------------------删库------------------------------
mysql> drop database kgc;

4.2.4、查

------------------ 查看数据库-------------------------------
mysql> show databases;

5、SQL语句

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能

5.1、SQL分类

DDL：数据定义语言，用于创造数据库对象，如库、表、索引等

DML：数据操纵语言，用于对标中的数据进行管理

DQL：数据查询语言，用于从数据表中查找符号条件的数据记录

DCL：数据控制语言，用于设置或者更改数据库用户或角色权限

5.2、对表操作

5.2.1、创

----------------------创建新表-----------------------------
mysql> create table ky18 (id int,name char(10) not null,age int,sex char(4),score decimal(5,2));
Query OK, 0 rows affected (0.01 sec)
​
----------------------创建临时表（当前用户可见）-----------------------------
mysql> create temporary table ky50 (id int,name char(4) not null,age int,sex char(4));

 

5.2.2、增

---------------增加表结构（alter table xxx add xxx）---------------------
mysql> alter table ky18 add address varchar(50) not null default '地址不详';

 5.2.3、删

-------------------删表------------------------------
mysql> drop table ky18;
​
-----------------删除字段----------------------------
mysql> alter table ky18 drop address;

 5.2.4、改

-------------------修改表名------------------------------
mysql> alter table ky19 rename ky20;

 5.2.5、查

---------------------查看表结构-----------------------------
mysql> describe user;    查看user表
mysql> desc mysql.user;

5.3、对数据操作

5.3.1、增

----------------------增加数据---------------------------------
 insert into ky18(id,name,age,sex,score) values(1,'奚凯',25,'男',77);

 5.3.2、删

----删数数据--（但是delect工作是一行行删，遇见自增约束，只会从后记录添加）---------------
mysql> delete from ky18 where name='戴志浩';
​
--------格式化删数所有数据-truncate table xxx---（删完从第一条记录开始添加）------------
mysql> truncate table ky18;

5.3.3、改

-------------------修改指定数据------------------------------
mysql> update ky18 set age=24 where name='奚凯';
mysql> update ky18 set age=22,score=100  where name='沈春秀';

 

5.3.4、查

----------------------查看数据------------------------------
mysql> select * from ky18;        查看全部数据
​
mysql> select name,age,sex from ky18;    查看指定数据
​
--------------------------纵向查看数据-------------------
mysql> select * from ky18\G      
​
--------------------------查看指定数据(前N行)-------------------
mysql> select * from ky18 limit 2;
​
--------------------------查看指定数据（指定N行）-------------------
mysql> select * from ky18 limit 2,3;       2，3指   从第3行开始，查看三行内容
​
--------------------------查看指定条件数据（条件为女）-------------------
mysql> select * from ky18 where sex='女';
​
mysql> select * from ky18 where sex='女' or sex='男';    利用or或and

 

 

6、键设置

唯一键：unique key 主键 primary key

共同点：字段的值都是唯一的，不允许有重复的值 不同点：一个表中只能有一个主键，但是可以有多个唯一键

------------------------- 创建主键 ------------------------
create table 表名 (字段1 xxx,字段2 xxx,.....primary key(字段))
create table 表名 (字段1 xxx primary key,.......)
​
--------修改表结构，设置唯一键-alter table xxx change aaa bbb int unique key----------------
mysql> alter table ky18 change score phone int unique key;
​
---------------------------增加主键-------------------------
mysql> alter table ky18 add primary key(id);

 

7、mysq六大约束

• 主键约束(primary key)

• 外键约束(foreign key)

• 非空约束(not null)

• 唯一性约束(unique key|index)

• 默认值约束(default)

• 自增约束（auto_increment）

zerofill:若数值不满4位数，则前面用0填充
外键约束：如果同一属性字段x在表一中是主键，在表二中不是主键，则字段x称为表二的外键
自增约束：主键字段自增1，添加记录失败也会自增

-------------外键约束-------利用constrain设置别名----------------------
mysql> alter table class add constraint pk_cid primary key(cid);
mysql> alter table student add constraint fk_classid foreign key(classid) references class(cid);
​
备注：做了外键约束后
     插入数据时，要先主后从
     删除数据时，要先从后主

-------------------删除外键---------------------------
##设置别名，是因为一个表可以有多个外键
##删除外键要先删除外键约束再删除外键
##不设置外键别名，会自动生成
mysql> alter table student drop foreign key fk_classid;
mysql> alter table student drop  key "fk_classid";

 

8、高级操作

8.1、克隆

--------------------克隆表结构（不拷贝内容）-----------------------
mysql> create table ky19 like ky18;
Query OK, 0 rows affected (0.01 sec)
​
---------------------拷贝表内容------------------------------------------
mysql> insert into ky30 select * from ky18;
​
----------------------克隆表结构（同时拷贝内容）-------------------------------
mysql> create table ky20 (select * from ky18);
​
------------------以命令形式显示创建表过程-----------------------------------
mysql> show create table ky18\G;
*************************** 1. row ***************************
       Table: ky18
Create Table: CREATE TABLE "ky18" (
  "id" int(11) NOT NULL,
  "name" char(10) NOT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(4) DEFAULT NULL,
  "phone" int(11) DEFAULT NULL,
  PRIMARY KEY ("id"),
  UNIQUE KEY "phone" ("phone")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
​
ERROR: 
No query specified

9、数据用户管理

9.1、新建用户

create user '用户名'@'来源地址' [identified by password '密码'];

• 用户名：指定将创建的用户名

• 来源地址：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用 localhost,运行任意主机登录可用通配符%

• 密码：若使用明文密码，直接输入‘密码’，插入到数据库时由mysql自动加密 若使用加密密码，需要先使用select password('密码')；获取密文，再在语句中添加password‘密文’ 若省略‘identified by’部分，则用户的密码将为空（不建议使用）

---------------明文创建用户-----------------------
mysql> create user 'zhangsan'@'localhost' identified by 'abc123';
​
------------密文转换--------------------
mysql> select password('abc123');
+-------------------------------------------+
| password('abc123')                        |
+-------------------------------------------+
| *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+-------------------------------------------+
1 row in set, 1 warning (0.01 sec)
​
---------------密文创建用户-----------------------
mysql> create user 'zhangsan'@'localhost' identified by '*6691484EA6B50DDDE1926A220DA01FA9E575C18A ';

9.2、查看用户信息

--------------从users查看用户信息-----------------
mysql> select User,Host from user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| bbsuser       | %         |
| bbsuser       | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| zhangsan      | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

9.3、重命名用户信息

--------------rename xxxx  form xxxxx  to--------------------------
mysql> rename user 'zhangsan'@'localhost'  to 'qiangge'@'localhost';

 9.4、删除用户

mysql> drop user 'qiangge'@'localhost';
Query OK, 0 rows affected (0.00 sec)
​
mysql> select User,Host,authentication_string from user;  ##查看信息

9.5、修改当前登录用户密码

-------修改当前用户密码----------------------------
mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
​
---------修改别的用户密码----------------------------
mysql> set password for 'zhangsan'@'local'=password('abc123');

9.6、忘记root密码的解决办法

1、修改/etc/my.conf配置文件，不使用密码直接登录到mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables   #添加，使登录mysql不使用授权表
​
systemctl restart mysqld
​
mysql         ###直接登录
​
2、使用update 修改root密码，刷新数据库
update mysql.user set authentication_string =password('abc123')  where user='root';
​
flush privileges;
quit
​
mysql -u root -p abc123
注意：最后再把/etc/my.cnf配置文件里面的skip-grant-tables删除。并重启mysql服务

10、数据库授权

10.1、授予权限

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户，当指定的用户名存在时，GRANT语句用于修改用户信息。
​
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [identified by '密码']；

允许用户zhangsan在本地查询kgc数据库中所有表的数据记录，但禁止查询其他数据库中表的记录
mysql> grant select on kgc.* to 'zhangsan'@'localhost' identified by 'abc123';
​
允许用户zhangsan在所有终端远程连接mysql，并拥有所有权限
mysql> grant all on *.* to 'zhangsan'@'%' identified by 'abc123';

10.2、查看权限

mysql> show grants for 'zhangsan'@'localhost';
+---------------------------------------------------+
| Grants for zhangsan@localhost                     |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost'      |
| GRANT SELECT ON "kgc".* TO 'zhangsan'@'localhost' |
+---------------------------------------------------+
2 rows in set (0.00 sec)

10.3、撤销权限

mysql> revoke all/权限列表 on *.*/库名.表名 from 'zhangsan'@'localhost';