数据库、SQL单表多表

目录

20.数据库、SQL单表多表

●数据存储方式比较

●Mysql数据库列表选项：

●Mysql数据库语句

●字段属性设置: 

●多表查询：

●关联查询：inner join

●select 查询模型

●模糊查找：like 

---

20.数据库、SQL单表多表

●数据存储方式比较

●数据在内存：

优点：读写速度快

缺点：程序结束后数据丢失

●保存到文件

优点：数据可以永久保存

缺点：

1、频繁的IO操作，效率不高

2、数据的管理非常不方便，需要把所有的数据整体都读取出来才能操作

●数据库：

1、数据永久保存

2、数据管理非常方便

●Mysql数据库列表选项：

① PRIMARY KEY： 主键，不能重复，唯一确定一条记录 （unique+not null）

② AUTO_INCREMENT： 自动增长

③ varchar(10) 和 char(10)的区别：

相同点：都可以最大放10个字符

不同点：char(10)不管输入的是多少都会占10个字符，例如输入名字“张三”只有两个字符，但是使用char(10)在数据库里面还是占10个字符的空间。使用varchar(10)最大支持是10个字符，但是实际长度就是输入字符长度，例如输入名字“张三”只有两个字符，那么在varchar(10)里面就只占两个字符。

●Mysql数据库语句

-- 列出所有的数据库

SHOW DATABASES;

  

-- 创建数据库

CREATE  DATABASE  java1812  DEFAULT  CHARACTER  SET  utf8;

  

-- 删除数据库

DROP  DATABASE  java1812;

  

-- ----------------------------------

-- 数据库表的操作

-- 切换数据库

USE java1812;

-- 创建表

CREATE  TABLE  student(

     id  INT ,

     NAME  CHAR (10),

     age  INT ,

     gender  CHAR (1)

);

  

-- 查看所有表

SHOW TABLES;

-- 查看表的结构

DESC  student;  -- description

-- 删除表

DROP  TABLE  student;

  

-- 更改表的结构

-- 添加字段

ALTER  TABLE  student  ADD  COLUMN  address  CHAR (10);

-- 删除字段

ALTER  TABLE  student  DROP  COLUMN  address;

-- 修改表的字段

ALTER  TABLE  student CHANGE address addr  CHAR (20);

-- 修改表的名字

ALTER  TABLE  student RENAME  TO  stu;

  

-- 创建表

CREATE  TABLE  student(

     id  INT  PRIMARY  KEY  AUTO_INCREMENT,

     NAME  VARCHAR (10),

     age  INT ,

     gender  VARCHAR (1)

);

-- * 代表查询所有的列

SELECT  *  FROM  student;

  

-- 插入数据

-- Duplicate entry '1' for key 'PRIMARY'

INSERT  INTO  student(id, NAME ,age,gender)  VALUES (1, 'wangwu' ,23, '男' );

INSERT  INTO  student(id, NAME ,age,gender)  VALUES (3, '赵六' ,23, '男' );

INSERT  INTO  student  VALUES (4, '赵六22' ,33, '男' );

-- 插入部分字段值(必须把前面的字段名都写上)

INSERT  INTO  student( NAME ,age,gender)  VALUES ( '小张11' ,23, '男' );

-- 一次插入多条数据

INSERT  INTO  student( NAME ,age,gender)  VALUES ( '小张77' ,23, '男' ),( '小王' ,22, '男' );

  

-- 修改数据

UPDATE  student  SET  age=age+1;

UPDATE  student  SET  age=age+1  WHERE  id=7;

UPDATE student SET `name`='赵六',age=25,gender='女' WHERE id=12;

  

-- 删除数据

DELETE  FROM  student;  -- 删除表中所有数据（很少使用，是非常危险）

DELETE  FROM  student  WHERE  age=24;  -- 所有age是24的数据都被删除了，可能有多条数据都是age=24

DELETE  FROM  student  WHERE  id=12;  -- 因为id是主键是唯一的，所以根据id删除只能删除唯一的一条数据

-- TRUNCATE删除表里面所有数据，自 增的id会重新初始化为初始值1

TRUNCATE  TABLE  student;

  

-- 查询数据

-- 显示所有列(字段)数据

SELECT  *  FROM  student;  -- 学习时候可以写*，但是在企业开发中需要什么字段就写什么字段

SELECT  id, NAME ,age,gender  FROM  student;

-- 查询指定列

SELECT  NAME ,age  FROM  student;

