MySQL(二)

MySQL(二)

1.sqlYog工具中,按tab键有提示信息

2.使用库USE myee_2203 ;

3.DML数据:数据库操作语句 :操作表的记录

-- 查询这个库中有哪些表
SHOW TABLES ;

-- 创建一张新的表学生表,id,姓名,年龄,性别,住址
CREATE TABLE student(
    id INT ,  -- 学号
    NAME VARCHAR(10), -- 姓名
    age INT,  -- 年龄
    gender VARCHAR(5), -- 性别
    address VARCHAR(50) -- 住址
) ;

-- 查询学生的表的结构
DESC student ;

插入数据

-- 给学生表中插入数据 
--  插入数据的语法1:insert into 表名 values(值1,值2,值3,值4...值n); 插入全部数据
INSERT INTO student VALUES(1,'高圆圆',42,'女','西安市') ;

-- 插入表的数据支持 :一次插入多条数据
-- insert into 表名 values(值1,值2,值3,值4...值n),(值1,值2,值3,值4...值n),(值1,值2,值3,值4...值n);
INSERT INTO student VALUES(2,'文章',35,'男','咸阳市'),
(3,'马伊琍',40,'女','上海市'),(4,'马保国',56,'男','宝鸡市') ;

-- 语法2:插入部分字段,没有插入的字段,默认值就是null, 也支持一次性插入多条数据
-- insert into 表名(字段名称1,字段名称2....) values(值1,值2...) ;
INSERT INTO student(id,NAME,age,gender) VALUES(5,'王宝强',35,'男') ;
INSERT INTO student(id,NAME,age,gender) VALUES(6,'张三丰',60,'男'),(7,'令狐冲',38,'男') ;

/*
注意事项:
	1)插入的这些值必须要和表中的字段对应上; 先后顺序保证一致!
	2)目前没有加入"数据库约束",可以插入非法数据,举例:id重复 ,
	后期需要使用数据库约束来限定用户的操作表的行为!
*/
-- 插入一个学生id为7的
INSERT INTO student VALUES(7,'张佳宁',32,'女','西安市') ;

修改数据

--  一般实际开发中修改:都是带条件修改  (推荐)
-- update 表名 set 字段名称 = 值 where 条件 ;

-- 需求:将name为张佳宁的学生id改为8
UPDATE student SET id = 8 WHERE NAME = '张佳宁' ;

-- 需求: 修改id为6的学生的姓名为 '姚笛' (id字段在实际开发中:都是非业务字段,以后唯一的)
UPDATE student SET NAME = '姚笛' WHERE id =  6 ;


-- 语法2:一次性修改多个字段
-- update 表名 set 字段名称1 = 值 ,字段名称2 =值2.... where 条件;

-- 需求:将id为6的学生 的年龄改为25,性别改为女,地址改为上海
UPDATE student SET age = 25,gender ='女',address='上海市' WHERE id = 6 ;


-- 语法3:不带条件 属于批量修改 (部分场景可以用的)
-- update 表名 set 字段名称= 值,字段名称2 =值2....
UPDATE student SET address = '鄠邑区' ;

删除数据

-- delete  from 表名 where 条件 ; 带条件删除记录 (使用的非业务字段id删除)
-- 需求:删除id为7的学生信息
DELETE FROM student WHERE id = 7 ;

-- delete from 表名 :删除全表数据
DELETE FROM student ;


-- truncate table 表名; 删除全表数据
TRUNCATE TABLE student;

/*
   面试题:
      delete from 表名 和 truncate table 表名 :两个区别?
      共同点:都是可以删除全表的记录的;
      不同点:
		delete from 表名 ; 仅仅只是将表的全部记录删除了,表还在!
它针对id(非业务字段:设置主键并且自增长),它不影响自增长主键的这个值; (数据库约束后面讲)
		truncat table 表名; 将表所有数据删除,而且还会把删除之后,
		自动创建一个张一模一样的表,影响自增主键的值!
*/

