Linux运维 第四阶段 (三) MySQL的SQL语句

时间:2022-01-03 03:19:44

Linux运维 第四阶段 (三) MySQLSQL语句

SQL语句:

1、

 

#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,直接将对应的数据库目录改名。

 

2、表

>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字段的所有数据内容按前5个字符降序排列)

>SHOW  INDEXES FROM  stu;  (查看表的索引)

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

>HELP  DROP INDEX  (删除索引)

例:>DROP  INDEX name_on_stu  ON  stu;

 

 

3、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();  (查看自动增长型数据的状态)

注:例如表中有10条记录,把表内容全删除,下次添加时会自动从11开始添加,若要从头开始计数,则删除数据时使用>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....

%:任意长度,任意字符;

_:任意单个字符;

REGEXPRLIKE:支持正则;

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;  (把前两个略过,只显示之后的连续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);

(在IN中使用子查询)

>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不支持,适用于基表更新数据不多的情况)

 

 

 

本篇是学习《马哥网络视频》做的笔记。

 


本文出自 “Linux运维重难点学习笔记” 博客,谢绝转载!