Linux运维　第四阶段　（三） MySQL的SQL语句

SQL语句：

１、库

#man  mysql

#mysql  -uroot -p  -e  ‘CREATE DATABASE  testdb;’

>HELP  CREATE DATABASE  （创建库）

>CREATE  DATABASE|SCHEMA  [IF NOT EXISTS]  db_name [CHARACTER  SET  ‘gbk’] [COLLATE  ‘gbk_chinese_ci’];

例：>CREATE DATABASE  IF  NOT EXISTS  students;  （IF NOT EXISTS要创建的数据库不存在则创建，防止报错信息出现）

#cat  /mydata/data/students/db.opt  （此文件记录有字符集及排序规则，查看字符集>SHOW CHARACTER SET;查看排序规则>SHOW COLLATION;）

>HELP  ALTER DATABASE  更改字符集及排序规则（老版本升级到新版本，升级数据字典名称时用）

>HELP  DROP DATABASE

>DROP  DATABASE [IF  EXISTS]  db_name; （删除数据库）

注：一般不会给数据库重命名，如果要改名，把服务down，直接将对应的数据库目录改名。

２、表

>HELP  CREATE TABLE  （创建表）

>CREATE  TABLE [IF NOT EXISTS]  ta_name  (col_name col_defination,....CONSTRAINT) [table option]; 

例：>CREATE TABLE  tb1  (          （方法一:直接定义一张空表）

id  INT UNSIGNED  NOT NULL  AUTO_INCREMENT  PRIMARY KEY,

name  CHAR(20) NOT  NULL,

age  TINYINT NOT  NULL)  ENGINE=MyISAM;

>SHOW  TABLE STATUS  LIKE  ‘tb1’\G （查看表的存储引擎）

>SHOW  INDEXES FROM  tb1;  （查看表索引）

>CREATE  TABLE ta_name  select-statement;  （方法二:从其它表中查询出数据，并以之创建新表，仅创建查询出的数据，字段属性不复制）

例：>CREATE TABLE  tb2  SELECT *  FROM  courses WHERE  CID<=2;

>DESC  testcourses; （此方法创建的新表，字段属性将不存在）

>CREATE  TABLE ta_name  LIKE  old_ta_name; （方法三:使用旧表中的字段属性创建一张新表）

例：>CREATE TABLE  tb3  LIKE courses; 

>DROP  TABLE tb1;  （删除表）

>HELP  ALTER TABLE  （修改表结构）

例：>ALTER TABLE  test  ADD UNIQUE  KEY(course);  （给字段添加唯一键）

>ALTER  TABLE test  CHANGE  course cou  VARCHA(50)  NOT NULL;  （更改字段名）

>ALTER  TABLE test  ADD  startdate date  default  ‘2015-08-10’ FIRST;  （添加字段并赋予默认值，并置于第一个字段）

>ALTER  TABLE test  ADD  Tname CHAR(10)  NOT  NULL  AFTER  startdate; （添加字段并置于某一字段后）

>ALTER  TABLE test  RENAME  TO testcourse;  （改表名）

>RENAME  TABLE testcourse  TO  test;  （改表名）

>ALTER  TABLE stu  ADD  FOREIGN KEY(CID)  REFERENCES  Courses(CID); （添加外键约束，之后往表stu里插入数据时字段CID一定要在表Courses(CID)有效范围内，否则会报错）

注：外键约束可防止误删数据，但消耗系统资源，一般不用。

>ALTER  TABLE stu  ENGINES=InnoDB;  （改表的存储引擎）

>SHOW  TABLE STATUS\G  （查看表的存储引擎等相关详细信息）

>SHOW  CREATE TABLE  stu;  （查看创建表时的详细语句，如查询添加的外键名称）

>ALTER  TABLE stu  DROP  FOREIGN KEY  foreign_name;  （删除外键约束）

>HELP  CREATE INDEX  （创建索引）

>CREATE  INDEX index_name  [USING  BTREE|HASH]  ON  tb_name(index_column_name);

例：>CREATE INDEX  name_on_stu  USING BTREE  ON  stu(Name);

