MySql数据库基本操作

MySql数据库

一、MySql基本使用

1.卸载

		查找到数据库数据文件存储位置：
			在mysql安装目录下有一个my.ini文件
			datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"			
			将数据文件保存目录删除，在将mysql安装目录删除.

2.安装

		1.可以选择典型安装，默认安装在C:\Program Files (x86)\mysql目录下
		2.可以选择自定义安装  可以选择安装的目录			

3.配置

		1) 配置mysql 默认字符集 		默认latin1 等价于 ISO-8859-1 改为 utf8
		2) Include Bin Directory in Window Path 将mysql/bin 目录配置环境变量path ---- 勾选
		3) 输入超级管理root 密码  		

4.查看mysql服务

		在运行中输入   services.msc
		在使用mysql之前，要确保mysql服务开启.			
		在服务器可以通过右键属性，设置服务手动开启，这样这个服务就不会跟随电脑开机启动。
		可以在命令行上通过命令来开启与关闭服务器				
			开启  net start mysql
			关闭  net stop mysql

5.操作mysql数据库

		1.mysql默认的客户端
			默认的客户端连接的是 localhost   3306这个数据库服务器.				
		2.在命令行下操作
			1. mysql -u root -p 回车
				输入密码  root					
				这种方式，默认连接的也是localhost  3306这个数据库服务器					
				-u 代表用户
				-p 代表密码					
			2.	mysql -u root -h localhost -P 3306 -p
				输入密码 root					
				-h 代表的是服务器的主机地址
				-P 代表的是端口号

二、数据库基本操作

SQL介绍

1.SQL是什么，它的作用是什么
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统

2.SQL分类 
	SQL一共分成四类，如下所示：
	数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等，例如创建、删除、修改：数据库、表结构等；
	数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新，例如：增、删、改表记录；
	数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户；
	数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。
大家可能会对DDL和DML分的不太清楚，DDL是对数据库或表的结构进行操作，而DML是对表的记录进行操作(增、删、改)。以上四个分类，我们以后最常用的就是DDL、DML、DQL，其中，DQL是难点。

3.SQL语法
	SQL的语法有以下要求：
	SQL语句可以单行或多行书写，以分号结尾；
	可使用空格和缩进来增强语句的可读性；
	MySQL数据库的SQL语句不区分大小写，建议使用大写，例如：SELECT * FROM user。

创建数据库

1.使用默认的字符集与校对 
	create database 数据库名;
2.可以指定数据库字符		
	create database 数据库名  CHARACTER  set gbk;
3.可以指定数据库字符与校对
	create database 数据库名 character set 编码 COLLATE 校对

练习:
			创建一个名称为mydb1的数据库。
			create database mydb1;
			创建一个使用utf8字符集的mydb2数据库。
			create database mydb2 character set utf8;
			创建一个使用utf8字符集，并带校对规则的mydb3数据库。
			create database mydb3 character set utf8 collate utf8_general_ci;

查看数据库

	查看当前数据库服务器下所有数据库
	show databases;				
	查看创建数据库的语句
	show create database 数据库名;		
			
练习:
	查看当前数据库服务器中的所有数据库
			show databases;
	查看前面创建的mydb2数据库的定义信息
			show create database mydb2;

修改数据库

	修改数据库操作我们很少使用，了解一下语法就可以。
	修改数据库的编码集
	alter database 数据库名称  character set 编码 collate 校对
	
练习:
		查看服务器中的数据库，并把其中某一个库的字符集修改为utf8;
		show create database mydb2;
		alter database mydb2 character set utf8 collate utf8_general_ci;

删除数据库

	删除数据库
	drop database 数据库名
练习:
	删除前面创建的mydb1数据库
	drop database mydb1;

其它关于库的操作

	1.切换数据库
	use 数据库名称
	2.查看当前所使用的事数据库
	select database();

对于数据库的操作，我们重点掌握创建，切换与删除数据库

		 创建:create database 数据库名称;
		 切换:use 数据库名
		 删除:drop database 数据库名

三、mysql中的数据类型

