mysql命令合集（一）

常用指令合集

# 连接mysql
# 命令格式
mysql -u <username> -p[passwd]
# 第一种格式
mysql -u root -p123456
# 第二种格式
mysql -u root -p

# 退出连接
exit;

# 展示当前mysql中的全部数据库
show databases;

# 选择某一个数据库
# 命令格式
use <databasename>;
# 举例
use mysql;

# 展示当前数据库下全部数据表
# 这个命令必须在选择数据库之后才能使用
show tables;

# 查询当前数据库某一个表的数据结构
# 这个命令必须在选择数据库之后才能使用
describe <tablename>;
# 举例
describe students;

# 创建一个数据库
# 格式
create database [if not exists] <databasename>;
# 举例
create database hello;

# 如果hello数据库不存在，才创建，否则不创建
create database if not exists hello;

# sql注释方式
-- 这是单行注释
/*
这是多行注释
*/

操作数据库指令与介绍

操作数据库>操作数据库中的表>操作数据库中表的数据

操作数据库

  1. 创建数据库
# 格式
create database [if not exists] <databasename>;
# 举例
create database hello;

# 如果hello数据库不存在，才创建，否则不创建
create database if not exists hello;
  1. 删除数据库
# 格式
drop database [if exists] <databasename>;
# 举例
drop database hello;
# 如果hello数据库存在，则删除
drop database if exists hello;
  1. 使用数据库
use `<databasename>`
# 举例
use hello
# 如果名字有特殊字符，需要加上`符号
use `hello`
  1. 查看数据库
# 查看当前有哪些数据库
show databases;

# 查看某一个数据库创建语句
show create database school

创建数据表

模拟环境

# 目标：创建一个school数据库
# 使用sql创建一个学生表（列，字段）
# 学号int, 登录密码varchar(20)，性别varchar(2)，出生日期datetime，家庭地址，email

# 注意事项：
# 1. 注意每一个字段定义完成后需要使用逗号结尾，最后一个字段除外
# 2. 表的名称和字段尽量用`符合包裹，这样可以避免使用到sql关键字
# 3. AUTO_INCREMENT自增
# 4. PRIMARY KEY主键，一般一个表只有一个唯一主键
# 5. 字符串使用单引号包裹

代码

# 格式展示
create table [if not exists] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ······
    `字段名` 列类型 [属性] [索引] [注释],
    primary key('字段名')
)[表引擎类型][字符集设置][注释]

# 代码编写
create table if not exists `student` (
    `id` int(4) not null auto_increment comment '自增主键',
    `studentNumber` int(4) comment '学号',
    `passwd` varchar(20) not null default '123456' comment '登录密码',
    `sex` varchar(2) not null default '女' comment '学员性别',
    `birthday` datetime default null comment '学员出生日期',
    `homeAddress` varchar(100) default null comment '学员家庭住址',
    `email` varchar(50) default null comment '学员邮箱',
    `gradeid` int(10) not null comment '学生年纪id',
    primary key(`id`)
)engine=innodb default charset=utf8

查看数据表

# 查看数据表创建语句
show create table students

# 查看数据表的结构
desc students

修改数据表

# 修改表名
alter table <旧表名> rename as <新表名> 

# 举例
alter table teacher rename as teacher1

# 增加表中的字段
alter table <表名> add <字段名> [约束]

# 举例
alter table teacher1 add age int(4)

# 修改表的字段的名字
alter table <表名> change <旧字段名> <新字段名> [约束]

# 举例
alter table teacher1 change age age1 int(10)

# 修改表的字段的约束
alter table <表名> modify <字段名> <约束>

# 举例
alter table teacher modify age1 varchar(11)

# 注意，modify和change的区别。modify只能修改约束，change只能修改名字

# 删除表中的字段
alter table <表名> drop <字段名>

# 举例
alter table teacher drop age1

删除表

drop table [if exists] <tablename>

# 举例
drop table if exists teacher1

mysql数据管理

  1. 外键（了解）
create table `grade`(
  `gradeid` int(10) not null auto_increment comment '年级id',
  `gradename` varchar(50) not null comment '年纪名称',
  primary key(`gradeid`)
)engine=innodb default charset=utf8

create table if not exists `student` (
    `id` int(4) not null auto_increment comment '自增主键',
    `studentNumber` int(4) comment '学号',
    `passwd` varchar(20) not null default '123456' comment '登录密码',
    `sex` varchar(2) not null default '女' comment '学员性别',
    `birthday` datetime default null comment '学员出生日期',
    `homeAddress` varchar(100) default null comment '学员家庭住址',
    `email` varchar(50) default null comment '学员邮箱',
    `gradeid` int(10) not null comment '学生年纪id',
    primary key(`id`),
    key `FK_gradeid`(`gradeid`),
    constraint `FK_gradeid` foreign key (`gradeid`) references `grade`(`gradeid`)
)engine=innodb default charset=utf8

