MySQL基础增删查改

符号‘;’在MySQL中表示语句的结束

在终端上利用net start 启动MySQL服务

在终端上启动MySQL的命令

查看结构：desc降序排列

基本查询：select * FROM 表名

创建数据库

创建名为jwgl的数据库

CREATE DATABASE jwgl;

创建xsgl数据库，指定字符集为gbk：

CREATE TABASE xsgl DEFAULT CHARACTER SET gbk；

删除xsgl数据库

DROP DATABASE xsgl；

查看MySQL的存储引擎：

SHOW ENGLNES;

数据操作：

本次数据如下

先创建员工表

CREATE TABLE emp(

empno   INT PRIMARY KEY,

ename   VARCHAR(20) UNIQUE NOT NULL,

job     VARCHAR(20) NOT NULL,

mgr     INT ,

sal     DECIMAL(7,2),

comm    DECIMAL(7,2),

deptno  INT 

) ;

使用INSERT语句：

INSERT INTO emp VALUES(9902,'赵六','分析员',9566,4000,NULL,20);

红色部分与字段名一一对应（9902—empno,赵六—ename，分析员—job，9566—mgr,4000—sal,NULL—comm,20--deptno）

可以使用SELECT语句查询数据：

SELECT * FROM emp；

向部分字段插入数据：

INSERT INTO emp (empno,ename,job,sal,deptno) VALUES(9839,'刘一','董事长',6000,10);

查看字段的信息可以使用查看数据库的创建过程：

SHOW CREATE TABLE emp\G

可以通过INSERT语句同时添加多条数据：

INSERT INTO emp

VALUES

(9499,'孙七','销售',9698,2600,300,30),

(9521,'周八','销售',9698,2250,500,30),

(9654,'吴九','销售',9698,2250,1400,30),

(9982,'陈二','经理',9839,3450,NULL,10),

(9988,'王五','分析员',9566,4000,NULL,20),

(9844,'郑十','销售',9698,2500,0,30),

(9900,'萧十一','保洁',9698,1050,NULL,30);

使用UPDATE更新数据：

UPDATE emp SET sal=sal+200;

使用WHERE时可以添加条件：

UPDATE emp SET sal=sal+300 WHERE job='保洁';

使用DELETE删除数据：

DELETE FROM emp WHERE ename='孙七';

使用DELETE  删除员工表的所有数据;

DELETE FROM emp;

TRUNCATE语句也可有删除全部数据的效果效果：

TRUNCATE TABLE emp；

DELETE和TRUNCATE的区别

DELETE 语句是逐行删除记录，而TRUNCATE语句则是直接删除数据表，再重新创建一个一模一样的新表

.使用TRUNCATE语句删除表中的数据后，再次向表中添加数据时，自动增加字段的默认初始值重新由1开始，而使用DELETE语句删除表中所有数据后，再次向表中添加数据时，自动增加字段的值为删除时该字段的最大值加1。（TEUNCATE删完后可以从1开始，DELETE删完后从删前的最大值加一开始）

本次数据

CREATE TABLE emp(

empno   INT PRIMARY KEY,

ename   VARCHAR(20) UNIQUE NOT NULL,

job     VARCHAR(20) NOT NULL,

mgr     INT ,

sal     DECIMAL(7,2),

comm    DECIMAL(7,2),

deptno  INT 

) ;

INSERT INTO emp

VALUES

(9839,'刘一','董事长',NULL,6000,NULL,10),

(9982,'陈二','经理',9839,3450,NULL,10),

(9369,'张三','保洁',9902,900,NULL,20),

(9566,'李四','经理',9839,3995,NULL,20),

(9988,'王五','分析员',9566,4000,NULL,20),

(9902,'赵六','分析员',9566,4000,NULL,20),

(9499,'孙七','销售',9698,2600,300,30),

(9521,'周八','销售',9698,2250,500,30),

(9654,'吴九','销售',9698,2250,1400,30),

(9844,'郑十','销售',9698,2500,0,30),