MySQL与Java一样，也有数据类型，MySQL中的数据类型主要应用在列上。
我们在定义表的时候对每一列的定义有列名和列类型即数据类型。
以下是MySQL数据库常用的数据类型:

	类型		描述
	int			整型，和java中的int类型一样；
	double		浮点型，例如double(5,2)表示该列存放的数据最多是5位，而且其中必须有2位小数，它的最大值是：999.99；
	decimal		浮点型，用于保存对准确精度有重要要求的值，例如与金钱有关的数据。
	char		固定长度字符串类型，如果没有指定长度，默认长度是255，如果存储的字符没有达到指定长度，mysql将会在其后面用空格补足到指定长度；
	varchar		可变长度字符串类型，它的长度可以由我们自己指定，它能保存数据长度的最大值是65535，如果存储的字符没有达到指定的长度，不会补足到指定长度；
	tinytext	可变长度字符串类型，其存储范围是28-1B；
	text		也是可变长度字符串类型，其存储范围是216-1B；
	mediumtext	也是可变长度字符串类型，其存储范围是224-1B；
	longtext	也是可变长度字符串类型，其存储范围是232-1B；
	tinyblob	是可变长度二进制类型，其存储范围是28-1B；
	blob		是可变长度二进制类型，其存储范围是216-1B；
	mediumblob	也是可变长度二进制类型，其存储范围是224-1B；
	longblob	也是可变长度二进制类型，其存储范围是232-1B；
	date		日期类型，格式为yyyy-MM-dd，只有年月日，没有时分秒；
	time		时间类型，格式为hh:mm:ss，只有时分秒，没有年月日；
	timestamp	时间戳类型，格式为：yyyy-MM:dd hh:mm:ss，年月日，时分秒都有。

补充：
char与varchar区别?

	char是一个定长字符串.指定长度不会随着内容的不足而改变
	varchar是一个可变长度的字符串，它根据信息自动改变长度.(只在字符串长度小于指定长度情况下)

四、创建表与约束

1.创建表

1.创建表—使用默认的字符集与校对

			create table 表名(
				字段1 类型，
				字段2 类型,
				.....
			);

2.创建表–指定字符集与校对

			create table 表名(
				字段1 类型，
				字段2 类型,
				.....
			) character set 字符集  collate 校对;

表名后面的内容需要使用“()”括起来，里面的内容是表结构，由列名和列类型组成，列名与列类型之间用空格隔开，每一列之间用逗号隔开，最后一列后面不需要加逗号，语句结束后需要加“；”。

2.约束

它是用于限定表中字段。
注意:数据库中一共有六种约束，而mysql只支持五种

主键约束

	主键是用于标识当前记录的字段。它的特点是非空，唯一。		
	在开发中一般情况下主键是不具备任何意义，只是用于标识当前记录。
	创建主键:
		1.在创建表时，在字段后面加上  primary key.
		create table tablename(						
			id int primary key,
			.......
		)
		2.也可以表创建的最后来指定主键																create table tablename(						
			id int，
			.......
			primary key(id)
)
		3.删除主键：alter table tablename drop primary key ;

唯一约束

	某一列的值不为重复可以使用唯一约束.	unique  

非空约束

	not null

默认值约束

	default 值

外键约束

自动增长  auto_increment					
扩展:
	mysql : auto_increment 自动增长
	sqlserver: identity(1,1)
	oracle:sequence序列

五、表操作-查看修改删除表

1.查看表

查看表结构
		desc 表名;
查看当前数据库下所有表
		show tables;				
.查看表的字符编码集
		show create table 表名;

2.修改表

修改表的结构语法格式:alter table 表名 关键字 ….;					
	关键字有以下几个:
		1.add----添加列操作   alter table 表名  add 列名 类型;
		2.modify--修改列的类型 alter table 表名  modify 列名  类型;
		3.drop --删除列  alter table 表名  drop 列名.
		4.change--修改列名称  alter table 表名 change 旧列名  新列名  类型;
	
修改表的名称
		rename table 旧表名 to 新表名;
					
修改表的字符编码集
		alter table 表名 character set 字符集;
			
练习:
在上面员工表的基本上增加一个image列。
	alter table user add image blob;
修改job列，使其长度为60。
	alter table user modify job varchar(60);
删除gender列。
	alter table user drop gender;
表名改为employee02。
	rename table employee01 to employee02;
