数据库 - mysql （个人学习笔记）--基本操作即命令概要(知识网络梳理)

一、用户管理

1.最高权限账户查看当前数据库内所有id

use mysql;	#1.进入根目录下mysql文件夹中

select host,user from user;	#2.打开mysql文件夹中的 user表，取出对应列

2.用户操作

①.用户操作：添加新用，进入user表中，选择host与user列直接插入新用户（由于user表及其重要，任意修改可能会破坏基本信息，所以此方法不推荐）

②.用户操作特殊命令

create user '用户名@'IP地址' identified by '密码';	#创建用户，密码必须要用‘’单引号标识

drop user '用户名'@'IP地址';	#删除用户

rename user '用户名'@'IP地址' to '新用户名'@'IP地址';	#修改用户

set password for '用户名'@'IP地址' = Password('新密码');	#修改密码

3.用户权限

非root用户，在新创建时，默认权限为无权限(usage)，即无法操作与查看，mysql下的几乎所有表和库

grant '权限' on '数据库'.'表'	to '用户名'@'IP地址'; #赋予指定用户拥有某个数据库下，某个表的，某种权限

grant '权限','权限' on '数据库'.* to '用户名'@'IP地址';	#赋予指定用户拥有某个库下，所有表的，某两种权限（*表示多）

*.*	#所有数据库下所有的表



#基本权限概要
select	#查看
insert	#插入
all privileges	#赋予最高权限（除授权给其他人以外的所有权限）
usage	#最低权限（无任何权限）
delete #删除数据行
drop #删除数据库

二、数据库级别语句

1.创建数据库

create database '数据库名';

create database	'数据库名' default charset utf8 collate utf8_general_ci;
#collate utf8_general_ci : utf8内部匹配排序规则

create database '数据库名' default character gbk collate gbk_general_ci;

由于utf8的适用范围更广，所以创建库时，更推荐使用utf8编码

2.其他操作命令

show databases; #查看所有数据库名

use '数据库名';	#进入数据库

drop database '数据库名'; #删除数据库

三、表级别语句

1.创建表

create table '表名'(nid int,name varchar(20)); #创建表，创建名为nid，name的两行数据

①.指定引擎创建表

create table '表名'(
	'列名' '数据类型' '是否可以为空',#not null ：不可为空，not null default 9：不可为空，且默认值为9
	'列名' '数据类型' '是否可以为空' #null ：可为空
)engine = innodb default charset=utf8;

engine = innodb ：指定操作引擎为innodb，事务处理引擎;
nid int not null default 2：不能为空，且默认值为2;

事务(原子级操作:不可分)：
例如：当两个银行用户间发生资金转账，一方已经转账，卡中已经扣钱，另一方还没收到时，银行突然断电。转账过程无法完整执行，为了维护用户的利益不缺失。如果转账到接收的整个过程无法完整执行，银行系统会自动将两个银行用户数据回滚到转账前
innodb引擎能使表实现事务功能

②.自增数据列

create table '表名'(
	nid int not null auto_increment primary key, #自增数据列
	name varchar(20)
)engine=innodb default charset=utf8;

primary key：声明主键索引，一张表，只有一个主键，不能为NULL，且数值不能重复；
唯一索引：可以为NULL；
自增列：索引必须是主键，且唯一。

自增数据列，相当于一个不重复的id名称，上述代码中，如果name列，每增加一行，nid列将会自动填上一行递增的数据。

仅向name列插入数据，但是nid列自动填入了数据

③.外键

#创建表后再建立约束关系
alter table '主表名' add constraint 'fk_主表名_从表明' foreign key '主表名(绑定的列名)' references '从表名(绑定的列名)';	#创建外键

#创建表时，建立约束关系
creat table part_nid(
nid int not null primary key,
name char(16) not null
)

create table part_part(
nid int not null primary key,
fruits char(32) null ,
color_id int not null,
constraint fk_cc foreign key (color_id) references part_nid(nid)
)

外键：绑定两个不同表的列，使其产生关联，‘从表列’ 约束 ‘主表列’。
fk_主表名_从表明：给外键命名。

实例举例：
创建两个表
主表：id，姓名，年龄，所属职业。
从表：id，职业名称。

所属职业列：输入数字，对应从表的id列，使得查看数字，就能了解人物对应的职业名称。

将主表 所属职业列 与从表的 id列 绑定，使得所属职业列，仅能输入从表 id列 中拥有的字符.

创建两个表：

绑定port_key表的nid列为port_list表的port_nid列外键

先给port_key插入数据，让其拥有职业信息

为主表插入数据：

可见标红段：port_nid列录入数字5时，由于从表中nid，没有5，插入数据失败。这就是外键的作用

④.修改表

alter table '表名' add '列名' '类型';	#添加列

alter table '表名' drop column '列名';  #删除列

alter table ' 表名' modify column '列名' '类型'; #一般情况下只修改列的类型

alter table '表名' change '原列名' '新列名' '类型'; #修改列名和类型

alter table '表名' add primary key('列名'); #添加主键

alter table '表名' drop primary key; #删除主键

alter table '表名' modify '列名' int,drop primary key; #删除主键

alter table '主表名' add constraint 'fk_主表名_从表明' foreign key '主表名(绑定的列名)' references '从表名(绑定的列名)';	#创建外键，外键名称：'fk_主表名_从表明'

alter table '表名' drop foreign key '外键名';  #删除外键，即删除约束关系

