MyQL数据库管理（基本操作，SQL语言，DDL,DML,DQL,DCL）

前言

• MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一
• MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性

一、MySQL数据库表结构

数据以表格的形式出现，每行为单独的一条记录，每列为一个单独的字段，许多的记录和字段组成一张表单（table）若干的表单组成（database）

1.1 常见数据类型

• 字符串类型（CHAR（O-255固定长度）
• VARCHAR（O-255可变长度））
• 数值类型（INT（整数型）、FLOAT（浮点型）)
• 日期和时间类型（DATE（年月日）、TIME（时分秒））

1.2 常用约束类型

约束是一种限制，它通过对表的行或到的数据做出限制，来确保表的数据的完整性、唯一性

1.2.1 主键约束 primary key

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也
不允许出现空值。每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引

1.2.2 外键约束foreign key

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的
两个字段或是两个表的两个字段之间的参照关系

1.2.3唯一约束unique

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。唯一约束不允许出现重复的值，但是可以为多个null。同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束

1.2.4 非空约束not null与默认值default

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。Null类型特征:所有的类型的值都可以是null，包括int、float等数据类型

二、数据库基本操作命令

2.1 MySQL常用管理操作

（1）查看数据库结构
（2）创建及删除库和表
（3）管理表的记录

2.2 查看数据库列表信息的命令

• show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

2.3 查看数据库中的数据表信息命令

• 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                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |

2.4 显示数据表的结构（字段）命令

• describe [数据库名.]表名

mysql> describe 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          

二、SQL语言

3.1 SQL语言概述

SQL语言

• 是Structured Query Language的缩写，及结构化查询语言
• 是关系型数据库的标准语言
• 用于维护管理数据库，如数据查询，数据更新，访问控制，对象管理等功能
  SQL分类
• DDL：数据定义语言
• DML：数据操纵语言
• DQL：数据查询语言
• DCL：数据控制语言

3.2 DDL语句

3.2.1 作用

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

3.2.2 DDL语句创建库、表(create)

• 创建数据库：create database 数据库名
• 创建数据表：create table 表名（字段定义…)

mysql> create database auth;	###创建库
Query OK, 1 row affected (0.00 sec)

mysql> use auth;
Database changed

示例：
mysql> create database school;		###创建数据库school
Query OK, 1 row affected (0.00 sec)

mysql> show databases;			###查看创建的school
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myadm              |
| mysql              |
| new                |
| performance_schema |
| school             |
| sys                |
+--------------------+
7 rows in set (0.00 sec)
mysql> use school;				###进入school
Database change

mysql> create table auth(	###创建表
    -> name char(16)not null,	###名称不为空，16个字符
    -> passwd char(48) default '',	###密码默认为空，密码课48个字符
    -> primary key(name));	###主键
Query OK, 0 rows affected (0.01 sec)

mysql> create table info (id int(4) not null primary key,name char(10) not null,address varchar(50) default 'nj');
Query OK, 0 rows affected (0.01 sec)
mysql> show tables；

示例：
mysql> create table info (
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> address varchar(50) default 'nanjing',
    -> age int(3) not null);
Query OK, 0 rows affected (0.00 sec)

3.2.3 DDL语句删除库，表（drop）

• 删除指定的数据表：drop table [数据库名.]表名
• 删除指定的数据库：drop database 数据库名

mysql> drop table auth.user;	###删除表
Query OK, 0 rows affected (0.00 sec)

mysql> drop database auth;	###删除库
Query OK, 0 rows affected (0.00 sec)

3.2.4 DDL语句更改表结构(alter)

删除列
ALTER TABLE 【表名字】 DROP 【列名称】
增加列
ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL  COMMENT '注释说明'
修改列的类型信息
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称（这里可以用和原来列同名即可）】 BIGINT NOT NULL  COMMENT '注释说明'
重命名列
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL  COMMENT '注释说明'
重命名表
ALTER TABLE 【表名字】 RENAME 【表新名字】
删除表中主键
Alter TABLE 【表名字】 drop primary key
添加主键
ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)
添加索引
ALTER TABLE sj_resource_charges add index INDEX_NAME (name);
添加唯一限制条件索引
ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);
删除索引
alter table tablename drop index emp_name;

3.2.5 DDL语句实验

创建登录用户

mysql>create user zhangsan@'%'identified by'123';
%:指任意的远程终端

给用户授权登录

GRANT all ON *.* TO 'zhangsan'@'192.168.1.149' IDENTIFIED BY '123';

测试用户登录

mysql -uzhangsan -p123 -h 192.168.1.149;

修改用户自身密码

mysql>set password=password ('123456') ;

root用户更改其他用户密码

mysql>set password for zhangsan'%'=password('123123');

root找回密码及修改

