Mysql命令汇总

丿蜗牛先生

已于 2022-08-28 23:14:07 修改

阅读量307

点赞数

分类专栏： mysql 文章标签： mysql 数据库

于 2022-08-28 22:24:40 首次发布

本文链接：https://blog.csdn.net/weixin_50092731/article/details/126575166

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL

一、概念

什么是数据库

数据库管理系统 (DataBase-ManagementSystem,DBMS) 由一个相互关联的数据的集合和一组用以访问这些数据的程序组成。这个数据集合通常称作数据库(database)

关系型数据库

关系数据库基于关系模型，使用一系列表来表达数据以及这些数据之间的关系。MySQL就是关系型数据库。关系模型已经成为当今主要的数据模型，它比之前的的网络模型和层次模型简化了编程者的工作。现在开始流行的NoSQL，泛指非关系型的数据库。

Mysql配置文件

Ubuntu18上通过apt安装的mysql配置文件及目录是/etc/mysql/mysql.conf.d，

windows上面是安装目录下的my.ini。如果需要改MySQL的一些设置就需要在配置文件里面去改

查看Mysql服务

Linux上，通过：

service mysql status	查看是否开启
service mysql start		开启
service mysql stop		关闭

Windows下通过任务管理器即可查看

注意

本文未按照Mysql标准来书写命令，若对此有要求则具体请参照mysql书写规范配合使用。

以下<> 尖括号内容为命令参数，参数含义可由其中英文意译得之。如：

<username> —— 用户名 <table_name> —— 表名 <field_name> —— 字段名 <constraint_name> —— 约束名

<table_alias> —— 表别名 <field_alias> —— 字段别名等。

同时每条命令后会接有使用例子，以便理解使用。

二、主要命令

1.连接数据库

本地连接

mysql -u <username> -p <password>
mysql -u admin -p qwe123

远程连接

mysql -h <ip> -p <端口号> -u <username> -p <password>
mysql -h127.0.0.1 -p3306  -u admin -p qwe123

2.操作数据库

查看数据库

show databases;

创建数据库

create database <database_name>;
create database test;

删除数据库

drop database <database_name>;
drop database test;

进入数据库

use <database_name>;
use test;

查看表所在当前数据库

select database();

3.操作数据表

创建数据表(create)

create table <tableName>{
	<field_name> <type>,
	<field_name> <type>,
	···
};
create table MyTable{
	id int,
	name varchar(20),		#varchar(20) 不定长  字符串数据  引号
	sex char(20)			#char(20) 定长  字符串数据  引号
};

查看数据表(show tables)

show tables;

删除数据表(drop)

drop table <tableName>;
drop table MyTable;

查看表结构(desc)

desc table <tableName>;
desc table MyTable;
show create table <tableName>;
show create table MyTable;

修改表名(alter rename)

alter table <tableName> rename <new_table_name>;
alter table MyTable rename MyNewTable;

4.操作数据表字段

添加字段(alter add)

# 单字段添加
alter table <tableName> add <field name> <type>;
alter table MyTable add age int;
# 多字段添加
alter table <tableName> add (<field_name> <type>, <field_name> <type>, ...);
alter table MyTable add (gender varchar(20), age int, size int);

删除字段(alter drop)

# 单字段删除
alter table <tableName> drop <field_name>;
alter table MyTable drop gender;
# 多字段删除
alter table <tableName> drop <field_name>, drop <field_name>, ..., drop <field_name>;
alter table  MyTable  drop size, drop age;

修改字段类型(alter modify)

alter table <tableName> modify <field_name> <new_type>;
alter table MyTable modify name char(20);

修改字段类型和名字(alter change)

alter table  <tableName> change <field_name> <new_field_name> <new_type>;
alter table MyTable change age ages char(12);

添加字段到指定位置

# 将字段添加到指定字段之后
alter table <tableName> add <new_field_name> <new_type> after <field_name>
alter table MyTable add email varchar(20) after phone

5.操作数据表数据

添加数据(insert into )

# 多个字段添加单个数据
insert into <tableName> (<field_name>, <field_name>, ...) value (<data>, <data>, ...) ;
insert into MyTable (id, name, age, gender) value (1, '猪头', '18'， '未知');

