数据库

笔记内容

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

2 .数据操纵语言DML

数据操纵语言DML主要有三种形式：
1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE

3. 数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
| | | | |
表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback

4. 数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等。

mysql常用命令：

数据库相关语句

1. 进入mysql中的命令
   mysql -u root -p，密码最好设为123456

2. 展现当前所有数据库：
   show databases；

3. 创建数据库：
   create database mydb1；(数据库名字，最好设为中文)sql语句不区分大小写，但是自己写的时候可以用大小写区分，关键词都使用大写
   create database if not exists mydb2;(if not exists 表示在没有的情况下创建，有则不会发生改变，也不会报错)

4. 创建数据库，规定编码表：
   create database mybd2 character set UTF8; (在mysql中UTF-8应该写为 UTF8)

5. 创建数据库，校队规则，Collation是在一个字符集中进行如何排序的规则
   create datebase mybd3 character set UTF8 collate UTF8_general_ci ==不区分大小写==
   create datebase mybd4 character set UTF8 collate UTF8_bin;==区分大小写==

6. 安 顺序 和 逆序查看表中数据：
   select from test1 order by name asc; ==升序==
   select from test1 order by nasme desc;==降序==

7. 查看数据库，显示数据库创建语句：
   show databases; 查看所有的数据库
   show create database db_name; 显示数据库创建语句

8. 删除数据库
   drop database mybd1;

9. 修改数据库的编码表
   alter database mydb1 character set utf8;

10. 备份，恢复数据库

    备份：在cmd中 mysqldump -u 用户名 -p 数据库名>文件名.sql

    恢复：方式一：在mysql中 source 文件名.sql

    ? 方式二：在cmd 中 mysql -u 用户名 p 数据库名 <文件名.sql

表的相关语句

1. 在操作表之前一定要先选择数据库
   use 数据库名；

2. 显示表：
   show tables; 显示全部的表
   desc 表名; 简单描述表结构
   describe 表名；简单描述表结构
   sho create table :查看生成表的 DDL 语句

