2-26.1 MySQL基础2

Mysql客户端工具安装

Navicat

Navicat是一套快速、可靠并价格便宜的数据库管理工具

SQLyog

SQLyog实际上对MysQL支持最多的数据库管理工具

PS：DataBase IDEA中集成的插件，直接操作数据库【适合简单的对数据库进行CRUD】

数据库插入、更新和删除（DML）

DML是对**【表中数据进行操作】**，千万别和DDL弄混

DML的关键字**【insert、update、delete】**

插入数据操作【insert】

语法：
    insert into 表名(列名1，列名2.....)values(列值1，列值2....)
注意:
	1.列名和列值【个数、顺序、数据类型必须是相同且一致】
	2.可以将列名看做时java方法中的形参名字，把列值看做时对形参的赋值
	3.值不能操作列所使用数类型范围，允许没有值但是需要使用【null】
	4.字符串和日期赋值【单引号】引起来
	PS: 以后使用jdbc进行数据库链接操作的时候需要注意取出值是null

修改表中数据【update】

语法：
    update 表名 set 列名=新列值，列名=新列值... [where 条件限制]
注意：
	1.如果没有条件限制，是可以不写where子句  ==》 全体
	2.如果需要限制条件修改，可以在where子句后面添加 条件==》【boolean表达式】 ==》单一
	例如： 将小白的年龄修改为19岁
	update t_user set age = 19 where name = '小白';

删除表中数据【delete】

delete from 表名 [where 条件限制]
注意：
	1.如果没有条件限制，是可以不写where子句  ==》 全体
	2.如果需要限制条件删除，可以在where子句后面添加 条件==》【boolean表达式】 ==》单一列

需求：创建一个数据库mydb1字符编码集为utf8,

​ 在这个数据库中创建一个表emp(id,name,gender,birthday,salary,entry_date【入职日期】，resume【描述】)

向表中插入数据【生日和入职如期是date类型】

创建数据库

create database mydb1 character set utf8;

CREATE TABLE `t_user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(60) CHARACTER SET gbk DEFAULT NULL,
  `age` int(11) DEFAULT NULL 
)CHARSET=utf8;   # 指定表的编码集
PS:因为直接修改了 my.ini 文件所以可以直接创建表不用指定编码集
   如果在创建数据库的时候指定了编码集，那么库中所有表都是使用 数据库的默认编码集
   
   CREATE TABLE emp(
	id INT,
	name VARCHAR(100),
	gender VARCHAR(10),
	birthday DATE,
	salary FLOAT(10,3),
  entry_date DATE,
  resume text
);  

CREATE TABLE emp(
	id INT,
	name VARCHAR(100),
	gender VARCHAR(10),
	birthday DATE,
	salary FLOAT(10,3),
  entry_date DATE,
  resume text
);  

#向表中插入数据  
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume) VALUES(1,'zhangsan','男','1995-1-1',100000,'2000-10-1','张三是个好孩子');
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume) VALUES(2,'lisi','女','1990-2-14',5000,'1992-10-1','李四是个好孩子');
#更新信息
#将所有员工薪水统一调整为100
UPDATE emp SET salary = 100;
#将lisi的薪水调整为1
UPDATE emp SET salary = 1 WHERE name = 'lisi';
#将lisi薪水修改为2，性别修改为男
UPDATE emp SET salary = 2,gender='男' WHERE name = 'lisi';

#lisi离职，删除这条记录
DELETE FROM emp where name = 'lisi';

#慎重！慎重！在慎重！【清空数据，但是表结构还在】
DELETE FROM emp;

#它和delete类似删除表，但是这个删除相当于是drop掉,然后会创建一张同样的新表，数据是无法找回
truncate table emp；



#查询表中数据
SELECT * FROM emp;

数据库查询语言【DQL 重要，非常重要，反正就是重要！！！】

数据库执行DQL语句**【不会对数据进行改变】，而是让数据库发送结果集到客户端【发送这个结果集是一长虚拟表】**

整个查询语句中核心关键字**【select】**

结合select关键字可以的到一些组合语句，这些语句组合可以得到不同查询效果

select 要查询的列的名称 from 要查询的那张表的名字 where 对查询条件的限制 group by 对查询结果进行分组 having 分组的条件进行限制  order by 对查询数据进行排序  limit 限定显示结果集

需要先将product.sql导入到数据库中，使用这个表中中数据进行操作

主键ID	商品名称	商品分类编号   零售价格	  供应商     品牌       折扣        成本价格

CREATE TABLE `product` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `productName` varchar(50) DEFAULT NULL,
  `dir_id` bigint(11) DEFAULT NULL,
  `salePrice` double(10,2) DEFAULT NULL,
  `supplier` varchar(50) DEFAULT NULL,
  `brand` varchar(50) DEFAULT NULL,
  `cutoff` double(2,2) DEFAULT NULL,
  `costPrice` double(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

简单查询

#DQL语句的使用
/*
简单查询
查询整张表的内容  select * from 表名;
PS： * 代表的所有内容
查询表中某列或多个列   
select  列名  from 表名;
select  列名1,列名2..... from 表名;
PS:多列查询使用【,】分隔
*/
#查询整张表达信息
select * from product;
#查询货品ID、货品名称
select id,productName from product;
#PS：允许使用反引号【`】对列名 和 表名进行使用 区分关键字, 不要使用关键字作为列名或表名
#select `salePrice` from `product`;

