java EE 2019 11 18关于mysql语句

day(20)

– Mysql的注释(–空格 注释的文字)

* 注释(特殊的注释)

/* mysql的多行注释 */

USE db1;

– 创建表名 student
– id字段 name字段,年龄, 性别字段 ,分数字段,出生日期
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,-- 将id字段设置int并且设置主键自增长约束
NAME VARCHAR(20),
age INT ,
gender VARCHAR(10),
score DOUBLE(3,1),
birthday DATE
);
SHOW TABLES;
DESC student;

– DML语句:数据库的操作语句(操作表中记录数) 使用tab键提示

给表中插入数据
– varchar(最大字符数):建议字符数据使用’'括起来

– 语法1:insert into 表名(字段1,字段2,字段3…) values(值1,值2,值3…)

注意:日期date类型 默认值 (yyyy-MM-dd)

INSERT INTO student(id,NAME,age,gender,score,birthday)
VALUES(1,‘张三’,20,‘男’,99.5,‘2019-11-18’) ;

– 语法2:插入数据的时候插入部分字段(注意,没有插入数据的字段默认 null)
INSERT INTO student(id,NAME,age) VALUES(2,‘高圆圆’,39) ;

– 语法3:insert into 表名 没有跟字段,直接使用values,将所有的字段都有插入,否则报错
– 错误的 列数和值个数 不一致
– insert into student values(3,‘张三丰’,117,‘男’,‘2019-10-10’) ;
INSERT INTO student VALUES(3,‘张三丰’,117,‘男’,NULL,‘2019-10-10’) ;

– 修改表中的记录数(update语句)

语法 update 表名 set 字段1 = 值1,set 字段2=值2… where 语句; (推荐使用带条件的修改)
UPDATE student SET age = 25 WHERE id = 1 ;
– 修改表中的某个字段的时候,如果没有携带where语句,将当前表中的所有的字段进行修改
UPDATE student SET age = 28 ;-- 属于批量修改
– update语句修改多个字段的时候中间使用逗号隔开,不能再后面字段加入set
UPDATE student SET NAME = ‘张三丰2’,age = 117 WHERE id =3;

– D(Delete) 删除表

语法:delete from 表名
DELETE FROM student; – 将表中的数据全部删除
– 删除语法 truncate table 表名
TRUNCATE TABLE student;
– 两个删除的区别?
– 1)直接表中记录全部移除,针对 (自增主键约束)的id字段,不会影响该值(id=1,2,3)
– 2)truncate table 表名,也是全表数据,直接自增长主键id字段,(id=1,2,3),使用该语句删除完毕,再次插入数据,id自增长主键从1开始
DROP TABLE student ;
SHOW TABLES;

– 插入数据
INSERT INTO student VALUES(1,‘高圆圆’,39,‘女’,99.8,‘2011-10-10’) ;
INSERT INTO student(NAME,age,gender,score,birthday) VALUES(‘文章’,27,‘男’,90.5,‘2012-11-10’) ;
INSERT INTO student (NAME,age,gender,score,birthday)VALUES(‘姚笛’,23,‘女’,95.8,‘2011-10-10’) ;

ALTER TABLE student RENAME TO stu ;

– 查询当前表中的所有数据(所有记录数)
– DQL语句查询全表数据
SELECT * FROM student; – 查询全表数据

– 查询相关的
– select * from 表名
– where 条件查询
– gruop by 分组查询
– having 分组后
– limit 分页查询 (当前页码的索引值,每页显示条数) (“方言”)
– like 模糊查询
– 创建一个表 student
– 字段,id,name,age,gender,address,出生日期,数学成绩math,英语成绩english
CREATE TABLE student(
id INT, – 编号
NAME VARCHAR(20),-- 姓名
age INT,-- 年龄
gender VARCHAR(10),-- 性别
address VARCHAR(20),-- 地址
birthday DATE,-- 出生日期
math INT,-- 数学
english INT-- 英语
);

