mysql 数据库 增删改查 基本操作

一   SQL  详细介绍

（一）SQL 分类

• 数据库：database
  
• 表：table，行：row 列：column
  
• 索引：index
  
• 视图：view
  
• 存储过程：procedure
  
• 存储函数：function
  
• 触发器：trigger
  
• 事件调度器：event scheduler，任务计划
  
• 用户：user
  
• 权限：privilege
  

（二） SQL 语言规范

• 在数据库系统中，SQL 语句不区分大小写，建议用大写
  
• SQL语句可单行或多行书写，默认以 " ; " 结尾
  
• 关键词不能跨多行或简写
  
• 用空格和TAB 缩进来提高语句的可读性
  
• 子句通常位于独立行，便于编辑，提高可读性
  

（三）数据库对象和命名

1，数据库的组件(对象)：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

2，命名规则：

必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）

不要使用MySQL的保留字,tabble select show databases

（四） SQL语句分类

• DDL: Data Defination Language 数据定义语言
  

CREATE，DROP，ALTER

• DML: Data Manipulation Language 数据操纵语言
  

INSERT，DELETE，UPDATE

软件开发：CRUD

• DQL：Data Query Language 数据查询语言
  

SELECT

• DCL：Data Control Language 数据控制语言
  

GRANT，REVOKE

• TCL：Transaction Control Language 事务控制语言
  

COMMIT，ROLLBACK，SAVEPOINT

二   查看数据库

（一）查看数据库的基本命令

1，show databases

查看 数据库（有啥库）

2， use 数据库名

去到  指定的库

3， show tables

看有哪些表

4  describe  表名  (desc)

看表的属性

（二）表 抬头介绍

Field:字段名称

type:数据类型

Null :是否允许为空

Key :主键

Type:数据类型

Null :是否允许为空

key :主键

Default :默认值

Extra :扩展属性，例如:标志符列（标识了种子，增量/步长）1 2

 

（三）type 数据类型   介绍

1，常用的数据类型:

int：                 整型                                    用于定义整数类型的数据
float：              单精度浮点4字节32位        准确表示到小数点后六位        
double：           双精度浮点8字节64位
char：               固定长度的字符类型           用于定义字符类型数据。 20字节  4
varchar：          可变长度的字符类型                               20   4
text：                文本
image：            图片
decimal(5,2)：  5个有效长度数字，小数点后面有2位        指定长度数组

 

2， 注意事项

2.1Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错

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

2.3varchar(50) 能存放几个 UTF8 编码的汉字？

mysql 5.0以上版本 varchar(50) 指的是50字符，无论存放的是数字、字母还是 utf8 编码的汉字，都可以存放50个

3   char 和 varchar 区别

char 设定20字节 放4字节存储 占用20字节

varchar 设定20字节 放4字节存储 占用4字节

char : 搜索反应速度快 但是占空间

varchar : 不占用磁盘空间 搜索反应速度慢

4  选择正确的数据类型对于获得高性能至关重要，三大原则

1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型
   
2. 简单就好，简单数据类型的操作通常需要更少的CPU周期
   
3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化
   

5   修饰符

5.1  适用所有类型的修饰符

5.2  适用数值型的修饰符

三  对库和表的操作

（一） 创建数据库

（二）创建表

创建一个  叫排名的表   表属性：   id为整数 且不能为空  且是唯一  名字15个字节以内  武力值指定为5位有效数字（小数点后有两位）

（三）删表

drop table 表名

delete from 表名

（四）删库

drop database 数据库名

（小伙子思想很危险！）

四  对 数据的操作

（一）加入数据

插入数据   insert into

注意 values 中 写 字符串要加单引号 数字可以不加单引号

要写 PASSWD('123456') 这样密码就是加密的

（二）查看数据

select

（三） 更新数据 （修改）

update

（四）  删除数据

delete from 表名 where(指定)

（五）select  高级使用

1， 查询所有

select * from paiming;

2,查询指定列

3,  查指定 行

4,  查看前3行

5,  跳过前三行的  查看后两行

6， 详细显示（一一排列）

注意  此处无 分号

五    数据库高级操作

（一）drop  truncate delete 几个删除的区别

1，drop

drop table table_name

1）属于DDL

2)不可回滚（无法恢复)

3)不可带where

4)表内容和结构删除

5)删除速度快

2, truncate

1)属于DDL

2)不可回滚

3)不可带where

4)表内容删除

5)删除速度快

3  delete

1)属于DML

2)可回滚(可恢复)

3)可带where

4)表结构在，表内容要看where执行的情况

5)册删除速度慢，需要逐行删除

delete ：比如有4 条数据 删了后 id 从5开始

4   三种删除应用场景

不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

5 三种删除比较

速度

drop> truncate > delete

安全性

delete 最好

（二）    临时表

（放在内存中的  退出就没了）且使用SHOWTABLES命令是看不到创建的临时表

1，  创建临时表

