学完数据表查询后,总感觉少了点什么,于是决定写篇博客来记录一下我的学习过程。

   在SQL反模式这本书中以产品和产品联系人说明了此反模式。

1.mysql
数据库备份:

图片 1

  
开始的时候一个产品只有一个产品联系人,一个产品联系人需要负责多个产品,

语法:

以这张数据表为例,我们来写有关它的查询语句。

 product_id(产品id)和account_id(产品联系人id)是多对一的关系,表设计如下:

MYSQLDUMP -uUSER -pPASS  DataBase > Path

 

 
图片 2

 其中:USER
是用户名,PASS 是密码,DataBase 是数据库名, Path
是数据库备份存储的位置。

 

目标:变成多对多关系

随着业务的发展,
一个产品可能存在多个产品联系人,即产品和联系人需要是多对多的关系。

  
为了尽少的改动,即把account_id修改varchar类型,把所有的产品联系人Id用逗号分隔一起存储在account_id字段,即存储多值的做法,表设计如下

图片 3

备注:1)执行备份是在系统条件下,而非
mysql 状态下。

在这里,比较简单的我一笔带过,主要给大家讲一下一些难一点的。

存储多值问题

但存储多值的设计会带来如下查询、更新和数据完整性的问题:

  1. 查询某个联系人负责的所有产品信息困难
  2. 查询某个产品对应的联系人详细信息困难
  3. 执行聚合查询困难,如COUNT(),SUM(),AVG(),比如统计各产品的联系人数量
  4. 更新产品的联系人困难
  5. 验证产品联系人id困难,varchar类型可以输入非整形等,没有外键约束
  6. 长度限制,当某个产品的所有联系人id连接起来超过100时,意味着需要字段长度,无法确定最长列表

所以,在设计多对多逻辑关系时,如果一个字段需要存储多值,需要避免此种反模式。

   2)请使用正确的文件地址,在文件地址中要使用双斜杠
“\\” 来代替单斜杠 “\”。

查询所有字段  select * from 表名;

解决方案

创建一张交叉表,来保存多对多的关系,表设计如下:

图片 4

这样前面的问题都可以比较简单的解决,采用Contacts.account_id做索引的查询效率比逗号分隔字符串高效,

还可以在Contacts表中增加一些其他字段,如添加联系人日期,再比如联系人的优先级等

 

查询指定字段 select 字段名 from 表名;

反模式适用场景

这个反模式个人用过,是在设计一个配置表的时候,包含配置项和配置值两个字段,在配置值里存储了多值。

比如查询关键字配置,配置项字段值为查询关键字,配置值字段值就存储了很多关键字,因为多值的记录总共就一条,所以程序比较容易控制。

另外如果作为存储过程的参数,为了是实现in查询时,让参数存储多值时,如果输入值的长度超过参数长度,会出现截断问题导致程序结果异常,这个需要当心的。

所以在反模式书中也强调并不是说反模式不能使用,是有适应场景的。

存储多值的适应场景:

  1. 如果应用程序接收的输入就是带逗号分隔的
  2. 只需要存储和使用,不会进行部分修改
  3. 不需要对其做复杂的查询

2.mysql
数据库的恢复

查询指定记录 select 字段名 from 表名 where 查询条件;

语法:

图片 5

mysql -uUSER -pPASS  DataBase < Path

查询水果表里水果价钱>10的水果,我们可以这样写:

 其中:USER
是用户名,PASS 是密码,DataBase 是数据库名, Path
是备份文件存储的位置。

select f_name from fruits where f_price>10;

 

 

3.实现跨数据库表的内容的复制

查询水果名为apple的价钱多少:

如实现将
db_database3 数据库中的 user 表复制到 database4 中的 user
表,在实现数据库表的内容复制时,需要在每个数据库表前要加上数据库的名称。

select f_price from fruits where f_name='apple';

具体语法如:

 

 insert into db_database4.user(column1,column2) select column1,column2 from db_database3.user;

 

 备注:1)仍然需要你自己在
db_database4 中建立一个与 db_database3 结构一样的 user 表;

between
and范围查询

     
2)要保证表中字段的数据类型的一致性,否则会导致出入数据的错误。

查询某个范围内的值,该操作需要两个值,即范围的开始值和结束值。

 

举个例子,查询价钱在2.00到10.20元之间的水果名称和价钱:

