MySQL基本操作命令和数据表高级操作

MySQL基本操作命令和数据表高级操作

一、常用的数据类型

主要包括以下五大类：

• 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
• 浮点数类型：FLOAT、DOUBLE、DECIMAL
• 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
• 日期类型：Date、DateTime、TimeStamp、Time、Year
• 其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

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

char与varchar

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：

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

1、查看数据库

mysql -u root -p
SHOW DATABASES;

2、查看数据库包含的表

USE 数据库名;
SHOW TABLES;

3、查看表结构

USE 数据库名;
DESCRIBE [数据库名.]表名;
可缩写成：DESC 表名;



查看表结构
desc 表名;                以表格格式展示表结构
show create table 表名;   以SQL命令格式展示表结构，还能显示表的其它信息

 

三、SQL语句

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

创建新的数据库

CREATE DATABASE 数据库名;

例如：create database hj;

创建新的表

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

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

删除指定数据表

DROP TABLE [数据库名.]表名;				#如不用USE进入库中，则需加上数据库名

DROP DATABASE 数据库名;

删除指定数据库

DROP DATABASE 数据库名;

 

1、管理表中的数据记录

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

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

例：create database ty;

use ty;

 create table aa (id int(5) not null, name char(5) not null, age char(5), sex char(2), primary key (id));

insert into aa (id,name,age) values(1,'张三',22);

 

 

查询数据记录

SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];

例：select * from aa;
select name from aa where id=4;
select name from aa\G   #以列表方式竖向显示
select * from aa limit 2;  #只显示头两行
select * from aa limit 2,1;  #显示第二行后的第一行

 

 

 

 

修改、更新数据表中的数据记录

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

例：insert into aa values(6,'赵六',19,'女');
insert into aa (id,name,age,sex) values(7,'钱七',23,'男');
select * from aa;

 update aa set id=3 where name='李四';
select * from aa;

update aa set age=30, sex='女' where id=3;
select * from aa;

 

 

 

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

DELETE FROM 表名 [WHERE 条件表达式];

例：delete from aa where id=3;
select * from people;


delete from aa where id >= 10 and id <= 20;

 

 

2、修改表名和表结构

修改表名

ALTER TABLE 旧表名 RENAME 新表名;

例： alter table aa rename bb;
show tables;
select * from bb;

 

扩展表结构（增加字段）

ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default ‘地址不详’：表示此字段设置默认值 地址不详；可与 NOT NULL 配合使用

例：
alter table bb add address varchar(50) default '地址不详';

 

 

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

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

例：
 alter table bb change name new_name varchar(20) unique key;
desc bb;

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

 

删除字段

ALTER TABLE 表名 DROP 字段名;

例：alter table bb drop address;

 

3、扩展

use hj;
create table if not exists info (
    -> id int(4) zerofill primary key auto_increment,
    -> name char(10) not null default '匿名',
    -> phone int not null unique key,
    -> address varchar(50) not null default '地址不详' unique key);

show tables;
desc info;
insert into info (name,phone,address) values('lyx',12345678,'南京');
insert into info (name,phone) values('zdr',12345643);
insert into info (phone) values(13245678);
insert into info (phone,address) values(13245678,'陕西');
insert into info (id,phone,address) values(3,123123789,'苏州');
insert into info (id,phone,address) values(10,12312379,'无锡');
insert into info (phone,address) values(123379,'扬州');

#---------------命令解释--------------------------------
#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
#not null：表示此字段不允许为NULL
#default '地址不详':指定默认值

 

 

 

 

 

 

 

四、数据表高级操作

1、克隆表

方法一

create table info1 like info;;  #通过 LIKE 方法，复制 info表结构生成 info1 表
insert into info1 select * from info; #备份内容

 

方法二

CREATE TABLE info2 (SELECT * from info);

show create table info2\G;					#获取数据表的表结构、索引等信息
SELECT * from info2;

表的结构不同

create table 新表 like 旧表;      通过like方法复制旧表的表结构
insert into 新表 select * from 旧表;        向新表插入旧表查询的数据

可以保证 新表的表结构和表数据 跟旧表是一致的


