有关MySQL的命令语句（二）

一、查询数据

使用SELECT语句从表或视图获取数据。表由行和列组成，如电子表格。

SELECT语句的结果称为结果集，它是行列表，每行由相同数量的列组成。

SELECT语句由以下列表中所述的几个子句组成：

语句 说明
SELECT  逗号分隔列或星号(*)的列表，表示要返回所有列或表达式
FROM 指定要查询数据的表或视图
JOIN 根据某些连接条件从其他表中获取数据。
WHERE 子句（条件查询） 行级过滤。按照“条件表达式”指定的条件进行查询。
GROUP BY 子句（分组） 分组说明。将一组行组合成小分组，并对每个小分组应用聚合函数。按照“属性名”指定的字段进行分组。
HAVING 组级过滤。过滤器基于GROUP BY子句定义的小分组。
ORDER BY 输出排序顺序。按照“属性名”指定的字段进行排序。排序方式由“asc”和“desc”两个参数指出。
LIMIT（限制结果集） 限制返回行的数量。

语句中的 SELECT 和 FROM语句 是必须的，其他部分是可选的。

1、数据的准备

 

  

-- 创建一个数据库：
create database test_db;
-- 使用数据库：  
use pysql;
-- 查看当前使用的是哪个数据库：
select database();
-- 创建数据表teams, heroes

 

mysql> create table dept
-> (
-> d_no INT(11) NOT NULL AUTO_INCREMENT,
-> d_name VARCHAR(50) NOT NULL,
-> d_location VARCHAR(100),

-> PRIMARY KEY('d_no'),

-> UNIQUE KEY 'd_no'('d_no')

-> );

mysql> create table employee
-> (
-> e_no INT(11) NOT NULL UNIQUE PRIMARY KEY,
-> e_name VARCHAR(50) NOT NULL,
-> e_gender CHAR(2),
-> dept_no INT(11) NOT NULL,
-> e_job VARCHAR(50) NOT NULL,
-> e_salary INT(11) NOT NULL,
-> hireDate DATE NOT NULL
-> );　
-- 查看数据库中已有的数据表：
show tables;
-- 了解数据表的创建方式：
show create table dept;
-- 查看表的基本数据结构：
describe/desc 表名； 查看表的字段信息，包括：字段名、字段数据类型、是否为主键、是否有默认值等
-- 插入数据
 mysql> INSERT INTO dept(d_no, d_name, d_location)
-> VALUES(10,'ACCOUNTING','ShangHai'),

-> (20,'RESEARCH','BeiJing'),

-> (30,'SALES','ShenZhen'),

-> (40,'OPERATIONS','FuJian');

　　mysql> INSERT INTO employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)

-> VALUES(1001,'SMITH','m',20,'CLERK',800,'2005-11-12'),

-> (1002,'ALLEN','f',30,'SALESMAN',1600,'2003-05-12'),

-> (1003,'WARD','f',30,'SALESMAN',1250,'2003-05-12'),

-> (1004,'JONES','m',20,'MANAGER',2975,'1998-05-18'),

-> (1005,'MARTIN','m',30,'SALESMAN',1250,'2001-06-12'),

-> (1006,'BLAKE','f',30,'MANAGER',2850,'1997-02-15'),

-> (1007,'CLAKE','m',10,'MANAGER',2450,'2002-09-12'),

-> (1008,'SCOTT','m',20,'ANALYST',3000,'2003-05-12'),

-> (1009,'KING','f',10,'PRESIDENT',5000,'1995-01-01'),

-> (1010,'TURNER','f',30,'SALESMAN',1500,'1997-10-12'),

-> (1011,'ADAMS','m',20,'CLERK',1100,'1999-10-05'),

-> (1012,'JAMES','f',30,'CLERK',950,'2008-06-15');

2、基本的查询功能

-- 查询所有字段(大数据库中慎用)： -- select * from 表名;  
select * from dept;

 

-- 查询指定字段： -- select 列1, 列2,...from 表名;  -- select 表名.字段... from 表名
select employee.e_no, employee.e_name  from employee;
select e_no, e_name, e_salary employee;

 

