【无标题】

1.子查询

1.1带比较运算符的子查询

select name,age from tb_emp where age>(select avg(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 left join tb_class on tb_class.id=tb_student.class_id;
########## 使用右外连接查询所有学生姓名和对应的班级 ##########
select tb_student.name as studentName,tb_class.name as className from tb_student right join 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 NOT IN (1);

3.3带BETWEEN AND 的范围查询

########## retrieving the Name and Salary with BETWEEN AND statement ##########
SELECT Name, Salary
FROM tb_emp
WHERE Salary BETWEEN 3000 AND 5000;

4.单表查询(二)

4.1带 LIKE 的字符匹配查询

SELECT Name, Salary
FROM tb_emp
WHERE Name Like 'C%';

4.2查询空值与去除重复结果

SELECT *
FROM tb_emp
WHERE DeptId IS NULL;


SELECT DISTINCT Name
FROM tb_emp;

4.3带 AND 与 OR 的多条件查询

SELECT *
FROM tb_emp
WHERE DeptId = 301 AND Salary > 3000;

SELECT *
FROM tb_emp
WHERE DeptId IN (301, 303);

5.单表查询(三)

5.1对查询结果进行排序

########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########
select * from tb_score 
    where class_id = 1 
        order by score desc;

5.2分组查询

########## 对班级名称进行分组查询 ##########
select * from tb_class 
    group by class_id;

5.3使用 LIMIT 限制查询结果的数量

########## 查询班级中第2名到第5名的学生信息 ##########
select * from tb_score 
    order by score desc 
        limit 1,4;

6.数据库和表的基本操作(二)

6.1插入数据

########## bundle insert the value ##########
INSERT INTO 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.00
WHERE Id = 3;

6.3删除数据

########## delete the value ##########
DELETE FROM tb_emp
WHERE Salary > 3000;

7.数据库和表的基本操作(一)

7.1查看表结构与修改表名

########## modify the table name ##########
ALTER TABLE tb_emp RENAME jd_emp;

########## show tables in this database ##########
SHOW TABLES;

########## describe the table ##########
DESCRIBE jd_emp;

7.2修改字段名与字段数据类型

########## change the column name ##########
ALTER TABLE tb_emp CHANGE Id prod_id int(11);

########## change the data type of column ##########
ALTER TABLE tb_emp MODIFY Name varchar(30);

7.3添加与删除字段

########## add the column ##########
ALTER TABLE tb_emp ADD Country varchar(20) AFTER Name;

########## delete the column ##########
ALTER TABLE tb_emp DROP Salary;

7.4修改字段的排列位置

########## modify the column to top ##########
ALTER TABLE tb_emp MODIFY Name VARCHAR(25) FIRST;

########## modify the column to the rear of another column ##########
ALTER TABLE tb_emp MODIFY DeptId INT(11) AFTER Salary;

7.5删除表的外键约束

########## delete the foreign key ##########
ALTER TABLE tb_emp DROP FOREIGN KEY emp_dept;