简单的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;