# 需要注意的地方：
1. FK_*这个是固定写法
2. key `FK_gradeid`(`gradeid`)是定义某一个字段为外键，外键名字叫gradeid
3. constraint `FK_gradeid` foreign key (`gradeid`) references `grade`(`gradeid`) 给这个外键添加约束（执行引用）。constraint：约束；foreign key：外键；reference：引用
4. 格式说明：constraint <约束名> foreign key(<作为外键的列>) references <被关联的表>(<被关联的字段>)

# 结论
以上的操作都是物理外键，属于数据库级别的外键，不建议使用（如果过多使用，会造成数据库维护困难等问题）
如果需要使用外键，可以在逻辑层去实现外键的操作，这样数据库中就只是用来单纯的存放数据了，不会再有物理外键了，也就不存在数据库维护的问题，直接修改程序逻辑即可

mysql语言

  1. DML:数据操作语言（重要，全记住）
    • 添加：insert
    # 格式
insert into <表名>([字段名1],[字段名2]...[字段名N]) values('值1','值2'...'值N'),('值1','值2'...'值N')...('值1','值2'...'值N')
# 举例
# 基础的插入操作
insert into `students`(`studentNumber`,`name`...) values(01,'小红')
# 一次性插入多行数据
insert into `students`(`studentNumber`,`name`...) values (01,'小红'...),(01,'小明'...)...(01,'小刚'...)
    • 修改：update
    # 语法
update <表名> set <字段名>=<值>[,<字段名>=<值>,<字段名>=<值>...<字段名>=<值>] where [条件]
# 举例
update `students` set `name`='moon',`email`='xxxx@xx.com' where id=1;
# 注意事项：
使用update的时候最好加上where之类的过滤条件，否则update会更改当前表中所有行的数据，这是非常危险的操作
    • 删除：delete
    # 语法：
delete from <表名> where <条件>
delete from student where id=1
# 注意事项
1. 如果delete不加上判断条件，会造成删除全部行
2. 值得一提的是命令truncate也可以清空数据表
   - truncate `students`

# delete与truncate区别
1. 相同点：都能删除数据，都不会删除表结构
2. 不同点：
   - truncate 删除后会重新设置自增列，计数器会归零
   - truncate 不会影响事务
# 关于delete的一些小知识点，当delete删除整个表后，重启数据库，在不同引擎下表现的现象
   - 在引擎是innodb的情况下，自增列会重新开始（存在内存之中，断电即失）
   - 在引擎是MylSAM的情况下，会继续从上一个自增量开始（存在文件中，不会丢失）
  2. DQL:数据查询语言（重要，全记住）
    • 数据库最多的操作是查询操作
    • 所有的查询操作都是使用select
    • 简单的查询，复杂的查询它都可以做
    • 数据库中最核心的语言，最重要的语句
    • 使用频率最高的语句
    # 语法：
select <字段>...from 表名

# 查询全部数据
select * from `students`

# 查询指定字段数据
select `id`,`name` from `students`

# 使用别名查询
select `id` as 学员id, `name` as 学员姓名 from `students`

# 使用函数处理查询
# 函数concat：查询结果拼接其他字符串
select concat('姓名：',`name`) from `students`

# 去重查询
# 函数distinct：去除重复关键字
select distinct `student` from `result`

# 数据库的列（表达式）
# 数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量...
select <表达式> from <表名>

# 函数表达式
# 查询数据库版本
select version()

# 变量
# 查询自增步长
select @@auto_increment_increment

# 表达式
select 100*3-1 as 计算结果

# 对某一列全部都做计算
select `id`+1 as 计算后id,`result` from `students`

where条件子句

  • 作用：检索数据中符合条件的值

  • 搜索的条件由一个或多个表达式组成，结果为布尔值

  • 尽量使用英文字母去查询，这样便于阅读
    select * from students where id>=1 and id <= 100
    select * from students where id>=1 && id <=100

  • select完整语法

模糊查询

运算符 语法 描述
is null a is null 如果操作符为null，结果为真
is not null a is not null 如果操作符不为null，结果为真
between...and a between b and c 若a在b和c之间，则结果为真
like a like b sql匹配，如果a匹配b，则结果为真
in a in (a1,a2....an) 假设a在a1，或者a2...an其中的某一个值中，结果为真

案例

  1. like用法

like结合%（代表0到任意个字符），_（一个字符）

  • 查询姓刘的同学
    select id,name from students where name like '刘%'
  • 查询姓刘的同学名字后面只有一个字的
    select id,name from students where name like 刘_
  • 查询姓刘的同学，名字后面只有两个字的
    select id,name from students where name like 刘__
  • 查询名字中间有嘉字的同学
    select id,name from students where name like %嘉%
  1. in用法

