SQL 入门 SQL：Structured query language 北京传智播客教育.

Presentation on theme: "SQL 入门 SQL：Structured query language 北京传智播客教育."— Presentation transcript:

1 SQL 入门 SQL：Structured query language 北京传智播客教育

2 MySQL简介 数据库简介 MySQL数据库的安装和配置 使用命令行窗口连接MYSQL数据库 mysql –u用户名 –p密码
SQL Server、Oracle、 MySQL、DB2、 SyBase MySQL数据库的安装和配置 使用命令行窗口连接MYSQL数据库 mysql –u用户名 –p密码 数据库安装好后，如何使用数据库保存程序中的数据呢？ 北京传智播客教育

3 数据库服务器、数据库和表的关系 所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。 为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。 数据库服务器、数据库和表的关系如图所示： 表 DB Client MySQL 表 DB 表 北京传智播客教育

4 数据在数据库中的存储方式 id name age 1 lisi 23 2 wang 24 User表 列(column) id=1
name=“lisi” age=23 id name age 1 lisi 23 2 wang 24 行(row) User对象 id=2 name=“wang” age=24 １、Java是使用对象封装数据，例如程序产生了如上所示２个user对象，这些对象的数据要想保存到数据库中，需要在数据库中创建与之对应的表，一个user对象中封装的数据，要保存到数据库中，一般就要在数据库中创建一个与之对应的表。 ２、对象的属性定义为表头，对象的数据对应于表中的一条记录。 ３、、每个对象对应于表中的一条记录。 明白数据库使用表保存数据后，如何在数据库中创建表呢？ User对象 表的一行称之为一条记录 表中一条记录对应一个java对象的数据 北京传智播客教育

5 创建数据库 CHARACTER SET：指定数据库采用的字符集 COLLATE：指定数据库字符集的比较方式 练习：
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数据库。 Basic SELECT Statement In its simplest form, a SELECT statement must include the following: A SELECT clause, which specifies the columns to be displayed A FROM clause, which specifies the table containing the columns listed in the SELECT clause In the syntax: SELECT is a list of one or more columns * selects all columns DISTINCT suppresses duplicates column|expression selects the named column or the expression alias gives selected columns different headings FROM table specifies the table containing the columns Note: Throughout this course, the words keyword, clause, and statement are used as follows: A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords. A clause is a part of a SQL statement. For example, SELECT employee_id, last_name, ... is a clause. A statement is a combination of two or more clauses. For example, SELECT * FROM employees is a SQL statement. 北京传智播客教育

6 查看、删除数据库 显示数据库语句： SHOW DATABASES 显示数据库创建语句：
SHOW CREATE DATABASE db_name 数据库删除语句： DROP DATABASE [IF EXISTS] db_name Basic SELECT Statement In its simplest form, a SELECT statement must include the following: A SELECT clause, which specifies the columns to be displayed A FROM clause, which specifies the table containing the columns listed in the SELECT clause In the syntax: SELECT is a list of one or more columns * selects all columns DISTINCT suppresses duplicates column|expression selects the named column or the expression alias gives selected columns different headings FROM table specifies the table containing the columns Note: Throughout this course, the words keyword, clause, and statement are used as follows: A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords. A clause is a part of a SQL statement. For example, SELECT employee_id, last_name, ... is a clause. A statement is a combination of two or more clauses. For example, SELECT * FROM employees is a SQL statement. 练习： 查看当前数据库服务器中的所有数据库 查看前面创建的mydb2数据库的定义信息 删除前面创建的mydb1数据库 北京传智播客教育

7 修改、备份、恢复数据库 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 --default-character-set=charset 指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。 练习 查看服务器中的数据库，并把其中某一个库的字符集修改为utf8; 备份test库中的数据，并恢复 北京传智播客教育

8 创建表(基本语句) 注意：创建表前，要先使用use db语句使用库。 field2 datatype, field3 datatype,
CREATE TABLE table_name ( field1 datatype, field2 datatype, field3 datatype, )character set 字符集 collate 校对规则 field：指定列名　datatype：指定列类型 注意：创建表前，要先使用use db语句使用库。 注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user对象 id int name string password string birthday date Basic SELECT Statement In its simplest form, a SELECT statement must include the following: A SELECT clause, which specifies the columns to be displayed A FROM clause, which specifies the table containing the columns listed in the SELECT clause In the syntax: SELECT is a list of one or more columns * selects all columns DISTINCT suppresses duplicates column|expression selects the named column or the expression alias gives selected columns different headings FROM table specifies the table containing the columns Note: Throughout this course, the words keyword, clause, and statement are used as follows: A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords. A clause is a part of a SQL statement. For example, SELECT employee_id, last_name, ... is a clause. A statement is a combination of two or more clauses. For example, SELECT * FROM employees is a SQL statement. Id Name Password birthday 北京传智播客教育

