MySQL数据库入门

数据库概述

数据库是存储电子文件的处所，本质是一个文件系统，我们可以使用命令（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'));