MySql基础(表的查询)

 单个表的查询：

DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作，可以从一个表中查询数据，也可以从多个表中查询数据。

基础查询

语法： select 查询列表 from 表名;

特点： 查询列表可以是：表中的字段、常量、表达式、函数 查询的结果是一个虚拟的表格

-- 修改表名
RENAME TABLE student TO stu
-- 复制表结构
CREATE TABLE student LIKE stu
-- DML 数据操作 增    修    删
INSERT INTO student(NAME,gender,birthday,phone,height,reg_time)VALUES('刘某','男','2023-12-12','1511151110',2.75,NOW())
INSERT INTO student SET NAME='科比',gender='男',birthday='1986-2-3',phone='99999999999'
INSERT INTO student(NAME,gender,birthday,phone,height,reg_time)
           VALUES('库里','男','1923-3-2','1555523432',1.85,NOW()),
             ('里裤','男','1923-4-2','1555443432',1.87,NOW()),
             
INSERT INTO student(NAME,gender,birthday,phone,height,reg_time)VALUES('马家骑','女','2004-5-9','00000000000',1.56,NOW())
INSERT INTO student(number,NAME,gender,birthday,phone,height,reg_time)SELECT * FROM student

-- 修改记录
UPDATE student SET NAME='梅西',height=1.70 WHERE height=1.85

-- 删除
DELETE FROM stu
DELETE FROM student WHERE number=6

-- sql - DQL 数据查询
-- select 结果  from 表名
SELECT NAME,gender,birthday,phone FROM student WHERE number=1

-- 查询所有列 *-所有的列    查询的结果表格是只读的，不可操作
SELECT * FROM student

-- 查询结果进行算术运算
SELECT NAME,height+1 FROM student

SELECT DISTINCT NAME,gender FROM student

-- 函数
-- 单行函数：会对查询中的每一行数据进行处理
-- 字符函数
-- length(列名)  以字节为单位
SELECT LENGTH(NAME),gender FROM student
-- char_length(列名)  以字符为单位
SELECT CHAR_LENGTH(NAME),gender FROM student

-- concat(str1,str2,str3) 连接多个字符串  as  别名
SELECT CONCAT(NAME,':',CHAR_LENGTH(NAME))AS NAME,gender FROM student

-- upper() lower()
SELECT UPPER(NAME),LOWER(NAME) FROM student

-- substring(字符串,起始位置,截取长度)截取字符串    从1开始
SELECT SUBSTRING(NAME,2,1) FROM student

-- instr(字符串，指定字符串)  返回指定字符首次出现的位置    找不到返回0
SELECT INSTR(NAME,'比')FROM student

-- trim(字符串)  默认去掉字符串前后的空格
SELECT TRIM(NAME),NAME FROM student

-- trim(指定的子串 from 字符串) 可以去掉前后指定的子串
SELECT TRIM('科' FROM NAME),NAME FROM student

-- 左填充  右填充  到指定长度
SELECT LPAD(NAME,5,'a'),RPAD(NAME,5,'b') FROM student

-- replace(列,'old','new') 替换
SELECT REPLACE(NAME,'西','东') FROM student

-- case when 条件 then 条件成立的结果 else 条件不成立的结果
SELECT
  NAME,
  gender,
  CASE WHEN height>=2 THEN '高个子' ELSE '正常身高' END
FROM
  student
  
SELECT
  NAME,
  gender,
  (CASE WHEN height>=2 THEN 'A'
    WHEN height>=2.5 THEN 'B'
    WHEN height IS NULL THEN '妹有'
    ELSE 'C'END)AS height,
  IFNULL(height,'暂未录入身高')AS height
FROM
  student
  
-- ifnull(列,字符串) 显示为空时的默认信息
SELECT
  NAME,
  gender,
  IFNULL(height,'暂未录入身高')AS height
FROM
  student  
  
-- if(条件,结果1，结果2)
SELECT
  NAME,
  gender,
  IF (height >= 2, '高个子', '正常') AS height
FROM
  student
  