DROP TABLE student ;
SHOW TABLES ;
-- 创建一个学生表,id 加入主键(非空且唯一)和自增长(不断的自增1)约束
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT , -- id主键并且自增长
	NAME VARCHAR(20),-- 姓名
	age INT 
);
-- 一次插入4条
INSERT INTO student(NAME,age) VALUES('高圆圆',42),('张佳宁',32),('文章',35),('王宝强',38) ;

-- 自增长的id可以自己给个值
INSERT INTO student  VALUES(15,'高圆圆2',42);
INSERT INTO student(NAME,age) VALUES('姚笛',35) ;

DQL语句:(数据库查询语句)

-- DQL语句最通用的语法:查询表的全部数据
-- -- * 代表所有字段,仅仅是自己玩的时候可以用,实际开发中不能用*,需要写上全部的字段名称
SELECT * FROM student ; 




CREATE TABLE student3 ( 
	 id INT, -- 编号
	 NAME VARCHAR(20), -- 姓名
	 age INT, -- 年龄 
	 sex VARCHAR(5), -- 性别 
	 address VARCHAR(100), -- 地址 
	 math INT, -- 数学
	 english INT -- 英语
  );
 INSERT INTO student3(id,NAME,age,sex,address,math,english) 
 VALUES (1,'马云',55,'男',' 杭州',66,78),
 (2,'马化腾',45,'女','深圳',98,87),
 (3,'马景涛',55,'男','香港',56,77),
 (4,'柳岩 ',20,'女','湖南',76,65),
 (5,'柳青',20,'男','湖南',86,NULL),
 (6,'刘德华',57,'男','香港 ',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);

1)最基本的查询语句 select

-- 查询全表数据:select * from 表名;
SELECT * FROM student3 ;
-- 实际开发中查询全部字段,把字段名称全部写上
SELECT 
	id,
	NAME,
	age,
	sex,
	address,
	math,
	english

FROM 
	student3;
	
-- 查询全部字段的时候,给字段起一个别名  as '别名名称' ,as省略
SELECT 
  id AS '编号',
  NAME AS '姓名',
  age AS '年龄',
  sex AS '性别',
  address AS '地址',
  math AS '数学成绩',
  english AS '英语成绩' 
FROM
  student3 ;
-- as 可以省略的

SELECT 
  id  '编号',
  NAME  '姓名',
  age  '年龄',
  sex  '性别',
  address  '地址',
  math  '数学成绩',
  english  '英语成绩' 
FROM
  student3 ;
 -- 当表的名称比较长的时候,可以给表名起一个别名;
SELECT 
  s.`id` '学生编号',
  s.`name` '学生姓名',
  s.`age` '学生年龄',
  s.`sex` '学生性别',
  s.`address` '学生地址',
  s.`math` '数学成绩',
  s.`english` '英语成绩' 
FROM
  student3 s ;-- 起了一个别名s 
    
  -- 可以查询部分字段
 -- 需求:查询学生的姓名以及数学和英语成绩
 SELECT 
	NAME '姓名',
	math '数学成绩',
	english '英语成绩'
 FROM
     student3 ;
-- 需求:查询学生的地址信息
SELECT 

	address

FROM 
	student3;
-- 发现:字段冗余(重复度大) 字段去重      DISTINCT 后面跟上字段名称  
SELECT  DISTINCT address FROM student3;

2DQL语句之条件查询 where 关键字

 -- 2.1)使用 赋值运算符=,比较运算符 <,<=,>=,>,!=,   mysql中的不等于 <>
 -- 2.2)Java中逻辑运算符:&&,||  mysql推荐使用 and , or
 -- 2.2)针对两个范围查询: 可以使用&&,可以使用and, 也可以 "字段名称 between 值1 and 值2"
 -- 需求:查询年龄大于20岁的学生所有信息     
SELECT
	 *
FROM
	student3 
	
WHERE  
	age > 20 ;

