文章目录
简介
- MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一
- MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
一、数据库的基本操作
1.1 查看数据库结构
1.1.1 查看数据库信息
- show databases
mysql> show databases; '大部分SQL操作命令必须以";"结束'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
1.1.2 查看数据库中的表信息
-
use 数据库名
-
show tables
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
Database changed
mysql> show tables; '查看数据库中的表信息'
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
......
1.1.3 显示数据表的结构（字段）
- describe [数据库名 . ]表名
mysql> describe user; '也可以使用desc user;' '显示数据表的结构（字段）'
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N
......
1.2 SQL语句概述
1.2.1 SQL语言
- Structured Query Language的缩写，即结构化查询语言
- 关系型数据库的标准语言
- 用于维护管理数据库
- 包括数据查询，数据更新，访问控制，对象管理等功能
1.2.2 SQL分类
- DDL：数据定义语言
- DML：数据操纵语言
- DQL：数据查询语言
- DCL：数据控制语言
1.2.3 DDL语句
创建数据库和表
- DDL语句用于创建数据库对象，如库，表，索引等
- 使用DDL语句新建库、表
-
创建数据库：create database 数据库名
-
创建数据表：create table 表名（字段定义…）
-
mysql> create database school; '创建数据库'
Query OK, 1 row affected (0.00 sec)
mysql> show databases; '查看数据库'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> create table info (id int(3) not null primary key auto_increment,name varchar(10) not null,score decimal(5,2),address varchar(50) default '未知'); '创建数据表'
'###not null 不允许为空'
'###primary key 主键'
'###auto_increment 学号自动增长，加入一个人名就自动增长'
'###varchar（50） 可变长度字符）'
'###decimal(5,2) 类型为浮点型，有效数字是5位，小数点后保留2位'
'###default 默认约束'
mysql> show tables; '查看数据表'
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)
mysql> desc info; '查看数据表的结构'
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 未知 | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.07 sec)
mysql> select * from info; '###此时没有写入数据，表中没有数据，只有表结构'
Empty set (0.00 sec)
1.2.4 删除数据库和表
-
删除指定的数据表：drop table [数据库名.]表名
-
删除指定的数据库：drop database 数据库名
mysql> drop table school.info; '//删除表，中间加点，就相当于是绝对路径'
Query OK, 0 rows affected (0.00 sec)
mysql> drop database school; '//删除库'
Query OK, 0 rows affected (0.00 sec)
1.2.5 DML语句
管理表中的数据
- DML语句用于对表中的数据进行管理
- 包括的操作
- INSERT：插入新数据
- UPDATE：更新原因数据
- DELETE：删除不需要的数据
【1】向数据表中插入新的数据记录
- insert into 表名（字段1，字段2，…） values（字段1的值，字段2的值，…）
顺序一致，数量相同
有序插入，中间用逗号分隔
mysql> use school;
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> desc info; '查看表中的字段'
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 未知 | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into info (id,name,score,address) values (1,'zhangsan',88,'beijing'); '插入数据'
Query OK, 1 row affected (0.01 sec)
mysql> select * from info; '查看数据表'
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.00 | beijing |
+----+----------+-------+---------+
1 row in set (0.01 sec)
mysql> insert into info values (2,'lisi',77,'shanghai'); 'insert into 后面不跟字段名（默认所有字段），values后面按顺序写全'
Query OK, 1 row affected (0.06 sec)
mysql> select * from info;
+----+----------+-------+----------+
| id | name | score | address |
+----+----------+-------+----------+
| 1 | zhangsan | 88.00 | beijing |
| 2 | lisi | 77.00 | shanghai |
+----+----------+-------+----------+
- 插入两行，前面学号定义自动增加
添加多个字段，括号，中间用逗号隔开
default 前面定义的是未知
mysql> insert into info (name,score,address) values('wangwu',66,'shenzheng'),('zhaoliu',99,default);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from info;
+----+----------+-------+-----------+
| id | name | score | address |
+----+----------+-------+-----------+
| 1 | zhangsan | 88.00 | beijing |
| 2 | lisi | 77.00 | shanghai |
| 3 | wangwu | 66.00 | shenzheng |
| 4 | zhaoliu | 99.00 | 未知 |
+----+----------+-------+-----------+
4 rows in set (0.00 sec)
- 把80分以上的成绩的人筛选出来：
select * from info where score > 80;
mysql> select * from info where score > 80;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.00 | beijing |
| 4 | zhaoliu | 99.00 | 未知 |
+----+----------+-------+---------+
2 rows in set (0.01 sec)
- 把大于80分的人的信息导入到一张临时新表中:
mysql> create table tmp as select * from info where score > 80; 'as 导入到新表中'
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info |
| tmp |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from tmp;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.00 | beijing |
| 4 | zhaoliu | 99.00 | 未知 |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
【2】修改、更新数据表中的数据记录
- update 表名 set 字段名 1=值1[,字段名2=值2] where条件表达式
'改info表中的zhaoliu 99改为100'
mysql> update info set score=100 where name='zhaoliu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from info;
+----+----------+--------+-----------+
| id | name | score | address |
+----+----------+--------+-----------+
| 1 | zhangsan | 88.00 | beijing |
| 2 | lisi | 77.00 | shanghai |
| 3 | wangwu | 66.00 | shenzheng |
| 4 | zhaoliu | 100.00 | 未知 |
+----+----------+--------+-----------+
4 rows in set (0.00 sec)
'通过直接修改数据库的方式实现修改数据库管理员密码'
mysql> update mysql.user set authentication_string=password('1234567') where user=.'root';
Query OK, 3 rows affected, 1 warning (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 1
mysql> flush privileges; '刷新'
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# systemctl restart mysqld '重启数据库服务'
[root@localhost ~]# netstat -ntap | grep 3306 '查看端口'
tcp6 0 0 :::3306 :::* LISTEN 120074/mysqld
[root@localhost ~]# mysql -u root -p '重新登录'
Enter password: '输入密码1234567'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, 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>
数据库密码破解（免密登录，忘记密码）
这是保存密码的字段：authentication_string
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
......
skip-grant-tables '跳过数据库权限验证'
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# netstat -ntap | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 119890/mysqld
'免密登录验证'
[root@localhost ~]# mysql -u root -p
Enter password: '不用输密码，直接回车'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, 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.
【3】在数据表中删除指定的数据记录
- delete from 表名 where条件表达式
'把tmp表中90分以上人的数据记录删掉：'
mysql> delete from tmp where score >= 90;
Query OK, 1 row affected (0.06 sec)
'不带where条件的语句表示删除表中所有记录'
mysql> delete from tmp; '删除整个表''谨慎操作'
Query OK, 1 row affected (0.01 sec)
1.2.6 DQL语言
- DQL是数据查询语句，只有一条：SELECT
- 用于从数据表中查找符合条件的数据记录
【1】查询时可不指定条件
mysql> select * from info;
+----+----------+--------+-----------+
| id | name | score | address |
+----+----------+--------+-----------+
| 1 | zhangsan | 88.00 | beijing |
| 2 | lisi | 77.00 | shanghai |
| 3 | wangwu | 66.00 | shenzheng |
| 4 | zhaoliu | 100.00 | 未知 |
+----+----------+--------+-----------+
4 rows in set (0.00 sec)
【2】查询时指定条件
- SELECT字段名1，字段名2…FROM表名 WHERE条件表达式
mysql> select * from info where score > 80;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.00 | beijing |
| 4 | zhaoliu | 99.00 | 未知 |
+----+----------+-------+---------+
2 rows in set (0.01 sec)
数据表高级操作
【1】清空表（数据）
mysql> truncate table info; '删除表中数据'
Query OK, 0 rows affected (0.02 sec)
mysql> select * from info;
Empty set (0.00 sec)
- 删除表结构：
mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tmp |
+------------------+
1 row in set (0.01 sec)
- 删库
mysql> drop database school;
Query OK, 0 rows affected (0.01 sec)
小结：
-
truncate 与 drop 区别：
truncate 是清空表内数据，表结构还在
drop删除表，表结构都没了 -
delete 和truncate的区别：
两者初始值id不同
【2】临时表
- 临时建立的表，用于保存一些临时数据，不会长期存在
- 连接断开，临时表被删除
mysql> create temporary table temp_info (id int(4) not null auto_increment,name varchar(10) not null,hobby varchar(10) not null,primary key(id))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
'写在括号内是是对字段的设定,写在括号外是对整张表的设定'
'PRIMARY KEY(id) 主键约束'
'innodb 支持事务'
【3】克隆表
- LIKE方法
两个步骤：
1 - 复制了表结构
create table test like mytmp;
2 - 复制了表数据
把原有数据导入到新表中
insert into test select * from mytmp;
'从info表完整复制结构生成tmp表，再导入数据'
mysql> create table tmp like info;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tmp select * from info;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
- SHOW CREATE TABLE 方法
'先查看info表完整结构，根据此结构创建名字不同结构相同的表tmp，再导入数据'
mysql> show create table info\G;
mysql> create table tmp(......);
mysql> insert into test select * from info;
1.2.7 DCL语言
数据库用户授权
【1】DCL语句设置用户权限（用户不存在时，则新建用户）
- GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [IDENTIFIED BY ‘密码′ ]
mysql> grant select on school.* to 'tom'@'localhost' identified by '123456';
'tom能够从本地访问school数据库中的所有表并能进行select操作，登录密码123456'
【2】查看用户的权限
- SHOW GRANTS FOR 用户名@来源地址
mysql> select user from mysql.user; '查看当前系统有哪些用户'
+-----------+
| user |
+-----------+
| root |
| root |
| mysql.sys |
| root |
+-----------+
4 rows in set (0.00 sec)
'创建用户Jerry，能够从本地登录，密码19961207，能够对所有数据库中的所有表使用所有命令'
mysql> grant all on *.* to 'jerry'@'localhost' identified by '19961207';
mysql> flush privileges; '刷新生效'
Query OK, 0 rows affected (0.00 sec)
mysql> select user from mysql.user; '查看当前系统用户'
+-----------+
| user |
+-----------+
| root |
| root |
| jerry |
| mysql.sys |
| root |
+-----------+
5 rows in set (0.00 sec)
mysql> show grants; '查看当前grant权限'
+---------------------------------------------------------------------+
| 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.01 sec)
mysql> show grants for 'jerry'@'localhost'; '查看jerry用户的权限，后面加for'
+----------------------------------------------------+
| Grants for jerry@localhost |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jerry'@'localhost' |
+----------------------------------------------------+
1 row in set (0.00 sec)
【3】撤销用户的权限
- REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址
mysql> revoke all on *.* from 'jerry'@'localhost'; '撤销Jerry 的所有权限'
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'jerry'@'localhost';
+-------------------------------------------+
| Grants for jerry@localhost |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)
小结：
grant:
当用户已存在是，直接提权
当用户不存在时，先创建用户，再提权。
revoke：
只撤销权限，不会删除用户
二、忘记数据库密码如何修改
#配置文件里面设置语句进行跳过验证
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
…
skip-grant-tables ===>添加一条代码
#进入mysql数据库
mysql> use mysql;
mysql> update mysql.user set authentication_string=password(‘123456’) where user=‘tom’;
#修改好之后记得将之前添加的代码删除
1614
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
