MySQL-表操作

MySQL-表操作

表操作

插入记录

创建如下的表：

create table users (
    id smallint unsigned primary key auto_increment,
    username varchar(20) not null,
    password varchar(32) not null, 
    age tinyint not null default 10,
    sex boolean 
);

当前users表id 字段为自增，在插入数据的时候，并不知道当前的值为多少，可以使用如下方式：

insert users values(NULL, 'Tom', '123', 25, 1);

对于有默认值的可以直接写DEFAULT.

insert users values(NULL, 'John', '246', DEFAULT, 0);

一次性插入多个值：

insert users values(DEFAULT, 'Amy', '147', 3*7-1, 1), (NULL, 'Rose', md5('123'), DEFAULT, 0);

第二种方法：

INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},....

此方法可以使用子查询（SubQuery），一次只能插入一条记录。

insert users set username='Ben', password='456';

第三种方法：

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

此方法可以将查询结果插入到指定数据表。

insert test(username) select username from users where age >= 20;

更新记录
单表更新

如果没有where条件，则更新全部的行。

例如，让age字段的值都加上5

update users set age = age + 5;

让age等于age减去id值，sex都设置为0

update users set age = age - id, sex = 0;

删除记录
单表删除

DELETE FROM tbl_name [WHERE where_condition]

如果没有where条件语句，则会删除所有的记录。

查询语句

查找记录

只查找id，username这两列

select id, username from users;

星号（*）表示所有列。tbl_name.*可以表示命名表的所有列。
查询表达式可以使用[AS] alias_name为其赋予别名。

select id as userId, username as uname from users;

别名可用于GROUP BY, ORDER BY或HAVING子句。

where语句中的操作符

Operator	Description
=	相等
<>	不相等。注意：在其它版本的SQL中可以写作!=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

多个过滤条件可以使用AND和OR

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

查询结果分组GROUP BY

[GROUP BY {col_name|position} [ASC|DESC], ...]

HAVING
设置分组条件

[HAVING where_condition]

ORDER BY 对查询结果进行排序

[ORDER BY {col_name|expr|position} [ASC|DESC], ...]

其默认是按升序排列。
也可以对多个列排序，如下：

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

限制查询结果返回的数量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

选择users表中的第3、4行数据

select * from users limit 2,2;

子查询和连接

子查询

子查询是指在另一个查询语句中的SELECT子句。
例句：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

其中，SELECT * FROM t1 称为Outer Query[外查询](或者Outer Statement),SELECT column1 FROM t2 称为Sub Query[子查询]。
子查询必须出现在圆括号之间。

子查询可以包含多个关键字或条件，如DISTINCT， GROUP BY， ORDER BY ，LIMIT，函数等。
子查询的外层查询可以是：SELECT,INSERT,UPDATE,SET或DO。

使用比较运算符的子查询
从tdb_goods表中取goods_price的平均价格

select round(avg(goods_price),2) from tdb_goods;

avg求平均值，round(avg(goods_price),2)表示保留两位小数。

从tdb_goods表中取出goods_price 的价格大于平均价格的项目。

select goods_id, goods_name, goods_price from tdb_goods where goods_price >= (select round(avg(goods_price),2) from tdb_goods);

当子查询有多个结果时，会提示出错，这时可以使用ANY SOME ALL这些关键字。

select goods_id, goods_name, goods_price from tdb_goods where goods_price > any (select goods_price from tdb_goods where goods_cate = '超级本');

使用[NOT] IN 的子查询

IN与=ANY运算符等下
NOT IN与!=ALL或<>ALL运算符等效

INSERT…SELECT

将查询的结果写入数据表

INSERT [INTO] tbl_name [(col_name)] SELECT ...

现在创建一个分类表tdb_goods_cates

create table if not exists tdb_goods_cates(
    cate_id smallint unsigned primary key auto_increment,
    cate_name varchar(40) not null
);

要把tab_goods中所有的类别添加到tdb_goods_cates中，使用如下语句：

insert tdb_goods_cates (cate_name) select goods_cate from tdb_goods group by goods_cate; 

多表更新

参照另外的表来更新本表的记录

table_reference

现在需要根据分类表tdb_goods_cates来更新tab_goods

update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name 
set goods_cate = cate_id;

创建数据表同时将查询结果写入数据表

现在创建一个品牌表，在创建品牌表tdb_goods_brands的同时，写入数据：

