Linux运维 第四阶段 （三） MySQL的SQL语句
Linux运维 第四阶段 （三） MySQL的SQL语句SQL语句：
１、库
#manmysql
#mysql-uroot -p-e‘CREATE DATABASEtestdb;’
>HELPCREATE DATABASE（创建库）
>CREATEDATABASE|SCHEMAdb_name ;
例：>CREATE DATABASEIFNOT EXISTSstudents;（IF NOT EXISTS要创建的数据库不存在则创建，防止报错信息出现）
#cat/mydata/data/students/db.opt（此文件记录有字符集及排序规则，查看字符集>SHOW CHARACTER SET;查看排序规则>SHOW COLLATION;）
>HELP > >HELPDROP DATABASE
>DROPDATABASE db_name; （删除数据库）
注：一般不会给数据库重命名，如果要改名，把服务down，直接将对应的数据库目录改名。
２、表
>HELPCREATE TABLE（创建表）
>CREATETABLE ta_name(col_name col_defination,....CONSTRAINT) ;
例：>CREATE TABLEtb1( （方法一:直接定义一张空表）
idINT UNSIGNEDNOT NULLAUTO_INCREMENTPRIMARY KEY,
nameCHAR(20) NOTNULL,
ageTINYINT NOTNULL)ENGINE=MyISAM;
>SHOWTABLE STATUSLIKE‘tb1’\G （查看表的存储引擎）
>SHOWINDEXES FROMtb1;（查看表索引）
>CREATETABLE ta_nameselect-statement;（方法二:从其它表中查询出数据，并以之创建新表，仅创建查询出的数据，字段属性不复制）
例：>CREATE TABLEtb2SELECT *FROMcourses WHERECIDDESCtestcourses; （此方法创建的新表，字段属性将不存在）
>CREATETABLE ta_nameLIKEold_ta_name; （方法三:使用旧表中的字段属性创建一张新表）
例：>CREATE TABLEtb3LIKE courses;
>DROPTABLE tb1;（删除表）
>HELP > 例：>ALTER TABLEtestADD UNIQUEKEY(course);（给字段添加唯一键）
>ALTERTABLE testCHANGEcourse couVARCHA(50)NOT NULL;（更改字段名）
>ALTERTABLE testADDstartdate datedefault‘2015-08-10’ FIRST;（添加字段并赋予默认值，并置于第一个字段）
>ALTERTABLE testADDTname CHAR(10)NOTNULLAFTERstartdate; （添加字段并置于某一字段后）
>ALTERTABLE testRENAMETO testcourse;（改表名）
>RENAMETABLE testcourseTOtest;（改表名）
>ALTERTABLE stuADDFOREIGN KEY(CID)REFERENCESCourses(CID); （添加外键约束，之后往表stu里插入数据时字段CID一定要在表Courses(CID)有效范围内，否则会报错）
注：外键约束可防止误删数据，但消耗系统资源，一般不用。
>ALTERTABLE stuENGINES=InnoDB;（改表的存储引擎）
>SHOWTABLE STATUS\G（查看表的存储引擎等相关详细信息）
>SHOWCREATE TABLEstu;（查看创建表时的详细语句，如查询添加的外键名称）
>ALTERTABLE stuDROPFOREIGN KEYforeign_name;（删除外键约束）
>HELPCREATE INDEX（创建索引）
>CREATEINDEX index_nameONtb_name(index_column_name);
例：>CREATE INDEXname_on_stuUSING BTREEONstu(Name);
>CREATEINDEX name_on_stuUSINGBTREE ONstu(Name(5)DESC);（创建索引，Name字段的所有数据内容按前５个字符降序排列）
>SHOWINDEXES FROMstu;（查看表的索引）
注：索引只能创建、删除不能修改，如要修改则先删除再重新创建。
>HELPDROP INDEX（删除索引）
例：>DROPINDEX name_on_stuONstu;
３、DML(SELECT/INSERT INTO/UPDATE/DELETE)
>INSERTINTO tb_name(col1,col2,...)VALUES (val1,val2,....) [,(val1,val2,...)];
例：>INSERT INTOstu(Tname,Age) VALUES(‘jowin’,25);（方式一：仅插入一行数据）
>INSERTINTO stuVALUES(1,’chai’,25,’M’),(2,’jowin’,25,’M’),(3,’xiang’,23,’F’);（方式二：可一次添加多条记录，在有些场景下，批量插入可提高性能）
>SELECTLAST_INSERT_ID();（查看自动增长型数据的状态）
注：例如表中有１０条记录，把表内容全删除，下次添加时会自动从１１开始添加，若要从头开始计数，则删除数据时使用>TRUNCATEtb_name;
>INSERTINTO tutors(Tname,Gender,Age)SELECT Name,Gender,AgeFROMstu WHEREAge>20;（方式三：从查询结果中添加）
>HELPREPLACE （使用方法同INSERT INTO）
>UPDATEtb_name SETcol1=’value’WHERE condition;
例：>UPDATE stuSETTname=’chai’ WHERESID=1;
>DELETEFROM tb_nameWHEREcondition;
例：>DELETE FROMstuWHERE Tname=’chai’;
注：为防止误删数据，一定要加上条件。
>TRUNCATEtb_name;（清空表，并重置计数器）
>SELECTselect_list FROMtb_nameWHERE qualification;
注：DISTINCT相同的值只显示一次，表示独有的。
>SELECTfield1,field2 FROMtb_name;（投影）
>SELECT* FROMtb_nameWHERE qualification;（选择）
查询语句类型：单表查询、多表查询、子查询
FROM子句：要查询的关系（表，多个表，其它的SELECT语句）
WHERE子句：
布尔关系表达式（真假比较操作）；
=/>/>=/SELECT *FROMstu WHEREAge>20AND Gender=’M’;
>SELECT* FROMstuWHEREAge+1>20;
>SELECT* FROMstuWHERENOTAge>20;
>SELECT* FROMstuWHERENOTAge>20 ANDNOTGender=’M’;
>SELECT* FROMstuWHERENOT(Age>20 ORNOTGender=’M’);
>SELECT* FROMstuWHEREAge>20AND AgeSELECT *FROMstu WHEREAgeBETWEEN 20AND25;
>SELECT *FROMstu WHERENameLIKE‘Y%’;
>SELECT *FROMstu WHERENameLIKE‘Y___’;
>SELECT *FROMstu WHERENameLIKE‘%Y%’;
>SELECT *FROMstu WHERENameRLIKE‘^.*$’;
注：用正则索引会失效
>SELECT *FROMstu WHEREAgeIN (18,25,20);
>SELECT *FROMstu WHERECID2IS NOTNULL;
>SELECT *FROMstu WHERECID2IS NULL;
注：字符型的用单引号，数值型的不能用引号。
ORDERBY column_name（将查询的结果进行排序）
注：descending降序，ascending升序，不写默认升序。
例：>SELECT *FROMstu WHEREAge>20AND Gender=’M’ ORDERBYName DESC;
AS别名
例：>SELECT NameASstu_name FROMstu;（字段名称是Name，但查询显示的结果是stu_name）
>SELECT2+1;（SELECT语句可直接进行算术运算）
>SELECT2+1 ASSUM;
LIMIT count（offset偏移量，count取多少个）
例：>SELECT NameFROMstu LIMIT2;（不管有多少个符合条件的，只显示前两个）
>SELECTName FROMstuLIMIT 2,3;（把前两个略过，只显示之后的连续３个）
聚合：SUM()、MIN()、MAX()、AVG()、COUNT()
例：>SELECT AVG(Age)FROMstu;（计算表中所有人的平均年龄）
>SELECTCOUNT(Tname) FROMstu;（计算查询出的个数）
GROUPBY column_nameHAVINGqualification;（分组，目的做聚合函数用，进一步筛选用HAVING，且HAVING只能与GROUPBY一起用）
例：>SELECT Age,GenderFROMstu GROUPBYGender;
>SELECTAVG(Age) FROMstuGROUP BYGender;
>SELECTCOUNT(CID1) ASpersons,CID1FROM stuGROUPBY CID1HAVINGpersons>=2;
注：查询语句顺序：FROM-->WHERE-->GROUPBY-->HAVING-->ORDERBY-->LIMIT
多表查询（复合查询）：
连接：
交叉连接（笛卡尔积）：>SELECT *FROMstu,course;
自然连接（两个表的某字段有等值的）：
>SELECT* FROMstu,coursesWHERE stu.CID1=courses.CID1;
>SELECTs.name,c.cnameFROM stuASs,courses AScWHERE s.CID1=c.CID1;
外连接：左外连接（……LEFT JOIN……ON……）；右外连接（……RIGHTJOIN……ON……）
>SELECTs.Name,c.CnameFROM stuASs LEFTJOINcourses AScON s.CID1=c.CID;
子查询：
>SELECTName FROMstuWHERE Age > (SELECTAVG(Age)FROM stu);
（比较操作中使用子查询，子查询语句只能返回单个值）
>SELECTName FROMstuWHERE AgeIN(SELECT AgeFROMtutors);
（在ＩＮ中使用子查询）
>SELECTName,Age FROM(SELECTName,Age FROMstu)AS tWHEREt.Age>=20;
UNION联合查询：
>(SELECTName,Age FROMstu)UNION (SELECTTname,AgeFROM tutors);
视图（存储下来的SELECT语句，基于基表的查询结果）：
注：使用MySQL不建议使用视图
>HELPCREATE VIEW
>CREATEVIEW view_nameASselect-statement;
例：>CREATE VIEWstuviewAS SELECTName,AgeFROM stu;
>SHOWTABLES;
>SELECT* FROMstuview;
>SHOWTABLE STATUS\G
>SHOWCREATE VIEWstuview;
>DROPVIEW stuview;
注：只要不违反基表中的数据规则，视图中可以插入数据，但不建议这么做。
物化视图（mysql不支持，适用于基表更新数据不多的情况）
本篇是学习《马哥网络视频》做的笔记。
页:
[1]