alter table '表名' alter '列名' set default '默认值'; #为列添加默认值

alter table '表名' alter '列名' drop default; #删除默认值

⑤.复制表

create table '新表名' like '目标表名';	#复制目标表格式
insert into '新表名' select * from '目标表名'; #复制目标表信息

2.其他操作

show tables; #查看所有表

desc '表名'; #查看指定表的属性

drop table '表名'; #删除指定表

delete from '表名'; #清空指定表

truncate table '表名'; #清空指定表

select * from '表名'; #查看指定表的信息

四、行级别语句

1.增

insert into '表名'(nid,name)values(33,"Tom"),(44,'Timy'); #给指定表中的nid，name行插入数据, 可以同时插入多行数据

insert into '主表名'('主表列名') select '从表列名' from '从表名'; #将从表的某一列合并到主表的某一列中，要求数据类型一致 

2.删

delete from '表名' where '列名' = '列值';	#删除表中所有数据为'列值'的行
#可用：and，or增加删除条件

3.改

update '表名' set '列名' = '值'; #更新表，即修改某列的数据为'值'
#可用：where '列名' = '值'指定更新列，and，or增加更新条件 
#可用：=可更改为 > or < 。

4.查

select * from '表名'; #查看表中的所有列,效率低，不推荐使用。

select '列名','列名',... from '表名'; #查看指定列。

select * from '表名' where 'id' between '5' and '10'; #查看id为5到10之间的行

select * from '表名' where 'id' in (11,22,33)
select * from '表名' where 'id' not in (11,22,33)
select * from '表名' where 'id' in (select nid from 表)

5.模糊搜索(通配符)

select * from '表名' where '列名' like 'ac%'  #查看对应列中，ac开头的所有行（后跟多个字符）

select * from '表名' where '列名' like 'ac_'  #查看对应列中，ac开头的所有行（后跟一个字符）

6.分页查看

select * from '表名' limit 3;   #查看前三行      
   
select * from '表名' limit 3,9; #查看第三行开始的后九行数据

select * from '表名' limit 9 offset 3;    #查看第三行开始的后九行数据，推荐使用

7.排序查看

select * from '表名' order by '列名' asc;      #按列从小到大排序
select * from '表名' order by '列名' desc;     #按列从大到小排序
select * from '表名' order by '列名1' desc,'列名2' asc;    #按列1的规则排序后，再按列2的规则小范围（不破坏列1规则）排序

8.分组

select '列名1' from '表名' group by '列名1'; #取出列名1中出现的不同元素，即：去重

select '列名1',	#max:取出可能重复的行中，值最大的行；
max('列名2'),	#min:取出值最小的行；
min('列名2'),	#sum:取出重复的行的值的和；
sum('列名2'),	#count:输出重复的个数，并给其指定一个别名为sum_num
count('列名2') as sum_num 
from '表名' group by '列名1'; #取出列名1中出现的不同元素，去重。

9.联合

select '列名' from '表名1' union select '列名' from '表名2'; #将表一表二中的指定列，联合在一张表中输出；有重复，将会去重

select '列名' from '表名1' union all select '列名' from '表名2'; #将表一表二中的指定列，联合在一张表中输出，union all:不去重

10.连表

select * from '表名1','表名2' where '表名1'.'列名1' = '表名2'.'列名2';#将表2按照列名对应的，接在表1右侧。**表2要迎合表1内容，不够自动补null**

select * from '表名1' left join '表名2' on '表名1'.'列名1' = '表名2'.'列名2';#将表2按照列名对应的，接在表1右侧。 **表2要迎合表1内容，不够自动补null**

select * from '表名1' inner join '表名2' on '表名1'.'列名1' = '表名2'.'列名2';#将表2按照列名对应的，接在表1右侧。相互迁就，即相互约束，没有null.

五、其他

1.枚举

用某一对象，标识某一对象；
例如我们声明x表示男人，或者y表示女人,这就是创建了一个枚举，相当于创建一个字典。
Enum person：
x = ‘男人’,
y = ‘女人’
person.x 代表: 男人
person.y 代表: 女人

创建枚举：

create table occupation(		
person_name varchar(20),
occupation_name enum('teacher','student','worker') #创建表时，创建枚举，这意味着，插入数据时，对应列，只能插入枚举中拥有的数据,且仅能插入一种枚举元素
); 
inser into occupation_list(person_name,occupation_name) values('Tom','teacher'); #枚举列只能插入一个枚举元素

2.集合

集合与枚举类似，都有着限制插入数据的作用，不同在于，插入集合列时，可以同时插入多个集合元素

create table set_part(
set_name set('a','b','c','d')	#创建表时，创建集合
);

inser into set_list(set_name) values('a','b'); #可以同时插入多个集合元素

3.数据类型一览

二进制：
	    bit 
整数：
	    tinyint
	    smallint
	    int
	    bigint
小数：
		decimal --精确的
		float   
		double 

字符串：
定长
		char #查找速度快，浪费空间

变长
		varchar #查获找速度慢，节省空间
		text
		mediumtext
		longtext

二进制数据：
		TinyBlob
		Blob
		MediumBlob
		LongBlob

时间：
		date
		YYYY-MM-DD（1000-01-01/9999-12-31）
		
		time
		HH:MM:SS（'-838:59:59'/'838:59:59'）
		
		year
		YYYY（1901/2155）
		
		datetime
		YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
		
		timestamp
		YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
		
枚举:	enum

集合:	set