mysql练习题

最新推荐文章于 2023-07-21 01:35:16 发布
最新推荐文章于 2023-07-21 01:35:16 发布
阅读量2.9k 收藏 11
点赞数
分类专栏： mysql 文章标签： mysql linux
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45713326/article/details/109517874
版权

（1）create创建练习

offices表结构
字段名数据类型主键外键非空唯一自增
officeCodeINT(10)是否是是否
cityINT(11)否否是否否
addressVARCHAR(50)否否否否否
countryVARCHAR(50)否否否否否
postalCodeVARCHAR(25)否否否是否
employees表结构
字段名数据类型主键外键非空唯一自增
employeesNumberINT(11)是否是是是
lastNameVARCHAR(50)否否是否否
firstNameVARCHAR(50)否否是否否
mobileVARCHAR(25)否否否是否
officeCodeVARCHAR(10)否是是否否
jobTitleVARCHAR(50)否否是否否
birthDATETIME否否是否否
noteVARCHAR(255)否否否否否
sexVARCHAR(5)否否否否否

1、创建数据库company

mysql> create database company;
Query OK, 1 row affected (0.00 sec)
mysql> use company;
Database changed

2.创建表offices和employee

Offices表
mysql> create table offices(
    -> officeCode int(11) primary key  not null unique,
    -> city int(11)  not null,
    -> address varchar(50),
    -> country varchar(50),
    -> postalCode varchar(50) unique
    -> );

Employee表
mysql> create table employee(employeesNumber int(11) primary key  not null unique auto_increment, lastName varchar(50) not null, firstName varchar(50) not null, mobile  varchar(25) unique, officeCode int(10)  not null unique,jobTitle varchar(50) not null, birth datetime not null, note varchar(255), sex varchar(5),constraint ls foreign key(officeCode) references offices(officeCode));
Query OK, 0 rows affected (0.00 sec)

3.将employee的mobile字段修改到officeCode字段后面

mysql> desc employee;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| employeesNumber | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastName        | varchar(50)  | NO   |     | NULL    |                |
| firstName       | varchar(50)  | NO   |     | NULL    |                |
| mobile          | varchar(25)  | YES  | UNI | NULL    |                |
| officeCode      | int(10)      | NO   | UNI | NULL    |                |
| jobTitle        | varchar(50)  | NO   |     | NULL    |                |
| birth           | datetime     | NO   |     | NULL    |                |
| note            | varchar(255) | YES  |     | NULL    |                |
| sex             | varchar(5)   | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> alter table employee modify mobile varchar(50)  after officeCode;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;                                                                                                   +-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| employeesNumber | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastName        | varchar(50)  | NO   |     | NULL    |                |
| firstName       | varchar(50)  | NO   |     | NULL    |                |
| officeCode      | int(10)      | NO   | UNI | NULL    |                |
| mobile          | varchar(50)  | YES  | UNI | NULL    |                |
| jobTitle        | varchar(50)  | NO   |     | NULL    |                |
| birth           | datetime     | NO   |     | NULL    |                |
| note            | varchar(255) | YES  |     | NULL    |                |
| sex             | varchar(5)   | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

4.将表employee的birth字段改名为employee_birth

mysql> alter table employee change birth  employee_birth datetime;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| employeesNumber | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastName        | varchar(50)  | NO   |     | NULL    |                |
| firstName       | varchar(50)  | NO   |     | NULL    |                |
| officeCode      | int(10)      | NO   | UNI | NULL    |                |
| mobile          | varchar(50)  | YES  | UNI | NULL    |                |
| jobTitle        | varchar(50)  | NO   |     | NULL    |                |
| employee_birth  | datetime     | YES  |     | NULL    |                |
| note            | varchar(255) | YES  |     | NULL    |                |
| sex             | varchar(5)   | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

5.修改sex字段，数据类型为CHAR(1)，非空约束

mysql> alter table employee modify  sex char(1) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| employeesNumber | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastName        | varchar(50)  | NO   |     | NULL    |                |
| firstName       | varchar(50)  | NO   |     | NULL    |                |
| officeCode      | int(10)      | NO   | UNI | NULL    |                |
| mobile          | varchar(50)  | YES  | UNI | NULL    |                |
| jobTitle        | varchar(50)  | NO   |     | NULL    |                |
| employee_birth  | datetime     | YES  |     | NULL    |                |
| note            | varchar(255) | YES  |     | NULL    |                |
| sex             | char(1)      | NO   |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

6.删除字段note

mysql> alter table employee drop note;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| employeesNumber | int(11)     | NO   | PRI | NULL    | auto_increment |
| lastName        | varchar(50) | NO   |     | NULL    |                |
| firstName       | varchar(50) | NO   |     | NULL    |                |
| officeCode      | int(10)     | NO   | UNI | NULL    |                |
| mobile          | varchar(50) | YES  | UNI | NULL    |                |
| jobTitle        | varchar(50) | NO   |     | NULL    |                |
| employee_birth  | datetime    | YES  |     | NULL    |                |
| sex             | char(1)     | NO   |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

