第二十九天
SQL中的DML语句
-- sqlYog 图形界面化工具 写sql,table键提示功能
-- 先去使用命令
USE mydb_01 ;
-- 查询表结构
DESC stu ;
-- DML语句: 数据库操作语句,操作表记录
-- insert into 插入数据
-- 1)插入全表数据insert into 表名 values(值1,值2,.....);
INSERT INTO stu VALUES(2,'赵又廷','男','北京市',32) ;
-- 插入全表:一次性插入多条数据
-- 语法2) insert into 表名 values(值1,值2....),(值1,值2...),(值1,值2....) ;
INSERT INTO
stu
VALUES
(3,'文章','男','西安市',35),
(4,'马伊琍','女','上海市',42),
(5,'德玛西亚','男','艾欧尼亚',25) ;
-- 插入部分字段 :语法3 insert into 表名(部分字段1,字段2...) values(值1,值2...)
INSERT INTO
stu(NAME,sex,address) VALUES('王宝强','男','咸阳市') ;
-- 注意:插入部分字段,必须字段数量和插入值的数量必须匹配
-- insert into stu(id,name,sex) values(6,'姚笛') ;
-- 不管插入部分字段还全部字段,那么必须要和当前类型一致
INSERT INTO
stu
VALUES(6,'男','马嘉钰','西安市',20) ;
-- 修改表的数据 update
-- 语法:按条件修改 update 表名 set 字段名称 = 值 where 字段名称= 值; 修改一个字段
-- 修改name是王宝强的将他的id设置为7
UPDATE stu SET id = 7 WHERE NAME = '王宝强' ;
-- 修改id为7的age设置为37
UPDATE stu SET age = 37 WHERE id = 7 ;
-- 一次修改多个字段
-- update 表名 set 字段名称1 =值1 ,字段名称2 =值2 where 条件;
-- 将id为的6的姓名修改为 '张三丰' ,性别修改为'男'
UPDATE
stu
SET
NAME = '张三丰',
sex = '男'
WHERE
id = 6 ;
-- 如果不带条件修改:就是批量修改
-- 将修改name为 '高圆圆'
UPDATE stu SET NAME = '高圆圆' ; -- 一般都是带条件进行修改!
-- 删除
-- 带条件来删除
-- delete from 表名 where 字段名称 = 值; //删除指定的记录
-- 删除id = 6的这条记录
DELETE FROM stu WHERE id = 6 ;
-- 删除全表数据
-- delete from 表名;
DELETE FROM stu ;
-- truncate table 表名;
TRUNCATE TABLE stu ;
/*
删除全表的语法
delete from 表名
truncate table 表名; 两个区别
1)delete from 表名:只是删除全表数据;表的结构还存在,
如果表中 存在主键并且自增长约束,那么不会受影响,下一次在插入数据
继续在之前的基础上继续自增长!
2)truncate table 表名 ;
将表中数据删除的同时删除了表,然后在创建一张一模一样空表,
肯定影响自增长主键的值,再次插入数据,自增长从1开始...
等价于
drop table my_use;
创建一个当前一模一样的表结构
*/
-- 创建一张新的表 my_user表
CREATE TABLE my_user(
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户编号 主键加增长
NAME VARCHAR(20),
age INT ,
gender VARCHAR(5)
) ;
INSERT INTO my_user(NAME ,age ,gender)
VALUES('张三',20,'男'),('李四',22,'女'),('文章',25,'男') ;
INSERT INTO my_user(NAME,age,gender) VALUES('王五',18,'男') ;
-- delete from my_user where id = 8 ;
-- 删除全表数据
DELETE FROM my_user ;
-- 删除全部数据
TRUNCATE TABLE my_user ;
SELECT * FROM my_user;
-- 查询stu的表的数据
-- DQL之基本的查询语句
SELECT * FROM stu ; -- 查询全表数据
-- 实际开发中,* (通配符),一般查询全表数据,需要带全部字段
SELECT -- 指定全部字段
id ,
NAME,
sex,
address,
age
FROM stu ;
-- 查询指定的字段
-- select 字段名称列表 from 表名;
SELECT
id ,
NAME ,
address,
age
FROM stu ;
-- 查询指定字段时可以通过as 关键字指定别名,as可以省略
SELECT
id AS '学生编号',
NAME AS '姓名',
address AS '地址',
age AS '年龄'
FROM stu ;
-- as 可以省略
SELECT
id '学生编号',
NAME '姓名',
address '地址',
age '年龄'
FROM stu ;
-- 基本查询:字段去重
-- DQL带条件查询
-- where条件查询
-- 可以基本运算符:比较运算符(<,>,<=,>=,!=)/逻辑运算符(|| && /and /or)/赋值运算符 =
-- where后面多个or in(集合数据) 在两个值之间 between 值1 and 值2
-- mysql 判断某个字段为null , is null /is not null
-- 模糊查询 like
-- 聚合函数 count(列名)/max(列名)/min(列名)/sum(列名)/avg(列名)
-- 排序查询 order by
-- 分组查询 group by
-- 筛选查询 having
-- 分页查询limit
-- 创建一个学生表
CREATE TABLE student (
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
DESC student ;
INSERT INTO student(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);
-- 条件查询
-- 查询年龄大于20岁的人的全部信息
SELECT
id '编号',
NAME '姓名',
age '年龄',
sex '性别',
address '地址',
math '数学成绩',
english '英语成绩'
FROM
student
WHERE
age > 20 ;
-- 查询年龄在20岁和30岁之间的学生信息
SELECT
*
FROM
student
WHERE
age >= 20 && age <=30 ; -- &&:Java的逻辑运算符
-- 另一种语法
SELECT
*
FROM
student
WHERE
age >= 20 AND age <=30 ;-- and mysql表示并列关系
-- 另一种语法:mysql 在两个值之间 between 值1 and 值2
SELECT -- 查询部分字段 并且年龄在20-30
NAME '姓名',
age '年龄',
address '地址',
math '数学成绩',
english '英语成绩'
FROM
student
WHERE
age
BETWEEN 20 AND 30 ;
-- 查询年龄是18或者20或者30岁的学生的编号,姓名,年龄以及地址 (|| 或者 or)
SELECT
id '编号',
NAME '姓名',
age '年龄',
address '地址'
FROM
student
WHERE
age = 18 OR age = 20 OR age = 30 ;
-- mysql另一种语法 where 字段名称 in(多个值)
SELECT
id '编号',
NAME '姓名',
age '年龄',
address '地址'
FROM
student
WHERE
age
IN(18,20,30) ;
-- 查询英语成绩为null的学号编号,姓名,性别,地址.数学成绩信息
/*
select
id ,
name,
sex,
address,
math
from
student
where
english == null ; -- java中的用法可以这样用 == = (都不行)
*/
SELECT
id ,
NAME,
sex,
address,
math,
english
FROM
student
WHERE
english IS NULL ; -- mysql用法
-- 查询英语成绩不为null的人 ,is not null 的用法
SELECT
id ,
NAME,
sex,
address,
math,
english
FROM
student
WHERE
english IS NOT NULL ;
-- 查询英语和数学成绩总分的学生新
SELECT
id 编号 ,
NAME 姓名 ,
sex 性别 ,
address '地址',
-- (math+english) as '总分'
(math+IFNULL(english,0)) '总分'
FROM
student ;
-- mysql 内置函数ifnull(值1,值2) ; ifnull(english,0) :
-- 如果当前英语成绩为null,给默认值0
-- 查询地址
SELECT
address 地址
FROM student ;
-- 查询字段,对字段去重 (distinct)
SELECT
DISTINCT address 地址
FROM student ;
-- 查询年龄不是20岁的学生信息
SELECT
*
FROM student
WHERE age != 20 ; -- != Java中这种语法 !=
-- mysql中的不等于 <>
SELECT
*
FROM
student
WHERE
age <> 20 ;
-- 模糊查询mysql服务中带字符集相关的变量 show variables like '%character%' ;
-- 模糊查询 like
-- select 字段列表 from 表名 where 字段名称 like '%字符%' ;
/*
% :包含的指定的字符 使用'%字符值%' 模糊查询包含指定字符的信息
_ :代表单个字符(一个_下划线代表一个字符)
两个相结合使用: '_%字符值%_' 三个字符:中间字符包含指定的值进行模糊查询
*/
-- 查询当前学生表中姓名包含马的学生信息
SELECT
*
FROM
student
WHERE
NAME
LIKE
'%马%' ;
-- 查询第二个字符包含化的学生信息
SELECT
*
FROM
student
WHERE
NAME
LIKE
'_%化%' ;
-- 查询姓名是三个字符的人
SELECT
*
FROM
student
WHERE
NAME
LIKE
'___' ;
-- 应用场景: 搜索框中输入关键字查询---使用到模糊查询
-- 聚合函数查询---- >查询结果:单行单列的数据
-- count(列名) :总记录数
-- max(列名): 最大值
-- min(列名字段):最小值
-- sum(字段名称):求和
-- avg(列名):平均分
-- select 聚合函数(列名) from 表名;
-- 查询当前student这个表的全部总记录数
-- 如果使用english字段查询总记录数
SELECT
COUNT(english) -- 使用业务字段查询(可能某个值为null,不会进行记录)
FROM
student ; -- 7条记录
-- 可以使用函数设置如果当前某个值null,给默认值
SELECT
COUNT(IFNULL(english,0)) 总记录数
FROM
student;
/*
count(列名)查询总记录数的时候,一般都使用非业务字段查询
student
id(非业务字段) name age gender (业务字段)
学校里面的学生表
某个学生刚开始还在学习, 后面转学了 (非业务字段id---主键+自增长的)
1 张三 20 男
select
count(id)
from 表名;
*/
-- 建议id来查询
SELECT
COUNT(id) 总条数
FROM
student ;
-- 查询数学的平均成绩 avg(列名)
SELECT
AVG(math) '数学平均成绩'
FROM
student ; -- 79.5000
-- 查询英语成绩---总成绩(求和 sum(列名))
SELECT
SUM(IFNULL(english,0)) 英语总分
FROM
student ;
-- 查询英语成绩的最高成绩
SELECT
MAX(IFNULL(english,0)) 英语最高分
FROM
student ;
-- 查询数学成绩最低分
SELECT
MIN(math) 数学最低分
FROM
student ;
-- 聚合函数使用最多的:count函数,avg函数
-- 排序查询:order by 字段名称 asc/desc (升序/降序)
-- select 字段列表 from 表名 order by 字段名 排序规则; -- 单个字段进排序
-- 数学成绩安装升序排序
SELECT
*
FROM
student
ORDER BY math ; -- 如果字段名称后面没有带排序规则:则默认升序排序
-- 英语降序排序
SELECT
NAME ,
age,
sex,
address,
IFNULL(english,0) 英语成绩
FROM
student
ORDER BY english DESC ; -- 降序
-- 针对多个字段同时排序,当前第一字段值相等,则按照第二个字段的排序规则执行
-- select 字段列表 from 表名 order by 字段名称1 升序1 ,字段名称2 升序2;
-- 查询全表数据,数学成绩降序,英语成绩升序
SELECT
*
FROM
student
ORDER BY
math DESC,
english ASC ;
-- 分组查询:group by
-- 分组group by 分组字段;
-- 查询的时候可以查询分组字段,
-- group by 后面不能使用聚合函数
-- 问题:如果group by 和where条件语句一块使用,先后顺序? where之后 才能使用group by
-- 现在需要按照性别分组-----分组之后查询出总人数
-- 性别-- 男/女
SELECT
-- 查询分组字段
sex '性别',
COUNT(id) '人数'
FROM
student
GROUP BY
sex ; -- 性别
-- 现在需要按照性别分组-----分组之后查询出总人数,数学的平均分
-- 条件:数学成绩不大于70分的人不参与分组;
/*
SELECT
-- 查询分组字段
sex '性别',
COUNT(id) '人数'
FROM
student
GROUP BY
sex -- 性别
where
math > 70 ;
*/
SELECT
sex '性别', -- 查询分组字段
COUNT(id) '总人数',
AVG(math) '数学平均成绩'
FROM
student
WHERE
math > 70 -- 条件:数学成绩大于70分人参与分组
GROUP BY
sex ; -- 性别分组
-- 筛选 having
-- 现在需要按照性别分组-----分组之后查询出总人数,数学的平均分
-- 条件:数学成绩不大于70分的人不参与分组
-- 筛选条件:总人数大于2的一组
-- having 必须置于group by 之后,where 置于 group by 之前
-- group by不能聚合函数,但是having后面可以聚合函数
SELECT
sex '性别', -- 查询分组字段
COUNT(id) '总人数',
AVG(math) '数学平均成绩'
FROM
student
WHERE
math > 70 -- 条件:数学成绩大于70分人参与分组
GROUP BY
sex -- 性别分组
HAVING
COUNT(id) > 2 ;
-- 优化
SELECT
sex 性别, -- 查询分组字段
COUNT(id) 总人数,
AVG(math) 数学平均成绩
FROM
student
WHERE
math > 70 -- 条件:数学成绩大于70分人参与分组
GROUP BY
sex -- 性别分组
HAVING
总人数 > 2 ;
-- 分页查询limit
-- select 字段列表 from 表名 limit 起始行数,每页显示多少条;
-- 给student表在插入三条记录
INSERT INTO student VALUES(9,'德邦',19,'男','西安',78,58) ,
(10,'vn',20,'女','宝鸡',89,65),(11,'亚索',22,'男','西安',95,74);
-- 每页显示3条记录
-- 查询第一页的数据
-- limit 起始行数=(当前页码数-1)*每页显示的条数,每页显示条数;
SELECT * FROM student LIMIT 0,3 ;
-- 查询第二页的数据
SELECT * FROM student LIMIT 3 ,3 ;
-- 查询第三页数据
SELECT * FROM student LIMIT 6,3 ;
-- 第四页数据
SELECT * FROM student LIMIT 9,3 ;
-- 查询全表
SELECT * FROM student ;
UPDATE student SET english = 98 WHERE id = 7 ;
-- 数据库约束
-- 约束用户操作表的一种行为
-- 创建一个新的表
CREATE TABLE test(
id INT , -- 编号
NAME VARCHAR(10) , -- 姓名
gender VARCHAR(2) DEFAULT '女' -- 性别 -- 默认约束 防止出现非法数据null(没有插入造成null值)
) ;
-- 1)默认约束,当前没有给那个字段设置值的时候,此时默认约束就会起作用
DROP TABLE test ;
INSERT INTO test VALUES(1,'张三','男') ;
-- 可能用户操作数据库的时候,插入非法数据(没有意义的数据)
-- insert into test values(2,null,'女') ;
-- 如果没有给某个字段赋值,默认值null
INSERT INTO test(id,NAME) VALUES(2,'高圆圆') ;
-- 通过sql语句修改表的类型,删除默认约束
ALTER TABLE test MODIFY gender VARCHAR(2) ;
INSERT INTO test(id,NAME) VALUES(3,'文章') ;
-- 修改表加入默认约束
ALTER TABLE test MODIFY gender VARCHAR(2) DEFAULT '女' ;
DELETE FROM test WHERE id = 3 ;
-- 2)非空约束
DROP TABLE test ;
CREATE TABLE test(
id INT ,
NAME VARCHAR(10) NOT NULL -- 非空约束
);
INSERT INTO test VALUES(1,NULL) ; -- 直接插入null值
-- insert into test (id) values(1) ; 没有给姓名赋值
INSERT INTO test VALUES(1,'') ; -- 存在值,只是空字符 和null不一样
-- 删除非空约束
ALTER TABLE test MODIFY NAME VARCHAR(10) ;
UPDATE test SET NAME = '高圆圆' WHERE id = 1 ;
INSERT INTO test VALUES(2,NULL) ; -- Column 'NAME' cannot be null
-- 修改表,加入非空约束
ALTER TABLE test MODIFY NAME VARCHAR(10) NOT NULL ;
DELETE FROM test WHERE id = 2 ;
-- 3)唯一约束 nuique
DROP TABLE test;
CREATE TABLE test(
id INT ,
NAME VARCHAR(10),
phone VARCHAR(11) UNIQUE -- 唯一约束 :可以有null值,不能重复
) ;
INSERT INTO test VALUES(1,'张三','13666668888') ;
-- INSERT INTO test VALUES(1,'张三',null) ;
-- INSERT INTO test(id,name) values(2,'李四') ;
INSERT INTO test VALUES(2,'李四','13666668889') ;
-- INSERT INTO test VALUES(2,'李四','13666668888') ;
-- Duplicate entry '13666668888' for key 'test.phone'
-- 通过语句删除唯一约束
-- alter table test modify phone varchar(11) ; 错误语法
-- 删除唯一约束的sql alter table test drop index 字段名称;
ALTER TABLE test DROP INDEX phone ;
INSERT INTO test VALUES(4,'赵六','13666668878') ;
DELETE FROM test WHERE id = 4 ;
-- 添加唯一约束
ALTER TABLE test MODIFY phone VARCHAR(11) UNIQUE ;
-- 主键约束 (非空+唯一特点) primary key
-- 都会给当前非业务字段去设置主键(xxid)
DROP TABLE test ;
CREATE TABLE test(
id INT PRIMARY KEY , -- 非业务字段
NAME VARCHAR(10),
gender VARCHAR(2)
) ;
INSERT INTO test VALUES(1,'洪学佳','男'),(2,'马三奇','男') ;
-- insert into test values(1,'马嘉钰','男') ;id值重复 -- Duplicate entry '1' for key 'test.PRIMARY'
-- insert into test values(null,'雷郁','男') ;-- id直接插入null值 Column 'id' cannot be null
DQL语句:基本数据库查询语句
sql----->Structure Query Language:结构化查询语言.
按条件查询
条件查询while
基本逻辑运算符<,>,<=,>=,=,!=<>
逻辑运算符&&,||,and,or
Mysql的约束
非空约束
用户操作表,给表中添加字段的值不能为null
默认约束
default,当用户在操作表示,插入数据,若没有插入值,使用默认值.
唯一约束
操作表当前字段不能重复
主键约束
primary key主键非空唯一
自增长约束
auto_increment
外键约束
constraint 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
