MySQL

1. MySQL数据库服务器

• MySQL数据库：一种开放源代码的关系型数据库管理系统，使用最常用的数据库管理语言SQL进行数据库管理
• 服务器：就是一台电脑，安装了相关的服务器软件，这些软件会监听不同的端口号，根据用户访问的端口号提供不同的服

2. MySQL的安装和卸载

• 安装：MySQL数据库软件安装.avi（我的root账号：root，密码： root123.）

• 卸载：Step1. 通过控制面板找到"MySQL Server"，点击卸载；

  ? Step2. 删除MySQL安装目录的所有文件C:\Program Files\MySQL

  ? Step3. 删除MySQL数据存放文件C:\ProgramData\MySQL

3. MySQL的SQL语句

• SQL: Structure Query Language 结构化查询语言

• SQL是用来存取关系数据库的语言，具有查询，操纵，定义和控制关系型数据库的四方面功能

• SQL分类:

  ? DDL: 数据定义语言(定义数据库和数据表的结构)：create(创建) drop(删除) alter(修改)

  ? DML: 数据操纵语言，主要是用来操纵数据：insert(插入) update(修改) delete(删除)

  ? DCL: 数据控制语言，用来设置用户权限和控制事务语句，如grant(授权)，revoke，if...else，while，begin transaction

  ? DQL: 数据查询语言:select(查询)

4. 数据库的CRUD操作(不区分大小写)

? 首先在控制台输入"mysql -u用户名 -p密码"连接MySQL数据库

? mysql -uroot -proot123.

• 创建数据库

  a. create database 数据库的名字;
  	eg: create database day06;
  b. create database 数据库的名字 character set 字符集;
  	eg: create database day06 character set utf8;//而不是utf-8
  c. create database 数据库的名字 character set 字符集 collate 校对规则;
  	eg: create database day06 character set utf8 collate utf8_bin;
  
  

• 查看数据库

  a. 查看数据库定义的语句
  	show create database 数据库的名字；
  	eg: show create database day06;
  b. 查看所有数据库
  	show databases;
  (information_schema  mysql  performace_schema不要动这三个库)
  c. 查看当前正在使用的数据库
  	select database();
  

• 修改数据库

  a. 修改数据库的字符集
  	alter database 数据库的名字 character set 字符集;
  	eg: alter database day06 character set gbk;
  b. 选中或切换数据库
  	use 数据库的名字；
  	eg: use day06;
  

• 删除数据库

  drop database 数据库的名字；
  eg:drop database day06;
  

5. MySQL常用数据类型

--字符串型： char,varchar 
	char是固定长度的，varchar是可变长度,长度代表字符的个数，如char(3)和varchar(3)中都存放字符a，char中存放的是“a 空格 空格”，而varchar中存放的是“a”，其它位置空着
    mysql varchar(50) 不管中文 还是英文 都是存50个的
--大数据类型： blob, text
	blob：二进制形式的长文本数据
	text：长文本数据
--数值型： tinyint, smallint, int, bigint, float, double
	tinyint：从 0 到 255 的整型数据，存储大小为 1 字节
	smallint：从-2^15(-32,768)到2^15-1(32,767)的整型数据，存储大小为 2 个字节
	int：从 -2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据（所有数字），存储大小为 4 个字节
    bigint：从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据（所有数字），存储大小为 8 个字节
    float数值类型用于表示单精度浮点数值
    double数值类型用于表示双精度浮点数值
--逻辑型： bit
	bit的值是一个二进制位，不是0就是1，类似Boolean类型中的True和False
--日期型： date, time, datetime, timestamp
	date: YYYY-MM-DD
	time: hh:mm:ss
	datatime: YYYY-MM-DD hh:mm:ss,默认值是null
	timestamp: YYYY-MM-DD hh:mm:ss,默认使用当前时间