解释： 创建一个叫  魅力的临时表格  表格属性为   id 6位数 且前五位自动补0  且唯一  且自动递增

            名字  20字节 且不为空   身份证18位  且不为空且唯一    美貌值 2位数

2，给临时表加 数据

3  查看临时表数据

这边可以看到  在给临时表加数据的时候  没有输入id  但是表格自动生成了 因为我们写了auto_increment

（三）  克隆表

1， 方法1

克隆表格属性和 表格数据

create table yyy2 like yyy;                  #复制格式，通过LIKE方法，复制yyy表结构生成yyy2表

insert into yyy2 select * from yyy;      #备份内容

2， 方法2

表格属性带不过来    只会克隆表格里的数据

克隆表，将数据表的数据记录生成到新的表中

CREATE TABLE test02 (SELECT * from test);   #复制test 表数据到test02中

（四） 修改表名  及列相关

1，修改表名

ALTER TABLE 旧表名 RENAME 新表名;

2，修改列名 列属性（生产环境不建议）

修改字段(列)名，添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];

3，添加列

ALTER TABLE 表名 ADD 新列名 属性

在加入  新列  时    可以看到虽然没填数据   全部会显现默认的数据

4  删除列

ALTER TABLE 表名 DROP 字段名;

六  数据库用户管理

（一） 新建用户

1，通式

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];

2  具体解释

2.1  '用户名'

指定将创建的用户名

2.2   '来源地址'

指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录   可用通配符%

2.3 ‘密码'

若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；

若使用加密密码，需要先使用SELECT PASSWORD('密码')；获取密文，再在语句中添PASSWORD '密文'；

若省略“IDENTIFIED BY"部分，则用户的密码将为空(不建议使用)

3 创建用户 实际操作

3.1 获取密码密文 注意加 单引号  不然报错

3.2 创建用户

（二）查看用户信息

创建后的用户保存在mysql 数据库的user表里

SELECT User,authentication_string,Host from user;

（三）重命名指定

RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

（四）删除用户

DROP USER 'lisi'@'localhost' ;

（五）修改当前密码

SET PASSWORD = PASSWORD('abc123');

（六）修改其他用户密码

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('abc123T');

（七）忘记root密码的解决办法

vim /etc/my.cnf
[mysqld]
skip-grant-tables 
#数据库的单用户模式
skip-networking  #MySQL8.0不需要
 
#然后清空密码
update mysql.user set authentication_string='' where user='root' and host='localhost';
#注意刷新后生效
flush privileges;

1，在mysql 数据库配置文件  加入这一行 （类似单机模式  其他时候都把他去掉，只有破解密码时需要）

2， 重启 mysql  可以直接登录 不需要密码

3，然后使用SQL语句修改密码  并刷新权限

4，记得重新进配置文件  删除刚刚加的一行  skip-grant-tables

5  , 再次登录  发现不需要密码

七   数据库用户授权

（一） 授权模式

1，创建用户 再去定义授权 给数据库 操作权限 来源地址(登录)

2，直接 创建 用户 授权 来源地址 做用户授权

（二） 格式

1， 通式

grant 提权
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

2，格式具体解释

#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select, insert,

update”。使用"all"表示所有权限，可授权执行任何操作。

#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。

例如，使用“kgc.*"表示授权操作的对象为school数据库中的所有表。

#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.xyw.com"、“192.168.80.%”等。

#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。

在新建用户时，若省略“IDENTIFIED BY"部分，则用户的密码将为空。

（三）all privilege

all privilege权限如下:

insert(插入数据)

select (查询数据)

update (更新表的数据)

delete(删除表中数据)

create (创建库，表)

drop(删除库，表)

refernces

index(建立索引)

alter(更改表属性)

create temp orary tableslock tables (锁表)

execute

create view (创建视图)

show view(显示视图)

create routine(创建存储过程)

alter routine(修改存储过程)

event(事件)

trigger on(创建触发器)

（四）实例授权

1  实例授权1 权限全开

授权 wyq 所有权限 ky35库下所有表 登录来源只能为 本机

flush privileges;    #刷新权限

登录wyq 账户  

权限都有  可删可查

2  实例授权2 来源地址

授权 guozi 看的权限 ky35库下所有表 登录来源只能为 192.168.217 网段

flush privileges;    #刷新权限

授权 guozi 看的权限 ky35库下所有表 登录来源只能为任意网段

flush privileges;    #刷新权限

使用连接测试  成功

（五）查看权限

#USAGE权限只能用于数据库登陆，不能执行任何操作;

USAGE权限不能被回收，即REVOKE不能删除用户。

（六）撤销权限

1， 通式

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

2， 实例

可以先查看权限  复制下面这一行 （不复制 grant 把 to 改成from）

flush privileges;    #刷新权限

可以看到权限被移除  

#USAGE权限只能用于数据库登陆，不能执行任何操作;

USAGE权限不能被回收，即REVOKE不能删除用户。