(9900,'萧十一','保洁',9698,1050,NULL,30);

用于查询的SELECT语句：

使用通配符’*’代表全部

SELECT empno,ename,job,mgr,sal,comm,deptno FROM emp;

等价与

SELECT * from emp；

查询部分数据：

SELECT empno,ename FROM emp;

使用DISTINCT去除重复数据

SELECT DISTINCT deptno FROM emp;

使用‘=’限定查询范围：

SELECT * FROM emp WHERE ename='张三';

使用’<>’和’!=’（表示否定）

SELECT * FROM emp WHERE deptno<>30;#查询所属部门编号不是30的员工信息

‘<’查询小于

‘>=’查询大于

‘IN‘查询某个值是否在表内

SELECT * FROM emp WHERE deptno IN(10,20);#查询部门编号为10或20的员工信息

当数值为NULL时，不能使用运算符’=’,’<>’,’!=’

查询mgr为NULL的员工信息（IS）

SELECT * FROM emp WHERE mgr IS NULL;

LIKE运算符查询，通配符‘%‘可以代表任意长度的字符串，例如，有数据为’张三‘，’张六‘，’张七‘，若用查询中使用到LIKE ’张%‘，则会查询到刚刚的三个数据

SELECT * FROM emp WHERE ename LIKE '%一';#查询员工姓名中以’一’为结尾的员工信息

转义字符’/’,使用转义字符后，将会把‘/’后面的字符看成一个转义字符。

‘NOT’的运算符：表示否定

SELECT * FROM emp WHERE deptno NOT IN(10,30);#查询部门编号不是10和30的员工信息

‘AND’的运算符：两个都要满足

SELECT * FROM emp WHERE job='经理' AND deptno=20;#查询部门编号为20并且职位为经理的员工信息

‘AND’结合‘BETWEEN’

SELECT * FROM emp WHERE empno BETWEEN 9900 AND 9935;#查询员工编号9900到9935的员工信息

‘OR’的运算符：两个之间满足一个即可

SELECT * FROM emp WHERE ename='刘一' or ename='李四' AND deptno=30;

#查询员工姓名为刘一，或者员工姓名为李四并且部门编号为30的员工

MySQL里的聚合函数

COUNT(e)计数，SELECT COUNT(*) FROM emp;#查询有多少个员工信息,

SELECT COUNT(COMM) FROM emp;#查询员工表中奖金不为NULL的员工个数

SUM(E)求和 , SELECT SUM(COMM) FROM emp;#算出员工奖金的总和，NULL不计入

AVG(e)平均值，SELECT AVG(COMM) FROM emp;#算出员工的平均奖金

MAX(e)最大值，SELECT MAX(sal) FROM emp;#算出员工的最高工资

MIN(e)最小值，SELECT MIN(sal) FROM emp;#算出员工的最小工资

分组查询：使用GROUP BY,用于查询一个字段里有哪几种数据（将相同的数据整合起来）

SELECT deptno FROM emp GROUP BY deptno;#查询员工表的部门编号有哪几种

GROUP BY 和聚合函数一起使用

SELECT deptno,AVG(sal),SUM(sal) FROM emp GROUP BY deptno;

#统计各部门的总薪资和平均薪资

DROUP BY和HAVING一起使用，对结果进行条件过滤

SELECT deptno,AVG(sal),SUM(sal) FROM emp GROUP BY deptno HAVING AVG(sum)<3000;

#统计部门中平均工资小于3000的部门编号和平均工资

排序查询：ASC表示升序，DESC表示降序

使用ORDER BY进行查询时 ，默认使用ASC进行升序排序

SELECT * from emp WHERE deptno=30 ORDER BY sal;

#查询部门编号为30的员工信息并将工资进行升序排列

SELECT * from emp WHERE deptno=30 ORDER BY sal DESC;

#查询部门编号为30的员工信息并将工资进行降序排序

如果字段中的数据含有NULL，那么NULL将会被看作最小值

