MySQL数据库管理
安装MySQL数据库
安装依赖包
yum -y install ncurses ncurses-devel bison cmake
- ncurses ncurses-devel:字符终端下屏幕控制的基本库
- bison :自动生成语法分析器程序
- cmake :跨平台的安装编译工具
[root@server3 ~]# useradd -s /sbin/nologin mysql #创建用户
[root@server3 ~]# tar zxvf mysql-boost-5.7.20.tar.gz #解压缩文件
[root@server3 ~]# cd mysql-5.7.20/
[root@server3 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_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
#编译安装
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_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
注意:如果在CMAKE的过程中有报错,当报错解决后,需要把源码目录中
的CMakeCache. txt文件删除,然后再重新CMAKE, 否则错误依旧---
[root@server3 mysql-5.7.20]#make && make install
#对数据库目录进行权限配置,属主,属组
[root@server3 mysql-5.7.20]# cd
[root@server3 ~]# chown -R mysql.mysql /usr/local/mysql/
[root@server3 ~]# vi /etc/my.cnf
[root@server3 ~]# chown mysql.mysql /etc/my.cnf
[client]
port = 3306
default-character-set = utf8
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306
default-character-set = utf8
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
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
#设置环境变量(重定向输入环境,赋值)
[root@server3 ~]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@server3 ~]# echo 'export PATH' >> /etc/profile
[root@server3 ~]# source /etc/profile
[root@server3 ~]# echo $PATH
#数据库初始化设置
[root@server3 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
[root@server3 ~]# cd /usr/local/mysql/
[root@server3 mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@server3 mysql]# systemctl daemon-reload
[root@server3 mysql]# systemctl start mysqld
[root@server3 mysql]# cd
[root@server3 ~]# netstat -anpt | grep 3306
数据库基础操作
查看数据库
[root@server3 ~]# mysql #进入数据库
mysql> show databases;
默认下列四个数据库
information_schema #这个数据库保存了Mysql服务器所有数据库的信息。如数据库名、数据库的表、表栏的数据类型访问权限等。
mysql # 这个库是系统库,里面保存有账户信息,权限信息等。
performance_schema #这是一个Mysql5.5开始新增的数据库,主要用于收集数据库服务器性能参数。此数据库中的存储引擎为performance_schema,但用户创建的数据库存储引擎不能为performance_schema。
sys # 这个是Mysql5.7新增的系统数据库,通过这个库可以快速了解系统的元数据信息。元数据是关于数据信息的数据,如数据库名、表名、列的数据类型、访问权限等。
查看数据库中的表信息
mysql> use mysql;
mysql> show tables;
显示数据表的结构(字段)
mysql> describe user;
SQL语句
- Structured Query Language的缩写,即结构化查询语言
- 关系型数据库的标准语言
- 用于维护管理数据库
- 包括数据查询,数据更新,访问控制,对象管理等功能
SQL分类
- DDL:数据定义语言
- DML:数据操纵语言
- DQL:数据查询语言
- DCL:数据控制语言
mysql数据库常用语句分类
- DDL:(Data Definition Language,数据定义语言):用来建立数据库,数据库对象和定义字段,如CREATE,ALTER,DROP.
- DML:(Data Manipulation Language,数据操纵语言):用来插入,删除和修改数据库中的数据,如INSERT,UPDATE,DELETE.
- DQL:(Data Query Language,数据查询语言):用来查询数据库中的数据,如SELECT.
- DCL:(Data Control Language,数据控制语言):用来控制数据库组件的存取许可,存取权限等,如COMOIT,ROLLBACK,GRANT,REVOKE.
DDL
创建新的数据库
mysql> create database qwe;
mysql> use qwe;
mysql> create table info (id char(16) not null, passwd char(48) default'', primary key(id));
mysql> describe users;
mysql> describe qwe.info;
格式
create table 表名(字段01名称 字段01类型 字段01约束,字段02名称 字段02类型 字段02约束,…)存储引擎,字符集
字段名称、类型、约束:
字段01名称:属性名称,自定义
字段01类型:int(4) 整型 代表0000-9999
double 浮点型:
decimal(5,2)有效数字是5位,小数点后面保留2位 100.00;099.50
float 单精度浮点 4字节
char 字符:
char (10)固定长度字符串,字符串要用单引号引起来
varchar(50)可变长度字符串
字段01约束:
非空约束:内容不允许为空 not null
主键约束:非空且唯一 标识 primary key(主键)
默认约束:假如没有填数据,默认预先设定的值填写 default ‘未知’
自增特性:id 1 2 3 4 auto_increment(自动增长)
存储引擎:myisam innodb
字符集:UTF-8
删除数据库和表
drop table users.info; #删除表
drop database users; #删除数据库
管理数据表中的数据
DML语句用于对表中的数据进行管理:
- insert:插入新数据
- update:更新原有数据
- delete:删除不需要的数据
注意:对数据的操作用DELETE,对库和表用drop
插入新数据
mysql> create database qwe;
Query OK, 1 row affected (0.00 sec)
mysql> use qwe;
Database changed
mysql> create table info (id char(64) not null primary key, name char(64) not null, passwd char(64) default'');
Query OK, 0 rows affected (0.01 sec)
mysql> describe qwe.info;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id | char(64) | NO | PRI | NULL | |
| name | char(64) | NO | | NULL | |
| passwd | char(64) | YES | | | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into info(id,name,passwd)values(1,'zhangsan','123123');
Query OK, 1 row affected (0.01 sec)
mysql> insert into info values(2,'lisi','456456');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+----------+--------+
| id | name | passwd |
+----+----------+--------+
| 1 | zhangsan | 123123 |
| 2 | lisi | 456456 |
+----+----------+--------+
2 rows in set (0.00 sec)
修改、更新数据表中的数据记录
mysql> update info set passwd=321321 where name='zhangsan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from info;
+----+----------+--------+
| id | name | passwd |
+----+----------+--------+
| 1 | zhangsan | 321321 |
| 2 | lisi | 456456 |
+----+----------+--------+
2 rows in set (0.00 sec)
删除指定的数据记录
mysql> delete from info where name='lisi';
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+----------+--------+
| id | name | passwd |
+----+----------+--------+
| 1 | zhangsan | 321321 |
+----+----------+--------+
1 row in set (0.00 sec)
数据表高级操作
清空表
mysql> delete from info;
mysql> truncate table info;
克隆表
like方法:
从info完整复制结构生成test表,再导入数据
mysql> create table test like info;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test select * from info;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> create table tmp as select * from test;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from tmp;
+----+----------+--------+
| id | name | passwd |
+----+----------+--------+
| 1 | zhangsan | 321321 |
+----+----------+--------+
1 row in set (0.00 sec)
数据库用户授权
DCL语句设置用户权限(用户不存在时,则新建用户)
设置登录密码为123123的lisi用户,可以从任意终端登录,对所有库和所有表有全部权限
mysql> grant all on *.* to 'lisi'@'%' identified by '123123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| lisi | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
设置登录密码为456456的asd用户,可以从本地终端登录,对mysql库中的user表有select权限
mysql> grant select on mysql.user to 'asd'@'localhost' identified by '456456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#grant:当用户已存在时,直接提权。当用户不存在时,先创建用户,再提权
查看用户权限
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for asd@localhost;
+-----------------------------------------------------+
| Grants for asd@localhost |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'asd'@'localhost' |
| GRANT SELECT ON "mysql"."user" TO 'asd'@'localhost' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select user from mysql.user;
+---------------+
| user |
+---------------+
| lisi |
| asd |
| mysql.session |
| mysql.sys |
| root |
+---------------+
5 rows in set (0.00 sec)
撤销用户权限
mysql> revoke select on mysql.user from 'asd'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'asd'@'localhost';
+-----------------------------------------+
| Grants for asd@localhost |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'asd'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)
# revoke:只撤销权限,不删除用户