7.添加字段名favorite_activity，数据类型为VARCHAR(100)

mysql> alter table  employee add favorite_activity varchar(100) after sex;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| employeesNumber   | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastName          | varchar(50)  | NO   |     | NULL    |                |
| firstName         | varchar(50)  | NO   |     | NULL    |                |
| officeCode        | int(10)      | NO   | UNI | NULL    |                |
| mobile            | varchar(50)  | YES  | UNI | NULL    |                |
| jobTitle          | varchar(50)  | NO   |     | NULL    |                |
| employee_birth    | datetime     | YES  |     | NULL    |                |
| sex               | char(1)      | NO   |     | NULL    |                |
| favorite_activity | varchar(100) | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

8.删除表offices

mysql> drop table offices;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

mysql> alter table employee  drop foreign key ls;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table offices;
Query OK, 0 rows affected (0.00 sec)

9.修改表employee存储引擎为MyISAM

mysql> alter table employee engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `employeesNumber` int(11) NOT NULL AUTO_INCREMENT,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `officeCode` int(10) NOT NULL,
  `mobile` varchar(50) DEFAULT NULL,
  `jobTitle` varchar(50) NOT NULL,
  `employee_birth` datetime DEFAULT NULL,
  `sex` char(1) NOT NULL,
  `favorite_activity` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`employeesNumber`),
  UNIQUE KEY `employeesNumber` (`employeesNumber`),
  UNIQUE KEY `officeCode` (`officeCode`),
  UNIQUE KEY `mobile` (`mobile`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

10.将表employee名称修改为employee_info

mysql> alter table  employee rename employee_info;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employee_info     |
+-------------------+
1 row in set (0.00 sec)

（2）查询练习

employee表结构
字段名字段说明数据类型主键外键非空唯一自增
e_no员工编号INT(11)是否是是否
e_name员工姓名VARCHAR(50)否否是否否
e_gender员工性别CHAR(2)否否否否否
dept_no部门编号INT(11)否否是否否
e_job职位varchar(50)否否是否否
e_salary薪水INT(11)否否是否否
hireDate入职日期DATE否否是否否
dept表结构
字段名字段说明数据类型主键外键非空唯一自增
d_no部门编号INT(11)是是是是是
d_name部门名称VARCHAR(50)否否是否否
d_location部门地址VARCHAR(100)否否否否否
employee表中的记录
e_noe_namee_genderdept_noe_jobe_salaryhireDate
1001SMITHm20CLERK8002005/11/12
1002ALLENf30SALESMAN16002003/5/12
1003WARDf30SALESMAN12502003/5/12
1004JONESm20MANAGER29751998/5/18
1005MARTINm30SALESMAN12502001/6/12
1006BLAKEf30MANAGER28501997/2/15
1007CLARKm10MANAGER24502002/9/12
1008SCOTTm20ANALYSE30002003/5/12
1009KINGf10PRESIDENT50001995/1/1
1010TURNERf30SALESMAN15001997/10/12
1011ADAMSm20CLERK11001999/10/5
1012JAMESf30CLERK9502008/6/15
dept表中的记录
d_nod_named_location
10ACCOUNTINGShangHai
20RESEARCHBeiJing
30SALESShenZhen
40OPERATIONSFuJian

1.创建数据表employee和dept，并插入数据

mysql> create table employee(
    -> e_no int(11)  primary key not null unique,
    -> e_name varchar(50) not null,
    -> e_gender char(2),
    -> dept_no int(11) not null,
    -> e_job varchar(50) not null,
    -> e_salary int(11) not null,
-> hireDate date not null);
Query OK, 0 rows affected (0.00 sec)


mysql> create table dept(
    -> d_no int(11) primary key not null unique AUTO_INCREMENT,
    -> d_name VARCHAR(50) not null,
    -> d_location varchar(100),
    -> constraint ls foreign key(d_no) references employee(e_no));
Query OK, 0 rows affected (0.00 sec)


mysql>  insert  into employee VALUES
    -> (1001,'SMITH','M',20,'CLERK',800,'2005-11-12'),
    -> (1002,'ALLEN','F',30,'SALESMAN',1600,'2003-5-12'),
    -> (1003,'WARD','F',30,'SALESMAN',1250,'2003-5-12'),
    -> (1004,'JONES','M',20,'MANAGER',2975,'1998-5-18'),
    -> (1005,'MARTIN','M',30,'SALESMAN',1250,'2001-6-12'),
    -> (1006,'BLAKE','F',30,'MANAGER',2850,'1997-2-15'),
    -> (1007,'CLARK','M',10,'MANAGER',2450,'2002-9-12'),
    -> (1008,'SCOTT','M',20,'ANALYSE',3000,'2003-5-12'),
    -> (1009,'KING','F',10,'PRESIDENT',5000,'1995-1-1'),
    -> (1010,'TURNER','F',30,'SALESMAN',1500,'1997-10-12'),
    -> (1011,'ADAMS','M',20,'CLERK',1100,'1999-10-5'),
    -> (1012,'JAMES','F',30,'CLERK',950,'2008-6-15');
Query OK, 12 rows affected (0.01 sec)
Records: 12  Duplicates: 0  Warnings: 0


mysql> select * from employee;
 
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job     | e_salary | hireDate   |
+------+--------+----------+---------+-----------+----------+------------+
| 1001 | SMITH  | M        |      20 | CLERK     |      800 | 2005-11-12 |
| 1002 | ALLEN  | F        |      30 | SALESMAN  |     1600 | 2003-05-12 |
| 1003 | WARD   | F        |      30 | SALESMAN  |     1250 | 2003-05-12 |
| 1004 | JONES  | M        |      20 | MANAGER   |     2975 | 1998-05-18 |
| 1005 | MARTIN | M        |      30 | SALESMAN  |     1250 | 2001-06-12 |
| 1006 | BLAKE  | F        |      30 | MANAGER   |     2850 | 1997-02-15 |
| 1007 | CLARK  | M        |      10 | MANAGER   |     2450 | 2002-09-12 |
| 1008 | SCOTT  | M        |      20 | ANALYSE   |     3000 | 2003-05-12 |
| 1009 | KING   | F        |      10 | PRESIDENT |     5000 | 1995-01-01 |
| 1010 | TURNER | F        |      30 | SALESMAN  |     1500 | 1997-10-12 |
| 1011 | ADAMS  | M        |      20 | CLERK     |     1100 | 1999-10-05 |
| 1012 | JAMES  | F        |      30 | CLERK     |      950 | 2008-06-15 |
+------+--------+----------+---------+-----------+----------+------------+
12 rows in set (0.00 sec)


mysql> insert into dept values(10,'ACCOUNTING','ShangHai'), (20,'RESEARCH','BeiJing'), (30,'SALES','ShenZhen'), (40,'OPERATIONS','FuJian');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`dept`, CONSTRAINT `ls` FOREIGN KEY (`d_no`) REFERENCES `employee` (`e_no`))
mysql> alter table dept  drop foreign key ls;