-- 查询时候添加常量列，通过as可以起别名

-- as 可以给某一列起别名，这个as可以省略

SELECT  id, NAME ,age  AS  '年龄' , 'java1812'  AS  '班级'  FROM  student;

-- 查询时候和并列，字段名可以当成java里面的变量来运算

SELECT  id, NAME ,(php+java)  AS  '总成绩'  FROM  student;

-- 查询时候去掉重复的记录

-- 查询这些学生来自于哪几个城市

SELECT  DISTINCT  address  FROM  student;

  

-- 条件查询 where

SELECT  *  FROM  student  WHERE  NAME = '小王' ;

  

-- 逻辑条件: and（同时成立） or(只要有一个成立)

SELECT  *  FROM  student  WHERE  NAME = '小王'  AND  address= '青岛' ;

SELECT  *  FROM  student  WHERE  NAME = '小王'  OR  address= '北京' ;

  

-- 比较运算: >  <  >=  <=  !=

SELECT  *  FROM  student  WHERE  java>=70  AND  java<=80;

-- between and (等价于>= and <=)

SELECT  *  FROM  student  WHERE  java  BETWEEN  70  AND  80;

-- 查询地址不是青岛的学生信息

SELECT  *  FROM  student  WHERE  address !=  '青岛' ;

  

-- 聚合查询

-- 聚合查询函数：sum(),avg(),max(),min(),count()

-- 统计学生php的总成绩（sum求和）

SELECT  SUM (php)  AS  'php总成绩'  FROM  student;

-- 查询该店每个栏目下挤压的货款

SELECT  cat_id, SUM (goods_number*shop_price) 

FROM  goods  GROUP  BY  cat_id ;

-- 查询该店每个栏目下挤压的货款 > 20000

SELECT  cat_id, SUM (goods_number*shop_price)  AS  total_price

FROM  goods  GROUP  BY  cat_id  HAVING  total_price>20000;

-- 统计学生php的平均值

SELECT  AVG (php)  AS  'php平均值'  FROM  student;

-- 统计学生php的最大值

SELECT  MAX (php)  AS  'php最大值'  FROM  student;

-- 统计学生表里面一共有多少学生

SELECT  COUNT (*)  AS  '总人数'  FROM  student;

SELECT  COUNT (id)  AS  '总人数'  FROM  student;

SELECT  COUNT (address)  AS  '总人数'  FROM  student;

-- 注意：count()函数统计的是指定列不包含NULL的数据个数

  

  

-- 查询排序

-- 语法：order by 字段  asc/desc  默认是asc升序，可以不写

SELECT  *  FROM  student  ORDER  BY  php;

SELECT  *  FROM  student  ORDER  BY  php  ASC ;

SELECT  *  FROM  student  ORDER  BY  php  DESC ;

-- 多个条件排序

-- 需求：先按照php降序，java升序(整体是按照php降序，如果php相同的数据再按照java标准排序)

SELECT  *  FROM  student  ORDER  BY  php  DESC , java  ASC ;

--LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

-- limit offset,rowcount

-- limit 偏移到哪个位置,往下数几个

SELECT  goods_id,cat_id,goods_name,shop_price

FROM  goods

ORDER  BY  shop_price  DESC  LIMIT 0,3;  -- limit 3

-- 取出点击量第三名到第五名的商品

SELECT  goods_id,cat_id,goods_name,click_count

FROM  goods

ORDER  BY  click_count  DESC  LIMIT 2,3;

-- 分组查询(group by)

-- 需求：查询男女分别有多少人

-- 先按照gender（因为gender里面只有两个值男个女），把所有的数据分成男和女两组，
-- 分组之后，select后面的查询都是基于group by分组之后在每个组里面查询

SELECT  gender, COUNT (id)  FROM  student  GROUP  BY  gender;

-- select后面的查询都是基于group by之后的

SELECT  address, COUNT (id)  FROM  student  GROUP  BY  address;

-- 查询的结果虽然不是一个真实的表，但是可以当成一张表来使用
-- 分组之后的筛选
-- 人数大于3人的组

SELECT gender AS '性别', COUNT(*) AS '人数'
FROM student GROUP BY gender HAVING COUNT(*)>3;

  

-- 分组查询后筛选

-- 需求：address大于1

-- group by之后的条件查询使用having

SELECT  address  AS  '地址' , COUNT (id)  AS  '人数'  FROM  student  GROUP  BY  address  HAVING  COUNT (id)>1;

  

SELECT  *  FROM  student;

