Mysql的简单操作

一.数据库的基本操作

步骤1：登陆数据库：

mysql -uroot -p

步骤2：创建数据库test：

CREATE DATABASE test;

SHOW DATABASES;

步骤3：选择当前数据库为test：

USE test;

查看数据库test的信息：

SHOW CREATE DATABASE test;

步骤4：删除数据库test：

DROP DATABASE test;

二.数据表的基本操作

步骤1：登陆数据库

mysql -uroot -p

步骤2：创建数据库Company

CREATE DATABASE company;

步骤3：创建表offices

CREATE TABLE offices 

(

officeid  int(10) NOT NULL ,

city        varchar(50) NOT NULL,

country     varchar(50) NOT NULL,

PRIMARY KEY  (officeid)

);

步骤4：创建表employees

CREATE TABLE employees 

(

employeeid  int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

lastName         varchar(50) NOT NULL,

firstName        varchar(50) NOT NULL,

mobile           varchar(12) NOT NULL,

officeid      int(10) NOT NULL,

birth            datetime,

note            varchar(255),

sex             varchar(5),

CONSTRAINT office_fk FOREIGN KEY(officeid) REFERENCES offices(officeid)

);

步骤5：将表employees的mobile字段修改到officeid字段后面

ALTER TABLE employees MODIFY mobile varchar(12) AFTER officeid;

步骤6：将birth字段改名为birthday;

ALTER TABLE employees CHANGE birth birthday DATETIME;

步骤7：修改sex字段，数据类型为CHAR(1)

ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;

步骤8：删除字段note

ALTER TABLE employees DROP note;

步骤9：增加字段名hobby，数据类型为VARCHAR(100)

ALTER TABLE employees ADD hobby VARCHAR(100);

步骤10：删除表offices

1）删除employees表的外键约束，输入如下语句：

ALTER TABLE employees DROP FOREIGN KEY office_fk;

2）删除表offices，输入如下语句：

DROP TABLE offices;

步骤12：将表名修改为employees_info

ALTER TABLE employees RENAME employees_info;

三.数据操作

步骤1：创建数据表employee和dept，

创建dept表

CREATE TABLE dept

(

d_no         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

d_name       VARCHAR(50),

d_location  VARCHAR(100)

);

创建employee表

CREATE TABLE employee

(

e_no        INT NOT NULL PRIMARY KEY,

e_name      VARCHAR(100) NOT NULL,

e_gender    CHAR(2) NOT NULL,

dept_no    INT NOT NULL,

e_job       VARCHAR(100) NOT NULL,

e_salary   SMALLINT NOT NULL,

CONSTRAINT dno_fk FOREIGN KEY(dept_no)

REFERENCES dept(d_no)

);

步骤2：将指定记录分别插入两个表中，执行过程如下：

向dept表中插入数据

INSERT INTO dept 

VALUES (10, 'accouting', 'ShangHai'),

(20, 'reasarch ', 'BeiJing '),

(30, 'sales ', 'ShenZhen '),

(40, 'market ', 'FuJian ');

向employee表中插入数据

INSERT INTO employee 

VALUES (1001, 'smith', 'm',20, 'clerk',5600),

(1002, 'allen', 'f',30, 'saleman', 8000),

(1003, 'wade', 'f',30, 'saleman', 6000),

(1004, 'jeams', 'm',20, 'manage', 5000),

(1005, 'blank', 'm',30, 'president', 7800), 

(1006, 'scott', 'f',30, 'clerk', 7500),

(1007, '小明', 'm',10, 'president', 12000),

(1008, '小小明', 'm',20, 'counting', 6500),

(1009, '红晓', 'f',10, 'analys', 7500);

步骤3：在employee表中，查询所有记录的e_no、e_name和e_salary字段值，语句如下：

SELECT e_no, e_name, e_salary;

步骤4：在employee表中，查询dept_no等于10和20的所有记录。

SELECT * FROM employee WHERE dept_no IN (10, 20);

步骤5：在employee表中，查询工资范围在5000到7500之间的员工信息。

SELECT * FROM employee WHERE e_salary BETWEEN 5000 AND 7500;

步骤6：在employee表中，查询部门编号为20的部门中的员工信息。

SELECT * from employee WHERE dept_no = 20;

步骤7：在employee表中，查询每个部门最高工资的员工信息。

SELECT dept_no, MAX(e_salary) FROM employee GROUP BY dept_no;

步骤8：在employee表中，计算每个部门各有多少名员工。

SELECT dept_no, COUNT(*) FROM employee GROUP BY dept_no;

步骤9：在employee表中，查询工资低于7500的员工信息。