# 多个字段添加多个数据
insert into <tableName> (<field_name>, <field_name>, ...) values (<data>, <data>, ...) (<data>, <data>, ...) ...;
insert into MyTable (id, name, age, gender) values (1, '猪头', '18'， '未知') (2, '大猪头', '19'， '男');

# 不指明字段的时候  默认全字段加数据(插入对应字段数目的数据)
insert into <tableName> value (<field_name>, <field_name>, ...);
insert into MyTable value (1, '猪头', '18', '未知');

删除数据(delete from)

delete from <tableName> where <field_name> = <target_data>;
delete from MyTable where id = 3;

修改数据(update … set)

update <tableName> set <field_name> = <want_data> where <target_field_name> = <target_data>;
update MyTable set age = '13' where id = 2;

查看数据(select … from)

select * from <tableName>;
select * from MyTable;

6.对字段添加约束（建表后）

非空约束(modify)

# 建立非空约束
alter table <tableName> modify <field_name> <type> not null;
alter table MyTable modify name char(20) not null;

# 删除非空约束
alter table <tableName> modify <field_name> <type> null;
alter table MyTable modify name char(20) null;

唯一约束(add)

# 建立唯一约束
alter table <tableName> add unique(<field_name>);
alter table MyTable add unique(id);

# 删除唯一约束
alter table <tableName> drop index <field_name>;
alter table MyTable drop index id;

主键约束(add)

注意，一张表只允许出现一个主键约束

# 建立主键约束
alter table <tableName> add primary key(<field_name>);
alter table MyTable add primary key(id);

# 删除主键约束
alter table <tableName> drop primary key;
alter table MyTable drop primary key;

自增约束(change)

关键字为 AUTO_INCREMENT 自动编号

注意必须与主键组合使用，默认情况下起始值为1，每次的增量为1

# 建立自增约束
alter table <tableName> change <field_name> <field_name> <type> not null auto_increment;
alter table MyTable change id id int not null auto_increment;

# 删除自增约束(即删除auto_increment关键字即可)
alter table <tableName> change <field_name> <field_name> <type> not null;
alter table MyTable change id id int not null;

默认约束(set)

# 建立默认约束
alter table <tableName> alter <field_name> set default <default_data>;
alter table MyTable alter gender set default "未知";

# 删除默认约束
alter table <tableName> modify <field_name> <type>;
alter table MyTable alter gender varchar(20);

7.对字段添加约束（建表前)

create table <tableName>{
	<field_name> <type> <constraint_name> <constraint_name> ...,
	<field_name> <type> <constraint_name> <constraint_name> ...,
	<field_name> <type> <constraint_name> <constraint_name> ...,
	...
}auto_increment = <initial_val>, charset = <initial_charset>;

create table MyTable{
	id int primary key auto_increment,
	name varchar(20) not null,
	age int default 18,
	sex varchar(20) default '男',
	phone char(11) unique key
}auto_increment = 0, charset = UTF8;

8.表关系(即添加相关外键约束)

表关系较为复杂，以下将用例子表述，以下注释为python写法，用以解释说明，直接复制会报错。

外键约束

之前讲到的约束，都是表内约束自身，而外键约束约束其他表。

创建条件

数据表的存储引擎只能为InnoDB
外键列和参照列数据类型一致
外键必须关联到键上面去
外键名在数据库要唯一

查看所有外键

select constraint_name from information_schema.key_column_usage where constraint_schema = 'mydb' and constraint_name != 'primary'

添加外键的格式

alter table <tableName> add foreign key (<field_name>) references <other_table_name>(<other_field>) on delete <> on update <>

on delete 及 on update 根据需要可省略

ondelete 及 on update 的添加选项

CASCADE：删除包含与已删除键值有参照关系的所有记录（级联删除）
SET NULL：修改包含与已删除键值有参照关系的所有记录，使用NULL值替换（只能用于已标记为NOT NULL的字段）
RESTRICT：拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)
NO ACTION：啥也不做

一对多关系

一对多与多对一是一个概念，指的是一个实体的某个数据与另外一个实体的多个数据有关联关系。