mysql> insert into dept values
    -> (10,'ACCOUNTING','ShangHai'),
    -> (20,'RESEARCH','BeiJing'),
    -> (30,'SALES','ShenZhen'),
    -> (40,'OPERATIONS','FuJian');

mysql> select * from dept;
+------+------------+------------+
| d_no | d_name     | d_location |
+------+------------+------------+
|   10 | ACCOUNTING | ShangHai   |
|   20 | RESEARCH   | BeiJing    |
|   30 | SALES      | ShenZhen   |
|   40 | OPERATIONS | FuJian     |
+------+------------+------------+
4 rows in set (0.00 sec)

2.在employee表中，查询所有记录的e_no，e_name和e_salary字段值

mysql> select e_no,e_name,e_salary from employee;
+------+--------+----------+
| e_no | e_name | e_salary |
+------+--------+----------+
| 1001 | SMITH  |      800 |
| 1002 | ALLEN  |     1600 |
| 1003 | WARD   |     1250 |
| 1004 | JONES  |     2975 |
| 1005 | MARTIN |     1250 |
| 1006 | BLAKE  |     2850 |
| 1007 | CLARK  |     2450 |
| 1008 | SCOTT  |     3000 |
| 1009 | KING   |     5000 |
| 1010 | TURNER |     1500 |
| 1011 | ADAMS  |     1100 |
| 1012 | JAMES  |      950 |
+------+--------+----------+
12 rows in set (0.00 sec)

3、在employee表中，查询dept_no等于10和20的所有记录

mysql> select * from employee where dept_no between  10 and 20;
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job     | e_salary | hireDate   |
+------+--------+----------+---------+-----------+----------+------------+
| 1001 | SMITH  | M        |      20 | CLERK     |      800 | 2005-11-12 |
| 1004 | JONES  | M        |      20 | MANAGER   |     2975 | 1998-05-18 |
| 1007 | CLARK  | M        |      10 | MANAGER   |     2450 | 2002-09-12 |
| 1008 | SCOTT  | M        |      20 | ANALYSE   |     3000 | 2003-05-12 |
| 1009 | KING   | F        |      10 | PRESIDENT |     5000 | 1995-01-01 |
| 1011 | ADAMS  | M        |      20 | CLERK     |     1100 | 1999-10-05 |
+------+--------+----------+---------+-----------+----------+------------+
6 rows in set (0.00 sec)


mysql> select * from employee where dept_no>=10 and dept_no<=20;
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job     | e_salary | hireDate   |
+------+--------+----------+---------+-----------+----------+------------+
| 1001 | SMITH  | M        |      20 | CLERK     |      800 | 2005-11-12 |
| 1004 | JONES  | M        |      20 | MANAGER   |     2975 | 1998-05-18 |
| 1007 | CLARK  | M        |      10 | MANAGER   |     2450 | 2002-09-12 |
| 1008 | SCOTT  | M        |      20 | ANALYSE   |     3000 | 2003-05-12 |
| 1009 | KING   | F        |      10 | PRESIDENT |     5000 | 1995-01-01 |
| 1011 | ADAMS  | M        |      20 | CLERK     |     1100 | 1999-10-05 |
+------+--------+----------+---------+-----------+----------+------------+
6 rows in set (0.00 sec)