列的类型：对比java和sql
		---Java			sql
			int				int
			Char/string		char/varchar
			double			double
			float			float
			boolean			boolean
			date			date
							time
							datetime
							timestamp
									
							text
							blob

6. 列的约束

主键约束：primary key（主键不能重复，不能为空）
唯一约束：unique
非空约束：not null

删除主键: alter table 表名 drop primary key;
主键自动增长: auto_increment，从1开始增长，当把第一条记录删除，再插入第二条数据时，主键值是2，不是1

7. 表的CRUD操作

• 创建表

  注意创建表之前要先使用use database语句使用数据库；

  creat table 表名（列名 列的类型(长度) 约束,列名2 列的类型(长度) 约束）；
  --创建表的实例
  学生：学生ID；姓名；性别
  create table student(
  	id int primary key,
  	name varchar(10),
  	sex int
  );//注意sex int结尾没有逗号
  
  

• 查看表

  查看所有表：show tables;
  查看表的创建过程：show create table 表名; 
  			eg: show create table student;
  查看表的结构：desc 表名;
  			eg: desc student;
  

• 修改表

  注意：修改列名时要加上该列本来的约束

  --修改表的结构相关内容时要加alter table 表名
  添加列(add)：alter table 表名 add 列名 列的类型 列的约束；
  			eg: alter table student add grade int not null; 
  修改列(modify)：alter table 表名 modify 列名 列的类型 列的约束;
  			eg: alter table student modify grade varchar(3);//也可修改列的长度
  修改列名(change)：alter table 表名 change 旧列名 新列名 列的约束；
  			eg: alter table student change sex gender int;
  删除列(drop)：alter table 表名 drop 列名；
  			eg: alter table student drop grade;
  修改表的字符集（不常用）：alter table 表名 character set 新字符集;
  			eg: alter table heima character set gbk;
  修改表名（rename）（不常用）：rename table 旧表名 to 新表名;
  			eg: rename table student to heima;
  

• 删除表

  drop table 表名;
  eg: drop table heima;
  

8. SQL完成对表中数据的CRUD操作

• 插入单条数据

  insert into 表名（列名1，列名2，列名3） values(值1，值2，值3);
  	eg: insert into student(id,name,sex) values(001,“Tom”,1);
  简单写法（前提必须是插入所有列名的数据，则表名后面的列名可以省略）：
  	eg: insert into student values(002,“Jerry”,1);
  验证是否插入成功：查看表中的数据：select * from student;
  

  插入的数据应与字段的数据类型相同；

  数据大小应在列的规定范围内；

  在values中列出的数据位置必须与被加入的列的排列位置相对应；

  字符和日期型数据应包含在单引号中。

• 插入多条数据

  insert into student(id,name,sex) values(003,"Jude",0),(004,"John",1),(005,"Jerry",0);
  如果是所有列都插入数据，也可写成：
  insert into student values(003,"Jude",0),(004,"John",1),(005,"Jerry",0);
  也可分开写：
  insert into student(id,name,sex) values(003,"Jude",0);
  insert into student(id,name,sex) values(004,"John",1);
  insert into student(id,name,sex) values(005,"Jerry",0);
  

9. 解决命令行插入中文出现的乱码问题

eg: insert into student values(1,"李四",1);
错误代码：ERROR 1366 (HY000): Incorrect string value: ‘\xC0\xEE\xCB\xC4‘ for column ‘n
错误原因(还不是很确定)：客户端的编码方式是gbk，数据库服务器的编码方式也是gbk，但存入数据仍会出现错误，因为数据在传输过程中采用的是utf8编码。例如：要客户端的数据是“张三”，在客户端采用gbk转换成“aabb”，传输过程中采用utf8转换成“1122”，传到数据库服务器后，将“1122”采用gbk存入就会出现错误
临时解决方案：set names gbk;相当于是告诉mysql服务器软件，我们当前在命令行下输入的内容是gbk编码，当命令窗口关闭后，再输入中文就会出错；