学校中一个学院可以有很多的学生，而一个学生只属于某一个学院（通常情况下），学院与学生之间的关系就是一对多的关系，通过外键关联来实现这种关系

# 一对多的关系  一个学院对多个学生
# 学院表
create table department (
    d_id int primary key auto_increment,	# 学院id 设置主键约束，自增约束
    d_name varchar(20) not null			    # 学院name 设置非空约束
);

#学生表
create table students (
    s_id int primary key auto_increment,	# 学生id 设置主键约束，自增约束
    s_name varchar(20) not null,			# 学生name 设置非空约束
    department_id int,						# 该学生的学院id
    # 设置外键关联，此处fk_ds为约束名，意为foreignkey_departmentstudent，可自定义，on delete set指定删除方式。
    constraint fk_ds foreign key (department_id) references  department (d_id)  on delete set null	
);

建立方法

<field_from_this_table> <type>,
constraint <foreignkey_name> foreign key (<field_from_this_table>) references <other_table> （<field_from_other_table>) on delete <dl_way>

一对一关系

指的是一个实体的某个数据与另外一个实体的一个数据有关联关系。

学生表中有学号、姓名、学院，但学生还有些比如电话，家庭住址等比较私密的信息，这些信息不会放在学生表当中，会新建一个学生的详细信息表来存放。这时的学生表和学生的详细信息表两者的关系就是一对一的关系，因为一个学生只有一条详细信息

#学生表
create table students (
    s_id int primary key auto_increment,	# 学生id 设置主键约束，自增约束
    s_name varchar(20) not null,			# 学生name 设置非空约束
    department_id int,						# 创建该学生的学院id，用于与学院做关联。
    # 设置外键关联，此处fk_ds为约束名，意为foreignkey_departmentstudent，可自定义，on delete set指定删除方式。
    constraint fk_ds foreign key (department_id) references  department (d_id)  on delete set null	
);

#学生详情表
create table stu_detail (
    sd_id int primary key auto_increment,
    sd_age int,
    sd_sex char(20) default 'nan',
    sd_tel char(11) unique key,
    student_id int unique key,		# 创建该学生详情表对应的学生id，设定唯一约束
    # 设置外键关联，此处fk_ss为约束名，以为foreignkey_studentstudentDetail，可自定义，on delete set指定删除方式。此处为级联删除
    constraint fk_ss foreign key (student_id) references  students (s_id)  on delete set cascade
);

建立方法

<field_from_this_table> <type> unique key,    # 设定唯一约束
constraint <foreignkey_name> foreign key (<field_from_this_table>) references <other_table> （<field_from_other_table>) on delete <dl_way>

多对多关系

一个实体的数据对应另外一个实体的多个数据，另外实体的数据也同样对应当前实体的多个数据。

学生要报名选修课，一个学生可以报名多门课程，一个课程有很多的学生报名，那么学生表和课程表两者就形成了多对多关系。对于多对多关系，需要创建第三张关系表，关系表中通过外键加主键的形式实现这种关系。

这里需要注意的是，在多对多关系中，我们常用中间表来表示。

#学生表
create table students (
    s_id int primary key auto_increment,	# 学生id 设置主键约束，自增约束
    s_name varchar(20) not null,			# 学生name 设置非空约束
    department_id int,						# 创建该学生的学院id，用于与学院做关联。
    # 设置外键关联，此处fk_ds为约束名，意为foreignkey_departmentstudent，可自定义，on delete set指定删除方式。
    constraint fk_ds foreign key (department_id) references  department (d_id)  on delete set null	
);

# 课程表
create table course (
    c_id int primary key auto_increment,
    c_name varchar(20) not null
);

#中间表
create table stu_course (
    s_id int,
    c_id int,
    primary key(s_id ,c_id)			# 建立联合主键
    constraint fk_s foreign key (s_id) references  students (s_id),
    constraint fk_c foreign key (c_id) references  course (c_id)
);

建立方法

