MySQL数据库必备基操！！！（一）

一、数据库概述

数据
1、描述事物的符号记录
2、包括数字，文字、图形、图像、声音、档案记录等
3、以“记录”形式按统一的格式进行存储
作用：持久化保存、高可靠、高可用、数据的快速提取
表
1、每行为一条记录，每列为字段，组合成表
2、用来存储具体数据
数据库
1、表的集合，是存储数据的仓库
2、以一定的组织方式存储的相互有关的数据集合
数据库管理系统
数据库管理系统是实现对数据库资源有效组织、管理和存取的系统软件
数据库系统
1、一个人机系统，硬件、OS、数据库、DBMS、应用软件和数据库用户组成
2、用户可以通过DBMS或应用程序操作数据库

二、数据库类型

关系型数据库（sql）

1、存储字符、字符串、数值、布尔值等
2、可持久化保存数据，数据存储在磁盘中
3、使用于关系很明确的数据建立模型、定义、存储数据
4、关系模型的数据结构使用简单易懂的二维数据表
5、E-R图：提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型
①实体：即实例，对应现实世界中可区别其他对象的“事件”或“事物”。例如：商店和店员。
②属性：实体所具有的某一特性，一个实体可以有多个属性。例如：“店员” 实体集中的每个实体均具有姓名、住址、电话等属性
③关系：实体集之间的对应关系称为联系。例如商店和店员的聘用关系

非关系型数据库（nosql）

1、存储海量数据，给于“大数据”进行分析，筛选出有价值的部分
2、存储图片、视频、语音等
3、存储数据不以关系模型为依据，不需要固定的表格式
4、Redis可以作持久化存储数据，运行时数据存储在缓存中
优点：
1、数据库可高并发读写
2、对海量数据高效率存储与访问
3、数据库具有高扩展性与高可用性

三、MySQL概述

1、开源型关系数据库
2、Oracle旗下产品
3、遵循GPL协议，免费可修改
4、特点
①性能卓越，服务稳定
②开源、无版本限制、成本低
③多线程、多用户
④基于c|s（客户端|服务器）架构

四、MySQL数据库的基本操作

一、常用的数据类型

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

二、SQL语句