修改表的字符集为utf8
	alter table employee character set utf8;
列名name修改为username
	alter table employee change column name username varchar(20);
	alter table employee change name username varchar(20);

3.删除表

	drop table 表名;

【重点总结】

1.mysql中数据类型：我们使用java的数据类型与mysql数据类型对比总结
		基本类型
				byte-------TINYINT
				short------SMALLINT
				int -------INT
				long-------BIGINT					
				float------float
				double-----double					
				char
				boolean----bool boolean 一般情况下直接使用数值类型来描述,直接使用int
			字符串
				String   char  varchar
				数据库中字符必须指定长度			
				
			时间日期
				java.sql.Date------Date datetime
				java.sql.Time------Time
				java.sql.Timestamp----Timestamp
2.关于创建表的格式
		Create table 表名(
			字段 类型,
			….
)
3.关于约束
		主键约束  primary key
		非空 not null
		唯一 unique
		自动增长 auto_increment
4.怎样给表中一个字段添加主键
		1.可以在字段后面直接使用primary key
		2.可以在表创建最后通过 primar key(字段名称)

六、记录操作

1、记录操作-insert操作

		插入所有列值
			insert into 表名(列名1,列名2, ...) values(列值1, 列值2, ...);
			表名后面是当前表中所有字段名称	
		插入部分列值
			:	insert into 表名(列名1,列名2) values(列值1, 列值2);
				表名后面是当前表中部分字段名称
		不指定列名
			:	insert into 表名 values(列值1, 列值2, ...);
			没有给出要插入的列，那么表示插入所有列；
			值的个数必须是该表的列的个数；
			值的顺序，必须与表创建时给出的列的顺序相同。

		插入操作注意事项
			插入的数据应与字段的数据类型相同
			数据的大小应该在列的长度范围内
			在values中列出的数据位置必须与被加入的列的排列位置相对应。
			.除了数值类型外，其它的字段类型的值必须使用引号引起。
			如果要插入空值，可以不写字段，或者插入 null.					
			注意:对于自动增长的列在操作时，直接插入null值即可.	

2、记录操作-update操作

		下面语句会将指定字段的值全部修改
			update 表名 set 字段名称=值;
		如果要修改多个字段
			update 表名 set 字段1名称=值 ,字段2名称=值,...;
				
		以上方式，进行修改，会将表中这个字段所有值都修改.
		在实际开发中，对于修改操作，都是有条件修改.
			update 表名 set 字段=值 ,... where 条件.
		练习:修改user表中信息
				1.所有人的password修改为”456”
				2.将名字叫tom的用户email修改为tom@itcast.cn
				3.将tom的gender修改为”female”,并且introduce修改为”good girl”

3、记录操作-delete操作

			delete操作是删除数据.
				格式  	delete from 表名.
					这会将表中所有数据删除。在开发一般情况下有条件删除
						delete from 表名 where 条件.

				关于删除表与删除表数据区别:
					
					1.删除表（表结构也没了）   
						drop table 表名
					2.删除表中所有记录	
						1.delete from 表名
						2.truncate table 表名
						
				关于delete 与truncate的区别?(笔试题)
					
					1.delete是一行一行删除   truncate是将表结构销毁，在重新创建表结构.
						如果数据比较多，truncate的性能高。
					
					2.delete是dml语句  truncate dcl语句		
						delete是受事务控制. 可以回滚数据.
						truncate是不受事务控制. 不能回滚.
						
		练习:
			删除表中所有数据
			Delete from user;
			Truncate table user;
			删除表中名字叫tom数据
			Delete from user where username=’tom’

4、记录操作-编码问题

		下面我们向user表中插入一条记录
		insert into user(username,password) values(‘张三’,’123’);		
		当执行这条sql时，会报错
		ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'username' at row 1
		 
			 原因:
				因为mysql的客户端编码的问题我们的是utf8,而系统的cmd窗口编码是gbk
			 解决方案（临时解决方案）:
				修改mysql客户端编码就可以。
				
				查看所有mysql的编码
				show variables like 'character%'; 
				与客户端有关的编码设置:
				client connetion result 和客户端相关 、
			   	database server system 和服务器端相关 
				
				将客户端编码修改为gbk.
				set character_set_results=gbk; / set names gbk;
				以上操作，只针对当前窗口有效果，如果关闭了服务器，失效。
				
			如果想要永久修改，通过以下方式:
				 在mysql安装目录下有my.ini文件
				 
					default-character-set=gbk 客户端编码设置						
					character-set-server=utf8 服务器端编码设置
				注意:修改完成配置文件，重启服务

