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 _';

⑥ 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;

测试如下：

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

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

有两种方式，第一修改表字段属性；第二直接追加。

第一种方式示例如下：

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

② 增加复合约束

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中库/表/字段/主键/用户操作示例与详解