-- 使用as给字段起别名 ：-- select 字段 as 别名 from 表名; 
select e_no as "员工编号“, e_name as "员工姓名" from employee;

 

-- 通过as给表起别名 ：--select * from dept  as h; 
-- 消除重复行 ： -- distinct 字段 select distinct gender from heroes;

3、条件查询

 

（1）-- 比较运算符：  
-- select ... from 表名 where 条件
-- 查询大于18岁的信息
select * from heroes where age>18;

 

  -- 查询id小于5的信息
  select * from heroes where id<5;

 

  --查询性别为男的英雄的id和名字
select id,name from heroes where gender=1;

 

  不等于：!= 或者 <> (<>在很多语言中都不用，所以首选 !=)
  select id,name from heroes where gender!=1;

 

逻辑运算符(与 或 非)：  and 或 &&
  -- 18到50岁之间英雄的信息
select * from heroes where age>18 and age<50;

 

  -- 18岁以上的女性
  select * from heroes where age>18 and gender=2;
  select * from heroes where age>18 and gender="女";

 

or 或 ||
  -- 50岁以上或身高180(包含)以上
  select * from heroes where age>50 or height>=180;

 

 

not 或 ！ ：not 加在谁前面就仅仅否定这一个条件,用()解决优先级的问题，不要死记硬背
  -- 不属于 70岁以上男英雄 的
  select * from heroes where not (age>70 and gender=1);

 

  -- 年龄不小于或等于18 的女性英雄.用()解决优先级的问题
  select * from heroes where (not age<=18) and gender=2;

 

-- 模糊查询
通配符：一种在SQL的WHERE条件子句中拥有特殊意思的字符，SQL语句支持很多通配符，可以和LIKE一起使用的通配符为‘%’和‘_’。
-- like (效率低) 
1） % 替换1个，0个或多个; 匹配任意长度的字符，甚至包括零字符。可放置在任意位置。
  • 2） _替换一个; 一次只能匹配任意一个字符
--查询姓名中以“赏”开头的名字
select name from heroes where name like "赏%";

　　--查询姓名中有“赏”的名字 

select name from heroes where name like "%赏%";

 

--查询两个字的名字 
select name from heroes where name like "__";

 

 --查询至少两个字的名字
 select name from heroes where name like "__%";

 

-- rlike 正则 
-- 查询以“泰”开始的名字 
select name from heroes where name rlike "^泰.*";

 

-- 查询以“希”开头，“尔”结尾的名字
 select name from heroes where name rlike "^希.*尔$";

 

-- 范围查询

 

查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用（）括起来，检索条件之间用逗号分开，只要满足条件范围内的一个值即为匹配项。

 

  -- in (18, 70, 50) 表示在一个非连续的范围内

 

  -- 查询年龄为18,70的英雄
  select name, age from heroes where age in (18, 70, 50);

 

  -- not in (18, 70, 50) 不在某个非连续的范围内
  select name, age from heroes where age not in (18, 70, 50);

 

-- between .. and .. 在某个连续的范围内

 

用来查询某个范围内的值。该操作符的两个参数，分别为范围的开始值和结束值。

 

  select name, age from heroes where age between 18 and 50;

 

  -- not between .. and .. 不在某个范围中，这是一个整体的语句，同时否定between和and的内容
  select name, age from heroes where age not between 18 and 50;
  select name, age from heroes where not age between 18 and 50;

 

-- 判断为空： is null

 

空值： 一般表示数据未知、不适用或将在以后添加数据。

 

  -- a = None 表示 a没有指向任何东西，a = "" 表示a指向一个为空的对象
  -- 查询身高为空的信息
 select * from heroes where height is null;

 

  -- 不为空的
 select * from heroes where height is not null;

 

4、查询排序 

 

-- order by 字段
-- asc 升序(默认值)
-- desc 降序
-- 先写那个条件，先按照这个条件排序，相同情况下，按第二个排，否则不生效

 

①单列排序：SELECT 字段名1 FROM 表名 ORDER BY 字段名1；

 

--查询年龄在20-70的男英雄，按照年龄升序排列
select * from heroes where (age between 20 and 70) and gender=1 order by age;
select * from heroes where (age between 20 and 70) and gender=1 order by age asc;

 

