数据库入门

1）数据定义语言，用于定义数据库对象：库、表、列。

# create(创建)   alter(修改)   drop(删除)
-------------------------------------------
# 查看创建的数据库
show create database 数据库名、
# 查看所有的数据库
show databases
# 查看表的信息
desc 表名
# 创建数据库
create database 数据库名 character set 字符集
create database 数据库名 charset 字符集
# 创建表并设置存储引擎
create table 表名(表中的字段) engine=innodb(存储引擎) charset uft8
eg:
    create table test(
    id int primary key auto_increment # 设置主键,自增
    sno varchar(10),
    primary key(sno),  #设置主键
    constraint 外键名 foreign key(要设置的字段sno) references 主表(主表字段) # 设置外键  constraint 外键名 不写系统会生成默认外键名 
    )engine= innodb auto_increment='1110' charset uft8

# 删除数据库
drop database 数据库名
# 删除表
drop table 表名
# 设置自增的开始
alter table 表名 auto_increment=xxx
# 修改数据表的引擎，字符集
alter table xxx engine=xxx charset xxx
# 修改数据库的的字符集
alter database charset xxx
# 修改表名
rename table 原表名 to 新表名
# 修改字段名（顺便也把字段类型也可以改了）、改变约束条件
alter table xx change 原字段名 新字段名  xx(类型)
# 修改字段的类型、添加默认值（也可以添加约束条件）
alter table 表名 modify 字段名 xxx(要修改的类型)
# 增加字段的约束条件/增加字段
alter table 表名 add xxx(约束条件)
alter table xxx add 新增字段 xxx(约束条件) after xxx(字段名)# 在哪个字段之后添加。不写默认在最后添加
eg alter table xxx add primary key(字段名)
    alter table xxx add unique(字段名)
    alter table xxx add constraint 外键名 foregin key(xx) references xxx(xx)
# 删除字段
alter table 表名 drop column 列名
# 删除约束
-- 非空约束以及自增约束 默认值约束：在定义时设置，或者通过MODIFY COLUMN 和 CHANGGE COLUMN设置及删除
-- 一张表只能有一个自增长列，并且该列必须定义了约束(可以是主键约束,也可以是唯一约束,也可以是外键约束,但是不可以是非空和检查约束)
 -- 语法：ALTER TABLE tab_name DROP PRIMARY KEY；/*主键是唯一的就这样就行了*/
-- 语法：ALTER TABLE tab_name DROP INDEX index_name /*删除
唯一性约束  查看所有名
SHOW INDEX FROM 表名*/
-- 语法：ALTER TABLE tab_name DROP FOREIGN KEY fk_name/*删除外键约束*/

2）数据操作语言：用来定义数据库数据

insert

# 单条插入
insert into 表名(字段名，xxx,xxx...) values(x,x,x..)
# 批量插入
insert into 表名(xx,xx,xx..) values(x,x,x..),(xxxx..),(xxx)

update

update 表名 set 字段=?
update 表名 set 字段=? where xxxx

delete

# 删除一条记录
delete from 表名  /* 删除所有记录*/
delete from 表名 where xxxx

3）数据控制语言: 用来定义访问权限和安全级别

4）数据查询语言：用来查询数据

基础查询

数据表定义：

# 学生表
CREATE TABLE stu (
    sid CHAR(6),
    sname       VARCHAR(50),
    age     INT,
    gender  VARCHAR(50)
);

INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

# 员工表
CREATE TABLE emp(
    empno       INT,
    ename       VARCHAR(50),
    job     VARCHAR(50),
    mgr     INT,
    hiredate    DATE,
    sal     DECIMAL(7,2),
    comm        DECIMAL(7,2),
    deptno      INT
) ;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
# 部门表
CREATE TABLE depte(
    deptno      INT,
    dname       VARCHAR(14),
    loc     VARCHAR(13)
);


INSERT INTO depte VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO depte VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO depte VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO depte VALUES(40, 'OPERATIONS', 'BOSTON');

#查询所有列
select *from 表名
# 查询指定列
select 列名，列名... from 表名
# 为查询到的列起别名
select 列名 as 别名 from 表名    # as 可以省略

