MySQL中库/表/字段/主键/用户操作示例与详解

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL中库/表/字段/主键/用户操作示例与详解

关联博文

MySQL数据库基础入门与常见操作

下面关于字符的设置中 : charset 等效于 character set 。

【1】数据库


① 创建数据库

create database `database` charset utf8;
//注意这里数据库名-database使用了反引号 `` ;
//如果不使用反引号,那么能使用关键字或者保留字。
//创建中文数据库;
set names gbk;
create database 中国 charset utf8;
//utf8字符集,校对集依赖字符集。
//修改数据库字符集:
alter database database_name charset GBK;
# 删除数据库
drop database name;
# 删除数据表
drop table table_name;
# 删除shi
drop view view_name;--删除视图



② 查看并切换数据库

# 查看当前所有数据库
show databases;
# “使用”一个数据库,使其作为当前数据库
use employees;


数据库命名规则

数据库名不得超过30个字符,变量名限制为29个

必须只能包含A–Z, a–z, 0–9, _共63个字符

不能在对象名的字符间留空格

必须不能和用户定义的其他对象重名

必须保证你的字段没有和保留字、数据库系统或常用方法冲突

保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了

③ sql92与sql99语法

sql92标准,仅仅支持内连接。

sql99标准,支持内连接+外连接(左外和右外)+交叉连接。

sql99实现连接条件和筛选条件的分离,可读性较高



④ 查看数据库的创建语句

show create database database_name;
• 1

⑤ 显示库中的数据表

切换到mysql数据库,浏览管理信息表,如:user

use mysql;
show tables;
show tables like '% or _';


7fefc2688ebc0da239c3f6ae50912d44.jpg

⑥ MySQL自带的数据库

MySQL有几个自带的数据库:mysql、information_schema、performance_schema和sys。

① mysql


MySQL系统自带的核心数据库,它存储了MySQL的用户账户信息和权限信息,一些存储过程、事件的定义信息,一些运行过程中产生的日志信息以及一些帮助信息和时区信息等。

② information_schema

MySQL系统自带的数据库,这个数据库保存着MySQL服务器维护的所有其他数据库的信息,比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据而是一些描述性信息,有时候也称之为元数据。在系统数据库information_schema中提供了一些以innodb_sys开头的表,用于表示内部系统表。

③ performance_schema

MySQL系统自带的数据库,这个数据库主要保存MySQL服务器运行过程中的一些状态信息,可以用来监控 MySQL服务的各类性能指标。比如统计最近执行了哪些语句,在执行过程的每个阶段都花费了多长时间,内存的使用情况等等。


④ sys

同样也是MySQL系统自带的数据库,这个数据库主要是通过视图的形式把 information_schema 和 performance_schema结合起来,帮助系统管理员和开发人员监控MySQL的运行性能。


【2】表操作

① 创建表

创建成功后,在数据库对应的文件夹下,会产生对应表的结构文件(扩展名为.frm,跟存储引擎有关系)。