②多列排序：SELECT 字段名1，字段名2 FROM 表名 ORDER BY 字段名1，字段名2；
-- 查询年龄在16-24之间的女性，按身高降序排列，如相同，按年龄升序排列
select * from heroes where (age between 16 and 20) and gender=2 order by height desc,age asc;

 

③指定排序方向：SELECT 字段名1 FROM 表名 ORDER BY 字段名1 DESC； 

（1）order by price  //默认升序排列
（2）order by price desc //降序排列
（3）order by price asc //升序排列，可写可不写
（4）order by rand() //随机排列，效率不高 

-- 全部人员，按照年龄从小到大排列，身高从高到低 
select * from heroes order by age, height desc;

 

5、聚合函数   

 函数

作用

AVG()

返回某列的平均值

COUNT()

返回某列的行数

MAX（）

返回某列的最大值

MIN()

返回某列的最小值

SUM()

返回某列的和

GROUP_CONCAT(col)

返回由属于一组的列值连接组合而成的结果

（1）总数:

count（）函数 :统计数据表中包含的记录函数，或根据查询结果返回列中包含的数据行数。

count（*）：计算表中总行数，不管某列是数值还是空值；

count(字段名)：计算指定列的总行数，计算时将忽略空值的行。

例1：查询表中的总行数，为结果命名为num，并将其显示出来；

 　　select count(*) AS num from 表名;

例2：查询 name 列下的总行数（不包括空值），为结果命名为num，并将其显示出来；

 　　select count(name) as num from 表名;

例3：计算不同分组中的记录总数

 　　用count()函数统计不同年龄下，姓名的个数（例如：年龄为21的有Lily，Jack ，Matin，则21对应的count()值为3）

 　　select age,count(name) from 表名 group by age

PS: 在指定列的值为空的行被COUNT()函数忽略；若不指定列，使用COUNT(*)时，则所有记录都不忽略。

（2）最大值: 

max（）函数: 返回指定列中的最大值。

MAX()函数可用于查询数值类型，还可用于查询字符类型。

字符串的大小比较，依据字符的ASCII码值大小进行比较，从a~z,a的ASCII码最小，z的最大。

-- 查询最大的年龄

select age from heroes;
select max(age) from heroes;

 

-- 查询女性最高身高 ：select max(height) as "最高身高" from heroes where gender=2;

(3)求和

sum（）函数:返回指定列值的总和

 例如：查看表中price字段列下所有价格的总和。

 select sum (price) AS Total from 表名;

 

PS: SUM()函数在计算时，忽略列值为NULL的行。

所有人身高总和

select sum(height) from heroes;

 

（4）平均值
avg（）函数：通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

PS： AVG()函数使用时，其参数为要计算的列名称，若要得到多个列的多个平均值，则需在每一列上使用AVG()函数。

-- 女性平均年龄，以下两种方式均可，此处目的在于说明select后面可以加运算式，
-- 但此类统计中尽量避免第二种方式，例如在此数据表中，如果是平均身高的话，因为有一个null的存在...
select avg(age) from heroes where gender=2;
select sum(age)/count(*) from heroes where gender=2;

 

6、分组查询

要和聚合搭配使用，才比较有意义： 

[GROUP BY 字段] [HAVING <条件表达式>]

select 类别, sum(数量) as 数量之和

from A

group by 类别

注：group by语句中select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中。

按照性别分组，查询所有的性别
select 可以唯一标记每个分组的...东西 from heroes group by gender;
select gender from heroes group by gender;

 

计算每种性别有多少人
select gender, count(*) from heroes group by gender;
-- 此处的count(*) 是对每组的计算结果
-- 计算每组中的最大年龄、平均年龄
select gender, max(age) from heroes group by gender;
select gender, avg(age) from heroes group by gender;
-- having
-- HAVING是数据分组之后来过滤选择分组，WHERE在分组之前用来选择记录。另外WHERE排除的记录不在包括在分组中。

例1：

select 类别, sum(数量) as 数量之和 from A

group by 类别

