传智播客sql入门

《传智播客sql入门》由会员分享，可在线阅读，更多相关《传智播客sql入门（38页珍藏版）》请在装配图网上搜索。

1、 高级软件人才实作培训专家 ! 北京传智播客教育 SQL 入门 讲师： 方立勋 高级软件人才实作培训专家 ! 北京传智播客教育 MySQL简介 数据库简介 SQL Server、 Oracle、 MySQL、 DB2、 SyBase MySQL数据库的安装和配置 使用命令行窗口连接 MYSQL数据库 mysql u用户名 p密码 高级软件人才实作培训专家 ! 北京传智播客教育 数据库服务器、数据库和表的关系 MySQL DB DB 表 表 表 Client 所谓安装数据库服务器，只是在机器上装了一个数据库管理程序， 这个管理程序可以管理多个数据库，一般开发人员会针对每一个 应用创建一个数据库。

2、 为保存应用中实体的数据，一般会在数据库创建多个表，以保存 程序中实体的数据。 数据库服务器、数据库和表的关系如图所示： 高级软件人才实作培训专家 ! 北京传智播客教育 数据在数据库中的存储方式 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

3、 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数据库。 高级

4、软件人才实作培训专家 ! 北京传智播客教育 查看、删除数据库 显示数据库语句： 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_sp

5、ecification . 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

6、, 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和

7、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的 1

8、6次方 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操作的时间 高级软件人

9、才实作培训专家 ! 北京传智播客教育 创建表练习 创建一个员工表 字段 属性 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 da

10、tatype 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 ch

11、ange column name username varchar(20); 高级软件人才实作培训专家 ! 北京传智播客教育 高级软件人才实作培训专家 ! 北京传智播客教育 数据库 CRUD语句 Insert语句 (增加数据 ) Update语句 (更新数据 ) Delete语句 (删除数据 ) Select语句 (查找数据 ) 高级软件人才实作培训专家 ! 北京传智播客教育 Insert语句 INSERT INTO table (column , column.) VALUES (value , value.); 使用 INSERT 语句向表中插入数据。 插入的数据应与字段的数据类型相同。

12、数据的大小应在列的规定范围内，例如：不能将一个长度为 80的字符串加入到长度为 40的列中。 在 values中列出的数据位置必须与被加入的列的排列位置相 对应。 字符和日期型数据应包含在单引号中。 插入空值，不指定或 insert into table value(null) 高级软件人才实作培训专家 ! 北京传智播客教育 Insert语句练习 练习：使用 insert语句向表中插入三个员工的信息。 字段名 字段类型 id 整形 name 字符串型 sex 字符或整数类型 birthday 日期型 salary 浮点型 entry_date 日期型 resume 大文本型 高级软件人才实作培

13、训专家 ! 北京传智播客教育 Update语句 UPDATE tbl_name SET col_name1=expr1 , col_name2=expr2 . WHERE where_definition 使用 update语句修改表中数据。 UPDATE语法可以用新值更新原有表行中的各列。 SET子句指示要修改哪些列和要给予哪些值。 WHERE子句指定应更新哪些行。如没有 WHERE子句，则更新所有的行。 高级软件人才实作培训专家 ! 北京传智播客教育 Update语句练习 练习：在上面创建的 employee表中修改表中的纪录。 要求 将所有员工薪水修改为 5000元。 将姓名为 zs 的

14、员工薪水修改为 3000元。 将 wu的薪水在原有基础上增加 1000元。 高级软件人才实作培训专家 ! 北京传智播客教育 Delete语句 delete from tbl_name WHERE where_definition 使用 delete语句删除表中数据。 如果不使用 where子句，将删除表中所有数据。 Delete语句不能删除某一列的值（可使用 update） 使用 delete语句仅删除记录，不删除表本身。如要删除表，使用 drop table 语句。 同 insert和 update一样，从一个表中删除记录将引起其它表的参照完整性问 题，在修改数据库数据时，头脑中应该始终不要

15、忘记这个潜在的问题。 删除表中数据也可使用 TRUNCATE TABLE 语句，它和 delete有所 不同，参看 mysql文档。 高级软件人才实作培训专家 ! 北京传智播客教育 Delete语句练习 删除表中名称为 zs 的记录。 删除表中所有记录。 使用 truncate删除表中记录。 高级软件人才实作培训专家 ! 北京传智播客教育 Select语句 (1) SELECT DISTINCT *|column1, column2. column3. FROM table; Select 指定查询哪些列的数据。 column指定列名。 *号代表查询所有列。 From指定查询哪张表。 DIST

