数据库概述
数据库是存储电子文件的处所,本质是一个文件系统,我们可以使用命令(SQL)对文件中的数据进行新增、查询、更新、删除等操作。
作用是存储数据,带有访问权限限制不同的用户使用不同的操作。
MySQL的SQL语句:
SQL:Struct Query Language 结构化查询语言
DDL(数据定义语言):定义数据库,数据表它们的结构:create(创建)、drop(删除)、alter(修改)
DML(数据操纵语言):主要是用来操作数据 insert(插入)、update(修改)、delete(删除)
DCL(数据控制语言):定义访问权限,取消访问权限,安全设置 grant
DQL(数据查询语言):select(查询)、form子句、where子句
数据库的CRUD操作
创建数据库
create database 数据库的名字;
---创建数据库的时候,指定字符集
create database 数据库的名字 character set utf8;
---创建数据库的时候,指定字符集和校对规则
create database 数据库的名字 character set utf8 collate 校对规则;
create database yunyu character set utf8 collate utf8_bin;
查看数据库
---查看数据库d定义的语句
show create database 数据库的名字;
---查看所以数据库
show databases;
修改数据库
---修改数据库的字符集
alter database 数据库的名字 character set 字符集;
删除数据库
drop database 数据库的名字;
其他数据库操作命令
--切换数据库(选中数据库)
use 数据库的名字;
--查看当前正在使用的数据库
select database();
创建表
create table 表名(
列名 列的类型(长度) 约束,
列名2 列的类型(长度) 约束
);
列的类型:
int
char 固定长度 ----长度代表的是字符的个数
varchar 可变长度
double
float
boolean
date : YYYY-MM-DD
time : hh:mm:ss
datetime : YYYY-MM-DD hh:mm:ss 默认是null
timestamp : YYYY-MM-DD hh:mm:ss 默认是当前时间
text : 主要是用来存放文本
blob : 存放的是二进制
列的约束:
主键约束:primary key
唯一约束:unique
非空约束:not null
外键约束: foreign key
自动增长 : auto_increment
--外键约束
alter table product add foreign key(cno) references category(cid);
--主键约束: 默认就是不能为空, 唯一
--外键都是指向另外一张表的主键
--主键一张表只能有一个
--唯一约束: 列面的内容, 必须是唯一, 不能出现重复情况, 为空
--唯一约束不可以作为其它表的外键
--可以有多个唯一约束
查看表
--查看所有表
show tables;
--查看表的创建过程
show create table 表名;
--查看表结构
desc 表名;
修改表
--添加列(add)
alter table 表名 add 列名 列类型 列的约束
--修改列(modify)
alter table 表名 modify 字段名 列的类型(长度)
--修改列名(change)
alter table 表名 change 旧列名 新列名 新列类型(长度) 新列的约束
--删除列(drop)
alter table 表名 drop 列名;
--修改表名(rename)
rename table 原表名 to 新表名;
--修改表的字符集
alter table 表名 character set utf8;
删除表
drop table 表名;
SQL完成对表中数据的CRUD的操作
插入数据
insert into 表名(列名1,列名2,列名3...) values(值1,值2,值3...);
--简单写法,如果插入的是全列名的数据,表名后面的列名可以省略
--如果插入的是部分列的数据,列名不能省略
insert into 表名 values (值1,值2,值3...);
--批量插入
insert into 表名 values(值1,值2,值3...),(值1,值2,值3...),(值1,值2,值3...)...;
命令行下插入中文问题:
临时解决方案
set names utf8; 相当于告诉MySQL服务器,当前命令行下输入的内容是utf8编码
永久解决方法:
暂停mysql的服务
在mysql安装路径下找到my.ini的配置文件,修改
default-character-set=utf8
重新启动mysql服务
删除记录
delete from 表名 [where 条件]; -- 不指定条件,会将表中数据一条一条全部删除
--delete删除数据和truncate 删除数据的区别
/*
delete : DML 一条一条删除表中数据
truncate : DDL 先删除表在重新建表
效率高低,得看数据量
数据量少:delete
数据量大:truncate 比较高效
*/
更新表记录
update 表名 set 列名=列的值,列名2=列的值 [where 条件];
查询记录
select [distict][*][列名1,列名2] from 表名 [where 条件]
distinct : 去除重复数据
--简单查询
--查询所有记录:
select * from product;
--查询指定字段的数据
select pname,price from product;
--别名查询 as 关键字 该关键字是可以省略的
--表别名
select p.pname,p.price from product as p;
--列别名
select pname as 商品名称,price as 商品价格 from product;
--省略as关键字
select pname 商品名称,price 商品价格 from product;
--去掉重复的值
select distinct price from product;
--select 运算查询
select *,price*1.5 as 涨后价格 from product;
--条件查询[where关键字]
--指定条件,确定要操作的记录
select * from product where price >60;
--where 后的条件写法
--关系运算符: > 、>=、 < 、<= 、= 、!= 、<>
<> : 不等于 标准SQL语法
!= : 不等于 非标准SQL语法
--逻辑运算: and , or ,not
select * from product where price > 10 and price < 100;
between ... and ...
select * from product where price between 10 and 100;
--like : 模糊查询
_ : 代表的是一个字符
% : 代表的是多个字符
--查询名称带AMD的记录
select * from product where like pname like '%AMD%';
--查询第二个字带'好'的所有记录
select * from product where like pname like '_好%';
--in 在某个范围内取值
select * from product where cno in (1,4,5);
--排序查询 order by 关键字
asc : ascend 升序 (默认的排序方式)
desc : descend 降序
select * from product order by price ;--默认升序
select * from product order by price desc; --降序
--聚合函数
sum() : 求和
avg() : 求平均值
count() : 统计数量
max() : 最大值
min() : 最小值
select sum(price) from product;
select avg(price) from product;
-- where条件后面不能接聚合函数
--分组:group by
1.根据cno字段分组,分组统计商品个数
select cno,count(*) from product group by cno;
2.根据cno字段分组,分组统计每组商品的平均价格,并且商品的平均价格大于60
select cno,avg(price) from product group by cno having avg(price)>60;
--having 关键字 可以接聚合函数 出现在分组之后
--where 关键字 不可以接聚合函数,出现在分组之前
--编写顺序:
SELECT .. FROM .. WHERE .. GROUP BY .. HAVING .. ORDER BY
--执行顺序:
FROM .. WHERE .. GROUP BY .. HAVING .. SELECT .. ORDER BY
多表查询
笛卡尔积
--笛卡尔积 ,查出来是两张表的乘积 ,查出来的结果没有意义
SELECT * FROM product,category;
内连接查询
-- 隐式内链接
SELECT * FROM product p,category c WHERE p.cno=c.cid;
-- 显示内链接
SELECT * FROM product p INNER JOIN category c ON p.cno=c.cid;
-- 区别:
--隐式内链接: 在查询出结果的基础上去做的WHERE条件过滤
--显示内链接: 带着条件去查询结果, 执行效率要高
外连接
--左外连接
--左外连接,会将左表中的所有数据都查询出来, 如果右表中没有对应的数据,用NULL代替
SELECT * FROM product p LEFT OUTER JOIN category c ON p.cno=c.cid;
--右外连接: 会将右表所有数据都查询出来, 如果左表没有对应数据的话, 用NULL代替
SELECT * FROM product p RIGHT OUTER JOIN category c ON p.cno=c.cid;
子查询
-- 单行子查询(> < >= <= = <>)
-- 查询出高于10号部门的平均工资的员工信息
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 10);
-- 多行子查询(in not in any all) >any >all
-- 查询出比10号部门任何员工薪资高的员工信息
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 10);
-- 查询出比10号部门任意一个员工薪资高的所有员工信息 : 只要比其中随便一个工资都可以
SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno = 10) AND deptno != 10;
-- 多列子查询(实际使用较少) in
-- 和10号部门同名同工作的员工信息
SELECT * FROM emp WHERE (ename,job) IN (SELECT ename,job FROM emp WHERE deptno=10) AND deptno !=10;
-- Select后面接子查询
-- 获取员工的名字和部门的名字
SELECT ename,(SELECT dname FROM dept d WHERE d.deptno = e.deptno ) 部门名称 FROM emp e ;
-- from后面接子查询
-- 查询emp表中所有管理层的信息
SELECT * FROM emp e,(SELECT DISTINCT mgr FROM emp) mgrtable WHERE e.empno = mgrtable.mgr;
-- where 接子查询
-- 薪资高于10号部门平均工资的所有员工信息
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno=10);
-- having后面接子查询
-- 有哪些部门的平均工资高于30号部门的平均工资
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno=30);
-- 列出达拉斯加工作的人中,比纽约平均工资高的人
SELECT * FROM emp WHERE deptno = ( SELECT deptno FROM dept WHERE loc ='DALLAS')
AND
sal > (SELECT AVG(sal) FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc ='NEW YORK'));