mysql 创建数据库的 table 时，如何加限定数量的插入（mysql在表中添加数据）

25-04-14 6

在这里，我们将给大家分享关于mysql创建数据库的table时，如何加限定数量的插入的知识，让您更了解mysql在表中添加数据的本质，同时也会涉及到如何更有效地A2-03-13.DDL-HowToRe

在这里，我们将给大家分享关于mysql 创建数据库的 table 时，如何加限定数量的插入的知识，让您更了解mysql在表中添加数据的本质，同时也会涉及到如何更有效地A2-03-13.DDL-How To Rename Table Using MySQL RENAME TABLE Statement、Azure Storage 系列（五）通过Azure.Cosmos.Table 类库在.Net 上使用 Table Storage、css – Fieldset不支持显示：table / table-cell、css – 是否有用于显示的polyfill：table,table-row,table-cell等的内容。

本文目录一览：

mysql 创建数据库的 table 时，如何加限定数量的插入（mysql在表中添加数据）
A2-03-13.DDL-How To Rename Table Using MySQL RENAME TABLE Statement
Azure Storage 系列（五）通过Azure.Cosmos.Table 类库在.Net 上使用 Table Storage
css – Fieldset不支持显示：table / table-cell
css – 是否有用于显示的polyfill：table,table-row,table-cell等

mysql 创建数据库的 table 时，如何加限定数量的插入（mysql在表中添加数据）

mysql 创建数据库的 table 时，有两个属性 (公司名和电话号码)，如何限定一个公司只能插入三个电话号码。我定义公司

branchno varchar(5)

constraint upto3

check(not exists(select branchno from

branch group by branchno having count(*)<4))

执行报错，然后我去掉了 constraint upto3，执行不报错，但是我可以无限制的插入，插入第四个电话号码时，他都照样保存。

A2-03-13.DDL-How To Rename Table Using MySQL RENAME TABLE Statement

转载自：http://www.mysqltutorial.org/mysql-rename-table/

Home / Basic MySQL Tutorial / How To Rename Table Using MySQL RENAME TABLE Statement

How To Rename Table Using MySQL RENAME TABLE Statement

Summary: in this tutorial, you will learn how to rename tables using MySQL RENAME TABLE statement and ALTER TABLE statement.

Introduction to MySQL RENAME TABLE statement

Because business requirements change, we need to rename the current table to a new one to better reflect the new situation. MySQL provides us with a very useful statement that changes the name of one or more tables.

To change one or more tables, we use the RENAME TABLE statement as follows:

1	RENAME TABLE old_table_name TO new_table_name;

The old table ( old_table_name) must exist, and the new table ( new_table_name) must not. If the new table new_table_name does exist, the statement will fail.

In addition to the tables, we can use the RENAME TABLE statement to rename views.

Before we execute the RENAME TABLE statement, we must ensure that there is no active transactions or locked tables.

Note that you cannot use the RENAME TABLE statement to rename a temporary table, but you can use the ALTER TABLE statement to rename a temporary table.

In terms of security, any existing privileges that we granted to the old table must be manually migrated to the new table.

Before renaming a table, you should evaluate the impact thoroughly. For example, you should investigate which applications are using the table. If the name of the table changes, so the application code that refers to the table name needs to be changed as well. In addition, you must manually adjust other database objects such as views, stored procedures, triggers, foreign key constraints, etc., that reference to the table. We will discuss this in more detail in the following examples.

MySQL RENAME TABLE examples

First, we create a new database named hr that consists of two tables: employees and departments for the demonstration.

HR Sample Database

1	CREATE DATABASE IF NOT EXISTS hr;

CREATE TABLE departments (

department_id INT AUTO_INCREMENT PRIMARY KEY,

dept_name VARCHAR(100)

);

CREATE TABLE employees (

id int AUTO_INCREMENT primary key,

first_name varchar(50) not null,

last_name varchar(50) not null,

department_id int not null,

FOREIGN KEY (department_id)

REFERENCES departments (department_id)

);