3. 创建表：
   create table 表名( 字段名 字符类型，字段名 字符类型，)；
   和java 相反，数据类型写在名字后
   create table student(id varchar(20, age int);
   注意表的名字如果和关键字一样，要用`隔开（在1的旁边，不是单引号） createorder`(id int); 在之后的表操作中也一直要加上```
4. 修改表
   • 增加新列
     alter table 表名 add 字段名 字符类型(限定大小),add 字段名 字符类型(限定大小)
     alter table student add id int,add name varchar(100);
   • 修改类的字段类型
     alter table 表名 modify 字段名 字符类型（限定大小）;
     alter table student id varchar(100);
   • 删除某列
     alter table 表名 drop 字段；
     alter table student drop id;
   • 修改表名
     altet table 表名 to 新表名
     alter table stuff to user;
   • 修改表的字符集
     alter table 表名 character set 字符集；
     alter table student character set utf8;
   • 修改表某列的名称
     ==修改表的方式有两种==
     Change既可以修改列的名称，也可以修改列的字段类型
     Modify只可以修改列的字段类型。
     举例：
     alter table student change name username varchar(50);
     alter table student modify name varchar（50）;
   • 修改表的默认值
     举例：修改了studnet中的name的默认值为0
     alter table studnet modify name varchar(50) default 0;
   • 删除表
     drop table 表名；

?

表内数据的操作

1. 新增数据（Insert语句）
   ==字符串和日期型数据应包含在单引号中。==
   表名后没有带列名的需要把所有数据都填充，而且数据类型要和表结构一直，或者可以指定相应列名，单独赋值
   a. 表名后没有带列名的，把数据全部填充
   insert into 表名 values(数据);//==有s啊！！s！！==
   举例 ：在student表中新增一个学生数据，学生有 id 姓名，年纪，三个属性， 数据类型分别为 int varchar(100) int
   insert into student values(123,‘赵元‘，18);
   b. 指定了相应的类名，单独赋值,==未填充的数据来自default==
   insert into 表名(列明1，列名2） values(数据1，数据2);
   insert into student (id,name) values(123,‘赵元‘);
   ==插入空值 insert into table values(null),==没啥意思

   注意:插入单行的时候使用VALUES，在插入多行的时候使用VALUE 这样比较快一点

2. 修改数据（Update语句）
   • UPDATE语法可以用新值更新原有表行中的各列。
   • SET子句指示要修改哪些列和要给予哪些值。
   • WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。
     a. 未限定范围，指定列的指都被改变
     update 表名 set 列名=数据；
     update student set age=18;
     b. 用where限定范围
     update 表名 set 列名=数据 where 限定范围。（where后跟的是一个逻辑表达式，即表达式的结果是布尔类型。）
3. 删除数据（Delete语句）
   delete from table_name??? ?
   [WHERE where_definition]

• 如果不使用where子句，将删除表中所有数据。
• Delete语句不能删除某一列的值（可使用update）删除的单位是行(行)
• 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
• 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。
  a. 删除某一行的值 delete from student where id =1; 将student中id为1 的这一行数据都删掉了
  b. 删除一个表内所有数据 delete from student ;

==删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同 ==
==delete 和drop的区别==:
delete from 表名：删除一个表内的元素
drop table 表明：删除一个表

数据查询语言--简单查询（DQL）

DQL：Data Query Language
作用：查询表中的数据。
关键字：
SELECT

1. 测试计算
   虽然 SELECT 语句通常用于从表中检索数据，但我们也可以用它计算表达式和函数的值。如：
   没啥意义
   SELECT 32; -- 计算表达式32的值
   SELECT NOW(); -- 查看当前的时间
   SELECT TRIM(‘ ab cd ‘); -- 修剪字符串‘ ab cd ‘左右两边的空白
   SELECT CONCAT(‘ab‘,‘cd’); -- 拼接字符串‘ab‘和‘cd‘
2. 查询单列
   SELECT column_name FROM table_name;
   举例：查询表 students 中学生的姓名。select name from student;
3. 查询多列
   SELECT col_name1, col_name2, … , col_namen
   FROM table_name;
   举例：我们查询 t_students 中学生的姓名和各科成绩。select chinese，math from student;
4. 查询所有列
   select from table_name;
   举例：查询 t_students 表中的所有数据。select from student;

• 注意：除非确实需要表中的所有数据，否则最好不要使用 * 通配符。因为，查询不必要的数据会降低查询和应用程序的效率。
• 提示：当我们不知道列的名称时，可以使用 * 通配符获取它们的数据。

1. 使用 WHERE 子句过滤记录.
   • SELECT * | {column_names}
   • FROM table_name
   • WHERE ; //filter_condition 是一个逻辑表达式，即表达式的结果是布尔类型。、
2. 去重 ：DISTINCT 过滤相同的记录
   SELECT DISTINCT {column_names}
   FROM table_name;
   注意：DISTINCT 操作的基本单位是行（记录），只有当两行数据一模一样的情况下，才会删除另一行。
   如：查询 t_students 表中的不同班级数据。
   select distinct id from student where calss；
3. 限制结果：SELECT 语句返回所有匹配的记录，指向显示一定数量的行数的时候需要用到
   • 方式1： LIMIT offset, nums; -- LIMIT 3, 4; //偏移量是3，显示4行数据

• 方式2： LIMIT nums OFFSET offset; //显示num行数据，偏移量是 offset
  注意：offset是指偏移量，当我们从第1行查起，偏移量自然为0。此时，可以写成 LIMIT nums;

  **扩展：**我们可以使用 LIMIT 关键字实现分页查询。 LIMIT (page_num – 1) * page_size, page_size;

1. 对查询结果进行排序：我们可以使用 ORDER BY 子句对查询结果进行排序。
   ==多列排序的时候，在最前面的优先级最强（首先按照第一列的要求进行排序，第一列排序完之后再细微调整第二列，也就是优先级由高到低的过程。）==
   如： 根据语文成绩 顺序排列，数学成绩 逆序，英语成绩 顺序
   select *from student order by chinese asc, math desc,english asc;

2. 分组查询
   [GROUP BY {column_names}] [HAVING ]
   使用 HAVING 过滤分组
   举例：查询各班人数
   select count(id) as number from student
   举例： 查询人数大于2的班级

注意：group concat()

当使用group by a列语句之后，在单独检索除了a列以   外的其他字段都没有什么意义，这个时候相同分类的数据   已经合并了， 所以，如果想再取出全部的数据，则必须   使用group concat()函数

注意：在 MySQL 中，0 表示 false，非 0 表示 true，故：SELECT * FROM t_students WHERE 1; 也是合法的。

### SELECT语句的执行顺序
(5) SELECT column_name, ...
(1) FROM table_name, ...
(2) [WHERE ...]
(3) [GROUP BY ...]
(4) [HAVING ...]
(6) [ORDER BY ...];
注意：从上到下是写sql语句时正确的语法结构，（）括号里面的数字代表该条sql语句在服务器内部解析的先后顺序。

数据类型：

注意：这里以MySQL为例，不同的DBMS的都支持数值类型，字符串类型以及日期类型，但他们的实现可能不一样。

整数

浮点数类型和定点数类型

其中 M 称为精度，表示总共的位数; 
D 称为标度，表示小数的位数。
DECIMAL 类型不同于 FLOAT & DOUBLE，DECIMAL 实际是以字符串存放  的，它的存储空间并不固定，而是由精度 M 决定的

时间的数据类型

ATETIME 和 TIMESTAMP 虽然显示的格式是一样的，但是它们有很大的区别：
DATETIME 的系统默认值是 NULL, 而 TIMESTAMP 的系统默认值是 当前时间 NOW();
DATETIME 存储的时间与时区无关，而 TIMESTAMP 与时区有关。

字符串类型

ENUM 类型总有一个默认值，当ENUM 列声明为NULL，则默认值为NULL。如果 ENUM 列被声明为 NOT NULL，则其默认值为列表的第一个元素。

二进制类型

不怎么用
字符串类型存储的是非二进制字符串（字符字符串），
二进制类型存储的是二进制字符串（字节字符串）

常见运算符介绍：

基本和java 一样，与java 不同的有一下几个：

        IS NULL 是否为NULL
        <=> 安全的等于
        IN  是否在列表内
        LIKE    通配符匹配

IiKE 通配符规则：

==not like==
==not like==
‘%’ 匹配任何数目的字符，甚至包括零字符
‘_’ 只能匹配一个字符
例如： 查询student中姓名第一个字为‘赵’的学生
select *from tsudnet where name like ‘赵%’

别名

注意：在很多DBMS中，AS关键字是可选的，不过最好使用它，这被视为一条最佳实践。
提示：AS 关键字不仅能给列起别名，还可以给表起别名。在多表查询中，我们会这样使用它。
我们可以给 Chinese + math + english 起一个更简单直接一点的名字。
SELECT name, chinese + math + english AS total FROM t_students;

聚合函数

• COUNT()
  COUNT(*) 计算表中的总==行==数;

    Select count(*)|count(列名) from tablename
    [WHERE where_definition]
    COUNT(column_name) 计算指定列下的总行数，计算时将忽略值为NULL的行。

• SUM():返回指定==列==的所有值之和
  elect sum(列名)｛,sum(列名)…｝ from tablename
  [WHERE where_definition]
  计算时将忽略值为NULL的行;
• AVG():返回指定==列==的平均值
  计算时将忽略值为NULL的行;
  Select sum(列名)｛,sum(列名)…｝ from tablename
  [WHERE where_definition]?
• MAX():返回指定==列==的最大值
  计算时将忽略值为NULL的行;
  Select max(列名)　from tablename
  [WHERE where_definition]

• MIN():返回指定==列==的最小值
  计算时将忽略值为NULL的行;
  Select max(列名)　from tablename
  [WHERE where_definition]?

分组

使用 GROUP BY 关键字对数据进行分组。
[GROUP BY {column_names}] [HAVING]
使用 HAVING 过滤分组
注意：使用分组后，数据会根据所指定的字段分组，但是不能把每个组内的数据完全显示
这时需要使用==group_concat()==函数，使全部的数据显示出来。

例如：

聚合函数和group by分组一起使用的时候，聚合函数会对每个一个组进行单独计算
举例：查询每个班的学生人数
select count(id) ,class as count from student group by class;
查询每个班人数大于2 的班级和人数：
select count(id) as number,class as count from student group by class having number>2;

==Having 和where？？？==

• where语句在分组之前，执行完where语句再去对数据进行分组

• having语句在分组之后，执行完分组之后，再对分组数据进行一个过滤筛选。

  例如：仅选出各科成绩都在90之上的学生，之后，对学生进行分组

  

数据完整性

数据完整性是为了保证插入到数据库中的数据是正确的，它防止了用户可能的输入错误。

数据完整性主要分为以下三类：

• 实体完整性 （唯一性）
  规定表的一行（即每一条记录）在表中是唯一的实体。实体完整性通过表的主键来实现。
• 域完整性：
  指数据库表的列（即字段）必须符合某种特定的数据类型或约束。比如NOT NULL。
• 参照完整性：
  保证一个表的外键和另一个表的主键对应。

定义表的约束

定义主键约束

• 定义主键约束
  primary key:不允许为空，不允许重复
  (可以区分两条记录的唯一性)

新建主键：
    方式一：新建表的时候创建
            create table student (id int PRIMARY KEY);
            primary key就是设置主键的关键字
    方式二： 修改表创建主键
            alter table studnet MODIFY id int PRIMARY KEY;
            alter table studnet ADD id int PRIMARY KEY;
            MODIFY  字段 类型 PRIMARY KEY，设置为主键
            ADD 字段 类型 PRIMARY KEY 新增字段为主键

• 删除主键：
  alter table tablename drop primary key ;

• 定义主键自动增长
  auto_increment

     方式一：新建表的时候创建自增长
     create table student(id int PRIMARY KEY AUTO_INCREMENT);
  
     方式二: 修改表这设置主键自增长
     alter table student MODIFY id int PRIMARY KEY AUTO_INCREMENT;       

• 定义唯一约束
  unique
  
• 定义非空约束
  not null
  

• 定义外键约束
  constraint constraint_FK_name foreign key(ordersid) references orders(id),

注意：实体完整性的自动维护

实体完整性中的自动维护，当删除自动维护添加的主键的时候，之后再添加元素，id也从所删的主键之后添加，不会连续，自己添加主键后，自动维护添加的主键是从最大的id开始自增。

定义唯一性约束 Unique

用在 例如用户注册的时候选择手机号或者邮箱
唯一性约束，可以插入多个null数据，但是其他的数据只能是唯一的

• 定义唯一性约束
  alter table student ADD email varchar(20) UNIQUE;
  alter table student MODIFY id VARCHAR(20) UNIQUE;
• 删除唯一性约束：
  alter table student DROP INDEX email；

Not NULL

两种方式：

• 第一种在新建表的时候，添加not null约束。
  create table student(id int not null);
• 第二种在新建完表之后，手动添加。
  alter table student id int not null;

外键约束

比如有两张表，一张学生选课表，另外一张课程表，学生选择的课程必须是课程表中出现的课程，这个时候可以用外键来约束。如果不是课程表内的课程，则无法插入。

新建外键：

• 方式一：在创建表的时候添加外键
  create table student(id int primary key auto_increment
  name varchar(20),
  cid int,
  CONSTARAINT student_course_FK foreign key (cid) REFERENCES course(id));
  create table course(
  id int PRIMARY KEY AUTO_INCREMENT,
  cname varchar(20));
• 方式二:修改表添加外键
  alter table student add CONSTRAINT student_course_FK foreign key(cid) references course(id);
  删除外键：
  alter table student drop foreign key student_course_FK;

外键的优劣：
关于外键的优劣，有两种观点，一种认为外键可以起到很好的约束作用，值得使用
另外一种观点，认为外键虽然可以起到约束，但是同时它给数据库系统带来的整体开销远远大于获得的益处。

多表设计：

一对多：在多的一方添加外键约束，有效地减少数据冗余，也就是减少数据重复
多对多：生成第三个表，存储她们的关系
一对一：可以将另外一张表的字段直接写入表中，很不常用

多表查询

• 连续查询
• 子查询
• 联合查询

• 报表查询

  连续查询：

• 交叉连接（cross join）:不带on子句，返回连接表中所有数据行的笛卡儿积。
• 内连接（inner join）：返回连接表中符合连接条件及查询条件的数据行。

• 外连接：分为左外连接（left out join）、右外连接（right outer join）。与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。
  Select *from TABLE1 join_type TABLE2 [on (join_condition)]
  [where (query_condition)]

  交叉连接

  返回所有结果的笛卡尔积。交叉连接基本没有使用的场景，但是交叉连接的结果作为其他连接查询的基础。
  两种方式：（交叉连接查询table1 和table 2）
• 方式一：select * from table1,table2;
  

• 方式二：select * from table1 CROSS JOIN table2;

  内连接

  返回连接表中符合连接条件及查询条件的数据行。
• 显式内连接：使用inner join关键字，在on子句中设定连接条件
  SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id;
  相当于：不需要再使用as 设置别名，别名可以直接跟在后面
  SELECT * FROM customer as c INNER JOIN orders as o
  ON c.id=o.customer_id;

• 隐式内连接：不包含inner join关键字和on关键字，在where子句中设定连接条件
  SELECT * FROM customer c,orders o WHERE c.id=o.customer_id;

外连接查询：

左连接查询和右连接查询没有太大区别

左连接查询：

• 使用left outer join关键字，在on子句中设定连接条件
  SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id;
  ==不仅包含符合c.id=o.customer_id连接条件的数据行，还包含左表中的其他数据行==

• 带查询条件的左外连接查询，在where子句中设定查询条件
  SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id WHERE o.price>250;

  右连接查询：

• 使用right outer join关键字，在on子句中设定连接条件
  SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer_id;
  ==不仅包含符合c.id=o.customer_id连接条件的数据行，还包含orders右表中的其他数据行==

• 带查询条件的右外连接查询，在where子句中设定查询条件
  SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer_id WHERE o.price>250;

  子查询

• 子查询也叫嵌套查询，是指在where子句或from子句中又嵌入select查询语句（一般写在where字句）

• 练习：
  查询“郭靖”的所有订单信息
  SELECT * FROM orders WHERE customer_id=(SELECT id FROM customer WHERE name LIKE ‘%郭靖%‘);
  select * from customer c inner join orders o on c.id = o.customer_id and c.name like ‘%郭靖%‘;

联合查询 UNION

• 联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返并没有重复数据行的查询结果。联合查询使用union关键字
  SELECT * FROM orders WHERE price>100 UNION SELECT * FROM orders WHERE customer_id=1;

  报表查询

• 报表查询对数据行进行分组统计，其语法格式为：
  select … from … [where…] [ group by … [having… ]] [ order by … ]
  -其中group by 子句指定按照哪些字段分组，having子句设定分组查询条件。

java学习——MySQL的初尝试

原文：https://www.cnblogs.com/zhaoyuan72/p/11306214.html