#避免重复值【去重】 distinct
#一般使用在 操作列的位置
#查询商品分类编号
select dir_id from product;
#去除重复汉字
select DISTINCT dir_id from product;
#多个列进行去重，只有列数据完全相同，此时才会去重重复
SELECT DISTINCT dir_id,salePrice from product

/*
在SQL语句中使用算数运算符
对整数或小数的数据类型可以使用【+ - * /】这四个运算符进行计算 组成一个算数表达式
对date【日期】类型可以使用【+ -】 进行计算
原则：
1.先算乘除再算加减
2.同级别的运算符顺序是从左向右
3.表达式使用“小括号”,可以强行改变优先级

特别注意： 在SQL语句中 == 并不是判断是否相等
					 比较是否相等 此时需要使用 =
					 如果需要给某一个列修改值 也需要使用 =

*/
#查询所有货品ID，商品名称和 批发价格（批发价格= 零售*折扣）
select id,productName,salePrice * cutoff from product;
#查询所有货品ID，货品名称和个进50个成本价格
select id,productName,costPrice,costPrice*50 from product;
#查询所有货品ID，货品名称和个进50个成本价格,每一个鼠标都要+1运费
select id,productName,costPrice,(costPrice+1)*50 from product;

/*
设置列的别名【主要用于虚拟表的列名使用】
例如现阶段查询结果salePrice * cutoff 批发价格，列的展示名字是salePrice * cutoff
对这个列名字进行精简 sc ，这种情况下就可以使用别名

例如多表联合查询【最少2张表】，启动一个表查询出来列需要另外一个表中使用，直接使用列名，不方便也不好记忆，可以将这个列修改别名，在下一张表中使用
遵守规则：
1.添加别名不会修改原有表列的名字，只会在查询表中展示
2.使用别名的是有，可以使用字符【大小写敏感】，尽量不要使用中文
3.在使用别名的时候，一定要注意语句的执行顺序，才可以正确使用别名

需要使用到关键字在  as
*/
#查询所有货品ID，货品名称和个进50个成本价格,每一个鼠标都要+1运费
select id,productName,costPrice,(costPrice+1)*50 AS totalCP from product;
#MySQL中允许省略as关键，但是这个别名要紧跟 列名的后面
select id,productName,costPrice,(costPrice+1)*50 totalCP from product;

where子句

