mysql练习题

(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)


  • 0
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值