SQL入门-职业技能培训

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,SQL,入门,上海浦东软件园职业技能培训,MySQL简介,数据库简介,SQL Server、Oracle、,MySQL、DB2、,SyBase,MySQL数据库的安装和配置,使用命令行窗口连接MYSQL数据库,mysql,u用户名,p密码,数据库服务器、数据库和表的关系,MySQL,DB,DB,表,表,表,Client,所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。,为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。,数据库服务器、数据库和表的关系如图所示：,数据在数据库中的存储方式,id=1,name=“lisi”,age=23,User对象,id,name,age,1,lisi,23,2,wang,24,User表,id=2,name=“wang”,age=24,行,(row),列,(column),表的一行称之为一条记录,表中一条记录对应一个,java对象的数据,User对象,创建数据库,CREATE DATABASE IF NOT EXISTS,db_name,create_specification,create_specification, .,create_specification,:,DEFAULT CHARACTER SET,charset_name,| DEFAULT COLLATE,collation_name,CHARACTER SET：指定数据库采用的字符集,COLLATE：指定数据库字符集的比较方式、规则，比如排序时,练习：,创建一个名称为,mydb1的数据库。,创建一个使用utf-8字符集的mydb2数据库。,创建一个使用,utf-8字符集，并带校对规则的mydb3数据库。,查看、删除数据库,显示数据库语句：,SHOW DATABASES,显示数据库创建语句：,SHOW CREATE DATABASE,db_name,数据库删除语句：,DROP DATABASE IF EXISTS,db_name,练习：,查看当前数据库服务器中的所有数据库,查看前面创建的,mydb2数据库的定义信息,删除前面创建的mydb1数据库,修改、备份、恢复数据库,ALTER DATABASE IF NOT EXISTS,db_name,alter_specification,alter_specification, .,alter_specification,:,DEFAULT CHARACTER SET,charset_name,| DEFAULT COLLATE,collation_name,备份数据库表中的数据,mysqldump -u 用户名 -p 数据库名 文件名.sql,恢复数据库,Source 文件名.sql,练习,查看服务器中的数据库，并把其中某一个库的字符集修改为utf8;,备份test库中的数据，并恢复,创建表,(基本语句),CREATE TABLE table_name,(,field1 datatype,field2 datatype,field3 datatype,)character set 字符集 collate 校对规则,field,：指定列名,datatype,：指定列类型,注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：,user对象,id int,name string,password string,birthday date,Id,Name,Password,birthday,注意：创建表前，要先使用,use db语句使用库。,MySQL常用数据类型,VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。,分类,数据类型,说明,数值类型,BIT(M),TINYINT UNSIGNED ZEROFILL,BOOL，BOOLEAN,SMALLINT UNSIGNED ZEROFILL,INT UNSIGNED ZEROFILL,BIGINT UNSIGNED ZEROFILL,FLOAT(,M,D,) UNSIGNED ZEROFILL,DOUBLE(,M,D,) UNSIGNED ZEROFILL,位类型。,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),BLOB LONGBLOB,TEXT(clob) LONGTEXT(longclob),固定长度字符串,可变长度字符串,二进制数据,大文本,时间日期,DATE/DATETIME/TimeStamp,日期类型,(YYYY-MM-DD) (YYYY-MM-DD HH:MM:SS)，TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间,创建表练习,创建一个员工表,字段,属性,Id,整形,name,字符型,sex,字符型或,bit型,brithday,日期型,Entry_date,日期型,job,字符型,Salary,小数型,resume,大文本型,修改表,使用,ALTER TABLE 语句追加, 修改, 或删除列的语法.,ALTER TABLE,table,ADD,(,column datatype,DEFAULT,expr,column datatype,.);,ALTER TABLE,table,MODIFY,(,column datatype,DEFAULT,expr,column datatype,.);,ALTER TABLE,table,DROP,(,column,);,修改表的名称：,Rename table,表名,to,新表名,修改表的字符集：alter table student character set utf8;,修改表,练习,在上面员工表的基本上增加一个,image列。,修改job列，使其长度为60。,删除sex列。,表名改为user。,修改表的字符集为utf-8,列名name修改为username,alter table user,change,column name username varchar(20);,数据库,CRUD语句,Insert语句 (增加数据),Update语句 (更新数据),Delete语句 (删除数据),Select语句(查找数据),Insert语句,INSERT INTO,table,(,column, column.,),VALUES,(value, value.,);,使用,INSERT 语句向表中插入数据。,插入的数据应与字段的数据类型相同。,数据的大小应在列的规定范围内，例如：不能将一个长度为,80的字符串加入到长度为40的列中。,在values中列出的数据位置必须与被加入的列的排列位置相对应。,字符和日期型数据应包含在单引号中。,插入空值，不指定或,insert into table value(null),Insert语句练习,练习：使用,insert语句向表中插入三个员工的信息。,字段名,字段类型,id,整形,name,字符串型,sex,字符或整数类型,birthday,日期型,salary,浮点型,entry_date,日期型,resume,大文本型,Update语句,UPDATE,tbl_name,SET,col_name1,=,expr1,col_name2,=,expr2,.,WHERE,where_definition,使用,update语句修改表中数据。,UPDATE语法可以用新值更新原有表行中的各列。,SET子句指示要修改哪些列和要给予哪些值。,WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。,Update语句练习,练习：在上面创建的,employee表中修改表中的纪录。,要求,将所有员工薪水修改为,5000元。,将姓名为zs的员工薪水修改为3000元。,将wu的薪水在原有基础上增加1000元。,Delete语句,delete from,tbl_name,WHERE,where_definition,使用,delete语句删除表中数据。,如果不使用,where子句，将删除表中所有数据。,Delete语句不能删除某一列的值（可使用update）,使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。,同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。,删除表中数据也可使用,TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。,Delete语句练习,删除表中名称为,zs,的记录。,删除表中所有记录。,使用,truncate删除表中记录。,Select语句(1),SELECT,DISTINCT,*|,column,1,column,2,.,column,3,.,FROM,table;,Select 指定查询哪些列的数据。,column指定列名。,*号代表查询所有列。,From指定查询哪张表。,DISTINCT,可选，指显示结果时，是否剔除重复数据,基本,select语句,Select语句(1),练习：,查询表中所有学生的信息。,查询表中所有学生的姓名和对应的英语成绩。,过滤表中重复数据。,Select语句(),SELECT,*|,column,1,expression,column,2,expression,，,.,FROM,table;,在,select语句中可使用表达式对查询的列进行运算,在,select语句中可使用as语句,SELECT,column as 别名 from 表名;,Select语句(),练习,在所有学生分数上加,10分特长分。,统计每个学生的总分。,使用别名表示学生分数。,Select语句(3),使用,where子句，进行过滤查询。练习：,查询姓名为,wu的学生成绩,查询英语成绩大于90分的同学,查询总分大于200分的所有同学,Select语句(),在,where子句中经常使用的运算符,比较运算符, = = ,大于、小于、大于,(小于)等于、不等于,BETWEEN,.AND.,显示在某一区间的值,IN(set),显示在,in列表中的值，例：in(100,200),LIKE,张pattern,模糊查询,IS NULL,判断是否为空,逻辑运算符,and,多个条件同时成立,or,多个条件任一成立,not,不成立，例：,where not(salary100);,Like语句中，,%,代表零个或多个任意字符，,_,代表一个字符，例first_name like _a%;,Select语句(4),查询英语分数在,8090之间的同学。,查询数学分数为89,90,91的同学。,查询所有姓李的学生成绩。,查询数学分80，语文分80的同学。,Select语句(),SELECT,column,1,column,2,.,column,3,.,FROM,table;,order by column asc|desc,Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。,Asc 升序、Desc 降序,ORDER BY,子句应位于SELECT语句的结尾。,练习：,对数学成绩排序后输出。,对总分排序后输出，然后再按从高到低的顺序输出,对姓李的学生成绩排序输出,使用,order by 子句排序查询结果。,合计函数,count,Select count(*)|count(列名) from tablename,WHERE,where_definition,练习：,统计一个班级共有多少学生？,统计数学成绩大于,90的学生有多少个？,统计总分大于250的人数有多少？,Count(列名)返回某一列，行的总数,合计函数,SUM,Select sum(列名),sum(列名) from tablename,WHERE,where_definition,练习：,统计一个班级数学总成绩？,统计一个班级语文、英语、数学各科的总成绩,统计一个班级语文、英语、数学的成绩总和,统计一个班级语文成绩平均分,注意：,sum仅对数值起作用，否则会报错。,注意：对多列求和，“，”号不能少。,Sum函数返回满足where条件的行的和,合计函数,AVG,Select sum(列名),sum(列名) from tablename,WHERE,where_definition,练习：,求一个班级数学平均分？,求一个班级总分平均分,AVG函数返回满足where条件的一列的平均值,合计函数,MAX/MIN,Select max(列名)from tablename,WHERE,where_definition,练习：,求班级最高分和最低分（数值范围在统计中特别有用）,Max/min函数返回满足where条件的一列的最大/最小值,Select语句(6),SELECT,column,1,column,2,.,column,3,. FROM,table;,group by column,练习：对订单表中商品归类后，显示每一类商品的总价,使用,group by 子句对列进行分组,SELECT,column,1,column,2,.,column,3,.,FROM,table;,group by column having .,使用,having 子句过滤,练习：,查询购买了几类商品，并且每类总价大于,100的商品,Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。,时间日期相关函数,ADDTIME (date2 ,time_interval ),将,time_interval加到date2,CURRENT_DATE (,),当前日期,CURRENT_TIME (,),当前时间,CURRENT_TIMESTAMP ( ),当前时间戳,DATE (datetime ),返回,datetime的日期部分,DATE_ADD (date2 , INTERVAL d_value d_type ),在,date2中加上日期或时间,DATE_SUB (date2 , INTERVAL d_value d_type ),在,date2上减去一个时间,DATEDIFF (date1 ,date2 ),两个日期差,NOW (,),当前时间,YEAR|Month|DATE (datetime ),年月日,示例：,select addtime(02:30:30,01:01:01); 注意：字符串、时间日期的引号问题,select date_add(entry_date,INTERVAL 2 year) from student;增加两年,select addtime(time,1 1-1 10:09:09) from student;时间戳上增加，,注意年后没有,-,字符串相关函数,CHARSET(str),返回字串字符集,CONCAT (string2,. ),连接字串,INSTR (string ,substring ),返回,substring在string中出现的位置,没有返回0,UCASE (string2 ),转换成大写,LCASE (string2 ),转换成小写,LEFT (string2 ,length ),从,string2中的左边起取length个字符,LENGTH (string ),string长度,REPLACE (str ,search_str ,replace_str ),在,str中用replace_str替换search_str,STRCMP (string1 ,string2 ),逐字符比较两字串大小,SUBSTRING (str , position,length ),从,str的position开始,取length个字符,LTRIM (string2 ) RTRIM (string2 ) trim,去除前端空格或后端空格,数学相关函数,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),定义表的约束,定义主键约束,primary key:不允许为空，不允许重复,删除主键：,alter table tablename drop primary key ;,定义主键自动增长,auto_increment,定义唯一约束,unique,定义非空约束,not null,定义外键约束,constraint ordersid_FK foreign key(ordersid) references orders(id),Tip：mysql中文乱码,mysql有六处使用了字符集，分别为：client 、connection、database、results、server 、system。,client是客户端使用的字符集。,connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。,database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。,results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。,server是服务器安装时指定的默认字符集设定。,system是数据库系统使用的字符集设定。,