39.MySql的“数据库带定义语句“和“增删查改“

最新推荐文章于 2024-11-06 11:25:59 发布

bug创可贴

最新推荐文章于 2024-11-06 11:25:59 发布

阅读量343

点赞数

文章标签：数据库开发 dba

本文链接：https://blog.csdn.net/Liang_it_yuan/article/details/119720284

版权

本文详细介绍了MySQL中的数据库定义（DDL）和数据操作（DML）语句，包括创建、查询、修改和删除数据库、表以及记录的方法。内容涵盖show databases、create database、alter database、drop database、insert、update、delete、select等关键操作，并通过实例演示了如何处理中文乱码、模糊查询、聚合函数及分页查询等高级技巧。

摘要由CSDN通过智能技术生成

一.DDL(数据库定义语句)

1.show databases;

2.创建数据库

方式一:create database 库名 ;

方式二:create database if not exists 库名;

3.查看创建数据库的默认的字符集(了解)

show create database 库名;

4.修改数据库的字符集(了解)

alter database 库名 (default)可以省略 character set=字符集;

5.删除库

方式一:drop database 库名;

方式二:drop database if exists 库名;

6.模糊查询mysql服务中所有的带character字符集的全局变量

如果出现中文乱码:
character_set_client 客户端 utf8
character_set_results 结果集 utf8

改动字符集:临时解决中文乱码
mysql> set character_set_client=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> set character_set_results=utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

7.创建表

create table 表明(
列名1 字段类型1,
列名2 字段类型2,
...
列名n 字段类型n
) ;

注意事项:就是给那一个库中创建表使用哪个库(选择数据库名)
use 库名;

8.查询当前数据库中有哪些表 :show tables ;

9.查询当前表的结构

desc 表名 ;

10.修改表

(1)给student表添加一列

alter table 表名 add 字段名称字段类型;

(2)修改表中的字段类型

alter table 表名 modify 字段名称更改后的字段类型;

(3)修改表中的字段名称

alter table 表名 change 旧列名新列名字段类型;

(4)修改表删除某一列字段

alter table 表名 drop 字段名称;

(5)修改表的表名

alter table 表名 renameto 新表名;

(6)复制一张表结构和以前表的结果一样

create table 新表名 like 旧表名;

11.删除表

(1)

方式一:drop tabble 表名;

方式二:drop table if exists 表名;

二.DML语句 (操作表的记录)

1.插入语句

2.修改语句

3.删除语句

用户编号,逐渐自增长

三.DQL 查询表中的数据

1.基本的查询语句

select * from 表名 ; -- 查询全表数据

select 字段名称列表 from 表名;--查询指定的字段

2.带条件查询

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的人 ,is  null 的用法     				
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+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 ;	
	

-- 查询英语成绩---总成绩(求和 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) '总人数',
	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 起始行数,每页显示多少条;

limit 起始行数=(当前页码数-1)*每页显示的条数,每页显示条数;

-- 给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 ;