create table 新表 (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 test1 ( id int(4) zerofill primary key auto_increment, name e char(10) not null default '匿名', phone int not null unique key, address varchar(50) noot null 

desc test1;

insert into test1 values(1,'zyz',123456,'卷');

select * from test3;
临时表的特点：使用show tables；无法查看到表

临时表    临时保存表结构  表数据  测试场景
create temporary table 表名 (字段 数据类型, ....);
临时表只能在当前连接中有效，能正常被增删改查，但是show tables是看不到的，并且换一个或者退出当前后都临时表都会失效

 

 

4、Mysql中6种常见的约束

主键约束（primary key）
外键约束（foreign key）
非空约束（not null）
唯一性约束（unique [key|index]）
默认值约束（default）
自增约束（auto_increment）

5、创建外键约束作用（误删，修改），保证数据的完整性和一致性

#创建主表profession
create table profession (proid int, proname char(5));
#创建从表student
create table student (id int primary key, name char(10), age int, pid int);

desc profession;

#为主表profession添加一个主键约束，主键名建议以“PK_”开头
alter table profession add primary key(proid);    给主表设置主键
#为从表student表添加外键，并将test5表的hobid字段和profession表的proid字段建立外键关联，外键名建议以“FK_”开头
alter table student add constraint FK_pro foreign key(pid) references profession (proid);


desc student;

insert into profession values (1,'云计算'),(2,'大数据'),(3,'JAVA'),(4,'测试');
insert into student values (1, 'zyz', 22, 1), (2, 'zwz', 24, 3), (3, 'gxl', 23, 2), (4, 'yzh', 27, 4);

主键表和外键表的理解：

• 以公共关键字作主键的表为主键表（父表、主表）
• 以公共关键字作外键的表为外键表（从表、外表）
  注意：与外键关联的子表的字段必须设置为主键。要求主表外键字段和子表的字段具备相同的数据类型、字符长度和约束。

专业表（主键表、主表、父表）
专业编号(主键)      专业名称
1                   云计算
2                   大数据
3                   java
4                   ui
5                   前端
6                   测试


学员表（外键表、外表、从表）
学号      姓名       年龄      专业编号（外键）
1         ZWZ        22        1
2         SJL        23        3
3         XYD        25        2

插入数据时，先主再外

删除数据时，先外再主

• 

 

 

• 插入新的数据记录时，要先主表再从表

 

 

• 删数据记录时，要先从表再主表，也就是说删除主键表时必须要先删除其他与之相关联的表

delete from student where name='yzh';
select * from student;
delete from profession where proname='测试';
select * from profession;

 

 

五、数据库用户管理

1、新建用户

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


create user 'lisi'@'localhost' identified by password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

 

2、查看用户信息

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

 

 

 2.1、查看当前登录用户

select user();

 

3、重命名用户

rename user 'zhangsan'@'localhost' to 'wangwu'@'%';
rename user 'zhangsan'@'localhost' to 'wangwu'@'localhost';

 

4、删除用户

drop user 'lisi'@'localhost';

 

5、修改当前登录用户密码

set PASSWORD = PASSWORD('abc123');

6、修改其他用户密码

 set password for 'wangwu'@'%' = 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('root') where user='root' and host='localhost';

FLUSH PRIVILEGES;
quit

mysql -u root -proot

 

六、数据库用户授权

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”部分， 则用户的密码将为空。

例：grand select on ty.* to 'wangwu'@'%';   给wangwu读的权限
    grant all on  *.* to 'wangwu'@'%'; 给wangwu所有权限
GRANT select ON ty.* TO 'wangwu'@'localhost' IDENTIFIED BY 'abc123';   可以授权同时设置密码

 

 

1.1远程登陆mysql

mysql -u wangwu -proot -h 192.168.10.17 -P 3306

注意防火墙一定要关，只有登录的权限，没有其他权限

2、查看权限

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

例如：
 show grants for 'wangwu'@'%';

3、撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址;
例如：
revoke select on "ty".* from 'wangwu'@'%';
SHOW GRANTS FOR 'ty'@'localhost';

#USAGE权限只能用于数据库登陆，不能执行任何操作；USAGE权限不能被回收，即 REVOKE 不能删除用户。
flush privileges;
revoke all on *.* from 'wangwu'@'%';
 show grants for 'wangwu'@'%';

 

 

总结

1、常用操作

创建库和表
create database 数据库名; 
create table 表名(字段1 数据类型,字段2 数据类型,....,[primary key(主键名)]);

删除库和表
drop table [数据库名.]表名;
drop table 数据库名;

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

扩展表结构（增加字段）
alter table 表名 add address varchar(50) default '地址不详';

修改字段（列）名，添加唯一键
alter table 表名 change 旧列名 新列名 数据类型 [unique key];

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

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

插入新数据
insert into 表名(字段1,字段2[,...]) values(字段1的值,字段2的值,...);

更新数据
update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式];

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

查询数据记录
select 字段名1,字段名2[,...] from 表名 [where 条件表达式];

select * from aa limit 2;  #只显示头两行
select * from aa limit 2,1;  #显示第二行后的第一行

2、主键唯一键区别

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

3、一些参数的意思

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

4、克隆表

方法一：create table 新的表名 like 想要复制的表;
insert into 新的表名 select * from 想要复制的表;
这个方法先是将表的结构进行复制，之后再复制内容，这种方法复制的表内容完整。

方法二：create table 新的表名 (select * from 想要复制的表);
这种方法会将oldtable中所有的内容都拷贝过来不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra（auto_increment）等属性。
 

5、清空表

• 速度
  drop>truncate>delete
• 安全性
  delete 最安全

6、创建临时表

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

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

7、创建外键约束

• 主键表和外键表的理解：
  • 以公共关键字作主键的表为主键表（父表、主表）
  • 以公共关键字作外键的表为外键表（从表、外表）
    注意：与外键关联的子表的字段必须设置为主键。要求主表外键字段和子表的字段具备相同的数据类型、字符长度和约束。.
  • 插入新的数据记录时，要先主表再从表
  • 删数据记录时，要先从表再主表，也就是说删除主键表时必须要先删除其他与之相关联的表
  • 删除外键约束

alter table wolong drop foreign key FK_card;
alter table wolong drop key FK_card;

8、MySQL中6种常见的约束

• 外键约束 foreign key
• 非空约束 not null
• 唯一约束 unique [key|index]
• 默认值约束 default
• 自增约束 auto_increment

9、数据库用户管理

增删改查的命令上面可以看到
当忘记用户密码时可以 修改 /etc/my.cnf 配置文件，不使用密码直接登录到 mysql
使用 update 修改 root 密码，flush privileges;刷新数据库。
修改完成之后记得需要将/etc/my.cnf添加的项注释或者删除
用户权限管理上面看操作