– 插入数据
INSERT INTO student VALUES(1,‘马云’,56,‘男’,‘杭州’,‘1979-10-20’,99,100) ;
INSERT INTO student VALUES(2,‘马化腾’,65,‘女’,‘香港’,‘1975-10-11’,80,87) ;
INSERT INTO student VALUES(3,‘马德’,45,‘男’,‘湖南’,‘1980-10-20’,78,89) ;
INSERT INTO student VALUES(4,‘柳岩’,46,‘女’,‘香港’,‘1979-10-20’,70,85) ;
INSERT INTO student VALUES(5,‘柳青’,34,‘男’,‘湖南’,‘1979-10-20’,89,75) ;
INSERT INTO student VALUES(6,‘刘德华’,25,‘男’,‘江西’,‘1979-10-20’,99,97) ;
INSERT INTO student VALUES(7,‘德玛西亚’,30,‘男’,‘西安’,‘1979-10-20’,65,75) ;
INSERT INTO student VALUES(8,‘德邦’,35,‘男’,‘西安’,‘1979-10-20’,87,65) ;

– 查看全表数据
– 基本查询
SELECT * FROM student ;
– 查看表中的部分字段信息 查询student表中姓名和年龄
SELECT NAME,age FROM student;
– 查询学生表中的地址信息
SELECT address FROM student;
– 查询地址的同时,去掉重复地址信息distinct(字段)
SELECT DISTINCT(address)FROM student;
– 查询表中数据的时候,可以使用as ‘别名’ (建议带上单引号),as可以省略
SELECT NAME AS ‘姓名’,age AS ‘年龄’,gender AS ‘性别’ FROM student;
– as 可以省略的
SELECT NAME ‘姓名’,age ‘年龄’ FROM student ;
– 关于多表查询时候,给表起别名 通过表的别名.字段的方式访问
SELECT
s.name,
s.age,
s.address
FROM
student AS s;

– 判断条件查询 (主要针对一些整数类型或者double等)

=,<=,<,>, <>(mysql中的不等于)
– && 或者 and 并列关系
– || 或者 or 或的关系
– 字段 between…and…
– 需求1: 查询数学成绩大于80的人的姓名
SELECT
NAME AS ‘姓名’,math AS ‘数学’
FROM student
WHERE math >=80 ;
– 需求2: 查询 英语成绩不等于100的人有哪些?
SELECT
NAME AS ‘姓名’,english AS ‘英语’
FROM student
WHERE english != 100 ; – 在mysql不建议使用Java的表达式 ,使用mysql 的特有表达式
– 推荐写法 :不等于 可以使用!= ,建议使用<>
SELECT
NAME AS ‘姓名’,english AS ‘英语’
FROM student
WHERE english <> 100 ;
– 需求3:查询数学成绩在70~80之间的人
SELECT
NAME AS ‘姓名’,math AS ‘数学’
FROM student
WHERE math>=70 && math<=80; – && java中的逻辑双与 (不建议使用Java中的)

SELECT
NAME AS ‘姓名’,math AS ‘数学’
FROM student
WHERE math>=70 AND math<=80;

– between …and 在…之间(两个端点包含的)
SELECT
NAME AS ‘姓名’,math AS ‘数学’
FROM student
WHERE math BETWEEN 70 AND 80 ;
– 查询数学和英语成绩总分>=150分的
INSERT INTO student VALUES(9,‘马德华’,40,‘女’,‘湖北’,‘1980-10-11’,80,NULL);
– 字段进行计算(前提都是数值类型的数据)
SELECT

NAME AS '姓名',math+IFNULL(english,0) AS '总分'
FROM student 
WHERE math+IFNULL(english,0)>=150;

– 如果某个字段为null值,用一个固定数值0代替
– 语法:IFNULL(字段名称,代替默认值) ,如果当前该字段名称是null,然后使用默认值替代
SELECT
NAME AS ‘姓名’,IFNULL(english,0) AS ‘英语’
FROM student ;

– 查询年龄是45的或者是56或者是34的人
SELECT
NAME AS ‘姓名’
FROM student
WHERE age = 45 || age = 56 || age =34; – 也不推荐这种方式 ,使用or

SELECT
NAME AS ‘姓名’
FROM student
WHERE age = 45 OR age = 56 OR age =34;

– in sql语句类似于集合 select 字段名称 from student in(值1,值2,值3) ;
SELECT NAME AS ‘姓名’
FROM student
WHERE age IN(45,56,34);

– 模糊查询 like
– 两个语法: %:代替任意字符
– _包含任意字符