4、在employee表中，查询工资范围在800~2500之间的员工信息

mysql> select * from employee where e_salary between  800 and 2500;
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job    | e_salary | hireDate   |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | SMITH  | M        |      20 | CLERK    |      800 | 2005-11-12 |
| 1002 | ALLEN  | F        |      30 | SALESMAN |     1600 | 2003-05-12 |
| 1003 | WARD   | F        |      30 | SALESMAN |     1250 | 2003-05-12 |
| 1005 | MARTIN | M        |      30 | SALESMAN |     1250 | 2001-06-12 |
| 1007 | CLARK  | M        |      10 | MANAGER  |     2450 | 2002-09-12 |
| 1010 | TURNER | F        |      30 | SALESMAN |     1500 | 1997-10-12 |
| 1011 | ADAMS  | M        |      20 | CLERK    |     1100 | 1999-10-05 |
| 1012 | JAMES  | F        |      30 | CLERK    |      950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
8 rows in set (0.00 sec)

mysql> select * from employee where e_salary>=800 and e_salary<=2500;
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job    | e_salary | hireDate   |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | SMITH  | M        |      20 | CLERK    |      800 | 2005-11-12 |
| 1002 | ALLEN  | F        |      30 | SALESMAN |     1600 | 2003-05-12 |
| 1003 | WARD   | F        |      30 | SALESMAN |     1250 | 2003-05-12 |
| 1005 | MARTIN | M        |      30 | SALESMAN |     1250 | 2001-06-12 |
| 1007 | CLARK  | M        |      10 | MANAGER  |     2450 | 2002-09-12 |
| 1010 | TURNER | F        |      30 | SALESMAN |     1500 | 1997-10-12 |
| 1011 | ADAMS  | M        |      20 | CLERK    |     1100 | 1999-10-05 |
| 1012 | JAMES  | F        |      30 | CLERK    |      950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
8 rows in set (0.00 sec)

5.在employee表中，查询部门编号为20的部门中的员工信息

mysql> select * from employee where dept_no=20;
+------+--------+----------+---------+---------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job   | e_salary | hireDate   |
+------+--------+----------+---------+---------+----------+------------+
| 1001 | SMITH  | M        |      20 | CLERK   |      800 | 2005-11-12 |
| 1004 | JONES  | M        |      20 | MANAGER |     2975 | 1998-05-18 |
| 1008 | SCOTT  | M        |      20 | ANALYSE |     3000 | 2003-05-12 |
| 1011 | ADAMS  | M        |      20 | CLERK   |     1100 | 1999-10-05 |
+------+--------+----------+---------+---------+----------+------------+
4 rows in set (0.00 sec)

6、在employee表中，查询每个部门最高工资的员工信息

mysql> select  e_no, e_name,e_job,max(e_salary) from employee group by  e_job;
+------+--------+-----------+---------------+
| e_no | e_name | e_job     | max(e_salary) |
+------+--------+-----------+---------------+
| 1008 | SCOTT  | ANALYSE   |          3000 |
| 1001 | SMITH  | CLERK     |          1100 |
| 1004 | JONES  | MANAGER   |          2975 |
| 1009 | KING   | PRESIDENT |          5000 |
| 1002 | ALLEN  | SALESMAN  |          1600 |

7、查询员BLAKE所在部门和部门所在地

mysql> select e_name,e_job,d_location from employee,dept where e_name='BLAKE' and dept_no=dept.d_no;
+--------+---------+------------+
| e_name | e_job   | d_location |
+--------+---------+------------+
| BLAKE  | MANAGER | ShenZhen   |
+--------+---------+------------+
1 row in set (0.00 sec)

8.使用连接查询、查询所有员工的部门和部门信息