ORDER BY 可以对多个字段的值进行排序,并且不同的字段可以有不同的排序方式（DESC或ASC）

SELECT * from emp WHERE deptno=30 ORDER BY job ,empno DESC;

#查询员工表中部门编号为30，并且将job进行升序排序，empno进行降序排序

限量查询：LIMIT +OFFSET和记录数

OFFSET：表示偏移量，若偏移量为0，则从第一条记录开始，若偏移量为1，则从第二条记录开始

记录数：表示需要返回多少条查询记录

SELECT * FROM emp ORDER BY sal DESC LIMIT 5;#查询工资最高的前五名员工

SELECT * FROM emp ORDER BY sal DESC LIMIT 1,4;#查询工资前2~5名的员工

内置函数：

使用CONCAT()函数实现各个字段在同一列中显示，并且用下划线’_’隔开

SELECT CONCAT(ename,'_',job,'_',deptno) FROM emp WHERE deptno=30;#结果如图

部门编号为30的员工姓名、员工奖金及员工部门编号的信息；如果奖金为NULL，返回“无奖金”。查询时可以使用IF()函数对奖金进行判断 

SELECT ename,IF(IS NULL(comm),'无奖金',comm),deptno FROM emp WHERE deptno=30;

 红色为IF的条件，蓝色为IF结果

输出结果如下图

设置别名：对数据表名或字段名设置别名 ，这个别名可以代替原本的数据表名或字段名，方便下一吃=次使用

SELECT * FROM 数据表名 [AS] 别名;AS可省略

SELECT * FROM emp e WHERE e.deptno=30；#给emp表设置一个别名e，后续再次使用的时候可以快速使用，如e.deptno

SELECT ename AS '姓名', comm '奖金',deptno '部门编号' FROM emp WHERE deptno=30;

#将字段ename的名称设置别名“姓名”，字段comm的名称设置别名“奖金”，字段deptno的名称设置别名“部门编号”

多表查询：基于两张或两张以上的表进行查询

CROSS JOIN：两个表中的数据进行笛卡尔积，就是将两表中的数据进行交叉连接

以下是创建本次的数据

# 创建部门表

CREATE TABLE dept(

       deptno   INT PRIMARY KEY,

       dname   VARCHAR(20) UNIQUE

);

# 插入部门数据

INSERT INTO dept

VALUES

(10, '总裁办'),

(20, '研究院'),

(30, '销售部'),

(40, '运营部');

内连接查询：

SELECT 查询字段 FROM 数据表1 [INNER] JOIN 数据表2 ON 匹配条件;

SELECT <字段名> FROM <数据表名1> CROSS JOIN <数据表名2> ;

INNER JOIN 用于连接两张表，其中INNER可以省略，ON用于指定查询的匹配条件

SELECT ename,dname FROM emp e JOIN dept d ON e.deptno=d.deptno;

#查询已经分配了部门（部门号不为NULL）的员工的信息

自连接查询：

SELECT e1.* FROM emp e1 JOIN emp e2 ON e1.deptno=e2.deptno WHERE e2.ename='王五’;# 查询员工王五所在部门的所有员工信息

外连接查询：

格式：SELECT 所查字段 FROM 数据表1 LEFT|RIGHT [OUTER] JOIN 数据表2 ON 匹配条件

LEFT JOIN：左连接

SELECT d.dname,e.ename FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno;

# 这个左连接中，emp是左表，所以查询结果会显示左表的全部内容，若右表没有对应的内容，则会显示NULL

RIGHT JOIN:右连接

SELECT d.dname,e.ename FROM dept d RIGHT JOIN emp e ON e.deptno=d.deptno;

#这个右连接中，emp为右表，所以查询结果会显示右表的全部内容，若左表中没有对应的数据，则会显示NULL

复合条件连接查询：连接查询中加入过滤条件（ORDER BY）

SELECT e.*,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno ORDER BY e.sal DESC;

#使用了左连接并使用ORDER BY进行排序

关键字结合子查询：先进行关键字外的语句