5、记录操作-select基本查询

		查询指定列
			 select 字段 from 表名;
				查询指定字段信息,如果要查询多个字段
			 select 字段1,字段2,...from 表名;						
		查询所有列
			select * from 表名;
				查询表中所有字段.
				注意:使用"*"在练习，学习过程中可以使用，在实际开发中，不建议使用。		
		去掉重复记录
			select distinct 字段 from 表名;				
			distinct它的作用是去除重复.
		使用别名				
			使用as 别名可以给表中的字段，表设置别名.
		在查询中可以直接对列进行运算
			我们在sql操作中，可以直接对列进行运算。
		ifnull函数使用
		在对数值类型的列做运算的时候，如果做运算的列的值为null的时，运算结果都为null，为了解决这个问题可以使用ifnull函数


练习
		创建表products
					CREATE TABLE `products` (
					  `id` INT AUTO_INCREMENT,
					  `name` VARCHAR(40) ,
					  `price` DOUBLE ,
					  `category` VARCHAR(40) ,
					  `pnum` INT(11) ,
					  `description` VARCHAR(255) ,
					  PRIMARY KEY (`id`)
					)			 
 		向表中插入数据
		INSERT INTO `products` VALUES (NULL,'感悟',100,'励志',100,'一次心灵的鸡汤');
		INSERT INTO `products` VALUES(NULL,'java与模式',67,'计算机',200,'让你的编程，从些不一样');
		INSERT INTO `products` VALUES(NULL,'java并发编程实战',190,'计算机',49,'实战大于理论');
		INSERT INTO `products` VALUES(NULL,'设计模式解析',88,'计算机',86,'头脑风暴');
		INSERT INTO `products` VALUES(NULL,'搭地铁游上海',28,'生活百科',120,'一次不一样的旅行');
		INSERT INTO `products` VALUES(NULL,'时空穿行',65,'科技',87,'这是一本好书');
		INSERT INTO `products` VALUES(NULL,'中国国家地理',45,'生活百科',100,'了解你生活的国家');
		INSERT INTO `products` VALUES(NULL,'欧洲', NULL,'生活',200,'你梦中向往的地方');
		INSERT INTO `products` VALUES(NULL,'网管员必备宝典',35,'计算机',120,'上网新手必备书籍');
		1.查询出所有商品信息
			Select * from products;
		2.查询出所有商品的名称，价格，类别及数量信息
			Select name,price,category,pnum from products;
		3.查询出所有的商品类别
			Select distinct category from products;
		4.查询出所有商品的名称及价格，将所有商品价格加10
			Select name,price+10 as 价格 from products
		5.查询出每一个商品的总价及名称
			Select name,price+10 as 价格 from products

6、记录操作-where子句

		我们在开发中，使用select操作，一般都是有条件查询，那以我们介绍一下关于where子句的使用
		格式 :select 字段  from 表名  where 条件;				
			where条件种类:					
				1.比较运算符
					> >= < <= = !=(<>)
				2.逻辑运算符
					and  or not						
				3.between ...and
					相当于 >= and <=						
					注意:between 后面的值必须是小值 and后面的是大值						
				4.in
					可以比较多个值						
				5.like 
					模糊查询
					通配符使用:
						1.% 匹配多个
						2._ 匹配一个							
				6.null值操作
					 is null; 判断为空 
					 is not null; 判断不为空
		练习:
		1.查询所有计算机类商品信息
			SELECT * FROM products WHERE category="计算机";
		2.查询出商品价格大于90的商品信息
			SELECT * FROM products WHERE price>90;
		3.查询出商品总价大于10000的商品信息
			SELECT * FROM products WHERE (price*pnum)>10000
		4.查询出价格在100-200之间的商品信息
			SELECT * FROM products WHERE price>=100 AND price<=200;
			SELECT * FROM products WHERE price BETWEEN 100 AND 200;
		5.查询出商品价格是65,100或190的商品信息
			SELECT * FROM products WHERE price=65 OR price=100 OR price=190;
			SELECT * FROM products WHERE price IN(65,100,190);
		6.查询出商品的名称中包含java的商品信息。
			SELECT * FROM products WHERE NAME LIKE "%java%";
		7.查询出书名是两个字的商品信息
			SELECT * FROM products WHERE NAME LIKE "__";
		8.查询出商品价格不为null商品信息
			SELECT * FROM products WHERE price IS NOT NULL;
			SELECT * FROM products WHERE NOT price IS NULL;