# 创建中间表
create table <tableName>{
	<field_name_1> type,
	<field_name_2> type,
	primary key(<field_name_1>, <field_name_2>),	# 建立联合主键
	constraint <foreignkey_name1> foreign key (<field_name_1>) references <other_table1>(<field1_from_other_table>),
	constraint <foreignkey_name2> foreign key (<field_name_2>) references <other_table2>(<field2_from_other_table>)
};

删除外键约束

alter table <tableName> drop foreign key <foreign_key_name>;
alter table students drop foreign key fk_ds;

9.事务

含义

数据库事务(transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

简单来说：几个操作是绑定在一起的，要么都做完，要么一个都不做。

完成一个操作的所有步骤序列

# 开启事务
begin

# 回滚
rollback

# 结束事务
commit

在这里插入图片描述

事务只针对数据处理做操作，不对表做操作

10.查询

基本查询

一般来说，查询所有字段是最耗时长的，所以今后查询数据尽可能按需所取。查询字段尽可能查询部分字段，不需要的字段就要不查询出来。

# * 表示所有字段
# 查询该表所有字段
select * from <tableName>;
select * from MyTable;

# 根据字段查询数据
select <field_name> from <tableName>;
select age from MyTable;

条件查询

查询条件可以是大于等于不等于（>,=,<>）等等，也可以是更加复杂的判断都是可以的。

# where
select <field_name> from <tableName> where <target_field_name> = <target_val>;
select name from MyTable where id = 2;

# and or not(除了等号同样可用大于小于号)
select <field_name> from <tableName> where <target_field_name1> = <target_val1> and <target_field_name2> = <target_val2>;
select name from MyTable where id = 2 and age = '18';

select <field_name> from <tableName> where <target_field_name1> = <target_val1> or <target_field_name2> = <target_val2>;
select name from MyTable where id = 1 or age = '18';

select <field_name> from <tableName> where <target_field_name1> > <target_val1> and not <target_field_name2> = <target_val2>;
select name from MyTable where id > 3 and not age = '18';

取别名

如果列名或者表名太长，可以给它们取一个别名,可以方便取使用。实际上 as 可以省略，但是为了方便阅读，我们通常会加上。

select <field_name> as <field_alias> from <tableName> as <table_alias>; 
select name as a from MyTable as mt;

在这里插入图片描述

模糊查询

# 关键字  like
select * from <tableName> where <field_name> like <like_data>;
select * from MyTable where name like '%o%';

多表查询

内连接

以下直接用例子来表述

在这里插入图片描述

把两张表想象成X轴和Y轴，轴上的每个点，就是两个表里面的每行数的集合。

SELECT  *  FROM  `department`  INNER JOIN  `students`;
SELECT  *  FROM  `department` ,  `students`;
SELECT  *  FROM  `department`  CROSS JOIN  `students`;
SELECT  *  FROM   `department`  JOIN  `students`;

以上这个四个SQL的结果都一样，都是笛卡尔积，也叫无条件连接/交叉连接等。注意在mysql中，INNER JOIN 和 CROSS JOIN 是一样的。

SELECT * FROM `department` INNER JOIN `students` ON `department`.`d_id` = `students`.`department_id`;
SELECT * FROM `department` as p , `students` as s WHERE  p.`d_id`=s.`department_id`;
SELECT * FROM `department` as p CROSS JOIN `students` as s WHERE p.`d_id` = s.`department_id`;

以上三条为使用别名的查询方法，同样结果一样

左连接

A表 LEFT JOIN B表会以左边的表为主，展示左边表的所有数据，展示右边表中符合ON子句中条件的数据，没有为空

SELECT * FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`student_id`;

右连接

右连接和左连接类似，只是作用相反。

SELECT * FROM `students` s RIGHT JOIN `student_details` sd ON s.`s_id`=sd.`student_id`;

全连接

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。

SELECT s.`name`,c.`name` FROM `students` AS s LEFT JOIN `select` AS se ON s.`s_id`=se.`stu_id` LEFT JOIN `course` c ON c.`id`=se.`coures_id`
UNION SELECT s.`name`,c.`name` FROM `students` AS s LEFT JOIN `select` AS se ON s.`s_id`=se.`stu_id` LEFT JOIN `course` c ON c.`id`=se.`coures_id`

子表查询

在一个SQL语句中出现两个SQL语句，就是子表查询，下面这个子表查询是在JOIN这个地方建立查询。

SELECT
	s.`name`,
	e.`name`
FROM
	`students` AS s 
LEFT JOIN(
		SELECT
			se.`stu_id`,
			c.`name`
		FROM
			`select` AS se
		JOIN `course` c ON se.`coures_id` = c.`id`
	) AS e ON s.`s_id`=e.`stu_id`;

子查询不但可以放在JOIN 哪里，也可以放在 WHERE 后面

SELECT
	*
FROM
	`students` AS s
WHERE
	s.`dept_id` = (
		SELECT
			`id`
		FROM
			`department` AS d
		WHERE
			d.`name` = '外国语学院’
	);

查询结果排序

对查询出来的结果进行排序，ASC升序(默认) DESC降序。

SELECT * FROM `student` AS s JOIN `student_details` AS sd ON s.`s_id`=sd.`stu_id` ORDER BY s.`s_id` DESC;

查询结果行数限制

对查询出来的结果限制显示的行数。

SELECT * FROM `students` s JOIN `student_details` sd ON s.`s_id`=sd.`stu_id` ORDER BY  s.`s_id` DESC LIMIT 3;

分组查询

分组是个常见的操作，常用于分组统计，使用GROUP BY后，会按照GROUP BY后面的字段进行分组，且必须是明确的字段，不能是* ，因此SELECT后面也不能是* .其次可以使用 HAVING 可以对分组之后的结果进行筛选，注意：HAVING 后的字段必须是SELECT后出现过的

SELECT d.`d_id`,d.`name`,COUNT(*) FROM `department` AS d LEFT JOIN `students` AS s ON d.`d_id`=s.`dept_id` GROUP BY d.`d_id`,d.`name`;
SELECT d.`d_id`,d.`name`,COUNT(*) FROM `department` AS d LEFT JOIN `students` AS s ON d.`d_id`=s.`dept_id` GROUP BY d.`d_id`,d.`name` HAVING COUNT(*)>1;

11.部分mysql函数

字段处理

IFNULL()

SELECT s.`name`,IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;  #处理NULL

DISTINCT

SELECT DISTINCT   `name`   FROM  `students` ; # 字段去重

字符串截取

字符串截取，left是从左边开始截取，right是从右边开始截取,SUBSTRING可以指定截取范围。

LEFT()

SELECT LEFT(s.`name`,2),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

RIGHT()

SELECT RIGHT(s.`name`,2),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

SUBSTRING()

SELECT SUBSTRING(s.`name`,2,5),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

字符串拼接

CONCAT()

SELECT CONCAT(s.`name`,sd.`id_card`) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

类型转换

CAST()

SELECT CAST(sd.`id_card` AS CHAR) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

CONVERT()

SELECT CONVERT(s.`dept_id`,SIGNED) FROM `students` s ;

时间函数

DAY()

SELECT DAY('2017-08-18')-DAY('2017-08-01’);

NOW()

SELECT NOW();

ROM students s LEFT JOIN student_details sd ON s.s_id=sd.stu_id;


##### SUBSTRING()

```mysql
SELECT SUBSTRING(s.`name`,2,5),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

字符串拼接

CONCAT()

SELECT CONCAT(s.`name`,sd.`id_card`) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

类型转换

CAST()

SELECT CAST(sd.`id_card` AS CHAR) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

CONVERT()

SELECT CONVERT(s.`dept_id`,SIGNED) FROM `students` s ;

时间函数

DAY()

SELECT DAY('2017-08-18')-DAY('2017-08-01’);

NOW()

SELECT NOW();

MySQL的函数还有很多，许多也和我们平常用的一样，比如ABS,MAX,MIN,ROUND,AVG,SUM等等，用法也是一样的，如果有需求，直接查下资料就可以。

丿蜗牛先生

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
Mysql命令汇总

本文含概详尽的mysql命令，同时辅以使用例子，对于光看命令不会使用的同学较为友好，同时含有除命令外的相关概念，将有利于对mysql的理解。
复制链接

扫一扫

专栏目录