having sum(数量) >30 

例2：

select 类别, SUM(数量)from A

where 数量 >8

group by 类别

having SUM(数量) >10

-- 查询平均年龄超过30的性别，以及其中包含的人名 ；
select gender, group_concat(name), avg(age) from heroes group by gender having avg(age) > 30;

 

-- 查询人数多于2的性别
select gender, group_concat(name), count(*) from heroes group by gender having count(*) > 2;


 


使用LIMIT限制查询结果的数量


LIMIT[offset，] 行数


第一个“offser [位移偏移量]”参数：指MySQL从哪一行开始显示，是一个可选参数。若不指定，将从表中的第一条记录开始（第一条记录的位置偏移量为0，第二条记录的位置偏移量为1…以此类推）；


第二个参数“行数”指示返回的记录条数。 


7、关联查询


 


连接查询为关系数据库中最重要的查询，主要包括内连接、外连接等。


 


（1）内连接查询：INNER JOIN ...ON ...


 


内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新纪录。


 


PS: INNER JOIN语法是ANSI SQL的标准规范，使用INNER JOIN连接语法能够确保不会忘记连接条件（ON），且是使用WHERE子句在某些时候会影响查询的性能。


 


内连接查询，取多个表的交集，否则不显示。


 


  -- select * from 表1 inner join 表2; 将两张表对应起来
  -- 表1 一行一行的来对应表2 所有行




  select * from heroes inner join groups;




 


  -- 查询有能够对应小队的的英雄以及小队信息


  -- select * from 表1 inner join 表2 on 条件;




 select * from heroes inner join groups on heroes.grp_id=groups.id;




 


（2）外连接查询


 


LEFT JOIN...IN...(左连接)：返回包括左表中的所有记录和右表中连接字段相等的记录；


 


select A.filed, [A.filed2, .... ,] B.filed, [B.filed4...,] from <left table> as A  left join <right table> as B on <expression>


 


假设有A、B两张表，左连接查询即 A表在左不动，B表在右滑动，A表与B表通过一个关系来关联行，B表去匹配A表。


 


RIGHT JOIN...IN...(右连接)：返回包括右表中所有记录和左表中连接字段相等的记录。


 


在 FROM 子句中使用关键字 LEFT OUTER JOIN 或者 LEFT JOIN，用于接收该关键字左表（基表）的所有行，并用这些行与该关键字右表（参考表）中的行进行匹配，即匹配左表中的每一行及右表中符合条件的行。在左外连接的结果集中，如果匹配到就显示，匹配不到就显示为null。


 


  -- right join...on  用的很少
  -- 将数据表名字互换位置，用left join即可完成


 


3）全连接查询 full join ... on ...


 


语法：


 


select ... from <left table> full join <right table> on <expression>


 


全连接会将两个表的所有数据查询出来，不满足条件的为NULL。


 


全连接查询跟全相乘查询的区别在于，如果某个项不匹配，全相乘不会查出来，全连接会查出来，而连接的另一边则为NULL。


 


4）联合查询 union


 


把两张表的字段都查出来，没有对应的值就显示null，但是注意：mysql是没有全外连接的(mysql中没有full outer join关键字)，想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接。


 


语法：


 


select A.field1 as f1, A.field2 as f2 from <table1> A union (select B.field3 as f1, field4 as f2 from <table2> B)


 


union是求两个查询的并集。union合并的是结果集，不区分来自于哪一张表，所以可以合并多张表查询出来的数据。


 


4.1、将两张表的数据合并查询出来


 


SELECT id, content, user FROM comment UNION (SELECT id, msg AS content, user FROM feedback);


 




 


4.2、union查询，列名不一致时，以第一条sql语句的列名对齐


 


SELECT id, content, user FROM comment UNION (SELECT id, msg, user FROM feedback);


 




 


4.3、使用union查询会将重复的行过滤掉


 


SELECT content,user FROM comment UNION (SELECT msg, user FROM feedback);


 




 


4.4、使用union all查询所有，重复的行不会被过滤


 


SELECT content,user FROM comment UNION ALL (SELECT msg, user FROM feedback);


 




 


