select name,age from tb_emp where age>(selectavg(age)from tb_emp);
1.2关键字子查询
#1.使用 ALL 关键字进行查询select position,salary from tb_salary where salary >ALL(select salary from tb_salary where position="java");#2.使用 ANY 关键字进行查询select position,salary from tb_salary where salary >ANY(select salary from tb_salary where position="java");#3.使用 IN 关键字进行查询select position,salary from tb_salary where salary IN(select salary from tb_salary where position="java");
2.连接查询
2.1内连接
select tb_student.name as studentName,tb_class.name as className from tb_class join tb_student on tb_class.id=tb_student.class_id;
2.2外连接查询
########## 使用左外连接查询所有学生姓名和对应的班级 ##########select tb_student.name as studentName,tb_class.name as className from tb_student leftjoin tb_class on tb_class.id=tb_student.class_id;########## 使用右外连接查询所有学生姓名和对应的班级 ##########select tb_student.name as studentName,tb_class.name as className from tb_student rightjoin tb_class on tb_class.id=tb_student.class_id;
2.3复合条件连接查询
select tb_student.name as studentName ,tb_student.score,tb_class.name as className from tb_student join tb_class on tb_student.class_id=tb_class.id where tb_student.score>90;
3.单表查询(一)
3.1基本查询语句
########## retrieving the Name and Salary ##########SELECT Name, Salary
FROM tb_emp;########## retrieving all the table ##########SELECT*FROM tb_emp;
3.2带IN关键字查询
########## retrieving the Name and Salary with IN statement ##########SELECT Name, Salary
FROM tb_emp
WHERE Id NOTIN(1);
3.3带BETWEEN AND 的范围查询
########## retrieving the Name and Salary with BETWEEN AND statement ##########SELECT Name, Salary
FROM tb_emp
WHERE Salary BETWEEN3000AND5000;
4.单表查询(二)
4.1带 LIKE 的字符匹配查询
SELECT Name, Salary
FROM tb_emp
WHERE Name Like'C%';
4.2查询空值与去除重复结果
SELECT*FROM tb_emp
WHERE DeptId ISNULL;SELECTDISTINCT Name
FROM tb_emp;
4.3带 AND 与 OR 的多条件查询
SELECT*FROM tb_emp
WHERE DeptId =301AND Salary >3000;SELECT*FROM tb_emp
WHERE DeptId IN(301,303);
5.单表查询(三)
5.1对查询结果进行排序
########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########select*from tb_score
where class_id =1orderby score desc;
########## bundle insert the value ##########INSERTINTO tb_emp (Id, Name, DeptId, Salary)VALUES(1,'Nancy',301,2300.00),(2,'Tod',303,5600.00),(3,'Carly',301,3200.00);
6.2更新数据
########## update the value ##########UPDATE tb_emp
SET Name ="Tracy", DeptId =302, Salary =4300.00WHERE Id =3;
6.3删除数据
########## delete the value ##########DELETEFROM tb_emp
WHERE Salary >3000;
7.数据库和表的基本操作(一)
7.1查看表结构与修改表名
########## modify the table name ##########ALTERTABLE tb_emp RENAME jd_emp;########## show tables in this database ##########SHOWTABLES;########## describe the table ##########DESCRIBE jd_emp;
7.2修改字段名与字段数据类型
########## change the column name ##########ALTERTABLE tb_emp CHANGE Id prod_id int(11);########## change the data type of column ##########ALTERTABLE tb_emp MODIFY Name varchar(30);
7.3添加与删除字段
########## add the column ##########ALTERTABLE tb_emp ADD Country varchar(20)AFTER Name;########## delete the column ##########ALTERTABLE tb_emp DROP Salary;
7.4修改字段的排列位置
########## modify the column to top ##########ALTERTABLE tb_emp MODIFY Name VARCHAR(25)FIRST;########## modify the column to the rear of another column ##########ALTERTABLE tb_emp MODIFY DeptId INT(11)AFTER Salary;
7.5删除表的外键约束
########## delete the foreign key ##########ALTERTABLE tb_emp DROPFOREIGNKEY emp_dept;