MYSQL经典基础教程课件

高级软件人才实作培训专家高级软件人才实作培训专家!SQL SQL 入门入门IT资讯交流网WWW.IT315.ORGSQL入门IT资讯交流网WWW.IT315.ORG1高级软件人才实作培训专家高级软件人才实作培训专家!MySQLMySQL简介简介l数据库简介SQL Server、Oracle、MySQL、DB2、SyBaselMySQL数据库的安装和配置l使用命令行窗口连接MYSQL数据库mysql u用户名 p密码MySQL简介数据库简介2高级软件人才实作培训专家高级软件人才实作培训专家!数据库服务器、数据库和表的关系数据库服务器、数据库和表的关系MySQLDBDB表表表Clientl所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。l为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。l数据库服务器、数据库和表的关系如图所示：IT资讯交流网WWW.IT315.ORG数据库服务器、数据库和表的关系MySQLDBDB表表表Cli3高级软件人才实作培训专家高级软件人才实作培训专家!数据在数据库中的存储方式数据在数据库中的存储方式id=1name=“lisi”age=23User对象idnameage1lisi232wang24User表id=2name=“wang”age=24行(row)列(column)l表的一行称之为一条记录l表中一条记录对应一个java对象的数据User对象IT资讯交流网WWW.IT315.ORG数据在数据库中的存储方式id=1User对象idnameag4高级软件人才实作培训专家高级软件人才实作培训专家!创建数据库CREATEDATABASEIFNOTEXISTSdb_namecreate_specification,create_specification.create_specification:DEFAULTCHARACTERSETcharset_name|DEFAULTCOLLATEcollation_namelCHARACTERSET：指定数据库采用的字符集lCOLLATE：指定数据库字符集的比较方式l练习：创建一个名称为mydb1的数据库。创建一个使用utf-8字符集的mydb2数据库。创建一个使用utf-8字符集，并带校对规则的mydb3数据库。创建数据库CREATEDATABASEIFNOT5高级软件人才实作培训专家高级软件人才实作培训专家!查看、删除数据库显示数据库语句：SHOWDATABASES显示数据库创建语句：SHOWCREATEDATABASEdb_name数据库删除语句：DROPDATABASEIFEXISTSdb_namel练习：查看当前数据库服务器中的所有数据库查看前面创建的mydb2数据库的定义信息删除前面创建的mydb1数据库查看、删除数据库显示数据库语句：练习：6高级软件人才实作培训专家高级软件人才实作培训专家!修改、备份、恢复数据库ALTERDATABASEIFNOTEXISTSdb_namealter_specification,alter_specification.alter_specification:DEFAULTCHARACTERSETcharset_name|DEFAULTCOLLATEcollation_namel备份数据库表中的数据mysqldump-u用户名-p数据库名文件名.sqll恢复数据库Source文件名.sqll练习l查看服务器中的数据库，并把其中某一个库的字符集修改为utf8;l备份test库中的数据，并恢复修改、备份、恢复数据库ALTERDATABASEI7高级软件人才实作培训专家高级软件人才实作培训专家!创建表(基本语句)CREATETABLEtable_name(field1datatype,field2datatype,field3datatype,)characterset字符集字符集collate校对规则校对规则field：指定列名datatype：指定列类型l注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user对象idintnamestringpasswordstringbirthdaydateIdNamePasswordbirthdayl注意：创建表前，要先使用usedb语句使用库。创建表(基本语句)CREATETABLEtable_na8高级软件人才实作培训专家高级软件人才实作培训专家!MySQL常用数据类型lVARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。分类数据类型说明数值类型BIT(M)TINYINTUNSIGNEDZEROFILLBOOL，BOOLEANSMALLINTUNSIGNEDZEROFILLINTUNSIGNEDZEROFILLBIGINTUNSIGNEDZEROFILLFLOAT(M,D)UNSIGNEDZEROFILLDOUBLE(M,D)UNSIGNEDZEROFILL位类型。M指定位数，默认值1，范围1-64带符号的范围是-128到127。无符号0到255。使用0或1表示真或假2的16次方2的32次方2的64次方M指定显示长度，d指定小数位数表示比float精度更大的小数文本、二进制类型CHAR(size)char(20)VARCHAR(size)varchar(20)BLOBLONGBLOBTEXT(clob)LONGTEXT(longclob)固定长度字符串可变长度字符串二进制数据大文本时间日期DATE/DATETIME/TimeStamp日期类型(YYYY-MM-DD)(YYYY-MM-DDHH:MM:SS)，TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间MySQL常用数据类型VARCHAR、BLOB和TEXT类是9高级软件人才实作培训专家高级软件人才实作培训专家!创建表练习l创建一个员工表字段属性Id整形name字符型sex字符型或bit型brithday日期型Entry_date日期型job字符型Salary小数型resume大文本型创建表练习创建一个员工表字段属性Id整形name字符型sex10高级软件人才实作培训专家高级软件人才实作培训专家!修改表使用ALTERTABLE语句追加,修改,或删除列的语法.ALTERTABLEtableADD(column datatype DEFAULTexpr,column datatype.);ALTERTABLEtableMODIFY(column datatype DEFAULTexpr,column datatype.);ALTERTABLEtableDROP(column);修改表的名称：Renametable表名to新表名修改表的字符集：altertablestudentcharactersetutf8;修改表使用ALTERTABLE 语句追加,修改,或删11高级软件人才实作培训专家高级软件人才实作培训专家!修改表l练习在上面员工表的基本上增加一个image列。修改job列，使其长度为60。删除sex列。表名改为user。修改表的字符集为utf-8列名name修改为usernamealtertableuserchangecolumnnameusernamevarchar(20);IT资讯交流网WWW.IT315.ORG修改表练习IT资讯交流网WWW.IT315.ORG12高级软件人才实作培训专家高级软件人才实作培训专家!IT资讯交流网WWW.IT315.ORGIT资讯交流网WWW.IT315.ORG13高级软件人才实作培训专家高级软件人才实作培训专家!数据库CRUD语句lInsert语句(增加数据)lUpdate语句(更新数据)lDelete语句(删除数据)lSelect语句(查找数据)IT资讯交流网WWW.IT315.ORG数据库CRUD语句Insert语句(增加数据)IT资14高级软件人才实作培训专家高级软件人才实作培训专家!Insert语句INSERTINTOtable(column,column.)VALUES(value,value.);l使用INSERT语句向表中插入数据。l插入的数据应与字段的数据类型相同。l数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。l在values中列出的数据位置必须与被加入的列的排列位置相对应。l字符和日期型数据应包含在字符和日期型数据应包含在单引号单引号中。中。l插入空值，不指定或insertintotablevalue(null)IT资讯交流网WWW.IT315.ORGInsert语句INSERTINTOtable(co15高级软件人才实作培训专家高级软件人才实作培训专家!Insert语句练习l练习：使用insert语句向表中插入三个员工的信息。l注意：字符和日期要包含在单引号中。lshowvariableslikecharacter%;lsetcharacter_set_results=gbk;IT资讯交流网WWW.IT315.ORGInsert语句练习练习：使用insert语句向表中插入三个16高级软件人才实作培训专家高级软件人才实作培训专家!Update语句UPDATE tbl_nameSETcol_name1=expr1,col_name2=expr2.WHEREwhere_definitionl使用update语句修改表中数据。lUPDATE语法可以用新值更新原有表行中的各列。lSET子句指示要修改哪些列和要给予哪些值。lWHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。IT资讯交流网WWW.IT315.ORGUpdate语句UPDATEtbl_name使用17高级软件人才实作培训专家高级软件人才实作培训专家!Update语句练习l练习：在上面创建的employee表中修改表中的纪录。l要求l将所有员工薪水修改为5000元。l将姓名为zs的员工薪水修改为3000元。l将姓名为aaa的员工薪水修改为4000元,job改为ccc。l将wu的薪水在原有基础上增加1000元。IT资讯交流网WWW.IT315.ORGUpdate语句练习练习：在上面创建的employee表中修18高级软件人才实作培训专家高级软件人才实作培训专家!Delete语句deletefromtbl_nameWHEREwhere_definitionl使用delete语句删除表中数据。l如果不使用where子句，将删除表中所有数据。lDelete语句不能删除某一列的值（可使用update）l使用delete语句仅删除记录，不删除表本身。如要删除表，使用droptable语句。l同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。l删除表中数据也可使用TRUNCATETABLE语句，它和delete有所不同，参看mysql文档。IT资讯交流网WWW.IT315.ORGDelete语句deletefromtbl_name19高级软件人才实作培训专家高级软件人才实作培训专家!Delete语句练习l删除表中名称为zs的记录。l删除表中所有记录。l使用truncate删除表中记录。IT资讯交流网WWW.IT315.ORGDelete语句练习删除表中名称为zs的记录。IT资讯交20高级软件人才实作培训专家高级软件人才实作培训专家!Select语句(1)SELECTDISTINCT*|column1,column2.column3.FROMtable;lSelect指定查询哪些列的数据。lcolumn指定列名。l*号代表查询所有列。lFrom指定查询哪张表。lDISTINCT可选，指显示结果时，是否剔除重复数据l基本select语句IT资讯交流网WWW.IT315.ORGSelect语句(1)SELECTDISTINCT*21高级软件人才实作培训专家高级软件人才实作培训专家!Select语句(1)l练习：l查询表中所有学生的信息。l查询表中所有学生的姓名和对应的英语成绩。l过滤表中重复数据。IT资讯交流网WWW.IT315.ORGSelect语句(1)练习：IT资讯交流网WWW.IT3122高级软件人才实作培训专家高级软件人才实作培训专家!Select语句()SELECT*|column1expression,column2expression，.FROMtable;l在select语句中可使用表达式对查询的列进行运算l在select语句中可使用as语句SELECTcolumnas别名别名from表名表名;IT资讯交流网WWW.IT315.ORGSelect语句()SELECT*|column1e23高级软件人才实作培训专家高级软件人才实作培训专家!Select语句()l练习l在所有学生分数上加10分特长分。l统计每个学生的总分。l使用别名表示学生分数。IT资讯交流网WWW.IT315.ORGSelect语句()练习IT资讯交流网WWW.IT31524高级软件人才实作培训专家高级软件人才实作培训专家!Select语句(3)l使用where子句，进行过滤查询。练习：l查询姓名为wu的学生成绩l查询英语成绩大于90分的同学l查询总分大于200分的所有同学IT资讯交流网WWW.IT315.ORGSelect语句(3)使用where子句，进行过滤查询。练习25高级软件人才实作培训专家高级软件人才实作培训专家!Select语句()l在where子句中经常使用的运算符比较运算符=大于、小于、大于(小于)等于、不等于BETWEEN.AND.显示在某一区间的值IN(set)显示在in列表中的值，例：in(100,200)LIKE张张pattern模糊查询ISNULL判断是否为空逻辑运算符and多个条件同时成立or多个条件任一成立not不成立，例：wherenot(salary100);Like语句中，%代表零个或多个任意字符，_代表一个字符，例first_namelike_a%;IT资讯交流网WWW.IT315.ORGSelect语句()在where子句中经常使用的运算符26高级软件人才实作培训专家高级软件人才实作培训专家!Select语句(4)l查询英语分数在8090之间的同学。l查询数学分数为89,90,91的同学。l查询所有姓李的学生成绩。l查询数学分80，语文分80的同学。IT资讯交流网WWW.IT315.ORGSelect语句(4)查询英语分数在8090之间的同学。27高级软件人才实作培训专家高级软件人才实作培训专家!Select语句()SELECTcolumn1,column2.column3.FROMtable;order by column asc|desclOrder by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。lAsc 升序、Desc 降序lORDER BY 子句应位于SELECT语句的结尾。l练习：l对数学成绩排序后输出。l对总分排序后输出，然后再按从高到低的顺序输出l对姓李的学生成绩排序输出l使用orderby子句排序查询结果。IT资讯交流网WWW.IT315.ORGSelect语句()SELECTcolumn1,col28高级软件人才实作培训专家高级软件人才实作培训专家!合计函数countSelect count(*)|count(列名列名)from tablenameWHEREwhere_definition l练习：l统计一个班级共有多少学生？l统计数学成绩大于90的学生有多少个？l统计总分大于250的人数有多少？lCount(列名)返回某一列，行的总数IT资讯交流网WWW.IT315.ORG合计函数countSelectcount(*)|coun29高级软件人才实作培训专家高级软件人才实作培训专家!合计函数SUMSelect sum(列名列名),sum(列名列名)from tablenameWHEREwhere_definition l练习：l统计一个班级数学总成绩？l统计一个班级语文、英语、数学各科的总成绩l统计一个班级语文、英语、数学的成绩总和l统计一个班级语文成绩平均分l注意：sum仅对数值起作用，否则会报错。l注意：对多列求和，“，”号不能少。lSum函数返回满足where条件的行的和IT资讯交流网WWW.IT315.ORG合计函数SUMSelectsum(列名),sum(列名30高级软件人才实作培训专家高级软件人才实作培训专家!合计函数AVGSelect sum(列名列名),sum(列名列名)from tablenameWHEREwhere_definition l练习：l求一个班级数学平均分？l求一个班级总分平均分lAVG函数返回满足where条件的一列的平均值IT资讯交流网WWW.IT315.ORG合计函数AVGSelectsum(列名),sum(列名31高级软件人才实作培训专家高级软件人才实作培训专家!合计函数MAX/MINSelect max(列名列名)from tablenameWHEREwhere_definition l练习：l求班级最高分和最低分（数值范围在统计中特别有用）lMax/min函数返回满足where条件的一列的最大/最小值IT资讯交流网WWW.IT315.ORG合计函数MAX/MINSelectmax(列名)fro32高级软件人才实作培训专家高级软件人才实作培训专家!Select语句(6)SELECTcolumn1,column2.column3.FROM table;group by columnl练习：对订单表中商品归类后，显示每一类商品的总价l使用groupby子句对列进行分组SELECTcolumn1,column2.column3.FROMtable;group by column having.l使用having子句过滤l练习：查询购买了几类商品，并且每类总价大于100的商品lHaving和和where均可实现过滤，但在均可实现过滤，但在having可以使用合计函数可以使用合计函数,having通常跟在通常跟在groupby后，它作用于组。后，它作用于组。IT资讯交流网WWW.IT315.ORGSelect语句(6)SELECTcolumn1,col33高级软件人才实作培训专家高级软件人才实作培训专家!时间日期相关函数ADDTIME(date2,time_interval)将将time_interval加到加到date2CURRENT_DATE()当前日期当前日期CURRENT_TIME()当前当前时间CURRENT_TIMESTAMP()当前时间戳当前时间戳DATE(datetime)返回返回datetime的日期部分的日期部分DATE_ADD(date2,INTERVALd_valued_type)在在date2中加上日期或中加上日期或时间DATE_SUB(date2,INTERVALd_valued_type)在在date2上减去一个上减去一个时间DATEDIFF(date1,date2)两个日期差两个日期差NOW()当前当前时间YEAR|Month|DATE(datetime)年月日年月日示例：selectaddtime(02:30:30,01:01:01);注意：字符串、时间日期的引号问题selectdate_add(entry_date,INTERVAL2year)fromstudent;增加两年selectaddtime(time,11-110:09:09)fromstudent;时间戳上增加，注意年后没有-IT资讯交流网WWW.IT315.ORG时间日期相关函数ADDTIME(date2,time_i34高级软件人才实作培训专家高级软件人才实作培训专家!字符串相关函数CHARSET(str)返回字串字符集返回字串字符集CONCAT(string2,.)连接字串接字串INSTR(string,substring)返回返回substring在在string中出中出现的位置的位置,没有返回没有返回0UCASE(string2)转换成大写成大写LCASE(string2)转换成小写成小写LEFT(string2,length)从从string2中的左中的左边起取起取length个字符个字符LENGTH(string)string长度度REPLACE(str,search_str,replace_str)在在str中用中用replace_str替替换search_strSTRCMP(string1,string2)逐字符比逐字符比较两字串大小两字串大小,SUBSTRING(str,position,length)从从str的的position开始开始,取取length个字符个字符LTRIM(string2)RTRIM(string2)trim去除前端空格或后端空格去除前端空格或后端空格IT资讯交流网WWW.IT315.ORG字符串相关函数CHARSET(str)返回字串字符集CONC35高级软件人才实作培训专家高级软件人才实作培训专家!数学相关函数ABS(number2)绝对值BIN(decimal_number)十十进制制转二二进制制CEILING(number2)向上取整向上取整CONV(number2,from_base,to_base)进制制转换FLOOR(number2)向下取整向下取整FORMAT(number,decimal_places)保留小数位数保留小数位数HEX(DecimalNumber)转十六十六进制制LEAST(number,number2,.)求最小求最小值MOD(numerator,denominator)求余求余RAND(seed)RAND(seed)IT资讯交流网WWW.IT315.ORG数学相关函数ABS(number2)绝对值BIN(de36高级软件人才实作培训专家高级软件人才实作培训专家!定义表的约束l定义主键约束lprimarykey:不允许为空，不允许重复l删除主键：altertabletablenamedropprimarykey;l定义主键自动增长lauto_incrementl定义唯一约束luniquel定义非空约束lnotnulll定义外键约束lconstraintordersid_FKforeignkey(ordersid)referencesorders(id),定义表的约束定义主键约束37高级软件人才实作培训专家高级软件人才实作培训专家!Tip：mysql中文乱码中文乱码lmysql有六处使用了字符集，分别为：client、connection、database、results、server、system。client是客户端使用的字符集。connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。server是服务器安装时指定的默认字符集设定。system是数据库系统使用的字符集设定。IT资讯交流网WWW.IT315.ORGTip：mysql中文乱码mysql有六处使用了字符集，分别38