MySQL数据库基础知识
一、数据库的基本概念主流数据库关系数据库非关系型数据库 二、MySQL数据库MySQL编译安装MySQL数据库基本操作新建表并插入数据修改数据记录删除数据记录查看表名修改表名和表结构复制表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/f[client]port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysql]port = 3306 #对应的端口default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysqld] #mysql服务配置user = mysql basedir = /usr/local/mysql #工作目录datadir = /usr/local/mysql/data #数据文件目录port = 3306character_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/f[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 3306tcp6 00 :::3306 :::*LISTEN106734/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 passwordEnter 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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: 0mysql> 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的成绩为99Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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填充，如：0001auto_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: 0mysql> 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"),#可以看到外键为idCONSTRAINT "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 failsmysql> 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';#新建用户whd2Query 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> exitBye[root@mysql ~]# mysql -uwhd -pEnter 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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;