-- 需求:查询年龄在20到30之间的学生的姓名,年龄,住址 ,数学和英语成绩信息

SELECT 
  NAME '姓名',
  age '年龄',
  address '住址',
  math '数学成绩',
  english '英语成绩' 
FROM
  student3 
WHERE age >= 20 && age <= 30 ; -- && 逻辑双与

-- mysql中使用and 并列关系
SELECT 
  NAME '姓名',
  age '年龄',
  address '住址',
  math '数学成绩',
  english '英语成绩' 
FROM
  student3 
WHERE age >= 20 AND age <= 30 ; -- and 连接


-- 使用的between 值1 and 值2
SELECT
    NAME '姓名',
    age '年龄',
    address '住址',
    math '数学成绩',
    english '英语成绩' 
FROM
    student3 
WHERE 
	age BETWEEN 20  AND 30 ;

-- 需求:查询年龄是20岁学生的所有信息
SELECT  * FROM student3 WHERE age = 20 ;

-- 需求:查询年龄不是20岁的学生的所有信息
SELECT * FROM student3 WHERE age != 20 ; -- != Java中 的用法

SELECT * FROM student3 WHERE age <> 20 ;-- mysql中的不等于 <>


-- 需求:查询年龄是18岁或者是20或者是45岁的学生的所有信息
SELECT 
	* 
FROM 
	student3
WHERE 
	age = 18 || age =20 || age = 45 ;-- Java中逻辑双或||
	
	
SELECT 
	* 
FROM 
	student3
WHERE 
	age = 18 OR age =20 OR age = 45 ; -- Mysql中的or这个表示 或(并集)

-- 上面这个格式优化为 in(值1,值2,值3..值n) ;in集合语句	
	
SELECT  
    *
FROM
    student3
WHERE 
	age IN(18,20,45) ;
	
	
-- Java语言中:去判断某个条件的内容为null ,mysql语言不支持这个格式 ==null
-- 需求:查询学生的英语成绩为null的学生所有信息
-- select * from student3 where english == null ;
-- mysql中判断某个字段为null,使用的语法是 is null 
-- 判断某个字段不为null,使用的语法是 is not null

SELECT * FROM student3 WHERE english IS NULL ;
-- 需求:查询学生的英语成绩不为null的学生的所有信息
SELECT * FROM student3 WHERE english IS NOT NULL ;	



-- 关于int类型字段求和的时候,注意: int类型的值 + null 值 = null;

-- 查询学生的姓名以及英语和数学总分
SELECT  
    NAME '姓名',
    (math+english) '总分'
FROM
    student3 ;
-- 上面这种情况:不太友好,数学成绩有值,但是结果求和是null
-- mysql提供函数 ifnull(字段名称,值);  如果字段名称是null,给一个默认值
SELECT  
    NAME '姓名',
    (math+IFNULL(english,0)) '总分'
FROM
    student3 ;
    
SHOW VARIABLES LIKE '%character%' ;

3)DQL语句之 where条件后面加入模糊查询 —关键字 like

-- select 字段列表 from 表名 where 字段名称 like '%xxx%' ;
-- %:代表任何多个字符或者某个字符   (使用居多):网站门户系统---"搜索商品" ,模糊搜索
-- _:代表某个单个字符

-- 需求:查询所有姓马的学生信息

SELECT
    *
FROM
    student3
WHERE 
    NAME LIKE   '%马%'
    
-- 查询学生姓名三个字符并且第二个字符是化的人
SELECT 
    *
FROM 	
     student3
WHERE 
     NAME  LIKE '_化_' ;
     
-- 查询姓名为三个字符的学生信息
SELECT 
    *
FROM 
	student3
WHERE 
	NAME LIKE '___' ;

4)聚合函数查询

针对int类型:
		单行单列数据
-- select  聚合函数 from 表名 where 还可以指定条件;