create table tdb_goods_brands(
    brand_id smallint unsigned primary key auto_increment,
    brand_name varchar(40) not null
)
select brand_name from tdb_goods group by brand_name;

参照品牌表tdb_goods_brands更新商品表tdb_goods：

update tdb_goods as g inner join tdb_goods_brands as b on g.brand_name = b.brand_name 
set g.brand_name = b.brand_id;

此时查看tdb_goods表，会发现goods_cate和brand_name的数据类型还是varchar(40)， 修改表结构：

alter table tdb_goods 
change goods_cate cate_id smallint unsigned not null,
change brand_name brand_id smallint unsigned not null;

连接

在MySQL中，JOIN、CROSS JOIN和INNER JOIN是等价的。
连接条件

使用ON关键字来设定连接条件，也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件
使用WHERE关键字进行结果集记录的过滤。

内连接

查找tdb_goods.cate_id等于tdb_goods_cates.cate_id的记录。

select goods_id, goods_name,cate_name from tdb_goods inner join tdb_goods_cates 
on tdb_goods.cate_id = tdb_goods_cates.cate_id;

外连接
左外连接

select goods_id, goods_name,cate_name from tdb_goods left join tdb_goods_cates 
on tdb_goods.cate_id = tdb_goods_cates.cate_id;

上述例子，选择了tdb_goods中全部数据

右外连接

多表连接

显示tdb_goods的全部完整信息，显示品牌信息、分类信息。

select goods_id, goods_name,cate_name,brand_name 
from tdb_goods as g 
inner join tdb_goods_cates as c on g.cate_id = c.cate_id
inner join tdb_goods_brands as b on g.brand_id = b.brand_id;

无限分类的数据表设计

形势如下：

   CREATE TABLE tdb_goods_types(
     type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     type_name VARCHAR(20) NOT NULL,
     parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
  ); 

插入数据：

自身连接
查找子类所有的父类

select s.type_id, s.type_name, p.type_name from tdb_goods_types as s
left join tdb_goods_types as p
on s.parent_id = p.type_id;

查找父类下所有的子类

select p.type_id, p.type_name, s.type_name 
from tdb_goods_types p
left join tdb_goods_types s
on p.type_id = s.parent_id;

多表删除

选出名字重复的商品：

select goods_id, goods_name from tdb_goods 
group by goods_name 
having count(goods_name) >= 2;

delete t1 from tdb_goods as t1 left join 
(select goods_id, goods_name from tdb_goods 
group by goods_name 
having count(goods_name) >= 2) as t2 
on t1.goods_name = t2.goods_name 
where t1.goods_id > t2.goods_id;

其它

IN Operator
IN操作符可以在WHERE中指定多个值。

SELECT * FROM Customers
WHERE City IN ('Paris','London');

---

BETWEEN Operator
BETWEEN可以指定一个范围，NOT BETWEEN相反

BETWEEN与IN一起使用
如下，选择Price在10到20之间，但是CategoryID 不为1,2,3

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

BETWEEN也可以用于Text
如下，查询出ProductName的开头在’C’ 和’M’之间的产品

SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';

BETWEEN也可以用于Date
如下，查询出OrderDate在04-July-1996和09-July-1996之间的数据：

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

Notice that the BETWEEN operator can produce different result in different databases!
In some databases, BETWEEN selects fields that are between and excluding the test values.
In other databases, BETWEEN selects fields that are between and including the test values.
And in other databases, BETWEEN selects fields between the test values, including the first test value and excluding the last test value.

Therefore: Check how your database treats the BETWEEN operator!

---

UNION
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

---

SELECT INTO
MySQL不支持SELECT ... INTO，但是可以使用INSERT INTO ... SELECT来实现

创建一个空表

SELECT *
INTO newtable
FROM table1
WHERE 1=0;

使用现有的表创建另一个表

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

或者

INSERT INTO this_table_archive (col1, col2, ..., coln)
SELECT col1, col2, ..., coln
FROM this_table
WHERE entry_date < '2011-01-01 00:00:00';

---

SQL约束

1.NOT NULL约束
2.UNIQUE约束，唯一，可用于多个列
3.PRIMARY KEY约束
defining a PRIMARY KEY constraint on multiple columns，如：

    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)
    )

4.FOREIGN KEY约束
一个表中的FOREIGN KEY 指向另一个表中的主键 PRIMARY KEY

• 用来阻止会破坏表之间link的action
• 阻止非法的数据被插入到foreign key列中

5.CHECK约束
如：

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')
)

6.DEFAULT约束