>CREATE  INDEX name_on_stu  USING  BTREE ON  stu(Name(5)DESC);  （创建索引，Name字段的所有数据内容按前５个字符降序排列）

>SHOW  INDEXES FROM  stu;  （查看表的索引）

注：索引只能创建、删除不能修改，如要修改则先删除再重新创建。

>HELP  DROP INDEX  （删除索引）

例：>DROP  INDEX name_on_stu  ON  stu;

３、DML(SELECT/INSERT INTO/UPDATE/DELETE)

>INSERT  INTO tb_name  (col1,col2,...)  VALUES (val1,val2,....) [,(val1,val2,...)];

例：>INSERT INTO  stu  (Tname,Age) VALUES  (‘jowin’,25);  （方式一：仅插入一行数据）

>INSERT  INTO stu  VALUES  (1,’chai’,25,’M’),(2,’jowin’,25,’M’),(3,’xiang’,23,’F’);  （方式二：可一次添加多条记录，在有些场景下，批量插入可提高性能）

>SELECT  LAST_INSERT_ID();  （查看自动增长型数据的状态）

注：例如表中有１０条记录，把表内容全删除，下次添加时会自动从１１开始添加，若要从头开始计数，则删除数据时使用>TRUNCATE  tb_name;

>INSERT  INTO tutors  (Tname,Gender,Age)  SELECT Name,Gender,Age  FROM  stu WHERE  Age>20;  （方式三：从查询结果中添加）

>HELP  REPLACE （使用方法同INSERT INTO）

>UPDATE  tb_name SET  col1=’value’  WHERE condition;

例：>UPDATE stu  SET  Tname=’chai’ WHERE  SID=1;

>DELETE  FROM tb_name  WHERE  condition;

例：>DELETE FROM  stu  WHERE Tname=’chai’;

注：为防止误删数据，一定要加上条件。

>TRUNCATE  tb_name;  （清空表，并重置计数器）

>SELECT  [DISTINCT]  select_list FROM  tb_name  WHERE qualification;

注：DISTINCT相同的值只显示一次，表示独有的。

>SELECT  field1,field2 FROM  tb_name;  （投影）

>SELECT  * FROM  tb_name  WHERE qualification;  （选择）

查询语句类型：单表查询、多表查询、子查询

FROM子句：要查询的关系（表，多个表，其它的SELECT语句）

WHERE子句：

布尔关系表达式（真假比较操作）；

=/>/>=/<=/< 

不等于：<>或!=(这两种符号准确表示NOT EQUAL TO)、<=>（此方式正确表示NULL SAFE EQUAL TO）;

逻辑关系：AND(&&)、OR(||)、NOT(!)、XOR(异或，Exclusive OR);

BETWEEN.....AND....

%：任意长度，任意字符；

_：任意单个字符；

REGEXP或RLIKE：支持正则；

IN(*,*,......)；

IS  NULL：是空值用此种方式表示，不能写成‘’，‘’表示字符串的空串；

IS  NOT NULL：非空；

LIKE  ‘　’；

例：>SELECT *  FROM  stu WHERE  Age>20  AND Gender=’M’;

>SELECT  * FROM  stu  WHERE  Age+1>20; 

>SELECT  * FROM  stu  WHERE  NOT  Age>20;

>SELECT  * FROM  stu  WHERE  NOT  Age>20 AND  NOT  Gender=’M’;

>SELECT  * FROM  stu  WHERE  NOT  (Age>20 OR  NOT  Gender=’M’);

>SELECT  * FROM  stu  WHERE  Age>20  AND Age<=25;

>SELECT *  FROM  stu WHERE  Age  BETWEEN 20  AND  25;

>SELECT *  FROM  stu WHERE  Name  LIKE  ‘Y%’; 

>SELECT *  FROM  stu WHERE  Name  LIKE  ‘Y___’;

>SELECT *  FROM  stu WHERE  Name  LIKE  ‘%Y%’;

>SELECT *  FROM  stu WHERE  Name  RLIKE  ‘^[MNY].*$’; 