create table t_user(
  id int(10)  PRIMARY KEY NOT NULL UNSIGNED AUTO_INCREMENT ,
  //主键,不为空,无符号,自动递增
  name VARCHAR(20) NOT NULL DEFAULT '',//默认为''
  age int(2) NOT NULL,
  dept_id int,
  CONSTRAINT `dept_id` FOREIGN KEY (`dept_id`) REFERENCES `department` (`department_id`) 
  ON DELETE CASCADE ON UPDATE CASCADE;
  --添加外键约束,外键名为‘dept_id’(可改,如deptId);
  --外键列(dept_id)对应表department的列department_id;
  --级联删除和更新
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
//设置存储引擎与表字符集和校对集


MySQL不支持检查约束,添加 check不起作用!

① 使用子查询创建表

使用AS subquery 选项,将创建表和插入数据结合起来。指定的列和子查询中的列要一一对应,通过列名和默认值定义列。

CREATE TABLE table
[(column, column...)]
AS subquery;
如:
CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees WHERE department_id = 80;

② 使用like创建表

#根据表employees创建employees2
CREATE TABLE employees2 LIKE myemployees.employees;

② 修改字符集、校对集和存储引擎、自增长

alter table table_name 表选项;
# 修改字符集
alter table sys_dept  convert to character set 'utf8'
# 修改字符集、校对集、存储引擎
alter table p_user charset=utf8,ENGINE=MYISAM,COLLATE=utf8_general_ci;
#修改自增长
alter table table_name auto_increment = 值 ;

修改表字符集表示表存储数据的时候使用该字符集。如utf8,表明使用三个字节存储一个汉字;gbk,表明使用两个字节存储一个汉字。

使用ALTER TABLE 语句可以实现:


  • 向已有的表中添加列
  • 修改现有表中的列
  • 删除现有表中的列
  • 重命名现有表中的列


③ 重命名表

执行RENAME语句改变表, 视图的名称,必须是对象的拥有者。

ALTER table dept RENAME TO detail_dept;


如下方式也可以

rename table emp to emp2


④ 为表添加属性约束语法

语法格式如下:

alter table [table_name] add 
[constraint] [constraint_name]
[unique| primary key|foreign key] ([column_name])


⑤ add增加列

alter table tb_sys_login_log 
add column remote_ip //列名
varchar(30) //字段类型
default null  //默认值
comment '后台获取真实ip' //注释
after ip;//位置
ALTER TABLE t_user
add COLUMN `born_date_time` 
datetime first(添加该列为第一列)


⑥ MODIFY 修改列

数字类型的如下所示

ALTER TABLE 库名.表名 
MODIFY COLUMN id  #列名
bigint(20)  # 字段类型
UNSIGNED #无符号
auto_increment # 自增
NOT null # 非空
primary key # 主键
COMMENT '设置主键'; #注释
AFTER update_op_id;# 位置

字符类型的如下所示

ALTER TABLE 库名.表名 
MODIFY COLUMN id  #列名
varchar(20)  # 字段类型
CHARACTER SET utf8 # 字符集
COLLATE utf8_bin # 排列规则
NOT null # 非空
COMMENT '设置注释'; #注释
AFTER update_op_id;# 位置

change关键字重命名列

ALTER TABLE p_user 
CHANGE COLUMN `NAME` `name`  
varchar(20)(带上数据类型)
 character set utf8
[位置:first];
-- 可以在改变列的时候同时改变其列位置
ALTER TABLE p_user 
CHANGE 
state
int(1) 
DEFAULT 0 
COMMENT '状态  0 申请;1 申请中;2 已审核;3 审核驳回'
AFTER STATE;

modify只能改字段数据类型完整约束,不能改字段名,但是change可以。

⑦ drop删除列

ALTER TABLE t_use drop COLUMN `born_date_time1`

⑧ 查看表结构

# 查看表DDL
desc/describle t_user;
# 显示表的所有列
show columns from  t_user;
# 查看表创建语句
show create table table_name;

⑨ 删除表

  • 数据和结构都被删除
  • 所有正在运行的相关事务被提交
  • 所有相关索引被删除
  • DROP TABLE 语句不能回滚



语法格式:

drop table 表名1,表名2
e.g:drop table t_user



其他删除用法如下:

DROP PROCEDURE procName ;-- 删除存储过程
drop table table_name;-- 删除表
drop view view_name; -- 删除视图
drop database name;-- 删除数据库
drop function name;-- 删除函数
drop trigger name;-- 删除触发器


⑩ 清空表

TRUNCATE TABLE语句:

  • 删除表中所有的数据
  • 释放表的存储空间

TRUNCATE语句不能回滚,可以使用DELETE 语句删除数据,可以回滚。


【3】表主键操作primary

① 创建表的时候在字段后面使用 primary key关键字

create table my_default(
id int PRIMARY KEY,--使用关键字
name VARCHAR(20) not null unique key ,
gender varchar(4) DEFAULT '男'
)charset utf8;


② 在创建表的时候,在所有的字段之后使用 primary key(主键的字段 列表)来创建主键(如果有多个字段作为主键,称之为复合主键)

create table my_pri(
number char(10),
course char(10),
score TINYINT,
PRIMARY KEY(number,course)--复合主键
)charset utf8;
desc my_pri;


3ffb02fa809995ec6558210658ecb4de.png


测试如下:

insert into my_pri VALUES('001','IT','10');--插入成功
insert into my_pri VALUES('001','IT','10');--插入失败
insert into my_pri VALUES('001','IT2','10');--插入成功



144782b25ff5e042a0e93b149542efd6.png

③ 创建表之后,为表追加主键

有两种方式,第一修改表字段属性;第二直接追加

第一种方式示例如下:

alter TABLE my_pri MODIFY number char(11) PRIMARY KEY;



第二种方式示例如下:

--增加单一主键
alter table table_name add primary key(column) ;
alter TABLE my_pri add PRIMARY KEY(number,course);
--增加复合主键
alter table my_pri add CONSTRAINT pk_score PRIMARY KEY(score)
--使用关键字CONSTRAINT 且指定主键名字pk_score 
alter table my_pri add CONSTRAINT  PRIMARY KEY(score)
--使用关键字CONSTRAINT 且使用默认名字 


④ 删除主键

alter table my_pri drop PRIMARY KEY;
--不能根据主键名字删除主键


【4】唯一约束UNIQUE

唯一约束就是唯一索引,其是一个索引。

① 增加单一约束

alter TABLE my_pri add CONSTRAINT nuc UNIQUE KEY(number);
--使用指定名字 nuc
alter TABLE my_pri add CONSTRAINT  UNIQUE KEY(course);
--使用默认名字
alter TABLE my_pri add UNIQUE KEY(score);
--不使用CONSTRAINT  关键字

查看增加约束后的表结构

show create TABLE my_pri;
CREATE TABLE `my_pri` (
  `number` char(11) NOT NULL,
  `course` char(10) NOT NULL DEFAULT '',
  `score` tinyint(4) DEFAULT NULL,
  UNIQUE KEY `nuc` (`number`),
  UNIQUE KEY `course` (`course`),
  UNIQUE KEY `score` (`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


68df98638bbaa81ba88db88bd2765ae2.png

② 增加复合约束

create table my_pri(
number char(10),
course char(10),
score TINYINT,
PRIMARY KEY(number,course)
)charset utf8;
alter TABLE my_pri add UNIQUE KEY(number,course);
--添加复合unique key,使用默认名字为第一个列名-number
alter TABLE my_pri add CONSTRAINT nuc UNIQUE KEY(number,course);
--添加复合唯一约束,自定义名字 nuc


查看创建唯一约束后表结构

show create TABLE my_pri;
--第一次
CREATE TABLE `my_pri` (
  `number` char(11) NOT NULL,
  `course` char(10) NOT NULL DEFAULT '',
  `score` tinyint(4) DEFAULT NULL,
  UNIQUE KEY `number` (`number`,`course`)--unique key 名字为 number
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--第二次
CREATE TABLE `my_pri` (
  `number` char(11) NOT NULL,
  `course` char(10) NOT NULL DEFAULT '',
  `score` tinyint(4) DEFAULT NULL,
  UNIQUE KEY `nuc` (`number`,`course`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
删除复合UNIQUE KEY number
alter table my_pri drop index number;
删除复合UNIQUE KEY nuc
alter table my_pri drop index nuc;


③ 删除属性约束同时增加一列

alter table my_default 
drop index name,--删除unique key name
add column id int first --增加id列位置为第一列


注意,普通索引和唯一索引删除的时候使用的关键字为index,主键和外键则使用对应关键字。


【5】数据操作

① 插入数据


给全表字段插入数据,不需要指定字段列表,但是插入数据值顺序与字段顺序一致。且非数值型数据需用引号包括(建议单引号),字符和日期型数据应包含在单引号中。

insert into table_name values(值列表)[,(值列表)];
--可一次性插入多条记录;
insert into p_user 
values
(1,'tom','B001'),
(2,'Janus','B002')

给部分字段插入数据,需选定字段列表。字段列表出现的顺序与字段的顺序无关。但是值列表的顺序必须与选定的字段列表顺序一致。

insert into table_name (字段列表) values(值列表)[,(值列表)];
--可一次性插入多条数据。
insert INTO p_user (name,age,sex) 
VALUES
('tom',12,'male'),('lily',15,'female')

向表中插入空值

隐式方式: 在列名表中省略该列的值

INSERT INTO departments (department_id, department_name [这里少两列])
VALUES(30, 'Purchasing');


显示方式: 在VALUES子句中指定空值

INSERT INTO departments VALUES(100, 'Finance', NULL, NULL);


从其它表中拷贝数据

INSERT 语句中加入子查询

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

不必书写VALUES 子句,子查询中的值列表应与INSERT 子句中的列名对应。

特点:


① 如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字

② 如果插入的数值超出了整型的范围,会报out of range异常,并且插入临界值

③ 如果不设置长度,会有默认的长度


长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用!

② 字段默认值

如果字段设计的时候赋予了默认值,要想默认值起作用,有如下两种方式(假设这里age字段默认值为18;):


① 插入时跳过使用默认值的字段

insert INTO p_user (name,sex) VALUES ('tom','male');


② 为该字段赋予值-default

insert INTO p_user (name,age,sex) VALUES ('tom',default,'male');



以上两种方式都能成功插入('tom',18,'male')


主键为ID-自增长时,插入时为该字段赋予值-null

insert INTO c_user VALUES (null,'male',18);


注意:该情况适用于id为 主键自增长的情况


③ 更新数据

修改单表的记录


语法:

update 表名
set 列=新值,列=新值,...
where 筛选条件;

修改多表的记录

sql92语法:

update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;

sql99语法:

update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;

如果需要回滚数据,需要保证在DML前,进行设置:SET AUTOCOMMIT = FALSE;

限制更新记录总数

update table_name set [field] = [value] [where 条件] [limit Num];

e.g:

update p_user_2 set age = 19 LIMIT 6;
-- 将前六名同学age统一改为19

多表修改实例:

UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;


④ 删除数据

单表的删除

delete from 表名 where 筛选条件

多表的删除

sql92语法:

delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;


sql99语法:

delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';


限制删除记录总数

delete from table_name [where 条件] [limit Num];


e.g:

delete from p_user_2  LIMIT 6;
-- 将前六名同学删除


join on级联删除多表数据

DELETE u,e
FROM users u
JOIN my_employees e ON u.`userid`=e.`Userid`
WHERE u.`userid`='Bbiri';



truncate table 清空表

语法:

truncate table 表名;
TRUNCATE TABLE boys ;


delete与truncate区别


delete 可以加where 条件,truncate不能加


truncate删除,效率高一点


假如要删除的表中有自增长列,如果用delete删除后,再插入数据,自增长列的值从断点开始。而truncate删除后,再插入数据,自增长列的值从1开始。


truncate删除没有返回值,delete删除有返回值


truncate删除不能回滚,delete删除可以回滚.

【6】数据库用户操作

① 用户操作

建立用户并分配权限

CREATE USER '用户名'@'%' IDENTIFIED BY '密码';
grant all privileges on *.* to 用户名@'%' identified by '密码' with grant option;
flush privileges;//刷新权限


修改用户

update mysql.user  set USER='xxx' WHERE USER='xxx';
flush privileges;//刷新权限

显示当前用户:

select user();
• 1

显示当前用户密码

select password('root');

查看用户权限

show grants for root@'localhost';
• 1


删除用户

delete form mysql.user where Host='xxxx' and User='xxxx';
flush privileges;//刷新权限



推荐使用drop删除用户

# 可以删除多个用户
drop user username [,username]
# e.g:
drop user 'jane' @ '%'
drop user 'jane' @ 'localhost'


不推荐通过delete进行用户删除,系统会有残留信息保留。而drop user命令会删除用户以及对应的权限,执行命令后你会发现mysql.user表和mysql.db表中的相应记录都消失了。

② 用户密码操作

可以通过多种方式来修改密码,使用 alter user 修改用户密码是MySQL官方推荐的方式。此外,也可以通过 set 语句修改密码。由于MySQL8中已经移除了PASSWORD()函数,因此不再使用update语句直接操作用户表修改密码。


① 修改当前用户密码

适用于root用户修改自己的密码,以及普通用户登录后修改自己的密码。

旧的写法如下:

# 修改当前用户密码为 123456  mysql5.7支持
set PASSWORD=PASSWORD('123456')


这里介绍两个推荐写法。

使用Alter user命令来修改当前用户密码

alter user user() identified by '123456'


使用set语句来修改当前用户密码

# 使用root用户登录MySQL后
set password='123456'

② 修改其他用户密码

使用alter语句修改普通用户密码

alter user 'jane'@ 'localhost' identified by '123456'


使用 set 命令来修改普通用户密码

set PASSWORD for 'jane' @ 'localhost' = '123456'


使用update语句修改普通用户密码(不推荐)

# MySQL5.5
update mysql.user set PASSWORD=PASSWORD('123456') WHERE USER='jane';
# MySQL5.7(不适用于MySQL8)
update mysql.user set authentication_string=PASSWORD('123456')
where User='jane' and Host='localhost';
flush privileges;


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
分布式计算 关系型数据库 MySQL
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型 图像处理 光通信 分布式计算 算法语言 信息技术 计算机应用
55 8
|
1月前
|
关系型数据库 MySQL
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
69 5
|
1月前
|
SQL DataWorks 关系型数据库
阿里云 DataWorks 正式支持 SelectDB & Apache Doris 数据源,实现 MySQL 整库实时同步
阿里云数据库 SelectDB 版是阿里云与飞轮科技联合基于 Apache Doris 内核打造的现代化数据仓库,支持大规模实时数据上的极速查询分析。通过实时、统一、弹性、开放的核心能力,能够为企业提供高性价比、简单易用、安全稳定、低成本的实时大数据分析支持。SelectDB 具备世界领先的实时分析能力,能够实现秒级的数据实时导入与同步,在宽表、复杂多表关联、高并发点查等不同场景下,提供超越一众国际知名的同类产品的优秀性能,多次登顶 ClickBench 全球数据库分析性能排行榜。
|
1月前
|
关系型数据库 MySQL
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
39 1
|
1月前
|
关系型数据库 MySQL 索引
MySQL的group by与count(), *字段使用问题
正确使用 `GROUP BY`和 `COUNT()`函数是进行数据聚合查询的基础。通过理解它们的用法和常见问题,可以有效避免查询错误和性能问题。无论是在单列分组、多列分组还是结合其他聚合函数的场景中,掌握这些技巧和注意事项都能大大提升数据查询和分析的效率。
115 0
|
2月前
|
存储 关系型数据库 MySQL
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取(pydicom 库使用)
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取(pydicom 库使用)
43 2
|
1月前
|
关系型数据库 MySQL Java
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
34 0
|
1月前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
35 0
|
13天前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
88 15
|
7天前
|
SQL 关系型数据库 MySQL
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。