MYSQL面经-命令相关和索引

一、命令相关

1、常见命令

mysql -uroot -p #如果刚安装好MySQL，root是没有密码的*
mysql> mysql -h192.168.206.100 -uroot -p12345678; #u与root可以不加空格

mysql> drop database db_name;    # -- 删除数据库
mysql> use db_name;              #-- 选择数据库
mysql> create table tb_name (字段名 varchar(20), 字段名 char(1));   #-- 创建数据表模板
mysql> show tables;              #-- 显示数据表
mysql> desc tb_name；            # -- 显示表结构
mysql> drop table tb_name；      #-- 删除表



#创建学生表
create table Student(
     Sno char(10) primary key,
     Sname char(20) unique,
     Ssex char(2),
     Sage smallint,
     Sdept char(20)
)；

#第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
mysql> insert into tb_name values (value1,value2,value3,...);
#第二种形式需要指定列名及被插入的值：
mysql> insert into tb_name (column1,column2,column3,...) values (value1,value2,value3,...);


#插入数据
mysql> insert into Student values ( 20180001,张三,男,20,CS）;
mysql> insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ( 20180003,王五,男,18,MA）;
mysql> insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ( 20180004,赵六,男,20,IS）;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

2.指定查询结果中的列标题

通过指定列标题（也叫列别名）可使输出结果更容易被人理解。 指定列标题时，可在列名之后使用AS子句；也可使用：列别名=<表达式>的形式指定列标题。AS子句的格式为：列名或计算表达式 [AS] 列标题
模板：

select <字符型字段> as 列标题1,<字符型字段> as 列标题2, <字符型字段> as 列标题3 from bt_name；
1

3.查询经过计算的列（即表达式的值）

选择行：选择表中的部分行或全部行作为查询的结果。格式: select [all|distinct] [top n[percent]]<目标列表达式列表> from 表名

• 消除查询结果中的重复行

在select语句中使用distinct关键字可以消除结果集中的重复行,
模板：

select distinct <字符型字段>[,<字符型字段>,...] from tb_name；
1

• 限制查询结果中的返回行数

使用top选项可限制查询结果的返回行数，即返回指定个数的记录数。其中：n是一个正整数，表示返回查询结果集的前n行；若带percent关键字，则表示返回结果集的前n%行。

  模板：select top n from tb_name order by ..; /*查询前 n 的数据*/
  模板：select top n percent from tb_name order by ..; /*查询前 n% tb_name的数据*/
1
2

• 查询满足条件的行: 用where子句实现条件查询

通过where子句实现,该子句必须紧跟在From子句之后。格式为：select [all|distinct] [top n[percent]]<目标列表达式列表> from 表名 where <条件>；说明：在查询条件中可使用以下运算符或表达式：

 运算符                 运算符标识
 比较运算符         <=，<，=，>，>=，!=，<>，!>，!<
 范围运算符         between... and，not between... and
 列举运算符         in，not in
 模糊匹配运算符      like，not like
 空值运算符         is null，is not null
 逻辑运算符         and，or，not
1
2
3
4
5
6
7

• 使用比较运算符：

模板：select * from tb_name where <字符型字段> >= n ；
1

• 指定范围：

用于指定范围的关键字有两个：between…and和 not between…and。
格式为：

select * from tb_name where [not] between <表达式1> and <表达式2>;
1

其中：between关键字之后的是范围的下限（即低值）,and关键字之后的是范围的上限（即高值）。用于查找字段值在（或不在）指定范围的行。

• 使用列举:

使用in关键字可以指定一个值的集合，集合中列出所有可能的值，当表达式的值与集合中的任一元素个匹配时，即返回true，否则返回false。

模板：select * from tb_name where <字符型字段> [not] in(值1,值2,...,值n);
1

• 使用通配符进行模糊查询:

可用like 子句进行字符串的模糊匹配查询，like子句将返回逻辑值（true或False）。
like子句的格式： select * from tb_name where <字符型字段> [not] like <匹配串>；
其含义是：查找指定字段值与匹配串相匹配的记录。匹配串中通常含有通配符%和_（下划线）。
其中: %：代表任意长度（包括0）的字符串

• 多重条件查询：使用逻辑运算符

逻辑运算符and（与：两个条件都要满足）和or（或：满足其中一个条件即可）可用来联接多个查询条件。and的优先级高于or,但若使用括号可以改变优先级。
模板：select * from tb_name where <字符型字段> = ‘volues’ and <字符型字段> > n;

5.对查询结果排序

order by子句可用于对查询结果按照一个或多个字段的值（或表达式的值）进行升序（ASC）或降序（DESC）排列，默认为升序。
格式：order by {排序表达式[ASC|DESC]}[,…n]；
其中：排序表达式既可以是单个的一个字段，也可以是由字段、函数、常量等组成的表达式，或一个正整数。
模板：

select * from tb_name order by <排序表达式> <排序方法>；
1

使用统计函数:又称集函数，聚合函数
在对表进行检索时，经常需要对结果进行计算或统计，T-SQL提供了一些统计函数（也称集函数或聚合函数），用来增强检索功能。统计函数用于计算表中的数据，即利用这些函数对一组数据进行计算，并返回单一的值。
常用统计函数表

        函数名      功能
        AVG         求平均值
        count        求记录个数，返回int类型整数
        max          求最大值
        min           求最小值
        sum          求和
1
2
3
4
5
6

1. SUM和AVG
   功能：求指定的数值型表达式的和或平均值。
   模板：

   select avg(<字符型字段>) as 平均数,sum(<字符型字段>) as 总数 from tb_name where <字符型字段> ='字符串';
   1

2. Max和Min
   功能：求指定表达式的最大值或最小值。
   模板：

   select max(<字符型字段>) as 最大值,min(<字符型字段>) as 最小值 from tb_name;
   1

3. count
   该函数有两种格式：count()和count([all]|[distinct] 字段名），为避免出错，查询记录个数一般使用count()，而查询某字段有几种取值用count(distinct 字段名）。
   (1).count():
   功能：统计记录总数。
   模板：select count(<字符型字段>) as 总数 from tb_name;
   (2).count([all]|[distinct] 字段名）
   功能：统计指定字段值不为空的记录个数，字段的数据类型可以是text、image、ntext、uniqueidentifier之外的任何类型。

    模板：select count(<字符型字段>) as 总数 from tb_name;
   1

6.对查询结果分组

​ group by子句用于将查询结果表按某一列或多列值进行分组，列值相等的为一组，每组统计出一个结果。该子句常与统计函数一起使用进行分组统计。
​ 格式为：group by 分组字段[…n] [having <条件表达式>]

1. 在使用group by子句后
   select列表中只能包含：group by子句中所指定的分组字段及统计函数。

2. having子句的用法
   having子句必须与group by 子句配合使用，用于对分组后的结果进行筛选（筛选条件中常含有统计函数）。

3. 分组查询时不含统计函数的条件
   通常使用where子句；含有统计函数的条件,则只能用having子句。
   模板：

     select <字符型字段>,count(<字符型字段>) as 列标题 from tb_name where <字符型字段>='字符串' group by <字符型字段>；
   1

4. 修改数据(Update）
   Update 语句用于修改表中的数据。

   格式：update tb_name set 列名称 = 新值 where 列名称 = 某值；
   
   
   1
   2
   3

5. 删除数据(Delete)

 删除单行
      格式：delete from tb_name where 列名称 = 某值；
  删除所有行
      可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
      格式：
1
2
3
4
5

   delete * from tb_name   或  delete from tb_name；
1

7、MySQL – 应用

学生-课程数据库
学生表：Student（Sno，Sname，Ssex，Sage，Sdept）
课程表：Course（Cno，Cname，Cpno，Ccredit）
学生选课表：SC（Sno，Cno，Grade）
关系的主码加下划线表示。各个表中的数据示例如图所示：

 建立一个“学生”表Student：
create table Student(
  Sno char(9) peimary key, /*列级完整性约束条件，Sno是主码*/
  Sname char(20) unique, /* Sname取唯一值*/
  Ssex char(2),
  Sage smallint,
  Sdept char(20)
);
 建立一个“课程”表Course：
create table Course(
  Sno char(4) primary key, /*列级完整性约束条件，Cname不能取空值*/
  Sname char(40) not null, /*Cpno的含义是先修课*/
  Cpno char(4)
  Ccredit smallint,
  foreign key (Cpnoo) references Course(Cno) /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);
 建立学生选课表SC：
create table SC(
  Sno char(9),
  Cno char(4),
  Grade smallint,
  frimary key (Sno,Cno), /*主码由两个属性构成，必须作为表级完整性进行定义*/
  foreign key (Sno) references Student(Sno), /*表级完整性约束条件，Sno是外码，被参照表是Student*/
  foreign key (Cno) references Course(Cno)   /*表级完整性约束条件，Cno是外码，被参照表是Course */
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

增 插入数据
语法：insert into [table_name] ([column],…) values(“name”,…);
例子：

insert into student(name,age,NCEE_grade,register_date) values("刘备",20,400,"2018-9-1")；
insert into student(name,age,NCEE_grade,register_date) values("关羽",19,380,"2018-8-31");
insert into student(name,age,NCEE_grade,register_date) values("张飞",18,360,"2018-8-30");
1
2
3

删 删除行数据
语法 ：delete from [table_name] where [column_name] = " "
例子：

1.删除叫刘备的小朋友 delete from student where name = "刘备"；
2.删除age大于等于19的小朋友 delete from student where age>=19;
3.删除登记日期在9月之后的 delete from student where date like "2018-09%";
1
2
3

改 更改数据值
格式：

update [table_name] set [columns] = " ",[columns] = " " where /
1

例子：将刘备小朋友的高考成绩改成390

update student set NCEE_grade = 390 where name = "刘备";
1

原文链接：https://blog.csdn.net/caipengbenren/article/details/98388046

二、MySQL索引

关于MySQL索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。还是以WordPress来说，其多个数据表都会对经常被查询的字段添加索引，比如wp_comments表中针对5个字段设计了BTREE（二叉树）索引。

合理的设计自己的数据库表和索引可以大大提高数据的检索速度，如果在大表中滥用索引反而会影响你的数据库性能，下边数据库优化有详细提到。

Alter table employees add index first_name (first_name);
1

2.1 索引类型

2.1.1 B树

大多数存储引擎都支持B树索引。B树通常意味着所有的值都是按顺序存储的，并且每一个叶子也到根的距离相同。B树索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取数据。下图就是一颗简单的B