MySQL-02

基础命令

DQL-数据查询语言-查

点击查看代码

# 1. SELECT
SELECT column_list FROM tbl_name;
SELECT DISTINCT column_name1, column_name2 FROM tbl_name;
SELECT column list FROM table_name LIMIT [number of records];

# 2. WHERE
SELECT column_list FROM table_name WHERE condition;
---
SELECT * FROM customers WHERE ID BETWEEN 3 AND 7;
SELECT ID, FirstName FROM customers WHERE City = 'New York';
SELECT ID, Age FROM customers WHERE Age >= 30 AND Age <= 40;
SELECT * FROM customers WHERE City = 'New York' AND (Age=30 OR Age=35);
SELECT * FROM customers WHERE City IN ('New York', 'Los Angeles');
SELECT * FROM customers WHERE City NOT IN ('New York', 'Los Angeles');

# 3. AS
SELECT CONCAT(FirstName,', ', City) AS new_column FROM customers;
SELECT ID, FirstName, LastName, Salary+500 AS Salary FROM employees;

# 4. LIKE
/* The LIKE keyword is useful when specifying a search condition within your WHERE clause.*/
SELECT * FROM employees WHERE LastName LIKE '%s';	 /* 以s结尾的*/
SELECT * FROM employees WHERE FirstName LIKE 'A%'; /* 以A开头的*/
SELECT * FROM employees WHERE LastName LIKE '_A';

# 5. 函数
SELECT CONCAT(FirstName, ', ' , City) FROM customers;
SELECT CONCAT(LOWER(FirstName), '_', UPPER(LastName)) AS fullName FROM employees;
SELECT AVG(Salary), SUM(Salary), MIN(Salary) FROM employees;

# 6. ORDER BY
-- 降序 DESC 
-- 升序 ASC（默认）
SELECT FirstName, Salary FROM employees
WHERE Salary>(SELECT AVG(Salary) FROM employees)
ORDER BY Salary DESC;

# 7. 分组--> GROUP BY
SELECT column, group_function(column) FROM table
WHERE condition 
GROUP BY group_by_expression
HAVING condition
ORDER BY column;

-- 查询领导编号>102的每个领导手下的最低工资>5000的领导编号及最低工资
SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000

# 8. 多表查询（sql92 & sql99）
-- 内连接：等值/inner、非等值、自连接
SELECT department_name, d.manager_id, MIN(salary)
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name, d.manager_id

-- 外连接：左外、右外、全外连接

# 9. 子查询
-- 单行单列
SELECT MIN(salary), department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
)
-- 多行多列：IN / ANY / ALL
SELECT last_name, employees_id, job_id, salary
FROM employees
WHERE salary < ANY(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG'

-- 查询每个部门平均工资的工资等级
SELECT a.*, b.grade_level
FROM (
    SELECT AVG(salary) avg, department_id
    FROM employees
    GROUP BY department_id
) a
INNER JOIN job_grades b
ON a.avg BETWEEN b.lowest_sal AND b.highest_sal

/* 每个班男生女生分别多少人 */
SELECT c.name 班级,
       sum(CASE s.gender 
		        WHEN 'M' THEN 1
		        ELSE 0
		        END
       ) 男生人数,
       sum(CASE s.gender
		        WHEN 'F' THEN 1
		        ELSE 0
		        END
       ) 女生人数
FROM classes c, students s
WHERE c.id = s.class_id
GROUP BY c.name

# 10. UNION 联合查询
-- 查询列数一致；

DDL-数据定义语言

点击查看代码

-- 数据库 和 数据表
# CREATE
CREATE DATABASE IF NOT EXISTS 库名;
RENAME DATABASE 库名 TO 新库名;

CREATE TABLE 表名(
     列名 列的类型 (长度 约束),
     ...
);

CREATE TABLE COPY LIKE 表名;
 
CREATE TABLE 表名
SELECT * FROM 表名2;

# ALTER
ALTER DATABASE 库名 CHARACTER SET gbk; 

ALTER TABLE 表名 RENAME TO 新表名;
ALTER TABLE 表名 ADD COLUMN 字段名 类型;
ALTER TABLE 表名 DROP COLUMN 字段名;
ALTER TABLE 表名 MODIFY COLUMN 字段名 类型;
ALTER TABLE 表名 CHANGE COLUMN 字段名 新字段名 类型;

# DROP
DROP DATABASE IF EXISTS 库名;
DROP TABLE IF EXISTS 表名;

# 约束
NOT NULL
DEFAULT
PRIMARY KEY 唯一性且非空
UNIQUE 唯一约束，唯一性且可以为空
FOREIGN KEY 外键约束
【CONSTRAINT 约束名】约束类型(字段名)

CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20) NOT NULL,
    gender CHAR(1),
    seat INT,
    age INT DEFAULT 18,
    majorid INT,
    CONSTRAINT pk PRIMARY KEY(id),
    CONSTRAINT uk UNIQUE(seat),
    CONSTRAINT ck CHECK(gender = '男' OR gender = '女'),
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
)

DML-数据操纵语言

# 1. 插入INSERT
INSERT INTO 表名(列名, ...) 
VALUES(值1, ...)

# 2. 修改UPDATE
UPDATE 表名 
SET 列=新值, ...
WHERE 条件

# 3. 删除DELETE
DELETE FROM 表名
WHERE 条件

# 4. TRUNCATE
-- 清空表所有数据，不能加条件

DCL-数据控制语言(权限)

TCL-事务控制语言

事务由单独单元的一个或多个SQL组成，每个SQL是相互依赖的，一旦执行失败或产生错误，整个单元将会回滚。