初识MySQL笔记

一、建库建表

1）.新建数据库，表之前先判断是否存在(if exists)
2）.数据库建好之后需要使用（use 数据库名）
3）.主键：primary key
4）.唯一：unique
5）.自增：auto_increment
6）.外键添加：constraint FK_empId foreign key(empId) references deptInfo(empId)
7）.设置引擎：engine=innodb default charset=UTF8 auto_increment=1001

二、数据库的增删查改

1）.增：insert into 表名 values();
2）.删：delete from 表名 where 条件;
3) .查：select * from 表名 where 条件;
4）.改：update 表名 set ... where 条件;

-- 判断数据库是否存在
DROP DATABASE IF EXISTS EMPDB;

-- 新建数据库
CREATE DATABASE EMPDB;

-- 使用数据库
USE EMPDB;

-- 判断表DeptInfo是否存在
DROP TABLE IF EXISTS DeptInfo;

-- 新建表
CREATE TABLE DeptInfo(
   DeptID VARCHAR(10) PRIMARY KEY,
   DeptName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB CHARSET=utf8;

-- 判断表EmpInfo是否存在
DROP TABLE IF EXISTS EmpInfo;

-- 新建表
CREATE TABLE EmpInfo(
   EmpID INT PRIMARY KEY AUTO_INCREMENT,
   EmpName VARCHAR(20) NOT NULL,
   EmpSex VARCHAR(2) NOT NULL DEFAULT '男',
   EmpBirth DATETIME NOT NULL,
   EmpTel VARCHAR(20) NOT NULL UNIQUE,
   DeptID VARCHAR(10) NOT NULL,
   CONSTRAINT FK_DeptID FOREIGN KEY(DeptID) REFERENCES DeptInfo(DeptID)
)ENGINE=INNODB CHARSET=utf8 AUTO_INCREMENT=1001;

-- 添加初始数据
INSERT INTO DeptInfo VALUES('A02','财务部');
INSERT INTO DeptInfo VALUES('A05','工程部');
INSERT INTO DeptInfo VALUES('A01','人事部');
INSERT INTO DeptInfo VALUES('A03','销售部');
INSERT INTO DeptInfo VALUES('A04','研发部');

INSERT INTO EmpInfo VALUES(DEFAULT , '陈二' , '男' , '1991-05-02' , '189199' , 'A04');
INSERT INTO EmpInfo VALUES(DEFAULT , '陈七' , '女' , '1991-01-13' , '189111' , 'A02');
INSERT INTO EmpInfo VALUES(DEFAULT , '黄二' , '男' , '1991-03-01' , '189321' , 'A03');
INSERT INTO EmpInfo VALUES(DEFAULT , '李四' , '男' , '1991-02-08' , '189456' , 'A05');
INSERT INTO EmpInfo VALUES(DEFAULT , '刘一' , '女' , '1991-07-07' , '189654' , 'A05');
INSERT INTO EmpInfo VALUES(DEFAULT , '钱七' , '女' , '1991-05-23' , '189331' , 'A02');
INSERT INTO EmpInfo VALUES(DEFAULT , '孙八' , '男' , '1991-09-09' , '189122' , 'A04');
INSERT INTO EmpInfo VALUES(DEFAULT , '王五' , '男' , '1991-11-06' , '189789' , 'A05');
INSERT INTO EmpInfo VALUES(DEFAULT , '吴十' , '男' , '1991-09-28' , '189166' , 'A01');
INSERT INTO EmpInfo VALUES(DEFAULT , '杨九' , '男' , '1991-01-14' , '189177' , 'A01');
INSERT INTO EmpInfo VALUES(DEFAULT , '张三' , '男' , '1991-01-01' , '189123' , 'A01');
INSERT INTO EmpInfo VALUES(DEFAULT , '赵六' , '女' , '1991-10-02' , '189987' , 'A03');
INSERT INTO EmpInfo VALUES(DEFAULT , '周八' , '男' , '1991-04-21' , '189661' , 'A03');
INSERT INTO EmpInfo VALUES(DEFAULT , '朱三' , '男' , '1991-08-08' , '189551' , 'A05');

-- 查询
SELECT * FROM DeptInfo;
SELECT * FROM EmpInfo;

-- 添加一个新部门，生产部，部门编号A06
INSERT INTO DeptInfo VALUES('A06','生产部');

-- 添加一个新员工信息，该员工分配到销售部
INSERT INTO EmpInfo VALUES(DEFAULT , '田七' , '男' , '1991-07-24' , '189591' , 'A03');

-- 修改刚新添加的员工的性别和出生日期
UPDATE EmpInfo SET EmpSex='女',EmpBirth='1991-08-01' WHERE EmpName='田七';

-- 删除这个新员工的信息
DELETE FROM EmpInfo WHERE EmpName='田七';

-- 查询显示所有员工的编号，姓名，性别
SELECT ALL EmpID,EmpName,EmpSex FROM EmpInfo; 

三、简单常用查询语句

1）.设置各种字段的别名（as）
2）.All关键字（不写编辑器自动添加）
3）.limit关键字（MySQL不支持top 关键字，SQL Server支持top关键字）
4）.distinct关键字：过滤重复记录
5）.where关键字：条件
6）.between...and...
7) .in / not in
8) .逻辑运算（and not or）
9）.like关键字（模糊查询）
10）.order by（desc：降序 asc：升序）
11) .对查询结果进行计算（sum，avg，max，min）
12）.count统计数据
13）.内部链接查询（inner join on）
14）.外部链接查询（left join on，right join on）