-- count(字段名称):查询表中记录 ,字段名称使用都是id,非业务字段
-- avg(字段名称): 查询这个字段中平均值 
-- sum(字段列表):求和函数
-- max(字段名称):最大值
-- min(字段名称):最小值

-- 需求:查询表的总记录数
-- select count(english) '总记录数' from student3; -- 前提条件使用业务字段查询,不能有null值
-- select count(ifnull(english,0)) '总记录数' from student3; 
SELECT COUNT(id) '总条数' FROM student3 ;
-- 查询数学平均分 avg(字段名称)
SELECT AVG(math) '数学平均分' FROM student3;
-- 数学和英语成绩求和的学生信息(姓名,和总分)
SELECT  
	SUM(math+IFNULL(english,0)) '总分'
FROM
        student3;
-- max()和min()
SELECT MAX(math) '数学最高分' FROM student3 ;


-- select语句嵌套select语句 --- 子查询
-- 需求:查询出学生数学成绩大于 数学平均分的学生所有信息;
-- 使用where 条件 后面带上 比较运算符...

--  Java中
-- int x =10,y ;
-- y = y+x ; 将10代入

-- 1)查询数学平均分是多少
SELECT AVG(math) FROM student3; -- 79.5000
-- 2)查询大于数学平均分的学生信息
SELECT 
	*
FROM 
	student3
WHERE 
	math > 79.5000;
	
-- 一步走
SELECT 
	*
FROM 
	student3
WHERE 
	math >  (SELECT AVG(math) FROM student3) ;
	
-- 查询数学成绩最高分的学生的姓名,年龄,地址以及数学成绩;
-- 1)查询最高分是多少
SELECT  MAX(math) FROM student3; -- 99
-- 2)查询最高分是这个成绩的学生信息
SELECT 
    NAME '姓名',
    age '年龄',
    address '地址',
    math '数学成绩'
FROM 
    student3 
 WHERE 
	math = 99 ;


-- 一步走
SELECT 
    NAME '姓名',
    age '年龄',
    address '地址',
    math '数学成绩'
FROM 
    student3 
 WHERE 
	math = 
	(SELECT  MAX(math) FROM student3) ;

5)DQL之排序查询 order by

 select 字段列表 from 表名 order by 字段名称 排序规则(asc(默认值就是升序)或者desc 降序)

UPDATE student3 SET english = 88  WHERE id = 5 ;
UPDATE student3 SET english = 94 WHERE id = 6 ;

-- 需求:按照数学成绩升序排序 单个字段
SELECT 
   *
FROM 
    student3
ORDER BY 
	math ASC ; -- 默认不写排序规则,就是asc 升序

-- 同时条件和order by,order by在where的后面
-- 需求:数学成绩大于56分的学生进行数学的降序排序
SELECT
   NAME ,
   math
FROM
    student3 
WHERE  math > 56    -- 先满足条件,才能排序!where 必须order by之前
ORDER BY math DESC ;

-- 多个字段要同时排序,首先第一个字段排序规则,然后才是第二个字段
-- 需求:数学成绩降序,英语成绩升序排序
SELECT 
    *
 FROM 
    student3 
 ORDER BY 
	math DESC , english ASC ;

SELECT * FROM student3 ;

= 94 WHERE id = 6 ;

– 需求:按照数学成绩升序排序 单个字段
SELECT
*
FROM
student3
ORDER BY
math ASC ; – 默认不写排序规则,就是asc 升序

– 同时条件和order by,order by在where的后面
– 需求:数学成绩大于56分的学生进行数学的降序排序
SELECT
NAME ,
math
FROM
student3
WHERE math > 56 – 先满足条件,才能排序!where 必须order by之前
ORDER BY math DESC ;

– 多个字段要同时排序,首先第一个字段排序规则,然后才是第二个字段
– 需求:数学成绩降序,英语成绩升序排序
SELECT
*
FROM
student3
ORDER BY
math DESC , english ASC ;

SELECT * FROM student3 ;