Second, we insert sample data into both employees and departments tables:

1 2	INSERT INTO departments(dept_name) VALUES(''Sales''),(''Markting''),(''Finance''),(''Accounting''),(''Warehouses''),(''Production'');

INSERT INTO employees(first_name,last_name,department_id)

VALUES(''John'',''Doe'',1),

(''Bush'',''Lily'',2),

(''David'',''Dave'',3),

(''Mary'',''Jane'',4),

(''Jonatha'',''Josh'',5),

(''Mateo'',''More'',1);

Third, we review our data in the departments and employees tables:

SELECT

department_id, dept_name

FROM

departments;

MySQL RENAME TABLE departments Table

SELECT

id, first_name, last_name, department_id

FROM

employees;

MySQL RENAME TABLE employees table

Renaming a table referenced by a view

If the table that you are going to rename is referenced by a view, the view will become invalid if you rename the table, and you have to adjust the view manually.

For example, we create a view named v_employee_info based on the employees and departments tables as follows:

CREATE VIEW v_employee_info as

SELECT

id, first_name, last_name, dept_name

from

employees

inner join

departments USING (department_id);

The views use the inner join clause to join departments and employees tables.

The following SELECT statement returns all data from the v_employee_info view.

SELECT

FROM

v_employee_info;

MySQL RENAME TABLE with View example

Now we rename the employees to people table and query data from the v_employee_info view again.

1	RENAME TABLE employees TO people;

SELECT

FROM

v_employee_info;

MySQL returns the following error message:

1 2	Error Code: 1356. View ''hr.v_employee_info'' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

We can use the CHECK TABLE statement to check the status of the v_employee_info view as follows:

1	CHECK TABLE v_employee_info;

MySQL CHECK TABLE

We need to manually change the v_employee_info view so that it refers to the people table instead of the employees table.

Renaming a table that referenced by a stored procedure

In case the table that you are going to rename is referenced by a stored procedure, you have to manually adjust it like you did with the view.

First, rename the people table back to the employees table.

1	RENAME TABLE people TO employees;

Then, create a new stored procedure named get_employee that refers to the employees table.

DELIMITER $$

CREATE PROCEDURE get_employee(IN p_id INT)

BEGIN

SELECT first_name

,last_name

,dept_name

FROM employees

INNER JOIN departments using (department_id)

WHERE id = p_id;

END $$

DELIMITER;

Next, we execute the get_employee table to get the data of the employee with id 1 as follows:

1	CALL get_employee(1);

After that, we rename the employees to the people table again.

1	RENAME TABLE employees TO people;

Finally, we call the get_employee stored procedure to get the information of employee with id 2:

1	CALL get_employee(2);

MySQL returns the following error message:

1	Error Code: 1146. Table ''hr.employees'' doesn''t exist

To fix this, we must manually change the employees table in the stored procedure to people table.

Renaming a table that has foreign keys referenced to

The departments table links to the employees table using the department_id column. The department_id column in the employees table is the foreign key that references to the departments table.

If we rename the departments table, all the foreign keys that point to the departments table will not be automatically updated. In such cases, we must drop and recreate the foreign keys manually.

1	RENAME TABLE departments TO depts;

We delete a department with id 1, because of the foreign key constraint, all rows in the people table should be also deleted. However, we renamed the departments table to the depts table without updating the foreign key manually, MySQL returns an error as illustrated below:

DELETE FROM depts

WHERE

department_id = 1;

