mysql

仅供参考，请以实操为准
跳转请使用Ctrl + f
有任何问题可以在评论区中留言

一、mysql登录与退出

1.1 登录DOS命令①

mysql -h localhost -u root -p

查看代码测试

1.2 登录DOS命令②（-p和密码之间没有空格）

mysql -h 127.0.0.1 -u root -p123456

查看代码测试

1.3 使用MySQL 8.0 Command Line Client登录

查看测试

1.4 退出方法① 使用exit命令

exit

查看代码测试

1.5 退出方法② 使用 \q命令

\q

查看代码测试

1.6 退出方法③ 使用quit命令

quit

查看代码测试

二、创建与显示数据库

2.1 创建数据库

-- create database 数据库名;
create database stu;

查看代码测试

2.2 显示数据库

show databases;
-- 或
show schemas;

查看代码测试

三、创建与查看数据表

3.1 锁定数据库（需要锁定数据库才能创建数据表）

-- use 数据库名;
use stu;

查看代码测试

3.2 锁定之后创建数据表

-- create table 表名(字段1 数据类型, 字段n 数据类型);
create table ss(id int, name varchar(8));

查看代码测试

3.3 查看数据表（显示当前数据库的表）

show tables;

查看代码测试

四、数据表的增删改查

4.1 修改数据表的名称

-- alter table 原表名 rename 现表名;
alter table ss rename s1;

查看代码测试

4.2 查看数据表的结构

-- describe 数据表名;
describe s1;

查看代码测试

4.3 修改表的字段为主键

-- alter table 表名 modify 某个字段 数据类型 primary key;
alter table s1 modify id int primary key;

查看代码测试

4.4 为数据表增加字段

-- alter table 表名 add 字段 数据类型;
alter table s1 add class char(10);

查看代码测试

4.5 删除数据表字段

-- alter table 表名 drop 字段名;
alter table s1 drop class;

查看代码测试

4.6 删除数据表

-- drop table 表名;
drop table s1;

查看代码测试

4.7 查看数据库信息

\s

查看代码测试

4.8 删除数据库（加 if exists 避免数据库不存在报错）

-- drop database/schema [if exists] 数据库名;
drop database if exists s1;
-- 或
drop schema if exists s1;

查看代码测试

4.9 数据库切换

-- \u 数据库名
\u mysql

查看代码测试

五、数据库操作进阶

5.1 避免当数据库存在时创建报错：在数据库名前加上 if not exists

-- create database/schema if not exists 数据库名;
create database if not exists mysql;

查看代码测试

5.2 创建数据库也可以使用schema代替database

create schema s2;

查看代码测试

5.3 显示所有字符集、字符序

show charset;
-- 以下为更完整的写法
show character set;

查看代码测试

5.4 创建数据库时指定字符集和字符序

-- create database/schema [if not exists] 数据库名 [default] character set 字符集名 [default] collate 字符序名; 【】表示可以省略
create database if not exists s3 default character set gbk default collate gbk_chinese_ci;
-- 或
create schema if not exists s4 default character set utf8 default collate utf8_general_ci;

查看代码测试

5.5 查看数据库字符集

-- show create database 数据库名称;
show create database s3;

查看代码测试

5.6 更改数据库字符集

-- alter database/schema 数据库名 [default] character set 字符集名;
alter database s4 character set gbk;

查看代码测试

5.7 更改数据库字符序

-- alter database/schema 数据库名 [default] collate 字符序名称;
alter schema s3 default collate utf8_general_ci;

查看代码测试

六、数据表操作进阶

6.1 完整创建数据表

-- create table [if not exists] 数据表名(字段名 数据类型 [ not null | null ] [ default 默认值 ] [ auto_increment ] [ unique [ key ] | [ primary ] key ][ comment ‘string’ ] [ reference_definition ]);
-- date 字段类型值的格式为：YYYY-MM-DD，例如：2022-10-01
create table if not exists tTable(
  id int not null auto_increment unique primary key comment 'id',
  name varchar(10) default null comment '姓名',
  tDate date default '2022-10-01'
);

查看代码测试

6.2 查看数据表结构

-- show create table 数据表名;
show create table tTable;

查看代码测试

6.3 修改字段名

-- alter table 表名 change 原字段名 新字段名 数据类型;
alter table tTable change name stuName varchar(10);

查看代码测试

6.4 修改数据类型

-- alter table 表名 modify 字段名 数据类型;
alter table tTable modify stuName char(20);

查看代码测试

6.5 添加字段并设置为第一字段

-- alter table 表名 add 字段名 数据类型 first;
alter table tTable add class varchar(10) first;

查看代码测试

6.6 修改字段1到字段2之后

-- alter table 表名 modify 字段名1 数据类型 after 字段名2;
alter table tTable modify class varchar(10) after id;

查看代码测试

6.7 添加新字段到字段2之后

-- alter table 表名 add 字段名1 数据类型 after 字段名2;
alter table tTable add a float after class;

查看代码测试

6.8 创建约束

查看参考教程

创建表class

create table class(
  classid char(8) primary key comment '主键，班级名称',
  className varchar(20) not null comment '非空，班级名称',
  classLocation varchar(30) comment '班级地址'
);

