Mysql基础知识复习(基础sql语句，索引)

一.sql语句

现场面被问了

1 创建表

语法

CREATE TABLE table_name(
    column_name1 INT PRIMARY KEY,
    column_name2 VARCHAR(50) NOT NULL
    ...
);

关键字CREATE TABLE再加表名，后面是小括号，不是大括号。列名后面设置属性，列和列之间使用","分开
例子：

CREATE TABLE product(
   product_id INT PRIMARY KEY,
   product_name VARCHAR(50) NOT NULL,
   product_price DECIMAL(8,2) NOT NULL,
   product_desc VARCHAR(50) NULL
);

2 给指定的列设置索引

2.1语法

CREATE INDEX加上索引名字 在ON这个表的某个列中，关键字CREATE INDEX … ON
ALTER TABLE加上表名字 ADD INDEX 索引名列名，关键字ALTER TABLE … ADD INDEX

CREATE UNIQUE INDEX index_name ON table_name(column_name);
ALTER TABLE table_name ADD INDEX index_name(column_name)

CREATE UNIQUE INDEX INDEX_ID ON product(product_name);  -- 给列创建索引
ALTER TABLE product ADD INDEX index_id(product_name);   -- 通过修改表来给表增加索引 

2.2为什么需要加上索引：

提高查询速度。
如果没有加上索引，对于一个联合查询，可能有table1_column1_length*table2_column2_length种组合的情况需要进行对比是否满足要求，但是如果给table_1和table_2的这两个列都加上索引，可以只遍历table_1的这个列，该列中的每一个key1只需要跟table2的key2进行对比，可以直接通过key1的值来在table2中找到对应的记录，而此时加了索引，可以直接查到结果，不需要每一条去比对，此种情况下加索引的查询速度就比不加索引时快了table2_column2_length倍。

2.3索引的类型

主键索引(PRIMARY INDEX)：被作为主键索引的列不能有记录的值为null，且值之间必须是不相同的，每一个表中主键索引是唯一的。如果有一列在创建时被声明为PRIMARY KEY数据库会为其自动创建主键索引。
普通索引：使用关键字创建索引，主要是为了提高查询速度。
唯一索引(UNIQUE INDEX)：创建索引的列中每一个值都是不相同的，一张表中可以添加多个唯一索引
如图所示，两个记录的product_price一致，我将这一行设置为唯一索引时，就会报错：有重复的entry

2.4索引的使用

1.某些字段需要被频繁地作为查询条件时，即经常出现在where语句后面的字段。
2.唯一性太差的字段不适合建立索引
3.频繁更新的字段不适合作为索引
4.表长度比较小的时候使用全表扫描比使用索引快，此种情况最好不要创建索引。
5.如果条件中有or，即使其中有条件带索引也不会走索引
6.like查询以%开头时不会走索引
7.列的类型为字符串时，在条件中数据需要使用引号，否则不走索引，如user_name=‘chen’
8.最左匹配原则
复合索引中的，以最左边为起点的任何连续索引都能匹配上。遇到范围查找(>,<,between,like)停止匹配。
如果建立(a,b)顺序的索引，查询where b=2时，是不走索引的。
跟B+树建立索引有关系，如果是复合索引的情况，创建联合索引(a,b)，结构如下：

a的值是有序的，a相同的情况b是有序的，a不同的情况b无序。直接查询b=2是没有办法走索引的，因为优先级最高的排序是a。遇上范围查找停止匹配的原因，如果a>1andb=2，a字段可以走索引，但b字段不可以，因为a的值是个范围，这个范围中b是无序的。

3.增加记录

关键字：INSERT INTO VALUES(没有TABLE)

INSERT INTO table_name(column1_name,column2_name) VALUES(value1,value2);
INSERT INTO table_name VALUES(value1,value2,....,valuen), --此时必须把每一列的值都写上

4 更新记录

关键字：UPDATE TABLE SET WHERE(有TABLE哦)

UPDATE TABLE table_name t SET t.name=new_value WHERE t.id = 1; 

5 删除记录

DELETE FROM table_name WHERE column_name=value;  --不能在table_name后面写缩写

6 查询

SELECT column1,column2 FROM table_name WHERE table_name.columni = value;

7修改

//修改字段
ALTER table 表 change column1 column2 int(11) not null,
//添加字段
ALTER table user add COLUMN column1 VARCHAR(20) DEFAULT NULL; 
//删除字段
ALTER table user DROP COLUMN column1;
//修改表名
ALTER  TABLE admin_user RENAME TO a_user

二.高级用法

1.连接

SELECT table1.column1,table2.column1
FROM table1,table2
WHERE table1.colunm2 = table2.column2;

使用关键字JOIN，内连接返回交集

SELECT table1.column1,table2.column1
FROM table1
INNER JOIN table2
ON table1.colunm2 = table2.column2
ORDER BY table1.column1;

左连接(LEFT JOIN)，返回左边表的所有行的指定列以及交集
右连接(RIGHT JOIN)，返回右边表的所有行的指定列以及交集

2.函数

2.1MAX()函数

SELECT MAX(column_name) FROM table_name;

2.2 GROUP BY语句

GROUP BY语句需要结合合计函数，根据一个或多个列对结果集进行分组
合计函数：SUM()
AVG()
COUNT()
MAX()

SELECT column_name,aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

2.3 having 语句

having可以和聚合函数一起使用，需要写在group by后面
https://www.runoob.com/sql/sql-having.html

SELECT column_name,aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING count(column_name) >=2;

返回一列中的最大值
参考资料：https://www.cnblogs.com/taidou/p/5153761.html
https://www.jb51.net/article/158135.htm
https://www.cnblogs.com/lanqi/p/10282279.html