sql复习

基本

创建表：

CREATE TABLE Websites(
  id INT PRIMARY KEY,
  name VARCHAR(255),
  url VARCHAR(255),
  alexa INT,
  country VARCHAR(255)
);


INSERT INTO Websites(id, name, url, alexa, country)
VALUES
  (1, 'Google', 'https://www.google.cm/', 1, 'USA'),
  (2, '淘宝', 'https://www.taobao.com/', 13, 'CN'),
  (3, '菜鸟教程', 'http://www.runoob.com/', 4689, 'CN'),
  (4, '微博', 'http://weibo.com/', 20, 'CN'),
   (5, 'Facebook', 'http://www.facebook.com/', 3, 'USA');


CREATE TABLE access_log(
  aid INT PRIMARY KEY,
  site_id INT,
  count INT,
  date VARCHAR(255)
);


INSERT INTO access_log
VALUES
  (1,1 ,45 , '2016-05-10'),
  (2,3 ,100 , '2016-05-13'),
  (3,1 ,230 , '2016-05-14');

# AND & OR
SELECT * FROM Websites
WHERE country='CN'
AND alexa > 50;

SELECT * FROM Websites
WHERE country='USA'
OR country='CN';

# DISTINCT 返回唯一的column1, column2, ...的组合
SELECT DISTINCT column1, column2, ...
FROM table_name;


# 将查询的结果使用某些列升序（默认）或降序排列
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

SELECT * FROM Websites
ORDER BY country,alexa DESC;


# 更新表中已存在的记录
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE Websites 
SET alexa='5000', country='USA' 
WHERE name='菜鸟教程';


# 删除行
DELETE FROM table_name
WHERE condition;


# 限制返回的记录的数目
SELECT column_name(s)
FROM table_name
LIMIT number;

select * from user limit 2;

# like
# 选取 name 不包含模式 "oo" 的
SELECT * FROM Websites
WHERE name NOT LIKE '%oo%';


# AS用于设置表或列的别名
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

SELECT w.name, w.url, a.count, a.date
FROM Websites AS w, access_log AS a
WHERE a.site_id=w.id and w.name="菜鸟教程";


# ALTER 
ALTER TABLE 语句用于在已有的表中添加、删除或修改列。
## 添加列
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


## 常见数据类型
VARCHAR(n)
INT
FLOAT(p)
DATE
TIME

其他就不摘录了，直接看菜鸟教程。

进阶

删除表的三种方式

drop table user; // 删除整个表，包括表的结构。
truncate table user; // 删除表中所有元素
delete from user where user_id = 1; // 删除表中某些元素

拼接多个表查询的结果：

基于表之间的共同字段，将查询结果拼接起来。

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.count;

INNER JOIN：返回满足条件的行。
LEFT JOIN：返回满足条件的行，如果左表的某行没有和右表的任何行匹配，那么只返回左表的内容，右表的内容为NULL。
RIGHT JOIN：返回满足条件的行，如果右表的某行没有和左表的任何行匹配，那么只返回右表的内容，左表的内容为NULL。
FULL JOIN：返回满足条件的行，如果右表的某行没有和左表的任何行匹配，那么只返回右表的内容，左表的内容为NULL；如果左表的某行没有和右表的任何行匹配，那么只返回左表的内容，右表的内容为NULL。

sql注入

sql注入
sql注入：输入字符串将原有的sql语句拼接成非法的操作，如删库、休眠、始终合法（OR TRUE）等。
避免SQL注入：1.验证字符串合法性 2.不将参数的内容视为 SQL 语句的一部分来进行处理 3.限制用户操作数据库的权限

其他：

# 连接两个表的查询结果集 UNION ALL代表允许重复行 UNION代表不允许重复行
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

# 只复 id=1 的数据到 "Websites" 中
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id=1;

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

# 如果UnitsOnOrder是null，那么IFNULL(UnitsOnOrder,0)为0
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

视图：相当于创建了一个虚的表。视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

# 选取 "Products" 表中所有单位价格高于平均单位价格的产品：
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
# 我们可以像这样查询上面这个视图：
SELECT * FROM [Products Above Average Price]

# 创建同名视图就可以覆盖原有视图，这就起到了修改视图的作用

# 删除视图
DROP VIEW view_name;

SQL 约束：

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

# 下面的 SQL 在 "Orders" 表创建时在 "P_Id" 列上创建 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) # 约束名为fk_PerOrders 

)

索引

用户无法看到索引，它们只能被用来加速搜索/查询。

CREATE INDEX index_name
ON table_name (column_name)

# 唯一索引
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

# 删除索引
ALTER TABLE table_name DROP INDEX index_name

posted @ 2023-10-15 19:44 好人~ 阅读(8) 评论(0) 编辑收藏举报

刷新页面返回顶部

codingbigdog

sql复习

基本

进阶

删除表的三种方式

拼接多个表查询的结果：

sql注入

其他：

SQL 约束：

索引

公告