4.5、union查询，如果列数不相等，会报列数不相等错误 


 




 


4.6、union 后的结果集还可以再做筛选


 


SELECT id,content,user FROM comment UNION ALL (SELECT id, msg, user FROM feedback) ORDER BY id DESC;


 


union查询时，order by放在内层sql中是不起作用的；因为union查出来的结果集再排序，内层的排序就没有意义了；因此，内层的order by排序，在执行期间，被mysql的代码分析器给优化掉了。


 


(SELECT id,content,user FROM comment ORDER BY id DESC) UNION ALL (SELECT id, msg, user FROM feedback ORDER BY id DESC);


 


order by 如果和limit一起使用，就显得有意义了，就不会被优化掉。


 


( SELECT goods_name,cat_id,shop_price FROM goods WHERE cat_id = 3 ORDER BY shop_price DESC LIMIT 3 )


 


UNION


 


( SELECT goods_name,cat_id,shop_price FROM goods WHERE cat_id = 4 ORDER BY shop_price DESC LIMIT 2 );


 


 


8、子查询


1、where型子查询（把内层查询结果当作外层查询的比较条件）


（1）查询id最大的一件商品(使用排序+分页实现)




SELECT goods_id,goods_name,shop_price FROM goods ORDER BY goods_id DESC LIMIT 1;




 


（2）查询id最大的一件商品(使用where子查询实现)




SELECT goods_id,goods_name,shop_price FROM goods WHERE goods_id = (SELECT MAX(goods_id) FROM goods);




 


（3）查询每个类别下id最大的商品(使用where子查询实现)




SELECT goods_id,goods_name,cat_id,shop_price FROM goods WHERE goods_id IN (SELECT MAX(goods_id) FROM goods GROUP BY cat_id);




 


2、from型子查询(把内层的查询结果当成临时表，供外层sql再次查询。查询结果集可以当成表看待。临时表要使用一个别名。)


（1）查询每个类别下id最大的商品(使用from型子查询)




 SELECT goods_id,goods_name,cat_id,shop_price FROM

(SELECT goods_id,goods_name,cat_id,shop_price FROM goods ORDER BY cat_id ASC,goods_id DESC) AS tmp GROUP BY cat_id;




 


子查询查出的结果集看第二张图，可以看到每个类别的第一条的商品id都为该类别下的最大值。然后将这个结果集作为一张临时表，巧妙的使用group by 查询出每个类别下的第一条记录，即为每个类别下商品id最大。


3、exists型子查询（把外层sql的结果，拿到内层sql去测试，如果内层的sql成立，则该行取出。内层查询是exists后的查询。）


（1）从类别表中取出其类别下有商品的类别(如果该类别下没有商品，则不取出)[使用where子查询]




SELECT c.cat_id,c.cat_name FROM category c WHERE c.cat_id IN (SELECT g.cat_id FROM goods g GROUP BY g.cat_id);




 


（2）从类别表中取出其类别下有商品的类别(如果该类别下没有商品，则不取出)[使用exists子查询]




 SELECT c.cat_id,c.cat_name FROM category c WHERE EXISTS (SELECT 1 FROM goods g WHERE g.cat_id = c.cat_id);




 


exists子查询，如果exists后的内层查询能查出数据，则表示存在；为空则不存在。


 


9、正则表达式


  




  mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';




 


 二、数据库管理语句（插入、更新和删除数据）


 2.1 插入数据


 1.为表的所有字段插入数据。 




INSERT INTO table_name(column_list)  VALUES (value_list);




 1）指定所有字段名（*）


 2）完全不指定字段名


 PS：使用INSERT插入数据时可忽略插入数据的列名称，但是值若不包含列名称，那VALUES关键字后面的值不仅要求完整而且顺序必须和表定义时列的顺序相同。


 若表的结构被修改，对列进行增加、删除或者位置改变操作，这些操作将使得用这种方式插入数据时的顺序也同时改变。


 如果指定列名称，则不会受到表结构改变的影响。


 2.为表的指定字段插入数据


 PS：在插入记录时，如果某些字段没有指定插入值，MySQL将插入该字段定义时的默认值。


 要保证每个插入值的类型和对应列的数据类型匹配，如果类型不同，将无法插入，并且MySQL会产生错误。


 3.同时插入多条记录 




