简单的SQL语句
简单的SQL语句
基本查询所有字段语句
SELECT * FROM student;
SELECT s_number,s_name,s_age,s_gender FROM student;
指定查询numer和name字段的记录
SELECT s_number,s_name FROM student;
查询student表的s_age>=25的所有记录
SELECT * FROM student WHERE s_age>=25;
BETWEEN AND: s_age大于等于25并且小于等于75的记录
SELECT * FROM student WHERE s_age BETWEEN 25 AND 75;
SELECT * FROM student WHERE s_age>=25 AND s_age<=75;
IS NULL
SELECT * FROM student WHERE s_age IS NULL;
查询s_age的值不为NULL的记录
SELECT * FROM student WHERE s_age IS NOT NULL;
s_age的值为25、35、45的记录(一句SQL)
SELECT * FROM student WHERE s_age IN(25,35,45);
实际应用是混合使用
SELECT * FROM student WHERE s_age>25 AND (s_gender=’male’ OR s_number IN());
查询s_name的值是以’z’开始的记录
SELECT * FROM student WHERE s_name LIKE ‘z%’;
查询s_name的值是以’n’结束的记录
SELECT * FROM student WHERE s_name LIKE ‘%n’;
查询s_name的值是包含’a’的记录
SELECT * FROM student WHERE s_name LIKE ‘%a%’;
查询s_name的值是以’z’开始的7个字符的记录
SELECT * FROM student WHERE s_name LIKE ‘z______’;
当查询字段值具有重复值,需要去重
SELECT DISTINCT s_gender FROM student;
SELECT DISTINCT s_gender,s_name FROM student;
SELECT losal+hisal AS he FROM salarygrade;
排序查询
SELECT * FROM student ORDER BY s_age DESC;
查询s_age的值大于25的记录,并且按照s_age由小到大排序
SELECT * FROM student WHERE s_age>25 ORDER BY s_age;
查询losal列的所有值之和
SELECT SUM(losal) FROM salarygrade;
查询salarygrade表的所有记录总数
SELECT COUNT(*) FROM salarygrade;
查询losal的最大值
SELECT MAX(losal) FROM salarygrade;
SELECT MIN(losal) FROM salarygrade;
SELECT AVG(losal) FROM salarygrade;
下面的写法逻辑错误
SELECT SUM(losal),hisal FROM salarygrade;
分组查询语句
SELECT * FROM student WHERE s_age>25 GROUP BY s_gender ORDER BY s_age;
使用聚合函数作为查询条件
SELECT * FROM student HAVING MAX(s_age)>25;
SELECT * FROM student WHERE s_age>25 GROUP BY s_gender HAVING MAX(s_age)>25 ORDER BY s_age;
多表查询
SELECT * FROM emp;
SELECT * FROM dept;
查询员工表,要求显示部门名称
SELECT emp.*,dept.dname FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno;
以上写法简写方式
SELECT emp.*,dept.dname FROM emp,dept WHERE emp.deptno=dept.deptno;
三张表连接查询
SELECT * from 表名1
left join 表名2 on 表名1.字段名=表名2.字段名
left join 表名3 on 表名2.字段名=表名2.字段名;
内连接查询
SELECT e1.ename AS yuangong,e2.ename AS zhuguan FROM emp AS e1 INNER JOIN emp AS e2 ON e1.mgr=e2.empno;
以上写法简写方式
SELECT e1.ename AS yuangong,e2.ename AS zhuguan FROM emp AS e1,emp AS e2 WHERE e1.mgr=e2.empno;
向student表插入一条学生记录
SELECT * FROM student;
插入所有字段值
INSERT INTO student VALUES(‘s_1020′,’ZhangWuJi’,’180′,’male’);
插入指定字段:s_number,s_name
INSERT INTO student(s_number,s_name) VALUES(‘s_1021′,’ZhouZhiRuo’);
如果表字段s_gender默认不允许为空的,插入时怎么办?
修改s_name值为’ZhangWuJi’的年龄为800
UPDATE student SET s_age=800;
删除s_name值为’ZhangWuJi’
DELETE FROM student WHERE s_name=’ZhangWuJi’;
创建数据库为day0233
CREATE DATABASE IF NOT EXISTS day0322;
创建一个用户表 名称、年龄、性别、工作等
CREATE TABLE myuser(
username VARCHAR(50),
age INT,
sex VARCHAR(50),
job VARCHAR(50)
);
查看所有数据库
SHOW DATABASES;
查看表结构
DESC myuser;
查看建表语句
SHOW CREATE TABLE myuser;
删除表
DROP TABLE myuser;