16、INCT可选，指显示结果时，是否剔除重复数据 基本 select语句 高级软件人才实作培训专家 ! 北京传智播客教育 Select语句 (1) 练习： 查询表中所有学生的信息。 查询表中所有学生的姓名和对应的英语成绩。 过滤表中重复数据。 高级软件人才实作培训专家 ! 北京传智播客教育 Select语句 ( ) SELECT *|column1 expression, column2 expression， . FROM table; 在 select语句中可使用表达式对查询的列进行运算 在 select语句中可使用 as语句 SELECT column as 别名 from 表名 ; 高级软

17、件人才实作培训专家 ! 北京传智播客教育 Select语句 ( ) 练习 在所有学生分数上加 10分特长分。 统计每个学生的总分。 使用别名表示学生分数。 高级软件人才实作培训专家 ! 北京传智播客教育 Select语句 (3) 使用 where子句，进行过滤查询。练习： 查询姓名为 wu的学生成绩 查询英语成绩大于 90分的同学 查询总分大于 200分的所有同学 高级软件人才实作培训专家 ! 北京传智播客教育 Select语句 ( ) 在 where子句中经常使用的运算符 比较运算符 = = 大于、小于、大于 (小于 )等于、不等于 BETWEEN .AND. 显示在某一区间的值 IN(se

18、t) 显示在 in列表中的值，例： in(100,200) LIKE 张 pattern 模糊查询 IS NULL 判断是否为空 逻辑运算符 and 多个条件同时成立 or 多个条件任一成立 not 不成立，例： where not(salary100); Like语句中， % 代表零个或多个任意字符， _ 代表一个字符，例 first_name like _a% ; 高级软件人才实作培训专家 ! 北京传智播客教育 Select语句 (4) 查询英语分数在 80 90之间的同学。 查询数学分数为 89,90,91的同学。 查询所有姓李的学生成绩。 查询数学分 80，语文分 80的同学。 高级软

19、件人才实作培训专家 ! 北京传智播客教育 Select语句 ( ) SELECT column1, column2. column3. FROM table; order by column asc|desc Order by 指定排序的列，排序的列即可是表中的列名，也可以 是 select 语句后指定的列名。 Asc 升序、 Desc 降序 ORDER BY 子句应位于 SELECT语句的结尾。 练习： 对数学成绩排序后输出。 对总分排序后输出，然后再按从高到低的顺序输出 对姓李的学生成绩排序输出 使用 order by 子句排序查询结果。 高级软件人才实作培训专家 ! 北京传智播客教育 合

20、计函数 count Select count(*)|count(列名 ) from tablename WHERE where_definition 练习： 统计一个班级共有多少学生？ 统计数学成绩大于 90的学生有多少个？ 统计总分大于 250的人数有多少？ Count(列名 )返回某一列，行的总数 高级软件人才实作培训专家 ! 北京传智播客教育 合计函数 SUM Select sum(列名 ) ,sum(列名 ) from tablename WHERE where_definition 练习： 统计一个班级数学总成绩？ 统计一个班级语文、英语、数学各科的总成绩 统计一个班级语文、英语、数

21、学的成绩总和 统计一个班级语文成绩平均分 注意： sum仅对数值起作用，否则会报错。 注意：对多列求和，“，”号不能少。 Sum函数返回满足 where条件的行的和 Student.sql 高级软件人才实作培训专家 ! 北京传智播客教育 合计函数 AVG Select sum(列名 ) ,sum(列名 ) from tablename WHERE where_definition 练习： 求一个班级数学平均分？ 求一个班级总分平均分 AVG函数返回满足 where条件的一列的平均值 Student.sql 高级软件人才实作培训专家 ! 北京传智播客教育 合计函数 MAX/MIN Select

22、max(列名 ) from tablename WHERE where_definition 练习： 求班级最高分和最低分（数值范围在统计中特别有用） Max/min函数返回满足 where条件的一列的最大 /最小值 Student.sql 高级软件人才实作培训专家 ! 北京传智播客教育 Select语句 (6) SELECT column1, column2. column3. FROM table; group by column 练习：对订单表中商品归类后，显示每一类商品的总价 使用 group by 子句对列进行分组 SELECT column1, column2. column3.

23、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

24、 ( ) 当前时间戳 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 ); 注意：字符串、时间日期的引号问题

25、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 ) 转换成大写 LCAS

26、E (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 (s

27、tring2 ) 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 (numerat

28、or ,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), 高级软件人才实作培训专家 ! 北京传智

29、播客教育 Tip： mysql中文乱码 mysql有六处使用了字符集，分别为： client 、 connection、 database、 results、 server 、 system。 client是客户端使用的字符集。 connection是连接数据库的字符集设置类型，如果程序没有指明连 接数据库使用的字符集类型就按照服务器端默认的字符集设置。 database是数据库服务器中某个库使用的字符集设定，如果建库时 没有指明，将使用服务器安装时指定的字符集设置。 results是数据库给客户端返回时使用的字符集设定，如果没有指明， 使用服务器默认的字符集。 server是服务器安装时指定的默认字符集设定。 system是数据库系统使用的字符集设定。