创建表stud

create table stud(
  stuNo int primary key comment '主键，学号',
  stuName varchar(10) not null comment '非空，姓名',
  classid char(8) comment '外键class(classid)，班级编号',
  score float default 0 comment '成绩，取值在0~100之间，默认值为0',
  foreign key(classid) references class(classid),
  check(score >= 0 and score <=100)
);

6.9 追加约束

• 表级约束
• 列级约束
• 特殊约束

-- alter table 表名 add constraint 约束名 约束类型(字段);
-- alter table 表名 add [constraint 约束名] 约束类型(字段名) [references 主表(关联列字段名)];
alter table stud add constraint PK_sno unique(stuNo);

-- alter table 表名 modify column 字段名 字段类型 新约束;
alter table stud modify column classid char(8) default 'a';

-- alter table 表名 change 原字段名 新字段名(原字段名) 数据类型 新约束;
alter table tTable change[ column] name name varchar(10) not null;

6.10 删除约束

• 表级约束
• 列级约束
• 其他方法

-- alter table 表名 drop 约束 约束名;
alter table stud drop foreign key fk_id;

-- alter table 表名 modify column 字段名 字段类型;
alter table stud modify column classid char(8);

-- alter table 表名 alter column 列名 drop 约束名;
alter table tTable alter column id drop default;

6.11 添加数据

• 为所有字段添加数据
• 为部分字段添加数据

• insert...values

方法一：不写字段名（数据的类型要和表中一致，顺序也要一致）

-- insert into values(字段1的值,字段2的值,...,表中所有字段的最后一个字段的值)
insert into values(1,'a','1',60.8);

方法二：写上所有字段名 （可以不和表中字段顺序一致，但需要和列出的字段的类型一致，顺序也要一致）

-- insert into 表名(字段1[,字段2,...,字段n]) values(字段1的值[,字段2的值,...,字段n的值])
insert into stud(stuNo,stuName,classid,score) values(1,'a','1',60.8);

使用方法一添加多个字段值

insert into values(1,'a','1',60.8),(2,'b','2',60.9);

使用方法二添加多个字段值

-- insert into 表名(字段1[,字段2,...,字段n]) values(字段1的值[,字段2的值,...,字段n的值])
insert into stud(stuNo,stuName,classid,score) values(1,'a','1',60.8),(2,'b','2',60.9);

• insert...set

写上所有字段名=字段值

-- insert into 表名 set 字段名=字段值;
insert into stud set stuNo=1,stuName='a',classid='1',score=60.8;

写上部分字段与其值对应

• insert...values

-- insert into 表名(字段1[,字段2,...,字段n]) values(字段1的值[,字段2的值,...,字段n的值])
insert into stud(stuNo) values(1);

添加多个字段值

-- insert into 表名(字段名1[,...,字段名n]) values(第1行数据1[,...,数据n])[,(第n行数据1,...,数据n)]
insert into stud(stuNo,stuName) values(1,'a'),(2,'a');

• insert...set

-- insert into 表名 set 字段名=字段值;
insert into stud set stuNo=1,stuName='a';

6.12 查询数据（可以使用destinct过滤重复数据）

• 查询所有字段
• 查询部分字段
• 条件查询
• 函数查询

方法一：使用通配符(*)

select * from stud;

方法二：写上所有字段名

-- select[ distinct] 字段名1[,...,字段名n] from 表名;
select stuNo,stuName,classid,score from stud;

-- select 字段名1[,...,字段名n] from 表名;
select stuNo from stud;

• limit 与 offset 限制

-- select 字段名/* from 表名 limit 开始(索引默认从0开始), 个数;
select * from stud limit 1, 2;

-- 可以使用 offset 代替 第一个参数
select * from stud limit 2 offset 1;

• 条件语句的单条件查询，和多条件查询

名称	条件
and	x and y => x(true) and y(true) <=> true x(false) and y(true) <=> false
or	x or y => x(true) or y(true) <=> true x(false) or y(true) <=> true x(false) or y(false) <=> false
xor	x xor y => x(true) xor y(true) <=> false x(false) xor y(true) <=> true x(false) xor y(false) <=> false

-- select 字段名/* from 表名[ where 条件表达式1 and/or/xor 条件表达式2];
select * from a where id=2 and name='aa';

• 模糊匹配查询 like 与 操作符

操作符名称	释义
%	通配符，匹配所有
_	下划线表示匹配单个字符（两个下划线等于匹配两个字符）
\	转义符（如果内容包含操作符，则需要使用转义字符）

-- select 字段名/* from 表名 where 字段名[ not] like[ binary] '[操作符]模糊字段值[操作符]';
select * from stud where name like '_i%';
select * from stud where name not like '%k__';
-- 添加binary区分大小写
select * from stud where name like binary 'Y';
-- 转义
select * from stud where name like '%\%%';

• 范围查询 between 用于查 date 日期类型

-- select 字段名/* from 表名 where 字段名 between 开始值 and 结束值;
select * from stud where id between 2 and 4;
select * from stud where time where between '2022-11-07' and '2022-11-09';

• 空/非空值查询 [not] null

-- select 字段名/* from 表名 where 字段名 is[ not] null;
select * from stud where id is not null;

• 排序 order（默认升序 asc）

-- select 字段名/* from 表名 order by 字段名1[,字段名2..字段名n][ desc/asc];
select * from stud order by id desc; -- 降序

• 替换查询 case when.. then..[ else..] end

  -- select 字段名1[,字段名n], (case when 条件表达式1 then 替换值[ when 条件表达式n then 替换值n][ else 条件表达式x] end) as 别名 from 表名;
  select id, (case when c>100 then 'a' when c>200 then 'b' else 'c' end) as c_cout from 表名;

• 运算符 +-*/%

  -- select 字段名运算符字段名[ as 别名] from 表名
  select price*num as p from 表名