永久解决方案：
		Step1: 在 任务管理器-服务 中暂停mysql的服务；
		Step2: 在mysql安装路径中找到my.ini配置文件;(C:\Program Files\MySQL\MySQL Server 5.5)
		Step3: 将57行的编码改成gbk;
		Step4: 保存文件退出，然后启动mysql服务

10. 删除表数据

delete from 表名 [where 条件];//[]表示可选，如果没有条件限制，则会全部删除，下面类似
drop table 表名；
面试问题：drop,truncate和delete的区别
	drop是DDL，删除表结构及所有数据，并将表所占用的空间全部释放；
	truncate是DDL，会隐式提交，先删除表，再重建表，这个表和索引所占用的空间会恢复到初始大小；
	delete是DML，一行一行地从表中删除数据，不会减少表或索引所占用的空间
当数据少时，用delete效率比较高；当数据较多时，用truncate效率比较高
一般来说，速度：drop>truncate>delete;

11. 更新表数据

update 表名 set 列名1=列的值1，列名2=列的值2 [where 条件];
	如果参数值是字符串以及日期，要加上单引号；
eg: 将sid为222的名字改为Andy；
	update student set name=‘Andy‘ where id=222;
	将sid为222的名字改为Andy，性别改为0；
	update student set name=‘Andy‘,sex=‘0‘ where id=222;
	
	将ww的薪水在原有基础上增加1000元。
	update user set salary=salary+1000 where username=‘ww‘;

12. 查询表数据

以下内容通过建表来讲解：
	--商品分类：分类ID 分类名称 分类描述；
		create table category(
    		id int primary key auto_increment,//整数字段从1开始，自动递增
        	name varchar (4),
       		desc varchar(10)
    	);//desc是关键字，最好不用，如果非要用，则需加上反引号，即`desc`；(控制台中要加``，如果是客户端中就不需要)
		插入数据：
		insert into category values(null,‘手机数码‘,‘电子产品‘);
		insert into category values(null,‘鞋靴箱包‘,‘生活必备‘);
		insert into category values(null,‘香烟酒水‘,‘宴席必备‘);
		insert into category values(null,‘文具办公‘,‘办公必备‘);
		insert into category values(null,‘馋嘴零食‘,‘追剧必备‘);
		查询所有数据：select * from category;
		查询部分数据：如查询商品名称和描述：select name,`desc` from category;
	--所有商品: 商品ID，商品名称，商品价格，生产日期，商品分类ID
		create table product(
			id int primary key auto_increment,
			name varchar(6),
			price double,
			mfd timestamp,
			num int
		);
		插入数据：
		insert into product values(null,‘小米8‘,1799,null,1);
		insert into product values(null,‘小米mix4‘,3999,null,1);
		insert into product values(null,‘特步‘,199,null,2);
		insert into product values(null,‘劲酒‘,19,null,3);
		insert into product values(null,‘酸梅汤‘,9,null,3);
		insert into product values(null,‘小米巨能写‘,9,null,4);
		insert into product values(null,‘卫龙辣条‘,3,null,5);
		insert into product values(null,‘乡巴佬鸡腿‘,9,null,5);
		查询商品名称和价格：select name,price from product;
		
		别名查询：as关键字，as可以省略
			表别名（主要是用在多表查询）：
		select p.name,p.price from product as p;
		（执行顺序：from procuct -> as p- > p.name,p.price）
			列别名：
		select name as 商品名称,price as 商品价格 from product;
		
		去掉重复的值：
		select distinct 列名 from 表名;//dictinct: 去除重复的数据
		select [distinct] [*] [列名1，列名2] from 表名 [where 条件];
			eg: 去掉重复的商品价格
		select distinct price from product;
		
		select运算查询：仅仅在查询结果上做了运算+ - * /
		select *,price*0.8 from product;
		select *,price*0.8 as 折后价 from product;
		
		条件查询(where 关键字)
		指定条件，确定要操作的记录；
		查询价格大于60元的所有商品信息：
			select * from product where price >60;
		where后的条件写法
			判断某一列是否为空：is null; is not null
			关系运算符：>  <  >=  <=  =  !=  <>
				<>: 不等于；标准sql语法
				!=：不等于；非标准sql语法
			查询价格不等于9的所有商品：
				select * from product where price<>9;
				select * from product where price!=9;
			查询商品价格在10~100之间的商品：
				select * from product where price>10 and price<100;
				select * from product where price between 10 and 100;(between 小 and 大)
			逻辑运算：and or not
			查询价格小于100和大于1000的商品：
				select * from product where price<100 or price >1000;
				
		模糊查询like：	
		_代表一个字符；%代表多个字符
			查询出名字中带有“小”的所有商品：
				select * from product where name like "%小%";
				本例中也可写作：select * from product where name like  "小%";
			查询名字第二个字是“米”的所有商品：
				select * from product where name like  "_米%";
			
		在某个范围内查询in：
			查询商品分类ID在1，3，5里的所有商品：
				select * from product where num in (1,3,5);
		
		排序查询：
		order by 关键字；order by子句应位于select语句的结尾
			asc: 即ascend 升序；desc: 即descend 降序；
			select * from product order by price;（默认按升序排列）
		查询名字中有“小”的所有商品，并按价格降序排列：
			select * from product where name like "%小%" order by price desc;

