MySQL基本操作

执行与取消

每条SQL语句都以;为结束，如：

show databases;

放弃语句

\c表示取消执行，常用于输入错了SQL语句，但又已经写的太长。如：

show databases \c            //不需要分号`;`

数据库管理

查看库列表

show databases;

创建新库

create database db_name;

删除库

drop database db_name;

为了避免库不存在时导致的执行错误，最好使用以下语句：

drop database if exists db_name;

查看当前使用库

select database();

切换库

use db_name;

导入语句

创建sql文件，编辑：

create database db_name;
show databases;

• 连接前导入

mysql -u root -p < sql文件地址

• 连接后导入

source sql文件地址;

数据表管理

新建表

create table class (
id int primary key AUTO_INCREMENT,
cname varchar(30) NOT NULL,
description varchar(100) default NULL)
charset utf8;

查看当前库中的表

show tables;

删除表

drop table table_name;

查看表结构

desc table_name;

插入数据

insert into class (cname, description) values ('nodejs', '这是nodejs教程');
insert into class (cname) values ('springboot');      //description 为默认值NULL

获取数据

select * from class;

清空表中数据

truncate table table_name;

• truncate只是删除行数据，表结构、索引、约束等保持不变
• drop table table_name 完全清除数据表
• delete from table_name where xxx = xxx 在truncate的基础上保留自增计数值

表间复制

复制表结构

create new_table like old_table;

插入A表数据到B表

insert into B select * from A;       //A、B结构一致，复制所有数据
insert into B (field_name) select field_name from A;      //只复制字段field_name

复制表时同时复制数据

create table new_table select * from old_table;            //复制所以字段
create table new_table select field_name from old_table;            //复制某一个或某些字段，此时新表结构只存在复制过去的字段
create table new_table (new_field varchar(30)) select old_field as new_field from old_table;      //复制字段时起别名，注意create new_table中字段的结构要有定义，且与被复制字段的结构一致

复制表结构时，主键、索引、约束等都会进行复制（不包括外键）
复制表数据时，主键、索引、约束等属性不会得到保留
需要复制结构与数据完全一样的表，需要先create like 旧表，或者通过show create table 旧表，将表名改为新表，建立新表后，进行insert into操作

临时表

新建临时表

create temporary table new_table select * from old_table;

临时表无法使用show tables查看到，但可正常使用desc与select进行表结构与数据查看，且表名不能重复
临时表可与实体表同名，且优先级高于实体表（可创建同名的，有数据的实体表与没有数据的临时表作比较）

删除临时表

drop temporary table if exists temp_table_name;

临时表更多的时候是被缓存方案给代替，如果考虑环境简单不设置缓存，临时表为一种次要替代方案

查询数据

指定查询字段

select field1, field2 from table_name;

条件筛选

select field1, field2 from table_name where field1 = result1;      //查询filed1字段完全等于result1的行数据，保留field1、field2字段
select field1, field2 from table_name where field1 like '%keyword%';  //查询包含关键词keyword的数据
select field1, field2 from table_name where field1 not like '%keyword%';  //查询不包含关键词keyword的数据

select distinct field1 from table_name;            //去重查询
select field1 from table_name where field1 <not> between result1 and result2;      //不带not：区间查询；带not：区外查询
select field1 from table_name where field1 <not> in (result1, result2);            //不带not：离散值查询；带not：非离散值查询

select field1, field2 from table_name where field1 = result1 and field2 = result2;            //并查询
select field1, field2 from table_name where field1 = result1 or field2 = result2;            //或查询
select field1 from table_name where field1 != result1;            //非查询

select field1 from table_name where field1 is <not> null;            //null查询，非null查询
select ifnull(field1, new_data) from table_name;                 //field1为null的显示new_data值

select field1 from table_name order by field1 desc;            //由大到小排序
select field1 from table_name order by field1 asc;            //由小到大排序

select field1 from table_name order by field1 asc limit 1;            //field1最小的一条数据
select field1 from table_name order by field1 asc limit 0,2;            //field1最小的两条数据，limit从0开始，此处偏移2行数据

select field1 from table_name order by rand() limit 1;      //随机查询一条数据

select concat(field1, field2) as 'new_name' from table_name;            //如果有一列为NULL，则合并的结果也为NULL

%keyword% 包含关键词keyword
keyword% 以关键词keyword开头
%keyword 以关键词keyword结尾

更新数据

更改数据

update table_name set field1 = new_data where field2 = data;
update table_name set field1 = field1 + new_data where field2 = data;      //自增，如age = age + 1，字符串不能加，要使用concat(field, data)

删除数据

delete from table_name where field1 = data;

添加数据

insert into table_name (field1, field2) values (data1, data2);
insert into table_name set field1 = data1, field2 = data2;
insert into table_name (field1, field2) values (data1, data2), (data3, data4);            //一次添加多条

表维护

修改字段

ALTER TABLE table_name MODIFY field1 varchar(30) not null;            //修改字符类型
ALTER TABLE table_name CHANGE field1 new_field varchar(30) not null;                  //修改字符名，新字段要带有字段定义描述

新增字段

ALTER TABLE table_name ADD field1 not null;            //新增的字段定义非空时，如果为整形，默认填充0；如果是字符，默认填充空字符串
ALTER TABLE table_name ADD PRIMARY KEY(field1);            //新增主键约束，要求表中无数据。表中如果存在数据，则会新增失败
ALTER TABLE table_name MODIFY field1 int not null AUTO_INCREMENT ,add PRIMARY key(field1);            //自增与主键约束一起加

删除字段

ALTER TABLE table_name DROP field1;                  //主键名已知时，也可删除
ALTER TABLE table_name DROP PRIMARY KEY;            //删除主键约束，并不是删除主键