MySQL入门

基础内容

DDL(定义)

库(DATABASE)

1.创建数据库

CREATE DATABASE IF NOT EXISTS database_name;

2.删除数据库

DROP DATABASE IF EXISTS database_name;

表(TABLE)

1.创建表

CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype,
    column2 datatype,
    ...
    columnN datatype,
    PRIMARY KEY (column1, column2, ...)
);

2.修改表结构

添加新列
ALTER TABLE table_name
ADD column_name datatype;

删除列
ALTER TABLE table_name
DROP COLUMN column_name;

修改列的数据类型
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

重命名列
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

重命名表
ALTER TABLE table_name
RENAME TO new_table_name;

3.删除表

DROP TABLE IF EXISTS table_name;

索引(INDEX)

1.创建索引

CREATE INDEX index_name ON table_name (column_name);

2.删除索引

ALTER TABLE table_name DROP INDEX index_name;

视图(VIEW)

1.创建视图

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

2.删除视图

DROP VIEW IF EXISTS view_name;

DML(修改)

1.插入记录(INSERT)

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

一次插入多条记录

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...), (value1, value2, ...), ...;

2.更新记录(UPDATE)

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

同时更新多个数据

UPDATE t1, t2, ...
SET t1.column1 = expr1, t2.column2 = expr2, ...
WHERE condition;

3.删除记录(DELETE)

DELETE FROM table_name WHERE condition;

#where可选, 无的话删除所有记录

DCL(用户管理)

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

删除用户

DROP USER 'username'@'host';

设置密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

授予权限

GRANT privileges ON database.table TO 'username'@'host';

#privileges是权限列表，可以是SELECT、INSERT、UPDATE、DELETE等

#给某个用户所有权限
GRANT ALL PRIVILEGES ON newdatabase.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

撤销权限

REVOKE privileges ON database.table FROM 'username'@'host';

查看权限

SHOW GRANTS FOR 'username'@'host';

查看所有用户权限
SELECT user, host FROM mysql.user;

重命名

RENAME USER 'old_username'@'old_host' TO 'new_username'@'new_host';

DQL(查询)

• 执行顺序
• 别名(AS)
• 条件查询(WHERE)
• 条件查询(HAVING)
• 排序(ORDER BY)
• 分组(GROUP BY)
• 去重(DISTINCT)
• 分页(LIMIT)
• 聚合查询
• 连表查询(JOIN)
• 子查询
• 联合查询(UNION)

执行顺序

SQL语句:
SELECT  FROM   WHERE   GROUP BY  HAVING  ORDER BY  LIMIT

执行顺序:
FROM           表名列表
WHERE          条件列表
GROUP BY       分组字段表
HAVING         分组后条件列表
SELECT         字段列表
ORDER BY       排序字段表
LIMIT          分页参数

Base

SELECT column1, column2, ...
FROM table_name
WHERE condition;

查询所有字段

SELECT * FROM table_name WHERE condition;

别名(AS)

• 可以给列名或表名取别名，使用AS关键字或直接写别名。

SELECT column1 AS alias1, column2 AS alias2
FROM table_name AS alias_table;

条件查询(WHERE)

SELECT column1, column2, ...
FROM table_name
WHERE condition;

条件查询(HAVING)

• 用于过滤分组后的结果

SELECT column1, COUNT(column2) AS count_column
FROM table_name
GROUP BY column1
HAVING count_column > 10;

排序(ORDER BY)

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name ASC|DESC;

#ASC：升序
#DESC: 降序

分组(GROUP BY)

SELECT column1, COUNT(column2) AS count_column
FROM table_name
GROUP BY column1;

去重(DISTINCT)

SELECT DISTINCT column1, column2
FROM table_name;

分页(LIMIT)

SELECT column1, column2, ...
FROM table_name
LIMIT offset, count;

#offset：指定从哪一行开始返回结果。
#count：指定返回的记录数。

聚合查询

使用COUNT(), SUM(), AVG().MAX(), MIN(), 等聚合函数。

SELECT COUNT(column1) AS count_column, AVG(column2) AS avg_column
FROM table_name;

• COUNT()：计算记录数。
• AVG()：计算平均值。
• SUM()：计算总和。
• MAX()：获取最大值。
• MIN()：获取最小值。

连表查询(JOIN)

• 将多张表连接在一起, 并返回数据

1.自连接(inner join) : 查询两个表的交集数据, 只有两个表中"匹配的行"才会被返回

select ename, dname from EMP 
inner join DEPT 
on EMP.deptno=DEPT.deptno and ename='SMITH';

2.左外连接(left join) :  查询出主表中所有数据(left前面的就是主表)

select * from stu 
left join exam 
on stu.id=exam.id; 
#主表是stu

3.右外连接(right join) : 查询出主表中所有的数据(right后面的是主表)

select * from stu 
right join exam 
on stu.id=exam.id;  
#主表是exam

子查询

• 在WHERE或FROM子句中嵌套查询。

1.单行子查询

select * from EMP 
WHERE deptno = (select deptno from EMP wheree name='smith');
                

2.多行子查询

in : 用于检查一个值是否存在于子查询返回的列表中(在为真)

查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

select ename,job,sal,deptno from EMP 
where job in (select distinct job from emp where deptno=10) and deptno<>10;
              

all: 用于比较一个值与子查询返回的所有值。只有当该值大于子查询返回的所有值时(为真)

显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号       

select ename, sal, deptno from EMP 
where sal > all(select sal from EMP where deptno=30); 

any: 用于比较一个值与子查询返回的任意一个值。如果该值大于子查询返回的任意一个值(为真)

显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工)

select ename, sal, deptno from EMP 
where sal > any(select sal from EMP where deptno=30);

联合查询(UNION)

union: 去重

select ename, sal, job from EMP where sal>2500 
union        
select ename, sal, job from EMP where job='MANAGER';
#去掉了重复记录

union all : 不去重

select ename, sal, job from EMP where sal>2500 
union all       
select ename, sal, job from EMP where job='MANAGER';

数据类型

函数

日期函数

字符串函数

数学函数

聚合函数

• 常用于SELECT语句的GROUP BY子句中，对分组后的数据进行统计计算

约束