INSERT INTO  table_name(column_list) 
VALUES (value_list), (value_list1), (value_list2),..... value_listN);




 PS:使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些包含数的字符串的意思：


 1）Records:表明插入的记录条数；


 2）Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值；


 3）Warnings：表明有问题的数据值，例如发生数据类型转换。


 PS：一个同时插入多条记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中，效率更高。


 即MYSQL执行单条INSERT语句插入多行数据，比使用多条INSERT语句块，所以在插入多条记录时，最好选择使用单条INSERT语句的方式插入。


 4.将查询结果插入列表中 




INSERT INTO table_name1 (column_list1)
SELECT (column_list2) FROM table_name2 WHERE (conditon)




 table_name1:指定待插入数据的表；


 column_list1：指定待插入表中要插入数据的哪些列；


 table_name2：指定插入数据是从哪个表中查询出来的，该列表必须和column_list1列表中的字段个数相同，数据类型相同；


 conditon：指定SELECT语句的查询条件。


 2.2  更新数据 




UPDATE table_name
SET column_name1 = value1,  column_name2 = value2,.....,column_nameN = valueN
WHETE (condition)




 column_name1,column_name2,.....,column_nameN：指定更新的字段名称；


 value1,value2,.....,valueN：相对应的指定字段的更新值；


 conditon：指定更新的记录需要满足的条件。


更新多个列时，每个“列_值”对 之间用逗号隔开，最后一列之后不需要逗号。


若忽略WHERE字句，MySQL将更新表中所有的行。


 2.3  删除数据 




DELETE FROM table_name {WHERE <condition>}




 table_name：指定要执行删除操作的表；


 WHERE <condition>：为可选参数，指定删除条件，若没有，则删除表中的所有记录。


 三 、索引


 3.1 索引的含义和特点 


根据存储引擎定义每个表的最大索引数和最大索引长度。
所有存储引擎支持每个表至少16个索引，总索引长度至少为256个字节。
MySQL中索引的存储引擎有两种：BTREE和HASH，具体和表的存储引擎相关；
MyISAM和InnoDB存储引擎只支持BTREE索引；
MEMORY/HEAP存储引擎可以支持BTREE和HASH索引；
索引的优点：
1）通过创建唯一索引，可以保证数据表中每一行数据的唯一性；
2）可大大加快数据的查询速度；
3）在实现数据的参考完整性方面，可以加速表和表之间的连接；
4）在使用分组和排序子句进行数据查询时，也可显著减少查询中分组和排序的时间；
索引的缺点：
1）创建索引和维护索引要耗费时间。
2）索引需要占磁盘空间；
3）当对表中的数据进行增加、删除和修改时候，索引也要动态的维护，降低数据的维护速度。 


3.2 索引的分类


 1）普通索引和唯一索引；


普通索引：MySQL的基本索引类型，允许在定义索引的列中插入重复值和空值；
唯一索引：索引列的值必须唯一，但允许有空值；若组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值；
2）单列索引和组合索引；
单列索引：一个索引只包含单个列，一个表可以有多个单列索引；
组合索引：表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。组合索引遵循最左前缀组合。
3）全文索引；
全文索引的类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值；
全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。
MYSQL中只有MyISAM存储引擎支持全文索引。
4）空间索引；


 必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。


3.3 创建、删除索引


 1.创建表的时候创建索引


 CREATE TABLE table_name {col_name data_type}




{UNIQUE | FULLTEXT | SPATIAL} {INDEX | KEY} {index_name} (col_name { length }) {ASC | DESC}




 UNIQUE、FULLTEXT 、 SPATIAL为可选参数，分别表示唯一索引，全文索引和空间索引；


INDEX 、KEY为同义词，两者作用相同，用来创建索引；
col_name：为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
index_name：指定索引的名称，为可选参数，若不指定，MySQL默认col_name为索引值；
length：可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC 、 DESC：指定升序或降序的索引值存储。 


1）创建普通索引  




CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication)
);





 SHOW CREATE TABLE 查看表结构： 




mysql> SHOW CREATE table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8




 用explain语句查看索引是否在使用： 




mysql> explain select * from book where year_publication =1990 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication
          key: year_publication
      key_len: 1
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)




select_type: 指定所使用的SELECT查询类型。可能取值SIMPLE（简单）、UNION、PRIMARY、SUBQUERY等。 


table: 指定数据库读取的数据表的名字，按被读取的先后顺序排列。 


partitions:  


type: 指定了本数据库表与其他数据库表的之间的关联关系，可能取值system, const, eq_ref, ref, range, index和all .  


possible_keys: 给出了MySQL在搜索数据记录时可选用的各个索引。  


key: MySQL实际选用的索引。 


key_len: 给出索引按字节计算的长度；数据越小，索引越快。 


ref: 给出关联关系中另一个数据表里的数据列的名称。 


rows: MySQL在执行查询时候预计从这个数据表里读出的数据行的个数。 


filtered:  


Extra: 提供了与关联操作有关的信息。 


2）创建唯一索引  




CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);




 3）创建单列索引   




CREATE TABLE t2
(
id INT NOT NULL,
 name CHAR(30) NOT NULL,
 INDEX SingleIdx(name(20))
);




 4）创建组合索引 




CREATE TABLE t3
(
id INT NOT NULL,




 5）创建全文索引 




CREATE TABLE t4
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTAXT INDEX FullTxtIdx(info)
)ENGINE = MyISAM;




 6)创建空间索引  




CREATE TABLE t5
(
g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) 
)ENGINE = MyISAM;




 2.在已有表上创建索引 


1）使用ALTER TABLE语句创建索引 




ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [ INDEX | KEY ]
[ index_name ] (col_name [ length ],.....)[ASC | DESC]




 ADD：表示向表中添加索引。 


使用SHOW INDEX语句查看指定表中创建的索引； 


Table: 表示创建索引的表。
Non_unique: 1表示非唯一索引；0表示唯一索引；
Key_name: 表示索引的名称；
Seq_in_index: 表示该字段在索引中的位置，单列索引该值为1；组合索引为每个字段在索引定义中的顺序；
Column_name: 表示定义索引的列字段。
Sub_part: 表示索引的长度；
Null:表示该字段是否能为空值；
Index_type: 表示索引类型。 


添加索引，在bookname字段上添加索引。 




ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30));




 插入唯一索引：  




ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId); 




 查看表中的索引 




SHOW INDEX FROM book \G;




 2）使用CREATE INDEX创建索引； 




CREATE [UNIQUE | FULLTEXT |SPATIAL] INDEX  index_name
ON table_name (col_name [length],......) [ ASC | DESC]




  在book表的bookId字段上建立名称为UniqidIdx的唯一索引 




CREATE UNIQUE INDEX UniqidIdx ON book (bookId);




 在book表的authors和info字段上建立组合索引； 




CREATE  INDEX BkAuAndInfoIdx ON book (authors(20), info(30) );




 3. 删除索引 




ALTER TABLE table_name DROP INDEX index_name;






DROP INDEX index_name ON table_name;




 问题列


问题1：where和having的区别： 


作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组。

WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而HAVING
在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚集函数；  


因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。
相反，HAVING 子句总是包含聚集函数。





（严格说来，你可以写不使用聚集的 HAVING 子句，
但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。）
我们可以在 WHERE 里应用数量字段来限制，因为它不需要聚集。 这样比在 HAVING 里增加限制更加高效，因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算。


即：having一般跟在group by之后，执行记录组选择的一部分来工作的。where则是执行所有数据来工作的。

再者having可以用聚合函数，如having sum(qty)>1000


 问题2： LIKE 与REGEXP的区别？


LIKE匹配的字符串如果在文本中间出现，则找不到它，相应的行也不会被返回；


而REGEXP在文本内进行匹配，如果被匹配的字符串在文本中出现，REGEXP将会找到它，相应的行也会被返回；






            

            
posted @ 
2020-10-13 19:21 
哈哈哈嘿呀 
阅读(178) 
评论(0) 
 
收藏 
举报