黑马程序员技术交流社区

标题: 【石家庄校区】数据库操作sql语句 [打印本页]

---

作者: 晨阳    时间: 2018-1-23 15:19
标题: 【石家庄校区】数据库操作sql语句

数据库笔记

数据库：就是一个为文件系统，这个文件必须通过标准的SQL语句访问
关系型数据库：
MySQL ：免费的，被Oracle收购了6.0后开始收费
Oracle ：Oracle收费的大型数据库
SQLServer：微软公司收费的中型数据库
DB2：IBM公司收费的大型数据库
SyBase：SyBase公司收费的数据库.已经被淘汰.PowerDesigner数据建模的工具.
SQLite：小型的嵌入式数据库
***** Java程序中经常使用的数据库
MySQL
Oracle
通常为一个应用创建一个数据库
通常应用中有一个实体创建一个表
有一个实体对象，就有一条记录与之对应
SQL：结构化的查询语言
SQL分类
DDL：数据定义语言
create   alter  drop
DML：数据操纵语言
update  insert  delete
DCL:数据控制语言
grant  if
DQL
select
SQL的特点：非过程性语言，一条语句就会有一个运行结果
使用SQL对数据库的增删改查CRUD
create database 数据库 character set 字符集 collate 字符集校对
校对规则，不写的话，就会使用默认的方式校对

查看数据库
查看数据库服务器中所有数据库
show database;
查看某个数据库的定义信息
show create database 数据库名;
查看正在使用的数据库
select database();
【删除数据库】
语法:
* 删除数据库:
    * drop database 数据库名;
【修改数据库】
语法:
* 修改数据库修改的是的数据库的字符集和校对规则.
    * alter database 数据库名 character set 新字符集 collate 校对规则;
【切换数据库】
语法:
* use 数据库名称;
   使用SQL操作数据库中的表(对数据库的表CRUD的操作)
【创建表】
Ø  语法:
* create table 表名 (
    字段名 类型(长度) 约束,
    字段名 类型(长度) 约束,
    字段名 类型(长度) 约束
);

Ø  数据类型:
* Java类型:                  MySQL:
  byte/short/int/long  tinyint/smallint/int/bigint
  String                       char/varchar
                                * 区别?char是固定长度的字符串,varchar可变长度的字符串.
                                * char(8) 和 varchar(8)
                                * 如果插入一个字符串hello 插入到char 那么 插入hello   .插入到varchar中 插入hello
float                         float
double                        double
BigDemal                      decimal
boolean                        bit(只有0和1两个值)
Date                    date/time/datetime/timestamp
    * datetime和timestamp都是既有日期又有时间的日期类型
   * 区别? datetime需要使用外部传入的日期.如果没传这个值就是Null. timestamp会使用系统当前的时间作为这个值的默认值.
文本文件    对应MySQL中             text
二级制文件   对应MySQL中           BLOB

***** Oralce使用CLOB/BLOB
***** MYSQL中除了字符串类型需要设置长度其他的类型都有默认长度.

Ø  约束：
单表约束：
* 主键约束：primary key (默认就是唯一非空的)
* 唯一约束：unique
* 非空约束：not null

Ø  创建一个表：
Ø  ***** 创建表之前先选择数据库:use 某个数据库;

【表的查看】
Ø  查看数据库中有哪些表：
* show tables;
Ø  查看表结构:
* desc 表名;
【表的删除】
Ø  表的删除:
* drop table 表名;
【表的修改】
Ø  修改表添加列：
* alter table 表名 add 列名 类型(长度) 约束;

Ø  修改表删除列：
* alter table 表名 drop 列名;
    *
Ø  修改表的列的类型长度及约束:
* alter table 表名 modify 列名 类型(长度) 约束;

Ø  修改表的列名
* alter table 表名 change 旧列名 新列名 类型(长度) 约束;

Ø  修改表名
* rename table 旧表名 to 新表名;
    * rename table employee to user;
Ø  修改表的字符集：
* alter table 表名character set 字符集;

使用SQL操作数据库中的表的记录(对表的记录的CRUD的操作)
【插入记录】
Ø  语法
* insert into 表名 (列名,列名,...) values (值1,值2,...);       ---插入指定列的值
* insert into 表名 values (值1,值2,...);                  ---插入所有列的值

Ø  注意事项：
* 列名的个数与值的个数对应.
* 列的类型与值的类型对应.位置也要对应.
* 列的类型如果是字符串或者日期,写值的时候使用单引号将值引起来.
* 插入的值的最大长度不能超过列的最大长度.

