【MySQL】2、MySQL数据库的管理

常用

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

常用的数据类型:

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

主键是唯一的，但主键可以由多个字段构成

SQL 语句

DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
DML：数据操纵语言，用于对表中的数据进行管理
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限

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

新建-create

增：
create database 库名;        创建库
use 库名;    
create table 表名 (字段1 数据类型,字段2 数据类型,...,PRIMARY KEY (主键名));

增例：
create database school;
use school;
create table class (id int not null,name char(10) not null,score decimal(5,2),passwd char(48) default'',primary key (id));

#NOT NULL   不允许为空值
#DEFAULT''  默认值为空
PRIMARY KEY :主键一般选择没有重复并且不为空值的字段

删除-drop

删：
drop table 表名;            删除表
drop table 库名.表名;       删除指定库中的表
drop database 库名;         删除库

#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

删例：
drop table class;
drop table school.class;
drop database school;

2、DML管理表中的数据记录

增-insert

insert into 表名(字段1,字段2,...) values (字段1的值，字段2的值,...);
desc 表名;        查看表的格式
insert into class(id,name,score,passwd) VALUES (1,'zhangsan',70.5,password('123456'));

insert into class values(2,'lisi',87.3,123456);    向表中添加数据
select * from 表名;        查看表中的内容


#PASSWORD('123456'):查询数据记录时，密码字串以加密形式显示;若不使用PASSWORD('')，查询时以明文显示。

添加字段

alter table 表名 add 新字段
alter table ky30 add address varchar(48) default '地址不详';

alter table 表名 modify column 字段名 数据类型；

改-update

UPDATE 表名 SET 字段名1=字段值1,字段名2=字段值2 WHERE 条件表达式;
#要修改该哪些字段就写哪些字段

例：
修改指定用户的密码：
update school.class set passwd=password('') where name='zhangsan';
修改指定id的姓名和密码
update class set name='wangwu',passwd='123' where id=2;

修改表的字段

alter table 表名 change 旧列名 新列名 数据类型;

修改表名

alter table 原表名 rename 新表名;
alter table ky30 rename ky29;

修改root用户登录密码

修改mysql的登录密码：
update mysql.user set authentication_string=password('123') where User='root';
刷新生效
flush privileges;

删-delete

DELETE FROM 表名 WHERE 条件表达式;

删除class表指定id的数据
delete from class where id=2;
清空表中的所有数据
delete from class;
drop table class;

delete清空表，原理：一行一行删除记录的数据，会返回删除的记录条目；如果表中有自增长字段，会记录最大ID，当再次新添加的记录，会从原来最大的记录ID后面继续自增写入记录

truncate清空表，原理是：将表结构按原样重新建立，速度较快，不会返回被删除的条目；再次添加记录，ID会从1开始

3、DQL数据查询语句

查-select

select * from 表名        查看指定表的所有数据

查找指定表中的指定用户：
select id,name,score,passwd from school.class where name='zhangsan';
select * from school.class where id=2;

限制查找
select * from limit 2;           显示前两行
select * from limit 2，3;        显示前两行后的三行

数据表高级操作

清空列表

delete from 语句可以使用 where 子句对删除的结果集进行过滤选择

truncate table语句是删除表中所有记录数据