– 需求1:查询学生表中所有姓马的人
SELECT
NAME AS ‘姓名’
FROM student
WHERE NAME LIKE ‘马%’;
– 需求2:查询学生表中包含华的人
SELECT
NAME AS ‘姓名’
FROM student
WHERE NAME LIKE ‘%华%’ ;
– 需求3:查询学生表中姓名有三个字的人
– 注意事项:插入字符串数据,后面不要空格,当做单独字符出现!
INSERT INTO student VALUES(10,'柳夏 ',35,‘女’,‘咸阳’,‘1999-10-11’,80,70);
SELECT
NAME AS ‘姓名’
FROM student
WHERE NAME LIKE ‘___’ ;

– 排序查询 order by(格式)
– 语法:select 字段 from 表名 order by 排序字段1 排序方式1,排序字段2 排序方式2…
– asc(默认的升序),desc(降序)
– 需求:将数学成绩按照升序排序(如果表名order by 只跟了字段,没有跟排序方式)
SELECT * FROM student ORDER BY math ; – 没有排序方式,默认升序排序
– 显示给定排序方式
SELECT * FROM student ORDER BY math ASC ;
– 将数学成绩降序排序
SELECT
NAME 姓名,math 数学成绩
FROM student
ORDER BY math DESC ;

– 注意:
– 使用排序的时候,可以真多个字段进行排序,如果前面前面字段的排序一样,需要
– 按照后面的结果排序
– 需要同时对数学成绩进行降序排序,英语成绩进行升序排序(如果前面排序结果一样,按照后面的排序结果执行)
– 先按照前面字段进行排序,如果一样,再按照后面!!!
SELECT
name 姓名,
math 数学成绩,
IFNULL(english,0) 英语成绩
FROM student
ORDER BY math DESC ,
english ASC ;

– 查询的时候去除重复distinct(字段) distinct 字段名称,同时还需要查询别的字段,必须将
– distinct在别的字段前面
– select name as 姓名,distinct address from student ;
SELECT DISTINCT address ,NAME FROM student; – 去除重复的字段写在前面

– 聚合函数
– 统计相关的函数
– 注意:聚合函数在(having 分组之后是可以使用的)
– count(id/其他字段) 统计当前表中记录数有多少条
– max(字段) 查询当前该字段的最大值
– min(字段) 查询当前字段中的最小值
– avg(字段) 当前字段的平均分

– 查询student表中的记录数
– count(字段) 特点:统计记录数的只能针对非null字段
SELECT COUNT(english) FROM student ;-- 推荐count(id)
– 查询当前数学成绩的最大值
SELECT MAX(math) AS 数学最高分 FROM student ;
– 查询英语成绩的最低分
SELECT MIN(english) FROM student ;
– 查询数学成绩平均分
SELECT
AVG(math) AS ‘数学平均分’
FROM student ;

SELECT
AVG(english) AS ‘英语平均分’
FROM student ;

– 分组查询(一般查询的是分组的字段)
– 语法
– select 分组相关的字段或者聚合函数(分组字段)
– from 表名
– group by 分组字段;
– 需求:按照性别分组,并查询男生,女生的人数
SELECT
gender AS ‘性别’,COUNT(gender)
FROM student GROUP BY gender ;
– 需求:按照性别分组,并查询男生,女生的人数 条件:数学成绩大于70的人参与分组
– 注意:加入条件查询,必须先进行条件查询,在分组(分组之前要进行条件查询)
SELECT
gender AS ‘性别’,COUNT(gender)
FROM student
WHERE math>=70
GROUP BY gender ;

– 筛选 :分组之后完成的 having
– 注意事项:having中可以使用聚合函数

– 需求:按照性别分组,并查询男生,女生的人数
– 条件:数学成绩大于70的人参与分组 ,分组之后统计人数大于2个的人
SELECT
gender AS ‘性别’,COUNT(gender),COUNT(id)
FROM student
WHERE math>=70
GROUP BY gender
HAVING COUNT(id)>2 ; – 使用聚合函数

– 关于group by 和having的使用

where条件 在分组之前,如果不满足条件,不参与分组;
– 分组之后完成筛选,满足条件进行筛选,不满足不进行筛选!

– 明天约束和多表查询 (左外/右外连接,内连接,笛卡尔乘积…)

– 需求 查询英语成绩是null的人
– is null
SELECT NAME AS ‘姓名’ FROM student WHERE english IS NULL ;
– 英语成绩不是null的人
SELECT NAME AS ‘姓名’ FROM student WHERE english IS NOT NULL ;