一、SQL语言
1、Structured Query Language的缩写，即结构化查询语言关系型数据库的标准语言。
2、用于维护管理数据库：
包括数据查询、数据更新、访问控制、对象管理等功能(增、删、改、查）
二、SQL分类
DDL:数据定义语言，用于创建数据库对象，如库、表、索引等
DML:数据操纵语言，用于对表中的数据进行管理
DQL:数据查询语言，用于从数据表中查找符合条件的数据记录
DCL:数据控制语言，用于设置或者更改数据库用户或角色权限

三、常用的约束

not null：非空约束，不允许为空值
default''：默认值约束，默认值为空值
primary key：主键约束，主键一般选择没有重复且不会为空值的字段
unique key：唯一键约束，此字段数据不可以重复，一张表中只能有一个主键，但是一张表中可以有多个唯一键。可以有空值，但只能有一个空值。
foreign key：外键约束，创建外键约束作用(误删，修改)，保证数据的完整性和一致性。与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主表外键字段和从表的字段具备相同的数据类型、字符长度和约束。
auto_increment：自增约束，即每条记录自动递增1，默认从1开始递增;自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次。

四、MySQL增删改查命令

一、库

#查看库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| py                 |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

#创建库
mysql> create database py;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| py                 |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

二、表

#查看表
mysql> use py;
mysql> show tables;
+--------------+
| Tables_in_py |
+--------------+
| hsh          |
+--------------+
1 row in set (0.00 sec)

#创建表
mysql> create table hsh (id int not null,name varchar(20) not null,score decimal(5,2) not null,age int(5),address varchar(50) default '深圳',primary key(id));
Query OK, 0 rows affected (0.03 sec)
mysql> describe hsh; 
+---------+--------------+------+-----+---------+-------+
| 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(5)       | YES  |     | NULL    |       |
| address | varchar(50)  | YES  |     | 深圳    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

三、表结构

#查看表结构
mysql> describe hsh; #可以简写为desc hsh;
+---------+--------------+------+-----+---------+-------+
| 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(5)       | YES  |     | NULL    |       |
| address | varchar(50)  | YES  |     | 深圳    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

四、表数据

#创建表数据
mysql> insert into hsh (id,name,score,age,address) values(1,'胖子',83.2,21,'北京'); #前面(id,name,score,age,address)字段名称可以省略
Query OK, 1 row affected (0.01 sec)
mysql> insert into hsh (id,name,score,age) values(2,'瘦子',83.4599,22);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from hsh;
+----+--------+-------+------+---------+
| id | name   | score | age  | address |
+----+--------+-------+------+---------+
|  1 | 胖子   | 83.20 |   21 | 北京    |
|  2 | 瘦子   | 83.46 |   22 | 深圳    |
+----+--------+-------+------+---------+
2 rows in set (0.00 sec)

#查看表数据
mysql> select * from hsh;
+----+--------+-------+------+---------+
| id | name   | score | age  | address |
+----+--------+-------+------+---------+
|  1 | 胖子   | 83.20 |   21 | 北京    |
|  2 | 瘦子   | 83.46 |   22 | 深圳    |
+----+--------+-------+------+---------+
2 rows in set (0.00 sec)

#修改表数据
mysql> update hsh set phone='12918878959' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from hsh;
+----+--------+-------+------+---------+--------------+
| id | name   | score | age  | address | phone        |
+----+--------+-------+------+---------+--------------+
|  1 | 胖子   | 83.20 |   21 | 北京    | 12918878959  |
|  2 | 瘦子   | 83.46 |   22 | 深圳    | 号码不详     |
+----+--------+-------+------+---------+--------------+
2 rows in set (0.00 sec)

五、用户

格式：
create user ‘用户名’@‘来源地址’ identified by '密码'

mysql> create user 'zhangsan'@'localhost' identified by 'abc233'; #创建MySQL用户
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mysql.user\G; #查看用户
*************************** 4. row ***************************
                  Host: localhost
                  User: zhangsan
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
………………
mysql> select password('abc233'); #设置密码加密
+-------------------------------------------+
| password('abc233')                        |
+-------------------------------------------+
| *2517D5E7FDE154859781E63A4F8E06E9804EB170 |
+-------------------------------------------+
1 row in set, 1 warning (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                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
………………
注释：
Field：字段名称
Type：数据类型
Null：是否允许为空
Key：主键
Default：默认值
Extra：拓展属性，如：标志弗列（表示了种子，增量/步长）

mysql> select user,authentication_string,host from mysql.user; #查看数据库用户
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| zhangsan      | *2517D5E7FDE154859781E63A4F8E06E9804EB170 | localhost |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.01 sec)

#用户重命名
mysql> rename user 'zhangsan'@'localhost' to 'lisi'@'localhost';
Query OK, 0 rows affected (0.03 sec)
mysql> select user,authentication_string,host from mysql.user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| lisi          | *2517D5E7FDE154859781E63A4F8E06E9804EB170 | localhost |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)

mysql> drop user 'zhangsan'@'localhost'; #删除用户
Query OK, 0 rows affected (0.00 sec)

mysql> set password = password('abc123');			#修改当前登录的用户的密码
mysql> set password for 'lisi'@'localhost' = password('123');	#修改其它用户的密码

六、复制及克隆

#复制
mysql> create table hsh1 like hsh; #复制表结构（备份表结构，主键也一起复制）
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+--------------+
| Tables_in_py |
+--------------+
| hsh          |
| hsh1         |
+--------------+
2 rows in set (0.00 sec)
mysql> select * from hsh1; #查看表数据，没有数据
Empty set (0.00 sec)
mysql> desc hsh1;查看表结构，与hsh的表结构一致
+---------+--------------+------+-----+---------+-------+
| 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(5)       | YES  |     | NULL    |       |
| address | varchar(50)  | YES  |     | 深圳    |       |
| phone   | char(12)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> insert into hsh1 select * from hsh; #复制表数据
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from hsh1;
+----+--------+-------+------+---------+--------------+
| id | name   | score | age  | address | phone        |
+----+--------+-------+------+---------+--------------+
|  1 | 胖子   | 83.20 |   21 | 北京    | 12918878959  |
|  2 | 瘦子   | 83.46 |   22 | 深圳    | 号码不详     |
+----+--------+-------+------+---------+--------------+
2 rows in set (0.00 sec)

