（1）create创建练习
| offices表结构 | ||||||
|---|---|---|---|---|---|---|
| 字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| officeCode | INT(10) | 是 | 否 | 是 | 是 | 否 |
| city | INT(11) | 否 | 否 | 是 | 否 | 否 |
| address | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
| country | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
| postalCode | VARCHAR(25) | 否 | 否 | 否 | 是 | 否 |
| employees表结构 | ||||||
|---|---|---|---|---|---|---|
| 字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| employeesNumber | INT(11) | 是 | 否 | 是 | 是 | 是 |
| lastName | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
| firstName | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
| mobile | VARCHAR(25) | 否 | 否 | 否 | 是 | 否 |
| officeCode | VARCHAR(10) | 否 | 是 | 是 | 否 | 否 |
| jobTitle | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
| birth | DATETIME | 否 | 否 | 是 | 否 | 否 |
| note | VARCHAR(255) | 否 | 否 | 否 | 否 | 否 |
| sex | VARCHAR(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_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/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 |
| dept表中的记录 | |||
|---|---|---|---|
| d_no | d_name | d_location | |
| 10 | ACCOUNTING | ShangHai | |
| 20 | RESEARCH | BeiJing | |
| 30 | SALES | ShenZhen | |
| 40 | OPERATIONS | FuJian |
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_id | SMALLINT(11) | 是 | 否 | 是 | 是 | 是 |
| w_name | VARCHAR(255) | 否 | 否 | 是 | 否 | 否 |
| w_address | VARCHAR(255) | 否 | 否 | 否 | 否 | 否 |
| w_age | CHAR(2) | 否 | 否 | 是 | 否 | 否 |
| w_note | VARCHAR(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表结构 | ||||||
|---|---|---|---|---|---|---|
| 字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| playid | int（11) | 是 | 否 | 是 | 否 | 否 |
| playname | varchar(30） | 否 | 否 | 是 | 否 | 否 |
| teamnum | int（11) | 否 | 否 | 是 | 是 | 否 |
| info | varchar(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)
6568
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
