MySQL数据库基础知识
一、数据库的基本概念
- 数据:描述事物的符号记录,以记录形式按统一格式进行存储数据(数字,文字,图形图像,声音,档案记录等)
- 表:由不同的记录和字段组成,用来存储具体的数据(数据库中记录为行,字段为列)
- 数据库:表的集合组成数据库,以一定的组织方式存储的相互有关的数据集合
- 数据库管理系统(DBMS):是实现对数据库资源的有效组织、管理和存取的系统软件
- 数据库系统:是一个人机系统,有硬件、OS、数据库、DBMS、应用软件和数据库用户组成,用户可以通过DBMS或应用程序操作数据库
- 数据库的特点:
(1)可以结构化存储大量数据信息,方便用户进行有效的检索和访问
(2)可以有效地保持数据信息的一致性、完整性,降低数据冗余
(3)可以满足应用的共享和安全方面的要求 - 数据库发展:20世纪60年代IBM公司研制大的第一代数据库——20世纪70年代第二代数据库关系型数据库,如:DB2,MySQL,——20世纪80年代三代数据库“关系-对象”数据库,如:Oracle
主流数据库
- SQL Server(微软公司产品):面向Windows操作系统,简单、易用
- Oracle(甲骨文公司产品):面向所有主流平台,安全、完善、操作复杂
- DB2(IBM公司产品):面向所有主流平台,大型、安全、完善
- MySQL(甲骨文公司收购):免费、开源、体积小
关系数据库
关系型数据库是基于关系模型的数据库系统,关系模型的数据结构使用简单易懂的二维数据表,其中主要包含了实体、关系、属性三要数
实体:对应现实世界中的“事件”或“事物”
关系;实体之间的对应联系
属性:描述一个实体的详细信息
非关系型数据库
- 非关系型数据库也称为nosql,它存储数据不以关系模型为依据,不需要固定的表格式,常用的有:Redis,mongoDB等
- 优点:
(1)数据库可高并发读写
(2)对海量数据高效率存储与访问
(3)数据库具有扩展性和高可用性
二、MySQL数据库
MySQL是一种开放源码的关系型数据库管理系统,属于Oracle旗下产品
MySQL编译安装
[root@mysql opt]# tar zxf mysql-boost-5.7.20.tar.gz
[root@mysql opt]# yum install -y install ncurses ncurses-devel bison cmake
[root@mysql opt]# cd mysql-5.7.20/
[root@mysql 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
[root@mysql mysql-5.7.20]# useradd -s /sbin/nologin mysql
[root@mysql mysql-5.7.20]# make && make install
[root@mysql ~]# chown -R mysql:mysql /usr/local/mysql/
[root@mysql ~]# vim /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] #mysql服务配置
user = mysql
basedir = /usr/local/mysql #工作目录
datadir = /usr/local/mysql/data #数据文件目录
port = 3306
character_set_server=utf8 #对应中文字符集
pid-file = /usr/local/mysql/mysqld.pid #PID文件位置
socket = /usr/local/mysql/mysql.sock #通讯文件
server-id = 1 #服务id,在之后的mysql集群中用于标识mysql服务器
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@mysql ~]# chown mysql:mysql /etc/my.cnf
[root@mysql ~]# echo "PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH" >> /etc/profile
[root@mysql ~]# echo "export PATH" >> /etc/profile
[root@mysql ~]# source /etc/profile
[root@mysql ~]# cd /usr/local/mysql
[root@mysql mysql]# bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data #初始化数据库
[root@mysql mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@mysql mysql]# systemctl start mysqld.service
[root@mysql mysql]# netstat -antp | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 106734/mysqld
[root@mysql mysql]# mysqladmin -u root -p password #创建root用户数据库密码
Enter password: #第一次没有密码直接回车
New password: #输入新的密码
Confirm new password: #再次输入新密码
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
MySQL数据库基本操作
[root@mysql mysql]# mysqladmin -u root -p password
Enter password: 输入密码
mysql> show databases; #查看当前MySQL中包含额库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql; #进入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 |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> describe user; #查看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:主键
- Default:默认值
- Extra:拓展属性,如:标志弗列(表示了种子,增量/步长)
常用的数据类型Type:
- int :整型, 用于定义整数类型的数据
- float :单精度浮点4字节32位 用于表示小数点后六位
- double :双精度浮点8字节64位
- char :固定长度的字符类型 用于定义字符类型数据, 如手机号码11位,char(11)
- varchar :可变长度的字符类型
- text :文本
- image :图片
- decimal(5,2) :5个有效长度数字,小数点后面2位
SQL是目前数据库的主要语法,它主要由一下几类
- DDL:数据定义语言,用于创建数据库对象,如库、表、索引等
- DML:数据操纵语言,用于对表中的数据进行管理
- DQL:数据查询语言,用于从数据表中查找符合条件的数据记录
- DCL:数据控制语言,用于设置或者更改数据库用户或角色权限
mysql> create database whd ; #新建数据库
Query OK, 1 row affected (0.01 sec)
mysql> show databases; #查看库是否创建成功过
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| whd |
+--------------------+
5 rows in set (0.00 sec)
mysql> use whd;
mysql> show tables; #新建的库中没有表
Empty set (0.00 sec)
mysql> create table whd1 (id int not null,name varchar(20) not null,score decimal(5,2) not null,age int(4),address varchar(50) default 'nanjing',primary key(id));
#### 添加表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_whd |
+---------------+
| whd1 |
+---------------+
1 row in set (0.00 sec)
mysql> desc whd1; #查看新建表
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| score | decimal(5,2) | NO | | NULL | |
| age | int(4) | YES | | NULL | |
| address | varchar(50) | YES | | nanjing | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> drop table whd1; #删除表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; #查看结果
Empty set (0.00 sec)
新建表并插入数据
mysql> create table whd1 (id int not null,name varchar(20) not null,score decimal(5,2) not null,age int(4),address varchar(50) default 'nanjing',primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> select * from whd1; #查看表
Empty set (0.00 sec)
mysql> insert into whd1 (id,name,score,age,address) values(1,'lisi',87.222,20,'shanghai');
####在表中插入数据,注意插入中主键有唯一性,不可设置重复冲突
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from whd1;
+----+------+-------+------+----------+
| id | name | score | age | address |
+----+------+-------+------+----------+
| 1 | lisi | 87.22 | 20 | shanghai |
+----+------+-------+------+----------+
1 row in set (0.00 sec)
修改数据记录
UPDATE 表明 set 字段名=新字段值 【,字段名2=新字段值】 where 条件表达式
mysql> create table whd1 (id int not null,name varchar(10) not null,score int(4),address varchar(40),primary key(id));
#### 新建表
mysql> desc whd1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| score | int(4) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into whd1 values(1,'lisi',40,'上海'),(2,'wangwu',50,'杭州'); #可同时插入两条记录
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from whd1;
+----+--------+-------+---------+
| id | name | score | address |
+----+--------+-------+---------+
| 1 | lisi | 40 | 上海 |
| 2 | wangwu | 50 | 杭州 |
+----+--------+-------+---------+
2 rows in set (0.00 sec)
mysql> update whd1 set score=99 where id=1; #修改lisi的成绩为99
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from whd1; #查看验证结果
+----+--------+-------+---------+
| id | name | score | address |
+----+--------+-------+---------+
| 1 | lisi | 99 | 上海 |
| 2 | wangwu | 50 | 杭州 |
+----+--------+-------+---------+
2 rows in set (0.00 sec)
删除数据记录
DELETE FROM 表名 WHERE 条件表达式
truncate table 表名; #清除表
mysql> delete from whd1 where id=2; #删除whd1表中id为2的记录
Query OK, 1 row affected (0.00 sec)
mysql> select * from whd1;
+----+------+-------+---------+
| id | name | score | address |
+----+------+-------+---------+
| 1 | lisi | 99 | 上海 |
+----+------+-------+---------+
1 row in set (0.00 sec)
查看表名
SELECT * FROM 表名;
SELECT 字段名 from 表名; #查看单独字段
SELECT 字段名,字段名 from 表名; #查看多个字段
SELECT name from 表名\G; #以列表方式竖向显示
SELECT * from 表名 info limit 2; #显示前三行,字段名的那一行为第一行
SELECT * from 表名 info limit 2,3; #显示从第二行往下的第三行
show create table test02\G #查看数据表的表机构、索引等消息
mysql> insert into whd1 values (2,'zhansan',20,'杭州'),(3,'wangwu',20,'杭州'),(4,'xuyi',40,'南京'),(5,'whd',50,'湖北');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from whd1;
+----+---------+-------+---------+
| id | name | score | address |
+----+---------+-------+---------+
| 1 | lisi | 99 | 上海 |
| 2 | zhansan | 20 | 杭州 |
| 3 | wangwu | 20 | 杭州 |
| 4 | xuyi | 40 | 南京 |
| 5 | whd | 50 | 湖北 |
+----+---------+-------+---------+
5 rows in set (0.00 sec)
mysql> select * from whd1 info limit 1,2;
+----+---------+-------+---------+
| id | name | score | address |
+----+---------+-------+---------+
| 2 | zhansan | 20 | 杭州 |
| 3 | wangwu | 20 | 杭州 |
+----+---------+-------+---------+
2 rows in set (0.00 sec)
修改表名和表结构
ALTER TABLE 旧表名 RENAME 新表名; #修改表名
ALTER TABLE 表名 ADD 字段名 数据类型; #新增字段
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型 unique key; #修改字段名、数据类型、约束等
ALTER TABLE 表名 DROP 字段名 ; #删除字段
mysql> show tables;
+---------------+
| Tables_in_whd |
+---------------+
| whd1 |
| |
+---------------+
2 rows in set (0.00 sec)
mysql> alter table whd1 rename whd2; #修改表名
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_whd |
+---------------+
| whd2 |
| |
+---------------+
2 rows in set (0.00 sec)
mysql> alter table whd6 add age int(2); #新增字段
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from whd2;
+----+---------+-------+---------+------+
| id | name | score | address | age |
+----+---------+-------+---------+------+
| 1 | lisi | 99 | 上海 | NULL |
| 2 | zhansan | 50 | 杭州 | NULL |
| 3 | wangwu | 20 | 杭州 | NULL |
| 4 | xuyi | 40 | 南京 | NULL |
| 5 | whd | 50 | 湖北 | NULL |
+----+---------+-------+---------+------+
5 rows in set (0.00 sec)
mysql> alter table whd2 change score score_new varchar(3); #修改字段名
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from whd2;
+----+---------+-----------+---------+------+
| id | name | score_new | address | age |
+----+---------+-----------+---------+------+
| 1 | lisi | 99 | 上海 | 20 |
| 2 | zhansan | 50 | 杭州 | NULL |
| 3 | wangwu | 20 | 杭州 | NULL |
| 4 | xuyi | 40 | 南京 | NULL |
| 5 | whd | 50 | 湖北 | NULL |
+----+---------+-----------+---------+------+
5 rows in set (0.00 sec)
mysql> alter table whd2 drop age; #删除字段
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from whd2;
+----+---------+-----------+---------+
| id | name | score_new | address |
+----+---------+-----------+---------+
| 1 | lisi | 99 | 上海 |
| 2 | zhansan | 50 | 杭州 |
| 3 | wangwu | 20 | 杭州 |
| 4 | xuyi | 40 | 南京 |
| 5 | whd | 50 | 湖北 |
+----+---------+-----------+---------+
5 rows in set (0.00 sec)
复制表
create table 复制的新表名 like 原表名; #这里只是单纯的复制表的格式,
insert into 源表名 select * from 复制的新表名; #将表中的内容导入新表中,也可以用来备份
create table test01 (select * from test); #克隆表 ,不带特殊格式,如主键
mysql> create table whd3 like whd2; #复制一个空白表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_whd |
+---------------+
| whd2 |
| whd3 |
| |
+---------------+
3 rows in set (0.00 sec)
mysql> insert into whd3 select * from whd2; #导入数据
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from whd3;
+----+---------+-----------+---------+
| id | name | score_new | address |
+----+---------+-----------+---------+
| 1 | lisi | 99 | 上海 |
| 2 | zhansan | 50 | 杭州 |
| 3 | wangwu | 20 | 杭州 |
| 4 | xuyi | 40 | 南京 |
| 5 | whd | 50 | 湖北 |
+----+---------+-----------+---------+
5 rows in set (0.00 sec)
mysql> create table whd6 (select * from whd2); #克隆表
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> show tables;
+---------------+
| Tables_in_whd |
+---------------+
| whd2 |
| whd3 |
| whd6 |
| |
+---------------+
4 rows in set (0.00 sec)
mysql> alter table whd6 change id id int(11) primary key; #为克隆表添加主键
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe whd6;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| score_new | varchar(3) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- 小拓展
mysql> create table if not exists whd8 (id int(4) zerofill primary key auto_increment,name varchar(10) not null,cardid int(40) not null unique key,hobby varchar(50));
Query OK, 0 rows affected (0.00 sec)
mysql> describe whd8;
+--------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+----------------+
| id | int(4) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| cardid | int(40) | NO | UNI | NULL | |
| hobby | varchar(50) | YES | | NULL | |
+--------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
注释:
- if not exists:表示检查创建的表是否存在,不存在就创建
- int(4) zerofill :表示若数值不满4位数,则前面用0填充,如:0001
- auto_increment :表示此字段为自增长值字段,每新增一条记录,自动递增1,自增长字段必须是主键
- unique key :表示唯一键,一张表中只能有一个主键,但是可以有多个唯一键
MySQL中的外键
含义:同一属性字段在表一中是主键,在表二中不是主键,则此字段称为表二的外键,外键的作用可以保证数据的完整性和一致性,注意:从表的外键字段和主表的字段必须具备相同的结构
- 创建主表
mysql> create table whd4 (id int(4),name varchar(50));
Query OK, 0 rows affected (0.01 sec)
mysql> alter table whd4 add constraint PK_id primary key (id); #为主表添加主键
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 创建从表
mysql> create table whd5 (id int(4),name varchar(50) primary key,age int(3));
Query OK, 0 rows affected (0.00 sec)
mysql> alter table whd5 add constraint FK_id foreign key (id) references whd4 (id);
#### 为从表添加外键,并将从表的id字段和主表的id字段建立外键关联
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table whd5;
…………………………
| whd5 | CREATE TABLE "whd5" (
"id" int(4) DEFAULT NULL,
"name" varchar(50) NOT NULL,
"age" int(3) DEFAULT NULL,
PRIMARY KEY ("name"),
KEY "FK_id" ("id"), #可以看到外键为id
CONSTRAINT "FK_id" FOREIGN KEY ("id") REFERENCES "whd4" ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
…………………………
外键作用:当插入新的数据记录时,要先插入主表才能插入从表,删除数据记录时,要先删除从表才能删除主表
- 插入记录
mysql> insert into whd5 values(1,'sahnghai',22);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("whd"."whd5", CONSTRAINT "FK_id" FOREIGN KEY ("id") REFERENCES "whd4" ("id"))
mysql> insert into whd4 values(1,'sahnghai');
Query OK, 1 row affected (0.00 sec)
mysql> insert into whd5 values(1,'sahnghai',22);
Query OK, 1 row affected (0.01 sec)
- 删除记录
mysql> drop table whd4;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop table whd5;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table whd4;
Query OK, 0 rows affected (0.00 sec)
如果要删除外键约束字段要先删除外键约束,在删除外键名
alter table 从表名 drop foreign key FK_id;
alter table 从表名 drop key FK_id;
数据库用户管理
- 新建用户
create user ‘用户名’@‘来源地址’ identified
mysql> create user 'lisi'@'localhost' identified by 'abc123'; #新建用户并设置密码
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mysql.user\G; #查看用户
mysql> select password('abc123'); #设置加密密码
+-------------------------------------------+
| password('abc123') |
+-------------------------------------------+
| *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
create user 'wangwu'@'localhost' identified by '*6691484EA6B50DDDE1926A220DA01FA9E575C18A';
#### 复制加密密码,新建用户使用加密密码
- 查看用户信息
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| lisi | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | localhost |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
- 用户名重命名
mysql> rename user 'lisi'@'localhost' to 'whd'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| whd | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | localhost |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
- 删除用户
mysql> create user 'whd2'@'localhost' identified by 'abc222'; #新建用户whd2
Query OK, 0 rows affected (0.00 sec)
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| whd | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | localhost |
| whd2 | *9DCA531FFCD837CE815EF4804980C0819080B86F | localhost |
+---------------+-------------------------------------------+-----------+
5 rows in set (0.00 sec)
mysql> drop user 'whd2'@'localhost'; #删除用户
Query OK, 0 rows affected (0.00 sec)
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| whd | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | localhost |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
- 修改用户密码
mysql> set password = password('aabb'); #修改当前登录的用户的密码
mysql> set password for 'whd'@'localhost' = password('bbcc'); #修改其它用户的密码
数据库用户授权
grant 权限列表 on 数据库名.表名 to ‘用户名’@‘来源地址’ identified by ‘密码’;
权限列表:表示授权使用各种操作命令,用逗号分隔,如:select,update,可以直接使用all表示所有权限
grant all on *.* :表示所有表所有权限
mysql> grant select on whd.* to 'whd'@'%' identified by 'abc123'; #修改用户whd对于数据库whd中的所有表的权限
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[root@mysql ~]# mysql -uwhd -p
Enter password:
mysql> show databases; #查看表格会发现只能看到两个库
+--------------------+
| Database |
+--------------------+
| information_schema |
| whd |
+--------------------+
2 rows in set (0.00 sec)
mysql> use whd;
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_whd |
+---------------+
| whd2 |
| whd3 |
| xuyichishi |
+---------------+
3 rows in set (0.00 sec)
mysql> select * from whd2;
+----+---------+-----------+---------+
| id | name | score_new | address |
+----+---------+-----------+---------+
| 1 | lisi | 99 | 上海 |
| 2 | zhansan | 50 | 杭州 |
| 3 | wangwu | 20 | 杭州 |
| 4 | xuyi | 40 | 南京 |
| 5 | hh | 50 | 湖北 |
+----+---------+-----------+---------+
5 rows in set (0.00 sec)
mysql> create table one (id int,name varchar(40)); #测试权限,不能使用
ERROR 1142 (42000): CREATE command denied to user 'whd'@'localhost' for table 'one'
- 查看权限
show grants for 用户名@来源地址;
mysql> show grants for 'whd'@'localhost'; #查看发现只有登录权限,USAGE表示登录权限
+-----------------------------------------+
| Grants for whd@localhost |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'whd'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)
- 撤销权限
revoke 权限列表 on 数据库名.表名 from 用户名@来源地址;
flush privileges;