mysql> select * from  employee,dept where dept_no=dept.d_no;
+------+--------+----------+---------+-----------+----------+------------+------+------------+------------+
| e_no | e_name | e_gender | dept_no | e_job     | e_salary | hireDate   | d_no | d_name     | d_location |
+------+--------+----------+---------+-----------+----------+------------+------+------------+------------+
| 1007 | CLARK  | M        |      10 | MANAGER   |     2450 | 2002-09-12 |   10 | ACCOUNTING | ShangHai   |
| 1009 | KING   | F        |      10 | PRESIDENT |     5000 | 1995-01-01 |   10 | ACCOUNTING | ShangHai   |
| 1001 | SMITH  | M        |      20 | CLERK     |      800 | 2005-11-12 |   20 | RESEARCH   | BeiJing    |
| 1004 | JONES  | M        |      20 | MANAGER   |     2975 | 1998-05-18 |   20 | RESEARCH   | BeiJing    |
| 1008 | SCOTT  | M        |      20 | ANALYSE   |     3000 | 2003-05-12 |   20 | RESEARCH   | BeiJing    |
| 1011 | ADAMS  | M        |      20 | CLERK     |     1100 | 1999-10-05 |   20 | RESEARCH   | BeiJing    |
| 1002 | ALLEN  | F        |      30 | SALESMAN  |     1600 | 2003-05-12 |   30 | SALES      | ShenZhen   |
| 1003 | WARD   | F        |      30 | SALESMAN  |     1250 | 2003-05-12 |   30 | SALES      | ShenZhen   |
| 1005 | MARTIN | M        |      30 | SALESMAN  |     1250 | 2001-06-12 |   30 | SALES      | ShenZhen   |
| 1006 | BLAKE  | F        |      30 | MANAGER   |     2850 | 1997-02-15 |   30 | SALES      | ShenZhen   |
| 1010 | TURNER | F        |      30 | SALESMAN  |     1500 | 1997-10-12 |   30 | SALES      | ShenZhen   |
| 1012 | JAMES  | F        |      30 | CLERK     |      950 | 2008-06-15|30 | SALES      | ShenZhen   |
+------+--------+----------+---------+-----------+----------+------------+------+------------+------------+
12 rows in set (0.00 sec)

在这里插入图片描述

9.在employee表中，计算每个部门各多少名员工

mysql> select e_job,count(e_job) from employee group by e_job;
+-----------+--------------+
| e_job     | count(e_job) |
+-----------+--------------+
| ANALYSE   |            1 |
| CLERK     |            3 |
| MANAGER   |            3 |
| PRESIDENT |            1 |
| SALESMAN  |            4 |
+-----------+--------------+
5 rows in set (0.00 sec)

10、在employee表中，计算不用类型职工的总工资数

mysql> select d_name,count(dept_no) from employee,dept where d_no=dept_no group by d_name;
+------------+----------------+
| d_name     | count(dept_no) |
+------------+----------------+
| ACCOUNTING |              2 |
| RESEARCH   |              4 |
| SALES      |              6 |
+------------+----------------+
3 rows in set (0.00 sec)

11.在employee表中，计算不同部门的平均工资

mysql> select dept_no,avg(e_salary) from employee group by dept_no;
+---------+---------------+
| dept_no | avg(e_salary) |
+---------+---------------+
|      10 |     3725.0000 |
|      20 |     1968.7500 |
|      30 |     1566.6667 |
+---------+---------------+
3 rows in set (0.00 sec)

12、在employee表中，查询工资低于1500的员工信息

mysql> select * from employee where e_salary<1500;
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job    | e_salary | hireDate   |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | SMITH  | M        |      20 | CLERK    |      800 | 2005-11-12 |
| 1003 | WARD   | F        |      30 | SALESMAN |     1250 | 2003-05-12 |
| 1005 | MARTIN | M        |      30 | SALESMAN |     1250 | 2001-06-12 |
| 1011 | ADAMS  | M        |      20 | CLERK    |     1100 | 1999-10-05 |
| 1012 | JAMES  | F        |      30 | CLERK    |      950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
5 rows in set (0.00 sec)

13、在employee表中，将查询记录先按照部门编号由高到低排列，在按员工工资由高到低排列

mysql> select dept_no  from employee order by dept_no desc;
+---------+
| dept_no |
+---------+
|      30 |
|      30 |
|      30 |
|      30 |
|      30 |
|      30 |
|      20 |
|      20 |
|      20 |
|      20 |
|      10 |
|      10 |
+---------+
12 rows in set (0.00 sec)



mysql> select  e_salary from employee order by  e_salary desc;
+----------+
| e_salary |
+----------+
|     5000 |
|     3000 |
|     2975 |
|     2850 |
|     2450 |
|     1600 |
|     1500 |
|     1250 |
|     1250 |
|     1100 |
|      950 |
|      800 |
+----------+
12 rows in set (0.00 sec)

14、在employee表中，查询员工姓名以字母A或S开头的员工信息

mysql> select * from  employee where e_name regexp '^[AS]';
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job    | e_salary | hireDate   |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | SMITH  | M        |      20 | CLERK    |      800 | 2005-11-12 |
| 1002 | ALLEN  | F        |      30 | SALESMAN |     1600 | 2003-05-12 |
| 1008 | SCOTT  | M        |      20 | ANALYSE  |     3000 | 2003-05-12 |
| 1011 | ADAMS  | M        |      20 | CLERK    |     1100 | 1999-10-05 |
+------+--------+----------+---------+----------+----------+------------+
4 rows in set (0.00 sec)

mysql> select * from  employee where e_name regexp '^S|^A';
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job    | e_salary | hireDate   |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | SMITH  | M        |      20 | CLERK    |      800 | 2005-11-12 |
| 1002 | ALLEN  | F        |      30 | SALESMAN |     1600 | 2003-05-12 |
| 1008 | SCOTT  | M        |      20 | ANALYSE  |     3000 | 2003-05-12 |
| 1011 | ADAMS  | M        |      20 | CLERK    |     1100 | 1999-10-05 |
+------+--------+----------+---------+----------+----------+------------+
4 rows in set (0.00 sec)

