第1关创建数据表并插入数据任务描述本关任务创建emp表并向表中插入数据。相关知识创建表结构CREATE TABLE创建 MySQL 数据表需要以下信息表名表字段名定义每个表字段以下为创建 MySQL 数据表的通用 SQL 语法CREATE TABLE table_name (column_name1 column_type1column_name2 column_type2);MySQL数据类型创建表之前我们需要了解创建数据的类型。MySQL 支持多种类型大致可以分为三类数值、日期/时间和字符串字符类型。数值类型数值数据类型严格包括这些类型INTEGER、SMALLINT、DECIMAL和NUMERIC以及近似数值数据类型FLOAT、REAL和DOUBLE PRECISION。注意关键字INT是INTEGER的同义词关键字DEC是DECIMAL的同义词。下面的表显示了需要的每个数值类型的存储和范围类型 大小 范围有符号 范围无符号 用途TINYINT 1 字节 (-128,127) (0255) 小整数值SMALLINT 2 字节 (-3276832767) (065535) 大整数值MEDIUMINT 3 字节 (-83886088388607) (016777215) 大整数值INT或INTEGER 4 字节 (-21474836482147483647) (04294967295) 大整数值BIGINT 8 字节 (-92333720368547758089223372036854775807) (018446744073709551615) 极大整数值FLOAT 4 字节 (-3.402823466E38-1.175494351E-38)0(1.175494351E-383.402823466351E38) 0(1.175494351E-383.402823466E38) 单精度浮点数值DOUBLE 8 字节 (-1.7976931348623157E308-2.225073858507201 4E-308)0(2.2250738585072014E-3081.7976931348623157E308) 0(2.2250738585072014E-3081.7976931348623157E308) 双精度浮点数值DECIMAL 对DECIMAL(M,D) 如果MD为M2否则为D2 依赖于M和D的值 依赖于M和D的值 小数值日期和时间类型表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。类型 字节 范围 格式DATE 3 1000-01-01/9999-12-31 YYYY-MM-DDTIME 3 -838:59:59/838:59:59 HH:MM:SSYEAR 1 1901/2155 YYYYDATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SSTIMESTAMP 4 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMS字符串类型字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。类型 大小 范围CHAR 0-255字节 定长字符串VARCHAR 0-65535 字节 变长字符串TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串TINYTEXT 0-255字节 短文本字符串BLOB 0-65 535字节 二进制形式的长文本数据TEXT 0-65 535字节 长文本数据MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据MEDIUMTEXT 0-16 777 215字节 中等长度文本数据LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据LONGTEXT 0-4 294 967 295字节 极大文本数据注意CHAR和VARCHAR类型类似但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。插入数据INSERT INTO以下为向 MySQL 数据表插入数据通用的 SQL 语法INSERT INTO table_name (field1,field2,...,fieldN) VALUES (value1,value2,...,valueN);如果数据是字符型必须使用单引号或者双引号如“value” 或 ‘value’。下面我们通过创建student表来演示CREATE TABLE student (no INT(8) NOT NULL,name CHAR(14),age INT(2));解析如果你不想字段为 NULL 空可以设置字段的属性为 NOT NULL 在操作数据库时如果输入该字段的数据为NULL就会报错命令终止符为分号;创建字段时类型后面需要加括号标注字段的长度创建多个字段时用逗号隔开注意最后一个字段不需要添加逗号,我们再向student表中插入数据INSERT INTO student(no,name,age) VALUES (17010001,zhangsan,22);插入成功后会有如下提示表示插入成功编程要求在右侧编辑器中补充代码创建雇员登记表并插入数据。雇员登记表emp结构如下字段名 类型 长度 能否为空默认 说明empno INT 4 NOT NULL 雇员编号ename CHAR 9 雇员名字job CHAR 10 工种mgr INT 4 经理编号sal INT 7 工资comm INT 7 联系方式deptno INT 2 NOT NULL 部门编号插入数据为empno ename job mgr sal comm deptno1011 Elon cameraman 001 8000 18207471234 301111 Donny waiter 003 4000 18207475678 40评测说明平台会对你编写的代码进行测试将输出你创建的表结构和表中你插入的数据由于输出内容过长请在右侧测试集中查看。万事开头难加油^-^########### Begin ########## #创建emp表 CREATE TABLE emp( empno INT(4) NOT NULL, ename CHAR(9), job CHAR(10), mgr INT(4), sal INT(7), comm INT(7), deptno INT(2) NOT NULL ); #向emp表中插入数据 INSERT INTO emp (empno, ename, job, mgr, sal, comm, deptno) values (1011, Elon, cameraman, 001, 8000, 18207471234, 30); INSERT INTO emp (empno, ename, job, mgr, sal, comm, deptno) values (1111, Donny, waiter, 003, 4000, 18207475678, 40); ########### End ##########第2关删除、修改表中的数据任务描述本关任务公司经常会因为人事变动需要我们对表中数据进行删除或修改本关我们将对以上操作进行学习和实践。相关知识删除数据表MySQL 中删除数据表是非常容易操作的但是你在进行删除表操作时要非常小心因为执行删除命令后所有数据都会消失。以下为 删除表 的 SQL 如下DROP TABLE table_name;若只要删除数据表中数据的 SQL 如下DELETE FROM table_name [WHERE Clause];如果没有指定 WHERE 子句MySQL 表中的所有记录将被删除你可以在 WHERE 子句中指定任何条件您可以在单个表中一次性删除记录修改数据表如果我们需要修改或更新表中的数据我们可以使用UPDATE命令来操作以下是修改的 SQL语法UPDATE table_name SET field1new-value1,field2new-value2 [WHERE Clause];你可以同时更新一个或多个字段你可以在 WHERE 子句中指定任何条件你可以在一个单独表中同时更新数据我们仍以student表为例以下操作会将student更新name为zhangsan 的age字段值UPDATE student SET age23 WHERE namezhangsan;编程要求请在右侧编辑器中补充代码我们已经在数据库中建立了雇员Elon和Donny的信息已经在岗位上工作的Elon因为家庭原因离职需要将他从数据表中删除同时Donny因为部门调整需要将他的部门编号修改为20。雇员登记表emp结构如下字段名 类型 长度 能否为空默认 说明empno INT 4 NOT NULL 雇员编号ename CHAR 9 雇员名字job CHAR 10 工种mgr INT 4 经理编号sal INT 7 工资comm INT 7 联系方式deptno INT 2 NOT NULL 部门编号评测说明平台将对你编写的代码进行测试预期输出------------------------------------------------------| empno | ename | job | mgr | sal | comm | deptno |------------------------------------------------------| 1111 | Donny | waiter | 3 | 4000 | 2147483647 | 20 |------------------------------------------------------放纵是人的本性而克制则是人的理智所在理智是一种能力更是一种胸怀胸怀开阔才能包容万物才能把整个宇宙抱在怀里。克制住自己的杂念别受外界干扰继续加油########### Begin ########## #删除 Elon 的信息 delete from emp where enameElon; #修改 Donny 的信息 update emp set deptno20 where enameDonny; ########### End ##########第3关简单查询语句任务描述本关任务使用查询语句查看表中的信息。相关知识查询语句 SELECT以下为查询表数据通用的 SQL 语法SELECT column_name1,column_name2 FROM table_name [WHERE Clause] [OFFSET M ] [LIMIT N];查询语句中你可以使用一个或者多个表表之间使用逗号(,)分割并使用WHERE语句来设定查询条件。SELECT 命令可以读取一条或者多条记录。你可以使用星号*来代替其他字段SELECT语句会返回表的所有字段数据你可以通过 OFFSET 指定 SELECT 语句开始查询的数据偏移量。默认情况下偏移量为0。你可以使用 LIMIT 属性来设定返回的记录数。常用操作的介绍WHERE以下是SELECT语句使用 WHERE 子句从数据表中读取数据的通用语法SELECT field1, field2,...,fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR] condition2...];你可以使用 AND 或者 OR 指定一个或多个条件。LIKE打个比方在表stdent里有时候我们需要获取no字段含有“1”字符的所有记录这时我们就需要在WHERE语句中使用LIKE子句。LIKE子句中使用百分号%字符来表示任意字符。如果没有使用百分号%LIKE子句与等号的效果是一样的。以下是SELECT语句使用LIKE子句从数据表中读取数据的通用语法SELECT field1, field2,...,fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 somevalue;你可以在 WHERE 子句中使用LIKE子句。你可以使用LIKE子句代替等号 。UNIONUNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。UNION 操作符语法格式如下SELECT expression1,expression2,...,expression_n FROM tables [WHERE conditions]UNION [ALL | DISTINCT]SELECT expression1,expression2,...,expression_n FROM tables[WHERE conditions];参数解释expression1, expression2, ... expression_n检索的列tables检索的数据表WHERE conditions检索条件DISTINCT删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据所以 DISTINCT 修饰符对结果没啥影响。而ALL可以返回所有结果集包含重复数据。GROUP BYGROUP BY语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNTSUMAVG等函数这些函数的含义非常简单在这里不多做赘述。SELECT column_name, function(column_name) FROM table_name WHEREcolumn_name operator value GROUP BY column_name;OEDER BY如果我们需要对读取的数据进行排序我们就可以使用ORDER BY子句来设定你想按哪个字段哪种方式来进行排序再返回搜索结果。SELECT field1, field2,...,fieldN table_name1,table_name2..., ORDER BYfield1,[field2...] [ASC [DESC]];你可以设定多个字段来排序。你可以使用 ASC 默认升序或 DESC降序 关键字来设置查询结果是按升序或降序排列。你可以添加 WHERE...LIKE 子句来设置条件。我们仍通过student表的例子来让大家学习该语句的操作查询表中所有记录SELECT no,name,age FROM student对学生年龄总和进行计算和统计SELECT SUM(age) FROM student查询学号1701001学生的姓名SELECT name FROM student WHERE no1701001;查询学号以1开头的学生学号、姓名SELECT no,name FROM student WHERE no LIKE 1%;将所有学号1开头学生按年龄的降序进行排序SELECT name,ageno FROM student WHERE no LIKE 1% ORDER BY age DESC;编程要求请在右侧编辑器中补充代码完成以下查询操作查询在20号部门工作的雇员姓名和工资返回的列包括雇员姓名、工资和编号使用LIKE查找以“s”开头的雇员姓名和所在部门编号按工资升序排列20号部门的雇员返回包括雇员姓名、工资和编号查询所有部门中工资大于2800的雇员返回包括雇员姓名、工资。下面为部门表dept和雇员表emp的源数据信息dept表结构信息deptno dname loc10 research beijing20 account chengdu30 sales changshaemp表结构信息empno ename job mgr sal comm deptno1 zcq clerk 10 2000 100 202 lbh analyst 20 6000 200 103 hdy clerk 20 8000 300 204 ljz salesman 30 3000 400 305 ssr clerk 30 8000 500 30评测说明平台将对你编写的代码进行测试将依次输出每个查询的结果由于输出结果过多请在测试集中进行查看。一个人能快乐地独自做着有利于社会、他人的事那是一位伟大的超人。一个人能快乐地独自做着有利于社会、他人的事那是一位伟大的超人。老想着自己不幸福幸福也会嫌弃你。########### Begin ########## #查询在20号部门工作的雇员姓名和工资 select ename,sal,deptno from emp where deptno20; #查找以“s”开头的雇员姓名和所在部门编号 select ename,deptno from emp where ename like s%; #按工资升序排列20号部门的雇员 select ename,sal,deptno from emp where deptno 20 order by sal asc; #查询所有部门中工资大于2800的雇员 select ename,sal from emp where sal 2800; ########### End ##########第4关深入学习查询语句任务描述在之前的学习中我们已经对数据表进行简单的查询掌握得比较好了能够应对基本的查询要求但是公司往往需要我们进行各种更复杂的查询我们又该怎么做呢相关知识更多操作的介绍AS查询可以使用AS关键字为查询的字段起一个别名SELECT CONCAT(last_name,first_name) AS full_name FROM mytable ORDER BY full_name;CONCAT函数用于将多个字符串连接成一个字符串是最重要的 mysql 函数之一AS是可选的如果你在查询字段之间忘记输入逗号MySQL将把第二个字段作为第一个字段的别名。例如在下面的 SQL 语句中columnb被视为columna的别名SELECT columna columnb FROM mytable;出于这个原因建议大家使用别名的时候加上as关键字以免在编写 SQL 中给自己带来不便。COUNTcount函数是用来统计表中或数组中记录数的例如我们对下面的user表进行统计userno name password1 name1 1234562 name2 1234563 name3 1234564 name4 NULL请注意以下查询的返回结果SELECT COUNT(*) FROM userSELECT COUNT(name) FROM userSELECT COUNT(password) FROM user输出结果443输出结果分析COUNT(*)是对行数目进行计数所以结果为4。COUNT(column_name)是对列中不为空的行进行计数所以COUNT(name)4而COUNT(password)3。HAVINGHAVING可以让我们筛选分组后的各种数据。注意WHERE在聚合前先筛选记录也就是说作用在GROUP BY和HAVING前而HAVING在聚合后对组记录进行筛选。比如我们对下面的bbc表进行筛选loc populationg area region1 100000 100000 a2 100000 100000 b... ... ... ...查询每个地区的总人口数和总面积SELECT region,SUM(population),SUM(area) FROM bbc GROUP BY region;先以region把返回记录分成多个组这就是GROUP BY的字面含义。分完组后然后用聚合函数对每组中的不同字段一或多条记录作运算。查询每个地区的总人口数和总面积超过1000000的地区SELECT region, SUM(population), SUM(area)FROM bbc GROUP BY region HAVING SUM(area)1000000;在这里我们不能用WHERE来筛选超过1000000的地区因为表中不存在这样一条记录。相反HAVING可以让我们筛选成组后的各组数据。ININ 运算符用于 WHERE 表达式中以列表项的形式支持多个选择语法如下WHERE column IN (value1,value2,...)WHERE column NOT IN (value1,value2,...)当 IN 前面加上 NOT 运算符时表示与 IN 相反的意思即不在这些列表项内选择。JOINJOIN按照功能大致分为如下三类INNER JOIN内连接或等值连接获取两个表中字段匹配关系的记录。LEFT JOIN左连接获取左表所有记录即使右表没有对应匹配的记录。RIGHT JOIN右连接 与 LEFT JOIN 相反用于获取右表所有记录即使左表没有对应匹配的记录。编程要求请在右侧编辑器中补充代码完成以下查询操作使用IN查找工种是职员clerk或分析员analyst的雇员姓名和工种查在编号为10的部门中哪些工种是编号为30的部门中没有的查看工资高于3000的雇员的名字下面为部门表dept和雇员表emp的源数据信息dept表结构信息deptno dname loc10 research beijing20 account chengdu30 sales changshaemp表结构信息empno ename job mgr sal comm deptno1 zcq clerk 10 2000 100 202 lbh analyst 20 6000 200 103 hdy clerk 20 8000 300 204 ljz salesman 30 3000 400 305 ssr clerk 30 8000 500 30评测说明平台将对你编写的代码进行测试将依次输出每个查询的结果由于输出结果过多请在测试集中进行查看。人的思想是了不起的只要专注于某一项事业就一定会做出使自己感到吃惊的成绩来。########### Begin ########## ########## 查询工种是职员或分析员的雇员姓名 ########## SELECT ename, job FROM emp WHERE job IN (clerk, analyst); ########## 查询在 10 号部门中哪些工种是 30 号部门中没有 ########## SELECT DISTINCT job FROM emp WHERE deptno 10 AND job NOT IN (SELECT job FROM emp WHERE deptno 30); ########## 查询工资高于3000的雇员信息 ########## SELECT e.ename, e.sal, e.deptno, d.loc, d.dname FROM emp e JOIN dept d ON e.deptno d.deptno WHERE e.sal 3000; ########### End ##########