关闭数据库，修改主配置文件(/etc/my.cnf）添加: skip-grant-tables
systemctl stop mysqld 
#vim /etc/my.cnf
skip-grant-tables
启动数据库，空密码登录并修改密码
systemctl start mysqld
update mysql.user set password=password(‘新密码’) where user='root';
或者 update mysql.user set authentication_string=password('123') where user='root';  ###centos7
删除skip-grant-tables,重启数据库验证新密码

3.3 DML语句

3.3.1 作用

DML语句用于对表中的数据进行管理
包括以下操作

• insert：插入新数据
• update：更新原有数据
• delete：删除不需要的数据

3.3.2 向数据表中插入新的数据记录

• insert into 表名（字段1，字段2，…）values（字段1的值，字段2的值，…）

mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

mysql> desc info;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(4)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | NO   |     | NULL    |                |
| address | varchar(50) | YES  |     | nanjing |                |
| age     | int(3)      | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.3.3 修改，更新数据表中的数据记录

• update 表名 set 字段名 1=值1[，字段名2=值2] where条件表达式

mysql> update city set name='叫兽' where id=1 or id=4;	###修改id1和id4的name
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> select * from city;
+----+--------------+---------+
| id | name         | address |
+----+--------------+---------+
|  1 | 叫兽         | no      |
|  2 | 王二麻子     | 北京    |
|  3 | 王三麻子     | 北京    |
|  4 | 叫兽         | 北京    |
|  5 | 王五麻子     | 北京    |
+----+--------------+---------+
5 rows in set (0.00 sec)

mysql> update city set name='ermazi';	###修改所有id的name
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from city;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | ermazi | no      |
|  2 | ermazi | 北京    |
|  3 | ermazi | 北京    |
|  4 | ermazi | 北京    |
|  5 | ermazi | 北京    |
+----+--------+---------+
5 rows in set (0.00 sec)

3.3.4 在数据表中删除指定的数据记录

• delete from 表名 where条件表达式

mysql> delete from city where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from city;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  2 | ermazi | 北京    |
|  3 | ermazi | 北京    |
|  4 | ermazi | 北京    |
|  5 | ermazi | 北京    |
+----+--------+---------+
4 rows in set (0.00 sec)

• 不带where条件的语句表示删除表中所有记录(谨慎操作）

mysql> delete from city;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from city;
Empty set (0.00 sec)

3.3.5 查看表结构

mysql> desc city;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(4)      | NO   | PRI | NULL    |       |
| name    | char(10)    | NO   |     | NULL    |       |
| address | varchar(50) | YES  |     | 66666   |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.4 DQL语句

3.4.1 作用

• DQL是数据查询语句，只有一条：SELECT
• 用于从数据表中查找符合条件的数据记录

3.4.2 查询时不指定条件

SELECT字段名1，字段名2…FROM表名

mysql> select name from city;
+--------+
| name   |
+--------+
| 小包   |
| 二包   |
| 二包   |
| 二包   |
| 二包   |
+--------+
5 rows in set (0.00 sec)
mysql> select name,address from city;
+--------+---------+
| name   | address |
+--------+---------+
| 小包   | 北京    |
| 二包   | 北京    |
| 二包   | 北京    |
| 二包   | 北京    |
| 二包   | 北京    |
+--------+---------+
5 rows in set (0.00 sec)

3.4.3 查询时指定条件

• SELECT字段名1，字段名2…FROM表名 WHERE条件表达式

mysql> select name,address from city where id=1 or id=3;
+--------+---------+
| name   | address |
+--------+---------+
| 小包   | 北京    |
| 二包   | 北京    |
+--------+---------+
2 rows in set (0.00 sec)

3.5 DCL语句

2.5.1 作用

• 设置或查看用户的权限，或者创建用户

3.5.2 设置用户权限

• 若用户已存在，则更改用户密码
• 若用户不存在，则新建用户
• GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [IDENTIFIED BY ‘密码′ ]

mysql> grant all privileges on *.* to 'root'@'%' identified by 'abc123' with grant option;
###all privileges：所有权限，%：所有终端
Query OK, 0 rows affected, 1 warning (0.00 sec)

3.5.3 查看用户权限

• SHOW GRANTS FOR 用户名@来源地址

mysql> show grants for 'root'@'%';
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

3.5.4 撤销用户权限

• REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址

mysql> revoke all privileges on *.* from 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'root'@'%';
+----------------------------------------------------+
| Grants for root@%                                  |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' WITH GRANT OPTION |
+----------------------------------------------------+
1 row in set (0.00 sec)

3.5.5 清空表数据

truncate table tmp;

3.5.6 在已有表中添加字段

alter table users add wocao  varchar(50) not null;

3.5.7 远程登录其他用户的数据库

mysql -ulph -p123 -h192.168.1.60

四、创建临时表

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;

五、克隆表

5.1 方法一：通过like方法，复制ky表生成test表

方法一：
mysql> create table test like ky;             
Query OK, 0 rows affected (0.01 sec)

mysql> show create table ky\G
*************************** 1. row ***************************
       Table: ky
Create Table: CREATE TABLE "ky" (
  "user_name" char(16) NOT NULL,
  "user_passwd" char(48) DEFAULT '',
  PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from ky;           ##like方法复制表结构，不复制数据
Empty set (0.00 sec)

mysql> insert into test select * from users;      ##将ky表的数据写入test表
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

5.2 方法二：通过创建表的方法克隆表

mysql> show create table ky\G         #获取源表结构，，索引等信息
*************************** 1. row ***************************
       Table: ky
Create Table: CREATE TABLE "ky" (
  "user_name" char(16) NOT NULL,
  "user_passwd" char(48) DEFAULT '',
  "level" char(16) NOT NULL,
  PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


CREATE TABLE "test3" (
  "user_name" char(16) NOT NULL,
  "user_passwd" char(48) DEFAULT '',
  "level" char(16) NOT NULL,
  PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8;         #改名后创建与源表一样的表结构


mysql> insert into test3 select * from ky;                          #导入源表数据
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from test3;                                                 #查看新创建表的数据
+-----------+-------------------------------------------+-------+
| user_name | user_passwd                               | level |
+-----------+-------------------------------------------+-------+
| lisi      | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 10    |
| lisi1     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 10    |
| lisi2     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 40    |
| lisi3     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 50    |
| lisi4     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 60    |
| lisi5     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 70    |
| lisi6     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 80    |
+-----------+-------------------------------------------+-------+