数据库的DML与DQL语句

1: DML语句

　　​ insert into values

insert into 表 values(值1,值2,值n);
insert into 表(字段1,字段2,字段n) values(值1,值2,值n);

两种语法的区别：
    基本语法1的插入语句，表中有多少个字段就必须要插入多少个值。一个不能多，一个也不能少。若有默认值，不想传，可以写上null。
    基本语法2中，除非有必填字段必须要写入值外。如果有默认值的不想写可以忽略不写。mysql会自动补主默认值

　　

　　delete from

delete from 表 [where 条件];

TRUNCATE TABLE 表名;
清空表的数据，并且让自增的id从1开始自增
-- 删除一条纪录
-- 一般情况下,我们是使用主键作为标识;
-- 根据where后面的条件,精准的删除我们数据库的某一条纪录;
-- 当我们的主键为自增的情况下,删掉的纪录,主键也默认为自增还是以前的数字;
DELETE FROM test WHERE id = 3;
DELETE FROM test WHERE NAME = 'rose';

-- 清空数据表 ; 主键自增也会回归1;
TRUNCATE TABLE test;

 

　　​ update 表名 set 字段名=更新值

update 表名 set 字段1=值1,字段2=值2,字段n=值n [where 条件];

-- 如何修改我们的数据表里面的纪录
-- 修改编号为2的人 将它的名字修改成 xxxx

UPDATE test SET NAME='xiaomei'; -- 修改所有的;将name字段,全部改成xiaomei

-- 如果你只想修改一条语句,一定要where要精准
-- 如果你想改多条,条件也一定要记得注意;
-- sql符合条件的都都会修改掉;
UPDATE test SET NAME='jack',age=22 WHERE id = 2;
UPDATE test SET NAME='rose',age=22 WHERE NAME = 'tony'; -- 所有的name为tony改掉

 

2: DQL语句(数据库查询语句)

　　关系型数据库

--表与表之间,可以建立关系;称之为主外键;

主键：是唯一标识一条记录，不能有重复的，不允许为空，用来保证数据完整性
    1: int类型,自增
    2: varchar类型 , UUID
    3: 自定义(当前时间+时间戳+机器码+int自增序列) ; 适用于分布式系统

外键：是另一表的主键, 外键可以有重复的, 可以是空值，用来和其他表建立联系用的。所以说，如果谈到了外键，一定是至少涉及到两张表.

外键的使用需要满足下列的条件：

1. 两张表必须都是InnoDB表，并且它们没有临时表。

注：InnoDB是数据库的引擎。MySQL常见引擎有两种：InnoDB和MyISAM，后者不支持外键。

2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。

3. 建立外键关系的对应列必须建立了索引。

4. 假如显式的给出了CONSTRAINT symbol，那symbol在数据库中必须是唯一的。假如没有显式的给出，InnoDB会自动的创建。

　　

　　

　　　　数据库三范式(设计表的原则) NF:normal form

第一范式（1NF）
    1NF的定义为：符合1NF的关系中的每个属性都不可再分
    1NF是所有关系型数据库的最基本要求，你在关系型数据库管理系统（RDBMS），例如SQL Server，Oracle，MySQL中创建数据表的时候，如果数据表的设计不符合这个最基本的要求，那么操作一定是不能成功的。
    
    学号 班级 姓名 系名 系主任 课程 语文 高数 英语 

    只满足1NF会有问题:数据冗余过大，插入异常，删除异常，修改异常;
    冗余: 相同数据重复出现
    插入问题: 3月份新开一个系,但是没有学生和班级; 如何插入数据?
    删除问题: 某个班级不存在了,学生和系信息怎么办?
    修改问题: 如果李四转到英语系,涉及到多条数据更改
----------------------------------------------------------

第二范式（2NF）
    2NF的定义为: 主键依赖,该表里面的其他字段,必须和主键相关;
    即一张表格只描述一个对象（主键），其它列名（副键）与对象之间相互完全依赖。
    
    生产一批香蕉,需要给香蕉进行记录,以便追踪去了哪里;首先给每把香蕉进行编号,然后描述他的颜色,成熟度,香味等;

第三范式(3NF)
    3NF的定义为: 主键以外的字段,不能有相互依赖的关系;

    比如，你想要对香蕉进行【编号】、【颜色】、【香味】、【香味得分】、【香味评级】这种列名，那么你要另开一张专门描述香味的表格。
    
    比如，你想要做【学号】、【姓名】、【性别】、【班级】、【班主任】这种列名，由于【班主任】受【班级】影响，那么你要另开一张专门描述班级的表格。

 

 

案例：

SELECT * FROM userinfo;
#删除一行
DELETE FROM userinfo WHERE id=7;
#修改一行
UPDATE userinfo SET username="dabu" WHERE id=1;
UPDATE userinfo SET username="da", userhobby="打人" WHERE id=1;

  USE nz201;
  SELECT * FROM stu;
  -- 普通的查询方式
-- select 字段 from 表名
SELECT * FROM stu; -- 查询所有   * : 通配符,统配表里面所有的字段
SELECT sid,sname FROM stu; -- 指定查询某些字段
 
-- 给字段取别名
-- 并不会改变影响原有的字段名,只对当前查询生效
SELECT sid AS '编号',sname AS '姓名',age AS '年龄' FROM stu; 
 