条件查询

​ 定义： 在查询时给出where子句。在where子句中可以使用如下运算符和关键字

=   !=  <   >   <=  >=
between..and ...在a和b之间
in:在....之中
is null: 为空  is not null
and     or   not

select * form 表名 where xxxx(条件表达式)
select 列名，列名 from 表名 where xxxx
eg:查询学号为S_1001，S_1002，S_1003的记录
    select *from stu where sid in('S_1001','S_1002','S_1003')

模糊查询

关键字 like

通配符

参考文章：https://www.cnblogs.com/parable/p/11225717.html

% 表示任意0个或多个字符。可以匹配任意类型和长度的字符
比如 SELECT * FROM [user] WHERE u_name LIKE '%三%'
将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。
另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件
SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%'
若使用 SELECT * FROM [user] WHERE u_name LIKE '%三%猫%'
虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。

_ 表示任意单个字符。匹配单个任意字符，它常用啦限制表达式的字符长度语句
比如 SELECT * FROM [user] WHERE u_name LIKE '_三_'
只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的；


再比如 SELECT * FROM [user] WHERE u_name LIKE '三__';
只找出“三脚猫”这样name为三个字且第一个字是“三”的；
[]:表示括号内所列字符中的一个（类似正则表达式）
比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'
将找出“张三”、“李三”、“王三”（而不是“张李王三”）；
如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”
SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'
将找出“老1”、“老2”、……、“老9”；


[^]表示不再括号所列指捏的单个字符
比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]';
将排除“老1”到“老4”，寻找“老5”、“老6”、……

字段控制查询

去掉重复记录

select distinct 字段名 from 表名
# 去掉emp表的deptno字段的重复记录
SELECT DISTINCT deptno FROM emp;

查看雇员月薪与佣金之和

SELECT sal+IFNULL(comm,0) AS s FROM emp;

ifnull() isnull() nullif()三者的区别
ifnull(exp1,exp2)# 如果exp1得值为空就返回exp2的值
isnull(exp) # 如果exp的值为null就返回1否则返回0
nullif(exp1,exp2) # 如果exp1==exp2 就返回Null 否则返回exp1

给列名添加别名

select 列名 as 别名, 列名 as 别名 from 表  # as可以省略

排序order b y列 asc(默认升序)/ desc(降序)

# 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
SELECT * FROM emp ORDER BY sal DESC,empno ASC;

聚合函数

• count():统计指定列不为Null的记录

• max():计算指定列的最大值

• min():计算指定列的最小值

• sum():计算指定列的数值和，列类型不是数值类型，那么计算结果为0

• avg(): 计算指定列的平均值，如若列不是数值类型，计算结果为0

分组查询

当需要使用分组查询的时需要使用group by子句

select 
#   查询每个部门的部门编号和每个部门的工资和：
SELECT deptno, SUM(sal+IFNULL(comm,0))FROM emp GROUP BY deptno

# 查询每个部门的部门编号以及每个部门工资大于1500的人数：
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

having和where的区别

1 Having是在分组之后对数据进行过滤
Where是在分组之前对数据做过滤
2 having之后可以跟聚合函数
Where之后不可以跟,where是在之前写

#查询工资总和大于9000的部门编号以及工资和并按照工资和升序排列：
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000 ORDER BY SUM(sal);

limit

LIMIT用来限定查询结果的起始行，以及总行数。
一般使用在分页查询中
如果一页记录为10条，希望查看第3页记录应该怎么查呢？
    第一页记录起始行为0，一共查询10行；
    第二页记录起始行为10，一共查询10行；
    第三页记录起始行为20，一共查询10行；

查询代码的书写顺序和执行顺序

Select-from-where-group by-having -order by- limit

From-where-group by-having-select-order by-limit

5）数据的完整性

作用：保证用户输入的数据保存到数据库中是正确的

实体完整性

实体：即表中的一行代表一个实体

作用：表示每一行数据不重复

约束条件：

* 主键约束(primary key)
* 唯一约束(unique)
* 自动增长(auto_increment)

域完整性

作用：限制单元格的数据正确

约束条件：

* 数据类型
* 非空约束(not null)
* 默认值约束(default)

