SQL一小时快速入门笔记

MySQL安装

参考：
链接1
链接2

MySQL workbench的使用

SQL创建、删除、显示数据库

# 创建数据库
CREATE DATABASE databasename;
# 删除数据库
DROP DATABASE databasename;
# 显示当前所有的数据库
SHOW DATABASES;
# 从当前数据库切换到databasename数据库
USE databasename;

创建Table

create table table_name(
      column1 datatype,
      column2 datatype,
      column3 datatype,
      ...
)

举例

create table Customers(
CustomerID int,
CustomerName varchar(255),
ContactName varchar(255),
Address varchar(255),
City varchar(255),
PostalCode varchar(255),
Country varchar(255)
);

创建了一个table，但里面没有数据，现在需要插入数据。

在table中插入数据

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

举例

INSERT INTO Customers(CustomerID,CustomerName,ContactName,Address,City,PostalCode,Country)
VALUES (1,'Alfreds Futterkiste','Maria Andres','Obere Str.57','Berlin','12209','Germany');
INSERT INTO Customers(CustomerID,CustomerName,ContactName,Address,City,PostalCode,Country)
VALUES (2,'Ana Trujillo Emparedadas helados','Ana Trujillo','Avda. de la Constitution 2222','Mexico D.F','85021','Mexico');
INSERT INTO Customers(CustomerID,CustomerName,ContactName,Address,City,PostalCode,Country)
VALUES (3,'Antanio Mareno Taqueria','Antanio Mareno','Mataderes 2312','Mexico D.F','05823','Mexico');
INSERT INTO Customers(CustomerID,CustomerName,ContactName,Address,City,PostalCode,Country)
VALUES (4,'Around the Horn','Thoeas Hardy','128 Hanover Sq','London','NA1 1CP','UK');
INSERT INTO Customers(CustomerID,CustomerName,ContactName,Address,City,PostalCode,Country)
VALUES (5,'Berglunds snabbkdp','Christina Berglund','Berguvsvdgen 8','Lulea','S-958 22','Sweden');

从表格中选取数据

# 选取table中所有数据
SELECT * FROM table_name;

创建Table的另外一种形式

CREATE TABLE table_name AS
SELECT column1,column2
FROM existing_table_name
WHERE condition;

举例

create table Customerspart AS
select CustomerName, Address from Customers;

select * from Customerspart;

删除表格

DROP TABLE table_name;

在Table中加入一列

ALTER TABLE table_name
ADD column_name, datatype;

Email列没有信息，现在是空的。

在Table中删除一列

ALTER TABLE table_name
DROP COLUMN column_name;

从Table中选择特定列

SELECT column1,column2 FROM table_name;

查看Table有哪些列

DESCRIBE table_name;

SQL删除行数据

DELETE FROM table_name WHERE some_column=some_value;

# 删除整个列表，慎用
DELETE * FROM table_names; or DELETE FROM table_names;

查看Table某一列不重复的数据有哪些

比如在例子中，想看看用户来自那几个国家，不需要重复输出相同的国家，这时候就可以用

SELECT DISTINCT column1,column2,...
FROM table_name;

Table过滤数据

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

过滤数据的常用操作

操作	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

select * from Customers where customerid=4;
select * from Customers where country<>'Mexico';
select * from Customers where customerid>3;
select * from Customers where customerid<3;
select * from Customers where customerid between 2 and 4;
# A%表示A字开头，%是通配符，啥都行
select * from Customers where ContactName like 'A%';
select * from Customers where country in ('Mexico','UK');

AND, OR , NOT操作

# 两个条件同时满足
SELECT column1,column2,...
FROM table_name
WHERE condition1 AND condition2;
#两个条件满足一个即可
SELECT column1,column2,...
FROM table_name
WHERE condition1 OR condition2;
# 非这个条件
SELECT column1,column2,...
FROM table_name
WHERE NOT condition;

排序 ORDER BY

# 默认升序操作
SELECT column1,column2,...
FROM table_name
ORDER BY column1,column2,... ASC|DESC;

举例

# 按country的字母升序排序
select * from Customers order by country asc;
# 按country的字母降序排序
select * from Customers order by country desc;
# 先按country排列，再按customerid排列
select * from Customers order by country,customerid asc;
# 先按country升序排列，再按customerid降序排列
select * from Customers order by country asc,customerid desc;

TABLE更新数据UPDATE

UPDATE table_names
SET column1=value1,column2=value2,...
WHERE some_column=some values;

举例

update Customers set Address='Shanghai' WHERE CustomerID=5;