1	Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`hr`.`people`, CONSTRAINT `people_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `depts` (`department_id`))

Renaming multiple tables

We can also use the RENAME TABLE statement to rename multiple tables at a time. See the following statement:

1 2	RENAME TABLE old_table_name_1 TO new_table_name_2, old_table_name_2 TO new_table_name_2,...

The following statement renames the people and depts tables to employees and departments tables:

1 2	RENAME TABLE depts TO departments, people TO employees;

Note the RENAME TABLE statement is not atomic. It means that if any errors occurred, MySQL does a rollback all renamed tables to their old names.

Renaming tables using ALTER TABLE statement

We can rename a table using the ALTER TABLE statement as follows:

1 2	ALTER TABLE old_table_name RENAME TO new_table_name;

The ALTER TABLE statement can rename a temporary table while the RENAME TABLE statement cannot.

Renaming temporary table example

First, we create a temporary table that contains all unique last names which come from the last_namecolumn of the employees table:

1 2	CREATE TEMPORARY TABLE lastnames SELECT DISTINCT last_name from employees;

Second, we use the RENAME TABLE to rename the lastnames table:

1	RENAME TABLE lastnames TO unique_lastnames;

MySQL returns the following error message:

1	Error Code: 1017. Can''t find file: ''.\hr\lastnames.frm'' (errno: 2 - No such file or directory)

Third, we use the ALTER TABLE statement to rename the lastnames table.

1 2	ALTER TABLE lastnames RENAME TO unique_lastnames;

Fourth, we query data from the unique_lastnames temporary table:

SELECT

last_name

FROM

unique_lastnames;

MySQL ALTER TABLE renames temporary table

In this tutorial, we have shown you how to rename tables using MySQL RENAME TABLE and ALTER TABLE statements.

一，引言

　　上一篇文章我们在.NET 项目中添加了 “WindowsAzure.Storage” 的 NuGet 包进行操作Table 数据，但是使用的 “WindowsAzure.Storage” NeGet 以及没微遗弃了，所以我们今天继续讲 Azure Table Storage，使用新的 Nuget 包--- “Microsoft.Azure.Cosmos.Table” 来操作 Table 数据。

nuget 链接：https://www.nuget.org/packages/Microsoft.Azure.Cosmos.Table

我们可以看到当前neget 包允许使用 Microsoft Azure CosmosDB 表 API 以及 Azure 表存储。

--------------------我是分割线--------------------

Azure Blob Storage 存储系列：

1，Azure Storage 系列（一）入门简介

2，Azure Storage 系列（二） .NET Core Web 项目中操作 Blob 存储

3，Azure Storage 系列（三）Blob 参数设置说明

4，Azure Storage 系列（四）在.Net 上使用Table Storage

5，Azure Storage 系列（五）通过Azure.Cosmos.Table 类库在.Net 上使用 Table Storage

6，Azure Storage 系列（六）使用Azure Queue Storage

二，正文

1，安装 “Microsoft.Azure.Cosmos.Table” 的 NuGet

使用程序包管理控制台进行安装

Install-Package Microsoft.Azure.Cosmos.Table -Version 1.0.8

2，创建ITableServiceV2 接口，和 TableServiceV2 实现类，控制器方法等

　　因为使用的 “Microsoft.Azure.Cosmos.Table” 的Nuget 和 “WindowsAzure.Storage” Nuget 中对 Table Storage 的操作中使用的方法，以及类都是一样的，只是命名空间不一样，所以代码上差别不太大。所以大家可以看一下具体代码和已遗弃的方法进行对比。

完整代码：

 1 public interface ITableServiceV2
 2     {
 3         /// <summary>
 4         /// AddEntity(abandoned)
 5         </summary>
 6         <param name="user">user</param>
 7         <returns></returns>
 8         Task AddEntity(UserInfoV2 user);
 9 
10         11          BatchAddEntities(abandoned)
12         13         <param name="users">users14         15         Task BatchAddEntities(List<UserInfoV2> users);
16 
17         18          QueryUsers(abandoned)
19         20         <param name="filter">filter21         22         IEnumerable<UserInfoV2> QueryUsers(string filter);
23 
24         25          UpdateEntity(abandoned)
26         27         28         29         Task UpdateEntity(UserInfoV2 user);
30 
31         32          DeleteEntity(abandoned)
33         34         35         36         Task DeleteEntity(UserInfoV2 user);
37 
38         39          DeleteTable(abandoned)
40         41         <param name="tableName">tableName42         43         Task DeleteTable( tableName);
44     }

ITableServiceV2.cs

class TableServiceV2 : ITableServiceV2
 3         private readonly CloudStorageAccount _cloudStorageClient;
 4         public TableServiceV2(CloudStorageAccount cloudStorageClient)
 5         {
 6             _cloudStorageClient = cloudStorageClient;
 7         }
 8 
 9         #region 01，添加表数据+async Task AddEntity(UserInfo user)
 添加表数据
用户数据15         async Task AddEntity(UserInfoV2 user)
16 17             var cloudTableClient = _cloudStorageClient.CreateCloudTableClient();
18             var cloudTable = cloudTableClient.GetTableReference("USERINFOV2");
19             await cloudTable.CreateIfNotExistsAsync();
20 
21             var tableOperation = TableOperation.Insert(user);
22              cloudTable.ExecuteAsync(tableOperation);
23         } 
24         #endregion
25 
26         async Task BatchAddEntities(List<UserInfoV2> users)
27 28             29             30             31 
32             var tableBatchOperation = new TableBatchOperation();
33             foreach (UserInfoV2 item in34             {
35                 tableBatchOperation.Insert(item);
            }
38              cloudTable.ExecuteBatchAsync(tableBatchOperation);
39 40 
41          Task DeleteEntity(UserInfoV2 user)
42 43             44             45 
46             var queryOperation = TableOperation.Retrieve<UserInfoV2>(user.PartitionKey,user.RowKey);
47 
48             var tableResult =  cloudTable.ExecuteAsync(queryOperation);
49             if (tableResult.Result is UserInfoV2 userInfo)
50 51                 var deleteOperation = TableOperation.Delete(userInfo);
52                  cloudTable.ExecuteAsync(deleteOperation);
53 54 55 
56         async Task DeleteTable( tableName)
57 58             59             var cloudTable = cloudTableClient.GetTableReference(tableName);
60              cloudTable.DeleteIfExistsAsync();
61 62 
63         public IEnumerable<UserInfoV2> QueryUsers( filter)
64 65             66             67 
68             TableQuery<UserInfoV2> query = new TableQuery<UserInfoV2>().Where(filter);
69 
70             var users = cloudTable.ExecuteQuery(query);
71             foreach (var item 72 73                 yield return item;
74 75 76 
77          Task UpdateEntity(UserInfoV2 user)
78 79             80             81 
82             83 
84             85             86 87                 user.ETag = userInfo.ETag;
88                  TableOperation.Replace(user);
89                 90 91 92     }

TableServiceV2.cs

 1 [Route(TableV2)]
 2      TableExplorerV2Controller : Controller
 3  ITableServiceV2 _tableService;
 5 
 6          TableExplorerV2Controller(ITableServiceV2 tableService)
 8             this._tableService = tableService;
 9 10 
11         [HttpPost(AddUser12         async Task<ActionResult> AddEntity([FromBody] UserInfoV2 user)
13 14             await _tableService.AddEntity(new UserInfoV2(huge",610124199012221000") { Email = huge@qq.com13000000000 });
15              Ok();
17 
18         [HttpPost(AddBatchUser19         async Task<ActionResult> AddEntities([FromBody] List<UserInfoV2>20 21             List<UserInfoV2> userList = new List<UserInfoV2>();
22             userList.Add(wangwei610124199012221001wangwei@qq.com1300000000123             userList.Add(6101241990122210021300000000224             userList.Add(6101241990122210031300000000325              _tableService.BatchAddEntities(userList);
26             28 
29         [HttpGet(Users30          ActionResult QueryUsers()
31 var filter = TableQuery.CombineFilters(TableQuery.GenerateFilterCondition(PartitionKey"),TableOperators.And,TableQuery.GenerateFilterCondition(RowKey));
33 
34              Ok(_tableService.QueryUsers(filter));
36 
37         [HttpPut(UpdateUser38          UpdateUser([FromBody] UserInfoV2 user)
40             await _tableService.UpdateEntity(huge@163.com1500000000041             43 
44         [HttpDelete(DeleteEntity45          DeleteEntity([FromBody] UserInfoV2 user)
46 47             await _tableService.DeleteEntity(49 50 
51         [HttpDelete({tableName}52         async Task<ActionResult> DeleteTable(54              _tableService.DeleteTable(tableName);
55             56 57     }

TableExplorerV2Controller.cs

3，添加对 TableServiceV2,CloudStorageAccount 的注入

services.AddSingleton(x => new Microsoft.Azure.Cosmos.Table.CloudStorageAccount(new Microsoft.Azure.Cosmos.Table.StorageCredentials(cnbateblogaccountFU01h022mn1JjONp+ta0DAXOO7ThK3diYhd4xrm0Hpg891n9nycsTLGZXXXXnJpGvTIZvO5VCVFhGOfV0wndOOQ==true));
services.AddSingleton<ITableServiceV2,TableServiceV2>();

4，测试使用新的 Nuget 包中的方法是否能正常操作 Table 表数据

添加用户数据，我们在 postman 中调用添加用户表数据接口

我们可以看到，在Azure Portal 上已经创建出一个叫 “USERINFOV2” 的表信息（注意，大家不要疑惑，可以看看上面添加用户的Service方法，我这里有两行代码）

await cloudTable.CreateIfNotExistsAsync();

接下来我们在Cloud Explorer 查看 “USERINFOV2” Table 的表信息

查询用户数据，我这里还是使用两个查询条件联合进行查询，分别是 “PartitionKey” 和 “RowKey” 作为查询的 Key，通过 “Partition” 等于 “wangwei” 和 “RowKey” 等于 “610124199012221001”

注意（我提前已经将调用了批量添加用户的接口，将 PartitionKey 等于 "wangwu" 的三条数据添加到 Table 中了）

OK，剩余的更新，删除就不再演示了，大家可以自行进行校验，今天的分享内容到此结束。

三，结尾

github：https://github.com/yunqian44/Azure.Storage.git

版权：转载请在文章明显位置注明作者及出处。如发现错误，欢迎批评指正。

css – Fieldset不支持显示：table / table-cell

我正在尝试使用display：table with fieldset,但是它没有正确缩放.如果我更改了< fieldset>到< div>.

我试过Safari和Firefox.

我错过了什么吗？

http://jsfiddle.net/r99H2/

解决方法

基本上,字段集的默认渲染实际上不能用CSS表示.因此,浏览器必须以非CSS方式实现它,并且会干扰CSS对元素的应用.

很多任何无法使用纯CSS重新创建的元素都会有这样的问题.

css – 是否有用于显示的polyfill：table,table-row,table-cell等

我意识到,出于很多目的,显示：table和table-row,table-cell等具有非常有用的定位属性,并且可以用于代替浮动用于很多目的,例如用于制作无浮动菜单,以及更好的对齐页脚.然而,缺乏支持是一种真正的威慑.

我想知道polyfills有哪些选项来模拟显示：table,table-cell,table-row等？

解决方法

例如,对表行的支持实际上非常好.自版本8： http://caniuse.com/#search=table-row以来它一直在IE中.

如果你正在寻找一个垫片来修补它在IE6和7中,你可能会找到你在display-table.htc寻找的,专为IE6 / 7设计.

我们今天的关于mysql 创建数据库的 table 时，如何加限定数量的插入和mysql在表中添加数据的分享就到这里，谢谢您的阅读，如果想了解更多关于A2-03-13.DDL-How To Rename Table Using MySQL RENAME TABLE Statement、Azure Storage 系列（五）通过Azure.Cosmos.Table 类库在.Net 上使用 Table Storage、css – Fieldset不支持显示：table / table-cell、css – 是否有用于显示的polyfill：table,table-row,table-cell等的相关信息，可以在本站进行搜索。

本文标签：