win7怎么做网站服务器,中国免费素材网,长春有几个区,网站开发什么语言目录
第一题 第二题 第三题 第一题 1.创建数据表pet#xff0c;并对表进行插入、更新与删除操作#xff0c;pet表结构如表8.3所示。
(1#xff09;首先创建数据表pet#xff0c;使用不同的方法将表8.4中的记录插入到pet表中。 mysql create table pet( name varchar(…
目录
第一题 第二题 第三题 第一题 1.创建数据表pet并对表进行插入、更新与删除操作pet表结构如表8.3所示。
(1首先创建数据表pet使用不同的方法将表8.4中的记录插入到pet表中。 mysql create table pet( name varchar(20) not null, owner varchar(20), species varchar(20) not null, sex char(1) not null, birth year not null, death year); Query OK, 0 rows affected (0.00 sec) mysql desc pet; ------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ------------------------------------------------- | name | varchar(20) | NO | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | NO | | NULL | | | sex | char(1) | NO | | NULL | | | birth | year(4) | NO | | NULL | | | death | year(4) | YES | | NULL | | ------------------------------------------------- 6 rows in set (0.00 sec) 插入数据 mysql insert into pet values(Fluffy,Harold,cat,f,2003,2010); Query OK, 1 row affected (0.00 sec) mysql insert into pet values(Claws,Gwen,cat,m,2004,NULL); Query OK, 1 row affected (0.00 sec) mysql insert into pet values(Buffy,NULL,dog,f,2009,NULL); Query OK, 1 row affected (0.00 sec) mysql insert into pet values(Fang,Benny,dog,m,2000,NULL); Query OK, 1 row affected (0.00 sec) mysql insert into pet values(Bowser,Diane,dog,m,2003,2009); Query OK, 1 row affected (0.00 sec) mysql insert into pet values(Chirpy,NULL,bird,f,2008,NULL); Query OK, 1 row affected (0.01 sec) mysql select * from pet; -------------------------------------------- | name | owner | species | sex | birth | death | -------------------------------------------- | Fluffy | Harold | cat | f | 2003 | 2010 | | Claws | Gwen | cat | m | 2004 | NULL | | Buffy | NULL | dog | f | 2009 | NULL | | Fang | Benny | dog | m | 2000 | NULL | | Bowser | Diane | dog | m | 2003 | 2009 | | Chirpy | NULL | bird | f | 2008 | NULL | -------------------------------------------- 6 rows in set (0.00 sec) (2使用UPDATE语句将名称为Fang 的狗的主人改为Kevin。 mysql update pet set ownerKevin where nameFang; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from pet; -------------------------------------------- | name | owner | species | sex | birth | death | -------------------------------------------- | Fluffy | Harold | cat | f | 2003 | 2010 | | Claws | Gwen | cat | m | 2004 | NULL | | Buffy | NULL | dog | f | 2009 | NULL | | Fang | Kevin | dog | m | 2000 | NULL | | Bowser | Diane | dog | m | 2003 | 2009 | | Chirpy | NULL | bird | f | 2008 | NULL | -------------------------------------------- 6 rows in set (0.00 sec) (3将没有主人的宠物的owner字段值都改为Duck。 mysql update pet set ownerDuck where ownerNULL; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql select * from pet; -------------------------------------------- | name | owner | species | sex | birth | death | -------------------------------------------- | Fluffy | Harold | cat | f | 2003 | 2010 | | Claws | Gwen | cat | m | 2004 | NULL | | Buffy | Duck | dog | f | 2009 | NULL | | Fang | Kevin | dog | m | 2000 | NULL | | Bowser | Diane | dog | m | 2003 | 2009 | | Chirpy | Duck | bird | f | 2008 | NULL | -------------------------------------------- 6 rows in set (0.00 sec) (4删除已经死亡的宠物记录。 mysql delete from pet where death is not NULL; Query OK, 2 rows affected (0.00 sec) mysql select * from pet; ------------------------------------------- | name | owner | species | sex | birth | death | ------------------------------------------- | Claws | Gwen | cat | m | 2004 | NULL | | Buffy | Duck | dog | f | 2009 | NULL | | Fang | Kevin | dog | m | 2000 | NULL | | Chirpy | Duck | bird | f | 2008 | NULL | ------------------------------------------- 4 rows in set (0.00 sec) (5)删除所有表中的记录。 mysql delete from pet; Query OK, 4 rows affected (0.00 sec) mysql select * from pet; Empty set (0.01 sec) 第二题 1.创建表 mysql create table employee( id int primary key auto_increment, name varchar(20), gender varchar(20), salary decimal(4,2)); Query OK, 0 rows affected (0.01 sec) mysql desc employee; ---------------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------------- | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | gender | varchar(20) | YES | | NULL | | | salary | decimal(6,2) | YES | | NULL | | ---------------------------------------------------------- 4 rows in set (0.00 sec) 2. 插入数据 mysql insert into employee values(1,张三,男,2000.00); Query OK, 1 row affected (0.00 sec) mysql insert into employee values(2,李四,男,1000.00); Query OK, 1 row affected (0.01 sec) mysql insert into employee values(3,王五,女,4000.00); Query OK, 1 row affected (0.00 sec) mysql select * from employee; ----------------------------- | id | name | gender | salary | ----------------------------- | 1 | 张三 | 男 | 2000.00 | | 2 | 李四 | 男 | 1000.00 | | 3 | 王五 | 女 | 4000.00 | ----------------------------- 3 rows in set (0.00 sec) 要求3.1 将所有员工薪水修改为5000元 mysql update employee set salary 5000.00; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql select * from employee; ----------------------------- | id | name | gender | salary | ----------------------------- | 1 | 张三 | 男 | 5000.00 | | 2 | 李四 | 男 | 5000.00 | | 3 | 王五 | 女 | 5000.00 | ----------------------------- 3 rows in set (0.00 sec) 3.2将姓名为张三的员工薪水修改为3000元 mysql update employee set salary 3000.00 where name张三; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from employee; ----------------------------- | id | name | gender | salary | ----------------------------- | 1 | 张三 | 男 | 3000.00 | | 2 | 李四 | 男 | 5000.00 | | 3 | 王五 | 女 | 5000.00 | ----------------------------- 3 rows in set (0.00 sec) 3.3将姓名为李四的员工薪水修改为4000元gener改为女 mysql update employee set salary 4000.00,gender女 where name李四; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from employee; ----------------------------- | id | name | gender | salary | ----------------------------- | 1 | 张三 | 男 | 3000.00 | | 2 | 李四 | 女 | 4000.00 | | 3 | 王五 | 女 | 5000.00 | ----------------------------- 3 rows in set (0.00 sec) 3.4 将王五的薪水在原有基础上增加1000元 mysql update employee set salary salary 1000.00 where name王五; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from employee; ----------------------------- | id | name | gender | salary | ----------------------------- | 1 | 张三 | 男 | 3000.00 | | 2 | 李四 | 女 | 4000.00 | | 3 | 王五 | 女 | 6000.00 | ----------------------------- 3 rows in set (0.00 sec) 第三题 创建表 CREATE TABLE emp ( empno int(4) NOT NULL, ename varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, job varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, mgr int(4) NULL DEFAULT NULL, hiredate date NOT NULL, sai int(255) NOT NULL, comm int(255) NULL DEFAULT NULL, deptno int(2) NOT NULL, PRIMARY KEY (empno) USING BTREE ) ENGINE InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT Dynamic; 插入数据 INSERT INTO emp VALUES (1001, 甘宁, 文员, 1013, 2000-12-17, 8000, NULL, 20); INSERT INTO emp VALUES (1002, 黛绮丝, 销售员, 1006, 2001-02-20, 16000, 3000, 30); INSERT INTO emp VALUES (1003, 殷天正, 销售员, 1006, 2001-02-22, 12500, 5000, 30); INSERT INTO emp VALUES (1004, 刘备, 经理, 1009, 2001-04-02, 29750, NULL, 20); INSERT INTO emp VALUES (1005, 谢逊, 销售员, 1006, 2001-09-28, 12500, 14000, 30); INSERT INTO emp VALUES (1006, 关羽, 经理, 1009, 2001-05-01, 28500, NULL, 30); INSERT INTO emp VALUES (1007, 张飞, 经理, 1009, 2001-09-01, 24500, NULL, 10); INSERT INTO emp VALUES (1008, 诸葛亮, 分析师, 1004, 2007-04-19, 30000, NULL, 20); INSERT INTO emp VALUES (1009, 曾阿牛, 董事长, NULL, 2001-11-17, 50000, NULL, 10); INSERT INTO emp VALUES (1010, 韦一笑, 销售员, 1006, 2001-09-08, 15000, 0, 30); INSERT INTO emp VALUES (1011, 周泰, 文员, 1006, 2007-05-23, 11000, NULL, 20); INSERT INTO emp VALUES (1012, 程普, 文员, 1006, 2001-12-03, 9500, NULL, 30); INSERT INTO emp VALUES (1013, 庞统, 分析师, 1004, 2001-12-03, 30000, NULL, 20); INSERT INTO emp VALUES (1014, 黄盖, 文员, 1007, 2002-01-23, 13000, NULL, 10); INSERT INTO emp VALUES (1015, 张三, 保洁员, 1001, 2013-05-01, 80000, 50000, 50); 1.查询出部门编号为30的所有员工 mysql select * from emp where deptno30; --------------------------------------------------------------------- | empno | ename | job | mgr | hiredate | sai | comm | deptno | --------------------------------------------------------------------- | 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 | | 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 | | 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 | | 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 | | 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 | | 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 | --------------------------------------------------------------------- 6 rows in set (0.00 sec) -- 2. 所有销售员的姓名、编号和部门编号。 -- 3. 找出奖金高于工资的员工。 -- 4. 找出奖金高于工资60%的员工。 -- 5. 找出部门编号为10中所有经理和部门编号为20中所有销售员的详细资料。 -- 6. 找出部门编号为10中所有经理部门编号为20中所有销售员还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。 -- 7. 无奖金或奖金低于1000的员工。 -- 8. 查询名字由三个字组成的员工。 -- 注意一个汉字占三个字节 -- 9.查询2000年入职的员工。 -- 10. 查询所有员工详细信息用编号升序排序 -- 11. 查询所有员工详细信息用工资降序排序如果工资相同使用入职日期升序排序 -- 12.查询每个部门的平均工资 -- 13.查询每个部门的雇员数量 -- 14.查询每种工作的最高工资、最低工资、人数