Ø  插入记录：
* 插入某几列的值:
    * insert into employee (eid,ename,email) values (null,'aaa','aaa@itcast.cn');
* 插入所有列的值:
    * insert into employee values (null,'bbb','bbb@itcast.cn','1990-09-01','HR','I am HR');
Ø  插入中文：
insert into employee (eid,ename,email) values (null,'张三','aaa@163.cn');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'ename' at row 1
***** 插入中文问题的解决：
* show variables like '%character%';
C:/Users/%E9%99%88%E6%B4%8B/AppData/Local/YNote/data/qq6C1F80BFC2D36C256C4E179EA0631FAE/1fa17cedb60b47a3a2a6571664c3c760/ip_image002.jpeg
***** 找到MYSQL的安装路径/my.ini文件：
C:/Users/%E9%99%88%E6%B4%8B/AppData/Local/YNote/data/qq6C1F80BFC2D36C256C4E179EA0631FAE/8cb7dadf675345218ef4d45aad5cdca5/ip_image004.jpeg
**** 重新加载mysql的配置文件：
* services.msc
* 停止mysql的服务,重新启动mysql服务.
* 执行之前的SQL语句.
【删除记录】
Ø  语法：
* delete from 表 [where 条件];
Ø  注意事项：
* 删除表中的一行记录,不能删除某列值
* 如果没有条件删除表中的所有列.
Ø  练习：
* 删除id为8的记录:
    * delete from employee where eid = 8;
* 删除所有记录:
    * delete from employee;
Ø  删除表中的所有记录truncate table 表名  和 delete from 表  区别?
* 区别:
    * truncate table 删除表的记录：将整个表删除掉,重新创建一个新的表.truncate属于DDL.
    * delete from 删除表的记录：一条   一条进行删除. delete属于DML。
        * 事务管理 只能作用在DML语句上.如果再一个事务中使用delete删除所有记录，可以找回.
【基本查询】
Ø  查询语句：
* select [distinct] *|列名 from 表 [where 条件];


Ø  查询所有记录:
* select * from exam;
Ø  查询这个班级人的姓名和英语成绩:
* select name,english from exam;
Ø  查询英语成绩,将重复英语成绩去掉:
* select distinct english from exam;
Ø  查询李四的学生成绩:
select * from exam where name='李四';
Ø  查询名称叫李四并且英语成绩大于90的
select * from exam where name='李四' and english >90;
Ø  将成绩+10分进行显示:
select name ,english+10,chinese+10 ,math+10 from exam;
Ø  显示这个人的名称和对应总成绩的分数:
select name,english+chinese+math from exam;
Ø  使用as起别名,as可以省略.
select name , english+chinese+math as sum from exam;
【条件查询】
Ø  where语句后面可以加：
条件的关键字：
= , > , >= , <, <= , <>

like中可以使用占位符: _ 和 %  ：下划线匹配一个字符, %:可以匹配任意多个字符.
* like ‘张％’;  like ‘张_’; like ‘%明’; like ‘%明%’;

in 后跟着一组值.
* id in (1,2,3)

and or not
【排序查询】
Ø  order by 对数据进行排序.默认升序. （asc升序,desc降序）
* 查询所有学生的信息,并且按语文成绩进行排序.
    * select * from exam order by chinese;
* 查询所有学生的信息,并且按语文成绩进行降序排序.
    * select * from exam order by chinese desc;
* 查询学生的信息,按照英语成绩降序排序，如果英语成绩相同,按照语文降序.
    * select * from exam order by english desc, chinese desc;
* 查询姓李的学生的信息,同时按照英语升序排序.
    * select * from exam where name like '李%' order by english asc;
【聚合函数】
Ø  sum()
Ø  count()
Ø  max()
Ø  min()
Ø  avg()
* 查询每个学生总成绩:
    * select name,(english+chinese+math) from exam;
* 统计所有学生的总分:
    * select sum(english+chinese+math) from exam;   -- ifnull(english,0)
    * select sum(english)+sum(chinese)+sum(math) from exam;
* 统计学生的个数:
    * select count(*) from exam;
* 统计英语成绩的最高分:
    * select max(english) from exam;
* 统计语文成绩的最低分:
    * select min(chinese) from exam;
* 统计英语成绩平均分:
    * select avg(english) from exam;
【分组】
Ø  group by
创建一个订单详情的表:
* 统计订单中的每类商品所购买的个数：
    * SELECT product,COUNT(*) FROM orderitem GROUP BY product;