SELECT sid '编号',sname '姓名' FROM stu;
 
SELECT DISTINCT gender FROM stu; -- 对结果去重
 
-- --------------------------------------------------------
 
-- 条件查询;  查询语句后面,加上 where 带上条件;
-- select 字段 from 表名  where 条件
-- 查询年龄为35岁的人的信息
SELECT sid,sname,age,gender FROM stu WHERE age = 35;
 
SELECT * FROM stu WHERE age = 35;
 
-- 查询年龄大于35岁的人的信息  大于 等于 小于 不等于 大于等于 小于等于
SELECT sid,sname,age,gender FROM stu WHERE age > 35;
SELECT sid,sname,age,gender FROM stu WHERE age < 35;
SELECT sid,sname,age,gender FROM stu WHERE age != 35; -- <>
SELECT sid,sname,age,gender FROM stu WHERE age >= 35;
SELECT sid,sname,age,gender FROM stu WHERE age <= 35;
 
-- between A and B;in(值1,值2,值3);
SELECT sid,sname,age,gender FROM stu WHERE age BETWEEN 20 AND 40;
 
-- 查询年龄在 in (集合里面)  where age = 35 or age = 15 or age = 65 or age = 70;
SELECT sid,sname,age,gender FROM stu WHERE age IN (35,15,65,70);
 
-- and 两个条件都要满足;   or   满足其中一个    not 非
SELECT sid,sname,age,gender FROM stu WHERE age >= 20 AND age <= 40;
 
SELECT sid,sname,age,gender FROM stu WHERE age = 35 OR age = 15 OR age = 65 OR age = 70;
 
-- 查询年龄段 性别过滤
SELECT sid,sname,age,gender FROM stu WHERE age BETWEEN 20 AND 40  AND gender='male';
 
-- 查询空的值 查询年龄是null
SELECT sid,sname,age,gender FROM stu WHERE age IS NULL;
 
-- 不为空的值;  建议大家不要用字符串去 判断大小之类的
SELECT sid,sname,age,gender FROM stu WHERE age IS NOT NULL AND sid > 's_1004';
 
-- 找一个年龄大于35, 可以允许他先加5
SELECT sid,sname,age,gender FROM stu WHERE (age+5) > 40;
SELECT sid,sname,age,gender FROM stu WHERE (age-5) > 40;
 
-- 如果这个字段值,是null; 就给一个默认值
SELECT sid,sname,IFNULL(age,88),gender FROM stu
 
-- --------------------------------------------------------
-- 模糊查询;  查询一些不能确定的结果
 
-- 查询名字带 'l' 的学生;
-- % 代表任意字符(0-n个)
SELECT * FROM stu WHERE sname LIKE '%l%';
 
-- 查询第二个字母是i的学生;
-- _ 代表一个字符
SELECT * FROM stu WHERE sname LIKE '_i%';
 
-- 查询第三个字母是u的学生;
SELECT * FROM stu WHERE sname LIKE '__u%';
 
-- 查询以xx字符开头
SELECT * FROM stu WHERE sname LIKE 'x%';
 
-- 查5个字母的学生
SELECT * FROM stu WHERE sname LIKE '_____';
 
-- --------------------------------------------------------
-- 排序 order by  和 where 可以组合使用 ;只对查询结果进行排序展示
 
SELECT * FROM stu;
 
-- 根据年龄来升序展示结果; 根据年龄来降序 展示结果
SELECT * FROM stu ORDER BY age ASC; -- 升序
SELECT * FROM stu ORDER BY age DESC; -- 降序
 
SELECT * FROM stu ORDER BY sid ASC;
SELECT * FROM stu ORDER BY sid DESC;
 
-- --------------------------------------------------------
-- 聚合函数
-- count() 总数 ; 如果是统计一个字段,这个字段为null的将不会统计
-- max 最大值 min 最小值 sum 总和 avg 平均值
 
-- 查询我们这个表总共有多少条件纪录
 
SELECT COUNT(*) FROM stu; -- 不建议大家使用
SELECT COUNT(age) FROM stu; -- 10; 为null的不会统计
 
SELECT MAX(age) FROM stu; -- 找到年龄最大的纪录
SELECT MIN(age) FROM stu; -- 最小
SELECT AVG(age) FROM stu; -- 平均
SELECT SUM(age) FROM stu; -- 和
-- --------------------------------------------------------
 
-- 分组查询;  能对字段进行分组查询 ; group by
 
-- 查询每个部门 , 工资之和;  
 SELECT * FROM emp;
SELECT deptno FROM emp GROUP BY deptno; -- 根据部门来分组
 
SELECT SUM(sal) FROM emp; -- 整个公司的工资之和;
 
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
 
-- 查询学生表; 查询每个班级有多少个学生;  
-- 查询每个班级(学生表里面的班级id做分组)的数学平均成绩
-- --------------------------------------------------------
 
-- 分页查询
 
SELECT * FROM stu;
 
-- 只想查看前三条;
 
SELECT * FROM stu LIMIT 0,3; -- limit   纪录起始位置 , 查询几条纪录
 
SELECT * FROM stu LIMIT 5; -- 默认从0 开始 查询 x条纪录
 
SELECT * FROM stu WHERE age > 30 LIMIT 2;