#克隆
mysql> create table hsh3 (select * from hsh); #克隆表
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> show tables;
+--------------+
| Tables_in_py |
+--------------+
| hsh          |
| hsh1         |
| hsh3         |
+--------------+
3 rows in set (0.00 sec)
mysql> select * from hsh3; #克隆会将表数据一起克隆
+----+--------+-------+------+---------+--------------+
| id | name   | score | age  | address | phone        |
+----+--------+-------+------+---------+--------------+
|  1 | 胖子   | 83.20 |   21 | 北京    | 12918878959  |
|  2 | 瘦子   | 83.46 |   22 | 深圳    | 号码不详     |
+----+--------+-------+------+---------+--------------+
2 rows in set (0.00 sec)
mysql> desc hsh3; #克隆的表结构，不包含主键
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   |     | NULL    |       |
| name    | varchar(20)  | NO   |     | NULL    |       |
| score   | decimal(5,2) | NO   |     | NULL    |       |
| age     | int(5)       | YES  |     | NULL    |       |
| address | varchar(50)  | YES  |     | 深圳    |       |
| phone   | char(12)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table hsh3 change id id int(11) primary key; #为克隆的表添加主键
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc hsh3; #通过查看表结构，查看主键是否添加 
+---------+--------------+------+-----+---------+-------+
| 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(5)       | YES  |     | NULL    |       |
| address | varchar(50)  | YES  |     | 深圳    |       |
| phone   | char(12)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

五、外键

外键：从表的的主键字段，对于关联的主表来说是外键，主表的外键字段和从表的主键字段必须有一样的数据类型、字符长度和约束。
1、外键的主表的支墩必须设为主键，从表不能是临时表
2、删除：先删从表在删主表，对于外键，先删除外键约束，在删除字段或表

mysql> create table a (id int(4),name varchar(50)); #创建主表
mysql> create table b (id int(4) primary key,name varchar(50),age int(3)); #创建从表
mysql> alter table a add constraint PK_name primary key(name); #主表设置主键，以PK开头
mysql> alter table b add constraint PK_name foreign key(name) references a(name); #我从表设置外键，并建立外键关联，references：引用
mysql> show create table b; 查看从表b的结构

添加数据先主表，在添加从表，关联字段数据一致
mysql> insert into a values(1,'zhangsan');
mysql> insert into b values(1,'zhangsan',18);
删除表，先删从表，在删主表
mysql> drop tables b;
mysql> drop tables a;
删除外键约束，先删外键约束，在删外键名
mysql> alter table b drop foreign key FK_name;
mysql> alter table b drop key FK_name;

总结：

查：
#库
show databases;
#表
show tables;
#表结构
describe 表名; #可简写desc 表名;
#表数据
SELECT * FROM 表名;#查看表数据
SELECT 字段名 from 表名;#查看单独字段
SELECT 字段名，字段名 from 表名;#查看多个字段
SELECT name from 表名\G;#以列表方式竖向显示
SELECT * from 表名 info limit 2;#显示前三行，字段名的那一行为第一行
SELECT * from 表名 info limit 2,3;#显示从第二行往下的第三行
#用户
select user,authentication_string,host from mysql.user #如果进入目标库中，不需要指定库名

增：
#库
create database 库名;
#表
create table 表名 (字段1 数据属性，字段2 数据属性，···，primary key (字段));
#表结构
alter table 表名 add 字段名 数据属性;
#表数据
insert into 表名 (字段名) values(字段值);
#用户
create user ‘用户名’@‘来源地址’ identified by '密码';

删：
ps:
#库
drop database 库名;
#表
drop table [库名.]表名; #use进入库中，不用加库名
#表结构
alter table 表名 drop 字段名;
#表数据
delete from 表名 别名 where 别名.id='';
#用户
drop user '用户名'@'localhost或来源IP地址';

改：
#表
alter table 旧表名 rename 新表名;
rename table 旧表名 to 新表名;
#表结构
alter table 表名 change 旧列名 新列名 数据类型(数值) unique key; #可添加唯一键
#表数据
update 表名 set 字段名=字段值 where id=“”;
#用户
set password = password('abc123'); #修改当前登录的用户的密码
set password for 'lisi'@'localhost' = password('123');	#修改其它用户的密码