具体的一个或者多个值

  • 查询指定id：1001,1002,1003号的学员
    select id,name from students where id in (1001,1002,1003)
  • 查询在北京的学生
    select id,name,homeAddress from students where homeAddress in ('北京','安徽')
  1. null和not null
    • 查询地址为空的学生null
      select id,name,homeAddress from students where homeAddress='' or homeAddress is null
    • 查询有出生日期的同学，不为空
      select id,name,birthday from students where birthday!='' or birthday is not null
    • 查询没有出生日期的同学，为空
      select id,name,birthday from students where birthday='' or birthday is null

联表查询：join

  1. 查询参加了考试的同学（学号，姓名，科目编号，分数）
    • 思路分析：
    1. 分析需求，分析查询的字段来自哪张表
2. 确定使用哪种查询，共计7种联表查询
   - 确定交叉点（被查询的两张表中哪些字段数据是相同的）
   - 判断的条件：学生表中的studentsNo = 成绩表中的 studentsNo
3. 写代码
# inner join查询
select s.studentNo,name,subjectNo,studentResult            # 查询需要查询的字段名，其中因为两个表都有studentNo，为了区分开，所以给后面的表写了别名，然后这里通过别名来调用字段名
from students as s                                         # 首先先确定从那个表开始查询，且取别名为s，这里使用students和result均可，在实际开发中，需要考虑简单已读，复杂性较低的方案 
inner join result as r                                     # 连接第二张表，取别名为r
where s.studentNo=r.studentNo                              # 确定交叉条件，因为在students表中的studentNo字段和result表中的studentNo字段是同一个东西，所以用这个字段作为交叉点

# left join 查询
select s.studentNo,name,subjectNo,studentResult
from students as s
left join result as r
on s.studentNo=r.studentNo                                 # 此处可以用on代替where做交叉条件的确认

# right join 查询
select s.studentNo,name,subjectNo,studentResult
from students as s
right join result as r
on s.studentNo=r.studentNo

# 查询缺考的同学：从已有数据表连接无数据表查询，使用where过滤条件
# 环境描述：在students表中存在一个学生没有参加考试，所以没有成绩，成绩为null
select s.studentNo,name,subjectNo,studentResult
from students as s
left join result as r
on s.studentNo=r.studentsNo                                # on用来确定交叉条件
where studentResult is null                                # where作为条件过滤

# 总结一下join on 和where差异
1. join <连接的表> on <判断条件> 写法是固定的，为连接查询
2. where 为等值查询，在上面的inner查询中，where和on其实查询处理的结果是一致的，这里可以互相替换

# 写多个表查询的思路：
1. 我们首先要确定我们需要查询哪些数据，且这些数据位于哪些表的哪些字段中，select ...
2. 然后我们先确定查询的第一个表，select ... from ...
3. 之后我们在确认下一个要查询的表和拼接方式，比如为inner join,并且拼接到上面那句后面，select ... from ... as ...inner join ... as ...
4. 我们这时候需要知道当前两个表的共同字段有哪些，就是交叉点的确认，然后继续写在后面，select ... from ... as ...inner join ... as ... on ...=...
5. 到这一步，我们已经查询到两张表的共同全部数据了，然后把这个共同全部数据看成一张新表，再重复上面四步select ... from ... as ...inner join ... as ... on ...=... left join ... as ... on ...=... 
6. 这一步进入收尾阶段，在上面语句完成后，写下筛选条件，比如where，select ... from ... as ...inner join ... as ... on ...=... left join ... as ... on ...=... where ...

# 查询了参见考试的同学信息：学号，学生姓名，科目名，分数
# 思路：
1. 分析需求，分析查询的字段来自哪些表，student，result，subject（连接查询）
2. 确定使用哪种查询方式，确定交叉点（这两个表中哪个数据是相同的），判断条件：学生表中studentNO = 成绩表中studentNo

# sql代码
select s.studentNo,studentName,SubjectName,studentResult
from students s
right join result r
on r.studentNo=s.studentNo
inner join subject sub
on r.subjectNo=sub.subjectNo
  2. inner,left,right差异
操作 描述
inner join 如果表中至少有一个匹配，就返回行
left join 会从左表中返回全部值，即使右表中没有匹配到，没匹配到的为null，或者不会查询左表中不存在的值
right join 会从右表中返回全部值，即使左表中没有匹配到，没匹配到的为null，或者不会查询右表中不存在的值

join 图解

  1. 常用join图解
  2. 7中join图解

where后面常用的操作符

操作符 含义 范围 结果
= 等于 5=6 false
<>或!= 不等于 5<>6 true
> 大于 5>6 false
< 小于 5<6 true
<= 小于等于 5<=6 true
>= 大于等于 5>=6 false
between...and... 在某一个指定范围内 [3,7] -
and 我和你(&&) 5>1 and 1>2 false
or 我或你( )
posted @ 2022-02-23 16:15  影梦无痕  阅读(57)  评论(0)    收藏  举报