●字段属性设置: 

1、not null： 不为空，表示该字段不能放“null”这个值。不写，则默认是可以为空

2、auto_increment: 设定int类型字段的值可以“自增长”，即其值无需“写入”，而会自动获得并增加

此属性必须随同 primary key 或 unique key 一起使用。 primary key = unique key + not null

3、[primary] key： 设定为主键。是唯一键“加强”：不能重复并且不能使用null，并且可以作为确定任意一行数据的“关键值”，最常见的类似：where id= 8; 或 where user_name = ‘zhangsan’;

通常，每个表都应该有个主键，而且大多数表，喜欢使用一个id并自增长类型作为主键。

但：一个表只能设定一个主键。

4、 unique [key] : 设定为唯一键：表示该字段的所有行的值不可以重复（唯一性）。

Duplicate entry 'zhangsan' for key 'name'

5、default ‘默认值’： 设定一个字段在没有插入数据的时候自动使用的值。

6、comment ‘字段注释’

CREATE  TABLE  teacher(

     id  INT  PRIMARY  KEY  AUTO_INCREMENT,

     NAME  CHAR (10)  NOT  NULL ,

     age  INT  COMMENT  '年龄',

     address  CHAR (10)  DEFAULT  '中国' ,  -- 插入数据时候如果不赋值，默认值是"中国"

     UNIQUE  KEY ( NAME ) -- 唯一键，代表这个字段不能重复

);

-- Duplicate entry 'zhangsan' for key 'name'

INSERT  INTO  teacher( NAME )  VALUES ( 'zhangsan' );

●多表查询：

学生表、班级表、课程表、班级课程表

---

-- 多对多

-- 班级表

CREATE  TABLE  banji(

     id  INT  PRIMARY  KEY  AUTO_INCREMENT,

     ` name `  VARCHAR (10)  NOT  NULL

);

INSERT  INTO  banji(` name `)  VALUES ( 'java1807' ),( 'java1812' );

SELECT  *  FROM  banji;

-- 学生表

CREATE  TABLE  student(

     id  INT  PRIMARY  KEY  AUTO_INCREMENT,

     ` name `  VARCHAR (10)  NOT  NULL ,

     age  INT ,

     gender  CHAR (1),

     banji_id  INT ,

     FOREIGN  KEY (banji_id)  REFERENCES  banji(id)

);

INSERT  INTO  student(` name `,age,gender,banji_id) 

VALUES ( '张三' ,20, '男' ,1),( '李四' ,21, '男' ,2),( '王五' ,20, '女' ,1);

-- Cannot add or update a child row: a foreign key constraint fails (`java1812`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`banji_id`) REFERENCES `banji` (`id`))

INSERT  INTO  student(` name `,age,gender,banji_id) 

VALUES ( '张三' ,20, '男' ,3);

SELECT  *  FROM  student;

-- 课程表

CREATE  TABLE  course(

     id  INT  PRIMARY  KEY  AUTO_INCREMENT,

     ` name `  VARCHAR (10)  NOT  NULL ,

     credit  INT  COMMENT  '学分'

);

INSERT  INTO  course(` name `,credit)  VALUES ( 'Java' ,5),( 'UI' ,4),( 'H5' ,4);

SELECT  *  FROM  course;

-- 班级课程表

CREATE  TABLE  banji_course(

     -- id int PRIMARY KEY AUTO_INCREMENT,

     banji_id  INT ,

     course_id  INT ,

     PRIMARY  KEY (banji_id,course_id),  -- 联合主键

     FOREIGN  KEY (banji_id)  REFERENCES  banji(id),  -- banji_id既是联合主键又是外键

     FOREIGN  KEY (course_id)  REFERENCES  course(id)  -- course_id既是联合主键又是外键

);

INSERT  INTO  banji_course(banji_id,course_id)  VALUES (1,1),(1,3),(2,1),(2,2),(2,3);

SELECT  *  FROM  banji_course;

---

-- 子查询：嵌套查询，一个查询语句是另一个查询语句的条件

-- 查询班级是java1812班所有学生信息

SELECT  *  FROM  student  WHERE  banji_id=2;

SELECT  id  FROM  banji  WHERE  ` name `= 'java1812' ;

SELECT  *  FROM  student  WHERE  banji_id=( SELECT  id  FROM  banji  WHERE  ` name `= 'java1812' );

-- 班级是java1807班或者java1812班所有学生信息

SELECT  *  FROM  student  WHERE  banji_id=1  OR  banji_id=2;