4.使用 UNION
ALL 语句批量插入数据

select f_name,f_price from fruits where f_price between 2.00 and 10.20;

是用 UNION
ALL 语句实现批处理,其语法如下:

 

INSERT  tableName  SELECT columnValue,... UNION  ALL SELECT columnValue,......

 

 1.tableName:要添加数据的数据表。

带like的字符匹配查询

2.columnValue:要添加到数据表中的数据。

‘%’匹配任意长度的字符,甚至是零字符。

例如:往一个
user 表中一次插入多条数据:其中表结构如下:

“_”一次只能匹配一个字符,如果要匹配多个字符,则需要使用相同个数的“_”。

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

 

 我们使用常规的
insert into 插入语句如下:

带and的多条件查询

mysql>  insert into user values('1','tom');
Query OK, 1 row affected (0.06 sec)

mysql>  insert into user values('2','kill');
Query OK, 1 row affected (0.09 sec)

mysql>  insert into user values('3','bill');
Query OK, 1 row affected (0.09 sec)

使用select查询时,可以增加查询的限制条件,这样可以使查询结果更加准确。AND可以连接两个甚至两个以上的查询条件。

 而若使用
union all 语句如下:

select f_name,f_price,s_id from fruits where f_price>6 and s_id=101;
mysql> insert user select '1','jim' union all select '2','kill' union all select '3','bill';

 图片 6

 备注:我们常用的
insert into
语句每次只能实现插入一条语句,这样重复输入往往不方便,这个时候可以选择使用union
all 来替代。

 

 

 带or的多条件查询

 5.更新数据库中的表—其中包括:对表本身结构的更改与对表中数据的更改

与AND相反,顾名思义,表示只需要满足其中一个查询条件的记录即可返回。

1)对表本身结构的更改,使用
alter 语句。

select f_name,f_price,s_id from fruits where f_price>6 or s_id=101;

语法:

图片 7

alter table 表名

 

 例如:

带IN关键字的查询

a.追加语法:alter table 表名 
add (column datatype,column datatype,...);
例如:alter table tt_table add image blob; //在tt_table表中添加一个image列用来保存大头照 

b.修改语法:alter table 表名 modify (column datatype,column datatype,...);
例如:alter table tt_table modify job varchar(60);//修改tt_table表的job列,使其长度为60 

c.删除语法:alter table 表名 drop (column);
例如:alter table tt_table drop job; //删除tt_table表中的job列  

d,修改表名:rename table 表名 to 新表名;
例如:rename table tt_table to tt;//修改tt_table表的表名为tt  

e.将tt_table表的字符集修改为utf8
alter table tt_table character set utf8;

f.将tt_table原表中的列名food 属性为varchar(20)修改为sale_food 属性为varchar(40)
alter table tt_table change column food sale_food varchar(40);

IN操作符用来查询满足指定范围内的条件的记录。使用IN操作符,将所有检索条件用括号括起来,检索条件之间用逗号分隔开来。

 2)对表中数据的更改,使用
update 语句。

 select s_id,f_name,f_price from fruits where s_id IN (101,102);

语法:

图片 8

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

我们来看一下用or查询结果是怎样的

例1:我们为
lastname 是 “Wilson” 的人添加 firstname

select s_id,f_name,f_price from fruits where s_id=101 or s_id=102;
    UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' ;

图片 9

 例2:我们会修改地址(address),并添加城市名称(city):

在这里可以看到,OR操作符和IN操作符使用后的结果是一样的,它们可以实现相同的功能。但是使用

    UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson';

IN操作符可以使得检索语句更加简洁明了,并且IN执行速度要快于OR。更重要的是,使用IN操作符,

备注:alter的修改只涉及表结构方面,而不能对其中保存的数据进行修改!掌握这点可以混淆使用
alter 和 update 。

可以执行更加复杂的嵌套查询(后面将会讲述)。

 

 

6.将数据表清空—–DELETE
语句与TRUNCATE TABLE 语句

 好了,暂时讲这么多。

1)DELETE
语句

DELETE
语句用于删除表中的行。
语法:

DELETE FROM 表名称 WHERE 列名称 = 值

 例1:删除lastname为wilson的行:

DELETE FROM Person WHERE LastName = 'Wilson'

 例2:删除所有行,可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的:

DELETE FROM table_name

发表评论

电子邮件地址不会被公开。 必填项已用*标注