15.在employee表中，查询到目前为止，工龄大于等于10年的员工信息

mysql> SELECT * FROM employee where YEAR(CURDATE()) - YEAR(hireDate) >= 10;
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job     | e_salary | hireDate   |
+------+--------+----------+---------+-----------+----------+------------+
| 1001 | SMITH  | M        |      20 | CLERK     |      800 | 2005-11-12 |
| 1002 | ALLEN  | F        |      30 | SALESMAN  |     1600 | 2003-05-12 |
| 1003 | WARD   | F        |      30 | SALESMAN  |     1250 | 2003-05-12 |
| 1004 | JONES  | M        |      20 | MANAGER   |     2975 | 1998-05-18 |
| 1005 | MARTIN | M        |      30 | SALESMAN  |     1250 | 2001-06-12 |
| 1006 | BLAKE  | F        |      30 | MANAGER   |     2850 | 1997-02-15 |
| 1007 | CLARK  | M        |      10 | MANAGER   |     2450 | 2002-09-12 |
| 1008 | SCOTT  | M        |      20 | ANALYSE   |     3000 | 2003-05-12 |
| 1009 | KING   | F        |      10 | PRESIDENT |     5000 | 1995-01-01 |
| 1010 | TURNER | F        |      30 | SALESMAN  |     1500 | 1997-10-12 |
| 1011 | ADAMS  | M        |      20 | CLERK     |     1100 | 1999-10-05 |
| 1012 | JAMES  | F        |      30 | CLERK     |      950 | 2008-06-15 |
+------+--------+----------+---------+-----------+----------+------------+
12 rows in set (0.00 sec)

（3）索引

writers
字段名数据类型主键外键非空唯一自增
w_idSMALLINT(11)是否是是是
w_nameVARCHAR(255)否否是否否
w_addressVARCHAR(255)否否否否否
w_ageCHAR(2)否否是否否
w_noteVARCHAR(255)否否否否否

1、在数据库index_test中创建writers，存储引擎为MyISAM，创建表的同时在w_id字段上添加名称为UniqIdx的唯一索引

mysql> create table writers(
    -> w_id smallint(11) primary key not null auto_increment,
    -> w_name varchar(255) not null ,
    -> w_address varchar(255) ,
    -> w_age char(2) not null,
    -> w_note varchar(255),
    -> unique index UniqIdx(w_id)
    -> )engine=MyIsAM;
Query OK, 0 rows affected (0.13 sec)

2、使用ALTER TABLE语句在w_name字段上建立名称为nameIdx的普通索引

