MySQL总结

一、理论背景1.1不同角色的人对数据库知识学习的侧重点•研发人员(测试及开发人员)：主要学习如何操作数据库的逻辑对象，如表、索引和视图等，以得到应用程序中需要的数据。•数据库管理员(DBA)：要学习和掌握数据库的全部内容，包括数据库对象管理、性能优化、备份与恢复、安全管理等软件测试在什么时候会用到数据库的知识？•搭建测试环境•通用数据库操作功能的测试(增、删、改、查等)•快速创建测试数据，提高测试工作效率关系数据模型的基本数据结构1.2DDL和DML1.2.1SQL简介•结构化查询语言(StructuredQueryLanguage)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，同时也是数据库脚本文件的扩展名•编写规范1)sql语句的所有表名、字段名全部小写，系统保留字、内置函数名、sql保留字大写。2)连接符or、in、and、以及＝、=、=等前后加上一个空格。3)对较为复杂的sql语句、过程、函数加上注释，说明算法、功能。4)多表连接时，使用表的别名来引用列主要掌握查询语句语言分类名称详细描述1、数据查询语句(DQL)select数据查询2、数据操纵语句(DML)InsertUpdateDelete插入更新删除3、数据定义语句(DDL)CreateAlterDropRenameTruncate新建修改结构删除重命名删除4、事务控制语句(TC)CommitRollbackSavepoint提交事务回滚存储点5、数据控制语言(DCL)GrantRevoke授权解除授权1.2.2DDL与DMLDDL（数据定义语言）是用来定义数据结构，完成数据库对象的创建（CREATE）、修改（ALTER）、删除（DROP）数据库对象包括：数据库实例，数据表，视图，索引，约束，存储过程DML（数据操纵语言）是用于维护数据表中的具体数据，完成数据的增加（INSERT）、更新（UPDATE）、删除（DELETE）1、MySQL支持的数据类型MySQL数据库支持的数据类型：整型、浮点型、字符串、日期和时间字符串数据类型通常定义为varchar，输入字符串时必须加””或者’’日期和时间数据类型输入值时必须加’’2、数据表约束a.主键约束b.外键约束定义外键约束：FOREIGNKEY指定test_table1表中的属性name为外键约束REFERENCES关联父表test_table2中的主键属性nONDELETE、ONUPDATE指明父表中对主键进行删除、更新操作，不指定则表示不能进行删除、更新操作CASCADE表明子表中外键字段值也会被更新或删除，除了CASCADE，还可以使用SETNULL（父表更新或删除时，子表字段值设置为NULL）、NOACTION（父表更新或删除时，子表不进行任何操作）c.唯一约束d.非空约束NOTNULLe.默认值约束defaultCREATETABLEtest_table(idINTPRIMARYKEY,nameVARCHAR(20),ageINTDEFAULT20);f.AUTO_INCREMENT当属性列定义为AUTO_INCREMENT时，能够为新插入的行赋值为上一次插入的值+1,MySQL要求AUTO_INCREMENT只能用于作为主键的列CREATETABLEtest_table(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(20));数据表中的属性可以同时定义多个约束，但是需要注意：-如果同一个属性有默认值约束和其他约束，默认值要写在其他约束前面-如果同一个属性有多个约束，中间使用空格分隔开二、常用命令-database，table1、数据库createdatabasetest07#新建数据库test07showDATABASES#查看已创建的数据库usetest07#使用数据库createdatabasenew07defaultcharactersetutf8COLLATEutf8_general_cicreatedatabasetesting07defaultcharactersetutf8#新建数据库的时候，指定字符集showcharacterset#查看mysql支持的字符集showcollation#查看MySQL字符集支持的校对集命令ALTERDATABASE数据库名称DEFAULTCHARACTER字符集COLLATE校对集#使用alter命令来完成数据库的修改，一般是修改数据库的字符集和校对集#删除数据库语法：DROPDATABASE数据库名称例子：DROPDATABASEtest_db2、数据表createTABLE表名#新建数据表例：usetest07createTABLEstudent(idintprimarykey,namevarchar(20)notnull,ageint,sexvarchar(10),xivarchar(20),gradeint)showtables#查看已存在的数据表desc表名#查看数据表的属性select*from表名[where条件子句]#查看表的内容#新增属性ALTERTABLE表名ADD属性名称数据类型[约束]ALTERTABLEtest_tableADDnameVARCHAR(20)NOTNULL;#修改属性数据类型，名称（change）语法：ALTERTABLE表名MODIFY属性名称数据类型[约束]语法：ALTERTABLE表名CHANGE属性名称属性名称数据类型[约束]例子：ALTERTABLEtest_tableMODIFYnameVARCHAR(20);ALTERTABLEtest_tableCHANGEnamenameVARCHAR(20);#新增属性约束fk可加可不加语法：ALTERTABLE表名ADDCONSTRAINT约束别名约束（属性）例：ALTERTABLEtest_tableADDCONSTRAINTPRIMARYKEY(id);ALTERTABLEtest_tableADDCONSTRAINTfkFOREIGNKEYtest_table(name)REFERENCEStest_table2(name)删除属性约束语法：ALTERTABLE表名DROP约束例子：ALTERTABLEtest_tableDROPPRIMARYKEY;ALTERTABLEtest_table1DROPFOREIGNKEYfk;#修改表名语法：ALTERTABLE表名RENAMETO新表名语法：RENAMETABLE原表名TO新表名例子：ALTERTABLEtest_tableRENAMETOtest_table_newRENAMETABLEtest_tableTOtest_table_new#删除数据表语法：DROPTABLE表名运行速度：droptruncatedelete语法：deletefrom表名语法：truncate表名例子：DROPTABLEtest_table#数据插入语法：INSERTINTO表名(属性1,属性2,……)VALUES(值1,值2,……)例子：INSERTINTOtest_table(id,name)VALUES(1,'zhangsan')向数据表中插入数据时，值与属性要一一对应，且数据类型要匹配还需要注意属性的约束要满足。当使用INSERT语句向表中插入数据时，如果不指定具体属性，则表示向所有属性中插入数据#特殊用法：将一张表中的数据插入到另外一张表语法：INSERTINTO表名1(属性1,….)SELECT表名2.属性1,….FROM表名2例子：INSERTINTOtest_table1SELECTid,nameFROMtest_table;需要注意，在当前这种用法中，SELECT语句查询到的属性值和向表1中插入的属性要一一对应，且数据类型要相同，属性约束要满足只删除数据，不删除表的结构和定义，truncate系统资源占用少#数据更新语法：UPDATE表名SET属性1=新值1,属性2=新值2,….[WHERE条件子句]例子：UPDATEtest_tableSETname='lisi'WHEREid=1;UPDATE语句会将数据表中满足WHERE条件的对应元组的属性1、属性2…更新为新的值，如果后面不接WHERE条件子句，则会更新整张表的所有数据#数据删除语法：DELETEFROM表名[WHERE条件子句]例子：DELETEFROMtest_tableWHEREid=1;DELETE语句会将数据表中满足WHERE条件的对应元组删除如果后面不接WHERE条件子句，则会删除数据表中的所有数据三、DQL查询语句-select基本语法：SELECT属性1,属性2,….FROM表名通配符*代表所有使用SELECT语句进行数据查询时，还可以进行算术运算、限定查询、排序、分组1、算术运算比较运算：in有时可以用or替代%代表若干个字符，_代表一个字符，like‘%经_’逻辑运算符：！后面括号不能省略and后面还有逻辑运算符时，例如and(条件1or条件2),注意加上(),避免查询结果有重复的运算符优先级：数学运算符比较运算符逻辑运算符注意：在WHERE子句中字符串和日期要用引号括起来对字符串不区分大小写日期值是格式敏感的2、限定查询限定结果-LIMIT使用LIMIT子句可以限定查询返回的记录数例如：查询数据表中前两条记录SQL语句：SELECT*FROMhzdl_empLIMIT2;例子：SELECT*FROMhzdl_empLIMIT2,3LIMIT子句后面可以有一个参数，也可以有两个参数，如果有一个参数，该参数表示从表开始位置查询对应的记录数量，如果有两个参数，那么第一个参数表示起始位置与第一行的偏移量，第二个参数表示查询的数量去除重复-DISTINCT使用DISTINCT关键字能够将查询到的结果集中重复的记录去除例子：SELECTdeptnoFROMhzdl_empSELECTDISTINCTdeptnoFROMhzdl_emp设置别名-AS使用AS关键字可以在查询时设置属性（列）的别名，不过AS可以省略SELECTempnoAS'员工号',enameAS'员工姓名'FROMhzdl_emp;SELECTempno'员工号',ename'员工姓名'FROMhzdl_emp;引号无所谓加不加3、排序使用ORDERBY子句可以将SELECT查询到的结果集按照指定属性排序默认采用升序（ASC）排序方式，也可以使用降序（DESC）例子：SELECT*FROMhzdl_empORDERBYdeptno;SELECT*FROMhzdl_empORDERBYdeptnoASC;SELECT*FROMhzdl_empORDERBYdeptnoDESC;同样，排序时也可以指定多个属性排序当多属性排序方式有冲突时，按照指定先后顺序排序例子：SELECT*FROMhzdl_empORDERBYdeptno,jobno;SELECT*FROMhzdl_empORDERBYdeptno,jobnoDESC4、分组-GROUPBY一般和聚合函数一起使用。常见聚合函数有：-COUNT计数函数，统计表中某个属性对应的记录数-AVG求平均值函数，统计表中某个属性对应的平均值-SUM求和函数SUM，统计表中某个属性的汇总值（和）-MAX最大值函数-MIN最小值函数-GROUP_CONCAT列值拼接函数，将表中某个属性的所有值拼接到同一个结果中，默认值与值之间用逗号分隔。也可以使用SEPARATOR关键字指定分隔符。只能拼接字符型，如果要拼接其他类型，需用cast(xastype)函数转换。SELECTGROUP_CONCAT(jobnameSEPARATOR'+')FROMhzdl_job和分组GROUPBY子句经常一起使用的还有HAVING子句，和where子句类似，它们的区别是：-WHERE子句在查询前限定查询条件-