* 统计订单中的每类商品所花的金额:
    * SELECT product,SUM(price) FROM orderitem GROUP BY product;
* 统计订单中的每类商品所花总金额大于2000信息.
    * SELECT product,SUM(price) FROM orderitem GROUP BY product HAVING SUM(price) > 2000;

* 统计订单中名称有电子的商品并且所花金额大于1500同时按照价格降序排序：
    * SELECT product,SUM(price) FROM orderitem WHERE product LIKE '电%' GROUP BY product HAVING SUM(price) > 1500 ORDER BY SUM(price) DESC;
【SQL的查询语句的总结】
顺序:   s...f...w...g...h...o...;
【数据库的多表设计】
数据库都是关系型的数据库,存的是实体之间的关系.实体之间有哪些关系?
实体的关系总结起来就有三种关系：

一对多:
    * 客户和订单：一个客户可以产生多个订单,一个订单只能属于是某一个客户.
    * 部门和员工：一个部门下可以有多个员工,一个员工只能属于某一个部门.
C:/Users/%E9%99%88%E6%B4%8B/AppData/Local/YNote/data/qq6C1F80BFC2D36C256C4E179EA0631FAE/7b6ea34f4a804ac18d128b247eaf0708/%E5%9B%BE%E5%9B%9B%20%E4%B8%80%E5%AF%B9%E5%A4%9A%E5%8F%8A%E5%A4%9A%E5%AF%B9%E5%A4%9A%E7%9A%84%E8%A1%A8%E7%9A%84%E5%88%9B%E5%BB%BA.bmp

多对多:
    * 学生和课程：一个学生可以选择多门课程,一门课程可以被多个学生选择.
    * 订单和商品：一个订单中包含多个商品,一个商品也可以出现多个订单中.
C:/Users/%E9%99%88%E6%B4%8B/AppData/Local/YNote/data/qq6C1F80BFC2D36C256C4E179EA0631FAE/5c925520e2604053ac5c1b574d5b1335/%E5%9B%BE%E5%85%AD%20%E5%95%86%E5%9F%8E%E7%9A%84%E8%A1%A8%E7%BB%93%E6%9E%84%E5%88%86%E6%9E%90.bmp

一对一：
    * 公司和地址：一个公司只能有一个注册地址,一个地址也只能被一个公司注册.
C:/Users/%E9%99%88%E6%B4%8B/AppData/Local/YNote/data/qq6C1F80BFC2D36C256C4E179EA0631FAE/5a4f7207dfbb472f831d1b7d900ca870/%E5%9B%BE%E5%9B%9B%20%E4%B8%80%E5%AF%B9%E4%B8%80%E7%9A%84%E8%A1%A8%E7%9A%84%E5%88%9B%E5%BB%BA.bmp
【多表的设计】
Ø  一对多的关系的建表原则：
* 在多的一方创建一个字段,这个字段作为外键执向一的一方的主键.

Ø  多对多的关系的建表原则：
*　创建一个第三种表，中间表中至少需要两个字段分别作为外键执向多对多双方的各自的主键.

Ø  一对一的关系的建表原则：
* 唯一外键对应：假设一对一的双方是一对多的关系.在多的一方创建外键执向一的一方的主键.需要在外键上添加一个unique约束.
* 主键对应：将一对一的双方的主键建立映射.
【使用SQL创建一对多的关系】


***** 约束：用来保证数据的完成型.
* 多表约束：外键约束！！！
***** 给orders表中的cid添加外键约束.
* alter table orders add foreign key (cid) references customer(cid);
【多表的查询的SQL】
Ø  多表的查询的方式：
* 交叉连接：
    * select * from A,B;   --- 获得的是两个表的笛卡尔积.
* 内连接: inner join -- inner 可以省略
    * 显式内连接：select * from A inner join B on 条件;

    * 隐式内连接：select * from A,B where 条件;

* 外连接：outer join -- outer 可以省略
* 左外连接：left outer join  -- select * from A left outer join B on 条件;

* 右外连接：right outer join -- select * from A right outer join B on 条件;

【多表查询的子查询】
Ø  一个SQL语句查询的过程中需要依赖另一个查询语句.
SELECT * FROM customer c,orders o WHERE c.cid = o.cid AND  c.cid IN (SELECT cid FROM orders WHERE addr LIKE '海淀%');
【多表练习】
按客户名称统计订单的个数.
SELECT c.cname,COUNT(*) FROM customer c,orders o WHERE c.cid = o.cid GROUP BY c.cname;

---

欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2