引用完整性（参照完整性）

​ 要有外键必须先有主键，主键和外键的类型必须一致

表与表之间的关系

# 一对一
# 一对多
# 多对多
多对多：例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。在把这两个外键作为主键

6）多表查询

多表查询有如下几种

• 合并结果集：union,union all

• 连接查询

  • 内连接 [inner] join on

1. 等值连接：在连接条件中使用等于号

   • 外连接 outer join on

     • 左外连接left [outer] join

     • 右外连接 right [outer] join

     • 全外连接(MySql不支持)full join

• 自然连接 natural join

• 子查询

合并结果集

# 合并结果集有两种方式
union:去除重复的记录
select 字段名,xxx from 表名 union select 字段名,xxx,.. from 表名

union all: 不去除重复的记录
select 字段名,xxx from 表名 union all select 字段名,xxx,.. from 表名

连接查询

连接查询就是求出多个表的乘积即笛卡尔积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。

select xxx from 表名1，表名2

可以使用主外键关系作为条件来去除无用信息

select xxx from exp1,exp2 where exp1.x=exp2.x

#查询员工表和部门表:得到员工编号,姓名,员工部门,岗位: 
SELECT e.empno,e.ename,e.deptno,e.job FROM depte d , emp e WHERE d.deptno=e.deptno
/*使用了别名*/

内连接返回两表的交集

上面的连接语句就是内连接，但他不是SQL标准中的查询方式

select 表1的列 from 表1 inner join 表2 on 表1.相同的列=表2 .相同的列
/* 可以去掉查询中有重复的列*/
select * from 表1 inner join 表2 using(相同的列);

# 内连接方式
SELECT  e.empno,e.ename,e.deptno,e.job FROM depte d INNER JOIN emp e on d.deptno=e.deptno

外连接：以某一边为准，查询符合条件的，不符合的显示为Null

左外连接：left outer join(outer可以省略) 先查左边的表，然后再查右边的表满足条件的部分，不满足条件的均为Null

SELECT * FROM depte d LEFT JOIN emp e ON e.deptno=d.deptno

右外连接：把右边表全部查询出来，再把左边满足查出来，不满足Null填充

总结

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

自然连接

大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式

两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

SELECT * FROM emp NATURAL JOIN depte; # 自然连接
SELECT * FROM emp NATURAL LEFT JOIN depte; # 左外
SELECT * FROM emp NATURAL RIGHT JOIN depte;# 右外

子查询：一个select语句中包含另一个完整的select语句。

子查询出现的位置：
where后，作为条件为被查询的一条件的一部分。
from后，作表

当子查询出现在where后作为条件时，还可以使用如下关键字：
any 只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件
all 内层子查询返回的结果需同时满足所有内层查询条件。

子查询结果集的形式：
单行单列（用于条件）
单行多列(用于条件) in
多行单列(用于条件)可以使用all或any，in关键字
多行多列(用于表) 派生表需要有别名

# 1查询工资高于jones的员工
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='JONES')/*子查询形式为单行单列*/
#2查询与SCOTT同一个部门的员工。
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT') /*子查询形式为单行单列*/
# 查询工作和工资与MARTIN（马丁）完全相同的员工信息
SELECT *FROM emp WHERE (job,sal) IN(SELECT job,sal FROM emp WHERE ename='MARTIN') /*子查询形式为单行多列*/

#3查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
方法一：
SELECT *FROM(SELECT *FROM emp AS e INNER JOIN depte d USING(deptno)
) AS a WHERE a.empno=7788 /*子查询形式为多行多列*/
方法二
SELECT *FROM emp AS e INNER JOIN depte d ON e.deptno=d.deptno AND empno=7788
#

自连接：自己连接自己，起别名

求7369员工编号、姓名、经理编号和经理姓名
分析:这些信息在一张表中,但是经理姓名一次查不出来
SELECT e1.empno,e1.ename,e1.mgr,e2.ename FROM emp e1,emp e2 WHERE e1.mgr=e2.empno AND e1.empno=7369

7)mysql中的函数

mysql时间日期相关函数.png mysql数学相关函数.png mysql字符串相关函数.png

未完待续