创建一个表并写入内容
create table ky (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', level CHAR(16) NOT NULL, PRIMARY KEY (user_name));
insert into ky values('lisi', password('123456'),'10');
insert into ky  values('lisi1', password('123456'),'45');
insert into ky  values('lisi2', password('123456'),'46');
insert into ky  values('lisi3', password('123456'),'47');
insert into ky  values('lisi4', password('123456'),'48');
insert into ky  values('lisi5', password('123456'),'49');
insert into ky  values('lisi6', password('123456'),'50');
insert into ky  values('lisi7', password('123456'),'1');
show tables;
select * from ky;

删除表中符合条件的数据
delete from ky where level >=45;
select * from ky;
清空表中的所有数据
truncate table ky;
select * from ky;

临时表

用于保存一些临时数据，只在当前连接可见，连接被关闭后，临时表就会被 MySQL 删除，相关的资源也会被释放。

CREATE TEMPORARY TABLE `mytmp` (`id` int(10) NOT NULL AUTO_INCREMENT,`NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`level` int(10) NOT NULL,PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into mytmp(name,level) values('aa',10);
select * from mytmp;
退出数据库；临时表就会消失

mysql -uroot -p

克隆表

方法1：

create table ky (name varchar(32) not null,passwd char(48) default'',id int(10) nott null);
show tables;
insert into ky values('lisi', password('123456'),'10');
insert into ky  values('lisi1', password('123456'),'45');
insert into ky  values('lisi2', password('123456'),'46');
insert into ky  values('lisi3', password('123456'),'47');
insert into ky  values('lisi4', password('123456'),'48');
insert into ky  values('lisi5', password('123456'),'49');
insert into ky  values('lisi6', password('123456'),'50');
insert into ky  values('lisi7', password('123456'),'1');
select * from ky;

克隆表的格式
create table test like ky;
select * from test;
克隆表的数据内容
insert into test select * from ky;
select * from test;

方法2：

show create table ky\G
克隆表的格式
create table test1 ("name" varchar(32) NOT NULL,"passwd" char(48) DEFAULT '',"id" int(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
show tables;
克隆表的内容
insert into test1 select * from ky;
select * from test1;

DCL设置用户权限

1.新建用户

create user '用户名'@'来源地址' 设置用户的密码 identified by '密码';

2.查询创建的用户信息，并做增删改

mysql 用户名 mysql.user

select user

修改用户密码
set password = password ('密码') 当前
修改其他用户的密码
set password for '用户名'@'localhost' = password ('密码')

忘记用户的密码
修改my.cnf配置，跳过密码认证
skip-grant-tables

数据库用户授权-grant

授予部分权限
grant 权限列表 on 库名.表名 to '用户名'@'来源地址' identified by '密码';
授予全部权限
grant all prvileges on 库名.* to '用户名'@'来源地址' identified by '密码';

grant select on school.* to 'zhangsan'@'localhost' identified by '123456';
flush privileges;   

退出mysql用zhangsan账户登录
quit
mysql -u zhangsan -p

验证授权的访问操作
select * from school.class; 
验证非授权的访问操作
drop table school.class;

授权
grant all on school.* to 'lisi'@'192.168.220.101' identified by 'abc123';   
刷新
flush privileges;
查看授权情况
show grants;

查看用户权限

show grants for 用户名@来源地址
查看用户权限
show grants for 'lisi'@'192.168.220.101';

撤销用户的权限

revoke 权限列表 on 库名.表名 from '用户名'@'来源地址'
例：
revoke all on school.* from 'lisi' @ 'localhost';

遇到root密码忘了怎么办？

【处理方案】

1、在 my.ini 或 my.cnf 的 [mysqld] 下添加 skip-grant-tables
[root@localhost mysql]# vi /etc/my.cnf
[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
skip-grant-tables                     ####此处添加###


2、重启mysql数据库
[root@localhost mysql]# systemctl restart mysqld    


3、登录数据库
[root@localhost mysql]# mysql -uroot -p
Enter password:               ###回车直接登录
mysql> 

4、修改root密码
mysql> update mysql.user set authentication_string=PASSWORD('abc123') where User='root'; 
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5、修改mysql免密码登录配置 然后重启服务
[root@localhost mysql]# vi /etc/my.cnf
[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
#skip-grant-tables

6、重启mysql数据库
[root@localhost mysql]# systemctl restart mysqld


7、mysql数据库验证
[root@localhost mysql]# mysql -uroot -p
Enter password:                                ####输入abc123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

案例：建立公司IT运营部工资表

需求描述:

为公司建立员工工资数据库 imployee_salary
在imployee_salary数据库中，建立 IT_salary数据表，以保存IT运营部员工的工资信息，如下表所示

表格如下：

岗位类别		姓名	年龄	员工ID	学历	年限	薪资
网络工程师	孙空武	27	011	本科	3	4800
Windows工程师	蓝凌	19	012	中专	2	3500
Linux工程师	姜纹	32	013	本科	8	15000
Java软件工程师	关园	38	014	大专	10	16000
硬件驱动工程师	罗中昆	29	015	大专	9	16500

答：

1、建立数据库
mysql> create database imployee_salary;
Query OK, 1 row affected (0.00 sec)

mysql> use imployee_salary;
Database changed

2、建立数据表IT_salary            ###这边一定要注意是中文的逗号还是英文的逗号
MySQL [imployee_salary]> create table IT_salary (
    -> type varchar(32) not null,
    -> name char(16) not null,
    -> age tinyint not null,
    -> id smallint not null,
    -> degree char(16) default '大专',
    -> years tinyint not null,
    -> salary int not null,
    -> primary key (id));



3、将IT运营部的员工工资信息插入到IT_salary

mysql> insert into IT_salary values(
    -> '网络工程师','孙空武',27,011,'本科',3,4800);
Query OK, 1 row affected (0.04 sec)

mysql> insert into IT_salary values(
    -> 'Windows工程师','蓝凌',19,012,'中专',2,3500);
Query OK, 1 row affected (0.01 sec)

mysql> insert into IT_salary values( 
    -> 'Linux工程师','姜纹',32,013,'本科',8,15000); 
Query OK, 1 row affected (0.00 sec)

mysql> insert into IT_salary values( 
    -> 'Java软件工程师','关园',38,014,'大专',10,16000);
Query OK, 1 row affected (0.00 sec)

4、查看核对信息
mysql> select * from IT_salary;
+---------------------+-----------+-----+----+--------+-------+--------+
| type                | name      | age | id | degree | years | salary |
+---------------------+-----------+-----+----+--------+-------+--------+
| 网络工程师          | 孙空武    |  27 | 11 | 本科   |     3 |   4800 |
| Windows工程师       | 蓝凌      |  19 | 12 | 中专   |     2 |   3500 |
| Linux工程师         | 姜纹      |  32 | 13 | 本科   |     8 |  15000 |
| Java软件工程师      | 关园      |  38 | 14 | 大专   |    10 |  16000 |
+---------------------+-----------+-----+----+--------+-------+--------+
4 rows in set (0.00 sec)