-- 数学函数
SELECT NAME,gender,ROUND(height),CEIL(height),FLOOR(height) FROM student
-- trunxate(),确定小数点后几位
SELECT NAME,gender,TRUNCATE(height,1),height,RAND() FROM student

-- 日期函数
-- now() 年月日 时分秒 系统当前时间
-- curdate() 年月日
-- curtime() 时分秒
SELECT NOW(),CURDATE(),CURTIME() FROM student

-- year(日期): 将日期格式化为年
SELECT YEAR(birthday),MONTH(birthday) FROM student

-- str_to_date('2023.12.23','%Y-%m-%d')将字符串日期 格式化为 日期类型
SELECT STR_TO_DATE(birthday,'%Y-%m-%d')FROM student

SELECT DATE_FORMAT(birthday,'%Y-%m')FROM student

-- 计算日期差多少天
SELECT DATEDIFF('2000-3-3',birthday)FROM student

-- 分组函数  聚合函数  统计函数
SELECT SUM(height) FROM student
SELECT AVG(height) FROM student
SELECT MAX(height) FROM student
SELECT MIN(height) FROM student
-- 查询表中目标列有多少行内容
SELECT COUNT(height) FROM student

SELECT * FROM student WHERE gender ='女' OR height<1.8
SELECT * FROM student WHERE gender = '男'AND height>1.8
SELECT * FROM student WHERE gender <> '女'
SELECT * FROM student WHERE gender != '男'

-- like '%比'  模糊匹配
SELECT * FROM student WHERE NAME LIKE '%比'

SELECT * FROM student WHERE height>=1.65 AND height<=1.85
-- between 1.65 and 1.85 两者之间（包含两者）
SELECT * FROM student WHERE height BETWEEN 1.65 AND 1.85
-- in(值1，值2，......值n)  在给定的值中
SELECT * FROM student WHERE height IN(1.75,1.85,1.95)
SELECT * FROM student WHERE height NOT IN(1.75,1.85,1.95)

-- 查询身高为null值的数据
SELECT * FROM student WHERE height IS NULL
-- 查询身高不为null的数据
SELECT * FROM student WHERE height IS NOT NULL

-- union 将多个查询结果合并，合并时，列数必须一致，会去除重复数据
SELECT NAME,gender FROM student WHERE gender '男'
UNION
SELECT NAME,gender FROM student WHERE gender ='女'

-- union all 只是将查询的多个结果合并，不会去除重复数据
SELECT NAME,gender FROM student WHERE gender = '男'
UNION ALL
SELECT NAME,gender FROM student WHERE gender ='女'

-- 排序      order by 列 asc 升序 desc 降序
SELECT * FROM student WHERE number>0 ORDER BY number ASC
SELECT * FROM student WHERE number>0 ORDER BY number DESC

SELECT * FROM student WHERE number>0 ORDER BY height ASC

SELECT * FROM student WHERE number>0 ORDER BY reg_time DESC

SELECT * FROM student WHERE number>0 ORDER BY height ASC,reg_time ASC

-- 数量限制 实现分页查询 limit 开始位置，每次查询的数量
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 0,3
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 3,3

-- 分组查询
-- 分组--在一些统计场景中，按照某个条件进行区分(分组)
-- 统计时，按照性别，班级，年龄，部门等等
-- 把满足某一个条件的数据，分到同一个组中，每个组内单独进行处理

-- 查询 男生，女生各有多少人    每个年级各有多少  每个部门各有多少人
   -- 分组统计 男生统计一次，女生统计一次
SELECT COUNT(*),gender FROM student GROUP BY gender

SELECT COUNT(height),gender FROM student GROUP BY gender
SELECT COUNT(*),DATE_FORMAT(birthday,'%Y-%m')FROM student GROUP BY DATE_FORMAT(birthday,'%Y-%m')

-- 男生和女生谁的人数大于2
-- having count(*)>2      having 条件  是对分组后的结果进行条件筛选
SELECT COUNT(*),gender FROM student GROUP BY gender HAVING COUNT(*)>2