注：用正则索引会失效

>SELECT *  FROM  stu WHERE  Age  IN (18,25,20);

>SELECT *  FROM  stu WHERE  CID2  IS NOT  NULL;

>SELECT *  FROM  stu WHERE  CID2  IS NULL;

注：字符型的用单引号，数值型的不能用引号。

ORDER  BY column_name  [DESC|ASC]  （将查询的结果进行排序）

注：descending降序，ascending升序，不写默认升序。

例：>SELECT *  FROM  stu WHERE  Age>20  AND Gender=’M’　ORDER  BY  Name DESC;

AS  别名

例：>SELECT Name  AS  stu_name FROM  stu;  （字段名称是Name，但查询显示的结果是stu_name）

>SELECT  2+1;  （SELECT语句可直接进行算术运算）

>SELECT  2+1 AS  SUM;

LIMIT  [offset,] count  （offset偏移量，count取多少个）

例：>SELECT Name  FROM  stu LIMIT  2;  （不管有多少个符合条件的，只显示前两个）

>SELECT  Name FROM  stu  LIMIT 2,3;  （把前两个略过，只显示之后的连续３个）

聚合：SUM()、MIN()、MAX()、AVG()、COUNT()

例：>SELECT AVG(Age)  FROM  stu;  （计算表中所有人的平均年龄）

>SELECT  COUNT(Tname) FROM  stu;  （计算查询出的个数）

GROUP  BY column_name  HAVING  qualification;  （分组，目的做聚合函数用，进一步筛选用HAVING，且HAVING只能与GROUP  BY一起用）

例：>SELECT Age,Gender  FROM  stu GROUP  BY  Gender;

>SELECT  AVG(Age) FROM  stu  GROUP BY  Gender;

>SELECT  COUNT(CID1) AS  persons,CID1  FROM stu  GROUP  BY CID1  HAVING  persons>=2;

注：查询语句顺序：FROM-->WHERE-->GROUP  BY-->HAVING-->ORDER  BY-->LIMIT

多表查询（复合查询）：

连接：

交叉连接（笛卡尔积）：>SELECT *  FROM  stu,course;

自然连接（两个表的某字段有等值的）：

>SELECT  * FROM  stu,courses  WHERE stu.CID1=courses.CID1;

>SELECT  s.name,c.cname  FROM stu  AS  s,courses AS  c  WHERE s.CID1=c.CID1;

外连接：左外连接（……LEFT JOIN  ……ON……）；右外连接（……RIGHT  JOIN……ON……）

>SELECT  s.Name,c.Cname  FROM stu  AS  s LEFT  JOIN  courses AS  c  ON s.CID1=c.CID;

子查询：

>SELECT  Name FROM  stu  WHERE Age > (SELECT  AVG(Age)  FROM stu);

（比较操作中使用子查询，子查询语句只能返回单个值）

>SELECT  Name FROM  stu  WHERE Age  IN  (SELECT Age  FROM  tutors);

（在ＩＮ中使用子查询）

>SELECT  Name,Age FROM  (SELECT  Name,Age FROM  stu)  AS t  WHERE  t.Age>=20;

UNION联合查询：

>(SELECT  Name,Age FROM  stu)  UNION (SELECT  Tname,Age  FROM tutors);

视图（存储下来的SELECT语句，基于基表的查询结果）：

注：使用MySQL不建议使用视图

>HELP  CREATE VIEW

>CREATE  VIEW view_name  AS  select-statement;

例：>CREATE VIEW  stuview  AS SELECT  Name,Age  FROM stu;

>SHOW  TABLES;

>SELECT  * FROM  stuview;

>SHOW  TABLE STATUS\G

>SHOW  CREATE VIEW  stuview;

>DROP  VIEW stuview;

注：只要不违反基表中的数据规则，视图中可以插入数据，但不建议这么做。

物化视图（mysql不支持，适用于基表更新数据不多的情况）

本文转自 chaijowin 51CTO博客，原文链接：http://blog.51cto.com/jowin/1683288，如需转载请自行联系原作者