13. 聚集函数

sum(): 求和；avg(): 求平均值；count(): 统计数量；max(): 最大值；min(): 最小值；
	求出所有商品价格的总和：
		select sum(price) from product;
	统计一个班级语文、英语、数学的成绩总和： 
	     select sum(chinese)+sum(math)+sum(english) from exam;
	     或select sum(chinese+math+english) from exam;
	求所有商品的平均价格：
		select avg(price) from product;
	求所有商品总数：
		select count(*) from product; 
	查出商品价格大于平均价格的所有商品（子查询）：
	注意：where条件后面不能接聚集函数，因为where条件的执行顺序在聚集函数之前
	错误写法：select * from product where price >avg(price);
	正确写法：
		select * from product where price >(select avg(price) from product);

14. 分组group by

根据分类ID进行分组，并统计商品的个数：
	select num,count(*) from product group by num;
根据num分组，统计每组商品的平均价格，筛选出平均价格>60的：
	select num,avg(price) 
	from product group by num
	having avg(price)>10;
	having关键字，过滤声明，出现在分组之后，可以接聚集函数；
	where关键字，约束声明，出现在分组之前，不可以接聚集函数
编写顺序：select控制显示
	S -> F -> W -> G -> H -> O
		select -> from -> where -> group by -> having -> order by
执行顺序：	
	F -> W -> G -> H -> S -> O	
		from -> where -> group by -> having -> select -> order

15. 备份，恢复数据库

--备份数据库:
	step1:进入控制台
	step2:进入mysql下的bin目录
		cd C:\Program Files\MySQL\MySQL Server 5.5\bin
	step3:mysqldump -h数据库服务器 -u 用户名 -p 数据库名>要备份的完整的目标路径名+文件名.sql
    	如：mysqldump -h localhost -u root -p day06>D:\JavaEE\day06.sql
		注意：路径名中不能有中文
	备份day06数据库中的某几张表category和product：
		mysqldump -h localhost -u root -p day06 category product>D:\JavaEE\day006.sql

--恢复数据库:
method1:先连接mysql数据库，在mysql内部使用
	step1:先选中要将数据恢复到哪个数据库中：
				use temp2;
	step2:source 文件名.sql   
    			source D:\JavaEE\day006.sql
    			
 method2:在cmd下使用
	mysql -h数据库服务器 –u 用户名 -p 要将数据恢复到哪个数据库中 < 备份的数据库全路径
	mysql -h localhost -u root -p temp<D:\JavaEE\day006.sql