-- 如果empDB数据库存在则删除掉
DROP DATABASE IF EXISTS empDB;

-- 创建数据库
CREATE DATABASE empDB;

-- 使用empDB库
USE empDB;

-- 如果departinfo表存在则删除掉
DROP TABLE IF EXISTS departinfo;  

-- 在empDB数据库中创建表
CREATE TABLE departinfo(
deptId VARCHAR(11) PRIMARY KEY, -- 部门的编号 字符串 主键
deptName VARCHAR(32) NOT NULL   -- 部门的名称 字符串 不为空
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

-- 如果empinfo表存在则删除掉
DROP TABLE  IF EXISTS empinfo;

-- 在empdb数据库中创建empinfo表
CREATE TABLE empinfo(
empid INT PRIMARY KEY AUTO_INCREMENT, -- 员工的编号 字符串 主键
empName VARCHAR(20) NOT NULL,  -- 员工的名字 字符串不为空
empSex VARCHAR(2) NOT NULL DEFAULT '男', -- 员工的性别 字符串 不为空 默认男
empBirthday DATE NOT NULL,-- 员工的生日 日期类型 不为空
empTel VARCHAR(20) NOT NULL UNIQUE, -- 员工的电话 字符串不为空 不重复
deptId VARCHAR(11) NOT NULL,
score DOUBLE,
CONSTRAINT FK_deptId FOREIGN KEY(deptId) REFERENCES departinfo(deptId)
)ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1001;

-- 添加初始化的测试数据
INSERT INTO departinfo VALUES('A01','开发部');
INSERT INTO departinfo VALUES('A02','测试部');
INSERT INTO departinfo VALUES('A03','人事部');
INSERT INTO departinfo VALUES('A04','开发部');
INSERT INTO departinfo VALUES('A05','测试部');
INSERT INTO departinfo VALUES('A06','人事部');

INSERT INTO empinfo VALUES(DEFAULT,'李丰',DEFAULT,'2001-1-1','1234567894','A01',45);
INSERT INTO empinfo VALUES(DEFAULT,'李佳伟','男','2001-1-1','1234567895','A02',85);
INSERT INTO empinfo VALUES(DEFAULT,'李江岚','女','2002-1-1','1234567896','A03',87);
INSERT INTO empinfo VALUES(DEFAULT,'李丰',DEFAULT,'2001-1-1','1234517894','A01',78.5);
INSERT INTO empinfo VALUES(DEFAULT,'李佳伟','男','2001-1-1','1234527895','A02',75.6);
INSERT INTO empinfo VALUES(DEFAULT,'李江岚','女','2002-1-1','1234467896','A03',95);
INSERT INTO empinfo VALUES(DEFAULT,'李丰',DEFAULT,'2001-1-1','1234545894','A01',70);
INSERT INTO empinfo VALUES(DEFAULT,'李佳伟','男','2001-1-1','1278567895','A02',79);
INSERT INTO empinfo VALUES(DEFAULT,'李江岚','女','2002-1-1','1234561196','A03',97);

-- 查询数据
SELECT * FROM departinfo;
SELECT * FROM empinfo;

-- limit关键字
SELECT empid AS 员工编号 ,empName AS 员工姓名 FROM empinfo LIMIT 0,2;

-- as关键字
SELECT empid AS 员工编号 ,empName AS 员工姓名 FROM empinfo;

-- 字段的计算 （字符串拼接）
SELECT empid+'-'+empName AS '员工编号-员工姓名' FROM empinfo;

-- all关键字
SELECT ALL empid AS 员工编号 ,empName AS 员工姓名 FROM empinfo;

-- distict关键字
SELECT DISTINCT deptName AS 部门名称 FROM departinfo;

-- where关键字
SELECT empName AS 员工姓名 FROM empinfo WHERE empid > 1005;

-- between... and...
SELECT empid AS 员工编号 ,empName AS 员工姓名 FROM empinfo WHERE empid BETWEEN 1003 AND 1006;

-- in / not in
SELECT empid AS 员工编号 ,empName AS 员工姓名 FROM empinfo WHERE empid IN(1001,1003,1008);

-- 逻辑运算（and or not）
SELECT empid AS 员工编号 ,empName AS 员工姓名 FROM empinfo WHERE empSex= AND 

-- like关键字（模糊查询）
SELECT * FROM empinfo WHERE empName LIKE('%江%');

-- order by排序 （desc 降序 asc 升序）---也可以对字符串类型的数字进行排序
SELECT * FROM empinfo ORDER BY empid DESC;
SELECT * FROM empinfo ORDER BY empTel ASC;

-- 对查询结果进行计算（sum avg min max）
SELECT SUM(score) AS 总成绩 FROM empinfo;
SELECT AVG(score) AS 平均分 FROM empinfo;
SELECT MAX(score) AS 最高分 FROM empinfo;
SELECT MIN(score) AS 最低分 FROM empinfo;

-- count统计行数
SELECT COUNT(*) AS 学生数 FROM empinfo;
SELECT COUNT(empid) FROM empinfo;

-- 内部链接查询
SELECT * FROM departinfo INNER JOIN empinfo ON departinfo.deptId=empinfo.deptId;
SELECT * FROM departinfo , empinfo WHERE departinfo.deptId=empinfo.deptId;

-- 外部链接查询
SELECT * FROM departinfo LEFT JOIN empinfo ON departinfo.deptId=empinfo.deptId;
SELECT * FROM departinfo RIGHT JOIN empinfo ON departinfo.deptId=empinfo.deptId;