#1.条件过滤
/*
添加了查询条件的过滤
语法：
     select  列名  from 表名 where 限制查询条件;
		 
PS：限制查询条件 可以看做时 boolean表达

比较运算符
= 【等于和赋值】	>【大于】	 >=【大于等于】  <【小于】  <=【小于等于】  [!= 、<>]【不等于】
*/
#查询货品零售讲个为 119的所有商品信息
select * from product where  salePrice = 119;
#查询货品名称不是‘罗技G9X’的所有商品信息
select * from product where  productName != '罗技M305';
select * from product where  productName <> '罗技M310';
#查询货品名称，零售价格小于等于200的货品
select  productName,salePrice from product where salePrice <= 200;
# where子句的后面可以添加 计算表达是进行判断
select productName,salePrice*cutoff 批发价格 from product  where salePrice*cutoff >350;

语句执行过程【必须要记住】

1.from子句【确定从那张表开始查询】 
	--》2.where子句【从表中筛选出符合条件的数据】
		--》3.select子句【从筛选出来的数据结果集中获取出对应列的数据】

逻辑运算符

/*
逻辑运算符
AND 【并且 所有都为true才为true】	OR 【或者 只要有一个条件为true就为true】 
NOT 【取非 false为true true为false】
*/
#查询货品ID，货品名称，批发价格在300~400之间的货品
select id,productName, salePrice * cutoff 批发价格 from product where salePrice * cutoff >=300 AND salePrice * cutoff <=400;
#查询或ID,商品名称，分类编号不为2的所有商品
#方式1：
select id,productName,dir_id FROM product where dir_id != 2;
#方式2：
select id,productName,dir_id FROM product where NOT dir_id = 2; #等价于java  !(dir_id == 2)
#查询所有商品信息，商品编号为2或为4的商品
select * from product  where dir_id =2 or dir_id = 4;
/*这些运算符存在一个计算优先级【先执行NOT然后在执行AND最后是OR】，如果使用小括号会修改优先级*/

Between…And和In 运算符

#范围查询
/*
主要作用是对数值类型或时间日期做一个范围查询，效果和AND是一样
语法:
   select * from 表名 where 列名 between 最小范围 and 最大范围; 
*/
#查询货品ID，货品名称，零售价格在300~400之间的货品
select id,productName, salePrice  from product where salePrice between 300 and 400;
#between and 还可以和NOT一起使用 相当于是取非值  
#查询货品ID，货品名称，零售价格不在300~400之间的货品
select id,productName, salePrice  from product where NOT salePrice between 300 and 400;

/* in运算符[相当于获取小括号中的某个一个值]  跟OR一样
select * from 表名 where 列名 in(值1，值2....)
*/
#查询所有商品信息，商品编号为2或为4的商品
select * from product  where dir_id in(2,4);
#in运算符可以和NOT一起使用，相当于取非
select * from product  where NOT dir_id in(2,4);#当前商品编号不再2和4

判断为列null和like查询

/*
判断列中值是否为null
格式：
select  * from  表名 where 列名 is null
判断不为null  ---》 is not  null 剩余语法和上面一样
*/

/*
模糊查询
使用like 关键字可以进行查询匹配，查询条件可以是包含文件或数字
通用匹配符号
【%】 通配符，可以表示零个或多个任意字符
【_】 通配符，可以表示一个字符

%XXX条件  代表以什么开头   XXX条件%  代表是以什么结尾
*/
#查询货品id，货品名称，货品名称匹配【罗技M】的所有商品
select id,productName from product where productName like '罗技M%';
#查询货品id，货品名称，货品名称匹配罗技M1并且有两个字符 或者  零售价格 大于等于200
select id,productName,salePrice from product where productName like '罗技M1__' or salePrice >= 200;

case…when语法

/*
case when语句  类似于Java中switch-case
select 列名
case
	 when 条件1  then 结果1
	 when 条件2  then 结果2
	 when 条件3  then 结果3
	 ......
	 else 结果4
end	 
	 
PS： 如果使用case when结构就建议使用 as【别名】 当做类名输出	 
*/
#查询 商品ID，商品名称，零售价格，根据零售价格给与不同等级划分
select id, productName,salePrice,
case
WHEN salePrice >= 1000 THEN 'A'
WHEN salePrice >= 500 AND salePrice<1000 THEN 'B'
WHEN salePrice >= 100 AND salePrice<500 THEN 'C'
else 'D'
END AS '罗技LEVEL'
from product;