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

zhangbinmy

　　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 >　　>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  CIDDESC  testcourses; （此方法创建的新表，字段属性将不存在）
　　>CREATE  TABLE ta_name  LIKE  old_ta_name; （方法三:使用旧表中的字段属性创建一张新表）
　　例：>CREATE TABLE  tb3  LIKE courses;
　　>DROP  TABLE tb1;  （删除表）

　　>HELP >　　例：>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子句：
　　布尔关系表达式（真假比较操作）；
　　=/>/>=/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 AgeSELECT *  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不支持，适用于基表更新数据不多的情况）
　　本篇是学习《马哥网络视频》做的笔记。