SELECT  *  FROM  student  WHERE  banji_id  IN (1,2);

SELECT  id  FROM  banji  WHERE  ` name `= 'java1807'  OR  ` name `= 'java1812' ;  -- 1,2

SELECT  *  FROM  student  WHERE  banji_id  IN ( SELECT  id  FROM  banji  WHERE  ` name `= 'java1807'  OR  ` name `= 'java1812' );

-- "="：要求子查询只有一个结果。 "in"：子查询可以有多个结果

●关联查询：inner join

关联查询：inner join

-- 列出所有学生学习的课程名称

-- 学生姓名   班级名称   课程名称    学分

---

-- inner join on 只有左右两个表有关联的才查询出来

-- left join on 左表中都显示出来，右表没有显示空

-- right join on 右表都显示，左表没有显示空

  SELECT  * 

FROM  student  as  s  INNER  JOIN  banji  as  b

on  s.banji_id=b.id;

SELECT  * 

FROM  student  as  s  LEFT  JOIN  banji  as  b

on  s.banji_id=b.id;

SELECT  * 

FROM student  as s  RIGHT  JOIN banji  as b

on  s.banji_id=b.id;

-- 没有分配课程也显示出来。

-- 班级名称   课程名称   学分

SELECT b.` name`  AS  '班级名称',c.` name`  as  '课程名称',c.credit  as  '学分'

FROM banji  AS b  LEFT  JOIN banji_course  AS bc

ON b.id=bc.banji_id

LEFT  JOIN course  as c

ON  bc.course_id=c.id;

总结：多表查询主要是账务下面两点

1、整个查询涉及到几张表，涉及到几张表就连接这几张表。

2、如果涉及到这几张表的关系搞不清楚，画一下ER图，弄清楚表和表之间的关系（就是根据外键建立的关系）

-- 统计每个班有多少学生

-- 学生数量

SELECT  COUNT (id)  as  '学生数量' 

FROM  student  GROUP  BY  banji_id;

-- 班级名称    数量

SELECT  * 

FROM  student  as  s 

INNER  JOIN  banji  as  b

ON  s.banji_id=b.id;

把inner join之后查询的结果当成一张表来使用， 在这个结果集里面根据班级id统计每个班级下面学生数量。

●select 查询模型

数据库中以表为组织单位存储数据。

表类似我们的Java类，每个字段对应类里面的属性。

那么用我们熟悉的java程序来与关系型数据对比，就会发现以下对应关系。

类--------------------表

表中属性-------------表中字段（列）

对象------------------记录（行）

字段（列）是变量（类中属性时变量）

变量是可以计算（操作）

where是表达式，值为真或者假（true或者false）

SELECT  b.` name `  AS  '班级名称' , COUNT (s.id)  as  '学生数量' 

FROM student  as s 

INNER  JOIN banji  as b

ON s.banji_id=b.id

GROUP  BY  s.banji_id;

-- 在上面基础上筛选出班级人数>1班级的名称和人数

SELECT b.` name`  AS  '班级名称', COUNT(s.id)  as  '学生数量' 

FROM student  as s 

INNER  JOIN banji  as b

ON s.banji_id=b.id

GROUP  BY s.banji_id

HAVING  COUNT (s.id)>1;

●模糊查找：like 

语法形式：字段 like '要查找字符'

说明：

1、like模糊查找用于对字符类型的字段进行字符匹配查找。

2、要查找的字符中，有两个特殊含义的字符：% , _:

     2.1: %含义是：代表0或多个的任意字符

     2.2: _含义是：代表1个任意字符

     2.3: 这里的字符都是指现实中可见的一个“符号”，而不是字节。

3、语法：like '%关键字%'

SELECT  *  FROM  student  WHERE  NAME  LIKE  '张%' ;  -- 以张开头

SELECT  *  FROM  student  WHERE  NAME  LIKE  '张_' ;  -- 以张开头，而且名字是两个字

SELECT  *  FROM  student  WHERE  NAME  LIKE  '%张%' ;  -- 名字里面只要有张就可以

如果要查找的字符里中包含"%","_"，

如果要查找的字符中包含“%”或“_”，“  ’”，则只要对他们进行转义就可以：

like ‘%ab\%cd%’ //这里要找的是： 包含 ab%cd 字符的字符

like ‘\_ab%’ //这里要找的是： _ab开头的字符

like ‘%ab\'cd%’ //这里要找的是： 包含 ab'cd 字符的字符