16. SQL创建多表及多表的关系

• 例：如何在数据库中表示分类表和商品表之间的关系

	商品分类表：分类ID，分类名称，分类描述
		create table category(
			id int primary key auto_increment,
			name varchar (4),
			`desc` varchar(10)
		);//desc是关键字，最好不用，如果非要用，则需加上反引号，即`desc`
		插入数据：
        insert into category values(null,‘手机数码‘,‘电子产品‘);
        insert into category values(null,‘鞋靴箱包‘,‘生活必备‘);
		insert into category values(null,‘香烟酒水‘,‘宴席必备‘);
        insert into category values(null,‘文具办公‘,‘办公必备‘);
        insert into category values(null,‘馋嘴零食‘,‘追剧必备‘);
	商品信息表: 商品ID，商品名称，商品价格，生产日期，商品分类ID
		create table product(
			id int primary key auto_increment,
			name varchar(6),
			price double,
			mfd timestamp,
			num int
		);
	插入数据：
		insert into product values(null,‘小米8‘,1799,null,1);
        insert into product values(null,‘小米mix4‘,3999,null,1);
        insert into product values(null,‘特步‘,199,null,2);
        insert into product values(null,‘劲酒‘,19,null,3);
        insert into product values(null,‘酸梅汤‘,9,null,3);
        insert into product values(null,‘小米巨能写‘,9,null,4);
        insert into product values(null,‘卫龙辣条‘,3,null,5);
        insert into product values(null,‘乡巴佬鸡腿‘,9,null,5);

技术分析：

? 外键约束：foreign key;

? 给product中的商品分类ID num添加外键约束，使其和category中的分类id关联起来；这样在product添加的商品的分类id必须是category中存在的才能添加进去

alter table product add foreign key(num) references category(id);
insert into product values(null,‘路虎揽胜‘,999999,null,6);//添加失败

? 从商品分类表中删除分类为5的信息

delete from category where id=5;//删除失败
首先要删除商品表product中num为5的所有商品，再执行此行代码才可删除成功
即：
	delete from product where num=5;
   	delete from category where id=5;

• 建数据库原则：

  ? 通常情况下，一个项目/应用建一个数据库

• 多表之间的建表原则：

  • 一对多：如商品分类和具体商品

  ? 建表原则：在多的一方添加一个外键指向一的一方的主键

  • 多对多：如学生和课程(一个学生可以选多门课程，一门课程也可以被多名学生选择)

  ? 建表原则：建一张中间表，将多对多的关系，拆分成一对多的关系，中间表至少要有两个外键，分别指向原来的那两张表

  • 一对一：如居民和身份证

    建表原则：

    ? a. 将一对一的情况当作多对一，在任意一张表添加外键，这个外键要唯一，指向另外一张表

    ? b. 直接将两张表合并成一张表

    ? c. 将两张表的主键建立连接，让两张表的主键相等

    实际用途：拆表操作

    ? 如相亲网站：

    ? 个人信息：姓名，性别，年龄，身高，体重，居住地，兴趣爱好，年收入，择偶目标

    ? 拆表操作：将个人的常用信息和不常用信息拆分成两张表，减少表的臃肿