9 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操作的时间 Selecting All Columns of All Rows You can display all columns of data in a table by following the SELECT keyword with an asterisk (*). In the example on the slide, the department table contains four columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID. The table contains seven rows, one for each department. You can also display all columns in the table by listing all the columns after the SELECT keyword. For example, the following SQL statement, like the example on the slide, displays all columns and all rows of the DEPARTMENTS table: SELECT department_id, department_name, manager_id, location_id FROM departments; Instructor Note Let the students know that details of all the tables are given in Appendix B. VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。 北京传智播客教育

10 创建表练习 创建一个员工表 字段 属性 Id 整形 name 字符型 sex 字符型或bit型 brithday 日期型
Entry_date job Salary 小数型 resume 大文本型 Basic SELECT Statement In its simplest form, a SELECT statement must include the following: A SELECT clause, which specifies the columns to be displayed A FROM clause, which specifies the table containing the columns listed in the SELECT clause In the syntax: SELECT is a list of one or more columns * selects all columns DISTINCT suppresses duplicates column|expression selects the named column or the expression alias gives selected columns different headings FROM table specifies the table containing the columns Note: Throughout this course, the words keyword, clause, and statement are used as follows: A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords. A clause is a part of a SQL statement. For example, SELECT employee_id, last_name, ... is a clause. A statement is a combination of two or more clauses. For example, SELECT * FROM employees is a SQL statement. 北京传智播客教育

11 修改表 使用 ALTER TABLE 语句追加, 修改, 或删除列的语法. 修改表的名称：Rename table 表名 to 新表名
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); Basic SELECT Statement In its simplest form, a SELECT statement must include the following: A SELECT clause, which specifies the columns to be displayed A FROM clause, which specifies the table containing the columns listed in the SELECT clause In the syntax: SELECT is a list of one or more columns * selects all columns DISTINCT suppresses duplicates column|expression selects the named column or the expression alias gives selected columns different headings FROM table specifies the table containing the columns Note: Throughout this course, the words keyword, clause, and statement are used as follows: A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords. A clause is a part of a SQL statement. For example, SELECT employee_id, last_name, ... is a clause. A statement is a combination of two or more clauses. For example, SELECT * FROM employees is a SQL statement. 修改表的名称：Rename table 表名 to 新表名 修改表的字符集：alter table student character set utf8; 北京传智播客教育

12 修改表 练习 在上面员工表的基本上增加一个image列。 修改job列，使其长度为60。 删除sex列。 表名改为user。
修改表的字符集为utf-8 列名name修改为username alter table user change column name username varchar(20); 北京传智播客教育

13 北京传智播客教育

14 数据库CRUD语句 Insert语句 (增加数据) Update语句 (更新数据) Delete语句 (删除数据)
Select语句　(查找数据) 北京传智播客教育

15 Insert语句 使用 INSERT 语句向表中插入数据。 插入的数据应与字段的数据类型相同。
INSERT INTO table [(column [, column...])] VALUES (value [, value...]); 插入的数据应与字段的数据类型相同。 数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。 在values中列出的数据位置必须与被加入的列的排列位置相对应。 字符和日期型数据应包含在单引号中。 插入空值，不指定或insert into table value(null) 北京传智播客教育

16 Insert语句练习 注意：字符和日期要包含在单引号中。 show variables like 'character%';
字段名 字段类型 id 整形 name 字符串型 sex 字符或整数类型 birthday 日期型 salary 浮点型 entry_date resume 大文本型 注意：字符和日期要包含在单引号中。 show variables like 'character%'; set character_set_results=gbk; 北京传智播客教育

17 Update语句 使用 update语句修改表中数据。 UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]     [WHERE where_definition]    UPDATE语法可以用新值更新原有表行中的各列。 SET子句指示要修改哪些列和要给予哪些值。 WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。 北京传智播客教育

18 Update语句练习 要求 将所有员工薪水修改为5000元。 将姓名为’zs’的员工薪水修改为3000元。
练习：在上面创建的employee表中修改表中的纪录。 要求 将所有员工薪水修改为5000元。 将姓名为’zs’的员工薪水修改为3000元。 将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。 将wu的薪水在原有基础上增加1000元。 北京传智播客教育

19 Delete语句 使用 delete语句删除表中数据。 delete from tbl_name
[WHERE where_definition]    如果不使用where子句，将删除表中所有数据。 Delete语句不能删除某一列的值（可使用update） 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。 删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。 北京传智播客教育

20 Delete语句练习 删除表中名称为’zs’的记录。 删除表中所有记录。 使用truncate删除表中记录。 北京传智播客教育