7、记录操作-order by排序

	在开发中，我们从数据库中查询出的数据经常需要根据某些字段进行排序，可以使用order by关键字,后面跟的就是要排序的列
	order by 子句是select的最后的一个子句。				
	asc 升序 (默认)
	desc 降序
	
	练习:
	1.查询出所有商品，并根据价格进行升序排序
	SELECT * FROM products ORDER BY price ASC;
	2.查询出所有商品，根据数量进行升序排列，如果数量相同，根据价格进行降序排列
	SELECT * FROM products ORDER BY pnum ASC, price DESC;

8、记录操作-聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
今天我们学习如下五个聚合函数：
		count：统计指定列不为NULL的记录行数；
		sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
		max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
		min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
		avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
练习:
1.统计商品表中共有多少条记录
	SELECT COUNT(*) FROM products;
2.统计商品表中价格大于50的有多少条记录
	SELECT COUNT(*) FROM products WHERE price>50;
3.统计有多少商品
	SELECT SUM(pnum) FROM products;
4.统计所有商品的总价值
	SELECT SUM(pnum*price) FROM products;
	SELECT SUM(pnum*IFNULL(price,0)) FROM products
5.统计所有商品的平均价格
	SELECT SUM(pnum*price)/SUM(pnum) FROM products;
6.统计出记录中price的平均值
	SELECT AVG(price) FROM products;
7.统计出商品表中price最大与最小值
	SELECT MAX(price),MIN(price) FROM products;
	SELECT MAX(price),MIN(IFNULL(price,0)) FROM products;

9、记录操作-分组操作

分组查询是指使用group by字句对查询信息进行分组,例如：我们要统计出products表中所有分类商品的总数量,这时就需要使用group by 来对products表中的商品根据category进行分组操作.
分组后我们在对每一组数据进行统计。
分组操作中的having子名是用于在分组后对数据进行过滤的，作用类似于where条件。

笔试题: having与where的区别:				
				1.having是在分组后对数据进行过滤.
				  where是在分组前对数据进行过滤
				  
				2.having后面可以使用分组函数(统计函数)
                  where后面不可以使用分组函数。
练习:
1.对商品分类别统计，求出每一种类商品的总数量
	SELECT category,SUM(pnum) FROM products GROUP BY category;
2.对商品分类别统计，求出每一种类商品的总数量，数量要大于100
	SELECT category,SUM(pnum) FROM products GROUP BY category HAVING SUM(pnum)>100

记录操作总结

综合我们学习的查询相关关键字：select，from，where，group by，having，order by；它们的执行顺序是如下：
from：首先执行from，找到要查询的emp表；
where：判断条件，筛选出工资大于15000的所有记录；
group by：根据以上关键字执行的结果上对记录按照指定列进行分组
having：对分组后的信息进行筛选；
select：选择所需要的列信息；
order by：对查询信息进行排序。
在SQL语言中，第一个被处理的子句是from字句，尽管select字句最先出现，但是几乎总是最后被处理。

【总结】

添加:insert into 表名(字段1,字段2,…) values(值1,值2,…);
修改:update 表名 set 字段1=值1,字段2=值2 where 条件
删除:delete from 表名 where 条件
truncate table 表名
查询:select 字段 from 表名 where 条件 group by 字段 having 条件 order by 字段.

关于sql中的条件

	1.比较运算符 > < >= <= = != <>
	2.逻辑运算符 and or not
	3.between and
	4.in
	5.like
	6.is null

聚合函数

	count
	sum
	avg
	max
	min

分组

	Group by   having