• 网上商城表实例分析

  ? 用户表与订单表关系：一对多，用户表1，订单表n；

  ? 订单表与商品表关系：多对多（因为一个订单可以有多种商品，而一种商品又可以被多个订单选择）；建立中间表（订单项）

  ? 订单表与中间表关系：一对多，订单表1，中间表n

  ? 商品表与中间表关系：一对多，商品表1，中间表n

  • 用户表（ID，用户名，密码，手机）

    create table user(
    	uid int primary key auto_increment,
        username varchar(10),
        password varchar(12),
        phone varchar(11)
    );
    //插入用户数据
    insert into user values(1,‘VAK‘,‘abc123‘,‘15512345678‘);
    

  • 订单表（订单号，总价，订单时间，收获地址，外链用户的ID）

    //注意order是关键字
    create table orders(
    	oid int primary key auto_increment,
        sum int,
        orderTime timestamp,
        address varchar(30),
        num int,
        foreign key(num) references user(uid)
    );
    //插入订单数据
    insert into orders values(1,72,null,"华工五山校区",1);
    insert into orders values(2,208,null,"华工大学城校区",1);
    

  • 商品分类表（分类ID，分类名称，分类描述）

    create table category(
    	cid int primary key auto_increment,
        cname varchar(8),
        cdesc varchar(20)
    );
    //插入商品分类数据
    insert into category values(null,‘手机数码‘,‘电子产品‘);
    insert into category values(null,‘鞋靴箱包‘,‘生活必备‘);
    insert into category values(null,‘香烟酒水‘,‘宴席必备‘);
    insert into category values(null,‘文具办公‘,‘办公必备‘);
    insert into category values(null,‘馋嘴零食‘,‘休闲必备‘);
    

  • 中间表：订单项（订单ID，商品ID，商品数量，总价，外链订单ID，外链商品ID）

    create table temp(
    	onum int,
        pnum int,
        foreign key(onum) references orders(oid),
        foreign key(pnum) references product(pid),
        tcount int,
        tsum double
    );
    //给1号订单添加89元的商品
    insert into temp values(1,5,6,54);
    insert into temp values(1,7,6,18);
    //给2号订单添加199元的商品
    insert into temp values(2,3,1,199);
    insert into temp values(2,6,1,9);
    

  • 商品表（商品ID，商品名称，商品价格，外链商品分类ID）

    create table product(
    	pid int primary key auto_increment,
        pname varchar(10),
        price double,
        pnum int,
        foreign key(pnum) references category(cid)
    );
    //插入商品数据
    insert into product values(null,‘小米8‘,1799,1);
    insert into product values(null,‘小米mix4‘,3999,1);
    insert into product values(null,‘特步‘,199,2);
    insert into product values(null,‘劲酒‘,19,3);
    insert into product values(null,‘酸梅汤‘,9,3);
    insert into product values(null,‘小米巨能写‘,9,4);
    insert into product values(null,‘卫龙辣条‘,3,5);
    insert into product values(null,‘乡巴佬鸡腿‘,6,5);
    

主键约束：默认不能为空，唯一

? 外键都是指向另外一张表的主键；一张表只能有一个主键

唯一约束：唯一约束里面的内容必须是唯一的，不能出现重复的情况，可以为空，可以多行数据都为空。

17. 使用商城表完成对商品信息的多表查询

? 在商城案例中,我的订单中包含很多信息.打开我的订单需要去查询表

多表查询

• 交叉连接查询（笛卡儿积）

  ? select * from product,category;

  ? 查出来的是两张表的外积，没有多大意义

  过滤出有意义的数据：

  SELECT * FROM product,category WHERE product.pnum=category.cid;

• 内连接查询

  内连接组合两张表，并基于两张表的关联关系来连接它们，需要指定表中的那些字段组成关联关系，并指定基于什么条件进行连接。

  内连接返回两张表的交集。

  写法一：SELECT * FROM product p INNER JOIN category c ON p.pnum=c.cid;

  写法二：SELECT * FROM product,category WHERE product.pnum=category.cid;

• 左外连接

  left outer join，简写为left join。左外连接，左表的记录全部显示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为null。

• 右外连接

  right outer join，简写为right join。右外连接，右表的记录全部显示出来，而左表只会显示符合搜索条件的记录，左表记录不足的地方均为null。

  SELECT * FROM product p RIGHT JOIN category c ON p.pnum=c.cid;

18.分页查询

? SELECT * FROM product LIMIT 0,3;

? param1:索引；param2:每页显示的记录数。

MySQL