21 Select语句(1) 基本select语句 Select 指定查询哪些列的数据。 column指定列名。 *号代表查询所有列。
SELECT [DISTINCT] *|{column1, column2. column3..} FROM table; Select 指定查询哪些列的数据。 column指定列名。 *号代表查询所有列。 From指定查询哪张表。 DISTINCT可选，指显示结果时，是否剔除重复数据 北京传智播客教育

22 Select语句(1) 练习： 查询表中所有学生的信息。 查询表中所有学生的姓名和对应的英语成绩。 过滤表中重复数据。 北京传智播客教育

23 Select语句(２) 在select语句中可使用表达式对查询的列进行运算 在select语句中可使用as语句
SELECT *|{column1｜expression, column2｜expression，..} FROM table; 在select语句中可使用as语句 SELECT column as 别名 from 表名; 北京传智播客教育

24 Select语句(２) 练习 在所有学生分数上加10分特长分。 统计每个学生的总分。 使用别名表示学生分数。 北京传智播客教育

25 Select语句(3) 使用where子句，进行过滤查询。练习： 查询姓名为wu的学生成绩 查询英语成绩大于90分的同学
查询总分大于200分的所有同学 北京传智播客教育

26 Select语句(４) 在where子句中经常使用的运算符 比较运算符 > < <= >= = <>
> < <= >= = <> 大于、小于、大于(小于)等于、不等于 BETWEEN ...AND... 显示在某一区间的值 IN(set) 显示在in列表中的值，例：in(100,200) LIKE ‘张pattern’ 模糊查询 IS NULL 判断是否为空 逻辑运算符 and 多个条件同时成立 or 多个条件任一成立 not 不成立，例：where not(salary>100); Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like ‘_a%’; 北京传智播客教育

27 Select语句(4) 查询英语分数在 80－90之间的同学。 查询数学分数为89,90,91的同学。 查询所有姓李的学生成绩。
查询数学分>80，语文分>80的同学。 北京传智播客教育

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

29 合计函数－count [WHERE where_definition] 练习： 统计一个班级共有多少学生？
Select count(*)|count(列名) from tablename [WHERE where_definition]   练习： 统计一个班级共有多少学生？ 统计数学成绩大于90的学生有多少个？ 统计总分大于250的人数有多少？ 北京传智播客教育

30 合计函数－SUM Sum函数返回满足where条件的行的和 [WHERE where_definition] 练习：
Select sum(列名)｛,sum(列名)…｝ from tablename [WHERE where_definition]   练习： 统计一个班级数学总成绩？ 统计一个班级语文、英语、数学各科的总成绩 统计一个班级语文、英语、数学的成绩总和 统计一个班级语文成绩平均分 注意：sum仅对数值起作用，否则会报错。 注意：对多列求和，“，”号不能少。 北京传智播客教育

31 合计函数－AVG AVG函数返回满足where条件的一列的平均值 [WHERE where_definition] 练习：
Select sum(列名)｛,sum(列名)…｝ from tablename [WHERE where_definition]   练习： 求一个班级数学平均分？ 求一个班级总分平均分 北京传智播客教育

32 合计函数－MAX/MIN Max/min函数返回满足where条件的一列的最大/最小值 [WHERE where_definition]
Select max(列名)　from tablename [WHERE where_definition]   练习： 求班级最高分和最低分（数值范围在统计中特别有用） 北京传智播客教育

33 Select语句(6) 使用group by 子句对列进行分组 使用having 子句过滤 练习：对订单表中商品归类后，显示每一类商品的总价
SELECT column1, column2. column3.. FROM table; group by column 练习：对订单表中商品归类后，显示每一类商品的总价 使用having 子句过滤 SELECT column1, column2. column3.. FROM table; group by column having ... 练习：查询购买了几类商品，并且每类总价大于100的商品 Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。 北京传智播客教育

34 时间日期相关函数 示例：select addtime(‘02:30:30’,‘01:01:01’); 注意：字符串、时间日期的引号问题
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,‘ :09:09’) from student;　／／时间戳上增加，注意年后没有- 北京传智播客教育

35 字符串相关函数 北京传智播客教育 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 去除前端空格或后端空格 北京传智播客教育

36 数学相关函数 北京传智播客教育 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]) 北京传智播客教育

37 定义表的约束 定义主键约束 primary key:不允许为空，不允许重复
删除主键：alter table tablename drop primary key ; 定义主键自动增长 auto_increment 定义唯一约束 unique 定义非空约束 not null 定义外键约束 constraint ordersid_FK foreign key(ordersid) references orders(id), alter table student modify id int primary key; 主銉不可重复修改 alter table student modify id int auto_increment; 北京传智播客教育

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