MySQL增删改查

DDL(Data Definition Language):数据定义语言，用来定义数据库对象：库、表、列等。
DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）。
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

• DDL操作表
  CREATE TABLE 语句用于创建新表。
  语法：
  CREATE TABLE [IF NOT EXISTS]表名（
  字段1 字段类型（长度）约束，
  字段2 字段类型（长度）约束，
  字段3 字段类型（长度）约束
  ）【charset=utf8】

示例：
SQL>CREATE TABLE IF NOT EXISTS student
(
id INT NOT NULL,
name VARCHAR(50),
age INT,
address VARCHAR(100)
)CHARSET=utf8;
或
SQL>CREATE TABLE IF NOT EXISTS ‘student’
(
id INT NOT NULL,
name VARCHAR(50),
age INT,
address VARCHAR(100)
)CAHRSET=utf8;
注意：数据库名、表名、字段名可以使用反勾号 `括住，也可以不括。如果SQL关键字一般要扩住。
常用数据类型：
int：浮点型（8个字节），近似值
double：浮点型（8个字节），近似值
例如double（5,2）表示最多5位，其中必须有2位小数，即最大值为999.99
decimal：精确数值数据，最大位数可以是65
例如decimal（5,2）能够存储具有五位数和两位小数的任何值，因此可以存储范围为-999.99至999.99
char：固定长度字符串类型；char（10) aaa 固定10个字符，不足补空格，长度0-255
（缺点）：浪费空间，优点：查询速度快
varchar:可变长度字符串；varchar(10) aaa 最多存储10个字符
（缺点）查询速度慢，优点：节省空间
text：大文本字符串类型；有字符编码，存储比较大的文本数据。
blob:Binary Large Object二进制大对象数据；可以存储图片、音频、视频
date:日期类型，格式为yyyy-MM-dd;
time:时间类型，格式为hh:mm:ss
timestamp:时间戳类型yyyy-MM-dd hh:mm:ss 会自动赋值
datetime：日期时间类型yyyy-MM-dd hh:mm:ss
常用约束：
主键约束：primary key(保证数据唯一性），不能重复，不能为null
唯一约束：unique[key],不能重复，可以为null
非空约束：not null
默认约束：foreign key
自动增长：auto_increment
DROP TABLE语句用于删除现有表
语法：DROP TABLE [IF EXISTS] table_name;

• DML操作（重要）
  DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。
  主要包括INSERT、DELETR、UPDATE
  小知识：
  在MySQL中，字符串类型和日期类型都要用单引号括起来。
  （1）插入操作：INSERT:
  语法：INSERT INTO表名（列明1，列明2…）VALUES(列值1，列值2…）；
  注意：列明与列值的类型、个数、顺序要一一对应。
  可以吧列名当做java中的形参，把列值当做实参。
  参数不要超过列定义的长度。
  如果插入空值，请使用null
  插入的日期和字符一样，都是用单引号括起来。

CREATE TABLE student
（
	id INT NOT NULL,
	name VARCHAR(50),
	age INT,
	address VARCHAR(100)
）;

一次添加一条数据

INSERT INTO student(id,name,age,address)values(3,'zhangsan',18,'北京市');
INSERT INTO student(id,name,age,address)values(4,'lisi',19,'深圳市');
INSERT INTO student(id,name,age,address)values(5,'wangwu',20,'广州市');

一次添加多条数据

INSERT INTO student(id,name,age,address)values(6,'zhang',18,'北京市')，
											  （7,wang,19,'海南'）,
											  (8,li,20,'上海');

(2)修改操作：UPDATE:
语法：UPDATE表名SET列名1=列值1，列名2=列名2…WHERE 列名=值
例，将ID 为‘1’的地址修改为“河北保定”

UPDATE student SET address='河北保定' WHERE id=1;

将姓名为‘曹操’的学生年龄改为50，地址修改为“河南郑州”。

UPDATE student SET age=50,address='河南郑州' WHERE name='曹操';

将所有学生的年龄加5岁。
UPDATE student SET age=age+5;
（3）删除操作：DELETE
语法：DELETE FROM 表名【WHERE 列名=值】
例，删除表中名称为“zhangsan”的记录。

DELETE FROM student WHRER name='zhangsan';

删除表中所有记录

DELETE FROM emp;

使用truncate删除表中记录（先把表删除，然后在创建空表）

TRUNCATE TABLE emp;

TELETE删除表中数据，表结构还在，删除后的数据使用日志可以找回。
TRUNCATE删除是把表直接DROP掉，然后再创建一个同样的新表。TRUNCATE删除的数据不能找回，执行速度比DELETE快。

• DQL数据查询（重点）
  
  示例操作：
  1>创建学生表并添加数据
  
• 简单查询
  查询所有列 *表示所有列

SELECT * FROM stu；

查询指定列

SELECT sid,sname,age FROM stu;

• 条件查询
  条件查询就是在查询时给出WHERE字句，在WHERE字句中可以使用如下运算符及关键字：
  比较运算符：
  =、！=、<>、<、<=、>、>=;
  BETWEEN…AND;
  IN(set);
  IS NULL;

关系运算符
AND;&&
OR; ||
NOT;!

算术运算符

+ - * 、/ %

（1）查询性别为女，并且年龄小于50的记录

SELECT * FROM stu WHERE gender='female' AND age<50;

（2）查询学号为s_1001，或者姓名为lisi的记录

SELECT * FROM stu WHERE sid = 's_1001' OR sname='lisi';

（3）查询学号为s_1001,s_1002,s_1003的记录

SELECT * FROM stu 
WHERE sid IN ('s_1001','s_1002','s_1003');
//等同于
SELECT *FROM stu
WHERE sid='s_1001' or sid='s_1002' or sid='s_1003');

（4）查询学号不是s_1001,s_1002,s_1003的记录

SELECT * FROM tab_student
WHERE sid NOT IN ('s_1001','s_1002','s_1003');

（5）查询年龄为null的记录

SELECT * FROM stu WHERE age IS NULL;

（6）查询年龄在20到40之间的学生记录

SELECT * FROM stu WHERE age>=20 and age<=40;
//或者
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

（7）查询性别为非男的学生记录

SELECT * FROM stu WHERE NOT gender=‘male’；

（8）查询性别不为null的学生记录

SELECT * FROM stu WHERE sname IS NOT NULL;

• 模糊查询
  模糊查询需要使用呢关键字like.
  
  （2）查询姓名由5个字符构成，并且第5个字符为“i”的学生记录

SELECT * FROM stu WHERE sname LIKE '_i%';

（3）查询姓名以“z”开头的学生记录

//其中“%”匹配0~n个任何字符
SELECT * FROM stu WHERE sname LIKE 'z%';

（4）查询姓名中第2个字符为“i”的学生记录

SELTCT * FROM stu WHERE sname LIKE '_i%';

（5）查询姓名中包含“a”字符的学生记录

SELECT * FROM stu WHERE sname LIKE '%a%';

• 字段控制查询
  （1）去除重复记录
  去重复记录（两行或两行以上记录中列的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表中的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用distinct:

SELECT DISTINCT sal FROM emp;
SELECT DISTINCT sal,comm FROM emp;

（2）查看雇员的月薪与佣金之和因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

SELECT *,sal+comm FROM emp;
//字符串的合并不能使用+，使用concat(ename,'__',job);
secect *,concat(ename,'__',job)from emp;

（3）给列名添加别名
为列名sal+IFNULL(comm,0)添加一个别名，为total:

SELECT *,sal+IFNULL(comm,0) total FROM emp;-

给列起别名时，是可以省略AS关键字的

SELECT *,sal+IFNULL(comm,0) total FROM emp;

• 排序
  （1）查询所有学生记录，按年龄升序排序

SELECT * FROM stu ORDER BY age ASC;

（2）查询所有学生记录，按年龄降序排序

SELECT * FROM stu ORDER BY age DESC;

（3）查询所有雇员，按月薪降序排序，如果月薪相同时，按偏好升序排序

SELECT * FROM emp ORDER BY sal DESC,empno ASC;

• 聚合函数
  聚合函数是用来做纵向运算的函数：
  count：统计指定列不为null的记录行数；
  max（）计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
  min（）计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
  sum（）计算指定列的数值和，如果指定列不是数值类型，那么计算结果为0；
  AVG（）计算指定列的平均值，如果指定列不是数值类型，那么计算结果为0；

• 分组查询
  当需要分组查询时需要使用 GROUP BY 字句，凡是和聚合函数同时出现的列名，则一定要写在 group by 之后。
  1、查询每个部门的部门编号和每个部门的工资和：

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

2、查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

• HAVING字句
  1、查询工资总和大于9000的部门编号以及工资和：

SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

• LIMIT限制
  LIMIT 用来限定查询结果的起始行，以及总行数。
  1、查询前5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0,5;

注意，起始行从0开始，即第一行开始！
2、查询10行记录，起始行从3开始

SELECT * FROM emp LIMIT 3,10;