IN关键字，界定一个范围

SELECT * FROM dept WHERE deptno;

#先查询工资大于2900员工所在的部门

SELECT * FROM dept WHERE deptno IN(10，20);

#接着再查询部门的信息

SELECT * FROM dept WHERE deptno IN(SELECT deptno FROM emp WHERE sal>2900);

#可以直接查询到工资大于2900员工所在的部门

EXISTS关键字：

SELECT * FROM dept  WHERE EXISTS SELECT * FROM emp WHERE emp.deptno=dept.deptno AND emp.sal>2900)

#先查询出部门的所有信息，接着到子查询部分，筛选出工资大于2900的员工信息

EXISTS关键字与IN关键字

EXISTS：适用与外表数据量比较小，内表数据量较大

IN：适用与外表数据量比较大，内表数据量比较小

ANY关键字：表示‘任意一个’，必须和操作符配合使用，例如，ANY和<结合起来使用，表示小于任意一个。值1>ANY(子查询)，判断比较值1是否大于子查询返回数据中的任意一个数据。

SELECT * FROM emp WHERE deptno=10 AND sal>ANY(SELECT sal FROM emp WHERE deptno=20);#查询部门编号为10的员工并且工资都大于部门编号为20的员工的最低工资

ALL关键字：与ANY相反，表示‘所有’，必须和操作符都配合使用，例如，‘值1>ALL(子查询)’，判断比较值是否大于子查询返回的数据中的所有数据
SELECT * FROM emp WHERE deptno=10 AND sal>ALL(SELECT sal FROM emp  WHERE deptno=20);#查询部门编号为10并且工资大于部门编号为20的员工的最低工资

除了‘>’外，还可以搭配其他比较运算符，如<,=,!=等

SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='王五') AND ename !='王五';#外查询为查询什么的工作职位并且姓名不是王五，子查询为王五的工作职位，合起来就是，查询与王五相同的工作职位的员工信息

外键约束：外键是数据表中的一个特殊字段，它引用另一张数据表中的一列或多列，被引用的列应该具有主键约束或唯一性约束。对于两个具有关联关系的数据表来说，相关联字段中主键所在的数据表就是主表，外键所在的数据表就是从表。

添加外键约束的格式：ALTER TABLE 从表名 ADD CONSTRAINT [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名);

ADD CONSTRAINT表示添加外键约束

FOREIGN KEY 表示外键约束

REFERENCES 指定创建的外键引用哪个表的主键

ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);#添加名为fk_deptno的外键，外键字段名为deptno，外键字段所在的表为dept

SHOW CREATE TABLE emp;显示外键创建的过程或语句

理解：创建外键的主要目的是为了保证数据的完整性和统一性，但是如果一旦主表的数据被删除或修改，则对应的从表的数据也会被修改，如果没有外键约束这种操作，将会有很多剩余的数据，导致数据库中存在很多无用的数据。MySQL可以在建立外键时添加ON DELETE或ON UPDATE子句，告诉数据库怎么避免无用数据的产生。

建立外键时避免垃圾数据的语法格式如下。

ALTER TABLE 从表名 ADD CONSTRAINT [外键名称] FOREIGN KEY(外键字段名) 

REFERENCES 外表表名(主键字段名);

[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

CASCADE：主表中删除或更新记录时，同时自动删除或更新从表中对应的记录

SET NULL：主表中删除或更新记录时，使用NULL值替换从表中对应的记录（不适用于已标记为NOT NULL的字段）

NO ACTION：拒绝主表删除或修改外键关联列

RESTRICT：拒绝主表删除或修改外键关联列（在不定义ON DELETE和ON UPDATE子句时，这是默认设置，也是最安全的设置）

操作关联表：（这部分等后面补充把）

删除关联表：但我们需要删除两个表之间的关联关系时，这时的外键约束就要删除了

删除外键约束的格式：ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

ALTER TABLE emp DROP FOREIGN KEY fk_deptno;#将员工表emp中的外键约束（fk_deptno）删除