SELECT * FROM employee WHERE e_salary < 7500;

步骤10：在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列。

SELECT e_name,dept_no, e_salary 

FROM employee ORDER BY dept_no DESC, e_salary DESC;

模糊查询以及更新修改数据操作：

在原来的数据库的基础上操作：

步骤1. 查询以明结尾的的数据

select * from employee where e_name like "%明";

步骤2. 查询以红开头的的数据

select * from employee where e_name like "红%";

步骤3. 查询名字有小的的数据

select * from employee where e_name like "%小%";

步骤4. 查询名字为两个字同时以小字为开头的数据

select * from employee where e_name like "小_";

步骤5. 查询名字为三个字同时以小字为开头的数据

select * from employee where e_name like "小__";

步骤6. 查询名字为不包含小字的数据

select * from employee where e_name  not like "%小%";

 步骤7. 删除数据里面工资为12000的记录

delete from employee where e_salary = 12000;

步骤8. 更新数据里面名字为wade的记录，把部门id改为20

update  employee set dept_no= 20 where e_name = "wade";

查询综合案例

步骤1：创建数据表employee和dept，
创建dept表
CREATE TABLE dept
(
d_no         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
d_name       VARCHAR(50),
d_location  VARCHAR(100)
);

创建employee表
CREATE TABLE employee
(
e_no        INT NOT NULL PRIMARY KEY,
e_name      VARCHAR(100) NOT NULL,
e_gender    CHAR(2) NOT NULL,
dept_no    INT NOT NULL,
e_job       VARCHAR(100) NOT NULL,
e_salary   SMALLINT NOT NULL,
hireDate   DATE,
CONSTRAINT dno_fk FOREIGN KEY(dept_no)
REFERENCES dept(d_no)
);

步骤2：将指定记录分别插入两个表中，执行过程如下：
向dept表中插入数据
INSERT INTO dept 
VALUES (10, 'ACCOUNTING', 'ShangHai'),
(20, 'RESEARCH ', 'BeiJing '),
(30, 'SALES ', 'ShenZhen '),
(40, 'OPERATIONS ', 'FuJian ');

向employee表中插入数据
INSERT INTO employee 
VALUES (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, 'ANALYST', 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', 'm',30, 'CLERK', 950,'2008-06-15');

步骤3：在employee表中，查询所有记录的e_no、e_name和e_salary字段值，语句如下：
SELECT e_no, e_name, e_salary FROM employee;

步骤4：在employee表中，查询dept_no等于10和20的所有记录。
SELECT * FROM employee WHERE dept_no IN (10, 20);

步骤5：在employee表中，查询工资范围在800到2500之间的员工信息。
SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;

步骤6：在employee表中，查询部门编号为20的部门中的员工信息。
SELECT * from employee WHERE dept_no = 20;

步骤7：在employee表中，查询每个部门最高工资的员工信息。
SELECT dept_no, MAX(e_salary) FROM employee GROUP BY dept_no;

步骤8：查询员工BLAKE所在部门和部门所在地。（嵌套查询）
SELECT d_no, d_location  FROM dept WHERE d_no=
(SELECT dept_no FROM employee WHERE e_name='BLAKE');

步骤9：使用连接查询，查询所有员工的部门和部门信息。（连表查询）
SELECT e_no, e_name, dept_no, d_name,d_location
FROM employee, dept WHERE dept.d_no=employee.dept_no;

步骤10：在employee表中，计算每个部门各有多少名员工。
SELECT dept_no, COUNT(*) FROM employee GROUP BY dept_no;

步骤11：在employee表中，计算不同类型职工的总工资数。
SELECT e_job, SUM(e_salary) FROM employee GROUP BY e_job;

步骤12：在employee表中，计算不同部门的平均工工资。
SELECT dept_no, AVG(e_salary) FROM employee GROUP BY dept_no;

步骤13：在employee表中，查询工资低于1500的员工信息。
SELECT * FROM employee WHERE e_salary < 1500;

步骤14：在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列。
SELECT e_name,dept_no, e_salary 
FROM employee ORDER BY dept_no DESC, e_salary DESC;

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

表A记录如下：       
aid　　　　aNum
1　　　　　a1
2　　　　　a2
3　　　　　a3
4　　　　　a4
5　　　　　a5

表B记录如下:
bid　　　　bNum
1　　　　　b1
2　　　　　b2
3　　　　　b3
4　　　　　b4
8　　　　　b5

分别执行以下三条指令：
select * from A
right join B
on A.aid = B.bid

select * from A
left join B
on A.aid = B.bid

select * from A
inner join B
on A.aid = B.bid