lxf's blog
0%

MySql学习整理(一)

发表于 更新于 分类于

MYSQL学习整理(一)

注意：以下整理基于mysql，有些语法在sql sever和oracle中会有不同

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 启动mysql
mysql.server start
# 登录mysql
mysql -u root -p
# 退出mysql
mysql.server stop
# 查看数据库
show databases;
# 进入某个表
use test_db
# 查看表
show tables;
# 查看表结构
desc Persons;
关键字：create database 创建数据库
1
CREATE DATABASE dbname;
关键字：drop database 创建数据库
1
DROP DATABASE database_name;
关键字：create table 创建表
1
2
3
4
5
6
7
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

constraint_name 是约束

sql中有如下约束：

  1. NOT NULL - 指示某列不能存储 NULL 值
  2. UNIQUE - 保证某列的每行必须有唯一的值
  3. PRIMARY KEY -主键- NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  4. FOREIGN KEY -外键- 保证一个表中的数据匹配另一个表中的值的参照完整性。
  5. CHECK - 保证列中的值符合指定的条件。
  6. DEFAULT - 规定没有给列赋值时的默认值。

not null

NOT NULL 约束强制列不接受 NULL 值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建表时添加约束
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);
# 向已创建表添加 not null 约束
# ALTER TABLE table_name MODIFY column_name datatype 修改数据类型
ALTER TABLE Persons
MODIFY Age int NOT NULL;
# 向已创建表删除 not null 约束
ALTER TABLE Persons
MODIFY Age int NULL;

unique

每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 单列unique
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
# 命名 unique 约束，并定义多个列的 unique 约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)
# 向已创建表添加 unique 约束
ALTER TABLE Persons
ADD UNIQUE (P_Id)
# 向已创建表添加命名 unique 约束，并定义多个列的 unique 约束
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
# 删除约束
ALTER TABLE Persons
DROP INDEX uc_PersonID

primary key

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 创建表时添加约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
# 组合主键 
# 注意：其中只有一个主键pk_PersonID
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
# 向已创建表添加约束
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
# 向已创建表添加组合约束
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
# 删除约束
ALTER TABLE Persons
DROP PRIMARY KEY pk_PersonID

foreign key

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。
FOREIGN KEY 约束用于预防破坏表之间连接的行为。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
# MySQL允许我们在表中的多个列上添加FOREIGN KEY约束。条件是子表中的每个外键都必须引用不同的父表。
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
# 向已创建表约束
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
# 向已创建表添加多个约束，需要保证子表中的每个外键都必须引用不同的父表
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
# 删除约束
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
关键字：drop table 删除表包括数据+表本身
1
DROP TABLE table_name;
关键字：truncate table 删除表数据 保留表本身
1
TRUNCATE TABLE table_name;
关键字：select 查询
1
2
3
SELECT column_name,column_name
FROM table_name;
SELECT * FROM table_name;
关键字：distinct 去重
1
2
SELECT DISTINCT column_name,column_name
FROM table_name;

牛客网对应知识点题目链接：
SQL8找出所有员工当前具体的薪水salary情况:
https://www.nowcoder.com/practice/ae51e6d057c94f6d891735a48d1c2397?tpId=82&tqId=29760&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey

关键字：where 过滤记录
1
2
3
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
关键字：and /or 基于一个以上的条件对记录进行过滤
1
2
3
SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA');
关键字：order by 排序
1
2
3
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC; #ASC升序 DESC降序
关键字：insert into 插入
1
2
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
关键字：update 更新
1
2
3
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
关键字：delete 删除
1
2
3
4
5
DELETE FROM table_name
WHERE some_column=some_value;
# 删除全部数据
DELETE FROM table_name;
DELETE * FROM table_name;
关键字：limit 返回指定数目记录
1
2
3
4
5
6
7
SELECT column_name(s)
FROM table_name
LIMIT number;
# 返回某个位置开始的指定数目记录
# 以下表示，从第2条记录往后取1条记录（即是第三条记录）
limit 1 offset 2
limit 2,1

牛客网对应知识点题目链接：

  1. SQL2 查找入职员工时间排名倒数第三的员工所有信息:
    https://www.nowcoder.com/practice/ec1ca44c62c14ceb990c3c40def1ec6c?tpId=82&tqId=29754&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey
关键字：like 在 WHERE 子句中搜索列中的指定模式
1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
关键字：in 在 WHERE 子句中规定多个值
1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

牛客网对应知识点题目链接：

  1. SQL12
    https://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6?tpId=82&tqId=29764&rp=1&ru=%2Factivity%2Foj&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey
关键字：between 范围
1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
关键字：as 别名
1
2
3
4
5
6
7
8
9
# 列别名
SELECT column_name AS alias_name
FROM table_name;
# 表别名
SELECT column_name(s)
FROM table_name AS alias_name;
# 多列合并别名 concat
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

使用别名的情况：

  1. 在查询中涉及超过一个表
  2. 在查询中使用了函数
  3. 列名称很长或者可读性差
  4. 需要把两个列或者多个列结合在一起
关键字：join 表连接 横向连接（列合并）

inner join/join 两者是相同的

1
2
3
4
5
6
7
8
9
10
#inner join
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
# join
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

left join

关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL.

1
2
3
4
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

right join

关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

1
2
3
4
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

full join

关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。

1
2
3
4
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

牛客网对应知识点题目链接：
SQL3 查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no:
https://www.nowcoder.com/practice/c63c5b54d86e4c6d880e4834bfd70c3b?tpId=82&tqId=29755&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey
SQL4 查找所有已经分配部门的员工的last_name和first_name:
https://www.nowcoder.com/practice/6d35b1cd593545ab985a68cd86f28671?tpId=82&tqId=29756&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey
多表连接类
SQL19
https://www.nowcoder.com/practice/5a7975fabe1146329cee4f670c27ad55?tpId=82&tqId=29771&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey
SQL21
https://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5?tpId=82&tqId=29773&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey

关键字：union 合并两个或多个 select 语句的结果集 纵向合并（表合并）

请注意，union 内部的每个 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 select 语句中的列的顺序必须相同。

1
2
3
4
5
6
7
8
9
# 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
# union all
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
# UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
关键字：insert into select 复制

从一个表复制数据，然后把数据插入到一个已存在的表中

1
2
3
4
5
6
7
# 复制全部
INSERT INTO table2
SELECT * FROM table1;
# 复制部分
INSERT INTO table2 (column_name(s))
SELECT column_name(s)
FROM table1;
关键字：check 约束用于限制列中的值的范围
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 创建时添加单个约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
# 创建时添加多个约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)
# 向已创建表添加单个约束
ALTER TABLE Persons
ADD CHECK (P_Id>0)
# 向已创建表添加多个约束
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
# 删除约束
ALTER TABLE Persons
DROP CHECK chk_Person