数据库之MySQL基本操作命令

数据库之MySQL基本操作命令

一、常用的数据类型

常用的数据类型	作用
int	整型
float	单精度浮点-------4字节32位
double	双精度浮点-------8字节64位
char	固定长度的字符类型
varchar	可变长度的字符类型
text	文本
image	图片
decimal(5,2)	5个有效长度数字，小数点后面有2位

二、查看数据库、表和表结构

远程登录MySQL

mysql -u root -p 表示用root用户登录mysql

1、查看数据库

SHOW DATABASES；

2、查看数据库包含的表

use mysql；
show tables；

3、查看表结构

use 数据库名；
DESCRIBE [数据库名.]表名；
可以缩写成：DESC 表名 ；

三、SQL语句

SQL语言	功能
DDL	数据定义语言，用于创建数据库对象，如库、表、索引等
DML	数据操纵语言，用于对表中的数据进行管理
DQL	数据查询语言，用于从数据表中查找符合条件的数据记录
DCL	数据控制语言，用于设置或者更改数据库用户或角色权限

创建新的数据库

CREATE DATABASE 数据库名;

例如：create database deng;

删除指定数据库

drop database 数据库名

创建新的数据表

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
例：use CLASS;
create table D (id int(5) not null,name char(20) not null,age char(5),primary key (id));

删除指定数据表

DROP TABLE [数据库名.]表名

1. 管理表中的数据

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

INSERT INTO 表名（字段1，字段2，[,...]） VALUES (字段1的值，字段2的值，...);

查询数据记录

SELECT 字段1，字段2[,...] FROM 表名  *表示查看所有字段
例：select * from d
select name from d where id=1 表示查看在d数据表id是1的name数据
select name from d\G 以列表方式竖向显示
select * from d limit 2； 只显示前两行
select * from d limit 2，3； 显示第二行之后的三行

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

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式]

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

delete from 指定表名 where 条件表达式

清除表的所有记录

truncase

2. 修改表名和表结构

ALTER TABLE 旧表名 RENAME 新表名；

扩展表结构（添加字段）

ALTER table 表名 ADD addres varchar（40） default ‘....’;

删除字段

ALTER TABLE 表名 drop 字段 

修改字段（列）名，添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
unique key:唯一键（特性：唯一，但可以为空，空值值允许出现一次）
primary key：唯一且非空（主键一般选择能代表唯一性的字段不允许取空值（NULL）），一个表只能有一个主键。）

mysql
有主键、唯一键
主键：唯一且非空
唯一键：唯一，但可以为空（空值只能出现一次）
主键包含唯一键的部分属性
而唯一键不能完全作为主键

3.复制表的结构 相当于备份

create table 新建表名 like 被复制的表名；

四、数据表高级操作

1.克隆表

insert into 表名 select * from 表名；

也可以直接全部复制  create 表名 （select * from 表名）；

2. 清空表，删除表内的所有数据

方法一

delete from info1;
#DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。

方法二

truncate table info2;
#TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录。

删除速度：drop>truncate>delete
安全性：delete 最安全

3.创建临时表

临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。
如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。
PS：临时表无法创建外键。

CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

例：
create temporary table test3 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
sex char(2) not null);

insert into test3 values(1,'zhangsan','男');

select * from test3;
show tables;

五、数据库用户管理

1、新建用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
#----------------------解释部分-----------------------------------------
'用户名'：指定将创建的用户名
'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，
          本地用户可用localhost，允许任意主机登录可用通配符%
'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
       若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文';
       若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）
#----------------------------------------------------------------------
例如：
create user 'user1'@'localhost' IDENTIFIED BY '35123512';

select password('35123512');
create user 'user2'@'localhost' IDENTIFIED BY PASSWORD '*686EC9AF708AFAEBB8809F506E9FFC8AE012E893';

2、查看用户信息

#创建后的用户保存在 mysql 数据库的 user 表里
use mysql;
select user,authentication_string,Host from user;

3.重命名用户

rename user ‘旧用户名’@‘主机名’ to ‘新用户名’@‘主机名’

删除用户

drop user 'zhangsan'@'localhost';

5.修改当前登陆用户密码

set password=password（‘......’）

6.修改其他用户密码

set PASSWORD for 'yang'@'localhost' = PASSWORD('abc123');

7、忘记root密码的解决方法

修改/etc/my.cnf 配置文件，不使用密码直接登陆到mysql

vim /etc/my.cnf
[mysqld]
skip-grant-tables					#添加，使登录mysql不使用授权表

systemctl restart mysqld.service

mysql								#直接登录

使用update修改root密码，刷新数据库

update mysql.user set AUTHENTICATION_STRING = PASSWORD('35123512') where user='root';

FLUSH PRIVILEGES;
quit

mysql -u root -p35123512

注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除或注释，并重启 mysql 服务。

六、数据库用户授权

1、授予权限

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时，GRANT 语句用于修改用户信息。

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



