mysql基本语法 - 查 - 《Mysql数据库》

基本查询
条件查询
模糊查询
聚合查询对表的多行数据进行操作得到一个结果
分页查询 limit x,y 表示跳过前面x条数据，查询y条数据
排序 asc(升序,默认) desc(降序)
关联查询

基本查询

查询学生表中的所有数据

SELECT * FROM student;

从学生表中查询指定的字段

SELECT id,NAME,chinese FROM student;

给字段取别名

SELECT id AS ‘序号’,NAME AS ‘名字’,chinese AS ‘语文成绩’ FROM student AS m;

查询合并列(只要数值类型的字段可以合并)

SELECT id AS ‘序号’,NAME AS ‘名字’,(chinese+english+math) AS ‘总成绩’ FROM student AS m;

去重查询

SELECT DISTINCT(NAME) FROM student;

条件查询

需求：查询id为2，且姓名为李四的学生

SELECT * FROM student WHERE id = 2 AND NAME = ‘李四’;

需求：查询id为2，或姓名为张三的学生

SELECT * FROM student WHERE id = 2 OR NAME = ‘张三’;

*比较条件 > < >= <= = <>(不等于) between and (等价于>= 且 <=)

需求：查询语文成绩大于70分的学生

SELECT * FROM student WHERE chinese >= 70;

需求：查询英语成绩大于等于75，且小于等于90分的学生

SELECT FROM student WHERE english>=75 AND english<=90;
SELECT FROM student WHERE english BETWEEN 75 AND 90; — 包前包后

查询性别不等于男的

SELECT * FROM student WHERE gender <> ‘男’;

关于null null用于不等于null null和空字符串是两个东西,空字符串是有值的,null是无值的

查询id为null的学生

SELECT * FROM student WHERE id IS NULL;

查询姓名为空字符串的

SELECT * FROM student WHERE NAME = ‘’;

需求：查询id不为null或者name有值的学生

SELECT * FROM student WHERE id IS NOT NULL OR NAME <> ‘’;

模糊查询

查询姓名以李开头的学生

%可以匹配一个或多个字符 _可以匹配一个字符
SELECT * FROM student WHERE NAME LIKE ‘李%’;

查询姓名以李开头的，并且姓名是两个字的

SELECT * FROM student WHERE NAME LIKE ‘李_‘;

查询名字包含小的学生

SELECT * FROM student WHERE NAME LIKE ‘%小%’;

聚合查询对表的多行数据进行操作得到一个结果

sum() avg() max() min() count()

SELECT SUM(chinese) FROM student;
SELECT AVG(chinese) FROM student;
SELECT COUNT(chinese) FROM student;

分页查询 limit x,y 表示跳过前面x条数据，查询y条数据

SELECT FROM student;
SELECT FROM student LIMIT 0,2;
SELECT FROM student LIMIT 2,2;
SELECT FROM student LIMIT 4,2;

排序 asc(升序,默认) desc(降序)

SELECT * FROM student ORDER BY chinese DESC;

查询所有的学生，首先按照语文成绩升序排列，当学生的语文成绩一样的时候再按照英语成绩降序排序

SELECT * FROM student ORDER BY chinese ASC,english DESC;

解决null在前面和后面的问题在需要排序的列前面添加-号

SELECT * FROM student ORDER BY -chinese DESC;

分组查询 group by

SELECT gender,COUNT(*) FROM student GROUP BY gender;

关联查询

1.创建一个部门表

CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT,
    deptName VARCHAR(20)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO dept(deptName) VALUES('技术部');
INSERT INTO dept(deptName) VALUES('人力资源部');
INSERT INTO dept(deptName) VALUES('市场部');
INSERT INTO dept(deptName) VALUES('总经办');
SELECT * FROM dept;

2.创建一个员工表

CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    empName VARCHAR(20),
    deptId INT,
    -- CONSTRAINT 表示约束    
    -- fk_emlyee_dept  表示约束的名字
    -- FOREIGN KEY(deptId)  表示给当前表的deptId字段添加约束
    -- REFERENCES dept(id)  当前表的deptId字段去引用dept表的id字段
    -- ON UPDATE CASCADE 级联更新
    -- ON DELETE CASCADE 级联删除
    CONSTRAINT fk_emlyee_dept FOREIGN KEY(deptId) REFERENCES dept(id) 
  ON UPDATE CASCADE ON DELETE CASCADE  
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO employee (empName,deptId) VALUES('小明',1);
INSERT INTO employee (empName,deptId) VALUES('小虎',2);
INSERT INTO employee (empName,deptId) VALUES('小强',4);
INSERT INTO employee (empName,deptId) VALUES('小白',1);
SELECT * FROM employee;

3.关联查询关联多个表进行查询

需求：查询员工及其所在部门(显示员工姓名，部门名称)

内连接写法1

SELECT empName,deptName FROM employee,dept WHERE employee.deptId = dept.id;

内连接写法2

SELECT deptName,empName FROM employee INNER JOIN dept ON employee.deptId = dept.id;

左外连接 LEFT OUTER JOIN 左边的叫左表

SELECT empName,deptName FROM employee LEFT OUTER JOIN dept ON employee.deptId = dept.id;

左外连接相当于内连接的好处是能够保证左表的数据完全显示

SELECT deptName,empName FROM dept LEFT OUTER JOIN employee ON dept.id = employee.deptId;

查