mysql> alter table writers add Index  nameIdx(w_name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、使用CREATE TABLE 语句在w_address和w_age字段上建立名称为MultiIdx的组合索引

mysql> create index multiidx on writers(w_address,w_age);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

4、使用CREATE INDEX语句在w_note字段上建立名称为FTIdx的全文索引

mysql> create fulltext index ftidex on writers(w_note);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

5、删除名称为FTIdx的全文索引

mysql> drop index FTIdex on writers;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

（4）权限

player表结构
字段名数据类型主键外键非空唯一自增
playidint（11)是否是否否
playnamevarchar(30）否否是否否
teamnumint（11)否否是是否
infovarchar(50）否否否否否

1.创建team库，在库内创建一个表player

mysql> create database  team;
Query OK, 1 row affected (0.00 sec)

mysql> use team
Database changed
mysql> create table player(
    -> playid int(11)  primary key not null,
    -> playname varchar(30) not null,
    -> teamnum int(11) not null unique,
    -> info varchar(50));
Query OK, 0 rows affected (0.09 sec)

2、创建一个新账户，用户名为account1，该用户通过本地主机连接数据库，密码为oldpwd1

mysql> CREATE USER 'account1'@'localhost' IDENTIFIED BY 'oldpwd1';
Query OK, 0 rows affected (0.00 sec)

mysql> Ctrl-C -- exit!
Aborted
[root@bogon ~]# mysql -u account1 -poldpwd1
[root@bogon ~]# mysql -u account1 -poldpwd1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.22-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3、授权该用户对team数据库中player表的select和insert权限，并且授权该用户对player表的info字段的update权限

切换回root用户登录
mysql> grant select,insert,update(info) on team.player to 'account1'@'localhost' identified by 'oldpwd1';
Query OK, 0 rows affected (0.00 sec)

4、创建SQL语句，更改account1用户的密码为newpwd2

mysql> set password for  'account1'@'localhost'=password('newpwd2');
Query OK, 0 rows affected (0.00 sec)

5、重新加载权限表

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

6、查看权限给account1用户的权限

mysql> show grants for    'account1'@'localhost'\G
*************************** 1. row ***************************
Grants for account1@localhost: GRANT USAGE ON *.* TO 'account1'@'localhost' IDENTIFIED BY PASSWORD '*6FE6C49810423D44E33DD9EAA61C4D419AF07278'
*************************** 2. row ***************************
Grants for account1@localhost: GRANT SELECT, INSERT, UPDATE (info) ON `team`.`player` TO 'account1'@'localhost'
2 rows in set (0.00 sec)

7、收回account1 用户的权限

mysql> revoke all privileges,grant option from 'account1'@'localhost';
Query OK, 0 rows affected (0.00 sec)



mysql> show grants for    'account1'@'localhost'\G
*************************** 1. row ***************************
Grants for account1@localhost: GRANT USAGE ON *.* TO 'account1'@'localhost' IDENTIFIED BY PASSWORD '*6FE6C49810423D44E33DD9EAA61C4D419AF07278'
1 row in set (0.00 sec)

8、将account1 用户的账户信息从系统中删除

mysql> drop user 'account1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

N . TO ‘account1’@‘localhost’ IDENTIFIED BY PASSWORD ‘*6FE6C49810423D44E33DD9EAA61C4D419AF07278’
*************************** 2. row ***************************
Grants for account1@localhost: GRANT SELECT, INSERT, UPDATE (info) ON team.player TO ‘account1’@‘localhost’
2 rows in set (0.00 sec)


**7、收回account1 用户的权限**

mysql> revoke all privileges,grant option from ‘account1’@‘localhost’;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for ‘account1’@‘localhost’\G
*************************** 1. row ***************************
Grants for account1@localhost: GRANT USAGE ON . TO ‘account1’@‘localhost’ IDENTIFIED BY PASSWORD ‘*6FE6C49810423D44E33DD9EAA61C4D419AF07278’
1 row in set (0.00 sec)


**8、将account1 用户的账户信息从系统中删除**

mysql> drop user ‘account1’@‘localhost’;
Query OK, 0 rows affected (0.00 sec)
smile学无止境1
关注
  • 0
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
db2 删除索引_MySQL【笔记】：索引练习题
weixin_39932181的博客
10-27 412
（1） 在数据库 index_ test 中创建表 writers， 存储引擎为 MyISAM，创建表的同时 在 w_ id 字段 上添加名称为UniqIdx的唯一索引。mysql> create table writers -> ( -> w_id smallint(11) primary key not null auto_increment, -> ...
MySQL实战（六）：MySQL视图、索引
Sirius的博客
12-20 1581
MySQL视图练习
MySQL练习-employees数据库(一)
aiqituo9030的博客
01-06 1822
安装一个数据库示例——employees 使用工具为Navicat——（ctrl + q: 打开新查询窗口 ctrl + r: 运行当前窗口内的所有语句） 参考http://www.cnblogs.com/chenyucong/p/5734800.html 该数据库中有6张关联，记录的数据为某一公司9个部门所有员工的薪资（包括离职员工），以下为关系图。 第一...
mysql的练习题（employ和dept）
qq_41737952的博客
05-31 7560
创建数据表employee和dept。CREATE TABLE dept(d_no         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,d_name       VARCHAR(50),d_location     VARCHAR(100));由于employee表dept_no依赖于父表dept的主键d_no，因此需要先创建dept表，然后创建empl...
mysql查询34练
自律
09-21 393
文末是sql脚本，执行建表 1、取得每个部门最高薪水的人员名称 求每个部门部门号和最高薪水 SELECT `DEPTNO`,MAX(`SAL`) maxsal FROM `emp` GROUP BY `DEPTNO`; 最终sql语句 SELECT e.`ENAME`,t.* FROM (SELECT `DEPTNO`,MAX(`SAL`) maxsal FROM `emp` GROUP BY `DEPTNO`) t,emp e WHERE t.`DEPTNO`=e.`DEPTNO` AND t.max
Mysql----mysql练习题（单表多表查询）
m0_58485238的博客
01-08 824
【代码】Mysql----mysql练习题（单表多表查询）
MySQL练习题（34道）
09-12
MySQL练习题涵盖了一系列基础到进阶的查询技巧，旨在帮助初学者巩固基础，熟练掌握数据库操作。以下是对这些练习题的详细解析： 1. **取得每个部门最高薪水的人员名称**： 这个问题可以通过`GROUP BY`与`MAX`函数...
MySQL练习题及答案-11页
06-01
MySQL是一种广泛使用的开源关系型数据库管理系统，其特点包括高效、可靠和可扩展性。...通过这些练习题，我们可以深入理解MySQL的各个核心概念，提升在数据库管理、查询优化和故障排查方面的能力。
MySQL练习题.md
最新发布
10-13
mysql经典的练习题，修改了网上的错误的解法，并提供易错写法、提供正确解法、提供较优解法。 虽然只有20多道，但足够了。
查询数据
q86974199
11-15 3553
#.查询部门表和雇员表所有信息 select * from dept; select * from employee;  #1.在employee表中，查询所有记录的e_no、e_name和e_salary字段值。 select e_no,e_name,e_salary from employee;   #2.在employee表中，查询dept_no等于10和2
MySQL索引（简介 & 练习）
JohnnyG2000的博客
05-04 1562
表结构如下： MySQL索引练习一级目录素材练习要求 一级目录 素材 writers表结构: 字段名 数据类型 主键 外键 非空 唯一 自增 w_id SMALLINT(11) 是 否 是 是 是 w_name VARCHAR(255) 否 否 是 否 否 w_address VARCHAR(255) 否 否 否 否 否 w_age CHAR(2) 否 否 是 否 否 w_note VARCHAR(255) 否 否 否 否 否 练习要求
MySQL常用语法练习题：聚合函数，where,group by ,having ,inner join on,order by,limit,like等
闫振兴的博客
06-13 4080
题目： 根据不同条件对表进行查询操作， 1：employee表结构 字段名 字段说明 数据类型 主键 外键 非空 唯一 自增 e_no 员工编号 int（11） 是 否 是 是 否 e_name 员工姓名 varchar(50) 否 否 是 否 否 e_gender员工性别 char(2) 否 否 否 否 否...
mysql创建一个新的账户用于远程访问，root用于本地访问
m0_69011137的博客
07-21 203
【代码】mysql创建一个新的账户用于远程访问，root用于本地访问。
MySQL第一天
qq_51552328的博客
04-10 302
CREATE DATABASE company; USE company; CREATE TABLE offices( officeCode INT(10) PRIMARY KEY NOT NULL UNIQUE, city VARCHAR(50) NOT NULL, address VARCHAR(50), country VARCHAR(50) NOT NULL, postalCode VARCHAR(50) UNIQUE ); CREATE TABLE employees( employeeNum.
2018.12.20
weixin_44167261的博客
12-21 181
一、查询多表数据 1.查询单表数据 SELECT 字段列表|* FROM 表名 [WHERE 条件] [GROUP BY 字段] [HAVING 条件] [ORDER BY 字段 ASC|DESC] [LIMIT [偏移量,] 记录条数] 2.查询多表数据 方式一： 连接查询 普通多表连接 SELECT 字段… FROM 表1,表2… WHERE 连接条件 AND 其它条件 内连接 ...
在MySQL中用root用户创建新的用户并为之授予权限
热门推荐
江南好
04-06 1万+
前言 在一个正常发展的公司里面都会进行着明显的数据权限管理，特别是操作数据库这种安全性要求比较高的软件时。毕竟，万一你误删了某些数据库，损失就大了。为此，在公司里进行项目开发时，一般会一个项目就创建一个专门的用户指定其权限只能控制某一个数据库进行操作。这样在开发项目的过程中，就只能用该用户操作指定的数据库，而无法影响到其他的数据库了，无疑，这种做法提高了数据操作的安全性。 创建用户 要创建一个新的mysql用户的话，需要用root用户登录MySQL服务器并进行操作。 首先登录mysql客户端：mysql -
P73 第13章_约束练习题
weixin_54671087的博客
12-08 841
第13章_约束练习题 练习一 #练习1 已经存在数据库test04_emp，两张表emp2和dept2 CREATE DATABASE test04_emp; USE test04_emp; CREATE TABLE emp2( id INT, emp_name VARCHAR(15) ); CREATE TABLE dept2( id INT, dept_name VARCHAR(15) ); #1.向表emp2的id列中添加PRIMARY KEY约束 ALTER TABLE emp2 ADD
MySQL数据库查询练习
weixin_51985152的博客
04-19 3602
MySQL练习实验要求实验过程 实验要求 创建数据表：employee表 创建部门表 dept 输入记录： employee表 部门表中的记录 查询练习： 查询所有记录的e_no、e_name和e_salary字段值 查询dept_no等于10和20的所有记录 查询工资范围在800~2500之间的员工信息 查询部门编号为20的部门中的员工信息 查询每个部门最高工资的员工信息 查询员工BLAKE所在部门和部门所在地 查询所有员工的部门和部门信息 计算每个部门各有多少名员工 计算不同类型职工的总工资数
软件测试mysql练习题
08-13
当涉及到软件测试MySQL的练习题时，以下是一些可以尝试的问题： 1. 编写一个测试用例，验证一个SQL查询是否返回预期的结果。 2. 设计一个测试计划，用于测试一个MySQL数据库的备份和恢复功能。 3. 编写一个测试用例，验证一个存储过程在给定参数下是否按预期运行。 4. 设计一个性能测试方案，评估一个MySQL数据库在高并发负载下的性能表现。 5. 编写一个测试用例，验证一个触发器在特定条件下是否按预期触发。 6. 设计一个安全性测试方案，评估一个MySQL数据库的安全性设置。 7. 编写一个测试用例，验证一个SQL语句是否在给定条件下执行了正确的事务处理。 8. 设计一个兼容性测试方案，确保一个MySQL数据库与不同版本的应用程序兼容。 9. 编写一个测试用例，验证一个索引在特定数据集下是否提高了查询性能。 10. 设计一个容错测试方案，评估一个MySQL数据库在异常情况下的容错能力。 这些练习题可以帮助你巩固对软件测试MySQL的理解，并提供实际应用的机会。通过实践这些练习题，你可以提高自己的MySQL软件测试技能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值