• 分组函数 group_concat()、group by 和 统计查询 with rollup

-- select[ 字段名/*,] group_concat(字段名/*) from 表名 group by 字段名[, 字段名2...字段名n];
select gp,group_concat(name) from q_gp group by gp;  -- 单字段查询
select gp,id,group_concat(name) from q_gp group by gp,id;  -- 多字段查询

-- select[ 字段名/*,] group_concat(字段名/*) from 表名 group by 字段名[, 字段名2...字段名n] with rollup;
select gp,group_concat(name) from q_gp group by gp with rollup;

• 过滤分组 hiving

-- select 字段名x[,字段名1..字段名n] from 表名[ group by 字段名] having [聚合函数名(字段名x)]/字段名x 条件表达式
select gp from q_gp having gp>2;
select id from q_gp having avg(id)>=2;

• 聚合函数

方法	释义
max()	最大值
min()	最小值
count()	计数
sum()	列求和
avg()	列平均

-- select 聚合函数名(字段名)[, 聚合函数名(字段名2), 字段名n] from 表名[ 查询条件][ where 条件表达式];
select gp,count(name) from q_gp group by gp;

6.13 更新数据

-- update 表名 set 字段名1=字段值1[,...,字段名n=字段值n][ where 条件表达式1[,...,条件表达式n]];
update stud set stuNo=10,stuName='cc' where stuNo=1,stuName='a';
update stud set score=100 where stuNo<3;

-- 不加条件就更改表中所有字段名匹配的数据
update stud set score=90;

6.14 删除数据

• 使用 delete 删除

  -- delete from 表名[ where 条件表达式];（不写条件表达式就表示删除该表中所有数据；数据删除：行）
  delete from stud where stuNo=1;

• 使用 truncate 删除表中所有数据

  -- truncate[ table] 表名
  truncate stud;

6.15 复制数据表（里面没有数据）

-- create table 新表名 like 旧表名;
create table newTable like stud;

6.16 复制表中数据（不写条件表达式即为新表插入所有数据）

-- insert into 新表名 select * from 旧表名[ where 条件表达式];
insert into newTable select * from stud;
insert into newTable select * from stud where id=2;

6.17 设置别名

-- as 别名
select id as idd,name from ss as aaa;

6.18 删除数据表（存在外键）

1. 删除外键，再删主表
2. 删除外键关联表，再删主表（不推荐）

七、多表操作

7.0 （补充）合并查询 union（表2字段要和表1相同，不足的字段可以用数字充当字段）

-- select */字段1[,字段n] from 表1 union[ all] select */字段1[,字段n][,1,2,..,x][ from 表2];
select id,name,age,sex from tb_student_info union select id,name,1,2 from tb_course;
select id,name from tb_student_info union select 1,2;

7.1 交叉连接（笛卡尔积）

-- select */字段1[,字段n] from 表1[ as][ 别名] cross join 表2[ as][ 别名][ where 条件表达式];
select * from tb_course cross join tb_student_info;

7.2 内连接

-- select */字段1[,字段n] from 表1[ as][ 别名][ inner] join 表2[ as][ 别名][ on 条件表达式];
select s.name,c.course_name from tb_students_info s join tb_course c on s.course_id = c.id;

7.3 外连接

7.3.1 左连接

-- select */字段1[,字段n] from 主表[ as][ 别名] left[ outer] join 表2[ as][ 别名][ on 条件表达式];
select s.name,c.course_name from tb_students_info s left join tb_course c on s.course_id = c.id;

7.3.2 右连接

-- select */字段1[,字段n] from 表2[ as][ 别名] right[ outer] join 主表[ as][ 别名][ on 条件表达式];
select s.name,c.course_name from tb_course c right join tb_students_info s on s.course_id = c.id;

7.4 子查询

in & not in
= & <>

-- select */字段1[,字段n] from 主表[ as][ 别名]([ not] in)/(=/<>) (select 子表字段1[,子表字段n] from 子表名 where 条件表达式);
select name from tb_students_info where course_id in (select id from tb_course where course_name = 'java');
select name from tb_students_info where course_id not in (select id from tb_course where course_name = 'java');
select name from tb_students_info where course_id = (select id from tb_course where course_name = 'java');
select name from tb_students_info where course_id <> (select id from tb_course where course_name = 'java');