MySql课堂笔记

闲来无事、看了下之前小朴朴（朴乾老师）的课堂笔记，很不错。觉得虽然现在做的是前端和android 但是JavaEE还是得复习复习。故发上来方便自己查阅复习。

一、数据库
1.mysql 数据库的安装与配置

2.SQL语言

二、SQL
1.数据库
	（1）创建数据库
		CREATE  DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
		create_specification:
			[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

		~创建一个名称为mydb1的数据库。
			CREATE DATABASE mydb1;
		~创建一个使用gbk字符集的mydb2数据库。
			create database mydb2 character set gbk;
		~创建一个使用utf8字符集，并带校对规则的mydb3数据库。
			create database mydb3 character set utf8 collate utf8_bin; 
	(2)查看数据库
		显示数据库语句：
			SHOW DATABASES;
		显示数据库创建语句：
			SHOW CREATE DATABASE db_name;

		~查看当前数据库服务器中的所有数据库 
			show databases;
		~查看前面创建的mydb2数据库的定义信息
			show create database mydb2;

	(3)修改数据库
		ALTER DATABASE [IF NOT EXISTS] db_name [alter_specification [, alter_specification] ...] 
		alter_specification:
			[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
		~查看服务器中的数据库，并把其中某一个库的字符集修改为utf8;
			alter database mydb2 character set utf8;
	(4)删除数据库
		DROP DATABASE  [IF EXISTS]  db_name 
 		~删除前面创建的mydb1数据库 
 			drop database mydb1;
 	(5)使用数据库
 		use db_name;进入某一个数据库，后续的操作都是对这个数据库的操作
 		select database(); 查看当前所选的数据库
2.表
	(1)创建表
		CREATE TABLE table_name
		(
			field1  datatype,
			field2  datatype,
			field3  datatype,
		)character set 字符集 collate 校对规则
		field：指定列名　datatype：指定列类型

		~创建一个员工表employee
			create table employee(
				id int primary key auto_increment,
				name varchar(20) unique,
				gender bit not null,
				birthday date,
				entry_date date,
				job varchar(100),
				salary double,
				resume text
			);

		约束：
			primary key 主键约束 auto_increment自动增长

			unique 唯一约束 

			not null 非空约束

			外键约束
	(2)查看表
		查看表结构：
			desc tabName
		查看当前所有表：
			show tables
		查看当前数据库表建表语句 
			show create table tabName;
	(3)修改表
		ALTER TABLE table ADD/MODIFY/DROP/change/character set (column datatype [DEFAULT expr][, column datatype]...);
		修改表的名称：rename table 表名 to 新表名;
		~在上面员工表的基本上增加一个image列。
			alter table employee add image blob;
		~修改job列，使其长度为60。
			alter table employee modify job varchar(60);
		~删除gender列。
			alter table employee drop gender;
		~表名改为user。
			rename table employee to user;
		~修改表的字符集为utf8
			alter table user character set gbk; 
		~列名name修改为username	
			alter table user change name username varchar(20);
	(4)删除表
		drop table tabName;
		~删除表
			drop table user;
		
3.表记录(CRUD)
	(1)INSERT
		INSERT INTO	table [(column [, column...])] 
		VALUES (value [, value...]);
		~使用insert语句向表中插入三个员工的信息
			insert into employee (id,name,gender,birthday,entry_date,job,salary,resume)
			values (null,'张飞',0,'1990-09-09','1991-01-01','打手',998.0,'真的很能打。。。');
			insert into employee values (null,'关羽',1,'1990-08-08','1990-10-01','财神',9999999.0,'公司挣钱就指着他了');
			insert into employee values (null,'刘备',1,'1980-07-07','1980-07-07','ceo',10000000.0,'公司的老大，其实什么事都不做的。。。'),(null,'赵云',1,'2000-02-21','2001-01-01','保安队长',5000.0,'跟公司的ceo走的很近，贴身保镖。。。');

		mysql中乱码的原因与解决：
			客户端发送数据时使用的编码和服务器处理数据时使用的编码不相同

			set names xxxx; --- 通知服务器和当前客户端进行交互时使用的编码集

			可以通过修改服务器中的my.ini配置文件，修改服务器默认认为的客户端的编码
	(2)UPDATE
		UPDATE tab_name SET col1 = expr1 [,col2=expr2....][WHERE ...]
		~将所有员工薪水修改为5000元。
			update employee set salary=5000;
		~将姓名为’张飞’的员工薪水修改为3000元。
			update employee set salary=3000 where name = '张飞';
		~将姓名为’关于’的员工薪水修改为4000元,job改为ccc。
			update employee set salary=4000,job='ccc' where name='关羽';
		~将刘备的薪水在原有基础上增加1000元。	
			update employee set salary=salary+1000 where name='刘备';

	(3)DELETE
		delete from tbl_name [WHERE where_definition]

		~删除表中名称为’张飞’的记录。
			delete from employee where name='张飞';
		~删除表中所有记录。
			delete from employee;
		~使用truncate删除表中记录。
			truncate employee;

	(4)SELECT
		<1>.普通查询
			SELECT [DISTINCT] * | {column1, column2. column3..} FROM table;
			~查询表中所有学生的信息。
				select * from exam;
			~查询表中所有学生的姓名和对应的英语成绩。
				select name ,english from exam;
			~过滤表中重复数据。
				select distinct english from exam;
			~在所有学生分数上加10分特长分显示。
				select name ,english+10,chinese+10,math+10 from exam;
			~统计每个学生的总分。
				select name,english+chinese+math from exam;
			~使用别名表示学生总分。
				select name as 姓名,english+chinese+math as 总成绩 from exam;
				select name 姓名,english+chinese+math 总成绩 from exam;
				select name english from exam; -- 小心这有问题~！
			~查询姓名为关羽的学生成绩
				select * from exam where name='关羽';

		<2>带查询条件的查询		
			~查询英语成绩大于90分的同学
				select name,english from exam where english > 90;
			~查询总分大于230分的所有同学
				select name 姓名,english+chinese+math 总成绩 from exam where english+chinese+math>230;
			~查询英语分数在 80－100之间的同学。
				select * from exam where english between 80 and 100;
			~查询数学分数为75,76,77的同学。
				select * from exam where math in(75,76,77);
			~查询所有姓张的学生成绩。
				select * from exam where name like '张%';
				select * from exam where name like '张_';
				select * from exam where name like '张__';
			~查询数学分>70，语文分>80的同学。
				select * from exam where math>70 and chinese>80;
		<3>排序查询
			SELECT column1, column2. column3.. FROM	table order by column asc|desc;
			~对数学成绩排序后输出。
				select name ,math from exam order by math desc;
			~对总分排序按从高到低的顺序输出
				select name 姓名,math+english+chinese 总成绩 from exam order by 总成绩 desc;
			~对姓张的学生成绩排序输出
				select name 姓名,math+english+chinese 总成绩 from exam where name like '张%' order by 总成绩 desc;
				
		<4>使用聚合函数
			~1.count -- 统计符合条件的记录共有几条
				~统计一个班级共有多少学生？
					select count(*) from exam;
				~统计数学成绩大于70的学生有多少个？
					select count(*) from exam where math>70;
				~统计总分大于230的人数有多少？
					select count(name) from exam where math+english+chinese>230;
			~2.sum --Sum函数返回满足where条件的行的和
				~统计一个班级数学总成绩？
					select sum(math) from exam;	
				~统计一个班级语文、英语、数学各科的总成绩
					select sum(chinese),sum(english),sum(math) from exam;
				~统计一个班级语文、英语、数学的成绩总和
					select sum(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) 班级总分 from exam;
				~统计一个班级语文成绩平均分
					select sum(chinese)/count(name) from exam
			~3.AVG --函数返回满足where条件的一列的平均值
				~求一个班级数学平均分？
					select avg(math) from exam;
				~求一个班级总分平均分？			
				select avg(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) 班级总分 from exam;
			~4.MAX/MIN--函数返回满足where条件的一列的最大/最小值	
				~求班级最高分和最低分
				select max(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) 班  级总分 from exam;
				select min(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) 班级总分 from exam;
		<5>分组查询--GROUP BY
			~对订单表中商品归类后，显示每一类商品的总价
				select id,product,sum(price) from orders group by product;
			~查询购买了几类商品，并且每类总价大于100的商品
				select product,sum(price) from orders group by product having sum(price)>100;
			~查询单价小于100 但是总价大于100的商品。
				select product from orders where price<100 group by product having sum(price)>100;
				
				where 在分组之前进行过滤，不能使用聚合函数
				having 在分组之后进行过滤，可以使用聚合函数,having中用到的列，必须在前面使用过才可以用

		<6>子查询

============================================================================================================
4.备份数据库
	备份数据库：
		在cmd窗口中 mysqldump -u root -p db_name>c:/1.sql

	恢复数据库：
		在cmd窗口 mysql -u root -p db_name<c:/1.sql

		在mysql窗口中使用source命令恢复数据库


5.多表设计 -- 外键约束
	外键约束：明确的声明表和表之间的对应的关系列之间的关系，从而使数据库维护这种关系。当我们对数据库中的数据进行增删改操作时，数据库会检查，这种操作是否会导致违反这种约束，如果检查出来会阻止这种操作的进行，从而保证数据库中数据的正确性和完整性。
		create table dept(
			id int primary key auto_increment,
			name varchar(30)
		);
		insert into dept values(null,'财务部');
		insert into dept values(null,'人事部');
		insert into dept values(null,'销售部');
		insert into dept values(null,'科技部');

		create table emp(
			id int primary key auto_increment,
			name varchar(30),
			dept_id int,
			foreign key (dept_id) references dept(id)
		);
		insert into emp values (null,'奥巴马',1);
		insert into emp values (null,'乔峰',2);	
		insert into emp values (null,'哈利波特',3);
		insert into emp values (null,'朴乾',4);
		insert into emp values (null,'萨达姆',4);

6.多表查询
			create table dept(
				id int primary key auto_increment,
				name varchar(30)
			);
			insert into dept values(null,'财务部');
			insert into dept values(null,'人事部');
			insert into dept values(null,'销售部');
			insert into dept values(null,'科技部');

			create table emp(
				id int primary key auto_increment,
				name varchar(30),
				dept_id int
			);
			insert into emp values (null,'奥巴马',1);
			insert into emp values (null,'乔峰',2);	
			insert into emp values (null,'哈利波特',3);
			insert into emp values (null,'朴乾',5);
			insert into emp values (null,'萨达姆',5);


			笛卡尔积查询 : 是两张表相乘的结果，如果左表有m条记录 右表有n条记录，则查询出m*n条记录。但是这种查询结果中通常具有大量错误的结果。所以通常是不会使用笛卡尔积查询。
				select * from dept ,emp;
			内连接查询：查询出左边表有且右边表也有的记录
				select * from dept,emp where dept.id = emp.dept_id;
				select * from dept inner join emp on dept.id = emp.dept_id; 
			外连接查询：
				左外链接查询：在内连接的基础上，增加上左边表有而右边表没有的记录
					select * from dept left join emp on dept.id = emp.dept_id;
				右外链接查寻：在内连接的基础上，增加右边表有而左边表没有的记录
					select * from dept right join emp on dept.id = emp.dept_id;
				全外链接查询：在内链接的基础上，增加左边有而右边没有的记录和右边有左边没有的记录
					select * from dept full join emp on dept.id = emp.dept_id; ##mysql中不支持全外链接
					select * from dept left join emp on dept.id = emp.dept_id
					union
					select * from dept right join emp on dept.id = emp.dept_id; ##在mysql中可以使用一个union来模拟全外链接