#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。

#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。*例如,使用“school.*”表示授权操作的对象为 school数据库中的所有表。

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

#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分， 则用户的密码将为空。

例如：
#允许用户 deng用户 在本地查询 deng 数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。
GRANT select ON school.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'abc123';

flush privileges;
quit

mysql -u zhangsan -pabc123
use school;
show tables;
select * from info;

其他授权例子：
#允许用户 zhangsan 在所有终端远程连接 mysql ，并拥有所有权限。
GRANT ALL [PRIVILEGES] ON *.* TO 'zhangsan'@'%' IDENTIFIED BY 'abc123';

2、查看权限

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

例如：
SHOW GRANTS FOR 'zhangsan'@'localhost';

3、撤销权限

REVOKE 权限列表 ON 数据库名.表名 from 用户名@来源地址；
REVOKE SELECT ON 'deng'.* from 'deng'@'localhost'

#usage权限只能用于数据库登录，不能执行任何操作；usage权限不能回收 所以revoke不能删除用户

总结

常用操作

查看，创建数据库和数据表
数据库：查看 show database； 创建create database 数据库名；
数据表：查看 show tables；   创建create table 数据表名 （字段1 数据类型，字段2 数据类型，[primary key（主键名）]）

删除数据库和数据表
drop 数据库名 
drop 数据表名 

修改表名
alter table 旧表名 rename 新表名

扩展表结构
alter table 表名 add 字段 数据类型 default ‘.......’

修改字段（列名）
alter table 表名 change 旧字段 新字段 数据类型 
修改字段数据类型
alter table 表名 change 旧字段 新数据类型

删除字段、主键
alter table 表名 drop 字段名

添加字段
alter table 表名 add 字段名

插入新数据
insert into 表名 （字段1，字段2） values（字段1值，字段2值）；

修改数据
update 表名 set 字段名=字段值 where 条件表达式；

删除数据 
delete from 表名 where 条件表达式

查询数据
select 字段名 from 表名；
select * from 表名；
select * from 表名 where 条件表达式；
可以搭配limit 假设数据表一共一百行 只看最后两行
select * from 表名 limit 98，2；

主键和唯一键区别

mysql
主键：是唯一的并且不能为空
唯一键：唯一，但可以为空（只能出现一次）
主键包含唯一键的部分属性
而唯一键不能完全作为主键

还有一些参数的意思

if not exists：表示检测要创建的表是否存在，如果不存在就继续创建
int（4）zerofill：表示若数值不满4位数，则前面用0填充，列如0001
auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
primary key ：表示主键 主键是数据表中的唯一标识，此字段不能重复

克隆表 备份

方法一：create table 新的表名 like 需要复制的表；
insert into 新的表名 select * from 被复制的表名；
这个是将表的结构先复制，然后将数据表的中的数据复制

方法二：create table 新表名 （select * from 被复制的表名）；

删除表数据

delete from 表名；
truncate table 表名；
drop 表名

创建临时数据表

临时表创建成功后 show table 是看不到临时表的 临时表会在退出连接是自动销毁
create temporary table 表名 （字段1 数据类型，字段2数据类型，  primary key（主键名））；

数据库的用户管理

创建新的用户 CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码']；
查看用户信息 所有的用户信息都在mysql数据库中的user数据表中 可以用select user,authentication_string,Host from user;查看部分
也可以用select * from user\G；查看所有字段
删除用户：drop user ‘用户名’@‘主机名’；
修改用户名密码：set password=password（‘......’）；
忘记root密码的解决方法：可以修改etc/my.cnf配置文件 在mysql中添加

管理数据库的用户权限

授予权限：grant 给与的权限 on ‘数据库名’.* to ‘用户’@‘localhost’ [IDENTIFIED BY '密码'];
查看权限：show grants ‘用户’@‘localhost’；
撤销权限：REVOKE 权限列表 ON 数据库名.表名 from 用户名@来源地址；

时数据表

临时表创建成功后 show table 是看不到临时表的 临时表会在退出连接是自动销毁
create temporary table 表名 （字段1 数据类型，字段2数据类型，  primary key（主键名））；

数据库的用户管理

创建新的用户 CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码']；
查看用户信息 所有的用户信息都在mysql数据库中的user数据表中 可以用select user,authentication_string,Host from user;查看部分
也可以用select * from user\G；查看所有字段
删除用户：drop user ‘用户名’@‘主机名’；
修改用户名密码：set password=password（‘......’）；
忘记root密码的解决方法：可以修改etc/my.cnf配置文件 在mysql中添加

管理数据库的用户权限

授予权限：grant 给与的权限 on ‘数据库名’.* to ‘用户’@‘localhost’ [IDENTIFIED BY '密码'];
查看权限：show grants ‘用户’@‘localhost’；
撤销权限：REVOKE 权限列表 ON 数据库名.表名 from 用户名@来源地址；